Excel 培训教材

, 200,9,LOTUS Corporation,*,F,ang wei,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,*, 200,9,LOTUS Corporation,LOTUS 200,9,*,1,Office Excel,函数,&,数据透视表,普及培训教材,2,Excel,普及性的知识,请参看光盘资料，演示,知识点：,1.,一般表格设计、制做,2.,修饰工作表，使表格美观,3.,数据操作：,sum,4.,数据组织分析：排序、筛选、表单,5.,图表制做,3,本次培训目的：主要针对更高一级的常用函数及其应用。,每一项内容都现场示例，请大家认真理解，不明白请当场提出！,4,第一部分：函数,Excel,是办公室自动化中非常重要的一款软件，它不仅仅能够方便的处理表格和进行图形分析，其更强大的功能体现在对数据的自动处理和计算,。,函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。,函数的结构：,函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的形式出现，请在函数名称前面键入等号（,=,）。在创建包含函数的公式时，公式选项板将提供相关的帮助。,示例,1,函数可以嵌套，就是指在某些情况下，您可能需要将某函数作为另一函数的参数使用。例如图,1,中所示的公式使用了嵌套的,AVERAGE,函数，并将结果与,50,相比较。这个公式的含义是：如果单元格,F2,到,F5,的平均值大于,50,，则求,F2,到,F5,的和，否则显示数值,0,。,示例,2,5,使用步骤,1.,单击需要输入函数的单元格 ；,2.,单击插入栏中,“,函数”按钮 ，将会在编辑栏下面出现一个,“,公式选项板,”,，此时,“,名称,”,框将变成,“,函数,”,按钮；,3.,选择函数类别及函数，确定；,4.,当选中所需的函数后，,Excel 2003,将打开,“,公式选项板,”,。用户可以在这个选项板中输入函数的参数，当输入完参数后，在,“,公式选项板,”,中还将显示函数计算的结果；,5.,单击,“,确定,”,按钮，即可完成函数的输入；,示例,3,内容：,1,、求和函数：,SUM SUMIF SUBTOTAL,2,、逻辑函数：,AND OR IF COUNTIF,3,、查询函数：,HLOOKUP,、,VLOOKUP,4,、统计函数：,AVERAGE,、,TRIMMEAN,、,COUNT,、,FREQUENCY,、,MAX,、,MIN,、,MODE,5,、其它：,TRIM,、,EXACT,、,HOUR,、,DAY,、,DAY,、,MONTH,、,YEAR,6,1.,求和函数,重点介绍,SUM,（计算一组参数之和）、,SUMIF,（对满足某一条件的单元格区域求和）、,SUBTOTAL,（分类汇总）的使用 。,示例,4,（,SUMIF),SUM,示例,SUMIF,函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中。,SUBTOTAL,：回列表或数据库中的分类汇总。通常，使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的列表。一旦创建了分类汇总，就可以通过编辑,SUBTOTAL,函数对该列表进行修改,7,SUBTOTAL(function_num, ref1, ref2, .),Function_num,为,1,到,11,（包含隐藏值）或,101,到,111,（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。,Ref1, ref2,为要进行分类汇总计算的,1,到,29,个区域或引用。,示例,5,（,SUBTOTAL),非空值单元格数,乘积,8,2.,逻辑函数,用来判断真假值，或者进行复合检验的,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 (,长工资标准分析）,9,2.,逻辑函数,（三）,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,嵌套,),10,COUNTIF,函数,用来计算给定区域内满足特定条件的单元格的数目。,语法形式为,COUNTIF(range,criteria),。,Range,区域，,criteria,条件。,注：如果,criteria,表示一个固定值，只接输入，如果表示逻辑关系，需加,” ”,如,”=90”,示例,8,（,COUNTIF),11,3.,查询函数,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,),练习,12,MATCH,函数,如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用,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,练习,13,4.,统计函数,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,物料状态统计,14,4.,统计函数,e,、数据集的最大值,MAX,与最小值,MIN,示例,14,f,、数据集中出现频率最多的数,MODE,15,5.,文本函数,*:,连接连个文本字符串,TRIM:,删除数据前后所有多余的空格，用一个空格代替多个空格。如,:,TRIM(“ My Home “)=My home,LEN:,返回单元格中字符的数量。如,:LEN(GREENHEAK VIP)=13,LEFT:,从左起返回确定数量的字符。如,:LEFT(“Beijing”,3)=Bei,RIGTH:,从右起返回确定数量的字符。如,:RIGHT(“Beijing”,4)=jing,MID:,在字符串中任意位置返回确定数量的字符。如,:,MID(422124197608119316,7,8)=19760811,UPPER:,将文本全部转化为大写。如,:UPPER(“join”)=JOIN,LOWER:,将文本全部转化为小写。如,:LOWER(“JOIN”)=join,示例,15,16,6.,其它函数,A,、,EXACT,函数，比较两个字符串是否相同,B,、取得日期,/,时间的部分字段值 。如果需要单独的年份、月份、日数或小时的数据时，可以使用,HOUR,、,DAY,、,MONTH,、,YEAR,函数直接从日期,/,时间中取出需要的数据。,示例,15,公司函数的应用讲解,:,1,、仓库系统的应用,2,、成本系统的应用,3,、皮带轮分解的应用,4,、在线物料需求的应用,17,二、数据透视表,如果一名财务人员，当你向老板汇报公司资产时，拿一堆枯燥的报表去说或许会吃老板的白眼,(,数据堆砌,),，而一个图文并茂的数据表加上您精彩的讲解，却一定会博得赞许。用图表来表达数据比直接堆砌数字要来得更直观一些，往往从数字上看不出来的问题，一转换成图表就马上清晰地暴露出来了。,数据透视图可以用图形的方式显示数据透视表的内容，将包含大量数据的表格变成生动的图形，使数据以更直观的形式表现出来，可以达到比表格更好的效果。,因为数据透视图与包含其源数据的数据透视表是相关联的，所示当数据透视表中的数据改变后，数据透视图也会自动随之改变，也就是说数据透视图具有自动更新功能。,18,使用数据透视表前,表格数据杂乱无序,:,19,使用数据透视表后,通过对数据不同的视角显示数据并对数据进行比较、揭示和分析，从而将数据转化成有意义的信息,:,20,创建数据透视表,1,、单击“数据”菜单，选择“数据透视表”。,2,、在创建数据透视表窗口中，选择数据源单元格区域 。在同一数据表格中，已经建了一个数据透视表，也可以在其基础上再进行修改处理。,3,、选择数据透视表的放置位置（新建工作表、现有工作表），如果选择“现有工作表”，可以指定透视表的单元格位置。,21,4、从右侧控制面版区域的字段列表中，可以拖动字段到数据透视表区域中的“页区域”、“行区域”、“列区域”、“数据区域”进行数据的分析比较。,5、右键单击数据透视表操作区域的字段,选择“字段设置”，在“数据透视表字段”中，可以修改字段名称、分类汇总方式等相关信息。,22,6、可在控制面版拖动字段到区域外以删除字段，并可在同一区域内上下拖动字段以调整位置，也可右键单击字段完成所需的删除和移动。在页区域、行区域和列区域中，每个字段都不能重复出现，但一个字段可以多次放到数据区域。,示例,16,23,特性介绍,正是这些特性使得应用透视表来分析数据对用户形成了很大的吸引力。,1,、内置筛选,数据透视表自动地内置了筛选，从而允许用户只浏览自己关心的细节。,2,、动态布局,数据透视表具有动态布局功能，只要通过拖放数据透视表中的字段到数据透,视表的其他区域，就能够轻易地改变数据的显示格式。,3,、自动汇总报表数据,不管用户决定采用什么字段作为总结，数据都能够自动汇总。用户,还能够改变数据汇总的计算类型。,4,、支持各种数据源,用户能够基于各种各样的数据源创建数据透视表，这些数据源包括,24,字段介绍,当创建数据透视表时，系统会要求用户为下面列出的四个区域选择字段：,1,、 页字段 页字段是数据表中主要层次的筛选。当用户选择页字段时，一定要弄清楚到底想怎样组织自己的数据。,2,、 行字段 行字段是更低一级的细节筛选，也是第二个层次的细节筛选。,3,、 列字段 列字段的筛选层次跟行字段一样。,4,、 数据项 数据项就是想要汇总的字段，通常比较适合用来作为数据项的字段，比如销售额、费用、库存数量等等。,25,示例,17,综合应用,在数据透视表中,你可以对行或列字段的数据项进行分组,.,注意演示步骤！,（附小电影）,26,作业：,1,、函数,IF,应用,2,、函数,VLOOKUP,应用,3,、数据透视表综合作业,谢谢大家参与此次学习！,27,附录：常用快捷键表,CTRL + 1:,打开单元格格式设定,CTRL + 2:,应用格式设定中字体粗体,CTRL + 3:,应用格式设定中字体斜体,CTRL + 4:,应用格式设定中字体下划线,CTRL + 5:,应用格式设定中字体删除线,CTRL + 6:,在筛选模式下打开或关闭筛选,CTRL + 7:,隐藏格式栏,CTRL + 9:,隐藏行,CTRL + 0:,隐藏列,CTRL + -:,删除行或列,28,11.CTRL + W:,关闭当前工作表，不退出,EXCEL,12.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:,打开定位对话框,29,21.CTRL + H:,打开查找与替换对话框,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:,应用字体粗体,30,31.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:,在工作薄中移动,31,41.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 +,向下,(,上,),键,