Excel中级培训教程实用教案

《Excel中级培训教程实用教案》由会员分享，可在线阅读，更多相关《Excel中级培训教程实用教案（52页珍藏版）》请在金锄头文库上搜索。

1、一、公式与函数一、公式与函数(hnsh)(hnsh)基础基础（10Min10Min）1.2、公式(gngsh)中的运算符： 1）算术运算符算术运算符号算术运算符号运算符含义运算符含义示例示例+(+(加号加号) )加加2+3=52+3=5-(-(减号减号) )减减3-1=23-1=2* *( (星号星号乘乘3*2=63*2=6/(/(斜杠斜杠) )除除6/2=36/2=3%(%(百分号百分号) )百分号百分号50%50%(脱字号脱字号) )乘方乘方43=443=43 3=64=64； 24=1624=161、认识(rn shi)公式1.1、常量：指直接输入公式中的数字或者文本值，即不用计算的值；

2、第1页/共51页第一页，共52页。1.2、公式(gngsh)中的运算符比较运算符比较运算符运算符含义运算符含义示示 例例=(=(等号等号) )相等相等B1=C1,B1=C1,若若B1B1中单元格内的值确实与中单元格内的值确实与C1C1中的中的值相等值相等, ,则产生逻辑真值则产生逻辑真值TRUE,TRUE,若不相等若不相等, ,则产生逻辑假值则产生逻辑假值FALSEFALSE(小于号小于号) )小于小于B1C1B1(大于号大于号) )大于大于B1C1,B1C1,若若B1B1中数值为中数值为6,C16,C1中数值为中数值为4,4,则条则条件成立产生逻辑真值件成立产生逻辑真值TRUE,TRUE,否

3、则产生逻辑否则产生逻辑假值假值FALSEFALSE=(=(大于等于号大于等于号) )大于等于大于等于B1=C1B1=C1(不等号不等号) )不等于不等于B1C1B1C1=(=(小于等于号小于等于号) )小于等于小于等于B1=C1B1=C1一、公式与函数(hnsh)基础（10Min）1、认识(rn shi)公式3）、比较运算符2）. 文本运算符： “&”号,可以将文本连接起来.第2页/共51页第二页，共52页。一、公式与函数(hnsh)基础（10Min）1.2、公式(gngsh)中的运算符4）. 引用运算符（实例中应该是C列，不是D列）第3页/共51页第三页，共52页。2 2、公式、公式(gng

4、sh)(gngsh)的输入与编辑的输入与编辑2.1、公式的输入 将光标移到单元格中，在其中输入等号和公式内容即可；输入过程中（左下图），输入的字符会同时显示(xinsh)在单元格和公式编辑栏； 输入完成后按enter键，再次选中该单元格，可见只有在编辑栏才会显示(xinsh)公式，在单元格内则只显示(xinsh)结果（右下图）一、公式与函数(hnsh)基础（10Min）第4页/共51页第四页，共52页。2 2、函数的输入、函数的输入(shr)(shr)与编辑与编辑2.2、函数的记忆式键入 1、将光标移到单元格中，在其中(qzhng)输入等号和公式内容的前几位字母时，会在单元格下方显示与这些字母

5、相关的有效函数；（左下图） 2、如果不清楚出现的这些函数如何选择；亦可直接在编辑栏点击2003版本的“=”号，2007版本的“fx”，在弹出的对话框中根据函数解释进行选择；一、公式与函数(hnsh)基础（10Min）第5页/共51页第五页，共52页。2 2、公式的输入、公式的输入(shr)(shr)与编辑与编辑2.3、公式编辑 1）、双击公式所在的单元格，在单元格内直接更改 2）、选中公式所在的单元格，然后单击公式编辑栏，在编辑栏内重新输入(shr)正确的公式 3）、选中公式所在的单元格，按F2键激活单元格的公式，在其中进行修改一、公式与函数(hnsh)基础（10Min）第6页/共51页第六页

