2.2.3 便捷的數(shù)值運(yùn)算函數(shù)

Excel函數(shù)里面同樣包含了許多對(duì)數(shù)值進(jìn)行處理的函數(shù)，方便快速處理數(shù)據(jù)并進(jìn)行相關(guān)運(yùn)算。常見(jiàn)的數(shù)值運(yùn)算函數(shù)包括生成隨機(jī)數(shù)值的RAND、RANDBETWEEN函數(shù)，進(jìn)行數(shù)學(xué)運(yùn)算的ABS、MOD、POWER、PRODUCT函數(shù)，四舍五入、向上向下取整的CEILING、FLOOR、ROUND、ROUNDUP、ROUNDDOWN、TRUNC函數(shù)等。日常工作中碰到需要數(shù)值進(jìn)行模擬運(yùn)算時(shí)，可使用RAND、RANDBETWEEN函數(shù)生成的隨機(jī)數(shù)值來(lái)計(jì)算。此外，對(duì)業(yè)務(wù)中的指標(biāo)數(shù)據(jù)進(jìn)行分段統(tǒng)計(jì)時(shí)，都需要使用CEILING、FLOOR等函數(shù)進(jìn)行數(shù)值處理。下面通過(guò)實(shí)例對(duì)常用的數(shù)值運(yùn)算函數(shù)進(jìn)行說(shuō)明。

1.RAND函數(shù)

功能說(shuō)明：返回一個(gè)大于等于0且小于1的、平均分布的隨機(jī)實(shí)數(shù)，每次計(jì)算工作表時(shí)都會(huì)返回一個(gè)新的隨機(jī)實(shí)數(shù)。

語(yǔ)法：RAND()

參數(shù)：無(wú)參數(shù)。

示例：隨機(jī)生成一組用戶的性別。

公式與步驟：單元格B2內(nèi)輸入公式“=IF(RAND()＞0.5,"男","女")”，然后向下拖拽復(fù)制公式，結(jié)果如圖2-50所示。

提示：

● 利用RAND函數(shù)生成大于等于0且小于1的隨機(jī)實(shí)數(shù)，然后嵌套IF函數(shù)判斷隨機(jī)數(shù)與數(shù)值0.5的大小，如果隨機(jī)實(shí)數(shù)大于0.5，用戶性別返回“男”，否則返回“女”。

2.RANDBETWEEN函數(shù)

功能說(shuō)明：返回位于兩個(gè)指定數(shù)之間的一個(gè)隨機(jī)整數(shù)。每次計(jì)算工作表時(shí)都將返回一個(gè)新的隨機(jī)整數(shù)。

語(yǔ)法：RANDBETWEEN(bottom, top)

參數(shù)：

● bottom必需。RANDBETWEEN函數(shù)返回的最小整數(shù)。

● top必需。RANDBETWEEN函數(shù)返回的最大整數(shù)。

示例：

隨機(jī)生成一組學(xué)生的年齡（20～25）、語(yǔ)文成績(jī)（0～100分）以及手機(jī)號(hào)碼（以133開(kāi)頭）。

公式與步驟：

● 隨機(jī)生成年齡：單元格B2內(nèi)輸入公式“=RANDBETWEEN(20,25)”，然后向下拖拽復(fù)制公式。

● 隨機(jī)生成語(yǔ)文成績(jī)：單元格C2內(nèi)輸入公式“=RANDBETWEEN(0,100)”，然后向下拖拽復(fù)制公式。

● 隨機(jī)生成手機(jī)號(hào)碼：單元格D2內(nèi)輸入公式“="133" & RANDBETWEEN(10000000, 99999999)”，然后向下拖拽復(fù)制公式，結(jié)果如圖2-51所示。

提示：RANDBETWEEN函數(shù)可以取到bottom和top的數(shù)值。

3.ABS函數(shù)

功能說(shuō)明：返回?cái)?shù)字的絕對(duì)值。

語(yǔ)法：ABS(number)

參數(shù)：number必需。需要計(jì)算其絕對(duì)值的實(shí)數(shù)。

示例：取數(shù)值-2的絕對(duì)值。

