Excel 实战技巧培训

1. Excel实战技巧培训 2011-09-10 Jim.Zhang

2. 目录 00 01 02 03 04 05 06 07 前言 操作与技巧 ——原来可以如此简单 函数与公式 ——Excel的精髓之处 数据透视表 ——百变星君 图表 ——专业的我也可以做到 总结 与作业 4 5 6 3 1 2

3. 前言——Excel有些什么 VBA编程 VBA编程 透视表 透视表 函数、图表 函数、图表 基本操作 基本操作

4. 前言——烦恼、困惑与疑问 工作中天天用到Excel,但除了简单的操作,其他技巧怎么也掌握不了.重复的操作耗费大量的时间、精力！ • Excel中如此多的功能、菜单、函数、图表类型、VBA代码，如何做到灵活运用？ • 我对Excel很感兴趣、可不知从何学起？ • 有没有一套有效的、快速的学习Excel技能的方法？ 烦恼 困惑 疑问

5. 前言——成为“高手”的捷径 No.1 积极的心态 “兴趣是最好的老师、压力是前进的动力” 心态 • No.2 正确的方法 • 循序渐进 • 善用资源、学以致用 • 阅读与实践的结合 方法 坚持 No.3持之以恒的努力，每天进步一点点

6. 前言——关于本次培训 解决问题的思路 学习Excel的方法 注重 知识点 20% 实例讲解 80% 采用Excel 2010进行讲解，尽量向低版本兼容 构成 说明

7. 前言——起步之前让我们回顾几个场景 场景1 Excel+计算器 场景2 Excel取代计算器 场景3 单元格表间运算 场景4 Vlookup + sumif 场景6 Excel+数据库 场景5 高级公式+透视表 …...

8. 目录 00 01 02 03 04 05 06 07 前言 操作与技巧 ——原来可以如此简单 函数与公式 ——Excel的精髓之处 数据透视表 ——百变星君 图表 ——秀的是专业、拼的是创意 总结 与作业 4 5 6 3 1 2 • 基础中的基础 • 排序 • 筛选 • 查找与替换 • 定位 • 选择性粘贴 • 效率加倍的快捷键

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. 目录 00 01 02 03 04 05 06 07 前言 操作与技巧 ——原来可以如此简单 函数与公式 ——Excel的精髓之处 数据透视表 ——百变星君 图表 ——秀的是专业、拼的是创意 总结 与作业 4 5 6 3 1 2 • 基础中的进阶 • 单元格格式 • 保护工作表/工作簿 • 名称的运用 • 数据有效性 • 条件格式 • 高级筛选 • 基础中的基础 • 排序 • 筛选 • 查找与替换 • 定位 • 选择性粘贴 • 效率加倍的快捷键

16. 操作与技巧进阶——单元格格式 单元格格式概述（Ctrl+1） 案例1:来浏览一些“千奇百怪”的自定义格式 案例2:如何对手机号码进行分段显示 案例3:如何对不同范围的数值使用不同格式 案例4:如何将自定义格式的显示转换成真实数据

17. 操作与技巧进阶——工作表/工作簿的保护 保护工作表/保护工作簿 案例1:保护工作表基础操作 案例2:如何限定用户只能选择可编辑的区域 案例3:如何在已保护的工作表中运用筛选

18. 操作与技巧进阶——数据有效性 数据有效性的概述 案例1:如何利用数据有效性控制单元格的输入 案例2:如何限制重复值的录入 案例3:用有效性制作二级下拉菜单 案例4:利用有效性圈释不符合要求的数据

19. 操作与技巧进阶——条件格式 条件格式概述 案例1:利用条件格式标注达成率 案例2:用条件格式判断列是否有重复 案例3:用条件格式自动实现间隔底纹 案例4:用条件格式自动为小计总计设定格式 简单介绍2010的条件格式、迷你图

20. 操作与技巧进阶——名称的运用 名称是什么东东？ • 名称：实际上是对Excel“对象”的命名。而所谓对象可以是： • 单元格 • 单元格区域 • 数值、数组常量 • 公式 • 宏表函数

21. 操作与技巧进阶——名称的运用 为什么要使用名称 • 名称的优点： • 使公式、有效性、条件格式等等易于理解，比如用“税率”代替单元格引用. • 简化公式 • 利于安全，易于维护 • 可以在条件格式、有效性中引用其他工作表单元格或常量数组 • 调用Excel4.0宏表函数必须使用名称 • 可以在控件中调用单元格内容 • Excel的几乎任何地方那个都可以使用名称

