說明經過改良的統計函數對於 Excel 分析工具箱所產生的影響

文章翻譯 文章翻譯
文章編號: 829208 - 檢視此文章適用的產品。
全部展開 | 全部摺疊

在此頁中

結論

本文將告訴您,精確度經過改良的 Microsoft Office Excel 2003 和更新版 Excel 的統計函數,對於分析工具箱 (ATP) 工具所產生的影響。大部分 ATP 工具在計算結果的過程中,都會呼叫 Excel 統計函數。在大多數的情況下,本文可做為個別 Excel 統計函數的文件指標。另外,也針對一些 ATP 工具,提供未來將進一步改良的說明。

Microsoft Excel 2004 for Mac 資訊

Excel 2004 for Mac 的統計函數已使用在 Microsoft Office Excel 2003 及更新版 Excel 中,用來更新統計函數的相同演算法加以更新。本文說明函數在 Excel 2003 及更新版 Excel 中如何運作或函數之修改的任何資訊,亦適用於 Excel 2004 for Mac。

其他相關資訊

除了在三個 ATP ANOVA 工具中的改良之外,並未直接編輯 ATP 的程式碼。

有幾個 ATP 工具的數值效能在 Excel 2003 和更新版 Excel 中已獲得改善,這是因為工具呼叫的 Excel 統計函數在 Excel 2003 和更新版 Excel 中已獲得改善。當舊版 Excel 和更新版 Excel 的結果不同時,Excel 2003 和更新版 Excel 的值會更準確。

大部分使用者不會注意到不同版本的 Excel 之間的結果有何差異。這是因為差異通常是由捨入誤差所造成,而捨入誤差只有在極端情況下才重要。不過,本文必須先指出因為 Microsoft Excel 2002 和舊版 Excel 中的公式錯誤而發生差異的一個情況。請避免在那些版本中使用該工具。

第二個範例是關於 Excel 2002 和舊版 Excel 的公式錯誤,它繼續出現在 Excel 2003 及更新版 Excel 中。在此情況下,請避免使用所有 Excel 版本的 ATP 工具。

首先,當您必須按一下以勾選 [常數為零 (Constant is Zero)] 核取方塊時,要避免使用 Regression 工具。這在 Excel 2003 和更新版 Excel 中已獲得修正。當 [常數為零 (Constant is Zero)] 核取方塊清除後 (這是較常見的實際狀況),您不必避免 Regression 工具的使用。

其次,所有 Excel 版本的使用者,都應該避免使用 ATP t-Test:Paired Two Sample for Means 工具 (除非您可以保證不會有遺漏的資料觀察值)。如果有可能會遺漏一個或多個觀察值,則此工具會提供不正確的答案 (或完全沒有答案)。

如需有關 ATP Matched Pairs Two Sample t-Test 工具的詳細資訊,請按一下下面的文件編號,檢視「Microsoft 知識庫」中的文件:
829252 You may obtain incorrect results and misleading labels when you use the Analysis ToolPak t-Test in Excel
如果您想要使用此工具,但有遺漏資料 (或有可能會遺漏資料),Excel 中的 TTEST 函數將可正確處理它。

本文後面提供的各小節會說明個別 ATP 工具。未列出的工具未受到 Excel 2003 及更新版 Excel 中的改良影響。

ANOVA:Single Factor、Two-Factor with Replication 及 Two-Factor without Replication

這三個 ANOVA 工具已全部重寫,使運算程序升級至數字功能更健全的雙計算行程演算法。這些改良類似計算平均值平方差總和的統計函數的改良 (例如:VAR、STDEV、SLOPE、PEARSON)。

如需有關 ATP ANOVA 的詳細資訊,請按一下下面的文件編號,檢視「Microsoft 知識庫」中的文件:
829215 Excel 2003 中 Analysis ToolPak ANOVA 工具的數值改良說明

Correlation

