Excel舍入法則

使用Excel的用戶都知道，通過單元格格式或函數(shù)可以設(shè)定數(shù)值的精度，雖然讓表格中的數(shù)值在顯示時只保留指定位數(shù)的數(shù)字不難，但是要在對數(shù)值進(jìn)行舍入處理的同時不影響整個電子表格的計算結(jié)果卻不是一件簡單的事情。下面CHIP就來幫助大家掌握正確的舍入操作方法。

相關(guān)函數(shù)

在Excel中能夠?qū)?shù)值精度進(jìn)行修改的函數(shù)有很多，例如取整函數(shù)INT、EVEN、ODD，四舍五入函數(shù)ROUND、ROUNDUP、ROUNDDOWN、MROUND，截位函數(shù)TRUNC、FLOOR、CEILING以及文本和貨幣格式化函數(shù)FIXED、DOLLAR、RMB。此外，還能夠通過對單元格的數(shù)字格式進(jìn)行設(shè)置，讓數(shù)值以指定的精度顯示。但是很少有人會注意到用這些舍入函數(shù)處理得出的結(jié)果并不相同，而且即便是Excel專門用于舍入處理的函數(shù)ROUND，也會讓參與計算的數(shù)值在進(jìn)行舍入處理之后使最終結(jié)果發(fā)生改變，而在一個復(fù)雜的電子表格中，這一輕微的變化，將可能會導(dǎo)致巨大的誤差。

大部分舍入函數(shù)對負(fù)數(shù)的處理更容易出現(xiàn)偏差，例如用于向上舍入的ROUNDUP函數(shù)，如“=ROUNDUP（-2.5，0）”得到的結(jié)果是“-3”，而不是“-2”。實(shí)際上，處理類似的數(shù)值，使用同樣用于向上和向下舍入的CEILING、FLOOR函數(shù)會更合適一些，不過，這兩個函數(shù)的第二個參數(shù)不再是舍入后保留的位數(shù)，而是要舍入到的倍數(shù)，如使用參數(shù)“0.01”，就是舍入后的結(jié)果最接近于0.01的倍數(shù)。另外，專門用于舍入處理的函數(shù)ROUND還有一個衍生的函數(shù)MROUND，它可以采用類似CEILING、FLOOR函數(shù)的數(shù)據(jù)處理方式，設(shè)定要舍入到的倍數(shù)對數(shù)值進(jìn)行處理。

數(shù)學(xué)上的舍入

我們對Excel的舍入函數(shù)進(jìn)行一些研究后發(fā)現(xiàn)，它們采用的都是標(biāo)準(zhǔn)的四舍五入算法，對于數(shù)學(xué)上用于避免舍入結(jié)果誤差偏大的四舍六入五留雙算法（Banker's Rounding）缺乏支持。所謂的四舍六入五留雙也就是四舍六入，并在逢五而且五的后面沒有其他數(shù)值時舍入為最接近的偶數(shù)，例如“2.5”舍入結(jié)果為“2”，“3.5”舍入結(jié)果則為“4”。如果我們需要Excel支持這一算法，那么可以通過VBA添加一個自定義函數(shù)。首先，使用快捷鍵[ALT]+[F11]打開VBA編輯器，選擇“插入|模塊”，鍵入以下代碼：

Public Function RoundM（ByVal X As Double，Optional Decimals As Long = 0）

RoundM = Round（X， Decimals）

End Function

再選擇“文件|關(guān)閉并返回Microsoft Excel”即可使用自定義函數(shù)ROUNDM進(jìn)行四舍六入五留雙的舍入運(yùn)算了。

正確的舍入

數(shù)字格式 如果只是希望數(shù)字能以指定的精度顯示，那么使用單元格的“數(shù)字格式”功能即可獲得令人滿意的結(jié)果。

四舍五入顯示 在“設(shè)置單元格格式”的對話框中選擇數(shù)值、貨幣等分類，即可通過“小數(shù)位數(shù)”選項(xiàng)指定保留的位數(shù)。

危險的選項(xiàng) 選中“文件|選項(xiàng)|高級|計算此工作簿時”中的“將精度設(shè)為所顯示的精度”選項(xiàng)，工作簿中的數(shù)據(jù)精度將按顯示精度進(jìn)行設(shè)定。

向上和向下舍入 通過圖中的表格可以看出不同舍入函數(shù)的差異，對于負(fù)數(shù)的處理，ROUNDUP和ROUNDDOWN處理的結(jié)果在數(shù)學(xué)邏輯上是不正確的，相比之下，CEILING和FLOOR會好一些。

其他舍入方法 要靈活地使用各種舍入函數(shù)，如在必要時以一個特定的基數(shù)對數(shù)值進(jìn)行舍入，使用函數(shù)“=MROUND（數(shù)值，0.05）”（以5分為基數(shù)舍入貨幣金額）是不錯的選擇。

總和的問題 修改金額的精度，可能會導(dǎo)致總和不正確，為了避免會計上的誤差，可以考慮在計算的最后部分采用不同的方法，例如用總金額減去其余部分的金額。

數(shù)學(xué)上的舍入 四舍五入將導(dǎo)致計算結(jié)果誤差偏大，因而，我們可以使用自定義函數(shù)ROUNDM，以數(shù)學(xué)上的四舍六入五留雙算法來減少偏差。

期望的結(jié)果 很多時候我們既需要以適當(dāng)?shù)木蕊@示數(shù)值，同時也必須保持準(zhǔn)確的計算結(jié)果。