22. 操作与技巧进阶——名称的运用 关于名称的约定 • 名称不得与单元格地址相同。包括R1C1样式。 • 名称不得使用空格。 • 名称不得以数字开头或单独使用数字。 • Excel识别名称时，不区分大小写。 • 名称是一种特殊形式的“公式”，因此其长度、内部嵌套等同样受到限制，比如内部长度最大16,384个字符。 • 名称所用字符不能超过255个字符。 • 当名称引用单元格区域时，要考虑相对引用和绝对引用。

23. 操作与技巧进阶——名称的运用 定义名称的三种方法 方法1:插入名称 案例2:使用名称框 案例3:根据所选内容创建（批量）

24. 操作与技巧进阶——名称的运用 关于名称的案例 案例1:在名称中使用常量 案例2:在名称中使用函数 案例3:动态名称的运用 案例4:如何一次性删除所有定义的名称

25. 操作技巧进阶——综合案例分析

26. 操作与技巧进阶——高级筛选 高级筛选及举例 参考一份网络上的教程。请看案例

27. 目录 00 01 02 03 04 05 06 07 前言 操作与技巧 ——原来可以如此简单 函数与公式 ——Excel的精髓之处 数据透视表 ——百变星君 图表 ——秀的是专业、拼的是创意 总结 与作业 4 5 6 3 1 2 • 热身运动，现在开始 • Excel运算符及其含义 • 什么是公式、什么是函数 • 如何快速、准确找到适合的函数

28. 函数与公式——热身运动 Excel运算符 让我们一起来看看Excel的常用运算符

29. 函数与公式——热身运动 公式？函数？ 公式：是以等号“=”开头，通过使用运算符将数据、函数等元素按一定顺序连接在一起，从而实现对工作表中的数据执行计算的等式。例如 C1=A1+A2+A3+A4+B1+B2+B3+B4 函数：简而言之，函数就是预先定义好了的公式。比如上例中的公式就可以用函数C1=SUM(A1:B4)代替。 函数不仅可以简化公式，而且具有仅用运算符连接的一般公式所不能替代的功能。例如查找引用，逻辑判断等等。

30. 函数与公式——函数的结钩分析

31. 函数与公式——热身运动 快速选择函数 快速选择、输入函数的方法 通过“公式库”功能区快速选择输入 通过开头字母的提示快速输入 通过插入函数输入 直接输入

32. 目录 00 01 02 03 04 05 06 07 前言 操作与技巧 ——原来可以如此简单 函数与公式 ——Excel的精髓之处 数据透视表 ——百变星君 图表 ——秀的是专业、拼的是创意 总结 与作业 4 5 6 3 1 2 • 热身运动，现在开始 • Excel运算符及其含义 • 什么是公式、什么是函数 • 如何快速、准确找到适合的函数 • 记住三个F吧，他们很棒 • F1,不离不弃的好兄弟 • F4,相对？还是绝对？由你说了算 • F9,一把“庖丁解牛”的宝刀

33. 函数起步——F1,不离不弃的好兄弟 F1，F1 哪里不会点哪里 自学函数的捷径全在这里了 记住：excel本身就是最好的参考书

34. 函数起步——F4,相对？绝对？由你说了算 何为引用，有何影响？ 案例1:三种引用的同时理解 案例2:为什么vlookup会出现错误值 案例3:综合案例

35. 函数起步——F9,一把“庖丁解牛”的宝刀

36. 目录 00 01 02 03 04 05 06 07 前言 操作与技巧 ——原来可以如此简单 函数与公式 ——Excel的精髓之处 数据透视表 ——百变星君 图表 ——秀的是专业、拼的是创意 总结 与作业 4 5 6 3 1 2 • 热身运动，现在开始 • 什么是函数 • 如何快速、准确找到适合的函数 • 兵马未动，逻辑先行 • 记住三个F吧，他们很棒 • F1,不离不弃的好兄弟 • F4,相对？还是绝对？由你说了算 • F9,一把“庖丁解牛”的宝刀 • 函数如此简单 • TRUE vs FALSE • 逻辑判断是函数的灵魂 • 数据汇总统计函数 • 查找引用函数 • 文本处理函数 • 璀璨明珠—Sumproduct