6、，共52页。一、公式(gngsh)与函数基础（10Min）3 3、单元格和范围、单元格和范围(fnwi)(fnwi)的引用的引用实例：旧的美元与人民币汇率为6.8；在下表中要求计算每行对应的人民币金额；当汇率发生变化时，只能将每行的公式重新输入；此时使用单元格的引用更高效；3.1、相对引用 相对引用指公式中的单元格位置将随着公式单元格的位置而改变3.2、绝对引用 绝对引用是指公式和函数(hnsh)中的位置是固定不变的. 绝对引用是在列字母和行数字之前都加上美元符号”$”,如$A$4,$C$63.3、混合引用 混合引用是指在一个单元格引用中,既有绝对引用,也有相对引用.第7页/共51页第七页，共

7、52页。1 1、字符串的编辑、字符串的编辑(binj)(binj)实例1：工作中经常使用除法进行两个数据的相除；得到的结果往往不是整数；此时如果要显示固定位数怎么办？常规(chnggu)工具条中的减少小数位数只能在显示上“欺骗”视觉，实际在单元格内还是多位数；字符串的编辑函数FIxed是解决方法之一： 二、文本(wnbn)的处理（5Min）当结果是非数字类型时,乘1即可变为数字类型第8页/共51页第八页，共52页。1 1、字符串的编辑、字符串的编辑(binj)(binj)实例2：统计并显示到岗人员；表格(biog)中显示每一天的到岗数，需要展示为“某日到岗人员为*人”，使用CONCATENAT

8、E函数合并字符串； 二、文本(wnbn)的处理（5Min）实例2：运算符&的功能与此相似；但需手工输入；逻辑性较差该功能在后续的多重条件VLOOKUP 中可以起到很好的辅助作用； 第9页/共51页第九页，共52页。1 1、字符串的编辑、字符串的编辑(binj)(binj)实例3：我司的新物料编码规则已经限制了编码中的字符(z f)数量；新编码是12位，旧编码17位；当面临大量编码时；如何快速区分新旧编码与错误编码？使用Len函数统计字符(z f)长度； 二、文本(wnbn)的处理（5Min）第10页/共51页第十页，共52页。2 2、返回、返回(fnhu)(fnhu)相应值相应值实例1：仍然以

9、物料编码为例：新物料编码规则中，前三位代表物料三级分类；比如：主机面壳都是以BCA开头； 如何在大量(dling)编码中，按照物料三级分类统计数量？Left函数； 二、文本(wnbn)的处理（5Min）前三位代码截取OK后，如何进行数量统计？参考数据汇总章节第11页/共51页第十一页，共52页。2 2、返回、返回(fnhu)(fnhu)相应值相应值实例2：right与MID函数；与Left函数类似，截取数据右侧或者中间固定位数的数据；实例3：在公司的物料(w lio)编码描述中，可以看到完整的物料(w lio)描述非常长；如果只想保留最开始的中文描述，怎么办？ 二、文本(wnbn)的处理（5M

10、in）方法1方法2：数据，分列第12页/共51页第十二页，共52页。1 1、日期、日期(rq)(rq)及时间函数及时间函数很多时候，我们需要了解当前日期(rq)/时间；但又不能老是手动调整，怎么办？方法1：Today（）函数与Now（）函数 三、使用(shyng)函数（40Min）方法2：使用快捷键1)Ctrl+； 显示当前日期2)Ctrl+shift+；显示当前时间3)“Ctrl+;”，然后按空格键最后按“Ctrl+Shift+;显示当前日期和时间区别：Now和Today函数是动态的；但快捷键得到的是静态的；第13页/共51页第十三页，共52页。2 2、查询、查询(chxn)(chxn)函数

11、函数无作业工时的统计界面如下(rxi)，如果想知道某个部门在某一个月的无作业工时，如何解决？Hlookup（水平查找） 三、使用(shyng)函数（40Min）需要在数据区域首行搜索的值原始的数据 区域满足条件2的单元格在数据区域的行数0：找不到时：返回#N/A；1：找不到时：返回0第14页/共51页第十四页，共52页。2 2、查询、查询(chxn)(chxn)函数函数如果无作业工时的统计界面如下，想知道某个(mu )部门在某一个月的无作业工时，又如何解决？Vlookup（竖直查找） 三、使用(shyng)函数（40Min）需要在数据区域首列搜索的值- PMC部原始的数据 区域满足条件2的单元

