Ecel实战培训_表格类模板_表格模板_实用文档.ppt

Excel实战技巧培训 Jim Zhang 目录 2 前言 操作与技巧 原来可以如此简单 数据透视表 百变星君 函数与公式 Excel的精髓之处 1 2 4 3 00 01 02 03 04 05 06 07 图表 专业的我也可以做到 5 6 总结与作业 前言 Excel有些什么 3 前言 烦恼 困惑与疑问 4 前言 成为 高手 的捷径 5 心态 方法 坚持 No 1积极的心态 兴趣是最好的老师 压力是前进的动力 No 2正确的方法循序渐进善用资源 学以致用阅读与实践的结合 No 3持之以恒的努力 每天进步一点点 前言 关于本次培训 6 前言 起步之前让我们回顾几个场景 7 目录 8 前言 操作与技巧 原来可以如此简单 数据透视表 百变星君 函数与公式 Excel的精髓之处 1 2 4 3 00 01 02 03 04 05 06 07 图表 秀的是专业 拼的是创意 5 6 总结与作业 操作与技巧基础 排序 9 排序的基本操作 案例1 如何按汉字笔画排序 案例2 如何用排序制作工资条 案例3 如何按行排序 案例4 如何对合并单元格相邻的数据区域排序 案例5 如何按单元格颜色排序 2007以上版本 操作与技巧基础 筛选 自动筛选 10 筛选的基本操作 案例1 如何对双行标题列表进行筛选 案例2 筛选中数据区域的注意事项 案例3 如何进行反向选择 案例4 如何不增加辅助列对日期按星期筛选 操作与技巧基础 查找与替换 11 查找与替换基本操作 案例1 如何清除或修改特定格式的单元格内容 案例2 如何查找批注中的内容 案例3 如何在整个工作簿查找 案例4 如何删除工作表中的通配符和特定字符 操作与技巧基础 定位 12 定位的概念和基本操作 案例1 如何披量清除工作表上的各种对象 案例2 如何运用定位行列差异单元格 案例3 如何利用定位将数据填充完整 测试题1 操作与技巧基础 选择性粘贴 13 选择性粘贴的基本操作和意义 案例1 如何将公式转为数值 案例2 如何直接对某个区域进行加减乘除运算 案例3 如何只复制单元格中的有效性设置 案例4 关于转置功能的运用 案例5 关于跳过空单元格 操作与技巧基础 效率加倍的快捷键 14 何为有用的快捷键 需要自己去摸索总结 本次培训仅仅列举了一小部分供大家参考 令效率加倍的快捷键举例 目录 15 前言 操作与技巧 原来可以如此简单 数据透视表 百变星君 函数与公式 Excel的精髓之处 1 2 4 3 00 01 02 03 04 05 06 07 图表 秀的是专业 拼的是创意 5 6 总结与作业 操作与技巧进阶 单元格格式 16 单元格格式概述 Ctrl 1 案例1 来浏览一些 千奇百怪 的自定义格式 案例2 如何对手机号码进行分段显示 案例3 如何对不同范围的数值使用不同格式 案例4 如何将自定义格式的显示转换成真实数据 操作与技巧进阶 工作表 工作簿的保护 17 保护工作表 保护工作簿 案例1 保护工作表基础操作 案例2 如何限定用户只能选择可编辑的区域 案例3 如何在已保护的工作表中运用筛选 操作与技巧进阶 数据有效性 18 数据有效性的概述 案例1 如何利用数据有效性控制单元格的输入 案例2 如何限制重复值的录入 案例3 用有效性制作二级下拉菜单 案例4 利用有效性圈释不符合要求的数据 操作与技巧进阶 条件格式 19 条件格式概述 案例1 利用条件格式标注达成率 案例2 用条件格式判断列是否有重复 案例3 用条件格式自动实现间隔底纹 案例4 用条件格式自动为小计总计设定格式 简单介绍2010的条件格式 迷你图 操作与技巧进阶 名称的运用 20 名称是什么东东 操作与技巧进阶 名称的运用 21 为什么要使用名称 操作与技巧进阶 名称的运用 22 关于名称的约定 操作与技巧进阶 名称的运用 23 定义名称的三种方法 方法1 插入名称 案例2 使用名称框 案例3 根据所选内容创建 批量 操作与技巧进阶 名称的运用 24 关于名称的案例 案例1 在名称中使用常量 案例2 在名称中使用函数 案例3 动态名称的运用 案例4 如何一次性删除所有定义的名称 操作技巧进阶 综合案例分析 25 操作与技巧进阶 高级筛选 26 高级筛选及举例 参考一份网络上的教程 请看案例 目录 27 前言 操作与技巧 原来可以如此简单 数据透视表 百变星君 函数与公式 Excel的精髓之处 1 2 4 3 00 01 02 03 04 05 06 07 图表 秀的是专业 拼的是创意 5 6 总结与作业 函数与公式 热身运动Excel运算符 28 让我们一起来看看Excel的常用运算符 函数与公式 热身运动公式 函数 29 公式 是以等号 开头 通过使用运算符将数据 函数等元素按一定顺序连接在一起 从而实现对工作表中的数据执行计算的等式 例如C1 A1 A2 A3 A4 B1 B2 B3 B4 函数 简而言之 函数就是预先定义好了的公式 比如上例中的公式就可以用函数C1 SUM A1 B4 代替 函数不仅可以简化公式 而且具有仅用运算符连接的一般公式所不能替代的功能 例如查找引用 逻辑判断等等 函数与公式 函数的结钩分析 30 函数与公式 热身运动快速选择函数 31 快速选择 输入函数的方法 通过 公式库 功能区快速选择输入 通过开头字母的提示快速输入 通过插入函数输入 直接输入 目录 32 前言 操作与技巧 原来可以如此简单 数据透视表 百变星君 函数与公式 Excel的精髓之处 1 2 4 3 00 01 02 03 04 05 06 07 图表 秀的是专业 拼的是创意 5 6 总结与作业 函数起步 F1 不离不弃的好兄弟 33 自学函数的捷径全在这里了记住 excel本身就是最好的参考书 F1 F1哪里不会点哪里 函数起步 F4 相对 绝对 由你说了算 34 何为引用 有何影响 案例1 三种引用的同时理解 案例2 为什么vlookup会出现错误值 案例3 综合案例 函数起步 F9 一把 庖丁解牛 的宝刀 35 目录 36 前言 操作与技巧 原来可以如此简单 数据透视表 百变星君 函数与公式 Excel的精髓之处 1 2 4 3 00 01 02 03 04 05 06 07 图表 秀的是专业 拼的是创意 5 6 总结与作业 函数如此简单 TRUEvsFALSE 37 Excel的逻辑值只有2个 TRUEFALSE TRUEFALSE是函数的关键 可以这么说 任何函数都是在解决TRUE还是FALSE的问题 TRUEFALSE与运算符进行各种计算时 请牢记 TRUE 1FALSE 0 请务必牢记 通过示例文件 我们来详细解释一下TRUE和FALSE 函数如此简单 逻辑判断是函数的灵魂 38 串联开关等同于逻辑函数AND 即多个条件同时满足 比如 TRUE TRUE 并联开关等同于逻辑函数OR 即只要满足一个条件 比如 TRUE FALSE 函数如此简单 逻辑判断是函数的灵魂 39 通过案例 轻松掌握逻辑运算 案例1 是否能申请经济适用房 案例2 如何根据性别和职务计算补贴 案例3 多层判断 彻底理清逻辑关系 函数如此简单 数据汇总统计类函数 40 注 数据汇总统计类将全部采用案例形式进行讲解 汇总求和类函数 关键词 sum sumif sumifs subtotal 案例1 汇总多个表格中相同单元格的数据 sum 案例2 如何汇总某品名的销售数量 sumif 案例3 深入理解sumif函数的 相对位置 案例4 如何汇总月薪在2000 4000元的工资总额 sumifs 案例5 如何忽略隐藏的行汇总数据 subtotal 案例6 如何始终生成连续的商品序号 subtotal 函数如此简单 数据汇总统计类函数 41 数据计数类函数 关键词 count countif countifs 案例1 如何统计成绩表中的及格人数 countif countifs 案例2 如何统计产品规格中带 号的产品个数 countif 案例3 如何统计35岁以上有房有车的人员名单 案例4 如何统计不重复的数据个数 函数如此简单 数据汇总统计类函数 42 极值均值类函数 关键词 min max median large small average 案例1 如何设置提成奖金的上下限 max min mdeian 案例2 如何不计重复返回一组数据中次大 次小值 large small 案例3 如何统计前十名成绩的平均分 average large 案例4 如何根据条件计算平均分 averageif averageifs 函数如此简单 查找引用类函数 43 注 查找引用类将采用函数解析 案例的方式 VLOOKUP函数动态解析 办公室函数使用率之最 MATCH函数动态解析 案例1 根据不同的商品不同的订货量计算单价及折扣 案例2 输入工号后可自动显示相关的人事资料 测试题 案例3 如何处理VLOOKUP函数找不到对应数据产生的错误 44 函数如此简单 查找引用类函数 INDXE函数动态解析 案例4 如何从右往左查询对应的数据 测试题 案例5 如何根据品牌和规格双条件查找单价 OFFSET函数动态解析 初次接触比较难理解 案例6 如何保证新增加的销量自动求和 offset counta动态引用 函数如此简单 文本函数 45 我们一起来学习几个最常用的文本处理函数 LEFT MID RIGHT 三个兄弟 本事各不同 FIND 在文本中找文本SUBSTITUTE 替换函数 案例1 判断产品缺陷中是否含某个字符 案例2 不通过分列如何将数据分成2列 案例2 如何查找某字符在字符串中第N次出现的位置 SUMPRODUCT 函数中一颗璀璨的明珠 46 千万不会要去迷恋Excel帮助文件中对此函数的描述 也许是因为SUMPRODUCT函数能力太过超群 Excel帮助文件都嫉妒了它的才能 SUM PRODUCT SUMPRODUCT 数据1 数据2 数据3 数据4 数据N SUMPRODUCT 数据1 数据2 数据3 数据4 数据N 将汇总数相乘 是这样吗 将乘积的结果汇总 Good SUMPRODUCT 数据1 数据2 SUMPRODUCT 函数中一颗璀璨的明珠 47 在了解的基本原理后 我们还是通过案例来讲解SUMPRODUCTDE的常规用法 案例1 起步 让一般统计类函数汗颜 案例2 奔跑 让多条件统计类函数折服 Hi 不小心 我们已经打开了统计类 数组公式 的潘多拉盒子 函数 美丽 魅力 绽放 你发现了吗 48 SUMPRODUCT 函数中一颗璀璨的明珠 很遗憾 没有任何事物是完美的 SUMPRODUCT也有自己的缺憾 1 在用我们今天所讲的 SUMPRODUCT 数据1 数据2 的方式时 计算项 求和时出现文本 错误值 逻辑值 将会出错 2 用SUMPRODUCT 计算很大的表 很复杂的逻辑时 运行速度会比较慢 因为该函数内部执行的是数组运算 会相对耗时 当然这个耗时只是相对而言 比如像对SUMIFS DSUM等等 我和函数的约定 日积月累 学以致用 49 函数是Excel中最核心的部分 是技巧 图表 透视表 VBA的基本功 同时函数本身内容也相当丰富 值得大家不断的去学习 思考如何让工作更轻松 同时 由于函数内容太多 不需要大家把每个函数都去掌握由于函数很有深度 很多函数都值得反复的去学习 去发现由于许多函数在理解上比较困难 不可能短期内达到某个高度因此 建议大家 日积月累 学以致用 Lookup查找之王 Text文本之王 目录 50 前言 操作与技巧 原来可以如此简单 数据透视表 百变星君 函数与公式 Excel的精髓之处 1 2 4 3 00 01 02 03 04 05 06 07 图表 秀的是专业 拼的是创意 5 6 总结与作业 数据透视图 数据透视图 数据透视表 数据透视表 认识数据透视表 51 Excel数据清单 数据库 文本文件 其他关系型数据文件 Excel数据透视表引擎 数据透视表 数据透视图 数据透视表 数据透视表 了解数据透视表的结构 52 数据透视表 认识数据透视表结构 53 报表筛选区域 行区域 列区域 数值区域 数据透视表 创建一个透视表 54 选择区域中任意单元格插入透视表选择列 行 值 数据透视表 无拘无束的数据透视表 1 55 筛选字段 列 行字段任意拖拽的无拘无束 字段汇总方式的无拘无束 字段显示方式的无拘无束 修改数据透视表的字段名称 灵活处理错误值和空白单元格 数据透视表 无拘无束的数据透视表 2 56 谁先谁后由决定 展示字段先后顺序任意指定 数据透视表的排序 数据透视表的筛选 数据透视表分组 组合方式由我定 更改数据源和移动数据透视表 数据透视表 如虎添翼的计算字段和计算项 57 首先理解计算字段 计算项 案例 用计算项计算销售提成 案例 用计算项计算年销售差异 数据透视表 动态创建数据透视表 58 方法 定义名称法创建动态的数据透视表 方法 使用表功能创建动态的数据透视表 方法 导入外部数据创建动态数据透视表 数据透视表 多重合并 外部引用 透视图 59 数据透视表的内容 比我们想象的还要多 今天我们仅仅见识了他的 冰山一角 目录 60 前言 操作与技巧 原来可以如此简单 数据透视表 百变星君 函数与公式 Excel的精髓之处 1 2 4 3 00 01 02 03 04 05 06 07 图表 秀的是专业 拼的是创意 5 6 总结与作业 图表 图表秀 61 图表 图表秀 62 图表 图表的制作过程 63 理解数据分析数据提炼