Excel中不可見浮點(diǎn)數(shù)誤差的來源探究

李鎮(zhèn)冰 唐啟奎

(青海省藥品檢驗(yàn)檢測(cè)院青海省中藏藥現(xiàn)代化研究重點(diǎn)實(shí)驗(yàn)室 青海 西寧 810016)

0 引 言

Excel不僅是電子表格軟件,更是數(shù)據(jù)處理軟件,使用者可以利用其豐富的功能和強(qiáng)大的函數(shù)庫以較低的學(xué)習(xí)成本實(shí)現(xiàn)對(duì)復(fù)雜數(shù)據(jù)的分析處理,因此在統(tǒng)計(jì)、工程、計(jì)量檢測(cè)等領(lǐng)域都有著廣泛的應(yīng)用[1-3]。

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

對(duì)于單純的計(jì)算而言,這只是數(shù)值的微小差異,通常并不會(huì)帶來嚴(yán)重的問題,但是涉及比較、查找等操作時(shí),則可能導(dǎo)致錯(cuò)誤的結(jié)論,因而存在較大的隱患[4]。

第一種誤差是可見的,對(duì)于具有一定計(jì)算機(jī)基礎(chǔ)、了解數(shù)據(jù)類型的用戶而言也是易于理解的,其產(chǎn)生的原因是十進(jìn)制小數(shù)轉(zhuǎn)換為二進(jìn)制浮點(diǎn)數(shù)時(shí)的精度損失[5];第二種誤差是不可見的,有學(xué)者認(rèn)為是由于單元格中所顯示的十進(jìn)制數(shù)和系統(tǒng)內(nèi)部存放的二進(jìn)制浮點(diǎn)數(shù)并不完全一致,存在一定的誤差[6],但未提供這一說法的依據(jù)。筆者最初對(duì)此有些疑惑,因?yàn)槲④浶QExcel嚴(yán)格遵循IEEE 754標(biāo)準(zhǔn)[7],運(yùn)算和存儲(chǔ)都基于64位二進(jìn)制雙精度浮點(diǎn)數(shù),相當(dāng)于十進(jìn)制的15位有效數(shù)字精度,而且可以很容易地確認(rèn)Excel中顯示的精度也是15位有效數(shù)字(在單元格中輸入“0.123 456 789 012 345 678 9”,結(jié)果會(huì)變?yōu)椤?.123 456 789 012 345”),即存儲(chǔ)和顯示精度似乎是一致的,不應(yīng)該存在“所見”與“所得”不一致的情況。

本文以示例的形式對(duì)Excel中可見與不可見浮點(diǎn)數(shù)誤差的相關(guān)問題進(jìn)行深入探討,并給出不可見誤差來自于存儲(chǔ)和顯示精度差異的直接證據(jù)。所用Excel版本為2019專業(yè)增強(qiáng)版(64位)。

1 構(gòu)造小數(shù)數(shù)列

采用以下方式在sheet1-sheet4工作表的A列分別構(gòu)造出公差為0.01、范圍為-2.59～2.59的等差數(shù)列,并在B列分別給出對(duì)應(yīng)的修約后的數(shù)據(jù)。結(jié)果如表1所示。

表1 構(gòu)造小數(shù)數(shù)列和修約的方式

2 數(shù)值查找匹配

2.1 “=”匹配

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

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

2.2 MATCH函數(shù)匹配

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

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

2.3 數(shù)值匹配結(jié)果

表2展示了部分代表性的匹配結(jié)果。

表2 sheet3數(shù)值匹配結(jié)果示例

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

表3 修約前后數(shù)據(jù)的數(shù)值匹配結(jié)果

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

此外,對(duì)sheet1-sheet4中修約后的數(shù)據(jù)進(jìn)行交叉匹配,未出現(xiàn)不匹配的結(jié)果,說明不同方式生成的數(shù)據(jù)在修約后都是完全一致的。

計(jì)算機(jī)中數(shù)據(jù)的存儲(chǔ)和運(yùn)算都是基于二進(jìn)制的形式,十進(jìn)制小數(shù)會(huì)被轉(zhuǎn)換為二進(jìn)制浮點(diǎn)數(shù),但是有些十進(jìn)制小數(shù)無法用有限位的二進(jìn)制數(shù)來表示,或者需要非常多位的二進(jìn)制數(shù)來準(zhǔn)確表示。但物理存儲(chǔ)空間是有限的,軟件設(shè)計(jì)時(shí)遵循的標(biāo)準(zhǔn)規(guī)范對(duì)于數(shù)據(jù)類型的長度也會(huì)有規(guī)定,這些都可能造成數(shù)據(jù)精度的損失。

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

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

3 文本查找匹配

3.1 獲取17位精度的數(shù)據(jù)

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

圖1 sheet1的XML代碼(局部)

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

3.2 15位文本“=”匹配

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

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

