应用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.