12、格在数据区域的列数第15页/共51页第十五页，共52页。2 2、查询、查询(chxn)(chxn)函数函数Hlookup/Vlookup使用(shyng)技巧1）如何获取查询满足条件的列序列号 三、使用(shyng)函数（40Min）第16页/共51页第十六页，共52页。2 2、查询、查询(chxn)(chxn)函数函数Hlookup/Vlookup使用技巧2）当行/列序列号不同时(tngsh)，重新输入公式比较慢，如何解决？ 三、使用(shyng)函数（40Min）首先确保数据区域已经被全部选中；鼠标点击单元格右下角，填充公式后，手动更改行数/列数即可第17页/共51页第十七页，共52页。2

13、 2、查询、查询(chxn)(chxn)函数函数手中有一个(y )部门的电话号码清单，现在有其中一个(y )人的电话号码，需要知道这个人是谁？查找指定数值lookup函数三、使用(shyng)函数（40Min）注意：数据区域的值需要以升序排序，否则可能无法提供正确的值第18页/共51页第十八页，共52页。3 3、逻辑判断、逻辑判断(pndun)(pndun)函数函数三、使用(shyng)函数（40Min）1）：逻辑(lu j)判断入门第19页/共51页第十九页，共52页。3 3、逻辑判断、逻辑判断(pndun)(pndun)函数函数三、使用(shyng)函数（40Min）1）：取交集进行逻辑判

14、断(pndun)同时满足所有条件才 Ture2）：取并进行逻辑判断只要其中一个满足就 Ture3）：根据不同条件返回不同值第20页/共51页第二十页，共52页。4 4、数学、数学(shxu)(shxu)函数函数三、使用(shyng)函数（40Min）1）举例：产品(chnpn)标准重量85KG，计算多件产品(chnpn)与标准重量的偏差；ABS（取绝对值）第21页/共51页第二十一页，共52页。4 4、数学、数学(shxu)(shxu)函数函数三、使用(shyng)函数（40Min）3）两数相除的整数部分(b fen)：QUOTIENT 函数2）举例：计算两数相除的余数；MOD成品的包装运输有

15、数量限制，假如一个卡通箱只能包60PCS，客户的订单情况如下；想知道整包装出货后仍有多少数量剩余？第22页/共51页第二十二页，共52页。4 4、数学、数学(shxu)(shxu)函数函数三、使用(shyng)函数（40Min）4）条件求和；SUMIf 各部门Q1 无作业工时汇总如下(rxi)，如何快速取得一个或多个部门的Q1无作业工时之和？第23页/共51页第二十三页，共52页。4 4、数学、数学(shxu)(shxu)函数函数三、使用(shyng)函数（40Min）5）舍入和取整函数(hnsh)Ceiling/Floor（按条件向上/下舍入）Even/ODD（按条件向上舍入为偶数/奇数），

16、INT：向下取整；第24页/共51页第二十四页，共52页。5 5、统计、统计(tngj)(tngj)函数函数三、使用(shyng)函数（40Min）1）获取数组的最大/最小值MAX,MAXA/MIN,MINAMAX与MIN功能是获取所有(suyu)数据的最小值；MAXA与MINA功能是获取所有(suyu)非空单元格的最小值2）单元格的统计函数COUNT与COUNTA 统计单元格个数； COUNTBLANK 统计空白单元格个数；COUNTIF：按条件统计； COUNTIFS：按多重条件统计3）计算条件平均值AVERAGEIF第25页/共51页第二十五页，共52页。1 1、自动、自动(zdng)(

17、zdng)筛选：快捷键：筛选：快捷键：Alt+D+F+FAlt+D+F+F四、数据(shj)筛选（10Min）2、高级(goj)筛选 数据筛选高级(goj)筛选； 方式1；在原有区域直接显示筛选结果；不满足条件部分隐藏不显示； 列表区域：原始数据区域； 条件区域：筛选条件区域；第26页/共51页第二十六页，共52页。2、高级、高级(goj)筛选筛选请使用若要查找?（问号）任何单个字符例如，sm?th 查找“smith”和“smyth”*（星号）任何字符数例如，*east 查找“Northeast”和“Southeast”（波形符）后跟 ?、* 或 问号、星号或波形符例如，“fy91?”将会查找

