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

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

1.RAND函數(shù)

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

語法：RAND()

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

示例：隨機生成一組用戶的性別。

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

提示：

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

2.RANDBETWEEN函數(shù)

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

語法：RANDBETWEEN(bottom, top)

參數(shù)：

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

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

示例：

隨機生成一組學(xué)生的年齡（20～25）、語文成績（0～100分）以及手機號碼（以133開頭）。

公式與步驟：

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

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

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

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

3.ABS函數(shù)

功能說明：返回數(shù)字的絕對值。

語法：ABS(number)

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

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

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

4.MOD函數(shù)

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

語法：MOD(number, divisor)

參數(shù)：

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

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

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

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

提示：

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

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

5.POWER函數(shù)

功能說明：返回數(shù)字乘冪的結(jié)果。

語法：POWER(number, power)

參數(shù)：

● number必需?；鶖?shù)。

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

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

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

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

6.PRODUCT函數(shù)

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

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

參數(shù)：

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

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

示例：

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

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

公式與步驟：

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

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

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

7.CEILING函數(shù)

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

語法：CEILING(number,significance)

參數(shù)：

● number必需。要舍入的值。

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

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

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

提示：

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

● 如果number和significance都為負，則按遠離0的方向進行向下舍入。

● 如果number為負，significance為正，則按朝向0的方向進行向上舍入。

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

8.FLOOR函數(shù)

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

語法：FLOOR(number, significance)

參數(shù)：

● number必需。要舍入的值。

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

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

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

提示：

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

● 如果number和significance都為負，則按朝0的方向進行向上舍入。

● 如果number為負，significance為正，則按遠離0的方向進行向下舍入。

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

9.ROUND函數(shù)

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

語法：ROUND(number, num_digits)

參數(shù)：

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

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

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

公式與步驟：

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

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

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

● 四舍五入到小數(shù)點左側(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ù)點左邊的相應(yīng)位數(shù)。

10.ROUNDUP函數(shù)

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

語法：ROUNDUP(number, num_digits)

參數(shù)：

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

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

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

公式與步驟：

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

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

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

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

提示：

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

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

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

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

11.ROUNDDOWN函數(shù)

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

語法：ROUNDDOWN(number, num_digits)

參數(shù)：

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

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

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

公式與步驟：

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

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

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

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

提示：

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

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

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

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

12.TRUNC函數(shù)

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

語法：TRUNC(number, [num_digits])

參數(shù)：

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

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

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

公式與步驟：

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

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

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

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

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

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

案例說明：區(qū)域A2:A11是隨機生成的用戶年齡（范圍是21～60歲），D3:D6是年齡分段，需要統(tǒng)計不同年齡段的用戶人數(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ù)進行單條件計數(shù)。

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

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

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

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

公式與步驟：

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

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

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

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

提示：

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

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