Excel实战培训

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,#,Excel,实战技巧培训,1,Excel,实战技巧培训,目录,2,前言,操作与技巧,原来可以如此简单,数据透视表,百变星君,函数与公式,Excel,的精髓之处,1,2,4,3,00,01,02,03,04,05,06,07,图表,专业的我也可以做到,5,6,总结,与作业,前言,Excel,有些什么,3,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,前言,操作与技巧,原来可以如此简单,数据透视表,百变星君,函数与公式,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,名称是什么东东？,名称：实际上是对,Excel,“对象”的命名。而所谓对象可以是：,单元格,单元格,区域,数值、数组常量,公式,宏表函数,操作与技巧进阶,名称的运用,21,为什么要使用名称,名称的优点：,使公式、有效性、条件格式等等易于理解，比如用“税率”代替单元格引用,.,简化,公式,利于,安全，易于维护,可以,在条件格式、有效性中引用其他工作表单元格或常量数组,调用,Excel4.0,宏表函数必须使用名称,可以在,控件中调用单元格内容,Excel,的几乎任何地方那个都可以使用名称,操作与技巧进阶,名称的运用,22,关于名称的约定,名称不得与单元格地址相同。包括,R1C1,样式。,名称,不得使用空格。,名称不得以数字开头或单独使用数字。,Excel,识别名称时，不区分大小写。,名称是一种特殊形式的“公式”，因此其长度、内部嵌套等同样受到限制，比如内部长度最大,16,384,个字符。,名称所用字符不能超过,255,个字符。,当名称引用单元格区域时，要考虑相对引用和绝对引用。,操作与技巧进阶,名称的运用,23,定义名称的三种方法,方法,1:,插入名称,案例,2:,使用名称框,案例,3:,根据所选内容创建（批量）,操作与技巧进阶,名称的运用,24,关于名称的案例,案例,1:,在名称中使用常量,案例,2:,在名称,中,使用,函数,案例,3:,动态名称的运用,案例,4:,如何一次性删除所有定义的名称,操作技巧进阶,综合案例分析,25,一份市场活动赠品申请表的编制案例,单纯的表格,VS,名称,+,数据有效性,+,条件格式,+,工作表保护,的表格,操作与技巧进阶,高级筛选,26,高级筛选及举例,参考一份网络上的教程。请看案例,目录,27,前言,操作与技巧,原来可以如此简单,数据透视表,百变星君,函数与公式,Excel,的精髓之处,1,2,4,3,00,01,02,03,04,05,06,07,图表,秀的是专业、拼的是创意,5,6,总结,与作业,热身运动，现在开始,Excel,运算符及其,含义,什么是公式、什么是函数,如何快速、准确找到适合的,函数,函数与公式,热身运动,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,总结,与作业,热身运动，现在开始,Excel,运算符及其含义,什么是公式、什么是函数,如何快速、准确找到适合的函数,记住三个,F,吧，他们很棒,F1,不离不弃的好兄弟,F4,相对？还是绝对？,由,你说了算,F9,一把“庖丁解牛”的宝刀,函数起步,F1,不离不弃的好兄弟,33,自学函数的捷径全在这里了,记住：,excel,本身就是最好的参考书,F1,，,F1,哪里不会点哪里,函数起步,F4,相对,？绝对,？由你,说了算,34,何,为引用，有何影响？,案例,1:,三,种引用的同时理解,案例,2:,为什么,vlookup,会出现错误值,案例,3:,综合案例,函数起步,F9,一,把“庖丁解牛”的宝刀,35,F9,应用举例,解析,统计函数,:,IF+COUNTIF+SUMIF,的综合应用,解析,文本函数,:,FIND+MID,的综合应用,解析复杂,数组公式,:,其实就是这么简单,目录,36,前言,操作与技巧,原来可以如此简单,数据透视表,百变星君,函数与公式,Excel,的精髓之处,1,2,4,3,00,01,02,03,04,05,06,07,图表,秀的是专业、拼的是创意,5,6,总结,与作业,热身运动，现在开始,什么是函数,如何快速、准确找到适合的函数,兵马未动，逻辑先行,记住三个,F,吧，他们很棒,F1,不离不弃的好兄弟,F4,相对？还是绝对？,由,你说了算,F9,一把“庖丁解牛”的宝刀,函数如此简单,TRUE vs FALSE,逻辑判断是函数的,灵,魂,数据汇总统计函数,查找引用函数,文本处理函数,璀璨明珠,Sumproduct,函数如此简单,TRUE vs FALSE,37,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,注：查找引用类将采用函数解析,+,案例的方式,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,理解数据,分析,数据,提炼信息,明确主题,提炼信息,选择图表,制作图表,数据图表,确定,比较关系,确定图表,类型,确定图表处理,方式,准备做图数据,制作美化图表,检查确认图表,原始数据,原始文件,图表,图表类型的选择,64,VS,Excel,常规,商业周刊,图表,打破常规方可彰显专业气质,65,图表,打破常规,66,打破,颜色的限制,打破图表的束缚,简单,唯美,，惜墨如金,主标题区,副标题区,图例区（可省略）,图表区,脚注区,图表,动态图表的基本制作思路,67,辅助区域法,方法,1,定义名称,方法,2,图片引用法,方法,3,图表,获取专业图表参考的方法,68,目录,69,前言,操作与技巧,原来可以如此简单,数据透视表,百变星君,函数与公式,Excel,的精髓之处,1,2,4,3,00,01,02,03,04,05,06,07,图表,秀的是专业、拼的是创意,5,6,总结,与作业,谢谢各位,！,结束,不是,是开始,