Excel中不可見浮點數誤差的來源探究

李鎮冰 唐啟奎

(青海省藥品檢驗檢測院青海省中藏藥現代化研究重點實驗室 青海 西寧 810016)

0 引 言

Excel不僅是電子表格軟件,更是數據處理軟件,使用者可以利用其豐富的功能和強大的函數庫以較低的學習成本實現對復雜數據的分析處理,因此在統計、工程、計量檢測等領域都有著廣泛的應用[1-3]。

但在使用Excel進行涉及小數的操作時,可能會遇到一些“莫名其妙”的困擾:比如計算結果與預期結果之間可能存在微小的差異——公式“=4.1-4.2+1”的結果在小數位數足夠多時顯示為“0.899 999 999 999 999”;又或者看上去相同的兩個數值,Excel卻認為它們是不同的——公式“=MATCH(0.1+0.2-0.1,0.1+(0.2-0.1),0)”返回的結果是“#N/A”,加法結合律似乎都失效了。

對于單純的計算而言,這只是數值的微小差異,通常并不會帶來嚴重的問題,但是涉及比較、查找等操作時,則可能導致錯誤的結論,因而存在較大的隱患[4]。

第一種誤差是可見的,對于具有一定計算機基礎、了解數據類型的用戶而言也是易于理解的,其產生的原因是十進制小數轉換為二進制浮點數時的精度損失[5];第二種誤差是不可見的,有學者認為是由于單元格中所顯示的十進制數和系統內部存放的二進制浮點數并不完全一致,存在一定的誤差[6],但未提供這一說法的依據。筆者最初對此有些疑惑,因為微軟宣稱Excel嚴格遵循IEEE 754標準[7],運算和存儲都基于64位二進制雙精度浮點數,相當于十進制的15位有效數字精度,而且可以很容易地確認Excel中顯示的精度也是15位有效數字(在單元格中輸入“0.123 456 789 012 345 678 9”,結果會變為“0.123 456 789 012 345”),即存儲和顯示精度似乎是一致的,不應該存在“所見”與“所得”不一致的情況。

本文以示例的形式對Excel中可見與不可見浮點數誤差的相關問題進行深入探討,并給出不可見誤差來自于存儲和顯示精度差異的直接證據。所用Excel版本為2019專業增強版(64位)。

1 構造小數數列

采用以下方式在sheet1-sheet4工作表的A列分別構造出公差為0.01、范圍為-2.59～2.59的等差數列,并在B列分別給出對應的修約后的數據。結果如表1所示。

表1 構造小數數列和修約的方式

2 數值查找匹配

2.1 “=”匹配

分別在sheet1-sheet4工作表的C2單元格輸入公式“=A2=B2”,拖曳填充至C520單元格。當修約前后數據的數值一致時,該公式結果為“TRUE”(表中以“T”表示),否則為“FALSE”(表中以“F”表示)。

另一等價方式:分別在sheet1-sheet4工作表的C2單元格輸入公式“=COUNTIF(A:A,B2)”,拖曳填充至C520。當修約前后數據的數值一致時,該公式結果為“1”,否則為“0”。

2.2 MATCH函數匹配

分別在sheet1-sheet4工作表的D2單元格輸入公式“=MATCH(A2,B:B,0)”,拖曳填充至D520。當修約前后數據的數值一致時,該公式結果為該數值所在行的行號(表中以“R”表示),否則為“#N/A”(表中以“N”表示)。

另一等價方式:分別在sheet1-sheet4工作表的D2單元格輸入公式“=VLOOKUP(A2,B:B,1,0)”,拖曳填充至D520。當修約前后數據的數值一致時,該公式結果為修約后的數值,否則為“#N/A”。

2.3 數值匹配結果

表2展示了部分代表性的匹配結果。

表2 sheet3數值匹配結果示例

表3中匹配結果為“FALSE”或“#N/A”的數目即為修約前后數值不匹配的數據數目。不同構造方式生成的數據,匹配結果也不相同,只有最后一種方式構造生成的數據在修約前后能夠完全匹配。

