2.2.3 便捷的数值运算函数

Excel函数里面同样包含了许多对数值进行处理的函数，方便快速处理数据并进行相关运算。常见的数值运算函数包括生成随机数值的RAND、RANDBETWEEN函数，进行数学运算的ABS、MOD、POWER、PRODUCT函数，四舍五入、向上向下取整的CEILING、FLOOR、ROUND、ROUNDUP、ROUNDDOWN、TRUNC函数等。日常工作中碰到需要数值进行模拟运算时，可使用RAND、RANDBETWEEN函数生成的随机数值来计算。此外，对业务中的指标数据进行分段统计时，都需要使用CEILING、FLOOR等函数进行数值处理。下面通过实例对常用的数值运算函数进行说明。

1.RAND函数

功能说明：返回一个大于等于0且小于1的、平均分布的随机实数，每次计算工作表时都会返回一个新的随机实数。

语法：RAND()

参数：无参数。

示例：随机生成一组用户的性别。

公式与步骤：单元格B2内输入公式“=IF(RAND()＞0.5,"男","女")”，然后向下拖拽复制公式，结果如图2-50所示。

提示：

● 利用RAND函数生成大于等于0且小于1的随机实数，然后嵌套IF函数判断随机数与数值0.5的大小，如果随机实数大于0.5，用户性别返回“男”，否则返回“女”。

2.RANDBETWEEN函数

功能说明：返回位于两个指定数之间的一个随机整数。每次计算工作表时都将返回一个新的随机整数。

语法：RANDBETWEEN(bottom, top)

参数：

● bottom必需。RANDBETWEEN函数返回的最小整数。

● top必需。RANDBETWEEN函数返回的最大整数。

示例：

随机生成一组学生的年龄（20～25）、语文成绩（0～100分）以及手机号码（以133开头）。

公式与步骤：

● 随机生成年龄：单元格B2内输入公式“=RANDBETWEEN(20,25)”，然后向下拖拽复制公式。

● 随机生成语文成绩：单元格C2内输入公式“=RANDBETWEEN(0,100)”，然后向下拖拽复制公式。

● 随机生成手机号码：单元格D2内输入公式“="133" & RANDBETWEEN(10000000, 99999999)”，然后向下拖拽复制公式，结果如图2-51所示。

提示：RANDBETWEEN函数可以取到bottom和top的数值。

3.ABS函数

功能说明：返回数字的绝对值。

语法：ABS(number)

参数：number必需。需要计算其绝对值的实数。

示例：取数值-2的绝对值。

公式与步骤：单元格E2内输入公式“=ABS(A2)”，结果如图2-52所示。

4.MOD函数

功能说明：返回两数相除的余数。返回结果的符号与除数相同。

语法：MOD(number, divisor)

参数：

● number必需。要计算余数的被除数。

● divisor必需。除数。

示例：区域A2:A5是被除数，区域B2:B5是对应的除数，计算每行数据的余数。

公式与步骤：单元格C2内输入公式“=MOD(A2,B2)”，然后向下拖拽复制公式，结果如图2-53所示。

提示：

● 如果divisor为0，则MOD返回错误值 #DIV/0!。

● 余数的符号与除数相同。

5.POWER函数

功能说明：返回数字乘幂的结果。

语法：POWER(number, power)

参数：

● number必需。基数。

● power必需。基数乘幂运算的指数。

示例：区域A2:A4是基数，区域B2:B4是对应的指数，计算每行数据的乘幂。

公式与步骤：单元格C2内输入公式“=POWER(A2,B2)”，然后向下拖拽复制公式，结果如图2-54所示。

提示：可以使用字符“^”代替POWER函数，表示基数乘幂运算的幂。例如，数字3的平方公式可以写成“=3^2”。

6.PRODUCT函数

功能说明：将参数形式给出的数字相乘并返回乘积。

语法：PRODUCT(number1, [number2], …)

参数：

● number1必需。要相乘的第一个数字或单元格区域。

● number2, … 可选。要相乘的其他数字或单元格区域。

示例：

● 计算区域A2:A5的数字乘积。

● 计算区域A2:A5的数字乘积再乘以3。

公式与步骤：

● 单元格E2内输入公式“=PRODUCT(A2:A5)”。

● 单元格E3内输入公式“=PRODUCT(A2:A5,3)”，结果如图2-55所示。

提示：可以使用字符“*”代替PRODUCT函数，表示数字之间的乘法运算。例如，数字1、2、4、8的乘积公式可以写成“=1*2*4*8”。

7.CEILING函数

