EXCEL常用函数介绍课件

1、EXCEL常用函数介绍单击此处输入你的副标题，请尽量言简意赅的阐述观点。EXCEL常用函数介绍EXCEL常用函数介绍单击此处输入你的副标题，请尽量言简意赅的阐述观点。常用函数实例2020/11/172你真棒常用函数实例2022/7/232函数参数常用符号或表示方法函数公式中的文本必须用半角引号，如：东南汽车;而非直接输入 东南汽车或“东南汽车”连接符: & 如：东南& 汽车的值为 东南汽车空值/空格的表示法: 空值： 空格： 相关数学符号:（不等于）;=（大于等于）;60, IF(B690,优秀,合格), 不合格)Logical_testValue_if_trueValue_if_false优

2、秀合格不合格2022/7/235IF函数应用举例 之一计算奖金无论公式的数值填充到任何位置，公式本身的引用指向一个绝对的单元格区域。绝对引用符号“$”。单元格引用-绝对引用公式说明（结果）=IF(A2B2,Over Budget,OK)判断第 1 行是否超出预算 (Over Budget)=IF(A3B3,Over Budget,OK)判断第 2 行是否超出预算 (OK)IF 函数的简单案例一性别为男者,称谓为XX先生;性别为女者,称谓为XX女士=IF(A289,A,IF(A279,B, IF(A269,C,IF(A259,D,F)=IF(A389,A,IF(A379,B, IF(A369,C

3、,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( 计数区域, “计数条件” )2022/7/2315F13=COUNTIF(F1:F12,及格)；a在指定的C13D13F13单元格显示平均数:C13=AVERAGE($C$3:$C$12)；D13=AVERAGE($D$3:$D$12)E13=AVERAGE($e$3:$e$12)bc在指定的F1

4、3H14单元格显示及格不及格人数：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以上述样表为据,完成以下案例:例1：依“师员”字段，统计师员级的人数countif($C$2:$C$6,师)countif($C$2:$C$6,员)例2:统计全公司年资大於等於3年的人数及大于等于3年小于5年的人数countif(I2:I6, =3)；countif

5、(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功能：对满足指定条件的单元内容求和格式：Sumi

6、f( 区域, “条件”,求和区域 )2022/7/2323Sumif案例 Go请统计人资组同仁的工资总额:Sumif($A$2:$A$8, 人资组,$C$2:$C$8)4. average功能：对指定单元格或区域内容求平均值格式：average(n1,n2,，nk) average(n1:nk) 2022/7/232610. rank功能：计算数据排位格式：rank（number，ref，order）参数：Number：待评论数据/单元格(相对地址)Ref：引用考虑的区域，一般为绝对地址Order：可以省略，控制升/降序2022/7/2327如果 order 为 0（零）或省略，Microso

7、ft Excel 对数字的排位是基于 ref 为按照降序排列的列表。 如果 order 不为零，Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。10. rank功能：计算数据排位格式：rank（number，ref，order）参数：Number：待评论数据/单元格(相对地址)Ref：引用考虑的区域，一般为绝对地址Order：可以省略，控制升/降序2022/7/2329=RANK(A1,$A$1:$A$7)11. max, min功能：计算指定区域内的最大/最小值格式：max(n1,n2,nk)min(n1,n2,nk)参数可选择单元格，如：a1,b6,c3可

8、选择单元格区域，如：a1:d42022/7/233211. max, min功能：计算指定区域内的最大/最小值格式：max(n1,n2,nk)min(n1,n2,nk)参数可选择单元格，如：a1,b6,c3可选择单元格区域，如：a1:d42022/7/233312.逻辑函数逻辑量 TRUE，FALSE 逻辑函数 函数参数和计算结果都是逻辑量的函数 and(logical1,logical2,logicaln) or(logical1,logical2,logicaln) not(logical1)2022/7/2334andand(logical1,logical2,logicaln)功能：

9、当所有参数的计算结果都是TRUE时， and函数的计算结果为TRUE 否则，计算结果为FALSE “有假则假”2022/7/2335oror(logical1,logical2,logicaln)功能： 当参数的计算结果中含有TRUE时， or 函数的计算结果为TRUE 当所有参数的计算结果全部为FALSE or函数计算结果为FALSE “有真则真”2022/7/2336notNot (logical)功能： 当参数的计算结果为TRUE时， not 函数的计算结果为FALSE 当参数的计算结果为FALSE not函数计算结果为TRUE2022/7/2337逻辑函数应用and (logical1

10、，,logicalk)2022/7/233813. 日期函数Now（）：当前日期时间Today（）：当前日期2022/7/233914.查找函数功能：查找指定数据所对应的数据值：格式：LOOKUP(lookup_value, lookup_vector, result_vector) 参数：lookup_value：指定查找对象lookup_vector：指定定位区域result_vector：指定查找结果区域要求lookup_vector和result_vector的区域必须对等2022/7/2340如果 LOOKUP 找不到 lookup_value，则它与 lookup_vector 中

11、小于或等于 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)” 返回男(张雷的性别) 2022/7/2342=LOOKUP(F4,$B$3:$B$8,$D$3:$D$8) =LOOKUP(100,B2:B10,C2:C10)查找函数(1) HLO

12、OKUP在表格的首行查找指定的数值，并在表格指定行的同一列中返回一个数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数 HLOOKUP。 2022/7/2350语法 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

13、_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 将查找

14、精确匹配值，如果找不到，则返回错误值 #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,rang

15、e_lookup)lookup_value需要在表格数组第一列中查找的值，它可以为数值、引用或者是文本；table_array需要在其中查找数据的表格数组。2022/7/2356col_index_num为table_array中待返回的值的列号；Range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如range_value为FALSE，函数VLOOKUP将返回精确匹配值。如果找不到，则返回错误值#N/A。 Lookup有一个大哥Vlookup函

16、数。两兄弟有很多相似之处，但大哥本领更大。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”相匹配。这样，同一

17、行对应的“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：需要在数据表中

18、查找的值。 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