關於 EXCEL 內的浮點數儲存內容

One-minute read
IEEE754 • EXCEL • Math

遇到 Issue when importing float as string from Excel. Adding precision incorrectly 跟 Excel importation may misinterpret fractional numbers 提到的問題，細細研究才發現水很深啊～ 😏

問題重現 Link to heading

  1. 建立一個 Excel 檔案，並在工作表 1 的 A1 儲存格寫上 0.0004
  2. 使用 excelize 讀取 A1 的值獲得 4.0000000000000002E-4
  3. 接著使用 decimal 解析這個值得到 0.00040000000000000002

然後改用 C# 利用 Microsoft.Office.Interop.Excel 跟 ExcelDataReader 解析出來卻是正確的 0.0004。

原以為是 excelize 的 bug，於透過解壓縮 *.xlsx 的方式找到 \xl\worksheets\sheet1.xml 發現裡面真的是存 4.0000000000000002E-4 😵

差異原因 Link to heading

基本上這篇 從 IEEE 754 標準來看為什麼浮點誤差是無法避免的 已經講得蠻清楚的。另外也可以透過這個 Double (IEEE754 Double precision 64-bit) Converter 來得到

0.0004 Most accurate representation = 4.00000000000000019168694409544E-4

目前測試的結果看起來 Excel 會依照小數點位數採取不同方式來記錄數字(原因不明)

  • 1 位：直接存
  • 2 位(不知道為什麼有這兩種差異)：
    • $0.07 = 7.0000000000000007\mathrm{E}{-2}$
    • 其他則直接用浮點換算的 0.XXXXXXXXXXXXXXXXXX(共18位) 紀錄。
  • 超過 2 位：以科學符號紀錄浮點換算的值，並保留 17 個數字，然後第 18 個數字四捨五入。

所以原本的問題

$$ 0.0004 \approx 4.00000000000000019168694409544\mathrm{E}{-4} \approx 4.0000000000000002\mathrm{E}{-4} $$

後記 Link to heading

  1. 參考 Read Excel File in C# 使用 Microsoft.Office.Interop.Excel 抓的 Value2 型態是 Double 所以轉換出的結果就是畫面上看到的那樣。
  2. 對於 Office Open XML 有興趣可以參考這幾篇：
  3. 安裝 ExcelDataReader 記得也 安裝 ExcelDataReader.DataSet 不然沒法使用範例的 var result = reader.AsDataSet();。
  4. 找到這篇 Excel 技巧整理，雖然現在工作不太接觸 Excel 但還是有備無患。
  5. 也找到微軟官方提供的 Office 產品疑難排解。