應用Excel進行方差分析和多重比較

武兆云

摘要方差分析和多重比較是數據處理的常用數理統計方法之一。應用Excel進行方差分析易于操作，而多重比較比較困難。實際上，只需掌握若干個函數就可以多重比較。介紹在Microsoft Excel 2007 中利用“數據分析”進行方差分析以及利用函數進行多重比較。

關鍵詞方差分析；多重比較；函數；Excel 2007

中圖分類號S11文獻標識碼A文章編號0517-6611（2014）14-04172-03

Analysis of Variance and Multiple Comparisons by Excel

WU Zhaoyun（Tobacco College， Henan Agricultural University， Zhengzhou， Henan 450002）

Abstract Analysis of variance and multiple comparisons is one of common used mathstatistical methods to process data. Analysis of variance by Excel is easy to operate. However， multiple comparisons are tough to operate. Multiple comparisons have been done if we can grasp a number of functions. It was introduced that how to use “data analysis” to analysis of variance and use several functions to multiple comparisons by Microsoft Excel 2007.

Key words Variance analysis； Multiple comparisons； Functions； Excel 2007

現在有多種專業性統計軟件用于數據統計分析，例如SAS、SPSS、MATLAB、Stata、SPLUS等。這些軟件功能強大，使用較復雜，需要長時間的學習才能掌握其使用方法。Microsoft Excel是Microsoft為使用Windows和蘋果Mac OS操作系統的電腦編寫的一款電子表格軟件。直觀的界面、出色的計算功能和圖表工具操作簡便，再加上成功的市場營銷，使之成為最流行的個人計算機數據處理軟件。利用Excel軟件中圖表功能和分析工具“宏”可進行描述統計、平均數假設測驗、方差分析、直線回歸與相關分析等。Microsoft Excel 經過簡單操作后就可以輸出方差分析的結果，但不能直接給出多重比較的結果，而多重比較的結果往往是關注的重點。只需掌握若干個與之相關的函數就可輕松實現多重比較。

1 方差分析

1.1數據輸入該文的數據源自蓋鈞鎰主編的《試驗統計方法》[1]例6.1，見圖1的A1：E5 區域所示。數據分析軟件為Microsoft Excel 2007。

圖1方差分析所用到的原始數據1.2利用分析工具“宏”進行方差分析首先，查看菜單欄的“數據”選項卡是否有“數據分析”項。若無，則需要加載宏。按以下步驟安裝這個加載宏：點擊“Office按鈕（）”——“Excel選項”，顯示“Excel選項”對話框，單擊“加載項”選項卡；在對話框底部，從“管理”下拉菜單中選擇“Excel加載項”命令，單擊“轉到”按鈕；Excel顯示“加載宏”對話框；在“加載宏”對話框中，勾選“分析工具庫”復選框，如圖2所示；單擊“確定”，關閉“加載宏”對話框。

圖2安裝分析工具庫之后，打開“數據”→打開“數據分析”→選擇“方差分析：單因素方差分析”→單擊“確定”。在“輸入區域”中填寫“$B$2：$E$5”（即為圖1的數據部分）。“分組方式”：選擇“行”。“α：0.05”為顯著水平。在“輸出區域”中填寫“$A$7”，如圖3所示。

圖3方差分析所填寫的信息方差分析結果見圖4。結果表明，F>F crit，可得出藥劑間處理有顯著差異。因此，應進一步對各藥劑處理平均數間進行多重比較。

圖4方差分析結果2 多重比較

多重比較有多種方法。該研究介紹以下常用的3種，有最小顯著差數法 （LSD法）、StudentNewmanKeuls檢驗和Duncan 檢驗。

2.1最小顯著差數法

2.1.1方法簡介。在處理間的F測驗為顯著的前提下，首先計算出顯著水平為α的最小顯著差數LSDα，再計算任何2個平均數的差數（yi-

2.2.1方法簡介。StudentNewmanKeuls檢驗，也被稱為q檢驗，一些統計軟件被簡稱為SNK或SNK。該方法是將一組k個平均數由大到小排列后，首先計算出2個處理平均數間秩次距為p、顯著水平為α的最小顯著極差值LSRα；再計算其差數，如其差值≥LSRα，即為在α水平上差異顯著；反之，則為在水平上差異不顯著。

2.2.2相關函數。HLOOKUP（lookup_value，table_array，row_index_num，range_lookup）。在表格或數值數組的首行查找指定的數值，在表格或數組中指定行的同一列中返回一個數值。

lookup_value為查詢值。數據表格范圍內第一行要查找單元格的值，可以是數值、引用地址或文本字符串。

table_array為數據查詢范圍，指數據表格的范圍或范圍名稱。

row_index_num為返回值的行序號，是一個數字，代表所要返回的是查詢表中的第幾行，即查詢表中待返回的匹配值的行序號。當行序號為1時，返回查詢表第一行的數值；當行序號為2時，返回第2行的數值，以此類推。

range_lookup為邏輯值。用來指定是要完全符合或部分符合，默認值為TRUE代表部分符合即可。當該參數值為FALSE時，會查找完全符合的值。

檢驗多重比較結果3討論

方差分析[2]的基本思想是將總變異剖分為各個變異來源的相應部分（處理效應和試驗誤差），從而發現各變異原因在總變異中相對重要程度[1，3]。若干個處理因素對觀測指標是否有顯著的影響，那么需要通過方差分析進行判斷。因此，方差分析在生物統計學具有重要地位。對于某些試驗，不僅要了解一組處理間總體上有無實質性差異，而且要掌握哪些處理間存在真實差異，則應進一步做處理平均數間的比較（即多重比較）。LSD檢驗的臨界值與處理間的秩次距無關，StudentNewmanKeuls檢驗和Duncan檢驗的臨界值隨處理間的秩次距增大而增大。當秩次距p=2時，LSD法、StudentNewmanKeuls法和Duncan法的顯著尺度是相同的。當秩次距p≥3時，3種檢驗的顯著尺度不同，LSD法最小，Duncan法次之，StudentNewmanKeuls法最大。這3種檢驗方法的檢驗尺度關系為LSD≤Duncan≤StudentNewmanKeuls[3]。選用何種比較標準可參考以下原則，即試驗事先確定比較的標準，凡與對照相比，或與預定要比較的對象相比，一般可選用LSD；根據否定一個正確的H0和接受一個不正確的H0的相對重要性來決定。對于試驗結論事關重大或有嚴格要求的，宜用StudentNewmanKeuls檢驗[1]。

應用Microsoft Excel就可以得到方差分析的結果，但不能直接得到多重比較的結果，Microsoft Excel具有強大的函數功能，只需掌握若干個與之相關的函數就可輕松實現多重比較。在多重比較中，應掌握3個重要的函數：對于TINV函數，返回作為概率和自由度函數的學生t 分布的t 值；對于IF函數，根據對指定的條件計算結果為TRUE或FALSE，返回不同的結果；對于HLOOKUP函數，在表格或數值數組的首行查找指定的數值，并在表格或數組中指定行的同一列中返回一個數值。

參考文獻

[1] 蓋鈞鎰. 試驗統計方法[M]. 北京： 中國農業出版社， 2000.

[2] GLOVER T，MITCHELL K.An introduction to biostatistics [M].Illinois： Waveland Press， 2001.

[3] 李春喜， 姜麗娜， 邵云， 等.生物統計學[M].3版. 北京：科學出版社， 2005.