Excel 中 Analysis ToolPak ANOVA 工具的數值改良說明

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

在此頁中

結論

本文將分別告訴您三種 Analysis ToolPak ANOVA 工具中的數值改良,並說明在極端的情況中,Microsoft Excel 2002 (含) 以前版本中的不正確結果。

其他相關資訊

許多函數都需要計算平均數的差平方和。為了精確執行這項操作,Microsoft OfficeExcel 2003 (含) 以後版本 會使用二段式程序,在第一階段找出平均數,然後在第二階段計算平均數的差平方。

就精確的算術而言,在使用「計算機公式」的舊版 Excel 中會發生相同的結果。這個公式的名稱來自於以前統計學家大多使用計算機而非電腦。舊版會使用計算機公式來加總觀察值的平方,然後從此總和中減去下列的數量:
((sum of observations)^2) / number of
		 
		  observations
這項計算會在一段式資料處理中進行。

就有限精確度算術而言,計算機公式很容易在極端的情況中發生無條件捨去的錯誤。對於大部分需要計算平均數之差平方和 (例如 VAR、STDEV、SLOPE 和 PEARSON) 的函數,Excel 2002 (含) 以前版本都會使用計算機公式進行計算。然而,這些版本的 Excel 也會針對 CORREL、COVAR 和 DEVSQ 函數使用在數值運算上功能更健全的二段式程序。

統計計算方面的專家並不建議使用計算機公式。在有關統計計算的書籍中,都是以「如何才能不使用」的觀點來論述計算機公式。可惜的是,在 Excel 2002 (含) 以前版本中,這三種 Analysis ToolPak (ATP) ANOVA 工具都廣泛使用計算機公式或相等的一段式。

Excel 2003 (含) 以後版本 對於三種 ATP ANOVA 模型都是使用二段式程序。本文將說明這些在 ATP 之三種 ANOVA 模型中的計算改良:
  • 單因子
  • 有複寫的雙因子
  • 沒有複寫的雙因子
本文稍後將會說明這些模型。

由於 Excel 對於 DEVSQ 一定是使用兩段式程序,因此本文經常會以它來說明改良後的程序。這些修訂後的程序可有效呼叫 DEVSQ 或使用功能和 DEVSQ 的功能完全相同的程式碼。

對於每一項 ANOVA 工具,ATP 輸出都包含一份「摘要」表格,其中包含「計數」、「總和」、「平均」和「變異數」的值,以及具有 SS、df、MS、F 和 P 值的平方與值的各種不同總和的 ANOVA 表格。摘要表格中的結果是透過呼叫 Excel 的 COUNT、SUM、AVERAGE 和 VAR 函數所計算得出。在這四種函數中,只有 VAR 容易發生無條件捨去的錯誤。

Excel 2002 (含) 以前版本是使用計算機公式來實作 VAR。下列有關 VAR 的文件說明了Excel 2003(含)以後 版本中的改良,並讓您嘗試使用數值資料來查看舊版中何時可能會發生無條件捨去的錯誤。

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


本文在介紹三種 ANOVA 模型時,重點是放在 ANOVA 輸出表格上。不論是哪一種情況,「摘要」表格在Excel 2003(含)以後 版本中都運作良好。在 Excel 2002 (含) 以前版本中,當資料有極端值時,[變異數] 欄中就會發生問題。

然而,本文在模型部分包含「摘要」表格是因為當您檢閱附錄中的修改後範例時,它們對於進行比較十分有用。

模型 1:單因子

以下是含有資料的簡單範例。
摺疊此表格展開此表格
ANOVA 1 基本模型:
123
244
365
486
57
68
Anova:單因子
摘要
群組計數總和平均變異數
欄 16213.53.5
欄 242056.666667
欄 36335.53.5
ANOVA
變異來源SSdfMSFP 值F crit
群組間12.7526.3751.5068180.2578973.805567
群組內55134.230769
總計67.7515
Excel 2002 (含) 以前版本是使用下列虛擬程式碼來計算平方和:
GrandSum = 0;
GrandSumOfSqs = 0; 
GrandSampleMeanSqrd = 0; 
GrandMeanSqrd = 0; 
GrandSampleSize = 0;

For s = 1 to Number_of_Samples do
   GrandSum = GrandSum + sum of observations in s-th sample;
   GrandSumOfSqs = GrandSumOfSqs + sum of squared observations in s-th sample;
   GrandSampleMeanSqrd = GrandSampleMeanSqrd  +
      (sum of observations in s-th sample^2)/size of s-th sample;
   GrandSampleSize = GrandSampleSize + size of s-th sample
Endfor;

GrandMeanSqrd = (GrandSum^2) / GrandSampleSize;

TotalSS = GrandSumOfSqs – GrandMeanSqrd;
BetweenGroupsSS = GrandSampleMeanSqrd – GrandMeanSqrd;
WithinGroupsSS = GrandSumOfSqs – GrandSampleMeanSqrd;
這種方法基本上就是計算機公式:計算觀察值的平方和,然後從中減去一單位數量,就像 VAR 計算觀察值的平方和一樣,然後減去 sum of observations^2/sample size。模型 2 和模型 3 已省略類似的虛擬程式碼。

