5.1 求和總是差0.01

看一個(gè)簡(jiǎn)單的例子，根據(jù)年收入計(jì)算出兩個(gè)人的月均收入分別為1.66金額1.68，相加后應(yīng)該是3.34才對(duì)，但是Excel中計(jì)算的結(jié)果卻少了0.01，如圖5-1所示。

圖5-1

類似這樣的問題在Excel的使用過程中經(jīng)常都會(huì)遇到，出現(xiàn)這種問題通常是有小數(shù)參與計(jì)算的時(shí)候，一種原因是計(jì)算機(jī)內(nèi)部的浮點(diǎn)運(yùn)算誤差，另一種原因是設(shè)置單元格格式顯示指定小數(shù)位數(shù)后與實(shí)際數(shù)值不同造成的誤差。

Excel 提供了多種函數(shù)來彌補(bǔ)舍入誤差，通常使用ROUND 函數(shù)強(qiáng)制四舍五入即可。

可以在計(jì)算月均收入的公式中添加ROUND函數(shù)，結(jié)果就沒有問題了，如圖5-2所示。

圖5-2

ROUND函數(shù)需要兩個(gè)參數(shù)，第一參數(shù)是要進(jìn)行取舍的數(shù)據(jù)，第二參數(shù)是要保留的小數(shù)位數(shù)。

當(dāng)?shù)诙?shù)大于0時(shí)，會(huì)按指定的小數(shù)位進(jìn)行四舍五入；當(dāng)?shù)诙?shù)等于0時(shí)，會(huì)四舍五入到最接近的整數(shù)；當(dāng)?shù)诙?shù)小于0時(shí)，會(huì)在小數(shù)點(diǎn)左側(cè)進(jìn)行四舍五入，如圖5-3所示。

圖5-3

對(duì)數(shù)值進(jìn)行取舍時(shí)，有時(shí)我們需要直接向上取舍，即無論要舍去的數(shù)是幾，都要向前一位進(jìn)1，這時(shí)可以使用ROUNDUP函數(shù)，例如快遞在計(jì)算重量的時(shí)候，不管小數(shù)部分是幾，重量都要加1，如圖5-4所示。

圖5-4

與ROUNDUP函數(shù)相反，不管要舍去的數(shù)是幾，想直接舍去而無需向前一位進(jìn)1，可以使用ROUNDDOWN函數(shù)，如圖5-5所示。

圖5-5

ROUNDUP和ROUNDDOWN函數(shù)的第二參數(shù)與ROUND函數(shù)第二參數(shù)功能完全一樣，不再贅述。

5.2 更多的取舍函數(shù)

除了ROUND系列的三個(gè)取舍函數(shù)，還有這幾個(gè)比較常用的取舍函數(shù)：MROUND、CEILING、FLOOR和TRUNC，以下逐一介紹。

1. TRUNC函數(shù)：

函數(shù)功能為按照指定的位數(shù)進(jìn)行截取數(shù)字，經(jīng)過TRUNC計(jì)算之后，無論小數(shù)有幾位，都被截為指定的位數(shù)，并且沒有進(jìn)行四舍五入，完全直接舍去，如圖5-6所示。

圖5-6

可以看出，當(dāng)TRUNC的第二個(gè)參數(shù)為0時(shí)，TRUNC函數(shù)同樣可以保留數(shù)值的整數(shù)部分，但是TRUNC在進(jìn)行取舍時(shí)，不考慮數(shù)值的正負(fù)，直接舍掉小數(shù)部分，只保留整數(shù)。這種情況下，TRUNC和INT函數(shù)的區(qū)別在于當(dāng)數(shù)字為負(fù)數(shù)時(shí)，TRUNC直接去掉小數(shù)部分，而INT則向遠(yuǎn)離0的方向進(jìn)位，如圖5-7所示。

圖5-7

2.MROUND、CEILING與FLOOR函數(shù)：

和前面介紹的取舍函數(shù)不同的是，這三個(gè)函數(shù)不是按照位數(shù)進(jìn)行取舍，而是按照指定的基數(shù)進(jìn)行取舍。

MROUND的作用是得到與基數(shù)的倍數(shù)中與數(shù)字最接近的值，當(dāng)數(shù)字除以基數(shù)的余數(shù)大于或等于基數(shù)的一半時(shí)，向遠(yuǎn)離0的方向舍入，反之向接近0的方向舍入。例如數(shù)字為7.9，基數(shù)為2時(shí)，與7.9最接近的2的倍數(shù)有兩個(gè)，6或者8，因?yàn)?.9除以2的余數(shù)大于2的一半，所以結(jié)果為8。

提示：使用MROUND函數(shù)時(shí)，數(shù)字與基數(shù)必須同為正數(shù)或者同為負(fù)數(shù)，否則會(huì)得到#NUM!這個(gè)錯(cuò)誤值。

CEILING的作用是得到將數(shù)字向上舍入（沿絕對(duì)值增大的方向）為最接近的指定基數(shù)的倍數(shù)，例如數(shù)字為-2.2，基數(shù)為-2時(shí)，-2的倍數(shù)最接近-2.2的有兩個(gè)，-2和-4，絕對(duì)值大的就是-4。

FLOOR的作用正好與CEILING相反，是得到將數(shù)字向下舍入（沿絕對(duì)值減小的方向）為最接近的指定基數(shù)的倍數(shù)。圖5-8列舉了這三個(gè)函數(shù)針對(duì)不同數(shù)字和基數(shù)的計(jì)算結(jié)果。

圖5-8

5.3 條條大路通羅馬

公式=INT(ROW(B2)/2)*2可以得到一個(gè)數(shù)列，使用公式=MROUND(ROW(A1),2)、=CEILING(ROW(A1),2)、=FLOOR(ROW(B2),2)可以得到完全相同的效果，如圖5-9所示。

圖5-9

INT可以取整，同樣用FLOOR甚至是MOD也可以實(shí)現(xiàn)取整的效果，如圖5-10所示。

圖5-10

TRUNC可以去掉數(shù)字的小數(shù)部分，ROUNDDOWN也可以有同樣的功能，甚至用INT結(jié)合邏輯值也可以得到相同的結(jié)果，如圖5-11所示。

圖5-11

以上三個(gè)例子說明了一個(gè)事實(shí)：公式?jīng)]有好壞之分，能夠得到自己需要的結(jié)果就是好的。任何一個(gè)問題都可能有多種方法去實(shí)現(xiàn)，嘗試用不同的思路和函數(shù)去得到同樣的結(jié)果，這對(duì)于開拓思路和公式函數(shù)的修煉會(huì)有很大的好處。