此工具未變更。不過,Correlation 工具和 Covariance 工具之間有一點小差異,此差異仍繼續出現在所有 Excel 版本中。Correlation 工具會傳回下三角相關表,1 會在對角線上,相關則從對角線延伸。此工具會使用 CORREL 來計算對角線延伸項目,並在項目中填入 CORREL 傳回的值 (因此,如果有任何資料項目變更,該表中的項目不會變更。請對照此行為與 Covariance 的行為)。

Covariance

此工具會傳回下三角共變數表,變數會在對角線上,共變數則從對角線延伸。對角線上的儲存格會包含公式 "=VARP(...)",因此,如果資料項目變更,該表中的結果也會隨之變更。VARP 在 Office Excel 2003 和更新版 Excel 中已獲得改善。

如需有關 VARP 的詳細資訊,請按一下下面的文件編號,檢視「Microsoft 知識庫」中的文件:
826393 Excel statistical functions:VARP
Covariance 工具會使用 COVAR 來計算對角線延伸項目,並在項目中填入 COVAR 傳回的值。因此,如果資料項目變更,對角線延伸項目不會變更。

Descriptive Statistics

此工具在計算任何項目時,都會呼叫 Excel 統計函數。因為 VAR 和 STDEV 在 Excel 2003 和更新版 Excel 中已獲得改善,所以在極端情況下,有可能因為捨入誤差而產生不同的值。

如需有關 VAR 的詳細資訊,請按一下下面的文件編號,檢視「Microsoft 知識庫」中的文件:
826112 Excel statistical functions:VAR

F-Test Two-Sample for Variances

和 Descriptive Statistics 工具一樣,此工具會呼叫 VAR。同樣地,在極端情況下,這有可能因為捨入誤差而產生不同的值。

Random Number Generation

此工具會在範圍中填入隨機觀察值。這些觀察值會直接輸入到儲存格中,因此,當工作表重新計算時,不會重新計算這些儲存格 (亦即不會以新的觀察值取代之)。反觀 Excel 內建的 RAND 函數,這會在每次重新計算工作表時,將現有的隨機亂數取代成新的隨機亂數。您可以使用 RAND 來保留值。如果要執行這項操作,請複製範圍中的結果,然後使用 [選擇性貼上] 命令,將值貼到相同範圍。

Random Number Generation (RNG) 工具也會從不同機率分佈中產生隨機觀察值,RAND 則對應到該工具的單一選項:Uniform,範圍為 0 到 1 之間。本文將告訴您,如何結合 RAND 與 Excel 的統計函數,來產生這類觀察值。

因此,從功能方面來看,您可以使用 RAND 和一些技巧來模擬 ATP 隨機亂數工具。有時候這樣做很有用,尤其是需要許多隨機亂數時。

以 Excel 2002 和更舊的版本而言,ATP 隨機亂數產生器和 RAND 在隨機標準測試上的評分都很差。效能差是因為虛擬隨機亂數序列開始重複的週期長度太短所致。但是唯有需要許多隨機亂數時,這才會成為問題。

RAND 在 Excel 2003 和更新版 Excel 中已獲得改善,因此現在 RAND 已經通過所有這類標準測試。RAND 的隨機亂數序列將在產生 1 兆個數字以上之後開始自我重複。

如需有關 RAND 的詳細資訊,請按一下下面的文件編號,檢視「Microsoft 知識庫」中的文件:
828795 說明 Excel 2007 與 Excel 2003 中的 RAND 函數
不過,ATP 的個別隨機亂數產生器並未升級。就像在 Excel 2002 和舊版 Excel 中的 RAND 版本,ATP 的個別隨機亂數產生器在隨機標準測試上的評分很差,且重複週期過短。唯有當您需要冗長的隨機亂數序列 (例如 1 百萬個) 時,這才會造成負面影響。

