2.5　其他函数

2.5.1　AVERAGE：求平均值函数

公式：=AVERAGE(row_num,column_num,abs_num,a1,sheet_text)

要注意的知识点

第1点：AVERAGE函数用于求平均值。

第2点：此函数的参数不支持错误值。

案例76　使用AVERAGE函数求单元格区域的平均值

案例及公式如下图所示。使用AVERAGE函数可以求单元格区域A1:A3的平均值。

公式

=AVERAGE(A1:A3)

公式解释

A1:A3这3个单元格的平均值刚好是80。

本案例视频文件：02/案例76 使用AVERAGE函数求单元格区域的平均值

2.5.2　AVERAGEIF：单条件求平均值函数

公式：=AVERAGEIF(range,criteria,average_range)

要注意的知识点

第1点：AVERAGEIF函数用于按条件求平均值。

第2点：此函数有3个参数，第1参数：条件所应用的区域；第2参数：条件；第3参数：求平均值的数据区域。

第3点：如果第3参数和第1参数一样，那么第3参数可以不写。

案例77　使用AVERAGEIF函数求单元格区域中大于或等于某个值的值的平均值

案例及公式如下图所示。使用AVERAGEIF函数可以求A1:A4单元格区域中大于或等于90的值的平均值。

公式

=AVERAGEIF(A1:A4,">=90")

公式解释

第1参数：单元格区域A1:A4，即条件所应用的区域。

第2参数：条件，即大于或等于90。

第3参数：和第1参数一样，所以第3参数可以省略。

本案例视频文件：02/案例77 使用AVERAGEIF函数求单元格区域中大于或等于某个值的值的平均值

2.5.3　AVERAGEIFS：多条件求平均值函数

公式：=AVERAGEIFS(average_range,criteria_range,criteria,...)

要注意的知识点

第1点：AVERAGEIFS函数用于多条件求平均值。

第2点：此函数的参数不确定。举个例子说明，如果有两个条件，即5个参数，则第1参数：求平均值的区域；第2参数：条件1所应用的区域；第3参数：条件1；第4参数：条件2所应用的区域；第5参数：条件2。

第4点：此函数是Excel 2007版本及以上版本才有的。

案例78　使用AVERAGEIFS函数求大于300且小于800的值的平均值

案例及公式如下图所示。此公式要求A1:A5单元格区域中大于300且小于800的值的平均值。

公式

=AVERAGEIFS(A1:A5,A1:A5,">300",A1:A5,"<800")

公式解释

第1参数：要求平均值的单元格区域A1:A5。

第2参数：条件1所应用的单元格区域A1:A5。

第3参数：条件1，即>300。

第4参数：条件2所应用的单元格区域。

第5参数：条件2，即<800。

本案例视频文件：02/案例78使用AVERAGEIFS函数求大于300且小于800的值的平均值

2.5.4　COUNT：计算数字个数函数

公式：=COUNT(value1,value2,...)

要注意的知识点

第1点：COUNT函数用于统计数值型数据的个数，且包含错误值。

第2点：此函数只有一个参数。

案例79　使用COUNT函数统计单元格区域中的数据有多少个为数值型

案例及公式如下图所示。使用COUNT函数可以统计A1:A5单元格区域中的数据有多少个为数值型的。

公式

=COUNT(A1:A5)

公式解释

COUNT函数用于统计数值型数据的个数，其中A2单元格中是文本型数据，所以返回3。

本案例视频文件：02/案例79 使用COUNT函数统计单元格区域中的数据有多少个为数值型

2.5.5　COUNTA：非空计数函数

公式：=COUNTA(value1,value2,...)

要注意的知识点

COUNTA函数用于统计非空单元格的个数。

案例80　统计单元格区域中非空单元格的个数

案例及公式如下图所示。这里统计A1:A5单元格区域中非空单元格的个数。

公式

=COUNTA(A1:A5)

公式解释

在使用COUNTA函数统计数据时，错误值数据也会被统计。

本案例视频文件：02/案例80 统计单元格区域中非空单元格的个数

2.5.6　COUNTIF：单条件计数函数

公式：=COUNTIF(range,criteria)

要注意的知识点

第1点：COUNTIF函数用于按条件统计数据个数。

第2点：此函数有两个参数，第1参数：条件所应用的单元格区域（一定要是单元格区域）；第2参数：条件。

