來自：精進Excel（ID： SeniorExcel）
這天，雷哥找到星爺訴苦，因為他碰到了一個無比棘手的問題：使用Excel做最簡單的求和計算時，得出的結果總是不對，每次都是錯0.01的樣子，如下圖所示。
圖1：簡單的加法運算
其實Excel具有15位有效數字的計算精度，對於上圖中的兩位小數來說，應該手到擒來才對，出現這樣的錯誤確實讓人奇怪。
這個問題要分兩種情況討論。
情況1：浮點運算誤差，這種情況確實是Excel的責任，我們應該討伐Excel；
情況2：是我們使用Excel的姿勢不對導致的錯誤，我們應該自我檢討。
Part 1
浮點運算誤差
浮點運算誤差通常出現在使用小數運算，或運算過程中包含小數的情況中，在減法和除法中比較多見，這算誤差的出現是隨機的，並不一定總能碰到。但是，以目前的科技，這種誤差總是客觀存在的。
1-1 出現的原因：
①由於計算機內部以二進位保存，所以十進位的有限位的小數，在計算機內部會是一個無限位的小數。
例如：十進位的0.9雖然只有一位小數，轉成2進位是無限循環小數0.1110011001100110011...
②軟體保存浮點數的精度有限，Excel可以保存15位有效數字，有效數字以後的就被忽略了。
1-2 浮點運算案例
如下圖所示，在C3單元格中寫入簡單的計算=5.1-5.2+1，計算結果毫無爭議應該是0.9，但是如果設置足夠多的小數位數，會發現Excel的計算結果並不等於0.9。
本案例來自：《Excel這麼用就對了》
這雖是一個很小的誤差，但是它畢竟不等於真實的數值。如果出現在工程中，甚至可能造成重大的安全事故。
1-3 解決方法
Excel 提供了多種函數來彌補捨入誤差，通常使用ROUND 函數強制四捨五入即可。
如圖所示，使用ROUND函數矯正誤差。
Part 2
人為誤差
從前文可以看到，浮點運算產生的誤差非常小，而且不是隨機出現，像圖1中出現0.01這麼大的誤差，通常不是浮點運算造成的，而是我們被Excel欺騙了。
2-1 出現的原因：
通常是在實際的使用過程中，為了顯示上的需要，對單元格數值進行了格式設置，使其只顯示一部分小數位數。
比如：單元格中的數值是49.995，在【設置單元格格式】→【數字】→【數值】中設置小數位數為2位。
設置小數位數為2位
但是，使用自定義格式只是改變了數據的顯示式樣，數據本身並沒有發生變化，這就是導致表面上看起來的數值與實際數值不一致的原因。
知道了這個細節，再來看一下雷哥出錯的數據，立刻就能找到錯誤的根源的。當我們滑鼠點到A1單元格時，發現在單元格中顯示的數據是50.00，但是在編輯欄中，實際的數值卻是49.995。
當我們滑鼠點到A2單元格時，發現在單元格中顯示的數據是149.99，但是在編輯欄中，實際的數值卻是149.985。
也就是說，因為對數值進行了格式設置，只能顯示小數點後兩位，所以原來三位小數被「四捨五入」為兩位小數，注意，這個四捨五入是假的，只是表面的四捨五入。
如果是「五入」，那麼顯示的數值就比實際數值大；如果是「四舍」，那麼顯示的數值就比實際數值小。雷哥的兩個數值恰好都是「五入」，所以導致顯示的兩個數值的和就比實際數值的和大了0.01。
2-2 解決方法
方法 1：以顯示精度為準
！！事先聲明：此方法要慎重使用！！
因為問題出在「顯示的數值」和「Excel實際認為的數值」不一致，因此可以設置Excel「以顯示精度為準」選項來防止四捨五入錯誤。
如下圖，在【選項】→【高級】→【計算此工作簿時】中勾選「將精度設為所顯示的精度」，此選項會強制將工作表中每個數字的值成為顯示的值。
設置之後，如果在「設置單元格格式中」選擇顯示兩位小數的數字格式，則在保存工作簿時，所有超出兩位小數的精度均將會丟失。
！！！注意：此選項將影響整個工作簿，而且無法撤消此選項和恢復丟失的數據，因此屬於暴力、強制性的捨去位數，建議在啟用此選項之前先備份工作簿。
方法 2：四捨五入函數進行位數取捨
還可以使用四捨五入函數對數值進行「正確的四捨五入」，此類型的函數較多，放在第三部分詳細講解。
Part 3
數值位數取捨函數
使用函數進行數值位數取捨不僅改變了數據的顯示式樣，同時也改變了數據本身。根據位數取捨函數的作用不同，可以分為三類。
取整最常使用的就是INT函數。
INT函數是將數值向下取整為最接近的整數。
因為INT函數是向下取整，所以會返回比原始數據小但最接近的數值。
3-2 按部就班的四捨五入另一類對函數位數取捨的方法是四捨五入，這樣取捨的方式，在很大程度上也是能滿足精度要求的，相比直接捨去小數部分的取整，四捨五入的方式更接近真實情況。
能夠實現四捨五入的函數，以ROUND為基礎，總共可以延伸出三個不同的類型的函數。
1）ROUND函數對數值四捨五入
ROUND函數是取捨函數中使用率最高的函數之一，使用它可以方便地對數值進真正數學意義上的四捨五入。通常用在嵌套中，在計算過程中，對數值取捨為想要的精度。
=ROUND (number, digits)
①Number：要取捨的數值 ②Digits：要保留的小數位數
註：如果round函數只有參數number,等同於digits 等於 0。
ROUND函數使用案例：
參數二大於等於0的情況，比較容易理解。下面詳細講解小於零的情況：
-1，就是將6.231四捨五入為最接近的 10 的倍數
-2，就是將50.35四捨五入到最接近的100的倍數
……
2）ROUNDUP對數值進行向上取捨對數值進行取捨時，有時我們需要直接向上取捨，即無論要捨去的數是幾，都要向前一位進1，這時可以使用ROUNDUP函數，如圖所示。
3） ROUNDDOWN對數值進行向下取捨
與ROUNDUP函數相反，不管要捨去的數是幾，如果想將它們直接捨去而無需向前一位進1，可以使用ROUNDDOWN函數，如圖所示。
3-3 靈活多變的截位
截位指的是，直接將小數部分的某一位或多位直接舍掉，而不進行任何進位。
TRUNC函數能夠實現這一功能，它的作用是直接將數字的小數部分多餘位數截去，保留指定的位數。
TRUNC函數語法：
TRUNC（number，num_digits）
①number：要舍位的數值；②num_digits：保留的小數位數
經過TRUNC計算之後，無論小數有幾位，都被截為指定的位數，並且沒有進行四捨五入，完全直接捨去。
可以看出，當TRUNC的第二個參數為0時，TRUNC函數同樣可以保留數值的整數部分，但是TRUNC在進行取捨時，不考慮數值的正負，直接舍掉小數部分，只保留整數。
這種情況下，TRUNC和INT函數的區別如下表。
掌握了這些技能，還會被Excel欺騙麼？
●本文編號619，以後想閱讀這篇文章直接輸入619即可
●輸入m可以獲取到全部文章目錄
●輸入c可以獲取到全部動畫下載地址
Word技巧↓↓↓
PPT技巧精選↓↓↓