這天,，雷哥找到星爺訴苦，因為他碰到了一個無比棘手的問題：使用Excel做最簡單的求和計算時,，得出的結果總是不對,，每次都是錯0.01的樣子，如下圖所示,。

圖1：簡單的加法運算

其實Excel具有15位有效數(shù)字的計算精度,，對于上圖中的兩位小數(shù)來說，應該手到擒來才對,，出現(xiàn)這樣的錯誤確實讓人奇怪,。

這個問題要分兩種情況討論。

情況1：浮點運算誤差,，這種情況確實是Excel的責任,，我們應該討伐Excel；

情況2：是我們使用Excel的姿勢不對導致的錯誤,，我們應該自我檢討,。

Part 1

浮點運算誤差

浮點運算誤差通常出現(xiàn)在使用小數(shù)運算，或運算過程中包含小數(shù)的情況中,，在減法和除法中比較多見,，這算誤差的出現(xiàn)是隨機的,，并不一定總能碰到。但是,，以目前的科技,，這種誤差總是可觀存在的。

1-1 出現(xiàn)的原因：

①由于計算機內部以二進制保存,，所以十進制的有限位的小數(shù),，在計算機內部會是一個無限位的小數(shù)。

例如：十進制的0.9雖然只有一位小數(shù),，轉成2進制是無限循環(huán)小數(shù)0.1110011001100110011...

②軟件保存浮點數(shù)的精度有限,，Excel可以保存15位有效數(shù)字，有效數(shù)字以后的就被忽略了,。

1-2 浮點運算案例

如下圖所示,，在C3單元格中寫入簡單的計算=5.1-5.2+1，計算結果毫無爭議應該是0.9,，但是如果設置足夠多的小數(shù)位數(shù),，會發(fā)現(xiàn)Excel的計算結果并不等于0.9。

本案例來自：《Excel這么用就對了》

這雖是一個很小的誤差,，但是它畢竟不等于真實的數(shù)值,。如果出現(xiàn)在工程中，甚至可能造成重大的安全事故,。

1-3 解決方法

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

如圖所示,，使用ROUND函數(shù)矯正誤差,。

Part 2

人為誤差

從前文可以看到，浮點運算產(chǎn)生的誤差非常小,，而且不是隨機出現(xiàn),，像圖1中出現(xiàn)0.01這么大的誤差，通常不是浮點運算造成的,，而是我們被Excel欺騙了,。

2-1 出現(xiàn)的原因：

通常是在實際的使用過程中，為了顯示上的需要,，對單元格數(shù)值進行了格式設置,，使其只顯示一部分小數(shù)位數(shù)。

比如：單元格中的數(shù)值是49.995,，在【設置單元格格式】→【數(shù)字】→【數(shù)值】中設置小數(shù)位數(shù)為2位,。

設置小數(shù)位數(shù)為2位

但是，使用自定義格式只是改變了數(shù)據(jù)的顯示式樣,，數(shù)據(jù)本身并沒有發(fā)生變化,，這就是導致表面上看起來的數(shù)值與實際數(shù)值不一致的原因,。

知道了這個細節(jié)，再來看一下雷哥出錯的數(shù)據(jù),，立刻就能找到錯誤的根源的,。當我們鼠標點到A1單元格時,，發(fā)現(xiàn)在單元格中顯示的數(shù)據(jù)是50.00,，但是在編輯欄中，實際的數(shù)值卻是49.995,。

當我們鼠標點到A2單元格時,，發(fā)現(xiàn)在單元格中顯示的數(shù)據(jù)是149.99，但是在編輯欄中,，實際的數(shù)值卻是149.985,。

也就是說，因為對數(shù)值進行了格式設置,，只能顯示小數(shù)點后兩位,，所以原來三位小數(shù)被“四舍五入”為兩位小數(shù)，注意,，這個四舍五入是假的,，只是表面的四舍五入。