18、“fy91?”通配符：以下通配符可作为筛选以及查找和替换内容时的比较通配符：以下通配符可作为筛选以及查找和替换内容时的比较(bjio)(bjio)条件条件: :第27页/共51页第二十七页，共52页。3 3、数据有效性设置及应用、数据有效性设置及应用 举例：在意见收集时，经常碰到收集意见太发散的情举例：在意见收集时，经常碰到收集意见太发散的情况况(qngkung)(qngkung)；比如以下情况；比如以下情况(qngkung)(qngkung)；对一次；对一次Team building Team building 进行意见收集；得到左下图的结果。作进行意见收集；得到左下图的结果。作为一个活动组

19、织者，如何统计最终意见？为一个活动组织者，如何统计最终意见？四、数据(shj)筛选（10Min）解决办法：在发出意见收集表之前，使用数据有效性设置功能，给出规定的几类选项，要求Team成员做“选择题”而非“自由回答”设置方法：数据菜单数据有效性选择设置序列第28页/共51页第二十八页，共52页。1 1、分类汇总的计算方法、分类汇总的计算方法 分类汇总是对数据清单进行数据分析的一种方法分类汇总是对数据清单进行数据分析的一种方法. .在数在数据中实现对符合某一个相同据中实现对符合某一个相同(xin tn)(xin tn)特性的数据行，特性的数据行，按照特性信息进行数据汇总的功能；按照特性信息进行数

20、据汇总的功能；四、数据(shj)汇总（10Min）举例：还是无作业工时：如果希望按照车间(chjin)，来统计损耗工时以及损耗工时（HR）；1）首先按照车间进行排序2）数据菜单-选择分类汇总第29页/共51页第二十九页，共52页。四、数据(shj)汇总（10Min）举例：希望按照车间，来统计(tngj)损耗工时以及损耗工时（HR）；点击按钮2点击按钮1第30页/共51页第三十页，共52页。2 2、数据的组合与取消、数据的组合与取消 在同一个在同一个Excel sheetExcel sheet表中；当数据行表中；当数据行/ /列非常多，对列非常多，对于大部分客户来说，只希望阅读到重点字段，可使用

21、组于大部分客户来说，只希望阅读到重点字段，可使用组合按钮；使报表合按钮；使报表(bobio)(bobio)外观更直观，简洁；外观更直观，简洁；四、数据(shj)汇总（10Min）举例：在下表中，如果只需要关注车间(chjin)及损耗工时字段；除了使用隐藏按钮外；亦可使用组合按钮选中“车间”与“损耗工时”之间的数据列单击 数据-组及分级显示-组合按钮点击：“损耗工时”上方按钮，自动合并上一步骤选中列；按钮由变成+第31页/共51页第三十一页，共52页。3 3、合并计算、合并计算当数据当数据(shj)(shj)行不多时，合并计算是数据行不多时，合并计算是数据(shj)(shj)汇总汇总的又一个好工