表3 修約前后數據的數值匹配結果

MATCH函數未能匹配的數據在sheet2和sheet3中多于“=”未能匹配的數據。經確認,“=”未能匹配的都是存在可見誤差的數據(如表2中通過“顯式步長”方式生成的數據“-0.10”實際顯示為“-0.099 999 999 999 999 6”)。而MATCH函數未能匹配的數據中,除包含全部存在可見誤差的數據外,還有部分數據看上去并無異常。

此外,對sheet1-sheet4中修約后的數據進行交叉匹配,未出現不匹配的結果,說明不同方式生成的數據在修約后都是完全一致的。

計算機中數據的存儲和運算都是基于二進制的形式,十進制小數會被轉換為二進制浮點數,但是有些十進制小數無法用有限位的二進制數來表示,或者需要非常多位的二進制數來準確表示。但物理存儲空間是有限的,軟件設計時遵循的標準規范對于數據類型的長度也會有規定,這些都可能造成數據精度的損失。

微軟宣稱Excel對于浮點數的存儲和運算嚴格遵循IEEE 754標準,雙精度浮點數以二進制形式存儲在65位范圍內(包括1個符號位、11位指數、1個隱含位和52位尾數),精度取決于尾數的大小。盡管存儲的數值可以非常大(最大值為1.797 693 134 862 32E+308,最小正數為2.225 073 858 507 2E-308),但是精度最高只有15位有效數字——在引言中已經驗證了Excel中的顯示精度確實為15位。當精度損失達到這一顯示精度(有效數字不超過15位)時,即呈現為可見的誤差。

但是這解釋不了“=”和MATCH函數匹配結果的不一致。盡管根據種種跡象可以推測這部分異常是由于Excel的存儲精度與顯示精度不一致導致的,MATCH函數能夠識別超出15位顯示精度的差異,而“=”只能識別15位的可見精度差異,但是缺乏直接的證據。那么在無法獲得Excel源代碼的情況下,如何能夠獲得直接的證據?

3 文本查找匹配

3.1 獲取17位精度的數據

Excel工作簿(2007版以上的“.xlsx”文件)本質是一個壓縮文件,其中的工作表其實是以“.xml”格式單獨存儲的。將Excel文件的擴展名由“.xlsx”改為“.zip”或“.rar”,然后使用解壓縮軟件打開,進入“xlworksheets”目錄,即可看到與各工作表名稱相同的“.xml”文件,使用網絡瀏覽器(如Internet Explorer、Chrome等)或記事本程序打開“.xml”文件后,即可看到其中的XML代碼(圖1)。XML代碼中,元素表示一個單元格,r屬性為單元格地址。的子元素的值就是該單元格的值(如果元素有t屬性,則子元素的值是各工作表共享的字符串的索引)。從圖1中可以看到其中的部分數據具有17位精度。

圖1 sheet1的XML代碼(局部)

將上文中的Excel示例文件另存為副本后按上述步驟操作,分別打開sheet1-sheet4對應的“.xml”文件,從中提取存儲的A2:B520區域內所有單元格的數據(此步驟需使用自編程序處理或使用Excel中Power Query查詢功能,限于篇幅不再贅述),然后以文本格式(文本格式可保留超過15位有效數字,而數值格式則會丟失15位之后的有效數字)錄入至原Excel文件中各自工作表的E2:F520區域,即獲得了原本在Excel中不可見的17位精度的數據(E、F列分別為修約前后的A、B列數據的17位精度文本)。

3.2 15位文本“=”匹配

15位文本“=”匹配的目的是模擬15位顯示精度下數據的匹配。

為了規避15位顯示精度的限制,17位精度的E、F兩列數據以文本格式存儲,因此無法使用常規的數值修約函數,而是通過下述公式“修約”至15位有效數字后再進行匹配。

