《教育用Excel实战培训.pptx》由会员分享，可在线阅读，更多相关《教育用Excel实战培训.pptx（69页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Excel实战技巧培训ExcelExcel 实战技巧培训实战技巧培训目录2前言操作与技巧原来可以如此简单数据透视表百变星君函数与公式Excel的精髓之处12430001020304050607图表专业的我也可以做到56总结与作业ExcelExcel 实战技巧培训实战技巧培训前言Excel有些什么3VBA编程VBA编程透视表透视表函数、图表函数、图表基本操作基本操作ExcelExcel 实战技巧培训实战技巧培训前言烦恼、困惑与疑问4烦恼工作中天天用到Excel,但除了简单的操作,其他技巧怎么也掌握不了.重复的操作耗费大量的时间、精力！困惑Excel中如此多的功能、菜单、函数、图表类型、VBA代码
2、，如何做到灵活运用？疑问我对Excel很感兴趣、可不知从何学起？有没有一套有效的、快速的学习Excel技能的方法？ExcelExcel 实战技巧培训实战技巧培训前言成为“高手”的捷径5心态方法坚持No.1 积极极的心的心态“兴趣是最好的老趣是最好的老师、压力是前力是前进的的动力力”No.2 正确的方法正确的方法循序循序渐进善用善用资源、学以致用源、学以致用阅读与与实践的践的结合合No.3持之以恒的持之以恒的努力，每天努力，每天进步一点点步一点点ExcelExcel 实战技巧培训实战技巧培训前言关于本次培训6解决解决问题的思路的思路学学习Excel的方法的方法注重知知识点点 20%实例例讲解解
3、80%构成采用采用Excel 2010进行行讲解，尽量向解，尽量向低版本兼容低版本兼容说明ExcelExcel 实战技巧培训实战技巧培训前言起步之前让我们回顾几个场景7场景1Excel+计算器场景2Excel取代计算器场景3单元格表间运算场景4Vlookup+sumif场景6Excel+数据库场景5高级公式+透视表.ExcelExcel 实战技巧培训实战技巧培训目录8前言操作与技巧原来可以如此简单数据透视表百变星君函数与公式Excel的精髓之处12430001020304050607图表秀的是专业、拼的是创意56总结与作业l基础中的基础排序筛选查找与替换定位选择性粘贴效率加倍的快捷键Excel
4、Excel 实战技巧培训实战技巧培训操作与技巧基础排序9 排序的基本操作排序的基本操作案例案例1:如何按如何按汉字笔画排序字笔画排序案例案例2:如何多重排序如何多重排序案例案例3:如何按行排序如何按行排序案例案例4:辅助列排序助列排序案例案例5:如何按如何按单元格元格颜色排序（色排序（2007以上版本以上版本)ExcelExcel 实战技巧培训实战技巧培训操作与技巧基础筛选（自动筛选)10 筛选的基本操作的基本操作案例案例1:自定自定义筛选案例案例2:筛选中数据区域的注意事中数据区域的注意事项案例案例3:如何如何进行反向行反向选择ExcelExcel 实战技巧培训实战技巧培训操作与技巧基础查找
5、与替换11 查找与替找与替换基本操作基本操作案例案例1:查找内容找内容案例案例2:查找格式找格式案例案例3:如何在整个工作簿如何在整个工作簿查找找案例案例4:如何如何删除工作表中的通配符除工作表中的通配符和特定字符和特定字符ExcelExcel 实战技巧培训实战技巧培训操作与技巧基础定位12 定位的概念和基本操作定位的概念和基本操作案例案例1:如何批量清除工作表上的各种如何批量清除工作表上的各种对象象案例案例2:如何如何运用运用定位定位查找找错误案例案例3:如何利用定位将数据填充完整如何利用定位将数据填充完整ExcelExcel 实战技巧培训实战技巧培训操作与技巧基础选择性粘贴13 选择性粘性
6、粘贴的基本操作和意的基本操作和意义案例案例1:如何将公式如何将公式转为数数值案例案例2:如何直接如何直接对某个区域某个区域进行加减乘除运算行加减乘除运算案例案例3:如何只复制如何只复制单元格中的有效性元格中的有效性设置置案例案例4:关于关于转置置功能功能的运用的运用案例案例5:关于跳关于跳过空空单元格元格ExcelExcel 实战技巧培训实战技巧培训操作与技巧基础效率加倍的快捷键14 何何为有用的快捷有用的快捷键？需要自己去摸？需要自己去摸索索总结，本次培，本次培训仅仅列列举了一小部了一小部分供大家参考。分供大家参考。令效率加倍的快捷令效率加倍的快捷键举例例ExcelExcel 实战技巧培训实
7、战技巧培训目录15前言操作与技巧原来可以如此简单数据透视表百变星君函数与公式Excel的精髓之处12430001020304050607图表秀的是专业、拼的是创意56总结与作业l基础中的基础排序筛选查找与替换定位选择性粘贴效率加倍的快捷键l基础中的进阶单元格格式保护工作表/工作簿名称的运用数据有效性条件格式高级筛选ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶单元格格式16 单元格格式概述（元格格式概述（Ctrl+1）案例案例1:来来浏览一些一些“千奇百怪千奇百怪”的自定的自定义格式格式案例案例2:如何如何对手机号手机号码进行分段行分段显示示案例案例3:如何如何对不同范不同范围的
8、数的数值使用不同格式使用不同格式案例案例4:如何将自定如何将自定义格式的格式的显示示转换成真成真实数据数据ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶工作表/工作簿的保护17 保保护工作表工作表/保保护工作簿工作簿案例案例1:保保护工作表基工作表基础操作操作案例案例2:如何限定用如何限定用户只能只能选择可可编辑的区域的区域案例案例3:如何在已保如何在已保护的工作表中运用的工作表中运用筛选ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶数据有效性18 数据有效性的概述数据有效性的概述案例案例1:如何利用数据有效性控制如何利用数据有效性控制单元格的元格的输入入案例案例2
9、:如何限制重复如何限制重复值的的录入入案例案例3:用有效性制作二用有效性制作二级下拉菜下拉菜单ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶条件格式19 条件格式概述条件格式概述案例案例1:利用利用条件格式条件格式标注达成率注达成率案例案例2:用用条件格式判断列是否有重复条件格式判断列是否有重复案例案例3:用条件格式自用条件格式自动实现间隔隔底底纹简单介介绍2010的条件格式、迷你的条件格式、迷你图ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶名称的运用20名称是什么名称是什么东东？名称：实际上是对Excel“对象”的命名。而所谓对象可以是：单元格单元格区域数值、数
10、组常量公式宏表函数ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶名称的运用21 为什么要使用名称什么要使用名称名称的优点：使公式、有效性、条件格式等等易于理解，比如用“税率”代替单元格引用.简化公式利于安全，易于维护可以在条件格式、有效性中引用其他工作表单元格或常量数组调用Excel4.0宏表函数必须使用名称可以在控件中调用单元格内容Excel的几乎任何地方那个都可以使用名称ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶名称的运用22 关于名称的关于名称的约定定名称不得与单元格地址相同。包括R1C1样式。名称不得使用空格。名称不得以数字开头或单独使用数字。Excel
11、识别名称时，不区分大小写。名称是一种特殊形式的“公式”，因此其长度、内部嵌套等同样受到限制，比如内部长度最大16,384个字符。名称所用字符不能超过255个字符。当名称引用单元格区域时，要考虑相对引用和绝对引用。ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶名称的运用23 定定义名称的三种方法名称的三种方法方法方法1:插入名称插入名称案例案例2:使用名称框使用名称框案例案例3:根据所根据所选内容内容创建（批量）建（批量）ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶名称的运用24 关于名称的案例关于名称的案例案例案例1:在名称中使用常量在名称中使用常量案例案例2:在
12、名称在名称中中使用使用函数函数案例案例3:动态名称的运用名称的运用案例案例4:如何一次性如何一次性删除所有定除所有定义的名称的名称ExcelExcel 实战技巧培训实战技巧培训操作技巧进阶综合案例分析25一份市场活动赠品申请表的编制案例一份市场活动赠品申请表的编制案例单纯的表格VS名称+数据有效性+条件格式+工作表保护的表格ExcelExcel 实战技巧培训实战技巧培训操作与技巧进阶练习26 练习题目目1请看案例ExcelExcel 实战技巧培训实战技巧培训目录27前言操作与技巧原来可以如此简单数据透视表百变星君函数与公式Excel的精髓之处12430001020304050607图表秀的是专
13、业、拼的是创意56总结与作业l热身运动，现在开始Excel运算符及其含义什么是公式、什么是函数如何快速、准确找到适合的函数ExcelExcel 实战技巧培训实战技巧培训函数与公式热身运动 Excel运算符28 让我我们一起来看看一起来看看Excel的常用运算符的常用运算符ExcelExcel 实战技巧培训实战技巧培训函数与公式热身运动 公式？函数？29 公式：是以等号公式：是以等号“=”开开头，通，通过使用运算符将数据、函数等元素按使用运算符将数据、函数等元素按一定一定顺序序连接在一起，从而接在一起，从而实现对工作表中的数据工作表中的数据执行行计算的等式。算的等式。例如例如 C1=A1+A2+
14、A3+A4+B1+B2+B3+B4函数函数：简而言之，函数就是而言之，函数就是预先定先定义好了的公式。好了的公式。比如上例中的公式比如上例中的公式就可以用函数就可以用函数C1=SUM(A1:B4)代替。代替。函数不函数不仅可以可以简化公式，而且具有化公式，而且具有仅用运算符用运算符连接的一般公式接的一般公式所不能替代的功能。例如所不能替代的功能。例如查找引用，找引用，逻辑判断等等。判断等等。ExcelExcel 实战技巧培训实战技巧培训函数与公式函数的结钩分析30ExcelExcel 实战技巧培训实战技巧培训函数与公式热身运动 快速选择函数31 快速快速选择、输入函数的方法入函数的方法通通过“
15、公式公式库”功能区快速功能区快速选择输入入通通过开开头字母的提示快速字母的提示快速输入入通通过插入函数插入函数输入入直接直接输入入ExcelExcel 实战技巧培训实战技巧培训目录32前言操作与技巧原来可以如此简单数据透视表百变星君函数与公式Excel的精髓之处12430001020304050607图表秀的是专业、拼的是创意56总结与作业l热身运动，现在开始Excel运算符及其含义什么是公式、什么是函数如何快速、准确找到适合的函数l记住三个F吧，他们很棒F1,不离不弃的好兄弟F4,相对？还是绝对？由你说了算F9,一把“庖丁解牛”的宝刀ExcelExcel 实战技巧培训实战技巧培训函数起步F1
16、,不离不弃的好兄弟33自学函数的捷径全在这里了记住：excel本身就是最好的参考书F1，F1哪里不会点哪里ExcelExcel 实战技巧培训实战技巧培训函数起步F4,相对？绝对？由你说了算34 何何为引用，有何影响？引用，有何影响？案例案例1:三三种引用的同种引用的同时理解理解案例案例2:引用位置偏移会出引用位置偏移会出现错误数据数据案例案例3:综合案例合案例ExcelExcel 实战技巧培训实战技巧培训函数起步F9,一把“庖丁解牛”的宝刀35F9应用举例应用举例解析统计函数:IF+COUNTIF+SUMIF 的综合应用 解析文本函数:FIND+MID的综合应用解析复杂数组公式:其实就是这么简
17、单ExcelExcel 实战技巧培训实战技巧培训目录36前言操作与技巧原来可以如此简单数据透视表百变星君函数与公式Excel的精髓之处12430001020304050607图表秀的是专业、拼的是创意56总结与作业l热身运动，现在开始什么是函数如何快速、准确找到适合的函数兵马未动，逻辑先行l记住三个F吧，他们很棒F1,不离不弃的好兄弟F4,相对？还是绝对？由你说了算F9,一把“庖丁解牛”的宝刀l函数如此简单TRUE vs FALSE逻辑判断是函数的灵魂数据汇总统计函数查找引用函数文本处理函数璀璨明珠SumproductExcelExcel 实战技巧培训实战技巧培训函数如此简单TRUE vs F
18、ALSE37 Excel的的逻辑值只有只有2个：个：TRUE FALSE TRUE FALSE是函数的关是函数的关键，可以，可以这么么说：任何：任何函数都是在解决函数都是在解决TRUE 还是是 FALSE的的问题 TRUE FALSE 与运算与运算符符进行各种行各种计算算时，请牢牢记：TRUE=1 FALSE=0，请务必牢必牢记 通通过示例文件，我示例文件，我们来来详细解解释一下一下TRUE 和和 FALSEExcelExcel 实战技巧培训实战技巧培训函数如此简单逻辑判断是函数的灵魂38串串联开关等同于开关等同于逻辑函数函数 AND,即即多多个条个条件同件同时满足：足：比如比如TRUE*TR
19、UE并并联开关等同于开关等同于逻辑函数函数 OR,即只要即只要满足一个条件：足一个条件：比如比如TRUE+FALSEExcelExcel 实战技巧培训实战技巧培训函数如此简单逻辑判断是函数的灵魂39 通通过案例，案例，轻松掌握松掌握逻辑运算运算案例案例1:是否是否能申能申请经济适用房适用房案例案例2:如何如何根据性根据性别和和职务计算算补贴案例案例3:多多层判断判断彻底理清底理清逻辑关系关系ExcelExcel 实战技巧培训实战技巧培训函数如此简单数据汇总统计类函数40 注：数据注：数据汇总统计类将全部采用案例形式将全部采用案例形式进行行讲解解l 汇总求和求和类函数，关函数，关键词:sum、s
20、umif、sumifs、subtotal案例案例1:汇总多个表格中相同多个表格中相同单元格的数据（元格的数据（sum)案例案例2:如何如何汇总某某品名的品名的销售数量售数量（sumif)案例案例3:深入理解深入理解sumif函数的函数的“相相对位置位置”案例案例4:如何如何汇总月薪在月薪在2000-4000元的工元的工资总额（sumifs)案例5:如何忽略隐藏的行汇总数据(subtotal)案例6:如何始终生成连续的商品序号(subtotal)ExcelExcel 实战技巧培训实战技巧培训函数如此简单数据汇总统计类函数41l 数据数据计数数类函数，关函数，关键词:count,countif,c
21、ountifs案例案例1:如何如何统计成成绩表中的及格人数表中的及格人数(countif,countifs)案例案例2:如何如何统计产品品规格中格中带“*”号的号的产品品个数个数(countif)案例案例3:如何如何统计35岁以上有房有以上有房有车的人的人员名名单案例案例4:如何如何统计不重复的数据个数不重复的数据个数ExcelExcel 实战技巧培训实战技巧培训函数如此简单数据汇总统计类函数42l 极极值均均值类函数，关函数，关键词:min,max,median,large,small,average案例案例1:如何如何设置提成置提成奖金的上下限（金的上下限（max,min,mdeian)案
22、例案例2:如何不如何不计重复返回一重复返回一组数据中次大，次小数据中次大，次小值(large,small)ExcelExcel 实战技巧培训实战技巧培训函数如此简单查找引用类函数43 注：注：查找引用找引用类将采用函数解析将采用函数解析+案例的方式案例的方式VLOOKUP函数动态解析(*办公室函数使用率之最*）MATCH函数动态解析案例案例1:根据不同的商品不同的根据不同的商品不同的订货量量计算算单价及折扣价及折扣案例案例2:输入工入工号后可自号后可自动显示相关示相关的人事的人事资料（料（测试题)案例案例3:如何如何处理理VLOOKUP函数找不到函数找不到对应数据数据产生的生的错误ExcelE
23、xcel 实战技巧培训实战技巧培训44函数如此简单查找引用类函数INDXE函数动态解析案例案例4:如何从右往左如何从右往左查询对应的数据（的数据（测试题）案例案例5:如何如何根据品牌和根据品牌和规格双条件格双条件查找找单价价OFFSET函数动态解析案例案例6:如何如何保保证新增加的新增加的销量自量自动求和求和(offset+counta动态引用引用)ExcelExcel 实战技巧培训实战技巧培训函数如此简单文本函数45l 我我们一起来一起来学学习几个最常用的文本几个最常用的文本处理函数：理函数：LEFT,MID,RIGHT “三三个个兄弟，本事各不同兄弟，本事各不同”FIND在文本中找文本在文
24、本中找文本SUBSTITUTE替替换函数函数案例案例1:判断判断产品缺陷中是否含某个字符品缺陷中是否含某个字符案例案例2:不通不通过分列如何将数据分成分列如何将数据分成2列列案例案例2:如何如何查找某字符在字符串中第找某字符在字符串中第N次出次出现的位置的位置ExcelExcel 实战技巧培训实战技巧培训SUMPRODUCT函数中一颗璀璨的明珠46*千万不会要去迷恋千万不会要去迷恋Excel帮助文件中帮助文件中对此函数的描述。也此函数的描述。也许是因是因为SUMPRODUCT函数能力太函数能力太过超群，超群，Excel帮助文件都嫉妒了它的才能。帮助文件都嫉妒了它的才能。_SUMPRODUCT=
25、SUMPRODUCT(数据1)*(数据2)*(数据3)*(数据4)*(数据N)=SUMPRODUCT(数据1)+(数据2)+(数据3)+(数据4)+(数据N)将汇总数相乘？是这样吗？将乘积的结果汇总!Good！=SUMPRODUCT(数据1)*(数据2)ExcelExcel 实战技巧培训实战技巧培训SUMPRODUCT函数中一颗璀璨的明珠47 在了解的基本原理后，我在了解的基本原理后，我们还是通是通过案例来案例来讲解解SUMPRODUCTDE的常的常规用法用法：案例案例1:起步起步,让一般一般统计类函数汗函数汗颜_案例案例2:奔跑奔跑,让多条件多条件统计类函数折服函数折服_ Hi，不小心，我，
26、不小心，我们已已经打开了打开了统计类“数数组公式公式”的潘多拉盒子。的潘多拉盒子。函数函数“美美丽”（魅力）（魅力）绽放，你放，你发现了了吗？ExcelExcel 实战技巧培训实战技巧培训我和函数的约定日积月累、学以致用48函数是函数是Excel中最核心的部分，是技巧、中最核心的部分，是技巧、图表、透表、透视表，表，VBA的基本功。的基本功。同同时函数本身内容也相当丰富，函数本身内容也相当丰富，值得大家不断的去学得大家不断的去学习、思考如何、思考如何让工作更工作更轻松。松。同同时：l由于函数内容太多，由于函数内容太多，不需要大家把每个函数都去掌握不需要大家把每个函数都去掌握l由于函数很有深度，
27、很多函数都由于函数很有深度，很多函数都值得反复的去学得反复的去学习、去、去发现l由于由于许多函数在理解上比多函数在理解上比较困困难，不可能短期内达到某个，不可能短期内达到某个高度高度因此，建因此，建议大家大家“日日积月累、学以致用月累、学以致用”Lookup查找之王找之王Text文本之王文本之王ExcelExcel 实战技巧培训实战技巧培训认识数据透视表创建第一个数据透视表无拘无束的数据透视表如虎添翼的计算字段、计算项动态数据透视表介绍多重合并和外部引用创建透视表介绍数据透视图目录49前言操作与技巧原来可以如此简单数据透视表百变星君函数与公式Excel的精髓之处1243000102030405
28、0607图表秀的是专业、拼的是创意56总结与作业ExcelExcel 实战技巧培训实战技巧培训数据透视图数据透视图数据透视表数据透视表认识数据透视表50Excel数据清单数据库文本文件其他关系型数据文件Excel数据透视表引擎数据透视表数据透视图数据透视表ExcelExcel 实战技巧培训实战技巧培训数据透视表了解数据透视表的结构51ExcelExcel 实战技巧培训实战技巧培训数据透视表认识数据透视表结构52报表筛选区域行区域列区域数值区域ExcelExcel 实战技巧培训实战技巧培训数据透视表创建一个透视表53选择区域中任意单元格插入透视表选择列、行、值ExcelExcel 实战技巧培训实
29、战技巧培训数据透视表无拘无束的数据透视表(1)54筛选字段、列、行字段任意拖拽的无拘无束字段、列、行字段任意拖拽的无拘无束字段字段汇总方式的无拘无束方式的无拘无束字段字段显示方式的无拘无束示方式的无拘无束修改数据透修改数据透视表的字段名称表的字段名称灵活灵活处理理错误值和空白和空白单元格元格ExcelExcel 实战技巧培训实战技巧培训数据透视表无拘无束的数据透视表(2)55谁先先谁后由决定，展示字段先后后由决定，展示字段先后顺序任意指定序任意指定数据透数据透视表的排序表的排序数据透数据透视表表的的筛选数据透数据透视表分表分组，组合方式由我定合方式由我定更改数据源和移更改数据源和移动数据透数据
30、透视表表ExcelExcel 实战技巧培训实战技巧培训数据透视表如虎添翼的计算字段和计算项56首先理解首先理解计算字段、算字段、计算算项案例：用案例：用计算算项计算算销售提成售提成案例：用案例：用计算算项计算年算年销售差异售差异ExcelExcel 实战技巧培训实战技巧培训数据透视表动态创建数据透视表57方法方法：定：定义名称法名称法创建建动态的数据透的数据透视表表方法方法：使用表功能：使用表功能创建建动态的数据透的数据透视表表方法：方法：导入外部数据入外部数据创建建动态数据透数据透视表表ExcelExcel 实战技巧培训实战技巧培训数据透视表多重合并、外部引用、透视图58多重合并的介多重合并
31、的介绍数据源数据源引用外部数据源引用外部数据源的介的介绍数据透视表的内容，比我们想象的还要多，今天我们仅仅见识了他的“冰山一角”简单介介绍数据透数据透视图ExcelExcel 实战技巧培训实战技巧培训目录59前言操作与技巧原来可以如此简单数据透视表百变星君函数与公式Excel的精髓之处12430001020304050607图表秀的是专业、拼的是创意56总结与作业图表秀图表的制作过程打破常规方可彰显专业气质动态图表的基本制作思路获取专业图表参考的方法ExcelExcel 实战技巧培训实战技巧培训图表图表秀60ExcelExcel 实战技巧培训实战技巧培训图表图表秀61ExcelExcel 实战
32、技巧培训实战技巧培训图表图表的制作过程62理解数据理解数据分析分析数据数据提炼信息提炼信息明确主题明确主题提炼信息提炼信息选择图表选择图表制作图表制作图表数据图表数据图表确定确定比较关系比较关系确定图表确定图表类型类型确定图表处理确定图表处理方式方式准备做图数据准备做图数据制作美化图表制作美化图表检查确认图表检查确认图表原始数据原始数据原始文件原始文件ExcelExcel 实战技巧培训实战技巧培训图表图表类型的选择63ExcelExcel 实战技巧培训实战技巧培训VSExcel常规商业周刊图表打破常规方可彰显专业气质64ExcelExcel 实战技巧培训实战技巧培训图表打破常规65打破打破颜色的限制颜色的限制打破图表的束缚打破图表的束缚简单简单唯美唯美，惜墨如金，惜墨如金主标题区副标题区图例区（可省略）图表区脚注区ExcelExcel 实战技巧培训实战技巧培训图表动态图表的基本制作思路66辅助区域法助区域法方法1定定义名称名称方法2图片引用法片引用法方法3ExcelExcel 实战技巧培训实战技巧培训图表获取专业图表参考的方法67ExcelExcel 实战技巧培训实战技巧培训目录68前言操作与技巧原来可以如此简单数据透视表百变星君函数与公式Excel的精髓之处12430001020304050607图表秀的是专业、拼的是创意56总结与作业谢谢各位！结束不是是开始http:/