EXCEL实用技巧培训PPT课件.pptx

实用技巧培训 EXCEL2013 2020 2 4 1 技巧一 排版篇 2020 2 4 2 Excel中的颜色要怎么搭配 视觉上更能接受且区分度高 首行 首列最深间行留白 三色原则 有必须重复信息的行 那么如果没有制作色差的话很容易看走眼 而且会较快的眼睛疲劳 所以很简单的改动就能让你的表单显得更为友好 为什么是三色 1 三色原则能最大范围地适用 对比色配色方案 互补色配色方案 或者 邻近色配色方案 2 最主要的原因是省油墨 办公室绝大部分企业都是黑白打印 双面利用以节省成本 如果只有三色 即白色 2主色 另外2个主色会以不同灰度的形式印刷出来 为什么要错行配色 以上内容引用自知乎用户ChaoticGood 上图为苹果iwork的numbers自带样式 饼图一类的统计图用色系根据明度递减做配色 表单依旧是三色配色方案 1 全选 Ctrl A 需要处理的数据 2 开始 套用表格格式 强烈建使用EXCEL的最新版本 目前最新是2013版 因为它的模板真的很强大 很省事 2020 2 4 6 做完两行请使用格式刷 不使用模板 你想要自己做 也可以 样式 套用表格格式 新建表格样式 设置 第一行条纹 第二行条纹 颜色 套用表格样式 其中 第一行条纹 为偶数行 包含标题行算起 即第二行 第二行条纹 为奇数行 如果不设置标题行则反之 点击后选 格式 然后设置填充颜色即可 方法一 方法二 技巧二 操作篇 2020 2 4 8 Alt Enter 表格内换行 1 有时收到同事的工作总结 我们手贱 调整了下列宽 错误案例 正确案例 使用 空格 调整格式的 使用 Alt Enter 调整格式的 2020 2 4 9 F4 重复上一步操作 2 插入行 神技能 节省操作步骤 删除列 字体加粗 填充背景色 2020 2 4 10 分号后面那个 3 主要解决的问题 录入身份证号 变成科学计算法录入 0 开头的编号 自动省略0 当然 也可以通过设置单元格格式来解决这个问题 注意要使用英文状态下的输入法 否则EXCEL无法识别 2020 2 4 11 复制 选择性粘贴 4 方式一 方式二 复制完后 点左上角粘贴 粘贴后 点右下角图标 2020 2 4 12 分列 5 做微信运营的同事后台导出的活动数据经常是这样的 2020 2 4 13 为什么分隔符号里明明有逗号 EXCEL却无法识别 而需要在其他那里输 才可以呢 答案是 逗号也有英文和中文的区别 EXCEl只能识别英文状态下的逗号 经过分列处理后 2020 2 4 14 冻结单元格 6 主要用途就是在移动表格时 方便能看到行表头或列表头 2020 2 4 15 技巧三 公式篇 2020 2 4 16 想要固定行或列 就在公式中给 按F4也有同样效果 此符号仅在填充单元格复制公式时适用 单元格绝对引用 1 未设置单元格绝对引用时 设置D2 B2 C2 往下填充公式 结果默认 D3 B3 C3D4 B4 C4 加 后再进行填充 D3 B3 C D4 B4 C 想要固定加同一个数作为奖金 这几个公式完全可以一起学 If countif sumif countifs sumifs 2 if countif countifs sumif sumifs 条件计数 条件求和 逻辑判断 判断是否满足某个条件 如果满足返回一个值 如果不满足则返回另一个值 计算某个区域中满足给定条件的单元格数目 统计一组给定条件所指定的单元格数 对满足条件的单元格求和 对一组给定指定的单元格求和 在D2设置公式 然后进行填充后 即可以获得下列结果 实际案例 迟到3次以下 即可获得全勤奖 否则 只有0元 if 公式含义 统计C列不为0的数量 注意 如果是不等于某个数字 或表示大于 小于等关系时 公式中需要加 实际案例 统计迟到人数 countif 公式含义 统计C列不为0 且B列大于5000的行的数量 实际案例 统计工资5000元以上的迟到人数 countifs 公式含义 如果C列的值为0 则将B列的值进行求和 实际案例 统计未迟到人员的工资总额 sumif 2020 2 4 23 以上的例子举得很无聊 谁有事没事拿工资说事 目的是为了帮助大家理解公式 下面介绍的这个函数 简直就是神器 2020 2 4 24 Vlookup 数据匹配 3 表一 行政部MM给了我两张表 说要将离职人员从通讯录中删除 行政部MM给了我两张表 说要将离职人员从通讯录中删除 表二 选中单元格 公式 插入函数 vlookup 确定 5 1 2 3 4 不理解 没关系 记住最后两项 填1和0就行了 需要查询和匹配的内容 例如表1通讯录的A列 需要删除的值 例如表2离职人员的A列 数字1表示返回表2中第 1 列的值 数字0表示精确匹配 2020 2 4 26 最终得出来的结果就是这样子的 再应用 筛选 或是 排序 就能够很轻松地将离职人员删除 最典型的应用场景 从表A的数据中找出表B的数据 2020 2 4 27 可在目标单元格后面增加某些字符 4 假设我们公司会给员工下发个工资短信 实际不会发 内容是 你本月的工资是 元 全勤奖是 元 总计 元 技巧四 数据透视表 2020 2 4 29 数据透视表最适合做这种 分类汇总计算 求和 的活 这是典型应用场景 2020 2 4 30 为了简单说明问题 拿出一小部分数据来做演示 这才是原始数据 2020 2 4 31 勾选需要分析的字段 并扔到区域中 注意勾选相应类别 及调整行 列 值的选项 更改计数类别 可以右键单击计数项 值汇总依据 2020 2 4 32 除了两个维度的数据透视表 还可以做三个维度 四个维度的数据透视表 大家感兴趣可以自己试一试 原理类似 一个数据透视表顶很多个公式啊 2020 2 4 33 技巧五 下拉菜单设计 2020 2 4 34 有时 我们提供表格给别的同事填写的时候 希望限定单元格内可填写的内容 例如只能按我们规定的选项来填 或者是自己填写时 图省事只希望有那么几个选项 这时候下拉菜单就非常有用了 记住这里如果是手输文字选项的话 一定要用英文状态下的逗号分隔开 还有一些高阶用法 大家可以自己钻研一下 篇幅关系不展开讲了 在用户输入内容时 给出提示或警告 可以在以下两个选项中进行设置 三级下拉菜单 最典型的用法莫过于 在一级菜单 省 选了省后 二级菜单 市 的选项也能根据省的情况进行相应的改变 需要用到Indirect函数 技巧六 宏 的使用 2020 2 4 38 当你把机械化的一套操作通过录制宏实现 眼看表格自动化操作 在两秒内给你返回原来每天固定要做十几分钟的数据分析结果时 那个鸡皮疙瘩 当你学会了前面介绍的所有的基本操作 仍不满足 当你每天有很多重复的 机械的 简单的操作 恭喜你 宏是可以满足你的 宏的应用场景 宏的定义 Excel中的宏 指的就是实现单一功能或复杂功能的VBA代码的集合 使用宏功能 可以让我们在处理电子表格的时候 仅通过一步的操作 就能实现需要通过多步才能完成的操作 还是以一个实际例子好理解一点 假设我们要处理多个类似的表格 例如对表格的数据进行美化 我们一般拿到的未经处理的数据是这样子的 1 录制宏路径 视图 宏 录制宏 2 定义宏的内容 这里要选择保留在个人宏工作簿 假设我们固定需要做的操作是 1 调整列宽至自动适合 2 调整字体为微软雅黑 3 设置表头 4 去掉网络线 5 设置灰色边框 3 开始正常的操作在表1中 做以下操作1 调整列宽至自动适合 2 调整字体为微软雅黑 3 设置表头 4 去掉网络线