EXCEL常用函数介绍.ppt

常用函数实例2022/11/241函数参数常用符号或表示方法n函数公式中的文本必须用半角引号，如：函数公式中的文本必须用半角引号，如：东南汽东南汽车车;而非直接输入而非直接输入 东南汽车或东南汽车或“东南汽车东南汽车”n连接符连接符:&:&如：如：东南东南&汽车汽车的值为的值为 东南汽东南汽车车n空值空值/空格的表示法空格的表示法:n 空值：空值：n 空格：空格：n相关数学符号相关数学符号:（不等于）（不等于）;=;=（大于等于）（大于等于）;=;60,IF(B690,优秀秀,合格合格),不合格不合格)4Logical_testValue_if_trueValue_if_false优秀优秀合格合格不合格不合格2022/11/24IF函数应用举例 之一计算奖金无无论公式的数公式的数值填充到任何位置，公式本身填充到任何位置，公式本身的引用指向一个的引用指向一个绝对的的单元格区域。元格区域。绝对引引用符号用符号“$”。单元格引用单元格引用-绝对引用绝对引用公式公式说明（明（结果）果）=IF(A2B2,Over Budget,OK)判断判断第第 1 行是否超出行是否超出预算算(Over Budget)=IF(A3B3,Over Budget,OK)判断判断第第 2 行是否超出行是否超出预算算(OK)IF 函数的简单案例一性别为男者性别为男者,称谓为称谓为XXXX先生先生;性别为女者性别为女者,称谓为称谓为XXXX女士女士=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(计数区域数区域,“计数条件数条件”)142022/11/24F13=COUNTIF(F1:F12,及格)；a在指定的在指定的C13D13F13单元格元格显示平均数示平均数:C13=AVERAGE($C$3:$C$12)；D13=AVERAGE($D$3:$D$12)E13=AVERAGE($e$3:$e$12)bc在指定的在指定的F13H14单元格元格显示及格示及格不及格人数：不及格人数：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以上述样表为据,完成以下案例:n例1：依：依“师员师员”字段，统计师员级的人字段，统计师员级的人数数ncountif($C$2:$C$6,countif($C$2:$C$6,师师)ncountif($C$2:$C$6,countif($C$2:$C$6,员员)n例2:统计全公司年资大於等於统计全公司年资大於等於3 3年的人数年的人数及大于等于及大于等于3 3年小于年小于5 5年的人数年的人数ncountif(I2:I6,countif(I2:I6,=3=3)；ncountif(I2:I6,countif(I2:I6,=3=3)-countif(I2:I6,)-countif(I2:I6,=5=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(区域区域,“条件条件”,求和区域求和区域)222022/11/24Sumif案例 Go请统计人资组同仁的工资总额:Sumif($A$2:$A$8,人资组,$C$2:$C$8)4.average功能：功能：对指定指定单元格或区域内容求平均元格或区域内容求平均值格式：格式：average(n1,n2,，nk)average(n1:nk)252022/11/2410.rank功能：功能：计算数据排位算数据排位格式：格式：rank（number，ref，order）参数：参数：Number：待：待评论数据数据/单元格元格(相相对地址地址)Ref：引用考：引用考虑的区域，一般的区域，一般为绝对地址地址Order：可以省略，控制升：可以省略，控制升/降序降序262022/11/24如果如果 order 为 0（零）或省略，（零）或省略，Microsoft Excel 对数字的排位是基于数字的排位是基于 ref 为按照降序排列的列表。按照降序排列的列表。如果如果 order 不不为零，零，Microsoft Excel 对数字数字的排位是基于的排位是基于 ref 为按照升序排列的列按照升序排列的列表。表。10.rank功能：功能：计算数据排位算数据排位格式：格式：rank（number，ref，order）参数：参数：Number：待：待评论数据数据/单元格元格(相相对地址地址)Ref：引用考：引用考虑的区域，一般的区域，一般为绝对地址地址Order：可以省略，控制升：可以省略，控制升/降序降序282022/11/24=RANK(A1,$A$1:$A$7)11.max,min功能：功能：计算指定区域内的最大算指定区域内的最大/最小最小值格式：格式：max(n1,n2,nk)min(n1,n2,nk)参数参数1.可可选择单元格，如：元格，如：a1,b6,c32.可可选择单元格区域，如：元格区域，如：a1:d4312022/11/2411.max,min功能：功能：计算指定区域内的最大算指定区域内的最大/最小最小值格式：格式：max(n1,n2,nk)min(n1,n2,nk)参数参数1.可可选择单元格，如：元格，如：a1,b6,c32.可可选择单元格区域，如：元格区域，如：a1:d4322022/11/2412.逻辑函数逻辑量量 TRUE，FALSE 逻辑函数函数 函数参数和函数参数和计算算结果都是果都是逻辑量的函数量的函数 and(logical1,logical2,logicaln)or(logical1,logical2,logicaln)not(logical1)332022/11/24andand(logical1,logical2,logicaln)功能：功能：当所有参数的当所有参数的计算算结果都是果都是TRUE时，and函数的函数的计算算结果果为TRUE 否否则，计算算结果果为FALSE “有假有假则假假”342022/11/24oror(logical1,logical2,logicaln)功能：功能：当参数的当参数的计算算结果中含有果中含有TRUE时，or 函数的函数的计算算结果果为TRUE 当所有参数的当所有参数的计算算结果全部果全部为FALSE or函数函数计算算结果果为FALSE“有真有真则真真”352022/11/24notNot(logical)功能：功能：当参数的当参数的计算算结果果为TRUE时，not 函数的函数的计算算结果果为FALSE 当参数的当参数的计算算结果果为FALSE not函数函数计算算结果果为TRUE362022/11/24逻辑函数应用and(logical1，,logicalk)372022/11/2413.日期函数Now（）：当前日期（）：当前日期时间Today（）：当前日期（）：当前日期382022/11/2414.查找函数功能：功能：查找指定数据所找指定数据所对应的数据的数据值：格式：格式：LOOKUP(lookup_value,lookup_vector,result_vector)参数：参数：lookup_value：指定：指定查找找对象象lookup_vector：指定定位区域：指定定位区域result_vector：指定：指定查找找结果区域果区域要求要求lookup_vector和和result_vector的区域必的区域必须对等等392022/11/24如果如果 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)”返回男返回男(张雷的性雷的性别)412022/11/24=LOOKUP(F4,$B$3:$B$8,$D$3:$D$8)=LOOKUP(100,B2:B10,C2:C10)查找函数(1)HLOOKUP在表格的首行在表格的首行查找指定的数找指定的数值，并在表格指，并在表格指定行的同一列中返回一个数定行的同一列中返回一个数值。当比。当比较值位位于数据表的首行，并且要于数据表的首行，并且要查找下面找下面给定行中定行中的数据的数据时，请使用函数使用函数 HLOOKUP。492022/11/24语法法 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需要在其中需要在其中查找数据的表格数找数据的表格数组。552022/11/24col_index_num为table_array中待返回的中待返回的值的列号；的列号；Range_lookup为一一逻辑值，指明函数，指明函数VLOOKUP返回返回时是精确匹配是精确匹配还是近似匹配是近似匹配如果如果为TRUE或省略，或省略，则返回近似匹配返回近似匹配值，也，也就是就是说，如果找不到精确匹配，如果找不到精确匹配值，则返回小于返回小于lookup_value的最大数的最大数值；如；如range_value为FALSE，函数，函数VLOOKUP将返将返回精确匹配回精确匹配值。如果找不到，。如果找不到，则返回返回错误值#N/A。Lookup有一个大哥有一个大哥Vlookup函数。两函数。两兄弟有很多相似之兄弟有很多相似之处，但大哥本，但大哥本领更大。更大。Vlookup用用对比数与一个比数与一个“表表”进行行对比，而不是比，而不是Lookup函数的某函数的某1列或列或1行，行，并且并且Vlookup可以可以选择采用精确采用精确查询或或是模糊是模糊查询方式，而方式，而Lookup只有模糊只有模糊查询。Vlookup函数来提取第函数来提取第1个例子中的工个例子中的工资税率税率结果。函数公式果。函数公式为“=VLOOKUP(F4,$B$3:$D$8,3,TRUE)”。这个个结果是怎么来的果是怎么来的?用用F4中的第中的第1个收个收入数入数“$123,409”，与左，与左侧表的表的“收入收入最低最低”各档数据各档数据(“$B$3:$B$8”)进行行对比，比，虽然然“$123,409”在在“收入最低收入最低”各档数中没有完全一致的数据与之匹配，各档数中没有完全一致的数据与之匹配，但是会与其中小于它的最大数但是会与其中小于它的最大数“$58,501”相匹配。相匹配。这样，同一行，同一行对应的的“36.00%”就提取出来了。就提取出来了。i3=VLOOKUP(H3,$B$2:$D$6,3,FALSE)”如果想如果想查找学生的考找学生的考试成成绩，公式，公式“=VLOOKUP(A3,A1:G5,4,FALSE)”结果果为92(张雷的雷的计算机成算机成绩)；则公式公式“=HLOOKUP(C1,A1:G5,4,FALSE)”的的结果果40(米雪的高等数学成米雪的高等数学成绩)。在在B11格中格中输入公式入公式=VLOOKUP(A11,$A$4:$B$8,2,TRUE)MATCH函数 含含义：返回指定数：返回指定数值在指定在指定数数组区域中区域中的相的相对位置位置。MATCH(lookup_value,lookup_array,match_type)lookup_value：需要在数据表中：需要在数据表中查找的找的值。lookup_array：可能包含有所要：可能包含有所要查找数找数值的的连续的的单元格区域。元格区域。match_type：为1时，查找小于或等于找小于或等于lookup_value的最大数的最大数值，lookup_array必必须按升序排列：按升序排列：为0时，查找等于找等于lookup_value的第一的第一个数个数值，lookup_array按任意按任意顺序排列：序排列：为-1时，查找大于或等于找大于或等于lookup_value的最小数的最小数值，lookup_array必必须按降序按降序排列。排列。=MATCH(39,B2:B5,1)因因为没有完全符没有完全符合的合的项目，所以会目，所以会传回范回范围 B2:B5 中中下一个下一个较小的小的值(38)。(2)=MATCH(41,B2:B5,0)范范围 B2:B5 中中 41 的位置。的位置。(4)=MATCH(40,B2:B5,-1)因因为 B2:B5 不不是依是依递减减顺序排列，所以会序排列，所以会传回回错误。(#N/A)文本函数MID(text,start_num,num_chars)text 字符串表达式，从中返回字符。字符串表达式，从中返回字符。start_num text 中被提取的字符部分的开始位置。中被提取的字符部分的开始位置。num_chars 要返回的字符数。要返回的字符数。M=4100 A1=Mid(M,1,1)A1=4 A2=Mid(M,2,2)A2=10 TRUNC（number,decimals）number 待做截取待做截取处理的数理的数值 decimals 指明需保留小数点后面的位数。可指明需保留小数点后面的位数。可选项，忽略它，忽略它则截去所有的小数部分截去所有的小数部分 TRUNC（89.985，2）=89.98 TRUNC（89.985）=89 TRUNC（89.985，-1）=80 注意：第二个参数可以注意：第二个参数可以为负数，表示数，表示为小数点小数点左左边指定位数后面的部分截去，即均以指定位数后面的部分截去，即均以0记。与取整与取整类似，比如参数似，比如参数为1即取整到十分位，即取整到十分位，如果是如果是-1，则是取整到十位，以此是取整到十位，以此类推。推。从身份证提取个人信息420102198706121717判断性判断性别：倒数第二位是奇数就：倒数第二位是奇数就为男，男，偶数就偶数就为女。女。=IF(MID(C1,17,1)/2=TRUNC(MID(C1,17,1)/2),女女,男男)TEXT主要功能：根据指定的数根据指定的数值格式将相格式将相应的数字的数字转换为文本形式文本形式 使用格式：TEXT(value,format_text)参数说明：value代表需要代表需要转换的数的数值或引用的或引用的单元元格；格；format_text为指定文字形式的数字格式指定文字形式的数字格式 TEXT函数从身份证提取出生年月=TEXT(MID(C1,7,8),0000-00-00)算年龄YEAR(TODAY()-YEAR(出生出生时间单元元格格)算工算工龄YEAR(TODAY()-YEAR(入入职时间单元元格格)2022/11/2478END