多重比较在Excel中的构建及应用探讨
姜有威
方差分析是生物统计研究中常用的方法，Excel等软件及其内嵌函数为其数据计算提供了很大帮助。但是，当方差分析F检验达显著水平后，进一步对各处理间的差异性进行多重比较时，Exce l等通用软件中则无处理相关事件的专门函数，往往采用人工判别的方法，面对较多且关系复杂的数值间差异性的判读又易出错或不精准，而一些第三方插件也因诸多原因未被普遍采用。为此笔者运用Excel 2010版内嵌函数提出了解决这一问题的思路和方法。
1.构建依据
column函数和vlookup函数（1）以完全随机区组设计（三次重复）为案例模型，运用Excel中的平方和函数SUMSQ (nu mberl，number2，…)、样本均值偏差平方和函数DEVSQ(number1,number2,...)、F概率分布函数的反函数FINV(probability,deg_freedom1,deg_freedom2)等，对试验数据进行方差分析，得出F检验结果。
（2）运用查第k个最大值函数LARGE(array,k)、搜索函数VLOOKUP(lookup_value,table_array, col_index_num, range_lookup)、地址函数ADDRESS(row_num,column_num,abs_num,a1,sheet_text)、行号函数ROW()、列标函数Column(refer
ence)、引用函数INDIRECT(ref_text,[a1])、字符串联函数C ONCATENATE(text1, [text2], ...)及数组公式等，结合给定的Duncan's新复极差SSR值表，对各处理间的差异性进行识别，并直观的给出判读结果。其中，当差异未达显著或极显著水平时自动标识为相同的小写或大写字母，当达差异达显著或极显著水平时自动标识为不同的小写或大写字母。
2.构建方法
2.1 案例数据
表中 x、Ti、s分别由AVERAGE(number, number2,…)、SUM(number1,[number2],...)、STDEVA(v alue1,value2,...)函数计算所得（因排版格式需要，结合ROUND(number,num_digits)函数将小数位控
制为2位，下同）。
为便于描述，文中举例的全部附表及引用的Excel单元格地址均设定在同一个Sheet工作表中，其中单元格地址均以列标+行号的形式表示。
2.2 方差分析
方差分析是多重比较的前提，只有经方差分析F值达显著时，才有多重比较的意义。
表2计算公式及引用的Excel函数如下：
（1）矫正数：为全部小区产量之和的平方除以小区数，即，C=ROUND(POWER(G13,2)/COUNT(C 4:E12),2)
（2）处理间：
处理自由度，为处理数减1，即，dft=COUNTA(B4:B12)-1
处理平方和，为各处理产量的平方和除以重复数减去矫正数，即SSt=SUMSQ(G4:G12)/COUNT(C 4:E4)-G20
处理均方，为处理平方和除以其自由度，即MSt=C17/B17
（3）区组（重复）间：
区组自由度，为区组（重复）数减1，即dfy=COUNTA(C4:E4)-1
区组平方和，为各区组产量的平方和除以处理数减去矫正数，即SSy=SUMSQ(C13:E13)/COUNT A(B4:B12)-G20
区组均方，为区组平方和除以其自由度，即MSy=C18/B18
（4）误差计算：
误差自由度，为处理自由度乘以区组自由度，即dfe=B17*B18
误差平方和，为总变异平方和减去处理平方和再减去区组平方和，即SSe=C20-C17-C18
误差均方，为误差平方和除以其自由度，即MSe=C19/B19
（5）总变异及F值计算：
总变异自由度，为处理数乘以区组数减1，即df总=COUNTA(B4:B12)*COUNT(C4:E4)-1
总变异平方和，为全部小区产量与其平均值偏差的平方和，即SS总=DEVSQ(C4:E12)
处理和区组F值，为其均方与误差均方之比，分别为：D17/D$19、D18/D$19
（6）差异显著性比较与检验：Excel提供了不同自由度和显著水平下查F概率分布反函数值的函数FINV()，针对本试验及常用的F0.05与F0.01值的查，可以通过在F17中输入=FINV(MID(F$16, 2,4),$B17,$B$19)，然后向右向下填充至F18:G18来完成。在E17中输入=IF(OR(D$19="",D17="",C17= 0),"",IF(D$19=0,"∞/**",IF(D17/D$19>=G17,ROUND(D17/D$19,2)&"**",IF(D17/D$19>=F17,ROUND(D17/ D$19,2)&"*",ROUND(D17/D$19,2)))))，向下填充到E18，则计算出处理间和区组间的F值，并对其显著性水平用0 2个“*”自动标记。
以上例举的数据，经F测验，处理间差异达极显著，可进一步进行多重比较。（通过以下公式，可以自动完成这一判断：=IF(LEN(E17)-LEN(SUBSTITUTE(E17,"*",""))=1,"F测验，处理间差异显著，可采用
Duncan's新复极差测验法进一步进行多重比较。",IF(LEN(E17)-LEN(SUBSTITUTE(E17,"*",""))=2,"F 测验，处理间差异达极显著，可采用Duncan's新复极差测验法进一步进行多重比较。","F测验，处理间差异不显著！"))）
2.3 用Excel内嵌函数自动实现多重比较
2.3.1 平均数自动降序排列
虽然可以利用Excel中的手动排序功能完成对数据的升序或降序排列，但是，这样的方法无法实现随资料数据的更新变动而自动排序。我们可以通过LARGE(array,k)、VLOOKUP(lookup_value,tabl e_array,col_index_num,[range_lookup])等函数来完成此项任务。
（1）建立排序辅助列
实现自动排序，需通过建立辅助列来完成（为不影响排版打印效果，一般的将辅助列建立在非打印区域，本文对应的Excel表的打印区域设定在A:G列，其右侧列均为非打印区域，下同）。
在I26中输入=F4+ROW(H1)/100000)，在J26中输入=B4，选取I26:J26，向下填充至I34:J34，即将原始资料中的平均值和名称引入过来，其目的有二：一是将平均值调换到名称的左侧（因为查函数VLOOKUP()需要从首列开始查）；二是在各处理原数值上再加其行号的十万分之一ROW()/1 00000
进行微调，是为了将数值相等的平均数予以区分开，以免在用查函数VLOOKUP()返回名称时，将相等平均数的不同处理只返回为同一个名称。在K26中输入= LARGE($I$26:$I$34,ROW(H1))，向下填充至K34，K26:K34即为平均数微调后的降序排列。
（2）返回对应处理名称及资料原数值降序
在A26中输入=VLOOKUP(K26,$I$26:$J$34,2,0)，向下填充至A34，A26:A34为依平均数微调降序排列后（K26:K34）的各对应处理名称；
在B26中输入=VLOOKUP(A26,$B$4:$F$12,5,0)&"±"&VLOOKUP($A$26,$B$4:$H$12,7,FALSE)，向下填充至B34，B26:B34为依重新排列名称后（A26:A34）的各对应原始数据平均值及标准差。
经以上两个步骤，实现下表目标：
表3 借助辅助列实现数值自动降序排列
2.3.2 多重比较极差值的计算
本文以Duncan's新复极差测验法为例，计算不同检测个数间的极差值。
（1）数据引用
计算多重比较极差值，涉及资料误差自由度、平均数标准误和新复极差测验SSRα值表等数据。
误差自由度已在B19中算得，即dfe=16
平均数标准误，为误差均方除以重复数的平方根，即S⎺y=SQRT(D19/COUNT(C4:E4))，计算于单元格P25中。
新复极差测验SSR0.05、SSR0.01值表数据，分别列于I1:Y10和I11:X20区域。（只将涉及本文例举资料的自由度小于16、检验个数在9以内的数据粘贴于表中，以用于计算极差值时调用）（2）极差值计算
极差值，通常为0.05和0.01显著水平下及资料误差自由度时的不同检测个数对应的SSR值乘以平均数标准误，即R0.05=S⎺y* SSR0.05，R0.01=S⎺y* SSR0.01。首先通过VLOOKUP()函数调用SSR值表数据，在M27中输入=VLOOKUP(L27,$I$3:$Y$10,$B$19+1)，向下填充至M34。公式中L27指向检测
个数(L27:L34)，$I$3:$Y$10为绝对引用0.05显著水平下的SSR值数据区，$B$19+1为函数调用的该区域内误差自由度所在列的数据。同理，在O27中输入=VLOOKUP(L27,$I$13:$X$20,$B$19)，向下填充至O34，为0.01显著水平下不同检测个数对应的SSR值。然后在N27中输入=M27*$P$25，向下填充至N34；在P27中输入=O27*$P$25，向下填充至P34。N27:N34和P27:P34即分别为0.05和
0.01显著水平下的各不同检测个数对应的极差值R0.05、R0.01。
2.3.3 差异比较和字母标记
（1）由于在以后的公式中需引用相关单元格地址名称做标识，可以通过LEFT(string,n)、Colum n(reference)、ADDRESS(row_num,column_num,abs_num,a1,sheet_text)三个函数配合完成这一任务，在L22中输入=LEFT(ADDRESS(1,COLUMN(L1),4,1),LEN(ADDRESS(1,COLUMN(L1),4,1))-1)，向右填充至B P22（假设检测的各数值间差异均达极显著水平，需将公式填充至BP列），这样就将对应的列标名称字母提取出来以便于公式引用。
（2）在Q26中输入=VLOOKUP(A26,$B$4:$F$12,5,0)，向下填充至Q34，将作为显著性比较的数值（平均值）引用到该列以便于调用。
（3）设定标记字母
用小写字母标记差异显著性，在Q24中输入:
=LOWER(LEFT(ADDRESS(1,INT(COLUMN(A1)/3+1),4,1),LEN(ADDRESS(1,INT(COLUMN(A1)/3+1),4,1))-1))，向右填充至AP24；
用大写字母标记差异极显著性，在AT24中输入:
=LEFT(ADDRESS(1,INT(COLUMN(A1)/3+1),4,1),LEN(ADDRESS(1,INT(COLUMN(A1)/3+1),4,1))-1)，向右填充至BP24。
（4）定位字符坐标
为便于公式判读，需对字母区域内出现的首个空格和非空格进行定位。
在R25中输入数组公式{=MAX((R26:R34<>"")*(ROW(R26:R34)))+1}，向右填充至BP25，返回该列指定行范围内第一个空格所在的自然行数。
在R35中输入数组公式{=MIN(IF(R26:R34<>"",ROW(R26:R34),""))}，向右填充至BP35，返回该列指定范围行内第一个非空单元格所在的自然行数。
（5）不同秩次距数值间差异性判读
第一步，在R列和AU列中，对第一个数值（最大平均值）向下依次比较，若二者之差＜R0.05均标记为小写字母a（差异不显著），否则显示为空；同理，若二者之差＜R0.01均标记为大写字母A（差异未达极显著），否则显示为空。
在R26中输入：
=IF($Q26="","",IF(OR($Q26=$Q$26,$Q$26-$Q26<$N26),R$24,""))，向下填充至R34；
在AU26中输入：
=IF($Q26="","",IF(OR($Q26=$Q$26,$Q$26-$Q26<$P26),AU$24,""))，向下填充至AU34。
第二步，在S列和AV列中，对上一步判读出的差异≥相应R值的数值（向下的第一个空单元格对应的平均值），进行向上比较，若二者之差＜R0.05标记为小写字母b，否则显示为空；同理，若二者之差＜R0.01标记为大写字母B，否则显示为空。
在S26中输入：
=IF(OR($Q26="",INDIRECT($Q$22&R$25)="",R$25=1),"",IF($Q26=INDIRECT($Q$22&R$25),S$24,IF(A ND(ROW(S26)<=ROW(INDIRECT(S$22&R$25)),ABS($Q26-INDIRECT($Q$22&R$25))<VLOOKUP(
ABS(ROW (R26)-ROW(INDIRECT(R$22&R$25)))+1,$L$27:$P$34,3)),S$24,"")))，向下填充至S34；
在AV26中输入：
=IF(OR($Q26="",INDIRECT($Q$22&AU$25)="",AU$25=1),"",IF($Q26=INDIRECT($Q$22&AU$25),AV $24,IF(AND(ROW(AV26)<=ROW(INDIRECT(AV$22&AU$25)),ABS($Q26-INDIRECT($Q$22&AU$25))<VLOOK UP(ABS(ROW(AU26)-ROW(INDIRECT(AU$22&AU$25)))+1,$L$27:$P$34,5)),AV$24,"")))，向下填充至AV3 4。
第三步，在T列和AW列中，对上一步判读出的最后一个＜R值的数值（向下的最后一个非空格对应的平均值），继续向下比较，若二者之差＜R0.05仍标记为小写字母b，否则显示为空；同理，
若二者之差＜R0.01仍标记为大写字母B，否则显示为空。
在T26中输入：
=IF(OR(S$35=0,R$35=0),"",IF(OR($Q26="",INDIRECT($Q$22&R$25)="",R$25=1,$Q26=INDIRECT($Q $22&R$25),ROW(R26)-ROW(INDIRECT(S$22&R$35))<2),"",IF(AND(ROW(T26)>ROW(INDIRECT(T$22&R$2 5)),ABS($Q26-INDIRECT($Q$22&S$35))<VLOOKUP(ABS(ROW(R26)-ROW(INDIRECT(S$22&R$35)))+1,$L$2 7:$P$34,3)),T$24,"")))，向下
填充至T34。
在AW26中输入：
=IF(OR(AV$35=0,AU$35=0),"",IF(OR($Q26="",INDIRECT($Q$22&AU$25)="",AU$25=1,$Q26=INDIREC T($Q$22&AU$25),ROW(AU26)-ROW(INDIRECT(AV$22&AU$35))<2),"",IF(AND(ROW(AW26)>ROW(INDIRE CT(AW$22&AU$25)),ABS($Q26-INDIRECT($Q$22&AV$35))<VLOOKUP(ABS(ROW(AU26)-ROW(INDIRECT(A V$22&AU$35)))+1,$L$27:$P$34,5)),AW$24,"")))，向下填充至AW34。
以上公式中使用条件语句IF(logical_test,value_if_true,value_if_false)，将原始数据为空等不同状况下的结果返回为空，一是可作为下一步公式判断的依据，二是便于在合并字符串是出现重复错误等情况。
第四步，在U列和AX列中，将标注字母相同的两列中的字符合并，在U26中输入=CONCATEN ATE(S26,T26)，向下填充至U34；在AX26中输入=CONCATENATE(AV26,AW26)，向下填充至AX34。
第五步，按照以上构思，除对最大值进行了一次向下比较外，其它各数值间的差异性比较均应遵从上
述第二步、第三步的两次比较这一方法，而Excel的公式复制功能为我们提供了便利。同时选取S26:U26，向右填充至AP26，再继续向下填充至S34:AP34；同时选取AV26:AX26，向右填充至BP26，再继续向下填充至AV34:BP34。如此，所有数值间的差异显著性比较均以a、b、c、d……（或A、B、C、D……）等字母的相同或相异标识出来。
第六步，将标识出的字母汇集在一起。
在I37中输入=CONCATENATE(R26,U26,X26,AA26,AD26,AG26,AJ26,AM26,AP26)，向下填充至I45；
在J37中输入=CONCATENATE(AR26,AU26,AX26,BA26,BD26,BG26,BJ26,BM26,BP26)，向下填充至J 45。
2.3.4 汇总标识字符
通过以上步骤，我们只是在辅助区域中完成了对数值间差异性的字母自动标识，为了直观明了，需将这些标识字母进行汇总并与各处理名称一一相对应，同时，为实现标识字母的错位排列，可通过截取字符函数LEFT(string,n)及单元格格式水平靠左对齐完成：
在C26中输入=IF(LEFT(I37,1)="a",I37,IF(LEFT(I37,1)="b"," "&I37, IF(LEFT(I37,1)="c"," "&I37,……，
即当第一个字母为a时，返回原值，当第一个字符为b时，前面空1格返回原值，当第一个字符为c时，前面空2格返回原值……依次类推。大写字母标识同此。
结果见下表
3.小结与讨论
（1）多重比较在试验分析中使用十分广泛，借助Excel自动完成这一任务，不仅为试验工作者提供了便利，而且结果准确。本文以Duncan's新复极差法举例，同样适用于其它多重比较的方法。但是Excel毕竟只是一种数据计算的工具，其结果是否正确最终是基于科学的生物统计方法的。
（2）由于Excel 2010中无专门计算SSR0.05、SSR0.01值表数据的函数，笔者亦尚未到相关计算公式，一些文章中介绍的QCRIT或QINV函数及使用方法，不仅在Excel 2010中无对应函数，而且常作为第三方插件需要下载资源包或加载宏，使用起来比较麻烦且运行缓慢，故暂时只能将相关数据表粘贴引用，期待有更好的计算或引用方法。
（3）本文提出的多重比较构建思路和方法各环节步骤，全部使用Excel内嵌函数完成，并使用了较为复杂的公式组合，初次创建可能会比较费时，但是一经建立，除原始数据需手动录入（更新）外，其余步骤均可自动完成。读者亦可根据实际需要进一步扩充完善。
附：多重比较在Excel中的构建及应用探讨.xlsx

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。