公式與步驟：單元格E2內(nèi)輸入公式“=ABS(A2)”，結(jié)果如圖2-52所示。

4.MOD函數(shù)

功能說(shuō)明：返回兩數(shù)相除的余數(shù)。返回結(jié)果的符號(hào)與除數(shù)相同。

語(yǔ)法：MOD(number, divisor)

參數(shù)：

● number必需。要計(jì)算余數(shù)的被除數(shù)。

● divisor必需。除數(shù)。

示例：區(qū)域A2:A5是被除數(shù)，區(qū)域B2:B5是對(duì)應(yīng)的除數(shù)，計(jì)算每行數(shù)據(jù)的余數(shù)。

公式與步驟：單元格C2內(nèi)輸入公式“=MOD(A2,B2)”，然后向下拖拽復(fù)制公式，結(jié)果如圖2-53所示。

提示：

● 如果divisor為0，則MOD返回錯(cuò)誤值 #DIV/0!。

● 余數(shù)的符號(hào)與除數(shù)相同。

5.POWER函數(shù)

功能說(shuō)明：返回?cái)?shù)字乘冪的結(jié)果。

語(yǔ)法：POWER(number, power)

參數(shù)：

● number必需。基數(shù)。

● power必需。基數(shù)乘冪運(yùn)算的指數(shù)。

示例：區(qū)域A2:A4是基數(shù)，區(qū)域B2:B4是對(duì)應(yīng)的指數(shù)，計(jì)算每行數(shù)據(jù)的乘冪。

公式與步驟：單元格C2內(nèi)輸入公式“=POWER(A2,B2)”，然后向下拖拽復(fù)制公式，結(jié)果如圖2-54所示。

提示：可以使用字符“^”代替POWER函數(shù)，表示基數(shù)乘冪運(yùn)算的冪。例如，數(shù)字3的平方公式可以寫(xiě)成“=3^2”。

6.PRODUCT函數(shù)

功能說(shuō)明：將參數(shù)形式給出的數(shù)字相乘并返回乘積。

語(yǔ)法：PRODUCT(number1, [number2], …)

參數(shù)：

● number1必需。要相乘的第一個(gè)數(shù)字或單元格區(qū)域。

● number2, … 可選。要相乘的其他數(shù)字或單元格區(qū)域。

示例：

● 計(jì)算區(qū)域A2:A5的數(shù)字乘積。

● 計(jì)算區(qū)域A2:A5的數(shù)字乘積再乘以3。

公式與步驟：

● 單元格E2內(nèi)輸入公式“=PRODUCT(A2:A5)”。

● 單元格E3內(nèi)輸入公式“=PRODUCT(A2:A5,3)”，結(jié)果如圖2-55所示。

提示：可以使用字符“*”代替PRODUCT函數(shù)，表示數(shù)字之間的乘法運(yùn)算。例如，數(shù)字1、2、4、8的乘積公式可以寫(xiě)成“=1*2*4*8”。

7.CEILING函數(shù)

功能說(shuō)明：返回將參數(shù)number向上舍入（沿絕對(duì)值增大的方向）為最接近的指定基數(shù)的倍數(shù)。

語(yǔ)法：CEILING(number,significance)

參數(shù)：

● number必需。要舍入的值。

● significance必需。要舍入到的倍數(shù)。

示例：區(qū)域A2:A6是要舍入的值，區(qū)域B2:B6是基數(shù)，用CEILING函數(shù)進(jìn)行向上舍入為最接近的指定基數(shù)的倍數(shù)。

公式與步驟：單元格C2內(nèi)輸入公式“=CEILING(A2,B2)”，然后向下拖拽復(fù)制公式，結(jié)果如圖2-56所示。

提示：

● 如果number正好是significance的倍數(shù)，則不進(jìn)行舍入。

● 如果number和significance都為負(fù)，則按遠(yuǎn)離0的方向進(jìn)行向下舍入。

● 如果number為負(fù)，significance為正，則按朝向0的方向進(jìn)行向上舍入。

● 如果number為正，significance為負(fù)，則結(jié)果返回錯(cuò)誤值#NUM!。

