統計/數值方法/Excel 中的數值計算

本文件的目的是評估 MS Excel 在統計程式方面的準確性，並得出結論：MS Excel 是否應該用於（統計）科學目的。

根據文獻，如果將 Excel 用於統計計算，則有三個主要問題領域。它們是

• 機率分佈，
• 單變數統計、方差分析和估計（線性&非線性）
• 隨機數生成。

如果評估統計包的結果，則應考慮到結果的可接受精度應在雙精度內實現（這意味著如果結果具有 15 位有效數字，則該結果被認為是準確的），前提是可靠的演算法也能在雙精度內提供正確的結果。如果可靠的演算法無法在雙精度內檢索結果，那麼預期該包（評估）應該達到雙精度是不公平的。因此我們可以說，評估統計包的正確方法是評估統計計算底層演算法的質量，而不是隻計算結果的有效數字。此外，測試問題必須是合理的，這意味著它們必須適合用已知的可靠演算法解決。（McCullough & Wilson, 1999, S. 28）

在後面的部分中，我們對 MS Excel 準確性的判斷將基於認證值和測試。作為基礎，我們有 Knüsel 的 ELV 軟體用於機率分佈，StRD（統計參考資料集）用於單變數統計、方差分析和估計，最後是 Marsaglia 的 DIEHARD 用於隨機數生成。每個測試和認證值將在相應部分進行解釋。

評估 Excel 統計分佈結果

正如我們上面提到的，我們對 Excel 機率分佈計算的判斷將基於 Knüsel 的 ELV 程式，該程式可以計算一些基本統計分佈的機率和分位數。使用 ELV，所有分佈的上下尾部機率以六位有效數字計算，機率小至 10−100，所有分佈的上下分位數計算，尾部機率 P 為 10−12 ≤ P ≤ ½。（Knüsel, 2003, S.1）

在我們的基準測試中，Excel 不應該顯示不準確的數字。如果顯示六位數字，則所有六位數字都應該是正確的。如果演算法僅精確到兩位數字，則僅應顯示兩位數字，以免誤導使用者（McCullough & Wilson, 2005, S. 1245）

在以下小節中，表格中的精確值取自 Knüsel 的 ELV 軟體，可接受的精度為單精度，因為即使在大多數情況下，最好的演算法也無法在發出機率分佈時達到 15 位正確數字。

正態分佈

• Excel 函式：NORMDIST
• 引數： 均值 = 0，方差 = 1，x（臨界值）
• 計算： 尾部機率 Pr X ≤ x，其中 X 表示具有標準正態分佈（均值 0，方差 1）的隨機變數

表 1：（Knüsel, 1998, S.376）

正如我們在表 1 中看到的，Excel 97、2000 和 XP 遇到了問題，並錯誤地計算了尾部的較小機率（即對於 x = -8,3 或 x = -8.2）。但是，這個問題在 Excel 2003 中得到了解決（Knüsel, 2005, S.446）。

逆正態分佈

• Excel 函式： NORMINV
• 引數： 均值 = 0，方差 = 1，p（X < x 的機率）
• 計算： x 值（分位數）

X 表示具有標準正態分佈的隨機變數。與上一節中發出的“NORMDIST”函式相反，給出了 p 並計算分位數。

如果使用，Excel 97 會打印出具有 10 位數字的分位數，儘管如果 p 很小，則這 10 位數字中的任何一個都可能不正確。在 Excel 2000 和 XP 中，Microsoft 試圖修復錯誤，儘管結果並不充分（見表 2）。但是，在 Excel 2003 中，問題完全得到了解決。（Knüsel, 2005, S.446）

表 2：（Knüsel, 2002, S.110）

逆卡方分佈

• Excel 函式： CHIINV
• 引數： p（X > x 的機率），n（自由度）
• 計算： x 值（分位數）

X 表示具有 n 個自由度的卡方分佈的隨機變數。

表 3：（Knüsel , 1998, S. 376）