案例81　使用COUNTIF函数统计字符

案例及公式如下图所示。这里要统计A1:A5单元格区域中有多少个男的。

公式

=COUNTIF(A1:A5,"男")

公式解释

第1参数：要统计的条件所应用的单元格区域。

第2参数：条件是“男”。

本案例视频文件：02/案例81 使用COUNTIF函数统计字符

2.5.7　COUNTIFS：多条件计数函数

公式：=COUNTIFS(criteria_range,criteria,...)

要注意的知识点

第1点：COUNTIFS函数的参数不确定。

第2点：此函数如果有1个条件，则有2个参数，其中第1参数：条件所在的区域；第2参数：条件。

第3点：此函数如果有2个条件，则有4个参数，其中第1参数：条件1所在的区域；第2参数：条件1；第3参数：条件2所在的区域；第4参数：条件2，依此类推。

第4点：此函数的作用是多条件计数。

案例82　使用COUNTIFS函数统计业务员是“曹丽”且销量大于500的记录个数

案例及公式如下图所示。这里使用COUNTIFS函数统计业务员是“曹丽”且销量大于500的记录的个数。

公式

=COUNTIFS(A2:A5,"曹丽",B2:B5,">500")

公式解释

A2:A5为条件1所应用的区域。“曹丽”是条件1。

B2:B5为条件2所应用的区域。“>500”是条件2。

本案例视频文件：02/案例82 使用COUNTIFS函数统计业务员是“曹丽”且销量大于500的记录个数

2.5.8　SUM：求和函数

公式：=SUM(number1,number2,...)

要注意的知识点

第1点：SUM函数用于求和。

第2点：此函数不能计算错误值。

案例83　使用SUM函数求多个工作表中的值的和，但不包括当前的工作表

案例及公式如下图所示。使用SUM函数可以求多个工作表中A1单元格中的值的和，但不包括当前的工作表，如下图所示。

公式

=SUM('*'!A1)

公式解释

在当前表（总表）的A1单元格中输入公式=SUM('*'!A1)，然后按Enter键，公式会自动变成=SUM(表1:表2!A1)。

公式里的星号表示包括所有工作表，但不包含当前工作表。

本案例视频文件：02/案例83 使用SUM函数求多个工作表中的值的和，但不包括当前的工作表

2.5.9　SUMPRODUCT：计算乘积之和函数

公式：=SUMPRODUCT(array1,array2,array3,...)

要注意的知识点

第1点：SUMPRODUCT函数是将其每一个参数对应的值相乘后再相加。

第2点：这个函数不支持逻辑值直接运算，如果有逻辑值，则要通过N函数转换一下。

案例84　使用SUMPRODUCT函数求产品名称是A且型号是大号的产品数量

案例及公式如下图所示。使用SUMPRODUCT函数可以求产品名称是A且型号是大号的产品数量。

公式

=SUMPRODUCT(N(A2:A6=A2),N(B2:B6="大"),C2:C6)

公式解释

此公式中的两个条件前面都加了N函数来处理数据，这是因为SUMPRODUCT函数不直接支持逻辑值计算，且两个条件中间用逗号分开，作为第1参数和第2参数。

当然，也可以将两个条件相乘，公式为=SUMPRODUCT((A2:A6="A")*(B2:B6="大")*(C2:C6))。

本案例视频文件：02/案例84 使用SUMPRODUCT函数求产品名称是A且型号是大号的数量

2.5.10　PRODUCT：计算所有参数的乘积函数

公式：=PRODUCT(number1,number2,...)

要注意的知识点

PRODUCT函数是求其各个参数对应的值的乘积。

案例85　使用PRODUCT函数计算体积

案例及公式如下图所示。使用PRODUCT函数可以根据A2:C2单元格区域中的值计算体积。

公式

=PRODUCT(A2,B2,C2)

公式解释

此函数为求各个参数对应的值的乘积，也可以写成=PRODUCT(A2:C2)。

本案例视频文件：02/案例85 计算体积

2.5.11　SUMIF：单条件求和函数

公式：=SUMIF(range,criteria,sum_range)

要注意的知识点

第1点：SUMIF函数的作用是按条件求和。

第2点：第1参数一定要是引用的单元格区域。