8.FLOOR函數(shù)

功能說(shuō)明：將參數(shù)number向下舍入（沿絕對(duì)值減小的方向）為最接近的指定基數(shù)的倍數(shù)。

語(yǔ)法：FLOOR(number, significance)

參數(shù)：

● number必需。要舍入的值。

● significance必需。要舍入到的倍數(shù)。

示例：區(qū)域A2:A6是要舍入的值，區(qū)域B2:B6是基數(shù)，用FLOOR函數(shù)進(jìn)行向下舍入為最接近的指定基數(shù)的倍數(shù)。

公式與步驟：單元格C2內(nèi)輸入公式“=FLOOR(A2,B2)”，然后向下拖拽復(fù)制公式，結(jié)果如圖2-57所示。

提示：

● 如果number正好是significance的倍數(shù)，則不進(jìn)行舍入。

● 如果number和significance都為負(fù)，則按朝0的方向進(jìn)行向上舍入。

● 如果number為負(fù)，significance為正，則按遠(yuǎn)離0的方向進(jìn)行向下舍入。

● 如果number為正，significance為負(fù)，則結(jié)果返回錯(cuò)誤值#NUM!。

9.ROUND函數(shù)

功能說(shuō)明：ROUND函數(shù)將數(shù)字四舍五入到指定的位數(shù)。

語(yǔ)法：ROUND(number, num_digits)

參數(shù)：

● number必需。要四舍五入的數(shù)字。

● num_digits必需。要進(jìn)行四舍五入運(yùn)算的位數(shù)。

示例：區(qū)域A2:A5是要處理的數(shù)值，區(qū)域B2:B5是對(duì)數(shù)值四舍五入的說(shuō)明。

公式與步驟：

● 四舍五入到2個(gè)小數(shù)位數(shù)：單元格C2內(nèi)輸入公式“=ROUND(A2,2)”。

● 四舍五入到3個(gè)小數(shù)位數(shù)：單元格C3內(nèi)輸入公式“=ROUND(A3,3)”。

● 四舍五入到小數(shù)點(diǎn)左側(cè)1位：單元格C4內(nèi)輸入公式“=ROUND(A4, -1)”。

● 四舍五入到小數(shù)點(diǎn)左側(cè)2位：單元格C5內(nèi)輸入公式“=ROUND(A5, -2)”，結(jié)果如圖2-58所示。

提示：

● 如果num_digits大于0，則將數(shù)字四舍五入到指定的小數(shù)位數(shù)。

● 如果num_digits等于0，則將數(shù)字四舍五入到最接近的整數(shù)。

● 如果num_digits小于0，則將數(shù)字四舍五入到小數(shù)點(diǎn)左邊的相應(yīng)位數(shù)。

10.ROUNDUP函數(shù)

功能說(shuō)明：朝著遠(yuǎn)離數(shù)值0的方向?qū)?shù)字進(jìn)行向上舍入。

語(yǔ)法：ROUNDUP(number, num_digits)

參數(shù)：

● number必需。需要向上舍入的任意實(shí)數(shù)。

● num_digits必需。要將數(shù)字舍入到的位數(shù)。

示例：區(qū)域A2:A5是要處理的數(shù)值，區(qū)域B2:B5是對(duì)數(shù)值向上舍入的說(shuō)明。

公式與步驟：

● 向上舍入到2個(gè)小數(shù)位數(shù)：單元格C2內(nèi)輸入公式“=ROUNDUP(A2,2)”。

● 向上舍入到3個(gè)小數(shù)位數(shù)：單元格C3內(nèi)輸入公式“=ROUNDUP(A3,3)”。

● 向上舍入到小數(shù)點(diǎn)左側(cè)1位：單元格C4內(nèi)輸入公式“=ROUNDUP(A4, -1)”。

● 向上舍入到小數(shù)點(diǎn)左側(cè)2位：單元格C5內(nèi)輸入公式“=ROUNDUP(A5, -2)”，結(jié)果如圖2-59所示。

提示：

● ROUNDUP與ROUND相似，區(qū)別是它始終將數(shù)字進(jìn)行向上舍入。

