Excel实战培训

1、Excel实战技巧培训Jim.ZhangExcelExcel 实战技巧培训实战技巧培训目录2前言操作与技巧原来可以如此简单数据透视表百变星君函数与公式Excel的精髓之处12430001020304050607图表专业的我也可以做到56总结与作业ExcelExcel 实战技巧培训实战技巧培训前言Excel有些什么3VBA编程VBA编程透视表透视表函数、图表函数、图表基本操作基本操作ExcelExcel 实战技巧培训实战技巧培训前言烦恼、困惑与疑问4烦恼工作中天天用到Excel,但除了简单的操作,其他技巧怎么也掌握不了.重复的操作耗费大量的时间、精力！困惑Excel中如此多的功能、菜单、函数、图

2、表类型、VBA代码，如何做到灵活运用？疑问我对Excel很感兴趣、可不知从何学起？有没有一套有效的、快速的学习Excel技能的方法？ExcelExcel 实战技巧培训实战技巧培训前言成为“高手”的捷径5心态方法坚持No.1 积极的心态“兴趣是最好的老师、压力是前进的动力”No.2 正确的方法循序渐进善用资源、学以致用阅读与实践的结合No.3持之以恒的努力，每天进步一点点ExcelExcel 实战技巧培训实战技巧培训前言关于本次培训6解决问题的思路学习Excel的方法注重知识点 20%实例讲解 80%构成采用Excel 2010进行讲解，尽量向低版本兼容说明ExcelExcel 实战技巧培训实战

3、技巧培训前言起步之前让我们回顾几个场景7场景1Excel+计算器场景2Excel取代计算器场景3单元格表间运算场景4Vlookup + sumif场景6Excel+数据库场景5高级公式+透视表.ExcelExcel 实战技巧培训实战技巧培训目录8前言操作与技巧原来可以如此简单数据透视表百变星君函数与公式Excel的精髓之处12430001020304050607图表秀的是专业、拼的是创意56总结与作业l 基础中的基础 排序 筛选 查找与替换 定位 选择性粘贴 效率加倍的快捷键ExcelExcel 实战技巧培训实战技巧培训操作与技巧基础排序9 排序的基本操作案例1:如何按汉字笔画排序案例2:如何

4、用排序制作工资条案例3:如何按行排序案例4:如何对合并单元格相邻的数据区域排序案例5:如何按单元格颜色排序（2007以上版本)ExcelExcel 实战技巧培训实战技巧培训操作与技巧基础筛选（自动筛选)10 筛选的基本操作案例1:如何对双行标题列表进行筛选案例2:筛选中数据区域的注意事项案例3:如何进行反向选择案例4:如何不增加辅助列对日期按星期筛选ExcelExcel 实战技巧培训实战技巧培训操作与技巧基础查找与替换11 查找与替换基本操作案例1:如何清除或修改特定格式的单元格内容案例2:如何查找批注中的内容案例3:如何在整个工作簿查找案例4:如何删除工作表中的通配符和特定字符ExcelEx

5、cel 实战技巧培训实战技巧培训操作与技巧基础定位12 定位的概念和基本操作案例1:如何披量清除工作表上的各种对象案例2:如何运用定位行列差异单元格案例3:如何利用定位将数据填充完整（测试题1)ExcelExcel 实战技巧培训实战技巧培训操作与技巧基础选择性粘贴13 选择性粘贴的基本操作和意义案例1:如何将公式转为数值案例2:如何直接对某个区域进行加减乘除运算案例3:如何只复制单元格中的有效性设置案例4:关于转置功能的运用案例5:关于跳过空单元格ExcelExcel 实战技巧培训实战技巧培训操作与技巧基础效率加倍的快捷键14 何为有用的快捷键？需要自己去摸索总结，本次培训仅仅列举了一小部分供

6、大家参考。令效率加倍的快捷键举例ExcelExcel 实战技巧培训实战技巧培训目录15前言操作与技巧原来可以如此简单数据透视表百变星君函数与公式Excel的精髓之处12430001020304050607图表秀的是专业、拼的是创意56总结与作业l 基础中的基础 排序 筛选 查找与替换 定位 选择性粘贴 效率加倍的快捷键l 基础中的进阶 单元格格式 保护工作表/工作簿 名称的运用 数据有效性 条件格式 高级筛选ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶单元格格式16 单元格格式概述（Ctrl+1）案例1:来浏览一些“千奇百怪”的自定义格式案例2:如何对手机号码进行分段显示案例3

