Excel軟件在農(nóng)業(yè)試驗(yàn)統(tǒng)計(jì)卡平方檢驗(yàn)中的應(yīng)用

徐崢嶸，徐瓊?cè)A

（玉溪農(nóng)業(yè)職業(yè)技術(shù)學(xué)院，云南 玉溪 653106）

農(nóng)業(yè)試驗(yàn)中的試驗(yàn)資料，必須經(jīng)過統(tǒng)計(jì)分析才能得到科學(xué)、可靠的結(jié)論。試驗(yàn)資料的統(tǒng)計(jì)分析方法很多，如t檢驗(yàn)、方差分析、卡平方檢驗(yàn)等。其中，卡平方檢驗(yàn)包括適合性檢驗(yàn)和獨(dú)立性檢驗(yàn)，它被用來進(jìn)行由質(zhì)量性狀利用統(tǒng)計(jì)次數(shù)法得到次數(shù)資料的統(tǒng)計(jì)分析。農(nóng)業(yè)科研工作者以往進(jìn)行卡平方檢驗(yàn)都依賴于計(jì)算器，不僅工作效率低，而且錯(cuò)誤率也比較高[1]。近年來，由于計(jì)算機(jī)技術(shù)的飛速發(fā)展，出現(xiàn)了很多優(yōu)秀的統(tǒng)計(jì)分析軟件，如SPSS、SAS、Stata、BMDP、Minitab、Statistica 等等，但它們的價(jià)格都很昂貴，普及率低，而且艱深難懂[2]。微軟公司提供的Excel軟件不但價(jià)格便宜，而且還具有易學(xué)、易用、易懂的特性[3]。

Excel軟件的統(tǒng)計(jì)分析功能雖比不上專業(yè)的統(tǒng)計(jì)軟件，但它提供了“數(shù)據(jù)分析”宏工具，輔之以強(qiáng)大的函數(shù)和公式編輯、繪圖、數(shù)據(jù)庫(kù)等功能，用來進(jìn)行農(nóng)業(yè)試驗(yàn)的統(tǒng)計(jì)分析已經(jīng)足夠。在運(yùn)用Excel軟件進(jìn)行農(nóng)業(yè)試驗(yàn)資料的統(tǒng)計(jì)分析方面，許多人[1，4-8]研究了統(tǒng)計(jì)描述、顯著性檢驗(yàn)、方差分析、相關(guān)與回歸分析的運(yùn)用。運(yùn)用Excel軟件進(jìn)行卡平方檢驗(yàn)的研究多出現(xiàn)在醫(yī)學(xué)和藥學(xué)方面[3，9-11]，這些操作在很多方面并不適用于農(nóng)業(yè)試驗(yàn)的統(tǒng)計(jì)分析。崔承鑫[12]雖介紹了用Excel軟件進(jìn)行農(nóng)業(yè)資料卡平方檢驗(yàn)的獨(dú)立性檢驗(yàn)，但沒有介紹如何進(jìn)行適合性檢驗(yàn)，也沒有提供當(dāng)卡平方值需要進(jìn)行連續(xù)性矯正（自由度為1）時(shí)的解決辦法。文章通過兩個(gè)實(shí)例，配合操作圖，逐一說明了如何用Excel軟件來實(shí)現(xiàn)卡平方檢驗(yàn)。

1 適合性檢驗(yàn)

1.1 原理與資料

1.1.1 工作平臺(tái) 微軟公司開發(fā)的Microsoft Office Excel 2003軟件。

1.1.2 檢驗(yàn)的原理[13]適合性檢驗(yàn)用來判斷實(shí)際觀察的屬性類別分配是否符合已知屬性類別分配理論或?qū)W說，其無效假設(shè)為H0：實(shí)際觀察的屬性類別分配符合已知屬性類別分配的理論或?qū)W說，備擇假設(shè)為HA：實(shí)際觀察的屬性類別分配不符合已知屬性類別分配的理論或?qū)W說，自由度df為屬性類別數(shù)k-1。當(dāng)df等于1時(shí)，利用公式（1）計(jì)算經(jīng)過連續(xù)性矯正的卡平方值；當(dāng)df不等于1時(shí)，利用公式（2）計(jì)算卡平方值。計(jì)算出來的卡平方值和卡平方的臨界值進(jìn)行比較，若小于臨界值，則接受H0，即實(shí)際觀察的屬性類別分配符合已知屬性類別分配的理論或?qū)W說；若大于或等于臨界值，則否定H0，即實(shí)際觀察的屬性類別分配不符合已知屬性類別分配的理論或?qū)W說。其中，理論次數(shù)是按已知屬性類別分配理論或?qū)W說來進(jìn)行計(jì)算的。