分別在sheet1-sheet4工作表的G2單元格輸入公式“=IF(LEN(E2)>5,LEFT(E2,LEN(E2)-17+15),E2)=IF(LEN(F2)>5,LEFT(F2,LEN(F2)-17+15),F2)”,拖曳填充至G520單元格。當修約前后數據的文本完全一致時,該公式結果為“TRUE”,否則為“FALSE”。該公式含義如下:(1) 當文本長度大于5位時,通常為17位有效數字,此時文本總長度減去17,得到其中可能包含的負號“-”、小數點“.”、前導“0”所占的位數,再加上15,即為按15位有效數字截取文本;(2) 當文本長度小于5位時,直接取原文本,不進行截取;(3) 用“=”對兩處文本進行匹配。

上述公式并不嚴謹,因為:(1) 按文本截取的規則可能與數值存儲和運算時的舍入規則不同,導致誤判;(2) 長度超過5位但不足17位的數據、以科學記數法表示的數據,其最終截取的位數有可能不正確;(3) 截取后末尾有多個“0”的數據與未經截取末尾沒有“0”的數據無法匹配。但由于此時數據為文本格式,通過Excel公式難以處理上述問題,遇到此類情形時只能進行人工判斷(更好的方式是在支持17位精度以上的程序中將其作為數值舍入到15位之后進行比較)。

3.3 全文本“=”匹配

全文本“=”匹配是對具有17位有效數字精度的數據文本進行直接比較。

分別在sheet1-sheet4工作表的H2單元格輸入公式“=E2=F2”,拖曳填充至H520單元格。當修約前后數據的文本完全一致時,該公式結果為“TRUE”,否則為“FALSE”。

3.4 文本匹配結果

表4展示了部分代表性的匹配結果。

表4 sheet3文本匹配結果示例

表5中,MATCH函數數值匹配的結果與全文本 “=”匹配(17位精度)的結果完全一致,說明MATCH函數能夠識別17位有效數字的精度。15位文本“=”匹配的結果與“=”數值匹配的結果完全一致,說明“=”用于數值匹配時只能夠識別15位有效數字的精度(“=”用于文本匹配時不存在有效數字精度的問題)。

表5 修約前后數據的匹配結果

從“.xml”文件中獲取的Excel存儲數據可知,在Excel中的顯示精度與存儲精度確實存在差異,存儲精度可達17位有效數字,而顯示精度只有15位。Heiser[8]從理論的角度解釋了為什么Excel中同時存在這兩種精度:IEEE 754標準中還有一種80位擴展雙精度浮點數格式,相當于十進制數的17位精度,微軟將Excel設計為在寄存器中使用該擴展精度進行運算,以獲得更高的中間結果精度,運算后的結果轉換為15位精度后返回。

經驗證,MATCH、LOOKUP、VLOOKUP、HLOOKUP、RANK、FREQUENCY、DELTA等函數能夠識別超過15位有效數字精度的數值,而“=”和COUNTIF等函數則只能識別15位有效數字精度的數值。

4 結果與討論

本文通過比較Excel界面中顯示的數據以及從“.xml”文件獲取的內部存儲數據,直接證明了如下事實:

(1) Excel中數值的存儲精度為17位有效數字,而顯示精度為15位有效數字。

(2) 不高于顯示精度(不超過15位有效數字)的浮點數誤差表現為可見誤差。

(3) 高于顯示精度(超過15位有效數字)的浮點數誤差表現為不可見的誤差。

采用不同的構造方式生成了“相同”的小數數列,發現:

(1) 不同的操作方式(填充序列和公式)可能產生完全相同的結果(可見誤差與不可見誤差均相同),相關數據限于篇幅未體現在文中,讀者可根據本文所述方法進行驗證。

(2) 相似的操作(“隱式步長”“顯式步長”和“整數步長”)可能產生不同的結果(“隱式步長”構造的數據包含更多的可見誤差,“顯式步長”構造的數據包含更多的不可見誤差,而“整數步長”構造的數據則不包含可見誤差和不可見誤差),甚至如引言中所舉的例子,僅僅是運算順序的差異都可能導致不同的結果。

采用不同的匹配公式將修約前后的數據進行比較,發現:

