EXCEL常用函数介绍

常用函数实例2019/8/151函数参数常用符号或表示方法函数公式中的文本必须用半角引号，如：〝东南汽车〞;而非直接输入东南汽车或“东南汽车”连接符:&如：〝东南〞&〝汽车〞的值为东南汽车空值/空格的表示法:空值：〝〞空格：〝〞相关数学符号:（不等于）;=（大于等于）;=（小于等于）单元格引用表示法:A2;$A$2;$A2;A$2;A2:B7；1:1；1:5；F:F；A：NGo1.IF函数语法格式：IF(logical_test,value_if_true,value_if_false)函数功能：3Logical_testValue_if_trueValue_if_false得到If_Value2019/8/15IF=IF(B660,IF(B690,优秀,合格),不合格)4Logical_testValue_if_trueValue_if_false优秀合格不合格2019/8/15IF函数应用举例之一——计算奖金无论公式的数值填充到任何位置，公式本身的引用指向一个绝对的单元格区域。绝对引用符号“$”。单元格引用-绝对引用公式说明（结果）=IF(A2B2,OverBudget,OK)判断第1行是否超出预算(OverBudget)=IF(A3B3,OverBudget,OK)判断第2行是否超出预算(OK)IF函数的简单案例一性别为男者,称谓为XX先生;性别为女者,称谓为XX女士姓名性别称谓公式张三男张三先生=IF(B2=男,A2&先生,A2&女士)李四女李四女士=IF(A289,A,IF(A279,B,IF(A269,C,IF(A259,D,F))))=IF(A389,A,IF(A379,B,IF(A369,C,IF(A359,D,F))))=IF(A489,A,IF(A479,B,IF(A469,C,IF(A459,D,F))))1000以下，统计判断为“人气一般”，大于等于1000为“人气强大”=IF(B21000,great,soso)2.countif功能：为满足指定条件的单元计数格式：Countif(计数区域,“计数条件”)142019/8/15F13=COUNTIF(F1:F12,及格)；a\在指定的C13\D13\F13单元格显示平均数:C13=AVERAGE($C$3:$C$12)；D13==AVERAGE($D$3:$D$12)E13==AVERAGE($e$3:$e$12)b\c\在指定的F13\…H14单元格显示及格\不及格人数：F13=COUNTIF(F1:F12,及格)；F14=COUNTIF(F1:F12,不及格)G13=COUNTIF(g1:g12,及格)；G14=COUNTIF(g1:g12,不及格)H13=COUNTIF(h1:h12,及格)；H14=COUNTIF(h1:h12,不及格)Countif案例Go以上述样表为据,完成以下案例: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.2例1：依“师员”字段，统计师员级的人数countif($C$2:$C$6,〝师〞)countif($C$2:$C$6,〝员〞)例2:统计全公司年资大於等於3年的人数及大于等于3年小于5年的人数countif(I2:I6,〞=3〞)；countif(I2:I6,〞=3〞)-countif(I2:I6,〞=5〞)公式说明（结果）计算第一列中苹果所在单元格的个数=COUNTIF(A2:A5,apples)计算第一列中桃所在单元格的个数(1)=COUNTIF(A2:A5,A4)计算第一列中柑桔和苹果所在单元格的个数(3)=COUNTIF(A2:A5,A3)+COUNTIF(A2:A5,A2)计算第二列中值大于55的单元格个数(2)=COUNTIF(B2:B5,55)计算第二列中值不等于75的单元格个数(3)=COUNTIF(B2:B5,75”)3.sumif功能：对满足指定条件的单元内容求和格式：Sumif(区域,“条件”,[求和区域])222019/8/15Sumif案例Go单位姓名工资人资组李宁5000总务组陈忠和12000装配组张湘祥8200人资组郭晶晶4000人资组张怡宁3000总务组石智勇5000焊装组田亮7200请统计人资组同仁的工资总额:Sumif($A$2:$A$8,〝人资组〞,$C$2:$C$8)4.average功能：对指定单元格或区域内容求平均值格式：average(n1,n2,……，nk)average(n1:nk)252019/8/1510.rank功能：计算数据排位格式：rank（number，ref，order）参数：Number：待评论数据/单元格(相对地址)Ref：引用考虑的区域，一般为绝对地址Order：可以省略，控制升/降序262019/8/15如果order为0（零）或省略，MicrosoftExcel对数字的排位是基于ref为按照降序排列的列表。如果order不为零，MicrosoftExcel对数字的排位是基于ref为按照升序排列的列表。10.rank功能：计算数据排位格式：rank（number，ref，order）参数：Number：待评论数据/单元格(相对地址)Ref：引用考虑的区域，一般为绝对地址Order：可以省略，控制升/降序282019/8/15=RANK(A1,$A$1:$A$7)11.max,min功能：计算指定区域内的最大/最小值格式：max(n1,n2,……,nk)min(n1,n2,……,nk)参数1.可选择单元格，如：a1,b6,c32.可选择单元格区域，如：a1:d4312019/8/1511.max,min功能：计算指定区域内的最大/最小值格式：max(n1,n2,……,nk)min(n1,n2,……,nk)参数1.可选择单元格，如：a1,b6,c32.可选择单元格区域，如：a1:d4322019/8/1512.逻辑函数逻辑量{TRUE，FALSE}逻辑函数函数参数和计算结果都是逻辑量的函数and(logical1,logical2,……,logicaln)or(logical1,logical2,……,logicaln)not(logical1)332019/8/15andand(logical1,logical2,……,logicaln)功能：当所有参数的计算结果都是TRUE时，and函数的计算结果为TRUE否则，计算结果为FALSE“有假则假”342019/8/15oror(logical1,logical2,……,logicaln)功能：当参数的计算结果中含有TRUE时，or函数的计算结果为TRUE当所有参数的计算结果全部为FALSEor函数计算结果为FALSE“有真则真”352019/8/15notNot(logical)功能：当参数的计算结果为TRUE时，not函数的计算结果为FALSE当参数的计算结果为FALSEnot函数计算结果为TRUE362019/8/15逻辑函数应用and(logical1，……,logicalk)372019/8/1513.日期函数Now（）：当前日期时间Today（）：当前日期382019/8/1514.查找函数功能：查找指定数据所对应的数据值：格式：LOOKUP(lookup_value,lookup_vector,result_vector)参数：lookup_value：指定查找对象lookup_vector：指定定位区域result_vector：指定查找结果区域要求lookup_vector和result_vector的区域必须对等392019/8/15如果LOOKUP找不到lookup_value，则它与lookup_vector中小于或等于lookup_value的最大值匹配。如果lookup_value小于lookup_vector中的最小值，则LOOKUP会提供#N/A错误值。查找函数LOOKUP(lookup_value,lookup_vector,result_vector)公式“=LOOKUP(A5,A3:A8,B3:B8)”返回22(张雷的年龄)公式“=LOOKUP(A5,A3:A8,c3:c8)”返回男(张雷的性别)412019/8/15=LOOKUP(F4,$B$3:$B$8,$D$3:$D$8)=LOOKUP(100,B2:B10,C2:C10)查找函数(1)HLOOKUP在表格的首行查找指定的数值，并在表格指定行的同一列中返回一个数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数HLOOKUP。492019/8/15语法HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)Lookup_value为需要在数据表第一行中进行查找的数值。Table_array为需要在其中查找数据的数据表。Row_index_num为table_array中待返回的匹配值的行序号。Row_index_num为1时，返回table_array第一行的数值。row_index_num为2时，返回table_array第二行的数值，以此类推。如果row_index_num小于1，函数HLOOKUP返回错误值#VALUE!；如果row_index_num大于table_array的行数，函数HLOOKUP返回错误值#REF!。Range_lookup为一逻辑值，指明函数HLOOKUP查找时是精确匹配，还是近似匹配。如果为TRUE或省略，则返回近似匹配值。也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值。如果lookup_value为FALSE，函数HLOOKUP将查找精确匹配值，如果找不到，则返回错误值#N/A。=HLOOKUP(Axles,A1:C4,2,TRUE)在首行查找Axles，并返回同列中第2行的值。(4)=HLOOKUP(“Bearings”,A1:C4,3,FALSE)在首行查找Bearings，并返回同列中第3行的值。=HLOOKUP(Bolts,A1:C4,4)在首行查找Bolts，并返回同列中第4行的值。(11)(2)VLOOKUP功能：在表格数组首列查找指定的值，并返回表格数组当前行中指定列的值语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)lookup_value需要在表格数组第一列中查找的值，它可以为数值、引用或者是文本；table_array需要在其中查找数据的表格数组。552019/8/15col_index_num为table_array中待返回的值的列号；Range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如range_value为FALSE，函数VLOOKUP将返回精确匹配值。如果找不到