RNG 工具會從不同的機率分佈和 Uniform[0,1] 提供隨機觀察值,Uniform[0,1] 是用於透過 RAND 輸出隨機亂數的分佈。ATP 工具會先抽出 Uniform[0,1] 隨機亂數 (或一個以上的數字),然後從下列其中一種特定分佈中將答案轉換成觀察值。基於為了要產生許多觀察值而偏好 RAND 之使用者的利益著想,本文建議使用下表的 RAND 公式。在該表後面,有一些關於分析工具箱之常態分佈案例的注意事項。
摺疊此表格展開此表格
Distribution使用 RAND() 的 Excel 公式
Bernoulli(p)=IF(RAND() <= p, 1, 0)
Binomial(n,p)=CRITBINOM(n, p, RAND())
Discrete請參閱下面
Normal(mu, sigma)=NORMINV(RAND(), mu, sigma)
Patterned並非真正隨機
Poisson(mean)請參閱下面
Uniform(low, high)= low + (high – low) * RAND()
在 Normal(mu, sigma) 案例中,基於兩個原因,建議您使用 RAND 和本表格中的公式而非 ATP 隨機亂數工具。第一,RAND 是比 ATP 更好的 Uniform[0,1] 隨機亂數產生器。第二,ATP 的工具不會呼叫 Excel 的 NORMINV 函數,而是有它自己內建的反常態分佈。其精確度不如 Excel 2003 和更新版 Excel 中的 NORMINV 版本。它使用的常態分佈近似值的精確度 (Excel 使用改良型 NORMSDIST 函數),以及其二進位搜尋效能 (Excel 的效能更高,以保證更接近 NORMINV 機率引數的值),都比較遜色。整體而言,在此情況下使用 ATP 無法發揮 Excel 2003 和更新版 Excel 在 NORMINV、NORMSDIST 及 RAND 函數的改良。

對於離散分佈的觀察值而言,假設值位於欄 B,且其機率位於欄 C,您可以在欄 A 的每一列填入觀察值小於該列之欄 B 值的機率。假設有 10 個值,並假設此資料是在儲存格 A1:C10。那麼,由於 A1 包含觀察值必須小於第一個值的機率,因此它必須設為 0。您可以使用 VLOOKUP(RAND(), A1:C10, 2);VLOOKUP 的第四個引數是選用項目,必須省略或設為 TRUE。"2" 表示您要將值傳回到第二欄 (此範例中的欄 B)。

在下面文章中,ATP 使用一種調節方法來產生 Poisson 觀察值:Press, W.H., S.A. Teukolsky, W. T. Vetterling, and B.P. Flannery, Numerical Recipes in C, The Art of Scientific Computing, 2nd ed., Cambridge University Press, 1992, pp.293-295. 有兩種方法可以很容易地利用現有的 Excel 函數。

第一種使用的觀察是含有平均值 m 的 POISSON 隨機變數,n 很大時,其分佈很接近 BINOMIAL(n, m/n)。接著您就可以呼叫 CRITBINOM(n, m/n, RAND())。n 的選擇取決於 m;比 m 大 1,000 倍的 n 應該夠大了。

第二種使 POISSON 分佈與指數 (Exponential) 相關聯。如果事件是根據 POISSON 程序發生,並以每單元時間的速率 m 發生,則事件之間的時間即為指數型 (Exponential) 分佈,且平均值為 1/m。以 POISSON 觀察值而言,您可以從這個指數型分佈中得到一系列觀察值,並計算在其總和超過 1 之前發生幾次事件。如果要從這個指數型分佈中取得觀察值,請使用 GAMMAINV(RAND(), 1, 1/m)。當 m 很接近 0 時,此方法適用。

Regression

Regression 工具會呼叫 Excel 的 LINEST。有關 LINEST 的文件,會說明 Excel 2003 和更新版 Excel 有大幅度的改良。