37. 函数如此简单——TRUE vs FALSE Excel的逻辑值只有2个：TRUE FALSE TRUE FALSE是函数的关键，可以这么说：任何函数都是在解决TRUE 还是 FALSE的问题 TRUE FALSE 与运算符进行各种计算时，请牢记：TRUE=1 FALSE=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. 函数如此简单——查找引用类函数 注：查找引用类将采用函数解析+案例的方式 MATCH函数 动态解析 VLOOKUP函数 动态解析 (*办公室函数使用率之最*） 案例1:根据不同的商品不同的订货量计算单价及折扣 案例2:输入工号后可自动显示相关的人事资料（测试题) 案例3:如何处理VLOOKUP函数找不到对应数据产生的错误

44. 函数如此简单——查找引用类函数 INDXE函数 动态解析 OFFSET函数 动态解析 （初次接触比较难理解） 案例4:如何从右往左查询对应的数据（测试题） 案例5:如何根据品牌和规格双条件查找单价 案例6:如何保证新增加的销量自动求和(offset+counta动态引用)

45. 函数如此简单——文本函数 • 我们一起来学习几个最常用的文本处理函数： • LEFT,MID,RIGHT “三个兄弟，本事各不同” • FIND——在文本中找文本 • SUBSTITUTE——替换函数 案例1:判断产品缺陷中是否含某个字符 案例2:不通过分列如何将数据分成2列 案例2:如何查找某字符在字符串中第N次出现的位置

46. SUMPRODUCT——函数中一颗璀璨的明珠 *千万不会要去迷恋Excel帮助文件中对此函数的描述。也许是因为SUMPRODUCT函数能力太过超群，Excel帮助文件都嫉妒了它的才能。^_^ 将乘积的结果汇总! Good！！ 将汇总数相乘？？ 是这样吗？ =SUMPRODUCT((数据1)*(数据2)) SUM PRODUCT =SUMPRODUCT((数据1)*(数据2)*(数据3)*(数据4)……*(数据N)) =SUMPRODUCT((数据1)+(数据2)+(数据3)+(数据4)……+(数据N))

47. SUMPRODUCT——函数中一颗璀璨的明珠 在了解的基本原理后，我们还是通过案例来讲解SUMPRODUCTDE的常规用法： 案例1:起步,让一般统计类函数汗颜~~^_^ 案例2:奔跑,让多条件统计类函数折服~~^_^ Hi，不小心，我们已经打开了统计类“数组公式”的潘多拉盒子。 函数“美丽”（魅力）绽放，你发现了吗？

48. SUMPRODUCT——函数中一颗璀璨的明珠 很遗憾，没有任何事物是完美的，SUMPRODUCT也有自己的缺憾： 1）在用我们今天所讲的 =SUMPRODUCT((数据1)*(数据2))的方式时，计算项，求和时出现文本、错误值、逻辑值，将会出错。 2）用SUMPRODUCT，计算很大的表、很复杂的逻辑时，运行速度会比较慢。因为该函数内部执行的是数组运算，会相对耗时。（当然这个耗时只是相对而言，比如像对 SUMIFS，DSUM 等等）

49. 我和函数的约定——日积月累、学以致用 • 函数是Excel中最核心的部分，是技巧、图表、透视表，VBA的基本功。同时函数本身内容也相当丰富，值得大家不断的去学习、思考如何让工作更轻松。 • 同时： • 由于函数内容太多， 不需要大家把每个函数都去掌握 • 由于函数很有深度，很多函数都值得反复的去学习、去发现 • 由于许多函数在理解上比较困难，不可能短期内达到某个高度 • 因此，建议大家“日积月累、学以致用” Lookup 查找之王 Text 文本之王

50. 目录 00 01 02 03 04 05 06 07 前言 操作与技巧 ——原来可以如此简单 函数与公式 ——Excel的精髓之处 数据透视表 ——百变星君 图表 ——秀的是专业、拼的是创意 总结 与作业 4 5 6 3 1 2 • 认识数据透视表 • 创建第一个数据透视表 • 无拘无束的数据透视表 • 如虎添翼的计算字段、计算项 • 动态数据透视表 • 介绍多重合并和外部引用创建透视表＊ • 介绍数据透视图＊