Excel培训教材PPT

1、公式与函数基础（10min）p2-p8 认识公式 /函数的输入与编辑 单元格和范围的引用 文本的处理（5min）p9-p13 字符串的编辑 字符串的截取/返回相应值 使用函数（45min）p14-p28 日期及时间函数（5min） 查询函数（10min） 逻辑判断函数（10min） 数学函数（5min） 统计函数（5min） 函数使用范例（10min,数据筛选（10min） p29-p31 自动筛选 高级筛选 数据有效性设置及应用 数据汇总（5min）p32-p36 分类汇总的计算方法 数据的组合与取消 合并计算 （5min） p37 使用数据透视表（15min） 创建数据透视表 编辑数据透视

2、表 宏的录制与运用（10min） 宏的功能 录制与运行宏 excel小技巧分享与讨论（10min,课程大纲,前言,excel用户层次分类原则,刚刚开始接触,基本掌握常用功能,熟练使用常用功能+数据透视表+公式,数组公式+vba简单编程,excel技能+专业知识+行业经验,新手,初级用户,中级用户,高级用户,专家,一、公式与函数基础（10min,1.2、公式中的运算符： 1）算术运算符,1、认识公式,1.1、常量：指直接输入公式中的数字或者文本值，即不用计算的值,1.2、公式中的运算符,一、公式与函数基础（10min,1、认识公式,3）、比较运算符,2）. 文本运算符： “”，然后按空格键 最后

3、按“ctrl+shift+;显示当前日期和时间,区别： now和today函数是动态的； 但快捷键得到的是静态的,2、查询函数,无作业工时的统计界面如下，如果想知道某个部门在某一个月的无作业工时，如何解决？hlookup（水平查找,三、使用函数（40min,需要在数据区域首行搜索的值,原始的数据 区域,满足条件2的单元格在数据区域的行数,0：找不到时：返回#n/a； 1：找不到时：返回0,2、查询函数,如果无作业工时的统计界面如下，想知道某个部门在某一个月的无作业工时，又如何解决？vlookup（竖直查找,三、使用函数（40min,需要在数据区域首列搜索的值- pmc部,原始的数据 区域,满足

4、条件2的单元格在数据区域的列数,2、查询函数,hlookup/vlookup使用技巧 1）如何获取查询满足条件的列序列号,三、使用函数（40min,2、查询函数,hlookup/vlookup使用技巧 2）当行/列序列号不同时，重新输入公式比较慢，如何解决,三、使用函数（40min,首先确保数据区域已经被全部选中； 鼠标点击单元格右下角，填充公式后，手动更改行数/列数即可,2、查询函数,手中有一个部门的电话号码清单，现在有其中一个人的电话号码，需要知道这个人是谁？查找指定数值lookup函数,三、使用函数（40min,注意：数据区域的值需要以升序排序，否则可能无法提供正确的值,3、逻辑判断函数

5、,三、使用函数（40min,1）：逻辑判断入门,3、逻辑判断函数,三、使用函数（40min,1）：取交集进行逻辑判断 同时满足所有条件才 ture,2）：取并进行逻辑判断 只要其中一个满足就 ture,3）：根据不同条件返回 不同值,4、数学函数,三、使用函数（40min,1）举例：产品标准重量85kg，计算多件产品与标准重量的偏差；abs（取绝对值,4、数学函数,三、使用函数（40min,3）两数相除的整数部分：quotient 函数,2）举例：计算两数相除的余数；mod 成品的包装运输有数量限制，假如一个卡通箱只能包60pcs，客户的订单情况如下；想知道整包装出货后仍有多少数量剩余,4、数

6、学函数,三、使用函数（40min,4）条件求和；sumif 各部门q1 无作业工时汇总如下，如何快速取得一个或多个部门的q1无作业工时之和,4、数学函数,三、使用函数（40min,5）舍入和取整函数ceiling/floor（按条件向上/下舍入） even/odd（按条件向上舍入为偶数/奇数），int：向下取整,5、统计函数,三、使用函数（40min,1）获取数组的最大/最小值max,maxa/min,mina max与min功能是获取所有数据的最小值； maxa与mina功能是获取所有非空单元格的最小值,2）单元格的统计函数 count与counta 统计单元格个数； countblank

7、统计空白单元格个数；countif：按条件统计； countifs：按多重条件统计,3）计算条件平均值averageif,1、自动筛选：快捷键：alt+d+f+f,四、数据筛选（10min,2、高级筛选 数据筛选高级筛选； 方式1；在原有区域直接显示筛选结果；不满足条件部分隐藏不显示； 列表区域：原始数据区域； 条件区域：筛选条件区域,2、高级筛选,通配符：以下通配符可作为筛选以及查找和替换内容时的比较条件,3、数据有效性设置及应用 举例：在意见收集时，经常碰到收集意见太发散的情况；比如以下情况；对一次team building 进行意见收集；得到左下图的结果。作为一个活动组织者，如何统计最终

8、意见,四、数据筛选（10min,解决办法：在发出意见收集表之前，使用数据有效性设置功能，给出规定的几类选项，要求team成员做“选择题”而非“自由回答,设置方法： 数据菜单数据有效性 选择设置序列,1、分类汇总的计算方法 分类汇总是对数据清单进行数据分析的一种方法.在数据中实现对符合某一个相同特性的数据行，按照特性信息进行数据汇总的功能,四、数据汇总（10min,举例：还是无作业工时：如果希望按照车间，来统计损耗工时以及损耗工时（hr,1）首先按照车间进行排序 2）数据菜单-选择分类汇总,四、数据汇总（10min,举例：希望按照车间，来统计损耗工时以及损耗工时（hr,点击按钮2,点击按钮1,2