對於模型 2 和模型 3,也同樣會計算平方和,然後從平方和中減去一單位數量,就像在計算機公式中一樣。可惜的是,基本統計書籍經常為 ANOVA 提供建議使用的方法,例如本文稍早所述的方法。

Excel 2003 (含) 以後 版本會使用不同的方法來計算 ANOVA 表格之 SS 欄中的各種不同項目。為了說明,本文會假設之前範例中的數值資料會出現在儲存格 A2:C7 中,且在儲存格 B6 和 B7 中缺少資料:
  • 「總計 SS」只是套用至所有資料的 DEVSQ,例如 DEVSQ(A2:C7)。即使缺少資料,DEVSQ 仍然可正常運作。
  • 「群組間 SS」等於「總計 SS」減去套用至各欄之 DEVSQ 的總和,例如 DEVSQ(A2:A7) + DEVSQ(B2:B7) + DEVSQ(C2:C7)。
  • 「群組內 SS」等於「總計 SS」減去「群組間 SS」。
如果 ANOVA 表格之 SS 欄中的項目計算正確,表格中的其他項目也會正確。

模型 2:有複寫的雙因子

以下是含有資料的簡單範例。
摺疊此表格展開此表格
ANOVA 2 基本模型群組 1群組 2群組 3
測試 1123
244
365
測試 2486
5107
6128
Anova:有複寫的雙因子
摘要群組 1群組 2群組 3總計
測試 1
計數3339
總和6121230
平均2443.333333
變異數1412.5
測試 2
計數3339
總和15302166
平均51077.333333
變異數1416.25
總計
計數666
總和214233
平均3.575.5
變異數3.5143.5
ANOVA
變異來源SSdfMSFP 值F crit
樣本72172366.22E-054.747221
37218.59.250.0037093.88529
交互作用924.52.250.1479733.88529
群組內24122
總計14217
同樣地,如果 SS 欄中的項目計算正確,輸出之 ANOVA 部分中的所有其他項目都會正確。

以下是Excel 2003(含)以後 版本的計算程序。它是使用 DEVSQ 來計算 ANOVA 表格之 SS 欄中各種不同的項目。為了說明,本範例會假設數值資料出現在儲存格 B2:D7 中。
  • 「總計 SS」只是套用至所有資料的 DEVSQ,例如 DEVSQ(B2:D7)。
  • 「樣本 SS」等於「總計 SS」減去套用至各範例之 DEVSQ 的總和,例如 DEVSQ(B2:D4) + DEVSQ(B5:D7)。
  • 「欄 SS」等於「總計 SS」減去套用至各欄之 DEVSQ 的總和,例如 DEVSQ(B2:B7) + DEVSQ(C2:C7) + DEVSQ(D2:D7)。
  • 「群組內 SS」是套用至各測試或群組配對之 DEVSQ 的總和,例如 DEVSQ(B2:B4) + DEVSQ(C2:C4) + DEVSQ(D2:D4) + DEVSQ(B5:B7) + DEVSQ(C5:C7) + DEVSQ(D5:D7)。
  • 「交互作用 SS」等於「總計 SS」減去「樣本 SS」、「欄 SS」及「群組內 SS」。

模型 3:沒有複寫的雙因子

以下是含有資料的簡單範例。
摺疊此表格展開此表格
ANOVA 3 基本模型:
貧窮123
244
365
中產階級486
5107
6128
富有71410
8126
9102
Anova:沒有複寫的雙因子
摘要計數總和平均變異數
貧窮3621
3103.3333331.333333
3144.6666672.333333
中產階級31864
3227.3333336.333333
3268.6666679.333333
富有33110.3333312.33333
3268.6666679.333333
321719
94557.5
9788.66666716
9515.6666676.25
ANOVA
變異來源SSdfMSFP 值F crit
176.6667822.083335.760870.0014762.591094
68.66667234.333338.9565220.0024553.633716
錯誤61.33333163.833333
總計306.666726
如果 SS 欄中的值計算正確,ANOVA 表格中的所有其他值都會正確。

Excel 2003 (含) 以後 版本會使用下列計算程序,此程序會使用 DEVSQ 來計算 ANOVA 表格之 SS 欄中的值。為了說明,本例會假設稍早範例中顯示的儲存格範圍是儲存格 A1:D10。因此,數值資料會出現在儲存格 B2:D10 中。
  • 「總計 SS」只是套用於所有資料的 DEVSQ,例如 DEVSQ(B2:D10)。
  • 「列 SS」等於「總計 SS」減去套用於各列之 DEVSQ 的總和,例如 DEVSQ(B2:D2) + DEVSQ(B3:D3) + DEVSQ(B4:D4) + DEVSQ(B5:D5) + DEVSQ(B6:D6) + DEVSQ(B7:D7) + DEVSQ(B8:D8) + DEVSQ(B9:D9) + DEVSQ(B10:D10)。
  • 「欄 SS」等於「總計 SS」減去套用於各欄之 DEVSQ 的總和,例如 DEVSQ(B2:B10) + DEVSQ(C2:C10) + DEVSQ(D2:D10)。
  • 「誤差 SS」等於「總計 SS」減去「列 SS」以及「欄 SS」。

