Excel 培训教材.ppt

1、 2009 LOTUS CorporationFang wei1Office Excel 函数&数据透视表普及培训教材第1页，共32页。2009 LOTUS CorporationFang wei2Excel 普及性的知识普及性的知识请参看光盘资料，演示请参看光盘资料，演示知识点：知识点：1.一般表格设计、制做2.修饰工作表，使表格美观3.数据操作：sum4.数据组织分析：排序、筛选、表单5.图表制做第2页，共32页。2009 LOTUS CorporationFang wei3 本次培训目的：主要针对更高一级的常用函数及本次培训目的：主要针对更高一级的常用函数及其应用。其应用。每一项内容都现

2、场示例，请大家认真理解，不明每一项内容都现场示例，请大家认真理解，不明白请当场提出！白请当场提出！第3页，共32页。2009 LOTUS CorporationFang wei4第一部分：函数第一部分：函数 Excel是办公室自动化中非常重要的一款软件，它不仅仅能够方便的处理表格和进行图形分析，其更强大的功能体现在对数据的自动处理和计算。函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。函数的结构：函数的结构：函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的形式出现，请在函数名称前面键入等号（=）。在创建包含函数的公式时

3、，公式选项板将提供相关的帮助。示例示例1 函数可以嵌套，就是指在某些情况下，您可能需要将某函数作为另一函数的参数使用。例如图1中所示的公式使用了嵌套的 AVERAGE 函数，并将结果与 50 相比较。这个公式的含义是：如果单元格F2到F5的平均值大于50，则求F2到F5的和，否则显示数值0。示例示例 2第4页，共32页。2009 LOTUS CorporationFang wei5使用步骤使用步骤1.单击需要输入函数的单元格；2.单击插入栏中“函数”按钮，将会在编辑栏下面出现一个“公式选项板”，此时“名称”框将变成“函数”按钮；3.选择函数类别及函数，确定；4.当选中所需的函数后，Excel

4、2003将打开“公式选项板”。用户可以在这个选项板中输入函数的参数，当输入完参数后，在“公式选项板”中还将显示函数计算的结果；5.单击“确定”按钮，即可完成函数的输入；示例示例3内容：1、求和函数：SUM SUMIF SUBTOTAL2、逻辑函数：AND OR IF COUNTIF3、查询函数：HLOOKUP、VLOOKUP4、统计函数：AVERAGE、TRIMMEAN、COUNT、FREQUENCY、MAX、MIN、MODE5、其它：TRIM、EXACT、HOUR、DAY、DAY、MONTH、YEAR第5页，共32页。2009 LOTUS CorporationFang wei61.求和函数

5、重点介绍SUM（计算一组参数之和）、SUMIF（对满足某一条件的单元格区域求和）、SUBTOTAL（分类汇总）的使用。示例示例4 （SUMIF)SUM示例示例SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中。SUBTOTAL：回列表或数据库中的分类汇总。通常，使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的列表。一旦创建了分类汇总，就可以通过编辑 SUBTOTAL 函数对该列表进行修改 第6页，共32页。2009 LOTUS CorporationFang wei7SUBTOTAL(function_num,ref

6、1,ref2,.)Function_num 为 1 到 11（包含隐藏值）或 101 到 111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。Ref1,ref2,为要进行分类汇总计算的 1 到 29 个区域或引用。示例示例5 （SUBTOTAL)非空值单元格数乘积第7页，共32页。2009 LOTUS CorporationFang wei82.逻辑函数 用来判断真假值，或者进行复合检验的Excel函数，我们称为逻辑函数。在Excel中提供了六种逻辑函数。即AND、OR、NOT、FALSE、IF、TRUE函数。示例示例6 (机器调试自动分析）机器调试自动分析）（一）AND

7、函数 所有参数的逻辑值为真时返回 TRUE；只要一个参数的逻辑值为假即返回 FALSE。简言之，就是当AND的参数全部满足某一条件时，返回结果为TRUE，否则为FALSE。语法为AND(logical1,logical2,.)，其中Logical1,logical2,.表示待检测的 1 到 30 个条件值 （二）OR函数指在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE。示例示例7 (长工资标准分析）长工资标准分析）第8页，共32页。2009 LOTUS CorporationFang wei92.逻辑函数（三）IF函数。IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同

8、的结果，因此If函数也称之为条件函数。语法：IF(logical_test,value_if_true,value_if_false)。条件 条件为true时的结果 条件为false时的结果示例示例8 （IF)函数 IF 可以嵌套七层，用 value_if_false 及 value_if_true 参数可以构造复杂的检测条件。示例示例8 （IF嵌套)第9页，共32页。2009 LOTUS CorporationFang wei10COUNTIF 函数 用来计算给定区域内满足特定条件的单元格的数目。语法形式为COUNTIF(range,criteria)。Range区域，criteria条件。