9、、数据的组合与取消 在同一个excel sheet表中；当数据行/列非常多，对于大部分客户来说，只希望阅读到重点字段，可使用组合按钮；使报表外观更直观，简洁,四、数据汇总（10min,举例：在下表中，如果只需要关注车间及损耗工时字段；除了使用隐藏按钮外； 亦可使用组合按钮,选中“车间”与“损耗工时”之间的数据列,单击 数据-组及分级显示-组合按钮,点击：“损耗工时”上方按钮，自动合并上一步骤选中列；按钮由变成,3、合并计算当数据行不多时，合并计算是数据汇总的又一个好工具,四、数据汇总（10min,举例：按照车间，汇总“损耗工时”以及“损耗工时（hr,1）鼠标点击一处空白区域，存放合并计算结果；

10、注意该区域不能和数据区域重叠,2）点击数据菜单：合并计算： *引用位置中 起始位置为：“车间”（b列） 结束位置为：“损耗工时（hr)”(h列） *标签位置：选中“首行”及“最左列” 确定,3、合并计算当数据行不多时，合并计算是数据汇总的又一个好工具；数据菜单，合并计算,四、数据汇总（10min,举例：按照车间，汇总“损耗工时”以及“损耗工时（hr,3）得到如下结果；增添标题行即可得到汇总结果,4）在同一个sheet表对同一份数据二次合并计算时，注意需要删除“所有引用位置”内数据；重新选择引用位置,线柱图/mtb中的柏拉图举例；在kpi指标的分解中，常需要了解各种不同因素的现状（柱图/饼图）以

11、及对结果的百分比影响；在excel中可以使用线柱图达到一图展示的效果,五、制作与使用图表（5min,线柱图/mtb中的柏拉图举例；在kpi指标的分解中，常需要了解各种不同因素的现状（柱图/饼图）以及对结果的百分比影响；在excel中可以使用线柱图达到一图展示的效果,五、制作与使用图表（5min,excel 2003版本,1）选中数值区域为名称，金额，及累计百分比,2）在“插入”菜单，“图表”选项下选择 自定义类型“两轴线柱图,线柱图/mtb中的柏拉图举例；在kpi指标的分解中，常需要了解各种不同因素的现状（柱图/饼图）以及对结果的百分比影响；在excel中可以使用线柱图达到一图展示的效果,五、

12、制作与使用图表（5min,excel 2003版本,3）在图表向导中完善各选项（标题，坐标轴等） 点击“下一步”完成图表绘制,线柱图/mtb中的柏拉图举例；在kpi指标的分解中，常需要了解各种不同因素的现状（柱图/饼图）以及对结果的百分比影响；在excel中可以使用线柱图达到一图展示的效果,五、制作与使用图表（5min,excel 2007版本,1）选中数据，点击“插入”“柱形图”“二维柱形图,线柱图/mtb中的柏拉图,五、制作与使用图表（5min,excel 2007版本,2）直方图出现了，果然没有折线关键的步骤来了，在直方图上点击右键，选择“设置数据系列格式”；,线柱图/mtb中的柏拉图,

13、五、制作与使用图表（5min,excel 2007版本,3）在“系统选项”“系列绘制在”中，选择“次坐标轴”。看到了吗？表示累计百分比的红色方出现了,线柱图/mtb中的柏拉图,五、制作与使用图表（5min,excel 2007版本,4）在红色方上单击右键，选择“更改系列图表类型”；,5）在“更改图标类型”中，选择“折线图,线柱图/mtb中的柏拉图,五、制作与使用图表（5min,excel 2007版本,6）折线出现啦再在图上单击右键“添加数据标签”，线柱双轴图大功造成！,1、数据透视表的强大功能,六、制作与使用数据透视表（15min,还是用5月无作业工时作为基础数据分析；应用数据透视表，将实现

14、强大的数据分析及展示能力,a：各车间的损耗工时和hr损耗工时各是多少,b：各车间的在5月份各停线多少单,c：各车间停线工时占5月百分比情况,d：各机型停线情况如何,e,2、创建数据透视表,六、制作与使用数据透视表（15min,1）鼠标点击数据区域； 2）“数据”菜单点击“数据透视表和数据透视图”按钮,六、制作与使用数据透视表（15min,3）按照统计要求拖放相关字段到行/列/页/数据区域,3、编辑数据透视表,六、制作与使用数据透视表（15min,数据透视表可通过拖放与隐藏字段实现按照不同字段汇总及比对,1、宏的功能,七、宏的录制与运用（10min,在excel中，宏是指一系列excel能够执行

15、的vba语句；在语言结构中，宏由一系列 的命令和函数组成，保存在vba的模块中。可以根据用户需要及时运行，执行对应 的任务,vba：是一种功能强大的脚本边写语言，主要功能是实现应用程序的自动化； vba中可以包含宏，但不仅仅由宏构成,当需要重复执行一个或多个简单命令时：使用宏可提高工作效率,1、宏的功能,七、宏的录制与运用（10min,举例： 将以下数据统一更改字体为“黑体”，字体大小设置为9号；增加边框； 并且将“车间”列填充为黄色； 在第一次操作时使用宏录制功能，当以上设置完成后，停止宏录制； 则在数据内容更改后，重新运行宏能瞬间完成相同的设置，而不是重新手动设置,2、录制与运行宏excel 2003“工具”菜单；“宏”-“录制宏”；excel 2007“视图”菜单；“宏” -“录制宏,七、宏的录制与运用（10min,1）、进入右图界面： 宏名：任意名称 快捷键：运行该宏时使用