Excel 培训教材.ppt

《Excel 培训教材.ppt》由会员分享，可在线阅读，更多相关《Excel 培训教材.ppt（31页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、2009LOTUSCorporationFangweiOfficeExcel函数&数据透视表普及培训教材12009LOTUSCorporationFangwei2Excel 普及性的知识普及性的知识请参看光盘资料，演示请参看光盘资料，演示知识点：知识点：1.一般表格设计、制做2.修饰工作表，使表格美观3.数据操作：sum4.数据组织分析：排序、筛选、表单5.图表制做22009LOTUSCorporationFangwei3 本次培训目的：主要针对更高一级的常用函本次培训目的：主要针对更高一级的常用函数及其应用。数及其应用。每一项内容都现场示例，请大家认真理解，每一项内容都现场示例，请大家认真理

2、解，不明白请当场提出！不明白请当场提出！32009LOTUSCorporationFangwei4第一部分：函数第一部分：函数Excel是办公室自动化中非常重要的一款软件，它不仅仅能够方便的处理表格和进行图形分析，其更强大的功能体现在对数据的自动处理和计算。函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。函数的结构：函数的结构：函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的形式出现，请在函数名称前面键入等号（=）。在创建包含函数的公式时，公式选项板将提供相关的帮助。示例示例1函数可以嵌套，就是指在某些情况下，您可能需

3、要将某函数作为另一函数的参数使用。例如图1中所示的公式使用了嵌套的AVERAGE函数，并将结果与50相比较。这个公式的含义是：如果单元格F2到F5的平均值大于50，则求F2到F5的和，否则显示数值0。示例示例 242009LOTUSCorporationFangwei5使用步骤使用步骤1.单击需要输入函数的单元格；2.单击插入栏中“函数”按钮，将会在编辑栏下面出现一个“公式选项板”，此时“名称”框将变成“函数”按钮；3.选择函数类别及函数，确定；4.当选中所需的函数后，Excel 2003将打开“公式选项板”。用户可以在这个选项板中输入函数的参数，当输入完参数后，在“公式选项板”中还将显示函数

4、计算的结果；5.单击“确定”按钮，即可完成函数的输入；示例示例3内容：1、求和函数：SUMSUMIFSUBTOTAL2、逻辑函数：ANDORIFCOUNTIF3、查询函数：HLOOKUP、VLOOKUP4、统计函数：AVERAGE、TRIMMEAN、COUNT、FREQUENCY、MAX、MIN、MODE5、其它：TRIM、EXACT、HOUR、DAY、DAY、MONTH、YEAR52009LOTUSCorporationFangwei61.求和函数重点介绍SUM（计算一组参数之和）、SUMIF（对满足某一条件的单元格区域求和）、SUBTOTAL（分类汇总）的使用。示例示例4 （SUMIF)S

5、UM示例示例SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中。SUBTOTAL：回列表或数据库中的分类汇总。通常，使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的列表。一旦创建了分类汇总，就可以通过编辑SUBTOTAL函数对该列表进行修改62009LOTUSCorporationFangwei7SUBTOTAL(function_num,ref1,ref2,.)Function_num为1到11（包含隐藏值）或101到111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。Ref1,ref2,为要

6、进行分类汇总计算的1到29个区域或引用。示例示例5 （SUBTOTAL)非空值单元格数乘积72009LOTUSCorporationFangwei82.逻辑函数用来判断真假值，或者进行复合检验的Excel函数，我们称为逻辑函数。在Excel中提供了六种逻辑函数。即AND、OR、NOT、FALSE、IF、TRUE函数。示例示例6 (机器调试自动分析）机器调试自动分析）（一）AND函数所有参数的逻辑值为真时返回TRUE；只要一个参数的逻辑值为假即返回FALSE。简言之，就是当AND的参数全部满足某一条件时，返回结果为TRUE，否则为FALSE。语法为AND(logical1,logical2,.)

7、，其中Logical1,logical2,.表示待检测的1到30个条件值（二）OR函数指在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE。示例示例7 (长工资标准分析）长工资标准分析）82009LOTUSCorporationFangwei92.逻辑函数（三）IF函数。IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此If函数也称之为条件函数。语法：IF(logical_test,value_if_true,value_if_false)。条件条件为true时的结果条件为false时的结果示例示例8 （IF)函数IF可以嵌套七层，用value_if_false及v

8、alue_if_true参数可以构造复杂的检测条件。示例示例8 （IF嵌套)92009LOTUSCorporationFangwei10COUNTIF函数用来计算给定区域内满足特定条件的单元格的数目。语法形式为COUNTIF(range,criteria)。Range区域，criteria条件。注：如果criteria表示一个固定值，只接输入，如果表示逻辑关系，需加”,如”=90”示例示例8 （COUNTIF)102009LOTUSCorporationFangwei113.查询函数HLOOKUP、VLOOKUP函数函数可以返回向量（单行区域或单列区域）或数组中的数值。当比较值位于数据表的首行