● 如果num_digits大于0，則將數(shù)字向上舍入到指定的小數(shù)位數(shù)。

● 如果num_digits等于0，則將數(shù)字向上舍入到最接近的整數(shù)。

● 如果num_digits小于0，則將數(shù)字向上舍入到小數(shù)點(diǎn)左邊的相應(yīng)位數(shù)。

11.ROUNDDOWN函數(shù)

功能說(shuō)明：朝著數(shù)值0的方向?qū)?shù)字進(jìn)行向下舍入。

語(yǔ)法：ROUNDDOWN(number, num_digits)

參數(shù)：

● number必需。需要向下舍入的任意實(shí)數(shù)。

● num_digits必需。要將數(shù)字舍入到的位數(shù)。

示例：區(qū)域A2:A5是要處理的數(shù)值，區(qū)域B2:B5是對(duì)數(shù)值向下舍入的說(shuō)明。

公式與步驟：

● 向下舍入到2個(gè)小數(shù)位數(shù)：單元格C2內(nèi)輸入公式“=ROUNDDOWN(A2,2)”。

● 向下舍入到3個(gè)小數(shù)位數(shù)：單元格C3內(nèi)輸入公式“=ROUNDDOWN(A3,3)”。

● 向下舍入到小數(shù)點(diǎn)左側(cè)1位：單元格C4內(nèi)輸入公式“=ROUNDDOWN(A4, -1)”。

● 向下舍入到小數(shù)點(diǎn)左側(cè)2位：單元格C5內(nèi)輸入公式“=ROUNDDOWN(A5, -2)”，結(jié)果如圖2-60所示。

提示：

● ROUNDDOWN與ROUND相似，區(qū)別是它始終將數(shù)字進(jìn)行向下舍入。

● 如果num_digits大于0，則將數(shù)字向下舍入到指定的小數(shù)位數(shù)。

● 如果num_digits等于0，則將數(shù)字向下舍入到最接近的整數(shù)。

● 如果num_digits小于0，則將數(shù)字向下舍入到小數(shù)點(diǎn)左邊的相應(yīng)位數(shù)。

12.TRUNC函數(shù)

功能說(shuō)明：將數(shù)字進(jìn)行截取返回整數(shù)。

語(yǔ)法：TRUNC(number, [num_digits])

參數(shù)：

● number必需。需要截尾取整的數(shù)字。

● num_digits可選。用于指定取整精度的數(shù)字，默認(rèn)值為0。

示例：區(qū)域A2:A4是要處理的數(shù)值，區(qū)域B2:B4是對(duì)數(shù)值進(jìn)行截取的說(shuō)明。

公式與步驟：

● 截取整數(shù)部分：單元格C2內(nèi)輸入公式“=TRUNC(A2)”。

● 截取到小數(shù)點(diǎn)右側(cè)2位：單元格C3內(nèi)輸入公式“=TRUNC(A3,2)”。

● 截取到小數(shù)點(diǎn)左側(cè)1位：單元格C4內(nèi)輸入公式“=TRUNC(A4, -1)”，結(jié)果如圖2-61所示。

提示：TRUNC與INT在對(duì)數(shù)值的整數(shù)部分進(jìn)行截取時(shí)有些相似。TRUNC是直接刪除數(shù)字的小數(shù)部分，而INT根據(jù)數(shù)字小數(shù)部分的值將數(shù)字向下舍入為最接近的整數(shù)。只有當(dāng)處理負(fù)數(shù)的時(shí)候，INT和TRUNC會(huì)有區(qū)別。例如，TRUNC(-3.14)返回-3，而INT(-3.14)返回-4。

以上是對(duì)數(shù)值運(yùn)算函數(shù)的介紹，并通過(guò)示例對(duì)函數(shù)進(jìn)行了功能講解，下面的數(shù)值運(yùn)算函數(shù)案例一～案例二是數(shù)值運(yùn)算函數(shù)的應(yīng)用擴(kuò)展。同一個(gè)案例采用了多種方法來(lái)解決。

13.數(shù)值運(yùn)算函數(shù)案例一