如需有關 LINEST 的詳細資訊,請按一下下面的文件編號,檢視「Microsoft 知識庫」中的文件:
828533 Description of the LINEST function in Excel
如果您使用 Excel 2002 或舊版的 Excel,請注意 ATP Regression 工具與 LINEST 的兩個相同缺點:
  • 當迴歸是透過原點而強制執行時,Regression Sum of Squares、r squared 及 f 統計值一定會不正確。

    以 LINEST 而言,這表示「第三個引數會設為 FALSE 而非 TRUE 或會被省略」。以 ATP 工具而言,它表示「已勾選 [常數為零 (Constant is Zero)] 核取方塊」。
  • LINEST 和 ATP 工具不在乎共線性問題。有關 LINEST 的文件,會說明 Excel 2003 和更新版 Excel 中 LINEST 的運算方法,其設計是要尋找共線性或近共線性 (如果存在) 並適當地反應。
這兩個 LINEST 缺點在 Excel 2003 和更新版 Excel 中都已經解決。所以 ATP Regression 工具的效能將同樣獲得改善。工具的程式碼沒有變更;其中是藉由呼叫改良過的 Excel 函數而獲得改善。我認為 LINEST 的改良是統計函數改良中最重要的一項。

下表顯示 Regression 工具在舊版的 Excel 和更新版 Excel 的輸出,其中已選取 [常數為零 (Constant is Zero)] 核取方塊。這將示範前述的第一個缺點。在舊版的 Excel 中,Regression sum of squares 是負數,R Square 值也是。
摺疊此表格展開此表格
X'sY's
111
212
313
Excel 2002 和舊版的 Excel
摘要輸出
Regression 統計值
Multiple R65535
R Square-20.4285714
Adjusted R Square-20.9285714
Standard Error4.629100499
Observations3
ANOVA
dfSSMSFSignificance F
Regression1-40.85714286-40.85714286-1.90666667#NUM!
Residual242.8571428621.42857143
Total32
Excel 2003 和更新版的 Excel
摘要輸出
Regression 統計值
Multiple R0.949342311
R Square0.901250823
Adjusted R Square0.401250823
Standard Error4.629100499
Observations3
ANOVA
dfSSMSFSignificance F
Regression1391.1428571391.142857118.253333330.14637279
Residual242.8571428621.42857143
Total3434

t-Test:Paired Two Sample for Means

如前所述,如果有可能會遺漏一個或多個資料值,請避免使用此工具。此測試的原型應用是在某項處置之前和之後對主體進行測量的實驗 (例如 60 天減肥計劃之前和之後的體重)。如果沒有遺漏觀察值,則此工具的表現良好。如果遺漏的之前和之後觀察值數量不同,您會看到錯誤訊息,且工具不會做任何計算。如果有遺漏觀察值且遺漏的之前和之後觀察值數量相等,此工具將傳回包含數個錯誤的答案。

如果有遺漏之前或之後的測量值,標準程序是從資料中移除主體,並分析只包含有之前和之後測量值的那些主體的資料。Excel 的 TTEST 函數會根據此標準程序來處理遺漏的資料。

其他兩個 t-Test 工具:Two-Sample Assuming Equal Variances 和 Two-Sample Assuming Unequal Variances 並無此瑕疵。

z-Test:Two Sample for Means

本文有提到,隨機亂數產生工具的常態分佈案例不會呼叫 NORMSINV 函數 (或者更精確地說,呼叫 NORMSINV 的 NORMINV),而是它會用自己的次級程序來尋找標準相反值。

z-Test 工具會呼叫 NORMSINV 函數並利用 Excel 2003 和更新版 Excel 的改良功能。

舊版 Excel 中的結果