9、，并且要查找下面给定行中的数据时，使用函数HLOOKUP。当比较值位于要进行数据查找的左边一列时，使用函数VLOOKUP。语法形式为：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value表示要查找的值，它必须位于自定义查找区域的最（上）左列Table_array查找的区域，用于查找数据的区域Row_index_num为table_array中待返回的匹配值的行序号。Col_index_

10、num为相对列号。Range_lookup为一逻辑值，指明函数HLOOKUP查找时是精确匹配，还是近似匹配。示例示例9（VLOOKUP)练习练习112009LOTUSCorporationFangwei12MATCH函数如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH函数语法：MATCH(lookup_value,lookup_array,match_type)为需要在为需要在 Look_array 中查中查找的数值找的数值，可以为数值（数，可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值）或对数字、文本或逻辑值的单元格字、文本或逻辑值的单元格引用引用 可能包含所要查找

11、可能包含所要查找的数值的连续单元的数值的连续单元格区域格区域 为数字-1、0或1。为1，函数MATCH查找小于或等于lookup_value的最大数值。Lookup_array必须按升序排列：.、-2、-1、0、1、2、.、A-Z、FALSE、TRUE。为0，函数MATCH查找等于lookup_value的第一个数值。Lookup_array可以按任何顺序排列。为-1，函数MATCH查找大于或等于lookup_value的最小数值。Lookup_array必须按降序排列：TRUE、FALSE、Z-A、.、2、1、0、-1、-2、.，等等。如果省略match_type，则假设为1。示例示例9 练

12、习练习122009LOTUSCorporationFangwei134.统计函数a、求参数的算术平均值函数AVERAGE语法形式为AVERAGE(number1,number2,.)其中Number1,number2,.为要计算平均值的130个参数。b、求数据集的内部平均值TRIMMEAN函数TRIMMEAN先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。c、求单元格个数的统计函数COUNT语法形式为COUNT(value1,value2,.)d、求区域中数据的频率分布求区域中数据的频率分布FREQUENCY语法形式为FREQUENCY(data_array,bins_array

13、)其中data_array为一数组或对一组数值的引用,bins_array为进行频率计算的分段点.示例示例10示例示例11示例示例12 成绩分布统计成绩分布统计示例示例13 物料状态统计物料状态统计132009LOTUSCorporationFangwei144.统计函数e、数据集的最大值MAX与最小值MIN示例示例14f、数据集中出现频率最多的数MODE142009LOTUSCorporationFangwei155.文本函数*:连接连个文本字符串TRIM:删除数据前后所有多余的空格，用一个空格代替多个空格。如:TRIM(“MyHome“)=MyhomeLEN:返回单元格中字符的数量。如:L

14、EN(GREENHEAKVIP)=13LEFT:从左起返回确定数量的字符。如:LEFT(“Beijing”,3)=BeiRIGTH:从右起返回确定数量的字符。如:RIGHT(“Beijing”,4)=jingMID:在字符串中任意位置返回确定数量的字符。如:MID(422124197608119316,7,8)=19760811UPPER:将文本全部转化为大写。如:UPPER(“join”)=JOINLOWER:将文本全部转化为小写。如:LOWER(“JOIN”)=join示例示例15152009LOTUSCorporationFangwei166.其它函数A、EXACT函数，比较两个字符串是

15、否相同B、取得日期/时间的部分字段值。如果需要单独的年份、月份、日数或小时的数据时，可以使用HOUR、DAY、MONTH、YEAR函数直接从日期/时间中取出需要的数据。示例示例15公司函数的应用讲解:1、仓库系统的应用2、成本系统的应用3、皮带轮分解的应用4、在线物料需求的应用162009LOTUSCorporationFangwei17二、数据透视表如果一名财务人员，当你向老板汇报公司资产时，拿一堆枯燥的报表去说或许会吃老板的白眼(数据堆砌)，而一个图文并茂的数据表加上您精彩的讲解，却一定会博得赞许。用图表来表达数据比直接堆砌数字要来得更直观一些，往往从数字上看不出来的问题，一转换成图表就马

16、上清晰地暴露出来了。数据透视图可以用图形的方式显示数据透视表的内容，将包含大量数据的表格变成生动的图形，使数据以更直观的形式表现出来，可以达到比表格更好的效果。因为数据透视图与包含其源数据的数据透视表是相关联的，所示当数据透视表中的数据改变后，数据透视图也会自动随之改变，也就是说数据透视图具有自动更新功能。172009LOTUSCorporationFangwei18使用数据透视表前使用数据透视表前,表格数据杂乱无序表格数据杂乱无序:182009LOTUSCorporationFangwei19使用数据透视表后使用数据透视表后,通过对数据不同的视角显示数据并对数据进行比较、揭示和分析，通过对数