9、注：如果criteria表示一个固定值，只接输入，如果表示逻辑关系，需加”,如”=90”示例示例8 （COUNTIF)第10页，共32页。2009 LOTUS CorporationFang wei113.查询函数HLOOKUP、VLOOKUP函数函数可以返回向量（单行区域或单列区域）或数组中的数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，使用函数 HLOOKUP。当比较值位于要进行数据查找的左边一列时，使用函数 VLOOKUP。语法形式为：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)VLOOKUP(

10、lookup_value,table_array,col_index_num,range_lookup)Lookup_value表示要查找的值，它必须位于自定义查找区域的最（上）左列 Table_array查找的区域，用于查找数据的区域Row_index_num为 table_array 中待返回的匹配值的行序号。Col_index_num为相对列号。Range_lookup为一逻辑值，指明函数 HLOOKUP 查找时是精确匹配，还是近似匹配。示例示例9（VLOOKUP)练习练习第11页，共32页。2009 LOTUS CorporationFang wei12MATCH函数如果需要找出匹配元

11、素的位置而不是匹配元素本身，则应该使用 MATCH 函数语法：MATCH(lookup_value,lookup_array,match_type)为需要在为需要在 Look_array 中查找的数中查找的数值值，可以为数值（数字、文本或逻，可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的辑值）或对数字、文本或逻辑值的单元格引用单元格引用 可能包含所要查找的数可能包含所要查找的数值的连续单元格区域值的连续单元格区域 为数字-1、0 或 1。为1，函数 MATCH 查找小于或等于 lookup_value 的最大数值。Lookup_array 必须按升序排列：.、-2、-1、0、1、2、

12、.、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页，共32页。2009 LOTUS CorporationFang wei134.统计函数a、求参数的算术平均值函数AVERAGE语法形式为AVERAGE(number1,number

13、2,.)其中Number1,number2,.为要计算平均值的 130 个参数。b、求数据集的内部平均值TRIMMEAN函数TRIMMEAN先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。c、求单元格个数的统计函数COUNT 语法形式为COUNT(value1,value2,.)d、求区域中数据的频率分布求区域中数据的频率分布FREQUENCY语法形式为FREQUENCY(data_array,bins_array)其中data_array为一数组或对一组数值的引用,bins_array为进行频率计算的分段点.示例示例10示例示例11示例示例12 成绩分布统计成绩分布统计示例示例

14、13 物料状态统计物料状态统计第13页，共32页。2009 LOTUS CorporationFang wei144.统计函数e、数据集的最大值MAX与最小值MIN 示例示例14 f、数据集中出现频率最多的数MODE 第14页，共32页。2009 LOTUS CorporationFang wei155.文本函数*:连接连个文本字符串TRIM:删除数据前后所有多余的空格，用一个空格代替多个空格。如:TRIM(“My Home “)=My homeLEN:返回单元格中字符的数量。如:LEN(GREENHEAK VIP)=13LEFT:从左起返回确定数量的字符。如:LEFT(“Beijing”,3

15、)=BeiRIGTH:从右起返回确定数量的字符。如:RIGHT(“Beijing”,4)=jingMID:在字符串中任意位置返回确定数量的字符。如:MID(422124197608119316,7,8)=19760811UPPER:将文本全部转化为大写。如:UPPER(“join”)=JOINLOWER:将文本全部转化为小写。如:LOWER(“JOIN”)=join示例示例15第15页，共32页。2009 LOTUS CorporationFang wei166.其它函数 A、EXACT函数，比较两个字符串是否相同 B、取得日期/时间的部分字段值。如果需要单独的年份、月份、日数或小时的数据时，

16、可以使用HOUR、DAY、MONTH、YEAR函数直接从日期/时间中取出需要的数据。示例示例15公司函数的应用讲解:1、仓库系统的应用2、成本系统的应用3、皮带轮分解的应用4、在线物料需求的应用第16页，共32页。2009 LOTUS CorporationFang wei17二、数据透视表 如果一名财务人员，当你向老板汇报公司资产时，拿一堆枯燥的报表去说或许会吃老板的白眼(数据堆砌)，而一个图文并茂的数据表加上您精彩的讲解，却一定会博得赞许。用图表来表达数据比直接堆砌数字要来得更直观一些，往往从数字上看不出来的问题，一转换成图表就马上清晰地暴露出来了。数据透视图可以用图形的方式显示数据透视表

