說到浮點誤差，有一個比較高深的計算機概念——浮點數，有興趣的同學可以問度娘。簡單說，在計算機的計算中，隻能存儲和處理二進制數據，就是1和0。excel在計算時，首先要把十進制的數值轉換為二進制，交給計算機處理，最後再把二進制的結果轉換為十進制，顯示到Excel中。

十進制數值轉換為二進制數值的計算過程為：

1、整數部分：

連續用該整數除以2取餘數，然後用商再除以2，直到商等于0為止，最後把各個餘數按相反的順序排列。

如果将十進制數值22轉換為二進制數值，其計算步驟如下：

22除以2結果為11，餘數為0。

11除以2結果為5，餘數為1。

5除以2結果為2，餘數為1。

2除以2結果為1，餘數為0。

1除以2結果為0，餘數為1。

最後将餘數按相反的順序排列，整數22的二進制結果為10110。

2、小數部分：

用2乘以十進制小數，将得到的整數部分取出，再用2乘以餘下的小數部分，然後再将積的整數部分取出。如此往複，直到積中的小數部分為0或者達到所要求的精度為止，最後把取出的整數部分按順序排列。

如果将十進制數值0.8125轉換為二進制數值，其計算步驟是：

首先用0.8125乘以2等于1.625，取整結果為1，小數部分是0.625。

0.625乘以2等于1.25，取整結果為1，小數部分是0.25。

0.25乘以2等于0.5，取整結果為0，小數部分是0.5。

0.5乘以2等于1.0，取整結果為1，小數部分是0，計算結束。

将乘積的取整結果順序排列，結果就是0.1101。

3、整數和小數混合數值：

含有小數的十進制數轉換成二進制時，先将整數、小數部分分别進行轉換，然後将轉換結果相加。

但是這種轉換有時候會陷入無限循環，比如按照上述方法将小數0.6轉換為二進制代碼，計算結果就是：

0.10011001100110011……

其中的0011部分會無限重複，無法用有限的空間量來表示。當結果超出Excel計算精度，産生了一個因太小而無法表示的數字時，在Excel中的處理結果就是0。

所以在不同進制之間轉換時，就很容易出現一些非常細小的誤差，這就是浮點誤差，

Excel裡的浮點由于函數機制等原因，在某些極端情況下隐蔽性很高，所以我再寫篇文章介紹下這個坑。

1，計算時産生的大量小數位數

這個結果出現大量小數位數的原因在之前的文章裡已經介紹得很詳細了，這裡不再贅述。

2，無法錄入的數據

由于浮點的問題，有些特殊數錄入就能生成浮點，導緻無法錄入正确内容，32768.598有興趣你就試試在excel裡錄入這個數。

然而這兩種浮點數，在把小數位數調高後都很容易發現異常，因此稍微對Excel有一定了解的夥伴都會知道用ROUND函數修正精度，但是，Excel裡存在一類更危險的的浮點數。

截圖為目前這類數裡目前測試下最簡單的算式。0.1 0.2，無論如何調高小數位數還是顯示為0.3，使用公式求值或者F9抹黑算式檢查也不會出現大量小數位數，均為0.3。等号判斷下和直接鍵入的0.3也是相同的。

但是，如果你把這個算式用于MATCH等函數内，結果卻是報錯的……

因為對Excel來說，浮點精度是可以超過15位的，可以視為高精度的浮點誤差

百度後發現JS裡0.1 0.2就是典型浮點誤差結果為：

0.30000000000000004

由于Excel有15位精度限制，故隻顯示出0.3，因為15位下剩餘小數位數均為0未顯示出來，因此，這個性質說明Excel的浮點精度是可以高于15位的。

在實際測試中，發現部分函數能識别這種高于15位的精度差異，而且這種差異會影響公式結果，這些函數包括：

RANK、FREQUENCY、MATCH、MODE、VLOOKUP、MODE.MULT、HLOOKUP、LOOKUP

有好多同學已經被類似0.1 0.2這種不産生大量小數位數的浮點誤差坑過，因為使用F9或者等号檢測時都無法檢測出這種高于15位的精度差異。

看到這裡肯定有人會問，有沒有什麼函數能直接識别這種精度差？

答案是當然有，有個DELTA函數專門判斷參數是不是相等的，可以識别出來，這個函數的結果為1，說明參數完全相等，為0則說明是有差異的：

除了使用ROUND修正精度的方式外，有時候我們也可以使用像COUNTIF這種不識别高于15位的精度的函數來解決這類數值的匹配問題，因為COUNTIF的第二參數在沒有無比較運算符和通配符等時，會将數字全部識别為數值型統計且不會識别高于15位的精度。

好了，今天咱們的内容就是這些吧，祝各位小夥伴一天好心情！

圖文制作：流浪鐵匠

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!