EXCEL函数应用

EXCEL函数应用管理部人力资源组2008年9月25日课程大纲课堂讲解篇:函数功能说明与案例讲解(共20个)课后自学篇:基础函数功能说明(25个)EXCEL函数教学目的通过功能说明与案例演示,了解函数的应用范围与功效,激发后续学习函数的兴趣掌握函数使用的方法与技巧充分发挥Excel的强大数据处理功能，提升工作效率函数应用初步演示利用函数在excel中制作工资条Go利用函数将选定区域的偶数行全部做上颜色标记GoEXCEL函数的结构Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。Excel函数结构:也有一些函数是没有参数的,如ROW()左右括号成对出现单一结构嵌套结构参数与参数之间使用半角逗号进行分隔函数参数常用符号或表示方法函数公式中的文本必须用半角引号，如：〝东南汽车〞;而非直接输入东南汽车或“东南汽车”连接符:&如：〝东南〞&〝汽车〞的值为东南汽车空值/空格的表示法:空值：〝〞空格：〝〞相关数学符号:（不等于）;=（大于等于）;=（小于等于）单元格引用表示法:A2;$A$2;$A2;A$2;A2:B7；1:1；1:5；F:F；A：NGo函数列表教学模块函数名称课堂讲解函数(20个）VALUE、IF、SUMIF、COUNTIF、COUNTA、OFFSET、COLUMN、ROW、MOD、VLOOKUP、MATCH、LEFT、RIGHT、MID、REPLACE、SUBSTITUTE、LEN、REPT、INDEX、PERCENTRANK课后自学函数（25个）RANK、QUARTILE、SIGN、SUM、AVERAGE、SUBTOTAL、AND、OR、ABS、ROUND、COUNTBLANK、INT、TRUNC、EXACT、ISEVEN、ISERROR、CONCATENATE、YEAR/MONTH/DAY、DATE、TODAY、WEEKDAY、EDATE、FREQUENCYVALUE用途：将表示数字的文字串转换成数字。语法：VALUE(text)。参数：Text为带引号的文本，或对需要进行文本转换的单元格的引用。它可以是Excel可以识别的任意常数、日期或时间格式。如果Text不属于上述格式，则VALUE函数返回错误值#VALUE!。Value演示Go文本型态的数字不可计算通过value函数转换就可以计算了If:逻辑函数功能：IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果。它的应用很广泛，可以使用函数IF对数值和公式进行条件检测。语法格式：IF(条件,A,B)。其中“条件”表示计算结果为TRUE或FALSE的任意值或表达式，若为常量，视为True。条件参数可使用任何比较运算符。条件返回的结果为真的话，则执行A，否则执行B。IF函数仅可以嵌套七层，超过将会出错。IF函数的简单案例一Go性别为男者,称谓为XX先生;性别为女者,称谓为XX女士姓名性别称谓公式张三男张三先生=IF(B2=男,A2&先生,A2&女士)李四女李四女士案例二:依下述税率表,利用if函数计算个人所得税(课后练习)注意：因IF仅可嵌套七层，所以无法计算月薪超过102000者个人所得税假如A3为存放税前工资的单元格,个人所得税计算公式如下:=IF(A382000,(A3-2000)*40%-10375,IF(A362000,(A3-2000)*35%-6375,IF(A342000,(A3-2000)*30%-3375,IF(A322000,(A3-2000)*25%-1375,IF(A37000,(A3-2000)*20%-375,IF(A34000,(A3-2000)*15%-125,IF(A32500,(A3-2000)*10%-25,IF(A32000,(A3-2000)*5%,0))))))))GoSumif:条件求和函数用途：根据指定条件对若干单元格、区域或引用求和。语法：SUMIF(条件区域，条件，需求和的区域)参数：条件是由数字、逻辑表达式等组成的判定条件。Sumif案例Go单位姓名工资人资组李宁5000总务组陈忠和12000装配组张湘祥8200人资组郭晶晶4000人资组张怡宁3000总务组石智勇5000焊装组田亮7200请统计人资组同仁的工资总额:Sumif(A:A,〝人资组〞,C:C)Countif：条件计数函数。用途：计算区域中满足给定条件的单元格的个数。语法：COUNTIF(统计区域，条件)参数：“统计区域”为需要计算其中满足条件的单元格数目的单元格区域。“条件”为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。Countif案例Go以上述样表为据,完成以下案例:例1：依“师员”字段，统计师员级的人数countif(C:C,〝师〞)；countif(C:C,〝员〞)例2:统计全公司年资大於等於3年的人数及大于等于3年小于5年的人数countif(I:I,〞=3〞)；countif(I:I,〞=3〞)-countif(I:I,〞=5〞)例3:检测到职编号是否有重复值countif(A:A,A2)ABCDEFGHI到职编号单位代码师员单位名称职务代码职务名称到职日期性别工作年限061964B316师服务保证课108课长1996-6-3男12.2104489B322员零件开发课231零件供应仓管员2000-9-20男7.9130230d222师开发四课117资深工程师2003-4-15男5.3150192D32C员乘用车初检线212车检技术员2005-4-8男3.4160350C131师涂装课121助理工程师2006-7-3男2.1170183C432员熔接二课226焊接技术员2008-7-1男0.1061971B322师零件开发课115高级工程师1996-6-3女12.2Counta用途：返回参数组中非空值的数目。利用函数COUNTA可以计算数组或单元格区域中数据项的个数。语法：COUNTA(单元格区域1,单元格区域2……)说明：参数的个数为1～30个。Counta的思考案例例1：如果A1=6.28、A2=3.74，其余单元格为空，则公式“=COUNTA(A1:A7)”的计算结果等于?。例2：统计C列的非空白单元格的表示方法为：？;若整张EXCEL表的每个单元格都有数据,前述函数公式的结果为?Offset用途：以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以是一个单元格或单元格区域（并不返回值）。语法：OFFSET(作为偏移量参照系的引用位置，上下偏移的行数，左右偏移的列数，[height]，[width])。参数：作为偏移量参照系的引用位置：它必须是单元格或相连单元格区域的引用；上(下)偏移的行数：正数代表在起始引用下边;负数代表在起始引用的上边；0代表没有上（下）偏移。左(右)偏移的列数:正数代表在起始引用右边;负数代表在起始引用的左边；0代表没有左（右）偏移。Height：是要返回的引用区域的总行数（必须为正数）Width：是要返回的引用区域的总列数（必须为正数）。Offset的思考题问题一：OFFSET(B1,2,1,4,1)定位到哪几个单元格？问题二：以A1单元格为参照，要定位到红线区域如何写函数：=OFFSET(A1,6,1,2,2)ABC16820312763599385124903452656680787103377815018215Counta、offset的综合应用案例Go要求：当记录增加时，公式自动统计总销量（假设销量存放600数值的单元格为C3）：=SUM(OFFSET(C3,0,0,COUNTA(C:C)-1,1))或=SUM(OFFSET(C2,1,0,COUNTA(C:C)-1,1))注：以上公式只能正确计算不间断的连续数据,如果表格中销量的数据有空白单元格,那么动态名称的引用位置将发生错误总销量3900车型月份销量DE1600DE2300DeⅡ6200PS21500MG3200LS4700LS5400COLUMN用途：返回给定引用的列标。语法：COLUMN(单元格引用)。参数：“单元格引用”为需要得到其列标的单元格，如果省略，则假定函数COLUMN是对所在单元格的引用。实例：公式“=COLUMN(A3)”返回1，=COLUMN(D5)返回?。Row用途：返回给定引用的行号。语法：ROW(单元格引用)。“单元格引用”为需要得到其行号的单元格或单元格区域。实例:利用row建立序号:Go序号到职编号单位代码师员单位名称1061964B316师服务保证课2104489B322员零件开发课3130230d222师开发四课4150192D32C员乘用车初检线5160350C131师涂装课6170183C432员熔接二课7061971B322师零件开发课8061988D172师电装课9062006D162师资料课10062020B215师副料二课11062037B215师副料二课12062051B142员出车业务课13062068A233师营缮课14062075A230师总务组15062123A231员事务行政课16062154C13Q员涂装课研磨贴纸2线MOD：取余用途：返回两数相除的余数，其结果的正负号与除数相同。语法：MOD(被除数，除数)参数：除数不能为零。Mod函数的案例实例1：公式“=MOD(14，4)”返回?；“=MOD(-5，-2)”返回？。实例2:如何利用MOD函数，将整张EXCEL表的偶数行都标上底色GoVlookup返回表格或数组当前行中指定列处的数值。语法：VLOOKUP(索引值，数据区域，列序号，查找方式)参数：索引值为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。数据区域为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。列序号指数据区域中待返回的匹配值的列序号。它等于1时，返回数据区域第一列中的数值；它等于2时，返回数据区域第二列中的数值，以此类推。查找方式为一逻辑值,指明函数VLOOKUP返回时是精确匹配还是近似匹配。如果为1或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于索引值的最大数值；如果为0，函数VLOOKUP将返回精确匹配值。如果找不到，则返回错误值#N/A。Vlookup案例案例一:利用vlookup抓取另一张表中的资料。Go案例二(请参考链接之案例,课后自学)：用vlookup计算所得税,解决IF只能嵌套七层的局限。Gomatch用途：返回在指定方式下与指定数值匹配的数组中元素的相应位置(不是具体的单元格)。语法：MATCH(A，B，C)。参数：A:为需要在数据表中查找的数值或单元格引用。B:是可能包含所要查找的数值的连续单元格区域。C:它说明Excel如何在B中查找A。C的常用值为0，表示函数MATCH查找等于A的第一个数值。注意：MATCH函数返回B中目标值的位置，而不是数值本身。Match函数的案例Go=MATCH(11，A1:A9，0)返回的值是?=match(420,A3:E3,0)返回的值是?ABCDE16821022023024027631032023024039841042023024042751052023024055661062023024061171022023024071028102202302408879102202302409631010220230240Vlookup与match函数的综合运用Go利用VLOOKUP和MATCH函数查找出相应月份和产品的销售额。品名月份SQR7160SQR7110SQR7200SQR7080SQR7300SQR7100一月125,860147,852124,578321,456856,231152,360二月132,589125,487236,541369,852956,231485,820三月235,689132,654258,963456