分別在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單元格。當(dāng)修約前后數(shù)據(jù)的文本完全一致時(shí),該公式結(jié)果為“TRUE”,否則為“FALSE”。該公式含義如下:(1) 當(dāng)文本長度大于5位時(shí),通常為17位有效數(shù)字,此時(shí)文本總長度減去17,得到其中可能包含的負(fù)號(hào)“-”、小數(shù)點(diǎn)“.”、前導(dǎo)“0”所占的位數(shù),再加上15,即為按15位有效數(shù)字截取文本;(2) 當(dāng)文本長度小于5位時(shí),直接取原文本,不進(jìn)行截取;(3) 用“=”對(duì)兩處文本進(jìn)行匹配。

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

3.3 全文本“=”匹配

全文本“=”匹配是對(duì)具有17位有效數(shù)字精度的數(shù)據(jù)文本進(jìn)行直接比較。

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

3.4 文本匹配結(jié)果

表4展示了部分代表性的匹配結(jié)果。

表4 sheet3文本匹配結(jié)果示例

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

表5 修約前后數(shù)據(jù)的匹配結(jié)果

從“.xml”文件中獲取的Excel存儲(chǔ)數(shù)據(jù)可知,在Excel中的顯示精度與存儲(chǔ)精度確實(shí)存在差異,存儲(chǔ)精度可達(dá)17位有效數(shù)字,而顯示精度只有15位。Heiser[8]從理論的角度解釋了為什么Excel中同時(shí)存在這兩種精度:IEEE 754標(biāo)準(zhǔn)中還有一種80位擴(kuò)展雙精度浮點(diǎn)數(shù)格式,相當(dāng)于十進(jìn)制數(shù)的17位精度,微軟將Excel設(shè)計(jì)為在寄存器中使用該擴(kuò)展精度進(jìn)行運(yùn)算,以獲得更高的中間結(jié)果精度,運(yùn)算后的結(jié)果轉(zhuǎn)換為15位精度后返回。

經(jīng)驗(yàn)證,MATCH、LOOKUP、VLOOKUP、HLOOKUP、RANK、FREQUENCY、DELTA等函數(shù)能夠識(shí)別超過15位有效數(shù)字精度的數(shù)值,而“=”和COUNTIF等函數(shù)則只能識(shí)別15位有效數(shù)字精度的數(shù)值。

4 結(jié)果與討論

本文通過比較Excel界面中顯示的數(shù)據(jù)以及從“.xml”文件獲取的內(nèi)部存儲(chǔ)數(shù)據(jù),直接證明了如下事實(shí):

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

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

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

采用不同的構(gòu)造方式生成了“相同”的小數(shù)數(shù)列,發(fā)現(xiàn):

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

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

采用不同的匹配公式將修約前后的數(shù)據(jù)進(jìn)行比較,發(fā)現(xiàn):

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

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

4.1 浮點(diǎn)數(shù)誤差的識(shí)別與修正

4.1.1可見誤差的識(shí)別與修正

可見誤差的識(shí)別可以使用LEN函數(shù)來獲取原始數(shù)據(jù)的長度,當(dāng)長度超過13位時(shí),則很可能出現(xiàn)了可見誤差。

可見誤差的修正建議使用ROUND函數(shù)。

微軟給出的另一種解決方式是設(shè)置顯示精度:在“文件”選項(xiàng)卡——“選項(xiàng)”——“高級(jí)”中勾選“將精度設(shè)為所顯示的精度”。勾選之后會(huì)導(dǎo)致工作簿中所有的數(shù)值都將精度調(diào)整至所顯示的精度,這一操作是不可逆的,強(qiáng)烈不建議采用。

4.1.2不可見誤差的識(shí)別與修正

不可見誤差的識(shí)別可以參照本文2.2節(jié)使用MATCH等函數(shù)對(duì)修約前后的數(shù)據(jù)進(jìn)行匹配,當(dāng)返回結(jié)果為“#N/A”時(shí),則表明出現(xiàn)了不可見誤差。

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

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

上述修正方式僅對(duì)靜態(tài)數(shù)據(jù)有效,因?yàn)楣降慕Y(jié)果是會(huì)重新計(jì)算的(“.xml”文件中同時(shí)儲(chǔ)存了公式和計(jì)算結(jié)果)。

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

4.2 目前無法解決的問題

仍然有一些與浮點(diǎn)數(shù)誤差相關(guān)的問題是目前無法解決的,例如:

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

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

這些問題只有留待相關(guān)理論與標(biāo)準(zhǔn)規(guī)范的進(jìn)一步發(fā)展,比如提升數(shù)據(jù)精度以減少此類問題的出現(xiàn)概率,或是發(fā)明更先進(jìn)的存儲(chǔ)和運(yùn)算方式,無損地進(jìn)行十進(jìn)制與二進(jìn)制的轉(zhuǎn)換,從而在根本上解決此類問題。

5 結(jié) 語

Excel的易用性是一把雙刃劍,一方面使得不具備編程能力的用戶也可以在Excel電子表格環(huán)境中進(jìn)行簡單的“編程”——通過組合使用Excel內(nèi)置函數(shù)以實(shí)現(xiàn)特定的功能;同時(shí)也使得原本應(yīng)當(dāng)由程序員處理的浮點(diǎn)數(shù)誤差變成了普通用戶需要面對(duì)和解決的問題[9]。

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