登入

Select the product you need help with

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

(http://support.microsoft.com/kb/826112/ )

Excel statistical functions: VAR

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

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

模型 1：單因子

以下是含有資料的簡單範例。

摺疊此表格展開此表格

ANOVA 1 基本模型：
1	2	3
2	4	4
3	6	5
4	8	6
5		7
6		8
Anova：單因子

摘要
群組	計數	總和	平均	變異數
欄 1	6	21	3.5	3.5
欄 2	4	20	5	6.666667
欄 3	6	33	5.5	3.5


ANOVA
變異來源	SS	df	MS	F	P 值	F crit
群組間	12.75	2	6.375	1.506818	0.257897	3.805567
群組內	55	13	4.230769

總計	67.75	15

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
測試 1	1	2	3
	2	4	4
	3	6	5
測試 2	4	8	6
	5	10	7
	6	12	8
Anova：有複寫的雙因子

摘要	群組 1	群組 2	群組 3	總計
測試 1
計數	3	3	3	9
總和	6	12	12	30
平均	2	4	4	3.333333
變異數	1	4	1	2.5

測試 2
計數	3	3	3	9
總和	15	30	21	66
平均	5	10	7	7.333333
變異數	1	4	1	6.25

總計
計數	6	6	6
總和	21	42	33
平均	3.5	7	5.5
變異數	3.5	14	3.5


ANOVA
變異來源	SS	df	MS	F	P 值	F crit
樣本	72	1	72	36	6.22E-05	4.747221
欄	37	2	18.5	9.25	0.003709	3.88529
交互作用	9	2	4.5	2.25	0.147973	3.88529
群組內	24	12	2

總計	142	17

同樣地，如果 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 基本模型：	低	中	高
貧窮	1	2	3
	2	4	4
	3	6	5
中產階級	4	8	6
	5	10	7
	6	12	8
富有	7	14	10
	8	12	6
	9	10	2

Anova：沒有複寫的雙因子

摘要	計數	總和	平均	變異數
貧窮	3	6	2	1
	3	10	3.333333	1.333333
	3	14	4.666667	2.333333
中產階級	3	18	6	4
	3	22	7.333333	6.333333
	3	26	8.666667	9.333333
富有	3	31	10.33333	12.33333
	3	26	8.666667	9.333333
	3	21	7	19

低	9	45	5	7.5
中	9	78	8.666667	16
高	9	51	5.666667	6.25


ANOVA
變異來源	SS	df	MS	F	P 值	F crit
列	176.6667	8	22.08333	5.76087	0.001476	2.591094
欄	68.66667	2	34.33333	8.956522	0.002455	3.633716
錯誤	61.33333	16	3.833333

總計	306.6667	26

如果 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 重點模型

摺疊此表格展開此表格

100000001	100000002	100000003
100000002	100000004	100000004
100000003	100000006	100000005
100000004	100000008	100000006
100000005		100000007
100000006		100000008

Anova：單因子

摘要
群組	計數	總和	平均	變異數
欄 1	6	600000021	1E+08	4.8
欄 2	4	400000020	1E+08	8
欄 3	6	600000033	1E+08	1.6


ANOVA
變異來源	SS	df	MS	F	P 值	F crit
群組間	0	2	0	0	1	3.805567
群組內	64	13	4.923077

總計	64	15

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

摺疊此表格展開此表格

	群組 1	群組 2	群組 3
測試 1	100000001	100000002	100000003
	100000002	100000004	100000004
	100000003	100000006	100000005
測試 2	100000004	100000008	100000006
	100000005	100000010	100000007
	100000006	100000012	100000008
Anova：有複寫的雙因子

摘要	群組 1	群組 2	群組 3	總計
測試 1
計數	3	3	3	9
總和	300000006	300000012	300000012	9E+08
平均	100000002	100000004	100000004	1E+08
變異數	0	4	0	4

測試 2
計數	3	3	3	9
總和	300000015	300000030	300000021	9E+08
平均	100000005	100000010	100000007	1E+08
變異數	0	4	0	6

總計
計數	6	6	6
總和	600000021	600000042	600000033
平均	100000004	100000007	100000005.5
變異數	4.8	14.4	1.6


ANOVA
變異來源	SS	df	MS	F	P 值	F crit
樣本	64	1	64	24	0.000367	4.747221
欄	32	2	16	6	0.015625	3.88529
交互作用	32	2	16	6	0.015625	3.88529
群組內	32	12	2.666666667

總計	128	17

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

摺疊此表格展開此表格

	低	中	高
貧窮	100000001	100000002	100000003
	100000002	100000004	100000004
	100000003	100000006	100000005
中產階級	100000004	100000008	100000006
	100000005	100000010	100000007
	100000006	100000012	100000008
富有	100000007	100000014	100000010
	100000008	100000012	100000006
	100000009	100000010	100000002

Anova：沒有複寫的雙因子

摘要	計數	總和	平均	變異數
列 1	3	300000006	100000002	0
列 2	3	300000010	100000003	2
列 3	3	300000014	100000005	2
列 4	3	300000018	100000006	4	<---
列 5	3	300000022	100000007	6
列 6	3	300000026	100000009	10
列 7	3	300000031	100000010	12
列 8	3	300000026	100000009	10
列 9	3	300000021	100000007	18

欄 1	9	900000045	100000005	8
欄 2	9	900000078	100000009	14
欄 3	9	900000051	100000006	4


ANOVA
變異來源	SS	df	MS	F	P 值	F crit
列	128	8	16	2	0.113281	2.591094
欄	32	2	16	2	0.167772	3.633716
錯誤	128	16	8

總計	288	26

回此頁最上方 | 提供意見

屬性

文章編號: 829215 - 上次校閱: 2007年3月14日 - 版次: 2.0

這篇文章中的資訊適用於:

Microsoft Office Excel 2007
Microsoft Office Excel 2003

kbexpertisebeginner kbfunctions kbprogramming kbfuncstat kbinfo KB829215

Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應，不負任何擔保責任。Microsoft及(或)其供應商謹此聲明，不負任何對與此資訊有關之擔保責任，包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。

回此頁最上方 | 提供意見