7、:如何对不同范围的数值使用不同格式案例4:如何将自定义格式的显示转换成真实数据ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶工作表/工作簿的保护17 保护工作表/保护工作簿案例1:保护工作表基础操作案例2:如何限定用户只能选择可编辑的区域案例3:如何在已保护的工作表中运用筛选ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶数据有效性18 数据有效性的概述案例1:如何利用数据有效性控制单元格的输入案例2:如何限制重复值的录入案例3:用有效性制作二级下拉菜单案例4:利用有效性圈释不符合要求的数据ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶条件格式19 条件

8、格式概述案例1:利用条件格式标注达成率案例2:用条件格式判断列是否有重复案例3:用条件格式自动实现间隔底纹案例4:用条件格式自动为小计总计设定格式简单介绍2010的条件格式、迷你图ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶名称的运用20名称是什么东东？名称：实际上是对Excel“对象”的命名。而所谓对象可以是：单元格单元格区域数值、数组常量公式宏表函数ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶名称的运用21 为什么要使用名称名称的优点：使公式、有效性、条件格式等等易于理解，比如用“税率”代替单元格引用.简化公式利于安全，易于维护可以在条件格式、有效性中引用其

9、他工作表单元格或常量数组调用Excel4.0宏表函数必须使用名称可以在控件中调用单元格内容Excel的几乎任何地方那个都可以使用名称ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶名称的运用22 关于名称的约定名称不得与单元格地址相同。包括R1C1样式。名称不得使用空格。名称不得以数字开头或单独使用数字。Excel识别名称时，不区分大小写。名称是一种特殊形式的“公式”，因此其长度、内部嵌套等同样受到限制，比如内部长度最大16,384个字符。名称所用字符不能超过255个字符。当名称引用单元格区域时，要考虑相对引用和绝对引用。ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶

10、名称的运用23 定义名称的三种方法方法1:插入名称案例2:使用名称框案例3:根据所选内容创建（批量）ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶名称的运用24 关于名称的案例案例1:在名称中使用常量案例2:在名称中使用函数案例3:动态名称的运用案例4:如何一次性删除所有定义的名称ExcelExcel 实战技巧培训实战技巧培训操作技巧进阶综合案例分析25一份市场活动赠品申请表的编制案例一份市场活动赠品申请表的编制案例单纯的表格VS名称+数据有效性+条件格式+工作表保护的表格ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶高级筛选26 高级筛选及举例参考一份网络上的教程

11、。请看案例ExcelExcel 实战技巧培训实战技巧培训目录27前言操作与技巧原来可以如此简单数据透视表百变星君函数与公式Excel的精髓之处12430001020304050607图表秀的是专业、拼的是创意56总结与作业l 热身运动，现在开始 Excel运算符及其含义 什么是公式、什么是函数 如何快速、准确找到适合的函数ExcelExcel 实战技巧培训实战技巧培训函数与公式热身运动 Excel运算符28 让我们一起来看看Excel的常用运算符ExcelExcel 实战技巧培训实战技巧培训函数与公式热身运动 公式？函数？29 公式：是以等号“=”开头，通过使用运算符将数据、函数等元素按一定顺

12、序连接在一起，从而实现对工作表中的数据执行计算的等式。例如 C1=A1+A2+A3+A4+B1+B2+B3+B4函数：简而言之，函数就是预先定义好了的公式。比如上例中的公式就可以用函数C1=SUM(A1:B4)代替。 函数不仅可以简化公式，而且具有仅用运算符连接的一般公式所不能替代的功能。例如查找引用，逻辑判断等等。ExcelExcel 实战技巧培训实战技巧培训函数与公式函数的结钩分析30ExcelExcel 实战技巧培训实战技巧培训函数与公式热身运动 快速选择函数31 快速选择、输入函数的方法通过“公式库”功能区快速选择输入通过开头字母的提示快速输入通过插入函数输入直接输入ExcelExce

13、l 实战技巧培训实战技巧培训目录32前言操作与技巧原来可以如此简单数据透视表百变星君函数与公式Excel的精髓之处12430001020304050607图表秀的是专业、拼的是创意56总结与作业l 热身运动，现在开始 Excel运算符及其含义 什么是公式、什么是函数 如何快速、准确找到适合的函数l 记住三个F吧，他们很棒 F1,不离不弃的好兄弟 F4,相对？还是绝对？由你说了算 F9,一把“庖丁解牛”的宝刀ExcelExcel 实战技巧培训实战技巧培训函数起步F1,不离不弃的好兄弟33自学函数的捷径全在这里了记住：excel本身就是最好的参考书F1，F1哪里不会点哪里ExcelExcel 实战