17、的内容，将包含大量数据的表格变成生动的图形，使数据以更直观的形式表现出来，可以达到比表格更好的效果。因为数据透视图与包含其源数据的数据透视表是相关联的，所示当数据透视表中的数据改变后，数据透视图也会自动随之改变，也就是说数据透视图具有自动更新功能。第17页，共32页。2009 LOTUS CorporationFang wei18使用数据透视表前使用数据透视表前,表格数据杂乱无序表格数据杂乱无序:第18页，共32页。2009 LOTUS CorporationFang wei19使用数据透视表后使用数据透视表后,通过对数据不同的视角显示数据并对数据进行比较、揭示和分析，从而将通过对数据不同的视

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

19、可以拖动字段到数据透视表区域中的“页区域”、“行区域”、“列区域”、“数据区域”进行数据的分析比较。5、右键单击数据透视表操作区域的字段,选择“字段设置”，在“数据透视表字段”中，可以修改字段名称、分类汇总方式等相关信息。第21页，共32页。2009 LOTUS CorporationFang wei226、可在控制面版拖动字段到区域外以删除字段，并可在同一区域内上下拖动字段以调整位置，也可右键单击字段完成所需的删除和移动。在页区域、行区域和列区域中，每个字段都不能重复出现，但一个字段可以多次放到数据区域。示例示例16第22页，共32页。2009 LOTUS CorporationFang w

20、ei23特性介绍特性介绍正是这些特性使得应用透视表来分析数据对用户形成了很大的吸引力。1、内置筛选数据透视表自动地内置了筛选，从而允许用户只浏览自己关心的细节。2、动态布局数据透视表具有动态布局功能，只要通过拖放数据透视表中的字段到数据透视表的其他区域，就能够轻易地改变数据的显示格式。3、自动汇总报表数据不管用户决定采用什么字段作为总结，数据都能够自动汇总。用户还能够改变数据汇总的计算类型。4、支持各种数据源用户能够基于各种各样的数据源创建数据透视表，这些数据源包括第23页，共32页。2009 LOTUS CorporationFang wei24字段介绍字段介绍当创建数据透视表时，系统会要求

21、用户为下面列出的四个区域选择字段：1、页字段 页字段是数据表中主要层次的筛选。当用户选择页字段时，一定要弄清楚到底想怎样组织自己的数据。2、行字段 行字段是更低一级的细节筛选，也是第二个层次的细节筛选。3、列字段 列字段的筛选层次跟行字段一样。4、数据项 数据项就是想要汇总的字段，通常比较适合用来作为数据项的字段，比如销售额、费用、库存数量等等。第24页，共32页。2009 LOTUS CorporationFang wei25示例示例17 综合应用综合应用在数据透视表中在数据透视表中,你可以对行或列字段的数据项进行分组你可以对行或列字段的数据项进行分组.注意演示步骤！注意演示步骤！（附小电影

22、）（附小电影）第25页，共32页。2009 LOTUS CorporationFang wei26作业：作业：1、函数、函数IF应用应用2、函数、函数VLOOKUP应用应用3、数据透视表综合作业、数据透视表综合作业第26页，共32页。2009 LOTUS CorporationFang wei27附录：常用快捷键表附录：常用快捷键表CTRL+1:打开单元格格式设定CTRL+2:应用格式设定中字体粗体CTRL+3:应用格式设定中字体斜体CTRL+4:应用格式设定中字体下划线CTRL+5:应用格式设定中字体删除线CTRL+6:在筛选模式下打开或关闭筛选CTRL+7:隐藏格式栏CTRL+9:隐藏行C

23、TRL+0:隐藏列CTRL+-:删除行或列第27页，共32页。2009 LOTUS CorporationFang wei2811.CTRL+W:关闭当前工作表，不退出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:打开定位对话框第28页，共32页。2009 LOTUS CorporationFang wei2921.CTRL+H:打开查

24、找与替换对话框22.CTRL+K:打开插入超链接对话框23.CTRL+L:打开创建列表对话框24.CTRL+;:输入当前日期25.CTRL+ENTER:用当前输入项填充选定的单元格区域 26.CTRL+Z:撤销上一步操作27.CTRL+X:剪切28.CTRL+C:复制29.CTRL+V:粘贴30.CTRL+B:应用字体粗体第29页，共32页。2009 LOTUS CorporationFang wei3031.CTRL+N:新建工作薄32.CTRL+SPACE:切换输入法33.CTRL+UP:快速定位到第一行34.CTRL+DOWN:快速定位到65536行35.CTRL+HOME:移到行首36

25、.CTRL+END:移到列尾37.CTRL+D:向下填充38.CTRL+R:向右填充39.CTRL+:在公式与结果中切换40.CTRL+TAB:在工作薄中移动第30页，共32页。2009 LOTUS CorporationFang wei3141.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页，共32页。2009 LOTUS CorporationFang wei演讲完毕，谢谢观看！第32页，共32页。