17、据不同的视角显示数据并对数据进行比较、揭示和分析，从而将数据转化成有意义的信息从而将数据转化成有意义的信息:192009LOTUSCorporationFangwei20创建数据透视表创建数据透视表1、单击“数据”菜单，选择“数据透视表”。2、在创建数据透视表窗口中，选择数据源单元格区域。在同一数据表格中，已经建了一个数据透视表，也可以在其基础上再进行修改处理。3、选择数据透视表的放置位置（新建工作表、现有工作表），如果选择“现有工作表”，可以指定透视表的单元格位置。202009LOTUSCorporationFangwei214、从右侧控制面版区域的字段列表中，可以拖动字段到数据透视表区域中

18、的“页区域”、“行区域”、“列区域”、“数据区域”进行数据的分析比较。5、右键单击数据透视表操作区域的字段,选择“字段设置”，在“数据透视表字段”中，可以修改字段名称、分类汇总方式等相关信息。212009LOTUSCorporationFangwei226、可在控制面版拖动字段到区域外以删除字段，并可在同一区域内上下拖动字段以调整位置，也可右键单击字段完成所需的删除和移动。在页区域、行区域和列区域中，每个字段都不能重复出现，但一个字段可以多次放到数据区域。示例示例16222009LOTUSCorporationFangwei23特性介绍特性介绍正是这些特性使得应用透视表来分析数据对用户形成了很

19、大的吸引力。1、内置筛选数据透视表自动地内置了筛选，从而允许用户只浏览自己关心的细节。2、动态布局数据透视表具有动态布局功能，只要通过拖放数据透视表中的字段到数据透视表的其他区域，就能够轻易地改变数据的显示格式。3、自动汇总报表数据不管用户决定采用什么字段作为总结，数据都能够自动汇总。用户还能够改变数据汇总的计算类型。4、支持各种数据源用户能够基于各种各样的数据源创建数据透视表，这些数据源包括232009LOTUSCorporationFangwei24字段介绍字段介绍当创建数据透视表时，系统会要求用户为下面列出的四个区域选择字段：1、页字段页字段是数据表中主要层次的筛选。当用户选择页字段时，

20、一定要弄清楚到底想怎样组织自己的数据。2、行字段行字段是更低一级的细节筛选，也是第二个层次的细节筛选。3、列字段列字段的筛选层次跟行字段一样。4、数据项数据项就是想要汇总的字段，通常比较适合用来作为数据项的字段，比如销售额、费用、库存数量等等。242009LOTUSCorporationFangwei25示例示例17 综合应用综合应用在数据透视表中在数据透视表中,你可以对行或列字段的数据项进行分组你可以对行或列字段的数据项进行分组.注意演示步骤！注意演示步骤！（附小电影）（附小电影）252009LOTUSCorporationFangwei26作业：作业：1、函数、函数IF应用应用2、函数、函

21、数VLOOKUP应用应用3、数据透视表综合作业、数据透视表综合作业262009LOTUSCorporationFangwei27附录：常用快捷键表附录：常用快捷键表1.CTRL+1:打开单元格格式设定2.CTRL+2:应用格式设定中字体粗体3.CTRL+3:应用格式设定中字体斜体4.CTRL+4:应用格式设定中字体下划线5.CTRL+5:应用格式设定中字体删除线6.CTRL+6:在筛选模式下打开或关闭筛选7.CTRL+7:隐藏格式栏8.CTRL+9:隐藏行9.CTRL+0:隐藏列10.CTRL+-:删除行或列272009LOTUSCorporationFangwei2811.CTRL+W:关闭

22、当前工作表，不退出EXCEL12.CTRL+Y:重复上一次操作13.CTRL+U:应用字体下划线14.CTRL+I:应用字体斜体15.CTRL+O:打开新工作表对话框16.CTRL+P:打开打印对话框17.CTRL+A:全选工作表区域18.CTRL+S:另存为对话框19.CTRL+F:打开查找与替换对话框20.CTRL+G:打开定位对话框282009LOTUSCorporationFangwei2921.CTRL+H:打开查找与替换对话框22.CTRL+K:打开插入超链接对话框23.CTRL+L:打开创建列表对话框24.CTRL+;:输入当前日期25.CTRL+ENTER:用当前输入项填充选定

23、的单元格区域 26.CTRL+Z:撤销上一步操作27.CTRL+X:剪切28.CTRL+C:复制29.CTRL+V:粘贴30.CTRL+B:应用字体粗体292009LOTUSCorporationFangwei3031.CTRL+N:新建工作薄32.CTRL+SPACE:切换输入法33.CTRL+UP:快速定位到第一行34.CTRL+DOWN:快速定位到65536行35.CTRL+HOME:移到行首36.CTRL+END:移到列尾37.CTRL+D:向下填充38.CTRL+R:向右填充39.CTRL+:在公式与结果中切换40.CTRL+TAB:在工作薄中移动302009LOTUSCorporationFangwei3141.CTRL+ESC:显示WINDOWS开始菜单42.CTRL+F1:打开帮助43.CTRL+F3:打开定义名称对话框44.CTRL+F4:关闭当前工作表45.CTRL+F5:46.CTRL+F6:在多个工作薄中切换47.CTRL+F9:最小化工作表48.CTRL+F10:最大化工作表49.ALT+ENTER:在单元格强制性换行50.END+向下(上)键31