14、技巧培训实战技巧培训函数起步F4,相对？绝对？由你说了算34 何为引用，有何影响？案例1:三种引用的同时理解案例2:为什么vlookup会出现错误值案例3:综合案例ExcelExcel 实战技巧培训实战技巧培训函数起步F9,一把“庖丁解牛”的宝刀35F9应用举例应用举例解析统计函数:IF+COUNTIF+SUMIF 的综合应用 解析文本函数:FIND+MID的综合应用解析复杂数组公式:其实就是这么简单ExcelExcel 实战技巧培训实战技巧培训目录36前言操作与技巧原来可以如此简单数据透视表百变星君函数与公式Excel的精髓之处12430001020304050607图表秀的是专业、拼的是创

15、意56总结与作业l 热身运动，现在开始 什么是函数 如何快速、准确找到适合的函数 兵马未动，逻辑先行l 记住三个F吧，他们很棒 F1,不离不弃的好兄弟 F4,相对？还是绝对？由你说了算 F9,一把“庖丁解牛”的宝刀l 函数如此简单 TRUE vs FALSE 逻辑判断是函数的灵魂 数据汇总统计函数 查找引用函数 文本处理函数 璀璨明珠SumproductExcelExcel 实战技巧培训实战技巧培训函数如此简单TRUE vs FALSE37 Excel的逻辑值只有2个：TRUE FALSE TRUE FALSE是函数的关键，可以这么说：任何函数都是在解决TRUE 还是 FALSE的问题 TRU

16、E FALSE 与运算符进行各种计算时，请牢记：TRUE=1 FALSE=0，请务必牢记 通过示例文件，我们来详细解释一下TRUE 和 FALSEExcelExcel 实战技巧培训实战技巧培训函数如此简单逻辑判断是函数的灵魂38串联开关等同于逻辑函数 AND,即多个条件同时满足： 比如TRUE*TRUE并联开关等同于逻辑函数 OR,即只要满足一个条件： 比如TRUE+FALSEExcelExcel 实战技巧培训实战技巧培训函数如此简单逻辑判断是函数的灵魂39 通过案例，轻松掌握逻辑运算案例1:是否能申请经济适用房案例2:如何根据性别和职务计算补贴案例3:多层判断彻底理清逻辑关系ExcelExc

17、el 实战技巧培训实战技巧培训函数如此简单数据汇总统计类函数40 注：数据汇总统计类将全部采用案例形式进行讲解l 汇总求和类函数，关键词:sum、sumif、sumifs、subtotal案例1:汇总多个表格中相同单元格的数据（sum)案例2:如何汇总某品名的销售数量 （sumif)案例3:深入理解sumif函数的“相对位置”案例4:如何汇总月薪在2000-4000元的工资总额（sumifs)案例5:如何忽略隐藏的行汇总数据(subtotal)案例6:如何始终生成连续的商品序号(subtotal)ExcelExcel 实战技巧培训实战技巧培训函数如此简单数据汇总统计类函数41l 数据计数类函数

18、，关键词:count,countif,countifs案例1:如何统计成绩表中的及格人数(countif,countifs)案例2:如何统计产品规格中带“*”号的产品个数(countif)案例3:如何统计35岁以上有房有车的人员名单案例4:如何统计不重复的数据个数ExcelExcel 实战技巧培训实战技巧培训函数如此简单数据汇总统计类函数42l 极值均值类函数，关键词:min,max,median,large,small,average案例1:如何设置提成奖金的上下限（max,min,mdeian)案例2:如何不计重复返回一组数据中次大，次小值(large,small)案例3:如何统计前十名成

19、绩的平均分(average,large)案例4:如何根据条件计算平均分(averageif,averageifs)ExcelExcel 实战技巧培训实战技巧培训函数如此简单查找引用类函数43 注：查找引用类将采用函数解析+案例的方式VLOOKUP函数动态解析(*办公室函数使用率之最*）MATCH函数动态解析案例1:根据不同的商品不同的订货量计算单价及折扣案例2:输入工号后可自动显示相关的人事资料（测试题)案例3:如何处理VLOOKUP函数找不到对应数据产生的错误ExcelExcel 实战技巧培训实战技巧培训44函数如此简单查找引用类函数INDXE函数动态解析案例4:如何从右往左查询对应的数据（