第3点：第1参数也可以用这几个函数：INDEX、OFFSET、INDIRECT。

第4点：此函数的第1参数为条件所在的区域；第2参数为条件；第3参数为求和区域。

案例86　使用SUMIF函数汇总数据

案例及公式如下图所示。这里要汇总姓名为“曹丽”对应的数量。

公式

=SUMIF(A2:A6,"曹丽",B2:B6)

公式解释

第3参数（B2:B6）也可以简写为B2，即公式为=SUMIF(A2:A6,"曹丽",B2)，也就是第3参数会根据第1参数自动匹配单元格区域。

本案例视频文件：02/案例86 使用SUMIF函数汇总数据

2.5.12　SUMIFS：多条件求和函数

公式：=SUMIFS(sum_range,criteria_range,criteria,...)

要注意的知识点

第1点：SUMIFS函数为多条件求和函数。

第2点：此函数的参数不确定，当有1个条件时，此函数就有3个参数；当有2个条件时，此函数就有5个参数，依此类推。

第3点：此函数的第1参数是求和区域，和SUMIF函数不同，SUMIF函数的第3参数是求和区域。

案例87　使用SUMIFS函数多条件求产品数量

案例及公式如下图所示。使用SUMIFS函数可以求产品名称为“A”且型号为“大”的产品数量。

公式

=SUMIFS(C1:C5,A1:A5,E1,B1:B5,F1)

公式解释

其中C1:C5是求和单元格区域，A1:A5是条件1所应用的区域；E1是条件1；B1:B5是条件2应用的区域；F1是条件2。

本案例视频文件：02/案例87 使用SUMIFS函数多条件求产品数量

2.5.13　MIN：最小值函数

公式：=MIN(number1,number2,...)

要注意的知识点

第1点：MIN函数用于返回最小值。

第2点：MIN函数的参数不能为错误值。

案例88　使用MIN函数判断单元格中的数值

案例及公式如下图所示。这里要对A列中的数值进行判断，如果数值大于100则显示100，否则显示数值本身。

公式

=MIN(A1,100)

公式解释

如果单元格中的数值比100大，则显示最小值100；如果单元格中的数值比100小，则最小值就是单元格中的数值，即显示单元格中的数值。此公式当然也可以用IF函数实现。

本案例视频文件：02/案例88 使用MIN函数判断单元格中的数值

2.5.14　MAX：最大值函数

公式：=MAX(number1,number2,...)

要注意的知识点

第1点：MAX函数用于返回最大值。

第2点：此函数的参数不能为错误值。

案例89　使用MAX函数判断单元格中的数值

案例及公式如下图所示。使用MAX函数可以判断A1单元格中的数值，如果小于100就显示100，否则显示单元格中的数值。

公式

=MAX(A1,100)

公式解释

如果A1单元格中的数值比100小，则100是最大值；如果单元格中的数值比100大，则单元格中的数值就是最大值。

本案例视频文件：02/案例89 使用MAX函数判断单元格中的数值

2.5.15　SMALL：返回第几个最小值函数

公式：=SMALL(array,k)

要注意的知识点

第1点：SMALL函数用于返回第几个最小值。

第2点：此函数有两个参数，第1参数：数据源；第2参数：第几个最小值。

案例90　使用SMALL函数升序排序单元格区域中的数值

案例及公式如下图所示。使用SMALL函数可以升序排序单元格区域A1:A5中的数值。

公式

=SMALL($A$1:$A$5,ROW(A1))

公式解释

将公式ROW(A1)向下填充会产生数列1,2,3,4,5,…

本案例视频文件：02/案例90 使用SMALL函数升序排序单元格区域中的数值

2.5.16　LARGE：返回第几个最大值函数

公式：=LARGE(array,k)

要注意的知识点

第1点：LARGE函数用于返回第几个最大值。

第2点：此函数有两个参数，第1参数：数据源；第2参数：第几个最大数值。

案例91　使用LARGE函数降序排序单元格区域中的数值

案例及公式如下图所示。使用LARGE函数可以降序排序单元格区域A1:A5中的数值。

公式

=LARGE($A$1:$A$5,ROW(A1))

公式解释

将公式ROW(A1)向下填充后会产生数列1,2,3,4,5,…

本案例视频文件：02/案例91 使用LARGE函数降序排序单元格区域中的数值