舊版 Excel 版本：雖然舊版 Excel 版本顯示十位有效數字，但如果 p 很小，則只有極少數位數字是準確的（見表 3）。即使 p 不小，準確的數字也不足以說明 Excel 對於這種分佈是足夠的。

Excel 2003：問題已解決。（Knüsel, 2005, S.446）

逆 F 分佈

• Excel 函式： FINV
• 引數： p（X > x 的機率），n1，n2（自由度）
• 計算： x 值（分位數）

X 表示具有 n1 和 n2 個自由度的 F 分佈的隨機變數。

表 4：（Knüsel , 1998, S. 377）

舊版 Excel 版本：Excel 打印出具有 7 位或更多有效數字的 x 值，儘管如果 p 很小，則只有其中的一兩位數字是正確的（見表 4）。

Excel 2003：問題已解決。（Knüsel, 2005, S.446）

逆 t 分佈

• Excel 函式： TINV
• 引數： p（|X| > x 的機率），n（自由度）
• 計算： x 值（分位數）

X 表示具有 n 個自由度的 t 分佈的隨機變數。請注意，|X| 值會導致 2 尾計算。（下尾&上尾）

表 5：（Knüsel , 1998, S. 377）

舊版 Excel： 儘管只有 1 或 2 位有效數字是正確的，但 Excel 會打印出具有 9 位或更多有效數字的分位數，如果 p 很小（參見表 5）。

Excel 2003：問題已解決。（Knüsel, 2005, S.446）

泊松分佈

• Excel 函式： POISSON
• 引數： λ（均值），k（案例數）
• 計算： 尾部機率 Pr X ≤ k

X 表示具有給定引數的泊松分佈的隨機變數。

表 6：（McCullough & Wilson，2005，S.1246）

舊版 Excel： 正確計算了非常小的機率，但沒有給出接近均值的中心機率（大約 0.5 的範圍內）的結果。（參見表 6）

Excel 2003： 中心機率已修復。但是，尾部結果不準確。（參見表 6）

對於 λ150 的值，可能會遇到 Excel 的奇怪行為。（Knüsel，1998，S.375）即使對於 0.01 到 0.99 之間的中心範圍內的機率，以及對於不能判斷為過於極端的引數值，它也會失敗。

二項分佈

• Excel 函式： BINOMDIST
• 引數： n（= 試驗次數），υ（= 成功機率），k（成功次數）
• 計算： 尾部機率 Pr X ≤ k

-X 表示具有給定引數的二項式分佈的隨機變數

表 7：（Knüsel，1998，S.375）

舊版 Excel： 正如我們在表 7 中看到的那樣，舊版本的 Excel 正確計算了非常小的機率，但沒有給出接近均值的中心機率的結果（舊版 Excel 上的泊松分佈出現相同的問題）

Excel 2003： 中心機率已修復。但是，尾部結果不準確。（Knüsel，2005，S.446）。（Excel 2003 上的泊松分佈存在相同的問題）。

對於 n > 1000 的值，可能會遇到 Excel 的這種奇怪行為。（Knüsel，1998，S.375）即使對於 0.01 到 0.99 之間的中心範圍內的機率，以及對於不能判斷為過於極端的引數值，它也會失敗。

其他問題

• Excel 97、2000 和 XP 在計算超幾何分佈 (HYPERGEOM) 時包含缺陷。對於某些值（N > 1030），檢索不到結果。在 Excel 2003 中，這種情況得到了阻止，但仍然沒有計算尾部機率的選項。因此，可以計算 Pr {X = k}，但無法計算 Pr {X ≤ k}。（Knüsel，2005，S.447）
• 用於伽馬分佈的函式 GAMMADIST 在 Excel 2003 中檢索到不正確的值。（Knüsel，2005，S.447-448）
• 同樣，用於逆貝塔分佈的函式 BETAINV 在 Excel 2003 中也計算了不正確的值（Knüsel，2005，S. 448）

評估 Excel 結果以進行單變數統計、方差分析和估計（線性與非線性）

