Excel軟件在農業試驗統計卡平方檢驗中的應用

徐崢嶸，徐瓊華

（玉溪農業職業技術學院，云南 玉溪 653106）

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

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

1 適合性檢驗

1.1 原理與資料

1.1.1 工作平臺 微軟公司開發的Microsoft Office Excel 2003軟件。

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

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

1.2 方法與步驟

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

圖1 試驗資料及計算

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

圖2 適合性檢驗的結果

1.2.3 與傳統方法檢驗結果的對比 將用Excel 2003進行適合性檢驗的結果和使用計算器進行檢驗的結果[14]進行對比，最后的推斷和結論一致，計算得到的卡平方值略有不同。查看計算過程發現，這是由于計算過程中四舍五入而造成的。Excel 2003在整個計算過程中，保留的小數位數較多，忽略的數值較少，累計舍入誤差也小，所得結果更加準確。

2 獨立性檢驗

2.1 原理與資料

2.1.1 工作平臺 同適合性檢驗。

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

需要說明的是，和適合性檢驗不同，獨立性檢驗在計算理論次數時沒有現成的理論或學說可利用，只能在兩因子相互獨立的假設下進行計算。

2.1.3 資 料[14]調查某蘋果不同樹齡各類枝組坐果數如表1所示，試檢驗坐果能力是否與枝組大小相關。

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

2.2 方法與步驟

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

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

圖4 理論次數

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

圖5 卡平方值的計算

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

圖6 獨立性檢驗的結果

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

3 討論

以上兩個例子的分析過程和教材盡可能保持了一致，可以讓初學者學習時更加容易理解；在計算過程中，許多數值的計算都是多余的，這是因為此工作表要編制成模板，若不加入這些計算，會遺漏當自由度為1時，卡平方值需要進行連續性矯正這種情況；錄入函數和公式時，必須使用半角狀態的英文輸入法，否則會因Excel無法識別而顯示錯誤；將獨立性檢驗的試驗數據和檢驗步驟分列在兩個工作表，是為刪除或插入行/列而不影響檢驗提供方便。

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

以上模板適用于適合性檢驗和2×2列聯表、2×c列聯表和r×c列聯表資料的獨立性檢驗，但能否適用于卡平方的其他檢驗還需進一步討論。是否可以編制其他更方便、更快捷的Excel模板來進行卡平方檢驗也還需要進一步研究。總之，Excel軟件是一個功能強大的數據處理分析軟件，利用好它，可以為農業工作者的數據分析提供很大的方便。

[1] 曾愛平，徐曉薇，劉洪見，等.農業統計中利用EXCEL進行方差分析[J].農業網絡信息，2006，（12）：118-119，121.

[2] 王文中.Excel在統計分析中的應用 [M].北京：中國鐵道出版社，2003.

[3] 盛 潔，胡建華.Excel軟件的統計功能在卡方檢驗中的應用[J].醫學信息，2008，21（1）：28-31.

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

[5] 馬俊青，張 靜，周 琳.EXCEL在農林試驗多因素統計分析中的應用[J].湖北農業科學，2009，48（7）：1754-1756.

[6] 楊向東.利用Excel進行單因素隨機區組產比試驗結果的統計分析[J].農業網絡信息，2009，（5）：131-133.

[7] 霍世清，張 靜，馮 崗.EXCEL在裂區試驗統計分析中的應用[J].中國農學通報，2011，27（30）：159-163.

[8] 韓學博.用EXCEL進行生物試驗的統計分析 [J].現代農業科技，2006，（6）：127-128.

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

[10] 袁 進，趙樹進.EXCEL統計函數在醫藥資料設計X 2檢驗中的應用[J].中國藥房，2006，17（20）：1595-1596.

[11] 蔡海芳，余詠吟.應用EXCEL做醫學統計 檢驗分析[J].職業與健康，2006，22（6）：456-457.

[12] 崔承鑫.在卡平方測驗中應用EXCEL統計函數 [J].農業與技術，2007，27（4）：169-170.

[13] 明道緒.田間試驗與統計分析（第二版）[M].北京：科學出版社，2008.

[14] 王寶山.田間試驗與統計方法 [M].北京：中國農業出版社，2002.