2.5.17　SUBTOTAL：分类汇总函数

公式：=SUBTOTAL(function_num,ref1,...)

要注意的知识点

第1点：SUBTOTAL函数可以忽略隐藏的行计算。

第2点：如果第1参数超过了100，就可以实现隐藏行并不将其进行计算。

案例92　使用SUBTOTAL函数给隐藏的行自动编号

案例及公式如下图所示。使用SUBTOTAL函数可以给隐藏的行自动编号。

公式

=SUBTOTAL(102,$B$1:B1)+1

公式解释

在此公式中，如果第1参数为2，则将计算隐藏的行，相当于COUNT函数的作用；如果第1参数为102，则将不计算隐藏的行。

使用SUBTOTAL函数计算不到最后一行，所以在公式中要从表头开始引用行，然后加1，否则最后的结果将差一行数据。

本案例视频文件：02/案例92 使用SUBTOTAL函数给隐藏的行自动编号

2.5.18　ROUND：四舍五入函数

公式：=ROUND(number,num_digits)

要注意的知识点

第1点：ROUND函数用于对数值进行四舍五入。

第2点：此函数有两个参数，第1参数为要处理的数值，第2参数为保留几位小数。

案例93　使用ROUND函数将单元格区域中的数值保留两位小数

案例及公式如下图所示。使用ROUND函数可以将单元格区域A1:A5中的数值保留两位小数。

公式

=ROUND(A1,2)

公式解释

在此公式中，第1参数为要处理的数据，第2参数为保留两位小数。

本案例视频文件：02/案例93 使用ROUND函数将单元格区域中的数值保留两位小数

2.5.19　ROUNDDOWN：向下舍入函数

公式：=ROUNDDOWN(number,num_digits)

要注意的知识点

ROUNDDOWN函数用于向下舍入数字。

案例94　使用ROUNDDOWN函数保留一位小数，不进行四舍五入，全部舍弃

案例及公式如下图所示。使用ROUNDDOWN函数可以把单元格区域A1:A5中的数值保留一位小数，不进行四舍五入，全部舍弃。

公式

=ROUNDDOWN(A1,1)

公式解释

在此公式中，第1参数为要处理的数值即A1；第2参数为1，即保留1位小数，但是，如果数值的百分位上的值大于或等于5，则也不会向十分位上进位。

本案例视频文件：02/案例94 使用ROUNDDOWN函数保留一位小数，不进行四舍五入，全部舍弃

2.5.20　ROUNDUP：向上舍入函数

参数; =ROUNDUP(number,num_digits)

要注意的知识点

ROUNDUP函数用于将小数向上进一位。

案例95　使用ROUNDUP函数保留一位小数，不进行四舍五入，全部进入

案例及公式如下图所示。使用ROUNDUP函数可以把单元格区域中的数值保留一位小数，不进行四舍五入，全部进入。

公式

=ROUNDUP(A1,1)

公式解释

在此公式中，ROUNDUP函数有两个参数，第1参数：要处理的数据，即A1；第2参数：1，即保留1位小数，但是如果数值百分位上的值小于5，则也要向十分位进1位。

本案例视频文件：02/案例95 使用ROUNDUP函数保留一位小数，不进行四舍五入，全部进入

2.5.21　CEILING：按倍数向上进位函数

公式：=CEILING(number,significance)

要注意的知识点

第1点：CEILING函数用于向上舍入为最接近指定参数的倍数。

第2点：CEILING函数有两个参数，第1参数：要处理的数据；第2参数：按第2参数的倍数向上查找并返回最接近的值。

案例96　使用CEILING函数进行数值舍入：十分位不足5就按5算，大于或等于5就向上进1

案例及公式如下图所示。使用CEILING函数可以将十分位不足5就按5算，大于或等于5就向上进1。

公式

=CEILING(A1,0.5)

公式解释

在此公式中，第2参数是0.5。因为A1中的数值是1.51，其十分位是5，所以要向上进1并返回0.5的倍数。

如果要处理的数值是1.2，十分位是2，小于5，则向上查找并返回大于1.2且是0.5的倍数。

本案例视频文件：02/案例96 使用CEILING函数进行数值舍入：十分位不足5就按5算，大于或等于5就向上进1

