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.