1.1.3 資 料[14]水稻稃尖色澤的有無和籽粒糯性各受一對(duì)等位基因控制?，F(xiàn)有一水稻遺傳試驗(yàn)，以稃尖有色非糯品種與稃尖無色糯性品種雜交，其F2代的表現(xiàn)型為有色非糯491株、有色糯性76株、無色非糯90株、無色糯性86株。試檢驗(yàn)實(shí)際結(jié)果是否符合 9∶3∶3∶1 的理論比例。

1.2 方法與步驟

1.2.1 建立工作表 新建一個(gè)Excel 2003工作簿，命名為“卡平方檢驗(yàn)”并保存。在“Sheet1”工作表中的A2∶F2單元格中分別錄入“表現(xiàn)型”、“觀察株數(shù)O”、“理論比例”、“理論株數(shù) E”、“（O-E）2/E”、“（0.5）2/E”，然后在 A3∶C6單元格中錄入相應(yīng)的試驗(yàn)資料。在A7單元格中錄入“Σ”，在B7單元格中錄入公式“=SUM（B3∶B6）”，計(jì)算總的觀察株數(shù)，然后在C7單元格利用自動(dòng)填充功能（選中B7單元格，然后將光標(biāo)移至單元格右下角，使之變成實(shí)心的“十”字后按下鼠標(biāo)左鍵，拖動(dòng)至C7單元格后放開），計(jì)算出理論比例的總和。在D3單元格中錄入公式“=B7*C3C7”，然后根據(jù)D3單元格自動(dòng)填充D4:D6單元格。在E3單元格中錄入公式“=（B3-D3）/D3”，然后根據(jù)E3單元格自動(dòng)填充E4∶E6單元格。在F3單元格中錄入公式“（ABS（B3-D3）-0.5）/D3”，然后根據(jù)F3單元格自動(dòng)填充F4∶F6單元格。完成以上步驟后，再根據(jù)B7單元格自動(dòng)填充D7∶F7單元格，結(jié)果如圖1所示。

圖1 試驗(yàn)資料及計(jì)算

1.2.2 進(jìn)行適合性檢驗(yàn) 根據(jù)卡平方檢驗(yàn)的步驟[14]，在 A9:A17 單元格中分別錄入“H0:”、“HA:”、“α”（顯著水平）、“k”、“df”、“卡平方”、“臨界值”、“推斷”、“結(jié)論”；在B9單元格中錄入“實(shí)際結(jié)果符合9∶3∶3∶1 的理論比例”，在 B10 單元格中錄入“實(shí)際結(jié)果不符合 9∶3∶3∶1 的理論比例”，在 B11、B12 單元格中分別錄入“0.01、4”。在B13單元格中錄入公式“=B12-1”來求出自由度；在B14單元格中錄入公式“=IF（B13=1，F(xiàn)7，E7）”來顯示卡平方值；在 B15 單元格中錄入公式“=CHIINV（B11，B13）”，求出特定概率和自由度下卡平方的臨界值；在B16單元格中錄入公式“=IF（B14

圖2 適合性檢驗(yàn)的結(jié)果

1.2.3 與傳統(tǒng)方法檢驗(yàn)結(jié)果的對(duì)比 將用Excel 2003進(jìn)行適合性檢驗(yàn)的結(jié)果和使用計(jì)算器進(jìn)行檢驗(yàn)的結(jié)果[14]進(jìn)行對(duì)比，最后的推斷和結(jié)論一致，計(jì)算得到的卡平方值略有不同。查看計(jì)算過程發(fā)現(xiàn)，這是由于計(jì)算過程中四舍五入而造成的。Excel 2003在整個(gè)計(jì)算過程中，保留的小數(shù)位數(shù)較多，忽略的數(shù)值較少，累計(jì)舍入誤差也小，所得結(jié)果更加準(zhǔn)確。

2 獨(dú)立性檢驗(yàn)

2.1 原理與資料

2.1.1 工作平臺(tái) 同適合性檢驗(yàn)。