案例說(shuō)明：區(qū)域A2:A11是隨機(jī)生成的用戶年齡（范圍是21～60歲），D3:D6是年齡分段，需要統(tǒng)計(jì)不同年齡段的用戶人數(shù)。

公式與步驟：

● 方法一：單元格B2輸入公式“=CEILING(A2,10)”，然后向下拖拽復(fù)制公式，在區(qū)域B2:B11內(nèi)生成年齡分段組輔助數(shù)據(jù)。單元格E3內(nèi)輸入公式“=COUNTIFS(B:B, ROW()*10)”，然后向下拖拽復(fù)制公式。

● 方法二：單元格F3內(nèi)輸入數(shù)組公式“{=SUM((CEILING($A$2:$A$11,10)=ROW()*10)+0)}”，然后向下拖拽復(fù)制公式。

● 方法三：單元格G3內(nèi)公式為“=COUNTIFS(A:A,"＞"&((ROW()-1)*10),A:A,"＜="&(ROW()*10))”，然后向下拖拽復(fù)制公式，結(jié)果如圖2-62所示。

提示：

● 方法一用CEILING函數(shù)把年齡數(shù)據(jù)向上取整，全部處理成10的整數(shù)倍，借助于ROW函數(shù)拼湊年齡段的條件，最后用COUNTIFS函數(shù)進(jìn)行單條件計(jì)數(shù)。

● 方法二用CEILING函數(shù)把年齡數(shù)據(jù)向上取整，全部處理成10歲的整數(shù)倍，然后將處理好的數(shù)組和年齡組的上限進(jìn)行比較，如果相等，返回TRUE，如果不相等，返回FALSE，從而生成布爾值數(shù)值，通過(guò)加0處理成數(shù)值數(shù)組，最后用SUM數(shù)組公式進(jìn)行求和。

● 方法三用ROW函數(shù)拼湊成每個(gè)年齡分段組的上限和下限，最后用COUNTIFS函數(shù)進(jìn)行多條件計(jì)數(shù)。

14.數(shù)值運(yùn)算函數(shù)案例二

案例說(shuō)明：區(qū)域A2:A11內(nèi)隨機(jī)生成介于1～100的數(shù)值，然后分別統(tǒng)計(jì)奇數(shù)和偶數(shù)的個(gè)數(shù)。

公式與步驟：

1）單元格A2內(nèi)輸入公式“=RANDBETWEEN(1,100)”，然后向下拖拽到單元格A11位置進(jìn)行公式復(fù)制。

2）選中A列或區(qū)域A1:A11，按〈Ctrl+C〉組合鍵進(jìn)行復(fù)制，選中區(qū)域生成虛線之后，右擊“粘貼選項(xiàng)”里面選擇“粘貼值”，此時(shí)單元格內(nèi)的公式將會(huì)消失，目的是讓隨機(jī)函數(shù)生成的數(shù)值固定不變。

3）單元格E2內(nèi)輸入數(shù)組公式“{=SUM(MOD(A2:A11,2))}”實(shí)現(xiàn)奇數(shù)個(gè)數(shù)統(tǒng)計(jì)。

4）單元格E3內(nèi)輸入數(shù)組公式“{=SUM(IF(MOD(A2:A11,2)=0,1,0))}”實(shí)現(xiàn)偶數(shù)個(gè)數(shù)統(tǒng)計(jì)，結(jié)果如圖2-63所示。

提示：

● 用MOD函數(shù)將隨機(jī)數(shù)值除以2求余，結(jié)果返回1（奇數(shù)）或者0（偶數(shù)），然后對(duì)結(jié)果中的數(shù)值1直接套用SUM數(shù)組進(jìn)行求和，統(tǒng)計(jì)的就是奇數(shù)的個(gè)數(shù)。

● 用MOD函數(shù)將隨機(jī)數(shù)值除以2求余，結(jié)果返回1（奇數(shù)）或者0（偶數(shù)），然后套用IF函數(shù)，將余數(shù)與數(shù)值0進(jìn)行比較，相等返回1，否則返回0，再套用SUM數(shù)組進(jìn)行求和，統(tǒng)計(jì)的就是偶數(shù)的個(gè)數(shù)。