文章編號: 78113 - 上次校閱: 2010年5月13日 - 版次: 7.0

浮點算術可能會在 Excel 中造成不正確的計算結果

檢視此文章適用的產品。
系統提示本文適用於您使用的作業系統之外的作業系統。與您不相關的文章內容已停用。

在此頁中

全部展開 | 全部摺疊

結論

本文將告訴您 Microsoft Excel 如何儲存和計算浮點數。由於四捨五入和/或資料截斷的緣故，這可能會影響某些數字或公式的計算結果。
回此頁最上方

概觀

Microsoft Excel 的設計是以有關儲存和計算浮點數的 IEEE 754 規格為基礎。IEEE 是電子電機工程師協會 (Institute of Electrical and Electronics Engineers) 的縮寫，這個國際組織負責決定電腦軟體和硬體的標準。754 規格是相當廣受採用的規格，它說明浮點數應該如何儲存在二進位電腦中。這個規格之所以如此普及，是因為它允許將浮點數儲存在合理的空間中，並讓計算可以相當快速地執行。754 標準已運用在現今幾乎所有 PC 微處理器的浮點單位及數值資料處理器，PC 微處理器是執行浮點算術的處理器，包括 Intel、Motorola、Sun 和 MIPS 處理器。

儲存數字時，對應的二進位數字可以代表任何數字或分數。例如，在小數系統中，分數 1/10 可以用 0.1 來表示。不過，二進位格式中的相同數字會變成重複的二進位和十進位
0001100110011100110011 (以此類推)
並且可以無限地重複。這個數字不能以有限的 (受限的) 空間量來表示。因此，在儲存時，這個數字會四捨五入為 -2.8E-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，正規化的數字就會因為隱含的前置 1 而變成 10011001。取消正規化的數字並沒有隱含的前置 1，所以就範例 0011001 而言，正規化的數字仍然相同。在此情況下，正規化的數字有 8 個有效位數 (10011001)，而取消正規化的數字只有 5 個有效位數 (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^(指數 - bias)
分數是數字的正規化分數部分，之所以要正規化，是因為指數已調整為讓前置位元永遠是 1。這樣一來，不必儲存就能得到更多位元的精確度。這也就是隱含位元存在的原因。這類似於操作指數，讓小數點左邊有一個位元的科學標記；只不過在二進位中，您永遠可以操作指數，讓第一個字元永遠是 1，因為其中只有 1 和 0。

Bias 是用來避免必須儲存負指數的 bias 值。單精度數字的 bias 是 127，雙精度數字的則為 1,023 (十進位)。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 位數的精確度。
回此頁最上方

使用非常小的數字的範例

請在新的活頁簿中輸入下列內容：
答 1：0.000123456789012345
B1: 1
C1:=A1+B1
儲存格 C1 中的結果值會是 1.00012345678901，而非 1.000123456789012345。這是受到 IEEE 規格的影響，只能儲存 15 個有效位數的精確度。如果要儲存上述計算，Excel 需要至少 19 位數的精確度。
回此頁最上方

修正精確度錯誤

Excel 提供兩個可以彌補四捨五入錯誤的基本方法：ROUND 函數和 [以顯示值為準] (Precision as displayed) 或 [以顯示值為準] (Set precision as displayed) 活頁簿選項。

方法 1：ROUND 函數

下列範例使用上述資料，並使用 ROUND 函數將數字強制為 5 位數。這可以讓您成功地將結果與其他值比較。
答 1：1.2E+200
B1:1E+100
C1:=ROUND(A1+B1,5)
結果是 1.00012。 
D1:=IF(C1=1.00012, TRUE, FALSE)
結果值為 TRUE。

方法 2：以顯示值為準

在某些情況下，您可以使用 [以顯示值為準] 選項避免四捨五入的錯誤影響工作成果。這個選項會強制工作表中各數字的值成為顯示值。如果要啟用這個選項，請依照下列步驟執行：
  1. 在 Excel 2003 (含) 以前版本中，按一下 [工具] 功能表上的 [選項]。
  2. 在 [計算] 索引標籤上，按一下以選取 [以顯示值為準] 核取方塊。
  1. 在 Excel 2007 中，按一下 Microsoft Office 按鈕，按一下 [Excel 選項]，然後按一下 [進階] 類別。
  2. 在 [計算此活頁簿時] 區段中，選取您要的活頁簿，然後選取 [以顯示值為準] 核取方塊。
例如，如果您選擇顯示兩個小數位數的數字格式，然後返回 [以顯示值為準] 選項，當您儲存活頁簿時就會損失超過這兩個小數點之外的精確度。這個選項會影響包括所有工作表在內的作用中活頁簿。您無法還原這個選項及修復損失資料。建議您在啟用這個選項前儲存活頁簿。
回此頁最上方

重複結果近似零的二進位數字和計算

以二進位格式儲存浮點數還有另一個困擾的問題，那就是十進位底數中有限的、不重複的數字，會在二進位中變成無限且重複的數字。最常見的範例就是值 0.1 和其中的變數。雖然這些數字可以在底數 10 中完美表示，但是在二進位格式中的相同數字，則會在儲存於假數中時變成重複的二進位數字：
000110011001100110011 (以此類推)
IEEE 754 規格不會對任何數字提供特別的規定；它會在假數中儲存可以儲存的內容，再截斷其餘部分。這會造成在儲存時出現有關 -2.8E-17 或 0.000000000000000028 的錯誤。

即使是一般的小數 (例如小數點 0.0001) 亦無法以二進位格式準確表示 (0.0001 是重複的二進位分數，其中間隔 104 位元)。這就像是分數 1/3 無法以小數點 (重複的 0.33333333333333333333) 準確表示的原因一樣。

這會說明為何 Microsoft Visual Basic for Applications 中的簡單範例
   Sub Main()
      MySum = 0
      For I% = 1 To 10000
         MySum = MySum + 0.0001
      Next I%
      Debug.Print MySum
   End Sub
輸出為 PRINT 0.999999999999996。二進位格式中重複的 0.0001 小錯誤會影響到加總。
回此頁最上方

新增負數的範例

  1. 請在新的活頁簿中輸入下列內容：
    A1:=(43.1-43.2)+1
  2. 用滑鼠右鍵按一下儲存格 A1，然後按一下 [設定儲存格格式]。在 [數值] 索引標籤上，按一下 [類別] 下的 [科學記號]。將 [小數位數] 設為 15。
Excel 顯示的不是 0.9，而是 0.899999999999999。因為會先計算 (43.1-43.2)，所以會暫時儲存 -0.1，而且來自儲存 -0.1 的錯誤也會加入計算。
回此頁最上方

當值接近零時的範例

  1. 在 Excel 95 或更早版本的新活頁簿中輸入下列內容：
    答 1：=1.333+1.225-1.333-1.225
  2. 用滑鼠右鍵按一下儲存格 A1，然後按一下 [設定儲存格格式]。在 [數值] 索引標籤上，按一下 [類別] 下的 [科學記號]。將 [小數位數] 設為 15。
Excel 95 不會顯示 0，而是顯示 -2.22044604925031E-16。

至於 Excel 97，則是加入了嘗試修正此問題的最佳化做法。如果進行了加法或減法作業而得出零或非常近似零的值，Excel 97 和更新版本會補償因為在二進位格式間轉換運算元所造成的錯誤。上述範例是在 Excel 97 中執行，而且之後會在科學標記中正確地顯示 0 或 0.000000000000000E+00。 如需詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
172911? (http://support.microsoft.com/kb/172911/ ) Incorrect Result Raising 10 to Very Large/Very Small Power
214373? (http://support.microsoft.com/kb/214373/ ) XL2000: Incorrect Result Raising 10 to Very Large/Very Small Power
如需有關浮點數和 IEEE 754 規格的詳細資訊，請造訪下列全球資訊網網站：
http://www.ieee.org (http://www.ieee.org)

http://stevehollasch.com/cgindex/coding/ieeefloat.html (http://stevehollasch.com/cgindex/coding/ieeefloat.html)
回此頁最上方

?考

如需有關如何解決這些錯誤的詳細資訊，按一下下列的文件編號，檢視「Microsoft 知識庫」中的文件：
214118? (http://support.microsoft.com/kb/214118/ ) How to correct rounding errors in floating-point arithmetic
回此頁最上方
這篇文章中的資訊適用於:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X for Mac
  • Microsoft Excel 2001 for Macintosh
  • Microsoft Excel 98 for Macintosh
回此頁最上方
關鍵字：?
kbinfo KB78113
回此頁最上方
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應，不負任何擔保責任。Microsoft及(或)其供應商謹此聲明，不負任何對與此資訊有關之擔保責任，包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。