2.1.2 檢驗(yàn)的原理[13]獨(dú)立性檢驗(yàn)用來判斷兩類因子是相互獨(dú)立還是彼此相關(guān)，其無效假設(shè)為H0：兩類因子是相互獨(dú)立的，備擇假設(shè)為HA：兩類因子是彼此相關(guān)的，自由度df為（行屬性類別數(shù)r-1）×（列屬性類別數(shù)c-1）。計(jì)算公式同適合性檢驗(yàn)。計(jì)算出來的卡平方值和卡平方的臨界值進(jìn)行比較，若小于臨界值，則接受H0，即兩類因子是相互獨(dú)立；若大于等于臨界值，則否定H0，即兩類因子是彼此相關(guān)的。

需要說明的是，和適合性檢驗(yàn)不同，獨(dú)立性檢驗(yàn)在計(jì)算理論次數(shù)時(shí)沒有現(xiàn)成的理論或?qū)W說可利用，只能在兩因子相互獨(dú)立的假設(shè)下進(jìn)行計(jì)算。

2.1.3 資 料[14]調(diào)查某蘋果不同樹齡各類枝組坐果數(shù)如表1所示，試檢驗(yàn)坐果能力是否與枝組大小相關(guān)。

表1 蘋果不同樹齡各類枝組坐果情況 （個(gè)）

2.2 方法與步驟

2.2.1 建立工作表 （1）試驗(yàn)數(shù)據(jù)的錄入。打開“卡平方檢驗(yàn)”工作簿，將“Sheet2”工作表命名為“獨(dú)立性檢驗(yàn)”。選中A1:E1單元格，點(diǎn)擊“合并及居中”按鈕“”，然后錄入“表1蘋果不同樹齡各類枝組坐果情況”，再在A2∶D5單元格中將表1的數(shù)字及各類數(shù)值錄入其中。在A6和E2單元格中分別錄入“列合計(jì)C”和“行合計(jì)R”，在B6單元格中錄入公式“=SUM（B3∶B5）”，計(jì)算總的大枝組坐果數(shù)，然后根據(jù)B6單元格自動(dòng)填充C6∶E6單元格（E6單元格中顯示的數(shù)值為全部觀測(cè)值的總和）；在E3單元格中錄入公式“=SUM（B3∶D3）”，計(jì)算 15 年樹齡的總坐果數(shù)，然后根據(jù)E3單元格自動(dòng)填充E4和E5單元格，結(jié)果如圖3所示。

（2）計(jì)算理論次數(shù)。復(fù)制A1∶E6單元格粘貼在A8∶E13單元格，在A8單元格中錄入“表2蘋果不同樹齡各類枝組理論坐果情況”。在B10單元格中錄入公式“=B6*E3 /E6”，然后根據(jù)B10單元格自動(dòng)填充C10和D10單元格，計(jì)算15年樹齡各類枝組的理論坐果情況；在B11單元格中錄入公式“=B6*E4/E6”，然后根據(jù)B11單元格自動(dòng)填充C11和D11單元格，計(jì)算22年樹齡各類枝組的理論坐果情況；在B12單元格中錄入公式“=B6*E5E6”，然后根據(jù)B12單元格自動(dòng)填充C12和D12單元格，計(jì)算48年樹齡各類枝組的理論坐果情況。完成以上錄入后，各行、各列的合計(jì)會(huì)自動(dòng)生成，結(jié)果如圖4所示。

圖4 理論次數(shù)

（3）計(jì)算卡平方值及經(jīng)過連續(xù)性矯正的卡平方值。（a）計(jì)算卡平方值。復(fù)制A1∶E6單元格粘貼在A15∶E20單元格，在A15單元格中錄入“表3卡平方值的計(jì)算”，刪除E16∶E20和A20∶D20單元格中的內(nèi)容，在A20單元格中錄入“∑”。根據(jù)公式“（OE）2/E”，在 B17 單元格中錄入公式“=（B3-B10）^2/B10”，然后根據(jù)B17單元格向右和向下自動(dòng)填充至D19單元格；在E20單元格中錄入公式“=SUM（B17∶D19）”，計(jì)算出卡平方值，結(jié)果如圖5所示。（b）計(jì)算經(jīng)過連續(xù)性矯正的卡平方值。復(fù)制A15∶E20單元格粘貼在A22∶E27單元格，在A22單元格中錄入“表4矯正卡平方值的計(jì)算”。根據(jù)公式“（-0.5）2/E”，在B24單元格中錄入公式“=（ABS（B3-B10）-0.5）^2/B10”，然后根據(jù) B24 單元格向右和向下自動(dòng)填充至D26單元格。完成以上錄入后，在E27單元格中會(huì)自動(dòng)生成B24∶D26的總和，計(jì)算出矯正卡平方值，結(jié)果如圖5所示。