我們對 Excel 在單變數統計、方差分析和估計方面的計算的判斷將基於 StRD，StRD 由美國國家標準與技術研究院 (NIST) 的統計工程部門設計，旨在幫助研究人員顯式地對統計軟體包進行基準測試。StRD 具有參考資料集（真實世界和生成的資料集），並具有經過認證的計算結果，這些結果使客觀評估統計軟體成為可能。它包含四個針對統計軟體的數值基準測試套件：單變數彙總統計、單因素方差分析、線性迴歸和非線性迴歸，並且每個測試套件包含多個問題。所有問題都有難度級別：低、平均或高。

在本節中，透過評估 Excel 結果，我們將使用 LRE（對數相對誤差），它可以用作統計軟體包結果準確性的得分。可以透過對數相對誤差來計算結果中的正確數字數量。請注意，對於雙精度，計算出的 LRE 介於 0 到 15 之間，因為在雙精度中最多可以有 15 個正確數字。

公式 LRE

$\lambda =LRE(x)=-log_{10}\left({\frac {|x-c|}{|x|}}\right)$

c：特定測試問題的正確答案（經過認證的計算結果）

x：Excel 對同一問題的答案

單變數統計

• Excel 函式： - AVERAGE、STDEV、PEARSON（也稱為 CORREL）

• 計算（分別）： 均值、標準差、相關係數

表 8：（McCullough & Wilson，2005，S.1247）

舊版 Excel： 使用了用於計算樣本方差和相關係數的不穩定演算法。即使對於低難度問題（表 8 中帶有字母“l”的資料集），舊版本的 Excel 也會失敗。

Excel 2003： 問題已修復，效能可以接受。小於 15 的準確數字並不表示實現不成功，因為即使是可靠的演算法也無法為這些平均和高難度問題（表 8 中帶有字母“a”和“h”的資料集）從 StRD 中檢索到 15 個正確數字。

單因素方差分析

• Excel 函式： 工具 - 資料分析 - 方差分析：單因素（需要分析工具包）
• 計算： df、ss、ms、F 統計量

由於方差分析會生成許多數值結果（例如 df、ss、ms、F），因此這裡只介紹了最終 F 統計量的 LRE。在評估 Excel 的效能之前，應該考慮，用於單因素方差分析的可靠演算法可以為平均難度問題提供 8 到 10 位數字，為更難的問題提供 4 到 5 位數字。

表 9：（McCullough & Wilson，2005，S.1248）

舊版 Excel： 考慮到數值解，對於困難問題只提供幾位有效數字的準確性並不表示軟體不好，但是，對於平均難度問題檢索到 0 位有效數字，則表示在計算方差分析時軟體不好。（McCullough & Wilson，1999，S. 31）。因此，早於 Excel 2003 的 Excel 版本的效能只有在低難度問題上才能接受。它為困難問題檢索到零位有效數字。此外，針對“組內平方和”和“組間平方和”的負結果是 Excel 使用的糟糕演算法的進一步指示。（參見表 9）

Excel 2003： 問題已修復（參見表 9）。Simon 9 測試的零位有效數字並非令人擔憂的原因，因為當使用可靠演算法時，也會發生這種情況。因此，效能可以接受。（McCullough & Wilson，2005，S. 1248）

線性迴歸

• Excel 函式： LINEST
• 計算： 線性迴歸所需的所有數值結果

由於 LINEST 會生成許多用於線性迴歸的數值結果，因此只考慮係數的 LRE 和係數標準誤差。表 9 顯示了每個資料集的最低 LRE 值，作為鏈條中最薄弱的環節，以反映最差的估計（最小 $\lambda _{\beta }$-LRE 和 $\lambda _{\sigma }$-LRE）由 Excel 為每個線性迴歸函式生成的。

舊版 Excel： 既沒有檢查輸入矩陣的近奇異性，也沒有正確地檢查它，因此對於病態的資料集“Filip (h)”，結果中沒有一個數字是正確的。實際上，Excel 應該拒絕該解決方案，並向用戶發出有關資料矩陣近奇異性的警告。（McCullough & Wilson, 1999, S.32,33）。然而，在這種情況下，使用者會被誤導。