有些 ATP 工具的效能在 Excel 2003 和更新版 Excel 中已獲得改善,因為它們呼叫的 Excel 統計函數在 Excel 2003 和更新版 Excel 中已獲得改善。其中一項針對 LINEST 的改良,是當它的第三個引數設為 FALSE,且已選取 [常數為零 (Constant is Zero)] 核取方塊時,ATP 迴歸工具以往在 Excel 2002 和舊版的 Excel 中會傳回不正確的結果。其他 Excel 函數獲得改善的情況,包括舊版的使用者不太可能注意到的差異 (因為大部分這些差異都涉及在極端情況下的捨入誤差)。

另有三個 ATP ANOVA 工具是藉由編輯 ATP 程式碼,以數字功能更健全的演算法取代之而獲得改善 (與 Excel VAR 的改良方式一樣)。在舊版的 Excel 中,這些工具的使用者只有在極端情況下才會注意到差異。

對所有版本的使用者的警告:避免使用 t-Test:Paired Two Sample for Means (如果有可能遺漏任何資料)。

Excel 2003 和更新版 Excel 的結果

Excel 的統計函數已做了重大改良。這使許多呼叫這些函數的 ATP 工具也獲得改良。其中有一個 ATP 工具 (隨機亂數產生器) 將不利用改良過的 RAND 函數 (因為它是以自我包含的方式實作,所以不會呼叫 RAND)。這點很可惜,但是更遺憾的是正常分佈隨機觀察值的特殊情況。反常態分佈也是以自我包含的方式實作,而不會呼叫改良過的 NORMSINV 函數。

下表列出 ATP 工具和它們會呼叫的 Excel 函數,這些函數已經在 Excel 2003 和更新版 Excel 中獲得改善。請讀者另行參考每一個所呼叫 Excel 函數的個別文件。
摺疊此表格展開此表格
ATP 工具呼叫的 Excel 函數
ANOVA:Single FactorVAR、FINV
ANOVA:Two-Factor With ReplicationVAR、FINV
ANOVA:Two-Factor Without ReplicationVAR、FINV
Correlation
Covariance
Descriptive StatisticsSTDEV、TINV、VAR
Exponential Smoothing
F-Test Two-Sample for VariancesVAR、FINV
Fourier Analysis
Histogram
Moving Average
Random Number Generation
Rank and Percentile
RegressionLINEST
SamplingRAND
t-Test:Paired Two Sample for MeansVAR、PEARSON、TINV
t-Test:Two-Sample Assuming Equal VariancesVAR、TINV
t-Test:Two-Sample Assuming Unequal VariancesVAR、TINV
z-Test:Two Sample for MeansNORMSDIST、NORMSINV
除了 LINEST 和 RAND 之外,凡是出現在此表格中的所有函數,只有因為極端情況下的捨入誤差,您才會看到舊版 Excel 和更新版 Excel 之間的差異。如前所述,LINEST 已改善很多,RAND 也有所改善。奇怪的是,Sampling 工具會呼叫 RAND,但 RNG 工具卻仰賴一個自我包含的產生器,當需要冗長的隨機觀察值序列時,此產生器的效能會比較差。

結論

除了三個 ANOVA 工具的 ATP 程式碼有變更之外,並未重寫其他 ATP 程式碼。不過,工具會因為呼叫經過改良的 Excel 函數而受益,如 ATP 工具表所顯示。t-Test 的缺點:在 Excel 2003 或更新版 Excel 中,並未修正 Paired Two Sample for Means 測試。最明顯的改善可能是 Regression 工具,在選取 [常數為零 (Constant is Zero)] 核取方塊之後,LINEST 不會再傳回不正確的結果,且 LINEST 已設計為可以正確處理共線性。

屬性

文章編號: 829208 - 上次校閱: 2007年3月19日 - 版次: 4.0
這篇文章中的資訊適用於:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2004 for Mac
關鍵字:?
kbformula kbexpertisebeginner kbfunctions kbfuncstat kbinfo KB829208
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應,不負任何擔保責任。Microsoft及(或)其供應商謹此聲明,不負任何對與此資訊有關之擔保責任,包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。

提供意見

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com