功能说明：返回将参数number向上舍入（沿绝对值增大的方向）为最接近的指定基数的倍数。

语法：CEILING(number,significance)

参数：

● number必需。要舍入的值。

● significance必需。要舍入到的倍数。

示例：区域A2:A6是要舍入的值，区域B2:B6是基数，用CEILING函数进行向上舍入为最接近的指定基数的倍数。

公式与步骤：单元格C2内输入公式“=CEILING(A2,B2)”，然后向下拖拽复制公式，结果如图2-56所示。

提示：

● 如果number正好是significance的倍数，则不进行舍入。

● 如果number和significance都为负，则按远离0的方向进行向下舍入。

● 如果number为负，significance为正，则按朝向0的方向进行向上舍入。

● 如果number为正，significance为负，则结果返回错误值#NUM!。

8.FLOOR函数

功能说明：将参数number向下舍入（沿绝对值减小的方向）为最接近的指定基数的倍数。

语法：FLOOR(number, significance)

参数：

● number必需。要舍入的值。

● significance必需。要舍入到的倍数。

示例：区域A2:A6是要舍入的值，区域B2:B6是基数，用FLOOR函数进行向下舍入为最接近的指定基数的倍数。

公式与步骤：单元格C2内输入公式“=FLOOR(A2,B2)”，然后向下拖拽复制公式，结果如图2-57所示。

提示：

● 如果number正好是significance的倍数，则不进行舍入。

● 如果number和significance都为负，则按朝0的方向进行向上舍入。

● 如果number为负，significance为正，则按远离0的方向进行向下舍入。

● 如果number为正，significance为负，则结果返回错误值#NUM!。

9.ROUND函数

功能说明：ROUND函数将数字四舍五入到指定的位数。

语法：ROUND(number, num_digits)

参数：

● number必需。要四舍五入的数字。

● num_digits必需。要进行四舍五入运算的位数。

示例：区域A2:A5是要处理的数值，区域B2:B5是对数值四舍五入的说明。

公式与步骤：

● 四舍五入到2个小数位数：单元格C2内输入公式“=ROUND(A2,2)”。

● 四舍五入到3个小数位数：单元格C3内输入公式“=ROUND(A3,3)”。

● 四舍五入到小数点左侧1位：单元格C4内输入公式“=ROUND(A4, -1)”。

● 四舍五入到小数点左侧2位：单元格C5内输入公式“=ROUND(A5, -2)”，结果如图2-58所示。

提示：

● 如果num_digits大于0，则将数字四舍五入到指定的小数位数。

● 如果num_digits等于0，则将数字四舍五入到最接近的整数。

● 如果num_digits小于0，则将数字四舍五入到小数点左边的相应位数。

10.ROUNDUP函数

功能说明：朝着远离数值0的方向将数字进行向上舍入。

语法：ROUNDUP(number, num_digits)

参数：

● number必需。需要向上舍入的任意实数。

● num_digits必需。要将数字舍入到的位数。

示例：区域A2:A5是要处理的数值，区域B2:B5是对数值向上舍入的说明。

公式与步骤：

● 向上舍入到2个小数位数：单元格C2内输入公式“=ROUNDUP(A2,2)”。

● 向上舍入到3个小数位数：单元格C3内输入公式“=ROUNDUP(A3,3)”。

● 向上舍入到小数点左侧1位：单元格C4内输入公式“=ROUNDUP(A4, -1)”。

● 向上舍入到小数点左侧2位：单元格C5内输入公式“=ROUNDUP(A5, -2)”，结果如图2-59所示。

提示：

● ROUNDUP与ROUND相似，区别是它始终将数字进行向上舍入。

● 如果num_digits大于0，则将数字向上舍入到指定的小数位数。

● 如果num_digits等于0，则将数字向上舍入到最接近的整数。

● 如果num_digits小于0，则将数字向上舍入到小数点左边的相应位数。

11.ROUNDDOWN函数

功能说明：朝着数值0的方向将数字进行向下舍入。

语法：ROUNDDOWN(number, num_digits)

参数：

● number必需。需要向下舍入的任意实数。

● num_digits必需。要将数字舍入到的位数。

示例：区域A2:A5是要处理的数值，区域B2:B5是对数值向下舍入的说明。

公式与步骤：

● 向下舍入到2个小数位数：单元格C2内输入公式“=ROUNDDOWN(A2,2)”。

● 向下舍入到3个小数位数：单元格C3内输入公式“=ROUNDDOWN(A3,3)”。

● 向下舍入到小数点左侧1位：单元格C4内输入公式“=ROUNDDOWN(A4, -1)”。

