Excel樞紐分析表協助整理有重複實驗數據作法

摘要：
正常的二因子有重複的因子設計，若欲借助Excel分析工具『雙因子變異數分析-重複試驗』進行實驗數據解析，如下圖1則輸入的數據表排列必須以的特定方式 -『每一樣本的列數( R)』來表示重複次數

本文將描述如何借助樞紐分析表將實驗計劃書整理為特定方式數據表，以便進行『雙因子變異數分析-重複試驗』的實驗數據解析工作，本方法都可在Excel2007以後版本運行。
註：Excel 2003版本也可借助樞紐分析表但操作方法不同

圖1：Excel ANOVA對話框

1. 用Excel進行有重複二因子實驗數據分析
正常的二因子有重複的因子設計，若欲借助Excel分析工具『雙因子變異數分析-重複試驗』時，參考下面表1，輸入數據表的排列必須以『每一樣本的列數( R)』來表示重複次數，因此在輸入數據表應該事先整理為這樣的格式，表1中顯示A因子2水準，B因子3水準，有重複2次的實驗數據整理的特定方式

		B1	B2	B3
	A1 重複1	111	112	113
	A1 重複2	211	212	213
	A2 重複1	121	122	123
	A2 重複2	221	222	223

表1：Excel ANOVA 有重複2次的實驗數據整理的特定方式



2. 實驗計劃書一般格式如圖2，需要整理為表1特定格式數據表



		A	B	C	D	E	F
	1	Std order	Run order	replicate	A	B	Y
	2	1	11	r1	A1	B1	111
	3	2	7	r1	A2	B1	121
	4	3	9	r1	A1	B2	112
	5	4	2	r1	A2	B2	122
	6	5	6	r1	A1	B3	113
	7	6	3	r1	A2	B3	123
	8	7	10	r2	A1	B1	211
	9	8	1	r2	A2	B1	221
	10	9	5	r2	A1	B2	212
	11	10	4	r2	A2	B2	222
	12	11	8	r2	A1	B3	213
	13	12	12	r2	A2	B3	223

圖2：有重複的實驗計劃書一般格式
圖2中Y為實驗結果數據，本文刻意以3位數字表示，首位數字1表示重複數為1的實驗結果，同理首位數字2表示重複數為2的實驗結果，後面2位數字則為AB各水準組合的Treatment。
當然整理為特定格式數據表最簡單方法就是以手工方式整理為表1的格式，但相當費力且容易出錯，因此合理的方式是利用Excel的強項『樞紐分析表』來製作表1特定格式，下文將以樞紐分析表的新手角度來製作表1的特定格式數據表。


3. 樞紐分析表的第1次嘗試
1) 建立樞紐分析表
實驗計劃書與實驗結果資料放置在Excel A1:F13範圍，先將滑鼠移到數據區範圍內的任意儲存格上，然後自Excel從功能表[插入]點選[樞紐分析表]，出現『建立樞紐分析表』對話框如圖3，其中『選取表格或範圍』Excel已經自動填入數據表的可能範圍，確認數據表範圍無誤後，指定放置樞紐分析表的位置到H1儲存格，然後按[確定]按鈕，出現[樞紐分析表欄位]的對話框


圖3：建立樞紐分析表對話框
2) 用滑鼠拖曳A、B、Y與replicate四個欄位如圖4


圖4：拖曳欄位以建立樞紐分析表
3) 將『加總-Y』改為『平均值-Y』
然後點紅框中的[加總-Y]旁倒三角形，選取[值欄位設定]，再點選[平均值]後[確定]，此時儲存格H1位置的內容如下圖圖5


圖5：樞紐分析表
Excel將此種樞紐分析表報表版面配置的形式稱為『以壓縮模式顯示』，並不是表1的形式，另外也多了小記列


4. 樞紐分析表的修改
將滑鼠移到樞紐分析表範圍(H1:L9)任意位置，Excel功能表就會出現[樞紐分析表工具]
1) 點選其下的[設計]，在[版面配置]下點選[小計]，選取[不要顯示小計]
2) 同樣[版面配置]下點選[報表版面配置]選取[以列表方式顯示]
圖5的樞紐分析表報表即變為下面圖6的內容，這是表1的形式。

圖6：以列表方式無小計的樞紐分析表
圖中紅框是用來表示，當要進行有重複二因子實驗數據分析時，如圖1『雙因子變異數分析-重複試驗』對話框中『輸入範圍』的區域，本例就是I2:L6，『每一樣本的列數( R)』本例填入2，如此就可以得到ANOVA表了