22、具；的又一个好工具；四、数据(shj)汇总（10Min）举例(j l)：按照车间，汇总“损耗工时”以及“损耗工时（HR）1）鼠标点击一处空白区域，存放合并计算结果；注意该区域不能和数据区域重叠2）点击数据菜单：合并计算：*引用位置中起始位置为：“车间”（B列） 结束位置为：“损耗工时（HR)”(H列） *标签位置：选中“首行”及“最左列”确定第32页/共51页第三十二页，共52页。3 3、合并计算、合并计算当数据行不多时，合并计算是数据汇总当数据行不多时，合并计算是数据汇总(huzng)(huzng)的又的又一个好工具；数据菜单，合并计算；一个好工具；数据菜单，合并计算；四、数据(shj)汇总

23、（10Min）举例(j l)：按照车间，汇总“损耗工时”以及“损耗工时（HR）3）得到如下结果；增添标题行即可得到汇总结果；4）在同一个sheet表对同一份数据二次合并计算时，注意需要删除“所有引用位置”内数据；重新选择引用位置；第33页/共51页第三十三页，共52页。线柱图线柱图/MTB/MTB中的柏拉图中的柏拉图举例；在举例；在KPIKPI指标的分解中，常需要了解各种不同因素的指标的分解中，常需要了解各种不同因素的现状（柱图现状（柱图/ /饼图）以及对结果的百分比影响；在饼图）以及对结果的百分比影响；在ExcelExcel中可以使用线柱图达到中可以使用线柱图达到(d do)(d do)一图

24、展示的效果；一图展示的效果；五、制作与使用(shyng)图表（5Min）第34页/共51页第三十四页，共52页。线柱图线柱图/MTB/MTB中的柏拉图中的柏拉图举例；在举例；在KPIKPI指标的分解中，常需要了解各种不同因素的指标的分解中，常需要了解各种不同因素的现状（柱图现状（柱图/ /饼图）以及对结果的百分比影响；在饼图）以及对结果的百分比影响；在ExcelExcel中可以使用线柱图达到中可以使用线柱图达到(d do)(d do)一图展示的效果；一图展示的效果；五、制作(zhzu)与使用图表（5Min）Excel 2003版本(bnbn)1）选中数值区域为名称，金额，及累计百分比2）在“插

25、入”菜单，“图表”选项下选择自定义类型“两轴线柱图第35页/共51页第三十五页，共52页。线柱图线柱图/MTB/MTB中的柏拉图中的柏拉图举例；在举例；在KPIKPI指标指标(zhbio)(zhbio)的分解中，常需要了解各种的分解中，常需要了解各种不同因素的现状（柱图不同因素的现状（柱图/ /饼图）以及对结果的百分比影响；饼图）以及对结果的百分比影响；在在ExcelExcel中可以使用线柱图达到一图展示的效果；中可以使用线柱图达到一图展示的效果；五、制作与使用(shyng)图表（5Min）Excel 2003版本(bnbn)3）在图表向导中完善各选项（标题，坐标轴等）点击“下一步”完成图表绘

26、制第36页/共51页第三十六页，共52页。线柱图线柱图/MTB/MTB中的柏拉图中的柏拉图举例；在举例；在KPIKPI指标的分解中，常需要了解各种不同因素的指标的分解中，常需要了解各种不同因素的现状（柱图现状（柱图/ /饼图）以及对结果的百分比影响饼图）以及对结果的百分比影响(yngxing)(yngxing)；在；在ExcelExcel中可以使用线柱图达到一图展示中可以使用线柱图达到一图展示的效果；的效果；五、制作与使用(shyng)图表（5Min）Excel 2007版本(bnbn)1）选中数据，点击“插入”“柱形图”“二维柱形图”；第37页/共51页第三十七页，共52页。线柱图线柱图/M

27、TB/MTB中的柏拉图中的柏拉图五、制作(zhzu)与使用图表（5Min）Excel 2007版本(bnbn)2）直方图出现了，果然没有折线关键的步骤来了，在直方图上点击右键，选择“设置数据系列(xli)格式”； ；第38页/共51页第三十八页，共52页。线柱图线柱图/MTB/MTB中的柏拉图中的柏拉图五、制作(zhzu)与使用图表（5Min）Excel 2007版本(bnbn)3）在“系统选项”“系列绘制在”中，选择(xunz)“次坐标轴”。看到了吗？表示累计百分比的红色方出现了； 第39页/共51页第三十九页，共52页。线柱图线柱图/MTB/MTB中的柏拉图中的柏拉图五、制作与使用(shy

28、ng)图表（5Min）Excel 2007版本(bnbn)4）在红色(hngs)方上单击右键，选择“更改系列图表类型”； ； 5）在“更改图标类型”中，选择“折线图”；第40页/共51页第四十页，共52页。线柱图线柱图/MTB/MTB中的柏拉图中的柏拉图五、制作与使用(shyng)图表（5Min）Excel 2007版本(bnbn)6）折线出现啦再在图上单击右键“添加数据标签(bioqin)”，线柱双轴图大功造成！ ； 第41页/共51页第四十一页，共52页。1 1、数据透视、数据透视(tush)(tush)表的强大功能表的强大功能六、制作六、制作(zhzu)(zhzu)与使用数据透视表与使用

29、数据透视表（15Min15Min） 还是用5月无作业工时作为基础数据分析；应用数据透视表，将实现强大(qingd)的数据分析及展示能力A：各车间的损耗工时和HR损耗工时各是多少？B：各车间的在5月份各停线多少单C：各车间停线工时占5月百分比情况D：各机型停线情况如何？E：第42页/共51页第四十二页，共52页。2 2、创建、创建(chungjin)(chungjin)数据透视表数据透视表六、制作六、制作(zhzu)(zhzu)与使用数据透视表与使用数据透视表（15Min15Min）1）鼠标点击数据区域；2）“数据”菜单(ci dn)点击“数据透视表和数据透视图”按钮第43页/共51页第四十三页

30、，共52页。六、制作六、制作(zhzu)(zhzu)与使用数据透视表与使用数据透视表（15Min15Min）3）按照(nzho)统计要求拖放相关字段到行/列/页/数据区域；第44页/共51页第四十四页，共52页。3 3、编辑、编辑(binj)(binj)数据透视表数据透视表六、制作与使用数据六、制作与使用数据(shj)(shj)透视表透视表（15Min15Min）数据透视表可通过拖放与隐藏字段实现按照(nzho)不同字段汇总及比对第45页/共51页第四十五页，共52页。1 1、宏的功能、宏的功能(gngnng)(gngnng)七、宏的录制(l zh)与运用（10Min） 在Excel中，宏是指

31、一系列Excel能够执行的VBA语句(yj)；在语言结构中，宏由一系列的命令和函数组成，保存在VBA的模块中。可以根据用户需要及时运行，执行对应的任务。VBA：是一种功能强大的脚本边写语言，主要功能是实现应用程序的自动化；VBA中可以包含宏，但不仅仅由宏构成；当需要重复执行一个或多个简单命令时：使用宏可提高工作效率第46页/共51页第四十六页，共52页。1 1、宏的功能、宏的功能(gngnng)(gngnng)七、宏的录制(l zh)与运用（10Min） 举例： 将以下数据统一更改字体为“黑体”，字体大小设置为9号；增加边框(binkung)；并且将“车间”列填充为黄色； 在第一次操作时使用宏

32、录制功能，当以上设置完成后，停止宏录制；则在数据内容更改后，重新运行宏能瞬间完成相同的设置，而不是重新手动设置；第47页/共51页第四十七页，共52页。2 2、录制与运行宏、录制与运行宏Excel 2003“Excel 2003“工具工具”菜单菜单(ci dn)(ci dn)；“宏宏”-“”-“录制录制宏宏”；Excel 2007“Excel 2007“视图视图”菜单菜单(ci dn)(ci dn)；“宏宏” -“” -“录制录制宏宏” ” 七、宏的录制(l zh)与运用（10Min）1）、进入右图界面：宏名：任意名称(mngchng)快捷键：运行该宏时使用保存在：默认当前工作表说明：任意备注

33、2）、确认后：开始进行正常的数据处理；如，全选数据，增加边框；更改字体类型及大小，填充颜色；3）、从菜单进入“宏”，选择“停止录制”；4）、当数据区域数据变更后，从菜单进入“宏”，选择“查看宏”“执行”；或直接运行快捷键，可运行该宏；第48页/共51页第四十八页，共52页。2 2、录制与运行宏、录制与运行宏 打开一个包含宏的打开一个包含宏的ExcelExcel文档，出现以下文档，出现以下(yxi)(yxi)对话对话框时，从工具菜单框时，从工具菜单- -选项选项- -安全性安全性- -宏安全性中，将安全级宏安全性中，将安全级别调低即可运行；别调低即可运行；七、宏的录制(l zh)与运用（10Mi

34、n）第49页/共51页第四十九页，共52页。八、Excel小技巧(jqio)分享与讨论（15Min）第50页/共51页第五十页，共52页。感谢您的欣赏(xnshng)！第51页/共51页第五十一页，共52页。内容(nirng)总结一、公式(gngsh)与函数基础（10Min）。输入过程中（左下图），输入的字符会同时显示在单元格和公式(gngsh)编辑栏。当结果是非数字类型时,乘1即可变为数字类型。参考数据汇总章节。Even/ODD（按条件向上舍入为偶数/奇数），INT：向下取整。不满足条件部分隐藏不显示。B：各车间的在5月份各停线多少单。数据透视表可通过拖放与隐藏字段实现按照不同字段汇总及比对。在Excel中，宏是指一系列Excel能够执行的VBA语句第五十二页，共52页。