● 向下舍入到小数点左侧2位：单元格C5内输入公式“=ROUNDDOWN(A5, -2)”，结果如图2-60所示。

提示：

● ROUNDDOWN与ROUND相似，区别是它始终将数字进行向下舍入。

● 如果num_digits大于0，则将数字向下舍入到指定的小数位数。

● 如果num_digits等于0，则将数字向下舍入到最接近的整数。

● 如果num_digits小于0，则将数字向下舍入到小数点左边的相应位数。

12.TRUNC函数

功能说明：将数字进行截取返回整数。

语法：TRUNC(number, [num_digits])

参数：

● number必需。需要截尾取整的数字。

● num_digits可选。用于指定取整精度的数字，默认值为0。

示例：区域A2:A4是要处理的数值，区域B2:B4是对数值进行截取的说明。

公式与步骤：

● 截取整数部分：单元格C2内输入公式“=TRUNC(A2)”。

● 截取到小数点右侧2位：单元格C3内输入公式“=TRUNC(A3,2)”。

● 截取到小数点左侧1位：单元格C4内输入公式“=TRUNC(A4, -1)”，结果如图2-61所示。

提示：TRUNC与INT在对数值的整数部分进行截取时有些相似。TRUNC是直接删除数字的小数部分，而INT根据数字小数部分的值将数字向下舍入为最接近的整数。只有当处理负数的时候，INT和TRUNC会有区别。例如，TRUNC(-3.14)返回-3，而INT(-3.14)返回-4。

以上是对数值运算函数的介绍，并通过示例对函数进行了功能讲解，下面的数值运算函数案例一～案例二是数值运算函数的应用扩展。同一个案例采用了多种方法来解决。

13.数值运算函数案例一

案例说明：区域A2:A11是随机生成的用户年龄（范围是21～60岁），D3:D6是年龄分段，需要统计不同年龄段的用户人数。

公式与步骤：

● 方法一：单元格B2输入公式“=CEILING(A2,10)”，然后向下拖拽复制公式，在区域B2:B11内生成年龄分段组辅助数据。单元格E3内输入公式“=COUNTIFS(B:B, ROW()*10)”，然后向下拖拽复制公式。

● 方法二：单元格F3内输入数组公式“{=SUM((CEILING($A$2:$A$11,10)=ROW()*10)+0)}”，然后向下拖拽复制公式。

● 方法三：单元格G3内公式为“=COUNTIFS(A:A,"＞"&((ROW()-1)*10),A:A,"＜="&(ROW()*10))”，然后向下拖拽复制公式，结果如图2-62所示。

提示：

● 方法一用CEILING函数把年龄数据向上取整，全部处理成10的整数倍，借助于ROW函数拼凑年龄段的条件，最后用COUNTIFS函数进行单条件计数。

● 方法二用CEILING函数把年龄数据向上取整，全部处理成10岁的整数倍，然后将处理好的数组和年龄组的上限进行比较，如果相等，返回TRUE，如果不相等，返回FALSE，从而生成布尔值数值，通过加0处理成数值数组，最后用SUM数组公式进行求和。

● 方法三用ROW函数拼凑成每个年龄分段组的上限和下限，最后用COUNTIFS函数进行多条件计数。

14.数值运算函数案例二

案例说明：区域A2:A11内随机生成介于1～100的数值，然后分别统计奇数和偶数的个数。

公式与步骤：

1）单元格A2内输入公式“=RANDBETWEEN(1,100)”，然后向下拖拽到单元格A11位置进行公式复制。

2）选中A列或区域A1:A11，按〈Ctrl+C〉组合键进行复制，选中区域生成虚线之后，右击“粘贴选项”里面选择“粘贴值”，此时单元格内的公式将会消失，目的是让随机函数生成的数值固定不变。

3）单元格E2内输入数组公式“{=SUM(MOD(A2:A11,2))}”实现奇数个数统计。

4）单元格E3内输入数组公式“{=SUM(IF(MOD(A2:A11,2)=0,1,0))}”实现偶数个数统计，结果如图2-63所示。

提示：

● 用MOD函数将随机数值除以2求余，结果返回1（奇数）或者0（偶数），然后对结果中的数值1直接套用SUM数组进行求和，统计的就是奇数的个数。

● 用MOD函数将随机数值除以2求余，结果返回1（奇数）或者0（偶数），然后套用IF函数，将余数与数值0进行比较，相等返回1，否则返回0，再套用SUM数组进行求和，统计的就是偶数的个数。