2.5.22　FLOOR：按倍数向下舍入函数

公式：=FLOOR(number,significance)

要注意的知识点

FLOOR函数有两个参数，第1参数：要处理的数据；第2参数：按此数值的倍数向下查找并返回最接近的值。

案例97　使用FLOOR函数进行数值舍入：十分位不足5就向下舍去，大于或等于5就按5算

案例及公式如下图所示。

使用FLOOR函数可以将单元格中的数值进行数值舍入，其中十分位不足5就舍去，大于或等于5就按5算。

公式

=FLOOR(A1,0.5)

公式解释

假设要处理的数值是1.51，因为十分位是5，大于或等于5，所以按5算。

假设要处理的数值是1.2，因为十分位是2，小于5，所以舍去并返回0.5的倍数，因此返回1。

本案例视频文件：02/案例97 使用FLOOR函数进行数值舍入：十分位不足5向下舍去，大于或等于5就按5算

2.5.23　INT：取整函数

公式：=INT(number)

要注意的知识点

第1点：INT函数是取整函数。

第2点：此函数只有一个参数。

案例98　使用INT函数把日期提取出来

案例及公式如下图所示。使用INT函数可以提取A列数值中的日期。

公式

=INT(A2)

公式解释

日期是整数，如果后面的时间数值超过24小时，则给日期加1天。

本案例视频文件：02/案例98 使用INT函数把日期提取出来

2.5.24　MOD：取余函数

公式：=MOD(number,divisor)

要注意的知识点

MOD函数是取余函数，返回两个数相除的余数。

案例99　使用MOD函数求余数

案例及公式如下图所示。使用MOD函数可以求单元格区域A1:A5的行号除以2的余数。

公式

=MOD(ROW(),2)

公式解释

A1单元格的行号是1，所以函数ROW()返回1；1除以2的余数为1，所以A1单元格中返回的结果为1。将公式填充到A2单元格中则返回2，因为2除以2的余数为0，以此类推。

本案例视频文件：02/案例99 使用MOD函数求余数

2.5.25　REPT：重复函数

公式：=REPT(text,number_times)

要注意的知识点

REPT函数有两个参数，第1参数：要重复的数值；第2参数：重复的次数。

案例100　使用REPT函数制作符号编号

案例及公式如下图所示。使用REPT函数可以制作如下图所示的特殊符号编号。

公式

=REPT("■",ROW(A1))

公式解释

在此公式中，第1参数是要重复的文本“■”；将第2参数“ROW(A1)”向下填充会产生数列1,2,3,…，也就是重复第1参数的次数。

本案例视频文件：02/案例100 使用REPT函数制作符号编号

2.5.26　N：将非数值型数值转换为数值型数值函数

公式：=N(value)

要注意的知识点

第1点：在N函数中，数值型数值不变，TURE会被转换为1，错误值还是错误值，日期被转换为对应的数值，其他类型数值全部被转换为0。

第2点：此函数只有一个参数，即要处理的数值。

案例101　使用N函数将数值进行转换

案例及公式如下图所示。使用N函数可以把单元格区域A1:A9中的数值进行转换。

公式

=N(A1)

公式解释

文本型数值也被转换为0。

本案例视频文件：02/案例101 使用N函数将数值进行转换

2.5.27　ABS：取绝对值函数

公式：=ABS(number)

要注意的知识点

第1点：ABS函数用于返回参数的绝对值。

第2点：此函数只有一个参数，即要处理的数值。

案例102　使用ABS函数求绝对值

案例及公式如下图所示。使用ABS函数可以求单元格区域A1:A5中的数值的绝对值。

公式

=ABS(A1)

公式解释

在此公式中把负数转换为正数，正数保持不变。

本案例视频文件：02/案例102 使用ABS函数求绝对值

2.5.28　CELL：获取单元格信息函数

公式：=CELL(info_type,reference)

要注意的知识点

第1点：CELL函数用于获取单元格中的信息。

第2点：此函数可以返回第2参数的左上角上的第一个单元格（如果第2参数为单元格区域）中的信息。

案例103　使用CELL函数获取工作簿路径及工作表信息

案例及公式如下图所示。使用CELL函数可以获取路径工作簿及工作表信息。

公式

=CELL("filename")

公式解释