如果是“五入”,，那么顯示的數(shù)值就比實際數(shù)值大,；如果是“四舍”，那么顯示的數(shù)值就比實際數(shù)值小,。雷哥的兩個數(shù)值恰好都是“五入”,，所以導致顯示的兩個數(shù)值的和就比實際數(shù)值的和大了0.01。

2-2 解決方法

方法 1：以顯示精度為準

??！事先聲明：此方法要慎重使用！,！

因為問題出在“顯示的數(shù)值”和“Excel實際認為的數(shù)值”不一致,，因此可以設置Excel“以顯示精度為準”選項來防止四舍五入錯誤。

如下圖,，在【選項】→【高級】→【計算此工作簿時】中勾選“將精度設為所顯示的精度”,，此選項會強制將工作表中每個數(shù)字的值成為顯示的值。

設置之后,，如果在“設置單元格格式中”選擇顯示兩位小數(shù)的數(shù)字格式,，則在保存工作簿時，所有超出兩位小數(shù)的精度均將會丟失,。

?。,。∽⒁猓?/span>此選項將影響整個工作簿,，而且無法撤消此選項和恢復丟失的數(shù)據(jù),，因此屬于暴力、強制性的舍去位數(shù),，建議在啟用此選項之前先備份工作簿,。

方法 2：四舍五入函數(shù)進行位數(shù)取舍

還可以使用四舍五入函數(shù)對數(shù)值進行“正確的四舍五入”，此類型的函數(shù)較多,，放在第三部分詳細講解,。

3-1 簡單粗暴的取整

取整最常使用的就是INT函數(shù),。

INT函數(shù)是將數(shù)值向下取整為最接近的整數(shù),。

因為INT函數(shù)是向下取整，所以會返回比原始數(shù)據(jù)小但最接近的數(shù)值,。

3-2 按部就班的四舍五入

另一類對函數(shù)位數(shù)取舍的方法是四舍五入,，這樣取舍的方式，在很大程度上也是能滿足精度要求的,，相比直接舍去小數(shù)部分的取整,，四舍五入的方式更接近真實情況。

能夠實現(xiàn)四舍五入的函數(shù),，以ROUND為基礎,，總共可以延伸出三個不同的類型的函數(shù)。

1）ROUND函數(shù)對數(shù)值四舍五入

ROUND函數(shù)是取舍函數(shù)中使用率最高的函數(shù)之一,，使用它可以方便地對數(shù)值進真正數(shù)學意義上的四舍五入,。通常用在嵌套中，在計算過程中,，對數(shù)值取舍為想要的精度,。

• ROUND函數(shù)語法：

=ROUND (number, digits)  

①Number：要取舍的數(shù)值  ②Digits：要保留的小數(shù)位數(shù)

• 參數(shù)二有三種不同的設置

注：如果round函數(shù)只有參數(shù)number,等同于digits 等于 0。

ROUND函數(shù)使用案例：

參數(shù)二大于等于0的情況,，比較容易理解,。下面詳細講解小于零的情況：

-1，就是將6.231四舍五入為最接近的 10 的倍數(shù)

-2,，就是將50.35四舍五入到最接近的100的倍數(shù)

……

2）ROUNDUP對數(shù)值進行向上取舍

對數(shù)值進行取舍時,，有時我們需要直接向上取舍，即無論要舍去的數(shù)是幾,，都要向前一位進1,，這時可以使用ROUNDUP函數(shù),，如圖所示。

3） ROUNDDOWN對數(shù)值進行向下取舍

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

3-3 靈活多變的截位

截位指的是，直接將小數(shù)部分的某一位或多位直接舍掉,，而不進行任何進位,。

TRUNC函數(shù)能夠實現(xiàn)這一功能,，它的作用是直接將數(shù)字的小數(shù)部分多余位數(shù)截去,，保留指定的位數(shù)。

TRUNC函數(shù)語法：

TRUNC（number,，num_digits）

①number：要舍位的數(shù)值,；②num_digits：保留的小數(shù)位數(shù)

經(jīng)過TRUNC計算之后，無論小數(shù)有幾位,，都被截為指定的位數(shù),，并且沒有進行四舍五入，完全直接舍去,。