应用Excel进行方差分析和多重比较
安徽农业科学。Journal
ofAnhui
AgIi.Sei.2014,42(14):4172—4174,4182
责任编辑刘月娟责任校对况玲玲
应用Excel进行方差分析和多重比较
武兆云
(河南农业大学烟草学院,河南郑州450002)
摘要方差分析和多重比较是数据处理的常用数理统计方法之一。应用Excel进行方差分析易于操作,而多重比较比较困难。实际上,只需掌握若干个函数就可以多重比较。介绍在MicrosoftExcel2007中利用“数据分析”进行方差分析以及利用函数进行多重比较。关键词方差分析;多重比较;函数;Excel2007
中图分类号S11
文献标识码A
文章编号0517—661l(2014)14—04172—03
柚lrsisofVarianceand
MultipleComparisonsbyExcel
WUghso-yun(TobaccoCollege,HenanAgriculturalUniversity,Zhengzhou,Henan450002)
Al,sh'aet
AnalysisofvaFianceandmultiplecomparisonsiS
one
ofcommonusedmath.statisticalmethodstoprocessdata.AnalysisofvariancebyExceliseasytooperate.However.multiplecomparisonsare
toushtooperate.Multiplecomparisonshavebeendoneifwecallgraspanumber
offunctions.Itwasintroducedthathowtouse“dataanalysis”toanalysisofvarianceanduse
severalfunctionstomultipleconll)al'ison8bvMi.
crosofiExcel200r7.Keywords
VarianceanMyms;Multiplecomparisons;Functions;Excel
2007
现在有多种专业性统计软件用于数据统计分析,例如所示;单击“确定”,关闭“加载宏”对话框。
SAS、SPSS、MATLAB、Stata、S.PLUS等。这些软件功能强大,使用较复杂,需要长时间的学习才能掌握其使用方法。Mi.
crosoft
Excel是Microsoft为使用Windows和苹果MacOS操
作系统的电脑编写的一款电子表格软件。直观的界面、出色的计算功能和图表工具操作简便,再加上成功的市场营销,
使之成为最流行的个人计算机数据处理软件。利用Excel软
件中图表功能和分析工具“宏”可进行描述统计、平均数假设
测验、方差分析、直线回归与相关分析等。MicrosoftExcel经
过简单操作后就可以输出方差分析的结果,但不能直接给出多重比较的结果,而多重比较的结果往往是关注的重点。只需掌握若干个与之相关的函数就可轻松实现多重比较。
1
方差分析
1.1数据输入该文的数据源自盖钧镒主编的《试验统计方法》…例6.1,见图‘1的A1:E5区域所示。数据分析软件
图2蚕装分析工具厍
为Microsoft
Excel2007。
之后,打开“数据”一打开“数据分析”一选择“方差分
C
D
E
F
药剂
耸高观察值
析:单因素方差分析”一单击“确定”。在“输入区域”中填写A
18212013“¥B¥2:¥E¥5”(即为图1的数据部分)。“分组方B2024
2622式”:选择“行”。“Ot:0.05”为显著水平。在“输出区域”中填C1015
1714D
28
27
29
32
写“¥A¥7”,如图3所示。
图1方差分析所用到的原始数据
输八
辅入区域o:
¥B¥2:SESs
1.2利用分析工具“宏”进行方差分析首先,查看菜单栏分组方式:0列蛙)
的“数据”选项卡是否有“数据分析”项。若无,则需要加载
国器④行∞
宏。按以下步骤安装这个加载宏:点击“Office按钮
口标志位于第一列∞
匝圃
a毽):M0
。。。。。。。。。。_。。。。。。。一05
j
(糕)”——“Excel选项”,显示“Excel选项”对话框,单击“加
输出选项
载项”选项卡;在对话框底部,从“管理”下拉菜单中选择“Ex-@输出区域迥):。缸恻哩).L
}塑
圜|
cel加载项”命令,单击“转到”按钮;Excel显示“加载宏”对话框;在“加载宏”对话框中,勾选“分析工具库”复选框,如图2
O新工作薄哑)
作者简介武兆云(1982一),男,安徽马鞍山人,讲师,从事烟草育种方
图3方差分析所填写的信息
面的工作。
收稿日期2014-04-23
方差分析结果见图4。结果表明,F>Fcrit,可得出药剂
42卷14期武兆云应用Excel进行方差分析和多重比较
4173
间处理有显著差异。因此,应进一步对各药剂处理平均数间进行多重比较。
^
j
0
J
析表中的“组内”、“内部”或“误差”项均方即鹏。;4为药剂
每组的重复数。
gFd
7方差分析:单囤紊声基于}栌
∈
9
塑
n行112抒213行3
10
婴塑
44t
塞塑壬塑
7292
18
直差
12.666676
2鲢:Lb'90.05=[二圆LsDo.01辈
墼!
&
:芝::=垒
C
P
‘.墨.‘
等
25
23fi86667
1t缸±
1516
!
酮≥!自
1t良666667
图5
蛰±:§§§§§Z
LSD。。和LSD㈣的计算方法
玎查幽
18差爰逮19魍筒
2D组内封
22总计朔I
在相邻两列,将各处理名及其平均数按平均数降序排
望
504
98
堂
3
堑
17-B166667
!
16820.57143
兰世ME!笪i1
5.∞卜05
3.(90295
列,如图6的A28:B31区域。在紧邻一列的上两行,将各处
理名及其平均数按平均数升序排列(左小右大),如图6的C27:F27)区域。行列交叉处为各处理间(药剂间)平均数差数。C28单元格计算公式为“=IF(¥B28一C¥27>0,¥B28一C¥27,””)”,将其公式复制到C28:F31区域即可得到药剂间的平均数差数,结果见图6。
^
B
60215
图4方差分析结果
2
多重比较
多重比较有多种方法。该研究介绍以下常用的3种,有
最小显著差数法(LSD法)、Student.Newman-Keuls检验和
Duncan检验。
2.1最小显著差数法
D
BA
亘_~卫
B
23
q
D
29
6
2.1.1方法简介。在处理间的,测验为显著的前提下,首先计算出显著水平为Ot的最小显著差数LSD。,再计算任何2
C∞罢号埔“
个平均数的差数(),。一Yj),如其绝对值≥脚。,即在a水平上
差异显著;反之,则在a水平上差异不显著。
图6处理间平均数差数
根据/-sDo.仿和/-SDo川的值判断处理间差异显著性,即差数大于4.40cm为差异显著,大于6.17cm为差异极显著,同时在结果输出时加上“+*”和“术”,分别表示为差异极显著和显著。在128单元格输入计算公式“=IF(C28>=¥D
¥24,TEXT(C28,”0”)&”术}”,IF(C28>=¥B¥24,
LSD删。俘
式中,MS,为方差分析表中的“组内”、“内部”或“误差”项均
方;n为组内重复数;£。是“组内”、“内部”或“误差”的自由度a水平上的双尾t值。2.1.2相关函数简介。
2.1.2.1
TEXT(C28,”0”)&ft书”,TEXT(C28,”0”)&””))”。该公式
首先判断单元格的值是否大于D24单元格的值(即LSD。。,的值),若大于该值则将该值转化为文本格式再与“术+”连接后输出;若小于该值,则进入另一个IF函数判断。若大于B24单元格的值(即LSDo惦的值),则将该值转化为文本格式再与“*”连接后输出;若小于B24单元格的值,则将其转化为文本输出。将公式复制到其他单元格(12.9、D0、J28、J29、K28),即可得到LSD法多重比较结果,见图7。
一DBA
TINV(probability,degrees—freedom)。返回作为概
率和自由度函数的学生t分布的t值。
Probability为对应于双尾学生t分布的概率。Degrees_freedom为分布的自由度数值。
2.1.2.2
IF(109ical_test,value—if_true,value—iffalse)。根据
对指定的条件计算结果为TRUE或FALSE,返回不同的结果,可以使用IF对数值和公式执行条件检测。
Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。例如,A10=100就是一个逻辑表达式;如果单元
一
一
一
一
+
LD
—
:。
图7
一懈融
格A10中的值等于100,表达式的计算结果为TRUE;否则,
为FALSE。该参数可使用任何比较运算符。
引一别一圳一—叫副训
C
Value_if_true是logical—test为TRUE时返回的值。Val-
ue__if_true可以是其他公式。Value—if—false是lo西cal—test为
LSD法多重比较结果
若将平均数相减,再比较LSDo.∞和LSDn叭合为一步输出,计算公式则复杂,例如在N28单元格输入计算公式“=IF
FALSE时返回的值。Value—疋脚se可以是其他公式。
2.1.2.3
TEXT(value,format—text)。将数值转换为按指定
((¥B28一C¥27)>=¥D¥24,TEXT((¥B28一C¥
27),”0”)&”¥芈”,IF((¥B28一C¥27)>=¥B¥24,rIEXT((¥B28一C¥27),”0”)&”书”,IF(¥B28一C¥27
数字格式表示的文本。Value为数值,计算结果为数字值的公式,或对包含数字值的单元格的引用。Format_text是作为用引号括起的文本字符串的数字格式。
2.1.3多重比较计算方法。首先,计算I-SDo.∞和LSD00。的值,单元格B24和D27分别为LSDo.。和LSDo。。,结果见图5。
B24的计算公式为“=TINV(0.05,12)¥SQRT(2{D20/4)”,函数SQRT计算的是数值的算术平方根,其中D20为方差分
>0,¥B28一C¥27,””)))’’。再将其计算公式复制到N28:Q31区域,即可得到多重比较结果,见图8。
2
Student-Newman-Keuls检验
2.2.1方法简介。Student.Newman.Keuls检验,也被称为q
检验,一些统计软件被简称为SNK或s-N—K。该方法是将一
4174
安徽农业科学
2014年
爵写产i霹雷三F
。!垫.:堑■黛
剪
4
:望l塑垫璺墼!耋塑!§堡堑5堡璧氅!彗21:2:l!:婪:!堡垒塑墼堡塑蝥!坠一塑l堡墼!塑!墅!:!:i!:!,!!堡塑罂g坠§塑!!窭垫::121
P
B
D
q
R
s
T
日
V
’
i?Z蛆
柚
I
艇一I
柚
戤I匿j,*
礁芦9抖
辨江
胁
缸jc
艟1
图8一步法计算出多重比较结果
组k个平均数由大到小排列后,首先计算出2个处理平均数间秩次距为P、显著水平为d的最小显著极差值LSR。;再计之,则为在水平上差异不显著。
元格输入计算公式“=IF((¥B28一C¥27)>=HLOOKUP
(C¥40一¥B41+1,¥B¥33:¥D¥37,5,FALSE),
算其差数,如其差值>,LSR。,即为在口水平上差异显著;反TEXT((¥B28一C¥27),0)&II枣木”,IF((¥B28一c¥
27)>=HLOOKUP(C¥40一¥B41+1,¥B¥33:¥D¥
(2)
LSR。=吼;斩P胚
37,4,FALSE),TEXT((¥B28一C¥27),0)&”母”,IF(¥B28一C¥27>0,¥B28一C¥27,””)))”。该公式首先计算平均数差数,即B28:B31(由大到小排列)与C27:F27(由小到大排列)的差值。接着,判断差值是否大于岱如。。的值。若大于该值,则将该值转化为文本格式再与“・木”连接后输出;若小于该值,则进入另一个IF函数判断。若大于岱R.晒的值,则将该值转化为文本格式再与“术”连接后输出;若小于丛%∞的值,则将其转化为文本输出。
HLOOKUP计算公式为“HLOOKUP(C¥40一¥B41+1,¥B¥33:¥D¥37,5,FALSE)”,“C¥40一¥B41+1”计算的是药剂处理D与c的秩次距;“¥B¥33:¥D¥37”为查询的范围;“5”表示要求返回所查询范围的第5行的值(即西|R0川的值),“4”表示要求返回所查询范围的第4行的值(即Ls‰.∞的值)。“FALSE”表示完全符合。将公式复制到其他单元格(D41、FAI、CA2、D42、CA3),即可得到q法多重比较结果,见图10。
.曼
SE=力虿石
(3)
式中,P为秩次距,是所有比较的平均数按大到小顺序排列所计算出的两极差范围内所包含的平均数个数,取值范围为2
≤p≤后。
2.2.2相关函数。mOOKUP(100kup—value,table—array,lOW—
index_num,range_lookup)。在表格或数值数组的首行查找指定的数值,在表格或数组中指定行的同一列中返回—个数值。
lookup__value为查询值。数据表格范围内第一行要查找单元格的值,可以是数值、引用地址或文本字符串。
table_array为数据查询范围,指数据表格的范围或范围
名称。
row_index_num为返回值的行序号,是一个数字,代表所要返回的是查询表中的第几行,即查询表中待返回的匹配值的行序号。当行序号为I时,返回查询表第一行的数值;当行序号为2时,返回第2行的数值,以此类推。
rangeJookup为逻辑值。用来指定是要完全符合或部分符合,默认值为TRUE代表部分符合即可。当该参数值为FALSE时,会查找完全符合的值。
2.2.3多重比较计算方法。首先,计算岱%∞和船%。。的值,查g值表即可得出当DF=12时P=2、3、4的q。值。B36
~一鞋—
D
23
~
{。F
|田。静
寸
~
B.ct
:D36、B37:D37分别为王艘。舾和墩。。,,B36的计算公式为
“=B34幸SQRT(¥D¥20/4)”,将其公式复制到B36:D36区域即可得到L踞。∞,B37的计算公式为“=B35木SQRT(¥D¥20/4)”,将其公式复制到B50:D50区域即可得到丛%0I'结果见图9。
图10Student-Newman-Keuls检验多重比较结果
2.3]Dunlin检验
2.3.1方法简介。Duncan检验又称为新复极差法。该法与Student-Newman-Keuls法相似,区别在于计算LSR。时查SSR
(Shortest
significant
ranges,最短显著差数)表。
(4)
觋A
82{33f13
LB—
C
旦…+里
3
4哇.2
LSR。=SE・SSR。.,
2.3.2多重比较计算方法。首先,计算LSR。晒和LsR。。。的值,查SSR,,值表即可得出当P=2、3、4的LSR。值。1149:IM9、B50:D50分别为Ls%∞和岱R.01’B49的计算公式为“=B47木SQRT(¥D¥20/4)”,将其公式复制到1349:D49区域即可得到Ls%舾;B50的计算公式为“=B48+SQRT(¥D¥20/4)”,将其公式复制到B50:D50区域即可得到
23.084.32
3哇Iq0.05
35【q0.0136fLgR0.05371LSR0.0138、
图9
3.775.04
5.56.00
7.86
4.406.17
5.39
7.20
LSRm∞和LSRnm值
根据秩次距P、岱黾∞和岱R.。。判断处理间差异显著性,即P=2时,差数大于4.40cm为差异显著,大于6.17cm为
差异极显著;p=3时,差数大于5.39cm为差异显著;大于
7.20
岱R0川,结果见图1l。
新复极差法与q法的计算方法相似,在此不再赘述。在C54单元格输人公式“=IF((¥B28一C¥27)>=HLOOK.
UP(C¥53一¥B54+1,¥B¥46:¥D¥50,5,FALSE),TEXT((¥B28一C¥27),0)&”木木”,IF((¥B28一C¥
(下转第4182页)
cm为差异极显著;p=3时,差数大于6.00cm为差异显
著,大于7.86cm为差异极显著。同时,在结果输出时加上
“。。”和“}”,分别表示为差异极显著和显著。在CAl单
4182
安徽农业科学
2014年
3结论
『5]BIYIDLA
withUv
B
S,JOSHIM.PhotostabilityofI-IDI-IDPEfilamemsstabilized
采用人工加速紫外老化和热老化2种试验方法,测试了
HDPE和PVC2种农用薄膜在2种老化方式下拉伸性能、透
absotbem(U、值)andlightstabilizem(HALS)[J].Journalof
EngineeredFibersandFabrics,2013,8(1):61一嘶.
[6]谢孝勋,彭汉.光降解聚乙烯薄膜的研究[J].塑料科技,2008,36(12):
56—59.
气性能和透湿性能的变化规律。研究表明,热老化与紫外老化都使HDPE薄膜的拉伸强度下降,但对PVC薄膜的拉伸强度影响不同,即紫外老化使PVC拉伸强度下降,而热老化使PVC拉伸强度增强。2种老化方式都使得HDPE薄膜的透气性上升,而PVC薄膜的透气性呈现出先上升后下降的趋势。2种老化方式对HDPE薄膜的透湿量都是先下降后上升,而对PVC薄膜透湿量呈下降趋势。对于2种薄膜性能的变化规律可以看出,HDPE薄膜比PVC薄膜更易发生老化,性能下降显著,紫外老化方式比热老化方式更易引起塑料薄膜老化降解。紫外光是引发塑料薄膜产生老化的主要因素。参考文献
[1]成鼷华,刘淑萍,孙占潮,等.天津市农用地膜残留状况调查及影响因
素分析[J].农业环境与发展,2011,28(2):90—94.
[2]苗翠霞,郑慧琴.农用棚膜人工加速老化试验与大气曝露试验相关性
[7]ESPIE,SALMERONA,FONTECHAA,eta1.Plasticfilmsforagricultural
ofPlasticFilmand
applications[J].Journal1∞.
Sh,ⅪthS,2006,22(4):K5—
[8]BRUINN,PO睫OT
0fmultivariate
M.PatriceBourson.RamancarmlationsHDPEctrosco-
py:AmethodstudyingphysicalproI-IDPErtiesofpolystyrenebythe
metricsand
n砖黜
analysis[J].ChemoIntelligentLaboratorySys-
terns,2013,2013,128:77-82.
[9]杨书君,高本虎,任晓力,等.农用聚乙烯塑料老化试验方法概述[J].
橡塑资源幂U用,2006(5):19—22.
[10]WANG
ester
YZ,WANGHY,uXs,etaI.03/uVsynergisticagiJlgof—y-
Sci—
entific
polyurethane
WorldJournal。2013:1—7.
filmmodifiedbycomlxmiteUVabsorber[J].The
[11]刘再满,丁生龙,柳明珠光/生物降解聚乙烯薄膜的光降解性能[J].
应用化学,2006,8(8):875—880.
[12]徐凤玲,魏然鼠人工加速老化试验中常见问题分析[J].化学建材,
2008,24(I):20-21.
[13]BASHVANDM,R心JIlARZ,RASTEGARS.Nano
stabilizerforaIlⅫllatic
zincoxide
as
a
UV—
polyurethane
coatings[J].ProS哪in
on
Orgallic
探讨[J].合成材料老化与应用,2005,34(1):22—25.
[3]高俊刚,李书润,王东.聚乙烯/无帆纳米复合材料的抗紫外老化性能
[J].高分子材料科学与工程,20晒,21(5):152—155.
[4]沙保峰,杜美刊,周安宁,等.聚乙烯薄膜光降解过程特性研究[J].现
coalin铲,2011,71(4):362-368.[14]XUT,LIGQ,PANGSS.Effects
syntactic
ofultravioletradiation
andthermo-mcchanicalpmHDPErtlesofshaHDPEraemory
p(枷based
morphology
foam[J].Composites:Part
A,2011,42:1525—1533.
代蚴口工应用,2004。16(6):4—6.
一
・‘。+一-+一’。+・一—‘卜一—卜—‘。卜一+—+-+一+一—+一—+-+-+-+——卜—+一十-+一—卜—+-+一+-+一+一十一+一+-+一+一+一—●—斗—+一+--1--一+一+—+一+一+一+-—卜—+-+—+—+.
(上接第4174页)
较(即多重比较)。LSD检验的临界值与处理间的秩次距无关,Student.Newman—Keuls检验和Duncan检验的临界值随处理间的秩次距增大而增大。当秩次距P=2时,LSD法、Student-Newman-Keuls法和Duncan法的显著尺度是相同的。当秩次距P>13时,3种检验的显著尺度不同,LSD法最小,
Duncan法次之,Student—Newman-Keuls法最大。这3种检验
c
D
E
27)>=肌oOKUP(C¥53一¥B54+1,¥B¥46:¥D¥
50,4,FALSE),TEXT((¥B28一C¥27),0)&”半”,IF(¥B28一C¥27>0,¥B28一C¥27,””)))”。将公式复制到其他单元格(D54、E54、C55、D55、C56),即可得到新复极差法
多重比较结果.见图12.
A
46口47
SSRO.05SSRO。01LSRO.05LSI!}0。01
B
方法的检验尺度关系为LSD≤Duncan≤Student—Newman.
2308432
4
3323455
4
J333
4
Keuls¨1。选用何种比较标准可参考以下原则,即试验事先确定比较的标准,凡与对照相比,或与预定要比较的对象相比,一般可选用LSD;根据否定一个正确的R和接受一个不正确的Ho的相对重要性来决定。对于试验结论事关重大或有严格要求的,宜用Student.Newman.Keuls检验…。
应用MicrosoftExcel就可以得到方差分析的结果,但不能直接得到多重比较的结果,MicrosoftExcel具有强大的函
48
49505】
68
40
7
624’66.69
61650
图II丛氐。和岱歌。值
一~
一n
DBAc
CDA3Il’‘j‘
6’
EB2
一检量验
t2
15’’9‘’
C
口4
数功能,只需掌握若干个与之相关的函数就可轻松实现多重比较。在多重比较中,应掌握3个重要的函数:对于TINV函数,返回作为概率和自由度函数的学生t分布的t值;对于IF函数,根据对指定的条件计算结果为TRUE或FALSE,返回
3{4
图12
n珊咖检验多重比较结果
不同的结果;对于HLOOKUP函数,在表格或数值数组的首行查找指定的数值,并在表格或数组中指定行的同一列中返回一个数值。
参考文献
[1]盖钧镒.试验统计方法[M].北京:中国农业出版社,2000.
[2]GLOWERT,MITCHEH,K.AIlintroductiontobiostatisties[M].Illinois:
WavelandPress,2001.
3讨论
●
●
方差分析B1的基本思想是将总变异剖分为各个变异来源的相应部分(处理效应和试验误差),从而发现各变异原因在总变异中相对重要程度¨’31。若干个处理因素对观测指标是否有显著的影响,那么需要通过方差分析进行判断。因此,方差分析在生物统计学具有重要地位。对于某些试验,不仅要了解一组处理间总体上有无实质性差异,而且要掌握哪些处理间存在真实差异,则应进一步做处理平均数间的比
[3]李春喜,姜丽娜,邵云,等.生物统计学[M].3版.北京:科学出版社,
应用Excel进行方差分析和多重比较
作者:作者单位:刊名:英文刊名:年,卷(期):
武兆云, WU Zhao-yun
河南农业大学烟草学院,河南郑州,450002安徽农业科学
Journal of Anhui Agricultural Sciences2014(14)
本文链接:http://d.g.wanfangdata.com.cn/Periodical_ahnykx201414002.aspx