此公式可以获取工作簿路径及工作表信息，其中第2参数可以是任意一个单元格，也可以省略。

本案例视频文件：02/案例103 使用CELL函数获取工作簿路径及工作表信息

2.5.29　ISNUMBER：检测是否为数值型数值函数

公式：=ISNUMBER(value)

要注意的知识点

第1点：ISNUMBER函数只有一个参数。

第2点：此函数用于判断数值是否是数值型，如果是数值型，则返回TRUE，否则返回FALSE。

案例104　使用ISNUMBER函数判断数值型数值

案例及公式如下图所示。使用ISNUMBER函数可以判断A列中的数值类型，如果是数值型则返回“Ok”。

公式

=IF(ISNUMBER(A1),"Ok","No")

公式解释

ISNUMBER(A1)：如果A1单元格中是数值型数值，就返回TRUE，否则就返回FALSE。

本案例视频文件：02/案例104 使用ISNUMTER函数判断数值型数值

2.5.30　ISTEXT：检测是否为文本函数

公式：=ISTEXT(value)

要注意的知识点

第1点：ISTEXT函数用于判断单元格中的数值是否是文本型，如果是文本型就返回TRUE，否则就返回FALSE。

第2点：此函数只有一个参数，即要处理的数值。

案例105　使用ISTEXT函数判断文本型数值

案例及公式如下图所示。使用ISTEXT函数可以判断A列单元格中的数值类型，如果是文本型就返回“Ok”。

公式

=IF(ISTEXT(A1),"Ok","No")

公式解释

ISTEXT(A1)：如果A1单元格中的数值是文本型，则返回TRUE，否则返回FALSE。

本案例视频文件：02/案例105 使用ISTEXT函数判断文本型数值

2.5.31　PHONETIC：另类文本字符连接函数

公式：=PHONETIC(reference)

要注意的知识点

PHONETIC函数只连接文本型数值，数值型数值则被忽略。

案例106　使用PHONETIC函数连接文本

案例及公式如下图所示。使用PHONETIC函数可以把A～C列中的数值连接起来放到D列中。

公式

=PHONETIC(A1:C1)

公式解释

使用PHONETIC函数可以把A～C列中的文本连接起来放到D列中。

在上图中，单元格B2、C3中的数值是数值型，所以被忽略。

本案例视频文件：02/案例106 使用PHONETIC函数连接文本

2.5.32　RAND：取随机小数函数

公式：=RAND()

要注意的知识点

RAND函数用于返回大于或等于0且小于1的随机数。

案例107　使用RAND函数生成随机数

案例及公式如下图所示。使用RAND函数可以生成随机数。

公式

=INT((RAND()*100+1))

公式解释

使用RAND函数可以在单元格区域A1:A5中生成1~100的随机数。

RAND()*100用于产生0～99的随机数，然后加1，就产生1～100的随机数，最后使用INT函数取整。

本案例视频文件：02/案例107 使用RAND函数生成随机数

2.5.33　RANDBETWEEN：取随机整数函数

公式：=RANDBETWEEN(bottom,top)

要注意的知识点

第1点：RANDBETWEEN函数在Excel 2003版本中没有，在Excel 2007及以上版本中才有。

第2点：此函数用于返回指定参数之间的随机整数。

案例108　使用RANDBETWEEN函数生成指定大小的随机整数

案例及公式如下图所示。这里要在A列中生成指定大小的随机整数。

公式

=RANDBETWEEN(1,50)

公式解释

RANDBETWEEN函数可以在单元格区域A1:A5中生成1～50的随机整数。

在此公式中，第1参数：随机数中的最小数；第2参数：随机数中的最大数。

本案例视频文件：02/案例108 使用RANDBETWEEN函数生成指定大小的随机整数

2.5.34　MODE：取众数函数

公式：=MODE(number1,number2,…)

要注意的知识点

MODE函数是一个取众数函数，返回一组数值中出现次数最多的数值。

案例109　使用MODE函数判断出现次数最多的数值

案例及公式如下图所示。使用MODE函数可以判断单元格区域A1:A5中出现次数最多的数值是哪个。

公式

=MODE(A1:A5)

公式解释

在单元格区域A1:A5中，100出现2次，其他数值各出现1次，所以返回结果100。

本案例视频文件：02/案例109 使用MODE函数判断出现次数最多的数值