文章編號: 828888 - 上次校閱: 2006年2月16日 - 版次: 3.1

Excel 2003 和 Excel 2004 for Mac 中統計函數改良的說明

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

在此頁中

全部展開 | 全部摺疊

結論

本文將告訴您，Microsoft Office Excel 2003 中六種類型的統計函數改良，為您 (讀者) 提供內容更詳細的文章，並且評估在 Excel 2003 的函數經過改良後，使用舊版 Microsoft Excel 所造成的影響。

回此頁最上方

Microsoft Excel 2004 for Mac 資訊

Excel 2004 for Mac 中的統計函數和 Microsoft Office Excel 2003 中的統計函數都是使用相同的演算法來更新的。本文中對於函數如何運作的說明，或是 Excel 2003 中修改函數的所有資訊，均適用於 Excel 2004 for Mac。
回此頁最上方

其他相關資訊

在 Excel 2003 中，已對下列六種類型進行改良：
  1. LINEST 以及相關函數。
  2. NORMSDIST 以及相關函數。
  3. 包含平方和的函數 (例如 VAR 和 STDEV)。
  4. 連續型分配反函數 (例如 CHIINV、FINV、NORMSINV 和 TINV)。
  5. 離散型分配的函數 (例如 BINOMDIST 和 POISSON)。
  6. 亂數產生器 (RAND)。
這六種類型會有專門的章節介紹。在第三、第四和第五類中，有幾個函數的改良是屬於相同的主題。在第一到第四類中，某個函數的改良對於基本上會呼叫該函數的其他函數的效能有正面的影響。

由於改良是針對 Excel 2003 進行的，因此您一定會想要瞭解舊版 Excel 中統計函數錯誤的風險 (及範圍)。這些問題將會在六個章節中分別簡述。這六種類型的順序是依照作者所認定的重要性來排列的。雖然大部分的使用者都不需要擔心 Microsoft Excel 2002 和舊版的效能，但是 LINEST (第一類) 的問題比 BINOMDIST (第五類) 或 RAND (第六類) 的問題更有可能出現。

在 Excel 2002 和舊版中，已確知有一個缺點是在 Excel 2003 中沒有修正的。適用於 t-Test 的 Analysis ToolPak 的工具的原型應用程式：Paired Two Sample for Means 的資料包括對於同一組主體在處理前和處理後的測量 (例如，節食計畫之前和之後的體重)。如果觀測前和觀測後的遺失數目不相等，這項工具便不會進行計算；如果觀測值有遺失，而且觀測前和觀測後的遺失數目相同，它所計算的答案便會不正確。除非您可以保證觀測值沒有遺失，否則最好是使用 Excel 中的 TTEST 函數 (因為它可以正確處理遺失的觀測值)。

