應(yīng)用Excel進(jìn)行方差分析和多重比較

武兆云

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

關(guān)鍵詞方差分析；多重比較；函數(shù)；Excel 2007

中圖分類(lèi)號(hào)S11文獻(xiàn)標(biāo)識(shí)碼A文章編號(hào)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

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

1 方差分析

1.1數(shù)據(jù)輸入該文的數(shù)據(jù)源自蓋鈞鎰主編的《試驗(yàn)統(tǒng)計(jì)方法》[1]例6.1，見(jiàn)圖1的A1：E5 區(qū)域所示。數(shù)據(jù)分析軟件為Microsoft Excel 2007。

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

圖2安裝分析工具庫(kù)之后，打開(kāi)“數(shù)據(jù)”→打開(kāi)“數(shù)據(jù)分析”→選擇“方差分析：?jiǎn)我蛩胤讲罘治觥薄鷨螕簟按_定”。在“輸入?yún)^(qū)域”中填寫(xiě)“$B$2：$E$5”（即為圖1的數(shù)據(jù)部分）。“分組方式”：選擇“行”。“α：0.05”為顯著水平。在“輸出區(qū)域”中填寫(xiě)“$A$7”，如圖3所示。

圖3方差分析所填寫(xiě)的信息方差分析結(jié)果見(jiàn)圖4。結(jié)果表明，F(xiàn)>F crit，可得出藥劑間處理有顯著差異。因此，應(yīng)進(jìn)一步對(duì)各藥劑處理平均數(shù)間進(jìn)行多重比較。

圖4方差分析結(jié)果2 多重比較

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

2.1最小顯著差數(shù)法

2.1.1方法簡(jiǎn)介。在處理間的F測(cè)驗(yàn)為顯著的前提下，首先計(jì)算出顯著水平為α的最小顯著差數(shù)LSDα，再計(jì)算任何2個(gè)平均數(shù)的差數(shù)（yi-

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

2.2.2相關(guān)函數(shù)。HLOOKUP（lookup_value，table_array，row_index_num，range_lookup）。在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，在表格或數(shù)組中指定行的同一列中返回一個(gè)數(shù)值。

lookup_value為查詢(xún)值。數(shù)據(jù)表格范圍內(nèi)第一行要查找單元格的值，可以是數(shù)值、引用地址或文本字符串。

table_array為數(shù)據(jù)查詢(xún)范圍，指數(shù)據(jù)表格的范圍或范圍名稱(chēng)。

row_index_num為返回值的行序號(hào)，是一個(gè)數(shù)字，代表所要返回的是查詢(xún)表中的第幾行，即查詢(xún)表中待返回的匹配值的行序號(hào)。當(dāng)行序號(hào)為1時(shí)，返回查詢(xún)表第一行的數(shù)值；當(dāng)行序號(hào)為2時(shí)，返回第2行的數(shù)值，以此類(lèi)推。

range_lookup為邏輯值。用來(lái)指定是要完全符合或部分符合，默認(rèn)值為T(mén)RUE代表部分符合即可。當(dāng)該參數(shù)值為FALSE時(shí)，會(huì)查找完全符合的值。

檢驗(yàn)多重比較結(jié)果3討論

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

應(yīng)用Microsoft Excel就可以得到方差分析的結(jié)果，但不能直接得到多重比較的結(jié)果，Microsoft Excel具有強(qiáng)大的函數(shù)功能，只需掌握若干個(gè)與之相關(guān)的函數(shù)就可輕松實(shí)現(xiàn)多重比較。在多重比較中，應(yīng)掌握3個(gè)重要的函數(shù)：對(duì)于TINV函數(shù)，返回作為概率和自由度函數(shù)的學(xué)生t 分布的t 值；對(duì)于IF函數(shù)，根據(jù)對(duì)指定的條件計(jì)算結(jié)果為T(mén)RUE或FALSE，返回不同的結(jié)果；對(duì)于HLOOKUP函數(shù)，在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并在表格或數(shù)組中指定行的同一列中返回一個(gè)數(shù)值。

參考文獻(xiàn)

[1] 蓋鈞鎰. 試驗(yàn)統(tǒng)計(jì)方法[M]. 北京： 中國(guó)農(nóng)業(yè)出版社， 2000.

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

[3] 李春喜， 姜麗娜， 邵云， 等.生物統(tǒng)計(jì)學(xué)[M].3版. 北京：科學(xué)出版社， 2005.