浮點運算可能會在Excel 中提供不正確的結果- Office - Microsoft ...

因為四捨五入或資料截斷，所以這可能會影響某些數位或公式的結果。

概觀. Microsoft Excel 是以IEEE 754 規格為設計，以判斷其儲存及計算浮點數的方式。

跳到主要內容 已不再支援此瀏覽器。

請升級至MicrosoftEdge，以利用最新功能、安全性更新和技術支援。

下載MicrosoftEdge 其他資訊 目錄 結束焦點模式 儲存 編輯 共用 Twitter LinkedIn Facebook 電子郵件 WeChat 目錄 浮點運算可能會在Excel中提供不正確的結果 發行項 11/16/2021 h 適用於: Excel2010,Excel2013,ExcelforOffice365,MicrosoftExcelforMac2011,ExcelforMacforOffice365 此頁面有所助益嗎？ 請為您的體驗評分 Yes No 還有其他意見反應嗎？ 系統會將意見反應傳送給Microsoft：按下[提交]按鈕，您的意見反應將用來改善Microsoft產品和服務。

隱私權原則。

送出 謝謝。

本文內容 注意 Office365專業增強版即將重新命名為Microsoft365企業版應用程式。

如需關於此變更的詳細資訊，請閱讀此部落格文章。

摘要 本文將討論MicrosoftExcel如何儲存及計算浮點數。

因為四捨五入或資料截斷，所以這可能會影響某些數位或公式的結果。

概觀 MicrosoftExcel是以IEEE754規格為設計，以判斷其儲存及計算浮點數的方式。

IEEE是電氣和電子工程師的研究院，一種除了其他事項以外，還會決定電腦軟體和硬體的標準。

754規格是非常廣泛採用的規格，說明如何在二進位電腦中儲存浮點數。

這是很常見的，因為它允許將浮點數儲存在合理的空間量內，並且計算速度相對快。

754標準是用於浮點數和數值資料處理者，其基本上是目前所有以PC為基礎的微處理器（包括Intel、Motorola、Sun及MIPS處理器）。

當數位儲存時，對應的二進位數可以代表每個數位或分數。

例如，分數1/10可以表示為十進位數字系統為0.1。

不過，二進位格式的相同數位會變成下列重複的二進位十進位： 0001100110011100110011(等等) 這可無限重複。

此數位不能以有限的(大小限制的)空間量來表示。

因此，此數位會在儲存時向下舍入約-2.8E-17。

不過，有三個一般類別的IEEE754規格的限制： 最小/最低限制 精度 重複的二進位數 其他相關資訊 最小/最低限制 所有電腦都有可以處理的最大和最小數目。

因為儲存號碼的記憶體位數是有限的，所以可儲存的最大或最小數目也是有限的。

Excel，可儲存的最大數位是1.79769313486232e+308，而且可以儲存的最小正數是2.2250738585072e-308。

我們遵循IEEE754的案例 下溢：產生的數位太小而無法表示時，會發生下溢。

在ieee和Excel中，結果為0(，但IEEE的概念為-0，且Excel不是)。

溢出：當數位太大而無法呈現時，就會發生溢出。