20、测试题）案例5:如何根据品牌和规格双条件查找单价OFFSET函数动态解析（初次接触比较难理解）案例6:如何保证新增加的销量自动求和(offset+counta动态引用)ExcelExcel 实战技巧培训实战技巧培训函数如此简单文本函数45l 我们一起来学习几个最常用的文本处理函数： LEFT,MID,RIGHT “三个兄弟，本事各不同” FIND在文本中找文本 SUBSTITUTE替换函数案例1:判断产品缺陷中是否含某个字符案例2:不通过分列如何将数据分成2列案例2:如何查找某字符在字符串中第N次出现的位置ExcelExcel 实战技巧培训实战技巧培训SUMPRODUCT函数中一颗璀璨的明珠4

21、6*千万不会要去迷恋Excel帮助文件中对此函数的描述。也许是因为SUMPRODUCT函数能力太过超群，Excel帮助文件都嫉妒了它的才能。_SUMPRODUCT=SUMPRODUCT(数据1)*(数据2)*(数据3)*(数据4)*(数据N)=SUMPRODUCT(数据1)+(数据2)+(数据3)+(数据4)+(数据N)将汇总数相乘？是这样吗？将乘积的结果汇总!Good！=SUMPRODUCT(数据1)*(数据2)ExcelExcel 实战技巧培训实战技巧培训SUMPRODUCT函数中一颗璀璨的明珠47 在了解的基本原理后，我们还是通过案例来讲解SUMPRODUCTDE的常规用法：案例1:起步

22、,让一般统计类函数汗颜_案例2:奔跑,让多条件统计类函数折服_ Hi，不小心，我们已经打开了统计类“数组公式”的潘多拉盒子。函数“美丽”（魅力）绽放，你发现了吗？ExcelExcel 实战技巧培训实战技巧培训48SUMPRODUCT函数中一颗璀璨的明珠很遗憾，没有任何事物是完美的，SUMPRODUCT也有自己的缺憾：1）在用我们今天所讲的 =SUMPRODUCT(数据1)*(数据2)的方式时，计算项，求和时出现文本、错误值、逻辑值，将会出错。2）用SUMPRODUCT，计算很大的表、很复杂的逻辑时，运行速度会比较慢。因为该函数内部执行的是数组运算，会相对耗时。（当然这个耗时只是相对而言，比如像

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

24、据透视表 如虎添翼的计算字段、计算项 动态数据透视表 介绍多重合并和外部引用创建透视表 介绍数据透视图目录50前言操作与技巧原来可以如此简单数据透视表百变星君函数与公式Excel的精髓之处12430001020304050607图表秀的是专业、拼的是创意56总结与作业ExcelExcel 实战技巧培训实战技巧培训数据透视图数据透视图数据透视表数据透视表认识数据透视表51Excel数据清单数据库文本文件其他关系型数据文件Excel数据透视表引擎数据透视表数据透视图数据透视表ExcelExcel 实战技巧培训实战技巧培训数据透视表了解数据透视表的结构52ExcelExcel 实战技巧培训实战技巧培

25、训数据透视表认识数据透视表结构53报表筛选区域行区域列区域数值区域ExcelExcel 实战技巧培训实战技巧培训数据透视表创建一个透视表54选择区域中任意单元格插入透视表选择列、行、值ExcelExcel 实战技巧培训实战技巧培训数据透视表无拘无束的数据透视表(1)55筛选字段、列、行字段任意拖拽的无拘无束字段汇总方式的无拘无束字段显示方式的无拘无束修改数据透视表的字段名称灵活处理错误值和空白单元格ExcelExcel 实战技巧培训实战技巧培训数据透视表无拘无束的数据透视表(2)56谁先谁后由决定，展示字段先后顺序任意指定数据透视表的排序数据透视表的筛选数据透视表分组，组合方式由我定更改数据源和移动数据透视表ExcelExcel 实战技巧培训实战技巧培训数据透视表如虎添翼的计算字段和计算项57首先理解计算字段、计算项案例：用计算项计算销售提成案例：用计算项计算年销售差异ExcelExcel 实战技巧培训实战技巧培训数据透视表动态创建数据透视表58方法：定义名称法创建动态的数据透视表方法：使用表功能创建动态的数据透视表方法：导入外部数据创建动态数据透视表ExcelExcel 实战技巧培训实战技巧培训数据透视表多重合并、外部引用、透视图59多重合并的介绍数