Excel 2003： 問題已解決，Excel 2003 的效能可以接受。（參見表 10）

表 10： (McCullough & Wilson, 1999, S. 32)

非線性迴歸

使用 Excel 求解非線性迴歸時，可以選擇以下內容：

1. 導數計算方法：向前（預設）或中心數值導數
2. 收斂容差（預設值 = 1.E-3）
3. 變數縮放（重新居中）
4. 求解方法（預設 - GRG2 擬牛頓法）

Excel 的預設引數並不總是產生最佳的解決方案（與所有其他求解器一樣）。因此，需要給出不同的引數並測試 Excel 求解器以進行非線性迴歸。在表 10 中，列 A-B-C-D 是不同非線性選項的組合。由於更改第 1 個和第 4 個選項不會影響結果，因此只更改了第 2 個和第 3 個引數進行測試。

• A：預設估計
• B：收斂容差 = 1E -7
• C：自動縮放
• D：收斂容差 = 1E -7 和自動縮放

在表 11 中，應用了最低 LRE 原則來簡化評估。（與線性迴歸一樣）

表 11 中的結果對於每個 Excel 版本（Excel 97、2000、XP、2003）都是相同的。

表 11： (McCullough & Wilson, 1999, S. 34)

正如我們在表 11 中看到的，非線性選項組合 A 生成了 21 次“0”準確數字，B 生成了 17 次，C 生成了 20 次，D 生成了 14 次，這表明 Excel 在這一領域的表現不足。期望 Excel 為所有問題找到所有精確的解決方案是不公平的，但如果它無法找到結果，則希望它警告使用者並承認無法計算出該解決方案。此外，應該強調，其他統計軟體包（如 SPSS、S-PLUS 和 SAS）在這些測試中只在少數情況下（0 到 3 次）表現出零位精度（McCullough & Wilson, 1999, S. 34）。

評估 Excel 的隨機數生成器

許多統計程式使用隨機數，並且期望生成的隨機數確實是隨機的。只有具有可靠的理論屬性的隨機數生成器才能使用。此外，應該對生成的樣本進行統計檢驗，並且只有輸出成功透過一組統計檢驗的生成器才能使用。（Gentle, 2003）

根據上面解釋的事實，我們應該透過以下方式評估隨機數生成的質量：

• 分析隨機數生成的底層演算法。
• 分析生成器的輸出流。有很多方法可以測試 RNG 的輸出。可以使用靜態測試來評估生成的輸出，在這些測試中，生成順序並不重要。這些測試是擬合優度檢驗。評估輸出流的第二種方法是對生成器執行動態測試，其中數字的生成順序很重要。

Excel 的 RNG - 底層演算法

隨機數生成的目的是生成任何給定大小的樣本，這些樣本與從 U(0,1) 分佈中獲得的相同大小的樣本不可區分。（Gentle, 2003）為此目的，可以使用不同的演算法。Excel 的隨機數生成演算法是 Wichmann-Hill 演算法。Wichmann-Hill 是一個適用於常見應用的實用 RNG 演算法，但對於現代需求而言已經過時（McCullough & Wilson, 2005, S. 1250）。此隨機數生成器的公式定義如下

$X_{i}=171.X_{i}-1mod30269$

$Y_{i}=172.Y_{i}-1mod30307$

$Z_{i}=170.Z_{i}-1mod30323$

$U_{i}={\frac {X_{i}}{30269}}+{\frac {Y_{i}}{30307}}+{\frac {Z_{i}}{30323}}mod1$

Wichmann-Hill 是一個同餘生成器，這意味著它是如上式所示的遞迴算術 RNG。它是三個其他線性同餘生成器的組合，需要三個種子：$X_{0}Y_{0}Z_{0}$.

週期，在隨機數生成的意義上，是指在 RNG 開始重複之前可以對 RNG 進行的呼叫次數。因此，週期長是隨機數生成器的質量指標。生成器的週期必須大於要使用的隨機數的數量。現代應用越來越多地需要更長更長的隨機數序列（例如在蒙特卡羅模擬中使用）（Gentle, 2003）