Excel(#NUM！)使用此案例自身的特殊標記法。

我們不遵循IEEE754的情況 非正常化數位：非正常化數位是以0的指數來表示。

在此情況下，會將整個數位儲存在尾數中，而且尾數沒有隱含的前導1。

因此，您會失去精確度，而且數值越小，精確度越好。

在此範圍的小型端只有一位數的精度。

範例：正規化的數位具有隱式前導1。

例如，如果尾數代表0011001，則正規化的數位會變成10011001，因為暗示的前導1。

非正常化的數位沒有隱式前導一個，因此在我們的0011001範例中，非正常化的數位會保持不變。

在此情況下，正規化的數位有八個有效位數(10011001)而非正常化數目具有五個有效位數(11001)具有前置零是無關緊要的。

非正常化數位基本上是一種解決方法，可讓數位小於儲存一般下限。

Microsoft不會因為其本質上的非正常化數位有可變位數，所以不會實現規格的選用部分。

這可能會導致大量的計算中輸入的錯誤。

正/負無窮大：當您以0做除數時，就會發生無窮大。

Excel不支援無窮大，而是會提供#DIV/0!在這些情況下發生錯誤。

非數位(NaN)：NaN是用來表示不正確運算(例如無窮大/無窮、無窮無窮大或-1)的平方根。

Nan允許程式繼續超過不正確操作。

Excel會立即產生錯誤，例如#NUM!或#DIV/0!。

精度 浮點數以二進位儲存在65位範圍內的三個部分：符號、指數及尾數。

符號 指數 尾數 1個符號位 11位指數 1個默示的bit+52位分數 符號(會儲存數位的正負號(正值或負數)，指數會儲存2的最大功率（2的最大/最低功率為+1023和-1022)），而且尾數會儲存實際的數位。

尾數的有限儲存區域會限制兩個連續的浮點數的(，也就是)的精確度。

尾數和指數都儲存為個別的元件。

因此，可能的精確度量會隨著(的尾數)數目而有所不同。

在Excel的情況下，雖然Excel可以將數位從1.79769313486232e308儲存至2.2250738585072e-308，但只能在15位數的精確度內執行。

此限制是嚴格遵循IEEE754規格的直接結果，而且不是Excel的限制。

在其他試算表程式中也可以找到這種精確度程度。

浮點數是以下列形式表示，其中的指數是二進位指數： X=分數*2^(指數-偏量) 分式是數位中正規化的分數部分，因為指數是調整的，所以前導位永遠為1。

如此一來，您就不需要儲存它，而是要有一位以上的精確度。

這就是暗示的位數。

這類似于科學標記法，您可以在其中操縱指數，以在小數點左邊使用一個數位;除了二進位之外，您可以一直操作指數，因為第一個位是1，因為只有1和0。

偏量是用來避免必須儲存負指數的偏量值。

單一精確度數值的偏量是127和1023(的雙精度數位的十進位)。

Excel會使用雙精度來儲存數位。

使用非常大數位的範例 在新活頁簿中輸入下列專案： A1:1.2E+200 B1:1E+100 C1:=A1+B1 儲存格C1中產生的值會是1.2E+200，與儲存格A1的值相同。

實際上，如果您使用IF函數比較儲存格A1和C1，例如，如果(A1=C1)，則結果會是TRUE。

這是由IEEE規格所導致，只儲存15個有效位數的精確度。

為了能夠儲存上述計算，Excel至少需要100位數的精確度。

使用非常小數位的範例 在新活頁簿中輸入下列專案： A1:0.000123456789012345 B1:1 C1:=A1+B1 儲存格C1中的結果值將會是1.00012345678901，而不是1.000123456789012345。

這是由IEEE規格所導致，只儲存15個有效位數的精確度。

為了能夠儲存上述計算，Excel至少需要19位數的精確度。

修正精確度錯誤 Excel提供兩種方法來補償舍入錯誤：ROUND函數和精度顯示或設定為[顯示的活頁簿]選項的精度。

方法1：ROUND函數 下列範例會使用舊資料，將數值強制為五位數。

這可讓您成功將結果與其他值進行比較。

A1:1.2E+200 B1:1E+100 C1:=ROUND(A1+B1,5) 這會產生1.2e+200。

D1：=IF(C1=1.2E+200，TRUE，FALSE) 這會導致值為TRUE。

方法2：顯示的精確度 在某些情況下，您可能會使用[以顯示為准]選項，避免舍入錯誤影響您的工作。

這個選項會強制工作表中每一個數位的值都是顯示的值。

若要開啟此選項，請遵循下列步驟。

在[檔案]功能表上，按一下[選項]，然後按一下[高級]類別。

在[計算此活頁簿]區段中，選取您想要的活頁簿，然後選取[設定為顯示的精度]核取方塊。

例如，如果您選擇顯示兩個小數位的數位格式，然後開啟[顯示精度]選項，當您儲存活頁簿時，除了兩位數以外的所有精確度都會遺失。

此選項會影響使用中的活頁簿，包括所有工作表。

您無法復原此選項，且無法復原遺失的資料。

建議您在啟用此選項之前儲存活頁簿。

重複的二進位數及具有接近零結果的計算 影響二進位格式之浮點數儲存的另一個混亂問題，就是在十進位基底中，一些有限的非重複數位，在二進位中是無限的重複數位。

最常見的範例是0.1值及其變化。

雖然這些數位可以以完美的方式表示，但在基底10中，相同的數位會在儲存為尾數時變成下列重複的二進位數： 000110011001100110011(等等) IEEE754規格對任何數位都沒有任何特殊的余量。

它會儲存尾數中的內容，並截斷其餘部分。

這會在儲存時產生大約-2.8E-17或0.000000000000000028的錯誤。

甚至常見的十進位分數，例如十進位0.0001，無法完全以二進位表示。

(0.0001是重複的二進位分數，其期間為104位)。

這與為何在重複0.33333333333333333333)中以(十進位表示分數1/3無法完全表示的原因。

例如，在MicrosoftVisualBasicforApplications中，請考慮下列簡單範例： SubMain() MySum=0 ForI%=1To10000 MySum=MySum+0.0001 NextI% Debug.PrintMySum EndSub 這會將0.999999999999996列印為輸出。

以二進位表示0.0001的小錯誤會傳播至總和。

範例：新增負數 在新活頁簿中輸入下列專案： A1：=(43.1-43.2)+1 以滑鼠右鍵按一下儲存格A1，然後按一下[設定儲存格格式]。

在[數位]索引標籤上，按一下[類別]底下的[科學將小數位位數設定為15。

Excel會顯示0.899999999999999，而不是顯示0.9。

因為(43.1-43.2)會先進行計算，所以會暫時儲存-0.1，並將儲存-0.1的錯誤引進計算中。

值達到零時的範例 在Excel95或更早版本中，在新的活頁簿中輸入下列專案： A1：=1.333+1.225-1.333-1.225 以滑鼠右鍵按一下儲存格A1，然後按一下[設定儲存格格式]。

在[數位]索引標籤上，按一下[類別]底下的[科學將小數位位數設定為15。

Excel95會顯示2.22044604925031e-16，而不是顯示0。

不過，Excel97會引進一種優化，嘗試糾正此問題。

當加法或減法運算導致值為或非常接近零時，Excel97和更新版本將會補償任何因轉換運算元為及自二進位時所引進的錯誤。

在Excel97和更新版本中執行時，會正確顯示0或0.000000000000000e+00（科學標記法）的範例。

如需有關浮點數和IEEE754規格的詳細資訊，請參閱下列WorldWide網站： https://www.ieee.org https://steve.hollasch.net/cgindex/coding/ieeefloat.html 本文內容