圖5 卡平方值的計(jì)算

2.2.2 進(jìn)行獨(dú)立性檢驗(yàn) 將“Sheet3”工作表命名為“獨(dú)立性檢驗(yàn)的結(jié)果”，根據(jù)卡平方檢驗(yàn)的步驟[14]，在 A1∶A10 單元格中分別錄入“H0:”、“HA:”、“α”、“r”（行屬性類別數(shù)）、“c”（列屬性類別數(shù)）、“df”、“卡平方”、“臨界值”、“推斷”、“結(jié)論”；在 B1 單元格中錄入“坐果能力與枝組大小無關(guān)”，在B2單元格中錄入“坐果能力與枝組大小有關(guān)”，在B3、B4、B5單元格中分別錄入“0.01、3、3”，在 B6 單元格中錄入公式“=（B4-1）*（B5-1）”來求出自由度；在 B7 單元格錄入公式“=IF（B6=1，獨(dú)立性檢驗(yàn)！E27，獨(dú)立性檢驗(yàn)!E20）”，顯示出卡平方值；在B8單元格中錄入公式“=CHIINV（B3，B6）”，求出特定概率和自由度下卡平方的臨界值；在B9單元格中錄入公式“=IF（B7

圖6 獨(dú)立性檢驗(yàn)的結(jié)果

2.2.3 與使用計(jì)算器進(jìn)行檢驗(yàn)的對(duì)比 用計(jì)算器進(jìn)行檢驗(yàn)，卡平方值為21.8241，最后的推斷是否定H0，結(jié)論是坐果能力與枝組大小有關(guān)。將用Excel 2003進(jìn)行獨(dú)立性檢驗(yàn)的結(jié)果和使用計(jì)算器進(jìn)行檢驗(yàn)的結(jié)果進(jìn)行對(duì)比，最后的推斷和結(jié)論一致，計(jì)算得到的卡平方值略有不同。引起卡平方值有出入的原因，和適合性檢驗(yàn)是相同的。

3 討論

以上兩個(gè)例子的分析過程和教材盡可能保持了一致，可以讓初學(xué)者學(xué)習(xí)時(shí)更加容易理解；在計(jì)算過程中，許多數(shù)值的計(jì)算都是多余的，這是因?yàn)榇斯ぷ鞅硪幹瞥赡０澹舨患尤脒@些計(jì)算，會(huì)遺漏當(dāng)自由度為1時(shí)，卡平方值需要進(jìn)行連續(xù)性矯正這種情況；錄入函數(shù)和公式時(shí)，必須使用半角狀態(tài)的英文輸入法，否則會(huì)因Excel無法識(shí)別而顯示錯(cuò)誤；將獨(dú)立性檢驗(yàn)的試驗(yàn)數(shù)據(jù)和檢驗(yàn)步驟分列在兩個(gè)工作表，是為刪除或插入行/列而不影響檢驗(yàn)提供方便。

為方便以后的使用，可以將“卡平方檢驗(yàn)”工作簿保存為Excel模板，通過調(diào)用模板進(jìn)行卡平方檢驗(yàn)，可以大大縮短計(jì)算時(shí)間，而且結(jié)果可靠性也較計(jì)算器高。調(diào)用模板進(jìn)行卡平方檢驗(yàn)時(shí)要注意，屬性類別數(shù)若和例題存在差異，只需要在試驗(yàn)資料部分刪除或插入相應(yīng)的行/列即可——適合性檢驗(yàn)直接刪除或插入行，而獨(dú)立性檢驗(yàn)可以直接刪除或插入列，但必須對(duì)表1、2、3、4同時(shí)刪除或插入相同的行。這是因?yàn)樵谡麄€(gè)檢驗(yàn)過程中，很多單元格內(nèi)都是用函數(shù)進(jìn)行計(jì)算，Excel對(duì)函數(shù)的引用會(huì)根據(jù)操作者的操作自己進(jìn)行調(diào)整。除了試驗(yàn)資料的變更外，還需要對(duì)一些分析過程中的值進(jìn)行調(diào)整，如假設(shè)的內(nèi)容、規(guī)定的顯著水平、屬性類別數(shù)等，但絕不可變動(dòng)編輯了函數(shù)或公式的單元格，否則得到的結(jié)論會(huì)出現(xiàn)錯(cuò)誤。