一個好的隨機數生成器 (RNG) 可接受的最小週期是 $2^{60}$，而 Wichmann-Hill RNG 的週期為 6.95E+12 (≈ $2^{43}$)。除了這種不可接受的效能，微軟聲稱 Wichmann-Hill RNG 的週期為 10E+13。即使 Excel 的 RNG 週期為 10E+13，它仍然不是一個可接受的隨機數生成器，因為這個值也小於 $2^{60}$。(McCullough & Wilson, 2005, S. 1250)

此外，眾所周知，Excel 的 RNG 在多次執行後會產生負值。然而，Wichmann-Hill 隨機數生成器的正確實現應該只產生 0 到 1 之間的數值。(McCullough & Wilson, 2005, S. 1249)

Excel 的 RNG - 輸出流

正如我們在上面討論的那樣，僅僅討論隨機數生成器的底層演算法是不夠的。在評估隨機數生成器的質量時，還需要對隨機數生成器的輸出流進行一些測試。因此，隨機數生成器應該產生透過一些隨機性測試的輸出。Marsaglia 準備了一套這樣的測試，稱為 DIEHARD。一個好的 RNG 應該通過幾乎所有的測試，但正如我們在表 12 中看到的，Excel 只通過了其中的 11 個 (7 個失敗)，儘管微軟聲稱 Excel 的 RNG 實施了 Wichmann-Hill 演算法。然而，我們知道 Wichmann-Hill 能夠透過 DIEHARD 的 16 個測試 (McCullough & Wilson, 1999, S. 35)。

由於在上一節和本節中解釋的原因，我們可以說 Excel 的效能不足 (因為週期長度、Wichmann Hill 演算法的錯誤實現，該演算法已經過時，DIEHARD 測試結果)

表 12: (McCullough & Wilson, 1999, S. 35)

結論

舊版本的 Excel (Excel 97, 2000, XP) 

• 在以下分佈上表現不佳: 正態分佈、F 分佈、t 分佈、卡方分佈、二項分佈、泊松分佈、超幾何分佈
• 在以下計算中得到不準確的結果: 單變數統計、方差分析、線性迴歸、非線性迴歸
• 具有不可接受的隨機數生成器

由於這些原因，我們可以說應該避免將 Excel 97、2000、XP 用於 (統計) 科學目的。

儘管 Excel 2003 中修復了幾個錯誤，但仍然應該避免將 Excel 用於 (統計) 科學目的，因為它

• 在以下分佈上表現不佳: 二項分佈、泊松分佈、伽馬分佈、貝塔分佈
• 在非線性迴歸方面得到不準確的結果
• 具有過時的隨機數生成器。

參考文獻

• Gentle J.E. (2003) 隨機數生成和蒙特卡羅方法 第二版。紐約 Springer Verlag
• Knüsel, L. (2003) 統計分佈的計算 ELV 程式文件 第二版。 http://www.stat.uni-muenchen.de/~knuesel/elv/elv_docu.pdf 檢索於 [2005 年 11 月 13 日]
• Knüsel, L. (1998)。關於 Microsoft Excel 97 中統計分佈的準確性。計算統計與資料分析 (CSDA)，第 26 卷，第 375-377 頁。
• Knüsel, L. (2002)。關於 Microsoft Excel XP 用於統計目的的可靠性。計算統計與資料分析 (CSDA)，第 39 卷，第 109-110 頁。
• Knüsel, L. (2005)。關於 Microsoft Excel 2003 中統計分佈的準確性。計算統計與資料分析 (CSDA)，第 48 卷，第 445-449 頁。
• McCullough, B.D. & Wilson B. (2005)。關於 Microsoft Excel 2003 中統計過程的準確性。計算統計與資料分析 (CSDA)，第 49 卷，第 1244-1252 頁。
• McCullough, B.D. & Wilson B. (1999)。關於 Microsoft Excel 97 中統計過程的準確性。計算統計與資料分析 (CSDA)，第 31 卷，第 27-37 頁。
• PC Magazin，2004 年 4 月 6 日，第 71 頁*