如需詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
829252? (http://support.microsoft.com/kb/829252/ ) You may obtain incorrect results and misleading labels when you use the Analysis ToolPak t-Test in Excel 2003
有兩份主要的參考資料指出了 Microsoft Excel 97 中的數值錯誤，分別是：有關第二、四和五類，由 Knusel (請見注意 1) 完成的研究報告；以及有關第一、三和六類，由 McCullough 和 Wilson 完成的研究報告。對於 Microsoft Excel 2000 和 Microsoft Excel 2002 中的 Excel 統計函數所做的改良幅度甚低。
回此頁最上方

LINEST 以及相關函數

LINEST 程式碼做了兩大改良。第一項改良：在將 LINEST 的第三個引數設定為 FALSE，表示您不希望 LINEST 模式化截距 (或常數) 的案例中，取代了求總平方和的不適當公式。實際上，在少數情況中會選取這個選項。當呼叫 LINEST(known_y's, known_x's, FALSE, TRUE) 以傳回含有五列詳細資訊的陣列時 (f-statistic、R-squared、迴歸及殘差或誤差平方和)，舊版 Excel 中的不適當公式會造成輸出陣列最後三列的結果錯誤。迴歸係數和其標準誤差不會受影響。這項不正確的公式會造成負的 R-squared 和負的迴歸平方和。這個問題已經加以修正。LINEST 的相關文件針對 Excel 2002 和更舊的版本提供了建議解決方案，以便在輸出陣列的最後三列中產生正確的輸出值。這是一個相當嚴重的錯誤，它會造成在呼叫第三個引數設定為 FALSE 的 LINEST 時，最後三列永遠是錯誤的。

如需詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
828533? (http://support.microsoft.com/kb/828533/ ) Description of the LINEST function in Excel 2003 and in Excel 2004 for Mac

注意

  • Knusel, L. On the Accuracy of Statistical Distributions in Microsoft Excel 97, Computational Statistics and Data Analysis, 26, 375-377, 1998.
  • McCullough, B.D. & B. Wilson, On the accuracy of statistical procedures in Microsoft Excel 97, Computational Statistics and Data Analysis, 31, 27-37, 1999.
McCullough 和 Wilson (McC 和 W) 正確指出 Excel 97 沒有注意到共線 (或幾乎共線) 預測因子欄的問題。如果可以捨棄一組預測因子欄中的某一個，而不會影響迴歸模型的適合度，則該組預測因子欄便是共線的。也就是說，被捨棄欄中所包含的資訊並不會增加值，因為可以從剩餘欄來重新建構被捨棄的欄。McC 和 W 中的測試範例包括共線性和近共線性。使用未考慮共線性問題的解決方法時，如果具有共線性便會造成結果不正確。這類結果包括迴歸係數中精確度的有效位數減少以及自由度的數目不適當。在 Excel 2003 中，解決方法改成使用 QR 分解。結果是大幅改善了 McC 和 W 所使用的超難測試案例中的精確度。LINEST 相關文件中會討論共線性；也會提供一個逐步說明 QR 分解演算法的小例子。在所有早於 Excel 2003 的 Excel 版本中，這個缺點只有在有共線預測因子時才有重大的影響。保證不會發生共線性的一個常見情況為，只有單一預測因子欄且該欄中並非所有值均相同時。

改良 LINEST 對於呼叫它的函數有正面的影響：除了 Analysis ToolPak 中的「線性迴歸」工具，還有 LOGEST、TREND 和 GROWTH。Excel 中的圖表工具可讓您使用線性迴歸工具，將直線套用至資料點圖表中。可惜的是，Excel 2003 中尚未升級圖表工具中的線性迴歸功能。

總之，如果您使用比 Excel 2003 更早的 Excel 版本，在呼叫第三個引數設定為 FALSE 的 LINEST 時，就必須拒絕 LINEST 中五列輸出表格的最後三列中的詳細 LINEST 結果。同時亦請注意共線性的可能性；在大部分的資料集中，共線性應該不是問題。
回此頁最上方

NORMSDIST 以及相關函數

NORMSDIST(z) 必須使用近似解法來評估。舊版的 Excel 對 z 的所有值均使用相同的程序。在 Excel 2003 中，使用了兩個不同的近似值：一個用於 |z| 小於等於 5 時，而另一個則用於 |z| 大於 5 時。這兩個新的程序對於所套用的範圍比舊程序更為精確。在舊版的 Excel 中，如同 Knusel 的文件中所提出的，在產生 z = 4 的三個有效位數的分配尾端的精確度會下降。同時，在 z = 1.2 的鄰域中，NORMSDIST 只會產生六個有效位數。然而實際上，這對於大部分的使用者可能已經足夠了。

Excel 2003 程式碼會對任何一個 Z 產生至少十五位小數的精確度。這表示 z = 1.2 會有十五個有效位數 (因前端的 0) 而 z = 4 會有十個有效位數。改良 NORMSDIST 的精確度對於下列依存函數的改良也會有正面的影響：CONFIDENCE、LOGINV、LOGNORMDIST、NORMDIST、NORMINV、NORMSINV 和 ZTEST。CONFIDENCE、LOGNORMDIST、NORMDIST 和 ZTEST 的程式碼尚未修訂；上述各函數的精確度獲得改良是因為它們基本上都是呼叫一次或多次的 NORMSDIST，並且在計算中使用這些呼叫的結果。NORMSDIST 的精確度改良也有利於 LOGINV、NORMINV 和 NORMSINV。由於這些都是連續型分配反函數 (請參閱下面的第四類)，因此與此類函數有關的搜尋改善亦對其有利。與其他反函數有關的第四類的搜尋改善是針對 Excel 2002 中的這三個反函數進行的。

總之，如果您是使用 Excel 2002 及更早的版本，應該對 NORMSDIST 感到滿意了。然而，如果您對於 z 必須具有遠超過 0、更精確的 NORMSDIST(z) 值 (例如 |z| 大於等於 4 時)，可能就需要使用 Excel 2003。NORMSDIST(-4) = 0.0000316712；舊版的精確度只到 0.0000317。您可以預期「大約 0.00003」或「大約 100,000 分之 3」對許多使用者而言，很可能已經是夠精確的答案了，而舊版本會再多兩位小數。
回此頁最上方

包含平方和的函數

McCullough 和 Wilson 指出，Excel 似乎是使用「計算機公式」來計算 VAR。他們的觀察是正確的，而且可以延伸到許多 (但奇怪的是並非全部) 計算有關平均數的平方差和的函數。計算機公式可以一次運算所有的資料。這種計算速度或許是舊版 Excel 使用它的主要原因。用於 Excel 2003 的另一種公式則需要運算兩次。對於 VAR，計算機公式會計算觀測值的數目、所有觀測值的平方和以及觀測值的總和。利用這些資料，它便可以計算：
觀測值的平方和 – ((觀測值的總和)^2) / 觀測值的數目
另一種程序是在第一次運算時，計算觀測值的數目和觀測值總和。利用這個資料，它便可以計算觀測值總和除以觀測值數目的平均數。在第二次運算時，此程序會計算：
個別觀測值與平均數之間的平方差和
使用上述其中一種計算方法，VAR 的計算是將結果除以觀測值數目 – 1；VARP 的計算則是將結果除以觀測值數目。

透過極為精確的計算，這兩種程序會產生相同的結果。然而，由於 Excel 的精確度有限，因此計算機公式較容易有四捨五入的誤差。在統計計算的相關文件中，計算機公式通常是作為如何不計算變異數的範例。利用 VAR 的相關文件隨附的工作表，您可以嘗試判斷出舊版 Excel 中的四捨五入誤差很可能會造成問題。您會看到當資料中有許多有效位數，但是值之間幾乎沒有差別時，所發生的問題。您可以預期對大部分的使用者而言，這種四捨五入的誤差實際上不太可能會有什麼麻煩。然而，修訂不同的函數以取代計算機公式的兩次運算程序是值得的，因為它會使用適當的程序來取代過時且不被信任的程序。它也影響到許多的函數。

DEVSQ 也會計算有關平均數的平方差和。DEVSQ 永遠是使用兩次運算程序來執行的。因此，另一種計算 VAR(data) 的方法就是計算 DEVSQ(data) / (COUNT(data) – 1)。這種方法在所有 Excel 版本中都會產生相同的結果。如果您是使用 Excel 2002 或更早的版本，可以同時計算 VAR 和 DEVSQ(data) / (COUNT(data) – 1)，看看這兩種結果的差別為何。其差別基本上就是 VAR 中的四捨五入誤差。其他需要與平均數有關的平方差和、以及永遠使用兩次運算程序的函數為 CORREL 和 COVAR。PEARSON 和 CORREL 都會計算皮爾森積差相關係數 (Pearson Product-Moment Correlation Coefficient)。雖然這兩者在 Excel 2003 中會產生相同的結果，但是 PEARSON 在舊版的 Excel 中是使用一次運算演算法來執行的。

許多函數都包含與平均數有關的平方差和。下列各函數均已執行兩次運算程序：VAR、VARA、VARP、VARPA、STDEV、STDEVA、STDEVP、STDEVPA、PEARSON、SLOPE 和 STEYX。其他由於基本上是呼叫前述清單的其中一個函數，因此已經改良的函數包括：FORECAST、INTERCEPT、RSQ、TTEST 和 ZTEST (當使用者從引數中省略標準差時)。在 Analysis ToolPak 中，三項 ANOVA 工具的改良方式都是利用以兩次運算程序來取代計算機公式。另外還有在樞紐分析表和資料合併中使用的 VAR、VARP、STDEV 和 STDEVP 的版本。有一份 DVAR、DVARP、DSTDEV 和 DSTDEVP 函數的相關文件。

如需詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
828125? (http://support.microsoft.com/kb/828125/ ) Excel statistical functions:DVAR, DVARP, DSTDEV, and DSTDEVP


這些也都已經升級。

總之，如果您是使用舊版的 Excel，就必須注意萬一資料包含許多有效位數、但是差異很小時，會有四捨五入誤差的問題。VAR 的相關文件提供了這方面的例子；您可以預期這些四捨五入的問題在真實資料中通常並不會經常發生。

如需詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
826112? (http://support.microsoft.com/kb/826112/ ) Excel statistical functions:VAR
回此頁最上方

連續型分配反函數

這類函數是名稱結尾為「INV」的所有函數。它們都是使用二元搜尋將收斂值傳回給使用者。因此，CHIINV、NORMSINV 和其他函數的精確度是視兩個因素而定：基礎分配的精確度 (例如 CHIDIST 和 NORMSDIST) 以及二元搜尋程序的改善。

在 Excel 2002 中，二元搜尋程序在一種情況中已改良，即為 NORMSINV。這項改良也會影響 NORMINV。NORMSINV 和 NORMINV 是到目前為止最常使用的反函數。改良的本質是要改善搜尋，讓它可以繼續，直到結果達到 Excel 精確度的限制為止，而不是在所找到的值的相關機率在正確機率的 3 * 10^(-7) 之內時就提早停止。雖然這項搜尋程序的改善改良了 NORMSINV，Excel 2002 的版本仍然需要更精確的 NORMSDIST。

在 Excel 2003 中，用於 Excel 2002 的 NORMSINV 的改良式二元搜尋是針對其他各個「INV」函數所執行的。由於 NORMSDIST 也已改良 (第二類)，因此 Excel 2003 中 NORMSINV 的版本優於 Excel 2002 中的版本，也優於 Excel 2000 和更早的 Excel 版本中的版本。

總之，假定您以不會太靠近 0 或 1 (例如約在 10^(-6) 之內) 的機率值 (例如 NORMSINV(p) 中的 p) 呼叫這些函數，這些函數在舊版 Excel 中的效能還算不錯。
回此頁最上方

離散型分配的函數

Knusel 指出，BINOMDIST、HYPGEOMDIST 和 POISSON 在特定情況下不會計算數值結果 (及傳回 #NUM!)。CRITBINOM 和 NEGBINOMDIST 也是同樣的情形。只要這些函數沒有產生如 #NUM! 的錯誤訊息，便會產生精確的結果。

這些問題已經加以修正。您不必太在意這些問題，因為輸入參數不太可能會有造成這種問題發生的值。例如，只要嘗試的次數少於 1,030，BINOMDIST 便會在 Excel 2002 和更早版本中傳回精確的答案。

這五種函數都是以相同的方式加以修正的：如果函數的輸入保證不會有計算問題 (例如 BINOMDIST 中少於 1,030 的嘗試次數)，則繼續使用現有的程式碼；萬一發生問題，則改用其他的計畫。對於這五種函數，這種方法都是使用允許評估您所希望的機率，而不需要評估組合係數 (如 COMBIN(1030, 515)) 或大型數字的階乘 (如 FACT(200)) 的程序。不論是何種情況，備用計畫都有相同的一般程序。

如需詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
827459? (http://support.microsoft.com/kb/827459/ ) Excel statistical functions:BINOMDIST
828117? (http://support.microsoft.com/kb/828117/ ) Excel statistical functions:CRITBINOM
828515? (http://support.microsoft.com/kb/828515/ ) Excel Statistical Functions:HYPGEOMDIST
828361? (http://support.microsoft.com/kb/828361/ ) Excel Statistical Functions:NEGBINOMDIST
828130? (http://support.microsoft.com/kb/828130/ ) Excel Statistical Functions:POISSON


總之，舊版 Excel 的使用者實際上不需要擔心數值問題，因為造成 #NUM! 結果的輸入值是很極端的。此外，您可以確定的是如果收到錯誤訊息以外的結果，該結果便是正確的。四捨五入誤差在這裡不是問題。
回此頁最上方

亂數產生器

對於一連串的亂數，有隨機性的標準測試。RAND 的亂數順序會重複，但其重複次數太少無法通過這些測試。改良式演算法經過執行，通過了所有的隨機性標準測試。

Excel 2002 及更早版本中的亂數產生器對您的隨機資料產生重大實際影響的機率非常小。例如，您必須先有一連串冗長的亂數 (例如 1 百萬)，重複的行為才會對您的結果有重大的影響。

由於取代演算法的執行很簡單，因此仍然值得嘗試進行改良。

總之，如果您是使用舊版的 Excel，除非您使用很多亂數，否則請不要擔心 RAND 所產生的虛擬亂數的品質。
回此頁最上方

結論

本概觀文件討論了六種類型的函數，它們是根據 Knusel 、McCullough 和 Wilson 在文件中所提出的 Excel 統計函數中的缺點所建立的。每種類型的函數均已進行改良。我們鼓勵讀者參閱個別函數的相關文件，以獲得詳細資訊。

本文警告 Excel 2002 及更早版本的使用者要避免使用 LINEST(known_y's, known_x's, FALSE, TRUE) 的輸出資料表最後三列的結果。所有版本的使用者均不應在下列情形中使用 Analysis ToolPak's tool 進行 t-Test：如果有資料遺失時的 Paired Two Sample for Means。不論資料值為何，這兩種情況都會傳回不正確的結果。

在其他情況下，Excel 2002 和更早版本中的數值錯誤對於使用者的影響很難測量。對於您提供數字引數的函數，錯誤的程度通常是視這些引數的值而定 (例如，嘗試次數為 1,500 的 BINOMDIST 或 NORMSDIST(-7))。在這些情況下，此類引數通常必須是「極端的」，才會有嚴重數字問題的風險。對於您提供一個或多個資料範圍的函數，例如包含平方和 (VAR、STDEV、SLOPE) 的任何函數，資料值也必須是「極端的」，亦即有許多的有效位數和微小差異，四捨五入的問題才會有影響。對於 LINEST，您必須注意共線性的可能性。

當 Excel 2002 或更早版本中建立的工作表在 Excel 2003 中重新計算時，或許您永遠不會注意到有何差別。然而，重要的是要改良在調查函數能力的測試中發現其數值精確度不足的函數，以便處理計算統計領域的專家已知的輸入，讓這些函數達到最大的利用。在最初將函數加入 Excel 中時，沒有人會預期到未來的使用。例如，六標準差 (Six Sigma) 技術的使用並不廣泛。現在，您不會想要傳回錯誤的 NORMSDIST(-6) 或 NORMSDIST(6) 的值了。希望 Excel 2003 的數值改良可以讓這些統計函數適合無法預期的未來使用。
回此頁最上方
這篇文章中的資訊適用於:
  • Microsoft Office Excel 2003
  • Microsoft Excel 2004 for Mac
回此頁最上方
關鍵字：?
kbinfo kbfunctions kbfuncstat KB828888
回此頁最上方
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應，不負任何擔保責任。Microsoft及(或)其供應商謹此聲明，不負任何對與此資訊有關之擔保責任，包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。