Excel舍入法則

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

相關函數(shù)

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

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

數(shù)學上的舍入

我們對Excel的舍入函數(shù)進行一些研究后發(fā)現(xiàn)，它們采用的都是標準的四舍五入算法，對于數(shù)學上用于避免舍入結果誤差偏大的四舍六入五留雙算法（Banker's Rounding）缺乏支持。所謂的四舍六入五留雙也就是四舍六入，并在逢五而且五的后面沒有其他數(shù)值時舍入為最接近的偶數(shù)，例如“2.5”舍入結果為“2”，“3.5”舍入結果則為“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

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

正確的舍入

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

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

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

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

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

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

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

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