Excel 培训教材

©2009LOTUSCorporationFangwei1OfficeExcel函数&数据透视表普及培训教材©2009LOTUSCorporationFangwei2Excel普及性的知识请参看光盘资料，演示》》知识点：1.一般表格设计、制做2.修饰工作表，使表格美观3.数据操作：sum4.数据组织分析：排序、筛选、表单5.图表制做©2009LOTUSCorporationFangwei3本次培训目的：主要针对更高一级的常用函数及其应用。每一项内容都现场示例，请大家认真理解，不明白请当场提出！©2009LOTUSCorporationFangwei4第一部分：函数Excel是办公室自动化中非常重要的一款软件，它不仅仅能够方便的处理表格和进行图形分析，其更强大的功能体现在对数据的自动处理和计算。函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。函数的结构：函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的形式出现，请在函数名称前面键入等号（=）。在创建包含函数的公式时，公式选项板将提供相关的帮助。示例1函数可以嵌套，就是指在某些情况下，您可能需要将某函数作为另一函数的参数使用。例如图1中所示的公式使用了嵌套的AVERAGE函数，并将结果与50相比较。这个公式的含义是：如果单元格F2到F5的平均值大于50，则求F2到F5的和，否则显示数值0。示例2©2009LOTUSCorporationFangwei5使用步骤1.单击需要输入函数的单元格；2.单击插入栏中“函数”按钮，将会在编辑栏下面出现一个“公式选项板”，此时“名称”框将变成“函数”按钮；3.选择函数类别及函数，确定；4.当选中所需的函数后，Excel2003将打开“公式选项板”。用户可以在这个选项板中输入函数的参数，当输入完参数后，在“公式选项板”中还将显示函数计算的结果；5.单击“确定”按钮，即可完成函数的输入；示例3内容：1、求和函数：SUMSUMIFSUBTOTAL2、逻辑函数：ANDORIFCOUNTIF3、查询函数：HLOOKUP、VLOOKUP4、统计函数：AVERAGE、TRIMMEAN、COUNT、FREQUENCY、MAX、MIN、MODE5、其它：TRIM、EXACT、HOUR、DAY、DAY、MONTH、YEAR©2009LOTUSCorporationFangwei61.求和函数重点介绍SUM（计算一组参数之和）、SUMIF（对满足某一条件的单元格区域求和）、SUBTOTAL（分类汇总）的使用。示例4（SUMIF)SUM示例SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中。SUBTOTAL：回列表或数据库中的分类汇总。通常，使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的列表。一旦创建了分类汇总，就可以通过编辑SUBTOTAL函数对该列表进行修改©2009LOTUSCorporationFangwei7SUBTOTAL(function_num,ref1,ref2,...)Function_num为1到11（包含隐藏值）或101到111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。Ref1,ref2,为要进行分类汇总计算的1到29个区域或引用。示例5（SUBTOTAL)非空值单元格数乘积©2009LOTUSCorporationFangwei82.逻辑函数用来判断真假值，或者进行复合检验的Excel函数，我们称为逻辑函数。在Excel中提供了六种逻辑函数。即AND、OR、NOT、FALSE、IF、TRUE函数。示例6(机器调试自动分析）（一）AND函数所有参数的逻辑值为真时返回TRUE；只要一个参数的逻辑值为假即返回FALSE。简言之，就是当AND的参数全部满足某一条件时，返回结果为TRUE，否则为FALSE。语法为AND(logical1,logical2,...)，其中Logical1,logical2,...表示待检测的1到30个条件值（二）OR函数指在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE。示例7(长工资标准分析）©2009LOTUSCorporationFangwei92.逻辑函数（三）IF函数。IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此If函数也称之为条件函数。语法：IF(logical_test,value_if_true,value_if_false)。条件条件为true时的结果条件为false时的结果示例8（IF)函数IF可以嵌套七层，用value_if_false及value_if_true参数可以构造复杂的检测条件。示例8（IF嵌套)©2009LOTUSCorporationFangwei10COUNTIF函数用来计算给定区域内满足特定条件的单元格的数目。语法形式为COUNTIF(range,criteria)。Range区域，criteria条件。注：如果criteria表示一个固定值，只接输入，如果表示逻辑关系，需加””,如”=90”示例8（COUNTIF)©2009LOTUSCorporationFangwei113.查询函数HLOOKUP、VLOOKUP函数函数可以返回向量（单行区域或单列区域）或数组中的数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，使用函数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_num为相对列号。Range_lookup为一逻辑值，指明函数HLOOKUP查找时是精确匹配，还是近似匹配。示例9（VLOOKUP)练习©2009LOTUSCorporationFangwei12MATCH函数如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH函数语法：MATCH(lookup_value,lookup_array,match_type)为需要在Look_array中查找的数值，可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用可能包含所要查找的数值的连续单元格区域为数字-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练习©2009LOTUSCorporationFangwei134.统计函数a、求参数的算术平均值函数AVERAGE语法形式为AVERAGE(number1,number2,...)其中Number1,number2,...为要计算平均值的1～30个参数。b、求数据集的内部平均值TRIMMEAN函数TRIMMEAN先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。c、求单元格个数的统计函数COUNT语法形式为COUNT(value1,value2,...)d、求区域中数据的频率分布FREQUENCY语法形式为FREQUENCY(data_array,bins_array)其中data_array为一数组或对一组数值的引用,bins_array为进行频率计算的分段点.示例10示例11示例12成绩分布统计示例13物料状态统计©2009LOTUSCorporationFangwei144.统计函数e、数据集的最大值MAX与最小值MIN示例14f、数据集中出现频率最多的数MODE©2009LOTUSCorporationFangwei155.文本函数*:连接连个文本字符串TRIM:删除数据前后所有多余的空格，用一个空格代替多个空格。如:TRIM(“MyHome“)=MyhomeLEN:返回单元格中字符的数量。如:LEN(GREENHEAKVIP)=13LEFT:从左起返回确定数量的字符。如:LEFT(“Beijing”,3)=BeiRIGTH:从右起返回确定数量的字符。如:RIGHT(“Beijing”,4)=jingMID:在字符串中任意位置返回确定数量的字符。如:MID(422124197608119316,7,8)=19760811UPPER:将文本全部转化为大写。如:UPPER(“join”)=JOINLOWER:将文本全部转化为小写。如:LOWER(“JOIN”)=join示例15©2009LOTUSCorporationFangwei166.其它函数A、EXACT函数，比较两个字符串是否相同B、取得日期/时间的部分字段值。如果需要单独的年份、月份、日数或小时的数据时，可以使用HOUR、DAY、MONTH、YEAR函数直接从日期/时间中取出需要的数据。示例15公司函数的应用讲解:1、仓库系统的应用2、成本系统的应用3、皮带轮分解的应用4、在线物料需求的应用©2009LOTUSCorporationFangwei17二、数据透视表如果一名财务人员，当你向老板汇报公司资产时，拿一堆枯燥的报表去说或许会吃老板的白眼(数据堆砌)，而一个图文并茂的数据表加上您精彩的讲解，却一定会博得赞许。用图表来表达数据比直接堆砌数字要来得更直观一些，往往从数字上看不出来的问题，一转换成图表就马上清晰地暴露出来了。数据透视图可以用图形的方式显示数据透视表的内容，将包含大量数据的表格变成生动的图形，使数据以更直观的形式表现出来，可以达到比表格更好的效果。因为数据透视图与包含其源数据的数据透视表是相关联的，所示当数据透视表中的数据改变后，数据透视图也会自动随之改变，也就是说数据透视图具有自动更新功能。©2009LOTUSCorporationFangwei18使用数据透视表前,表格数据杂乱无序:©2009LOTUSCorporationFangwei19使用数据透视表后,通过对数据不同的视角显示数据并对数据进行比较、揭示和分析，从而将数据转化成有意义的信息:©2009LOTUSCorporationFangwei20创建数据透视表1、单击“数据”菜单，选择“数据透视表”。2、在＂创建数据透视表＂窗口中，选择数据源单元格区域。在同一数据表格中，已经建了一个数据透视表，也可以在其基础上再进行修改处理。3、选择数据透视表的放置位置（新建工作表、现有工作表），如果选择“现有工作表”，可以指定透视表的单元格位置。©2009LOTUSCorporationFangwei214、从右侧控制面版区域的字段列表中，可以拖动字段到数据透视表区域中的“页区域”、“行区域”、“列区域”、“数据区域”进行数据的分析比较。5、右键单击数据透视表操作区域的字段,选择“字段设置”，在“数据透视表字段”中，可以修改字段名称、分类汇总方式等相关信息。©2009LOTUSCorporationFangwei226、可在控制面版拖动字段到区域外以删除字段，并可在同一区域内上下拖动字段以调整位置，也可右键单击字段完成所需的删除和移动。在页区域、行区域和列区域中，每个字段都不能重复出