(1) MATCH、LOOKUP、VLOOKUP、HLOOKUP、RANK、FREQUENCY、DELTA等函數能夠識別17位有效數字的存儲精度。

(2) “=”和COUNTIF等函數只能識別15位有效數字的顯示精度。

4.1 浮點數誤差的識別與修正

4.1.1可見誤差的識別與修正

可見誤差的識別可以使用LEN函數來獲取原始數據的長度,當長度超過13位時,則很可能出現了可見誤差。

可見誤差的修正建議使用ROUND函數。

微軟給出的另一種解決方式是設置顯示精度:在“文件”選項卡——“選項”——“高級”中勾選“將精度設為所顯示的精度”。勾選之后會導致工作簿中所有的數值都將精度調整至所顯示的精度,這一操作是不可逆的,強烈不建議采用。

4.1.2不可見誤差的識別與修正

不可見誤差的識別可以參照本文2.2節使用MATCH等函數對修約前后的數據進行匹配,當返回結果為“#N/A”時,則表明出現了不可見誤差。

對于不可見誤差的修正,除了使用ROUND函數或設置為顯示精度的方式外,筆者在研究中還發現了另一種有趣的方式:(1) 將sheet3中A列的數據復制并選擇性粘貼為值;(2) 任意選取“=”匹配結果為“TRUE”、MATCH函數匹配結果為“#N/A”的數據(即存在不可見誤差的數據),如A12單元格的“-2.49”,雙擊該單元格進入編輯狀態,直接按Enter鍵,此時MATCH函數匹配結果將由“#N/A”變為“12”,說明不可見誤差被修正了。

通過以下方式可以修正整列數據的不可見誤差:選中A列,點擊“數據”選項卡——“數據工具”功能區——“分列”,直接點擊“完成”。此時MATCH函數匹配結果中#N/A的數目將從最初的377個縮減至16個(這16個均為存在可見誤差的數據)。

上述修正方式僅對靜態數據有效,因為公式的結果是會重新計算的(“.xml”文件中同時儲存了公式和計算結果)。

筆者推測其原理如下:原本顯示的數值是經過某些運算或操作得到的,與直接輸入的數據存在微小差異原數據為“-2.489 999 999 999 999 8”,修正后的數據為“-2.490 000 000 000 000 2”,與修約后的數據相同),修正的過程相當于按照15位顯示精度進行了修約,舍棄了15位之后的尾數,因而與ROUND函數修約的結果一致(誤差只出現在15位有效數字之后,因此,在本例中修約至15位有效數字之前都將得到相同的結果)。

4.2 目前無法解決的問題

仍然有一些與浮點數誤差相關的問題是目前無法解決的,例如:

(1) 無法再在單元格中輸入“39 524.848”,結果會顯示為“39 524.847 999 999 9”。

(2) 無法進行超出15位有效數字的運算,“=1+0.000 123 456 789 012 345”的結果為“1.000 123 456 789 01”。

這些問題只有留待相關理論與標準規范的進一步發展,比如提升數據精度以減少此類問題的出現概率,或是發明更先進的存儲和運算方式,無損地進行十進制與二進制的轉換,從而在根本上解決此類問題。

5 結 語

Excel的易用性是一把雙刃劍,一方面使得不具備編程能力的用戶也可以在Excel電子表格環境中進行簡單的“編程”——通過組合使用Excel內置函數以實現特定的功能;同時也使得原本應當由程序員處理的浮點數誤差變成了普通用戶需要面對和解決的問題[9]。

Excel中淡化了數據類型的概念,但是對于整數和小數的操作仍然是有區別的,這一區別源自部分十進制小數轉換為二進制時不可避免的精度損失。因此進行涉及小數的比較與查找等操作時,務必非常謹慎。使用ROUND函數修約應當成為習慣,這是規避此類問題最有效的手段。需要指出的是,修約并沒有避免精度損失(如表4中修約后的“-2.49”在17位精度下為“-2.490 000 000 000 000 2”,仍然存在誤差),而是通過主動損失精度,將可能存在誤差的部分尾數舍棄,只保留真正關注的“主體”數據。