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

  • 發行項
  • 適用於:
    Excel 2010, Excel 2013, Excel for Microsoft 365, Microsoft Excel for Mac 2011, Excel for Mac for Microsoft 365

摘要

本文將討論 Microsoft Excel 如何儲存及計算浮點數。 因為四捨五入或截斷資料的緣故，這可能會影響某些數字或公式的結果。

概觀

Microsoft Excel 遵循 IEEE 754 規格來決定如何儲存及計算浮點數。 IEEE 是電氣電子工程師學會，這個國際組織的其中一項任務就是決定電腦軟體和硬體的標準。 規格 754 是非常廣泛採用的規格，說明如何在二進位電腦中儲存浮點數。 這很常見，因為它允許以合理的空間量儲存浮點數，並且計算速度相對較快。 幾乎目前所有具備浮點運算能力的 PC 類微處理器（包括 Intel、Motorola、Sun 及 MIPS 處理器）都在其浮點數和數值資料處理器使用 754 標準。

在儲存數字時，對應的二進位數可代表每個數字或分數。 例如，分數 1/10 在十進位數字系統可以表示為 0.1。 不過，二進位格式的相同數字會變成下列重複的二進制十進位數字：

0001100110011100110011 (等等)

這可無限重複。 這個數字無法以有限 (大小有限) 空間量來表示。 因此，在儲存這個數字時向下捨去約 -2.8 E-17。

不過，涵蓋三個一般類別的 IEEE 754 規範具有部分限制：

  • 最大/最小限制
  • 精確度
  • 重複的二進位數

其他相關資訊

最大/最小限制

所有電腦能夠處理的最大和最小數字都有限制。 因為儲存數字的記憶體位元有限，所以可儲存的最大或最小數字也有限。 對於 Excel 而言，可儲存的最大數字是 1.79769313486232E+308，而可儲存的最小正數是 2.2250738585072E-308。

我們在這些情況遵循 IEEE 754

  • 下溢：當產生的數字太小而無法表示時，會發生下溢。 在 IEEE 和 Excel 中，其結果為 0 (，但 IEEE 具有 -0 的概念，而 Excel 沒有)。
  • 上溢：當數字太大而無法表示時，就會發生上溢。 Excel 在這種情況下使用自己的特殊表示法 (#NUM!)。

我們在這些情況不遵循 IEEE 754

  • 非正規化數字：非正規化數字是以 0 的指數來表示。 在此情況下，會將整個數字儲存在小數部分，同時小數之前不以 1 開始。 因此，您會失去精確度，而且數值越小，遺失的精確度越多。 此範圍在數字小的那端只有一位數的精確度。

    範例：正規化數字以 1 開頭。 例如，如果小數以 0011001 表示，則正規化的數字會變成 10011001，因為要以 1 開頭。 非正規化的數字並未以 1 開頭，因此在我們的 0011001 範例中，非正規化的數字保持不變。 在此情況下，正規化的數字包含八位有效位數 (10011001) 而非正規化數字包含五位有效位數 (11001)，而前面的零並不重要。

    非正規化數字基本上是一種解決方法，可讓數字小於儲存的一般下限。 Microsoft 不會導入規範的選項部分，因為非正規化數字本質上即具有可變的位數。 這可能會在計算中引進大量錯誤。

  • 正/負無窮大：當您以 0 為除數時，便會產生無窮大的結果。 Excel 不支援無窮大，而會在這些情況下提供 #DIV/0! 錯誤。

  • 非數字 (NaN) ： NaN 是用來表示無效運算 (例如無窮大除無窮大、無窮大減無窮大或 -1 的平方根)。 NaN 允許程式繼續而忽略無效運算。 相反，Excel 會立即產生錯誤，例如 #NUM! 或 #DIV/0!。

精確度

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

符號 指數 尾數
1 個符號數位 11 數位指數 1 個默示的數位 + 52 數位分數

符號會儲存數字的正負號 (正或負) ，指數會儲存 2 的最大功率 (2 的最大/最低功率為 + 1,023 和 -1,022) )，而且尾數會儲存實際的數位。 尾數的有限儲存區域會限制兩個連續的浮點數的 (也就是精確度) 的鄰近程度。

尾數和指數都儲存為個別的元件。 因此，可能的精確度量會隨著 (尾數) 數目而有所不同。 在 Excel 中，雖然 Excel 可以將數位從 1.79769313486232E308 儲存至 2.2250738585072E-308，但只能在 15 位數的精確度內執行。 此限制是嚴格遵循 IEEE 754 規格的直接結果，而且不是 Excel 的限制。 在其他試算表程式中也能夠發現這種精確度程度。

浮點數是以下列形式表示，其中的指數是二進位指數：

X = 分數 * 2 ^ (指數-偏量)