以上模板適用于適合性檢驗(yàn)和2×2列聯(lián)表、2×c列聯(lián)表和r×c列聯(lián)表資料的獨(dú)立性檢驗(yàn)，但能否適用于卡平方的其他檢驗(yàn)還需進(jìn)一步討論。是否可以編制其他更方便、更快捷的Excel模板來進(jìn)行卡平方檢驗(yàn)也還需要進(jìn)一步研究?？傊珽xcel軟件是一個(gè)功能強(qiáng)大的數(shù)據(jù)處理分析軟件，利用好它，可以為農(nóng)業(yè)工作者的數(shù)據(jù)分析提供很大的方便。

[1] 曾愛平，徐曉薇，劉洪見，等.農(nóng)業(yè)統(tǒng)計(jì)中利用EXCEL進(jìn)行方差分析[J].農(nóng)業(yè)網(wǎng)絡(luò)信息，2006，（12）：118-119，121.

[2] 王文中.Excel在統(tǒng)計(jì)分析中的應(yīng)用 [M].北京：中國(guó)鐵道出版社，2003.

[3] 盛 潔，胡建華.Excel軟件的統(tǒng)計(jì)功能在卡方檢驗(yàn)中的應(yīng)用[J].醫(yī)學(xué)信息，2008，21（1）：28-31.

[4] 薛 香，梁云娟，郜慶爐.EXCEL繪制直方圖方法淺析[J].承德醫(yī)學(xué)院學(xué)報(bào)，2007，24（2）：173-175.

[5] 馬俊青，張 靜，周 琳.EXCEL在農(nóng)林試驗(yàn)多因素統(tǒng)計(jì)分析中的應(yīng)用[J].湖北農(nóng)業(yè)科學(xué)，2009，48（7）：1754-1756.

[6] 楊向東.利用Excel進(jìn)行單因素隨機(jī)區(qū)組產(chǎn)比試驗(yàn)結(jié)果的統(tǒng)計(jì)分析[J].農(nóng)業(yè)網(wǎng)絡(luò)信息，2009，（5）：131-133.

[7] 霍世清，張 靜，馮 崗.EXCEL在裂區(qū)試驗(yàn)統(tǒng)計(jì)分析中的應(yīng)用[J].中國(guó)農(nóng)學(xué)通報(bào)，2011，27（30）：159-163.

[8] 韓學(xué)博.用EXCEL進(jìn)行生物試驗(yàn)的統(tǒng)計(jì)分析 [J].現(xiàn)代農(nóng)業(yè)科技，2006，（6）：127-128.

[9] 宿懷予.利用EXCEL軟件建立四格表檢驗(yàn)自動(dòng)校正處理程序[J].中國(guó)藥房，2008，19（28）：2235-2236.

[10] 袁 進(jìn)，趙樹進(jìn).EXCEL統(tǒng)計(jì)函數(shù)在醫(yī)藥資料設(shè)計(jì)X 2檢驗(yàn)中的應(yīng)用[J].中國(guó)藥房，2006，17（20）：1595-1596.

[11] 蔡海芳，余詠吟.應(yīng)用EXCEL做醫(yī)學(xué)統(tǒng)計(jì) 檢驗(yàn)分析[J].職業(yè)與健康，2006，22（6）：456-457.

[12] 崔承鑫.在卡平方測(cè)驗(yàn)中應(yīng)用EXCEL統(tǒng)計(jì)函數(shù) [J].農(nóng)業(yè)與技術(shù)，2007，27（4）：169-170.

[13] 明道緒.田間試驗(yàn)與統(tǒng)計(jì)分析（第二版）[M].北京：科學(xué)出版社，2008.

[14] 王寶山.田間試驗(yàn)與統(tǒng)計(jì)方法 [M].北京：中國(guó)農(nóng)業(yè)出版社，2002.