Excel 2002 (含) 以前版本中的結果

在資料中包含許多有效位數但也包含很小的變異數的極端情況中,計算機公式會導致不正確的結果。本文稍後的附錄會提供在這種極端情況中,無條件捨去的問題範例。

Excel2003(含)以後 版本中的結果

Excel 2003 (含) 以後 版本會使用二段式資料處理的程序。在第一階段,Excel 2003 (含) 以後 版本會計算資料值的總和與計數。透過這些資料,Excel 便可以計算樣本平均數。

在第二階段,Excel 會計算各資料點之間的差平方以及樣本均數,然後加總這些差平方。因此,Excel 2003(含)以後 版本中的結果在數值方面會更穩定。

結論

和舊版相比,二段式方法改良了所有三種 ATP ANOVA 工具在Excel 2003(含)以後 版本中的數值效能。使用Excel 2003 (含) 以後 版本取得的結果絕對比使用舊版取得的結果精確。

然而,在大部分實際情況中,這些結果並無差別。這是因為一般而言,資料並不會出現如下面附錄所述的不尋常行為。當資料包含大量的有效位數,而資料值之間幾乎沒有變異時,最有可能在舊版 Excel 中發生數值不穩定的情況。

如果您使用舊版 Excel,而想要查看Excel 2003(含) 以後 版本是否會提供不同的 ANOVA 結果,請比較在舊版中使用 ANOVA 工具取得的結果以及使用 DEVSQ 的程序取得的結果。

注意 本文稍早已說明過對於與各工具有關聯的 ANOVA 表格使用 DEVSQ 的程序。

如果要確認各範圍的變異數在「摘要」表格中是正確的,請使用 DEVSQ(range)/(COUNT(range) – 1)。

附錄:Excel 2002 (含) 以前版本的效能的數值範例

對於模型 1、2 和 3 的各個基本範例,本文之前已顯示 ATP 工具的輸出,包括「摘要」和 ANOVA 表格。每個範例中的資料都已經過修改,以建立「重點」範例,而方法則是在各資料值中加入 10^8。在各資料值中加入如 10^8 的常數不會影響「摘要」表格中的「變異數」(但是將會明顯影響「平均數」及「總和」)。它應該也不會影響 ANOVA 表格中的任何項目。

如果您比較「摘要」表格中的「變異數」和 ANOVA 表格中的 SS,將會注意到除了模型 3 中以「<---」指出的一個項目以外,這些在下列三個重點模型中的計算都是不正確的。

在所有的重點範例中,使用 Excel 2003 (含) 以後版本取得的 ANOVA 結果都會符合基本範例中之前的結果 (也應該如此)。

有龐大資料值的 ANOVA 1 重點模型

摺疊此表格展開此表格
100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
100000004100000008100000006
100000005100000007
100000006100000008
Anova:單因子
摘要
群組計數總和平均變異數
欄 166000000211E+084.8
欄 244000000201E+088
欄 366000000331E+081.6
ANOVA
變異來源SSdfMSFP 值F crit
群組間020013.805567
群組內64134.923077
總計6415

有龐大資料值的 ANOVA 2 重點模型

摺疊此表格展開此表格
群組 1群組 2群組 3
測試 1100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
測試 2100000004100000008100000006
100000005100000010100000007
100000006100000012100000008
Anova:有複寫的雙因子
摘要群組 1群組 2群組 3總計
測試 1
計數3339
總和3000000063000000123000000129E+08
平均1000000021000000041000000041E+08
變異數0404
測試 2
計數3339
總和3000000153000000303000000219E+08
平均1000000051000000101000000071E+08
變異數0406
總計
計數666
總和600000021600000042600000033
平均100000004100000007100000005.5
變異數4.814.41.6
ANOVA
變異來源SSdfMSFP 值F crit
樣本64164240.0003674.747221
3221660.0156253.88529
交互作用3221660.0156253.88529
群組內32122.666666667
總計12817

有龐大資料值的 ANOVA 3 重點模型

摺疊此表格展開此表格
貧窮100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
中產階級100000004100000008100000006
100000005100000010100000007
100000006100000012100000008
富有100000007100000014100000010
100000008100000012100000006
100000009100000010100000002
Anova:沒有複寫的雙因子
摘要計數總和平均變異數
列 133000000061000000020
列 233000000101000000032
列 333000000141000000052
列 433000000181000000064<---
列 533000000221000000076
列 6330000002610000000910
列 7330000003110000001012
列 8330000002610000000910
列 9330000002110000000718
欄 199000000451000000058
欄 2990000007810000000914
欄 399000000511000000064
ANOVA
變異來源SSdfMSFP 值F crit
12881620.1132812.591094
3221620.1677723.633716
錯誤128168
總計28826

屬性

文章編號: 829215 - 上次校閱: 2007年3月14日 - 版次: 2.0
這篇文章中的資訊適用於:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
關鍵字:?
kbexpertisebeginner kbfunctions kbprogramming kbfuncstat kbinfo KB829215
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