分數是數位中正式化的分數部分，因為指數是調整過的，所以前導數位永遠為 1。 如此一來，您就不需要儲存它，而且有一位以上的精確度。 這就是默示的位數。 這類似于科學標記法，您可以在其中操縱指數，以在小數點左邊使用一個數位；除了二進位之外，您可以一直操作指數，第一個數位是 1，因為只有 1 和 0。

偏量是用來避免必須儲存負指數的偏量值。 單一精確度數值的偏量是 127 和 1,023 (十進位) 的雙精度數位。 Excel 會使用雙精度來儲存數位。

使用非常大數位的範例

在新活頁簿中輸入下列文字：

A1: 1.2E+200
B1: 1E+100
C1: =A1+B1

儲存格 C1 中產生的值會是 1.2E+200，與儲存格 A1 的值相同。 實際上，如果您使用 IF 函數比較儲存格 A1 和 C1，例如，IF(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 函數

下列範例會使用舊資料，使用 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：顯示的精確度

在某些情況下，您可能會使用 [顯示的精確度] 選項，避免捨入錯誤影響您的工作。 這個選項會強制工作表中每一個數位的值都是顯示的值。 若要開啟此選項，請依照下列步驟執行。

  1. 在 [檔案] 功能表上，按一下 [選項]，然後按一下 [進階] 類別。
  2. 在 [計算此活頁簿時] 區段中，選取您想要的活頁簿，然後選取 [以顯示值為準] 核取方塊。

例如，如果您選擇顯示兩位小數位數的數字格式，然後開啟 [以顯示值為準] 選項，當您儲存活頁簿時，兩位小數位數以外的所有精確度都會遺失。 此選項會影響使用中的活頁簿，包括所有工作表。 您無法復原此選項，也無法復原遺失的資料。 建議您在啟用此選項之前，先儲存活頁簿。

重複的二進位數字與接近零的計算結果

另一個令人困惑會影響以二進位格式儲存浮點數的問題，就是有些有限的非重複十進位 (10) 數字，使用二進位時為無限的重複數字。 最常見的範例是值 0.1 與其變體。 雖然這些數字在使用十進位 (10) 時可以完美表示，但相同的數字若使用二進位，儲存為尾數時卻會變成下列重複的二進位數字：

000110011001100110011 (等等)

IEEE 754 規格不會特別允許任何數字。 而是盡可能將數字儲存在尾數中，並截斷其餘部分。 因此會在儲存時，產生大約 -2.8 E-17 或 0.000000000000000028 的誤差。

甚至是常見的十進位小數，例如十進位的 0.0001，也無法以二進位精確表示。 (0.0001 是總共 104 位元的重複二進位小數)。 這和分數 1/3 無法以十進位精確表示的原因類似 (重複的 0.33333333333333333333)。

例如，請考量下列的簡單 Microsoft Visual Basic for Applications (VBA) 範例：

   Sub Main()
      MySum = 0
      For I% = 1 To 10000
         MySum = MySum + 0.0001
      Next I%
      Debug.Print MySum
   End Sub

這會將 0.999999999999996 列印為輸出。 以二進位表示 0.0001 的微小誤差會傳播到總和。

範例：新增負數

  1. 在新活頁簿中輸入下列文字：

    A1: =(43.1-43.2)+1

  2. 以滑鼠右鍵按一下儲存格 A1，然後按一下 [儲存格格式]。 在 [數值] 索引標籤上，按一下 [類別] 底下的 [科學記號]。 將 [小數位位數] 設定為 15。

Excel 會顯示 0.899999999999999，而不是顯示 0.9。 因為會先計算 (43.1-43.2)，所以會暫時儲存 -0.1，而將儲存 -0.1 的誤差引進計算中。

值達到零時的範例

  1. 在 Excel 95 或較舊版本中，於新的活頁簿中輸入下列文字：

    A1: =1.333+1.225-1.333-1.225

  2. 以滑鼠右鍵按一下儲存格 A1，然後按一下 [儲存格格式]。 在 [數值] 索引標籤上，按一下 [類別] 底下的 [科學記號]。 將 [小數位位數] 設定為 15。

Excel 95 會顯示 -2.22044604925031E-16，而不是顯示 0。

不過，Excel 97 引進最佳化計算，嘗試更正此問題。 當加法或減法運算導致值為零或非常接近零時，任何因運算元轉換為二進位或從二進位轉換成運算元時所引進的誤差，Excel 97 和更新版本都會加以補償。 上述範例在 Excel 97 和更新版本中執行時，可正確顯示 0 或 0.000000000000000E+00 (科學記號標記法)。

如需有關浮點數與 IEEE 754 規格的詳細資訊，請參閱下列全球資訊網站：