在Excel表中进行多元方差分析.pdf
西北农业学报 2006,15(2):174179Acta A griculturae Boreali2occidentalis Sinica在Excel表中进行多元方差分析3胡想顺,刘小凤,赵惠燕,李东鸿,胡祖庆(西北农林科技大学植物保护学院,陕西杨凌 712100)摘 要:应用函数编辑器和简单的复制、粘贴功能即可在Excel表中实现较为复杂的多元方差分析,可用到的命令有求和命令“SUM”,计数器命令“COUNT”,求平均值命令“AVERAGE”,对应数据相乘的积的求和命令“SUMPRODUCT”,矩阵相乘命令“MMUL T”以及求解矩阵的行列式的值的命令“MDETERM”。并给出了一个在Excel表中进行双因素不等重复处理的多元方差分析和多重比较的例子。关键词:Microsoft Excel 2000;函数编辑器;多元方差分析;生物统计中图分类号:S11+3 文献标识码:A 文章编号:100421389(2006)0220174205Multiple Covariance Analysis in Microsoft Excel 2000HU Xiang2shun,LIU Xiao2feng,ZHAO Hui2yan,LI Dong2hong and HU Zu2qing(College of Plant Protection,Northwest A&F University,Yangling Shaanxi712100,China)Abstract:Used functions editor and simple copy,stick function could do multiple coveriance analysisin Microsoft Excel table.The order include total sum order SUM,count order COUN T,meanorder AVERAGE,and the order SUMPRODUCTthat about count the total sum of some datasproduct which some groups number corresponding multiply;the order MMUL T that used to countproduct of two matrixes;the order MDETERM that used to count the determinant value of one ma2trix.An example about two factors multiple covariance analysis and multiple comparisons which havedifferent repetition in Microsoft Excel 2000 had been given.Key words:Microsoft Excel 2000;Functions editor;Multiple coveriance analysis;Biostastics 多元方差分析是数理统计的基本方法之一,用于解决2个以上同协方差阵多元(多指标)正态总体均值向量的比较问题1。Excel是Microsoft公司推出的Office系列产品之一,是目前应用最广泛的功能强大的电子表格数据处理软件,具有方便直观,易学易用的特点2,可以进行较为复杂的生物统计分析38。Microsoft Excel 2000数据分析程序给出的双因素方差分析要求等重复,而其他专业软件要求具有较高的计算机知识,但在实际的数据处理中,往往由于种种原因使得所获得的数据不全或重复数不相等,而一般农业科技工作者又缺乏专业的计算机知识。本文以双因素不等重复处理为例就用Excel进行多元方差分析作以介绍。这种方法直观易懂,对环境要求不高,具有基本的多元统计知识的农业科技工作者均可顺利操作。1 多元方差分析常用公式符号及统计学意义以4个小麦品种(系)(表示为Ai,i=1,2,3,4,A1=Astron,A2=Amigo,A3=98210235,A4=小偃222)上2种蚜虫(表示为Bj,j=1,2,B1=麦长管蚜(Sitobion avenae),B2=禾谷缢管蚜(Rhopalosiphum padi)的生物学指标xijtk(t为重复数,t=1,2,3,r,本文中r不相等;k=1,2,3收稿日期:2005207214 修回日期:2005211202基金项目:中德两国农业部合作项目(02/03);西北农林科技大学青年专项基金(080807)。作者简介:胡想顺(1973-),男,甘肃天水人,助研,在职博士,从事农作物病虫害综合治理、昆虫生态学与抗虫育种的研究工作。E-mail:huxiangshun .3,4:xijt1=MRGR(日均体重相对增长量),xijt2=发育历期(d),xijt3=体重差(g)以及xijt4=生殖力(成虫在羽化后最初的与发育历期相等的时间内的产仔数)为例,我们进行双因素4元方差分析。基本公式及其统计学意义见表1。表1 多元方差分析常用基本公式及统计学意义Table 1The statistical meaning and function in multivariate analysis of variance公式Function统计学意义Statistical meaningXijt=(xijt1,xijt2,xijt3,xijt4)T观察值向量的转置Tij.=(Tij.1,Tij.2,Tij.3,Tij.4)T处理AiBj的观察和向量的转置Ti.=(Ti.1,Ti.2,Ti.3,Ti.4)T品种Ai的观察和向量的转置T.j.=(T.j.1,T.j.2,T.j.3,T.j.4)T蚜虫Bj的观察和向量的转置T.=(T.1,T.2,T.3,T.4)T观察总和向量的转置?Xij.=(?Xij.1,?Xij.2,?Xij.3,?Xij.4)T处理AiBj的观察均值向量的转置?Xi.=?Xi.1,?Xi.2,?Xi.3,?Xi.4)T品种均值向量的转置?X.j.=?X.j.1,?X.j.2,?X.j.3,?X.j.4)T蚜虫均值向量的转置?X.=?X.1,?X.2,?X.3,?X.4)T总均值向量的转置2 用Excel进行数据处理的步骤2.1 在Excel中进行多元方差分析常用公式符号及统计学意义Excel中进行多元方差分析常用公式符号均可在菜单“插入”命令中的“fx函数”中找到,常用的函数及其统计学意义见表2。其中“SUM、COUNT、AVERAGE”位于函数分类中的“统计”名下,其他则需从函数分类中的“全部”中寻找,也可以在公式编辑栏中直接输入。表2 在Excel中进行多元方差分析用到的命令举例及统计学意义Table 2The exemple of multivariates analysis of varianceorder and its statistical meaning in excel table命令名称Order统计学意义Statistical meaningSUM(B3:B14)计算单元格B列中从第3行到第14行区域中所有数字之和COUNT(B3:B14)统计单元格B列中从第3行到第14行区域中所有数字的个数AVERAGE(B3:B14)计算单元格B列中从第3行到第14行区域中所有数字之和SUMPRODUCT(B3:B14,D3:D14)计算单元格B列中从第3行到第14行区域中数字与单元格D列中从第3行到第14行区域中数字彼此对应的乘积的和MMUL T(B234:E234,A229:A232)计算矩阵(单元格从B列到E列第234行到第234行区域中数字)与矩阵(单元格从A列第229行到第232行区域中数字)的乘积MDETERM(B224:E227)计算的矩阵(单元格从B列到E列,第224行到第227行区域中数字)的行列式的值2.2 原始数据的录入与一级数据处理建立一个空白的Excel表,选中并双击鼠标左键启动Excel,录入原始数据,为了后面计算方便,同一因素不同水平的原始数据在表中的起始行可以根据实际情况有规律的放在Excel表中,如本例原始数据的录入的起始行号为41n(n为非负整数,品种Astron的n=0,起始行号为41;品种Amigo的n=1,起始行号为81;品种98210235的n=2,起始行号为121;品种小偃222的n=3起始行号为161,为节约篇幅,表中仅给出了2种蚜虫在Astron上的4项原始指标,其他3个品种的原始数据从略)。这样也可为一级数据处理留下位置,如图1所示。在进行2种蚜虫在Astron上取食的4个指标的一级数据处理时,用鼠标选中表中B15,再将光标移到公式编辑框中,在公式编辑框中直接输入“=SUM(B3:B14)”,计算麦长管蚜(S.ave2nae)在Astron上的MRGR(x11.1)各观察值的和(也可先选中B15,再在菜单“插入”命令中的“fx函数”中,选中函数分类中的“统计”名下的“SUM”函数,选择计算区域(B3:B14)实现之,其他函数均可按这2种方法实现计算)。同样,分别用鼠标点中表中B16,B17的位置,分别在公式编辑框中输入“=COUNT(B3:B14)”,“=AVERA GE(B3:B14)”,计算麦长管蚜(S.avenae)在Astron上的MRGR(x11.1)的观察值的重复数r和观察值的均值(图1)。计算完成后,可利用复制,粘贴功能实现麦长管蚜(S.avenae)及禾谷缢管蚜(R.padi)在Astron上其他指标的和、重复数及均值的计算:选中区域B15:B17,将鼠标置于该区域右下角,等出现实心黑十字架时按住鼠标左键,直接向右托动鼠标至5712期 胡想顺等:在Excel表中进行多元方差分析单元格区域C15:I17即可,也可选中区域B15:B17,点鼠标右键,复制,再粘贴到C15:I17实现快捷计算(即相对应用2)。选中B22,在公式编辑框中编辑“=SUM2PRODUCT(B3:B14,B3:B14)”,选中C22,编辑“=SUMPRODUCT(B3:B14,C3:C14)”,选中D22,编辑“=SUMPRODUCT(B3:B14,D3:D14)”,选中E22,编辑“=SUMPRODUCT(B3:B14,E3:E14)”;选中C23,编辑“=SUMPROD2UCT(C3:C14,C3:C14)”,选中D23,编辑“=SUMPRODUCT(C3:C14,D3:D14)”,选 中E23,编辑“=SUMPRODUCT(C3:C14,E3:E14)”;选中D24,编辑“=SUMPRODUCT(D3:D14,D3:D14)”,选中E24,编辑“=SUMPROD2UCT(D3:D14,E3:E14)”,选中E25,编辑“=SUMPRODUCT(E3:E14,E3:E14)”,可实现麦长管蚜(S.avenae)在Astron上各指标两两彼此对应元素的乘积的和 ri=1x11klx11km的计算,将这些值组成的矩阵放在(B22:E22)区域内,该矩阵是对称矩阵,所以在一级计算中仅列出主对角线以上部分数据。选中B22:E22,复制,粘贴到F22:I22即可完成对禾谷缢管蚜(R.padi)的 ri=1x11klx11km矩阵计算。图1 两种蚜虫在Astron的生物学指标原始数据及相关计算值在Excel表中的界面图Fig.1The interface of 2 aphids biological indexes on wheat variety Astron in Excel table 选中B28,在公式编辑框中编辑“=SUM2PRODUCT(B15,B15)”,选 中C28,编 辑“=SUMPRODUCT(B15,C15)”,选中D28,编辑“=SUMPRODUCT(B15,D15)”,选中E28,编辑“=SUMPRODUCT(B15,E15)”;选中C29,编辑“=SUMPRODUCT(C15,C15)”,选中D29,编辑“=SUMPRODUCT(C15,D15)”,选中E29,编辑“=SUMPRODUCT(C15,E15)”;选 中D30,编 辑“=SUMPRODUCT(D15,D15)”,选 中E30,编 辑“=SUMPRODUCT(D15,E15)”,选中E31,编辑“=SUMPRODUCT(E15,E15)”,可实现麦长管蚜(S.avenae)在As2tron上各指标的和向量间两两彼此对应元素的乘积Tij.lTij.m矩阵的计算,将这些值组成的矩阵放在(B28:E28)区域内,该矩阵是对称矩阵,所以在一级计算中仅列出主对角线以上部分数据。选中(B28:E28),复制,粘贴到(F28:I28)即可完成对禾谷缢管蚜(R.padi)在Astron上各指标的和向量间两两彼此对应元素的乘积Tij.l3Tij.m矩阵的计算。选中B34,在公式编辑框中编辑“=B15+F15”,选中C34,在公式编辑框中编辑“=C15+G15”,选中D34,在公式编辑框中编辑“=D15+H15”,选中E34,在公式编辑框中编辑“=E15+I15”,计算品种水平和向量Ti.=(Ti.1,Ti.2,Ti.3,Ti.4)T,将其放在(B34:E34)区域内,同理,应用公式命令“SUMPRODUCT”计算品种水平和向量各元素两两乘积的矩阵Ti.lTi.m,将其放在表中F(34):I(37)区域内。计算其他品种时,先以40n行为起始行录入原始数据,再直接选中15至38行,复制,粘贴到(40n+15)至(40n+38)行即可。各品种数据计算结束后,计算蚜虫水平的观察值的和向量T.j.=(T.j.1,T.j.2,T.j.3,T.j.4)T,671西 北 农 业 学 报 15卷(图2中B162:I162),及其各元素两两乘积矩阵T.j.kT.j.m(B164:I167);计算总和向量T.=(T.1,T.2,T.3,T.4)T,及其各元素两两乘积的矩阵T.kT.m(B172:E175)。将这些矩阵分别有规律的放在固定的位置。本次实验为不等重复的多元方差分析,所以abr,ar,br,r应该提前列出(A177:I184)。2.3 离差阵计算及相关公式2.3.1 总离差阵WW=l11l12l13l14l21l22l23l24l31l32l33l34l41l42l43l44其中lkm=4i=12j=1rt=1xijtkxijtm-TkTmabr(k,m=1,2,3,4;abr=85(当k=m时为lkm=lkk=4i=12j=1rt=1x2ijtk-T2kabr,下面其他公式与此相似)在B188编辑公式:B22+F22+B62+F62+B102+F102+B142+F142-B172/85,数据框显示l11的计算结果:0.430759,利用Excel的复制、粘贴功能,可以快速计算所有lij。结果见图2(选中B189,点击公式编辑框上“=”,再点击C188,依此可以得到全对称矩阵,下同)。2.3.2 品种离差阵L品种L品种=l品种11l品种12l品种13l品种14l品种21l品种22l品种23l品种24l品种31l品种32l品种33l品种34l品种41l品种42l品种43l品种44其中=l品种km=1bri4i=1Ti.kTi.m-T.kT.mabr(k,m=1,2,3,4;abr=85,br1=22,br2=22,br3=17,br4=24)在F188编辑公 式:F34/22+F74/22+F114/17+F154/24-B172/85,数据框中显示l品种11计算结果:0.003326,选中F188,复制、粘贴实现l品种km的快捷计算。结果见图2(F188:I191)。2.3.3 蚜虫离差阵L蚜虫L蚜虫=l蚜虫11l蚜虫12l蚜虫13l蚜虫14l蚜虫21l蚜虫22l蚜虫23l蚜虫24l蚜虫31l蚜虫32l蚜虫33l蚜虫44l蚜虫41l蚜虫42l蚜虫43l蚜虫44其中=l蚜虫km=1arj2j=1T.j.kT.j.m-T.kT.mabr(k,m=1,2,3,4;abr=85,ar1=48,ar2=37)在B194中编辑公式:B164/48+F164/37-B172/85,数据框中显示l蚜虫11计算结果:0.28129,选中B194,托动鼠标或复制实现l蚜虫km的快捷计算。计算结果见图2中(B194:E197)。图2Excel表中的二级数据计算结果界面表Fig.2The interface of secondly data in Excel table2.3.4 品种 蚜虫的离差阵L品种 蚜虫L品种 蚜虫=7712期 胡想顺等:在Excel表中进行多元方差分析l品种 蚜虫11l品种 蚜虫12l品种 蚜虫13l品种 蚜虫14l品种 蚜虫21l品种 蚜虫22l品种 蚜虫23l品种 蚜虫24l品种 蚜虫31l品种 蚜虫32l品种 蚜虫33l品种 蚜虫34l品种 蚜虫41l品种 蚜虫42l品种 蚜虫43l品种 蚜虫44其中l品种 蚜虫km=1rij4i=12j=1Tij.kTij.m-1br14i=1Ti.kTi.m-1ar12j=1T.j.kT.j.m+TkTmabr(k,m=1,2,3,4;abr=85;br1=22,br2=22,br3=17,br4=24;ar1=48,ar2=37;rij=121012101251212),在F194中编辑公式:“=B28/12+F28/10+B68/12+F68/10+B108/12+F108/5+B148/12+F148/12-(F34/22+F74/22+F114/17+F154/24)-(B164/48+F164/37)+B172/85”,数据框中显示l品种 蚜虫11计算结果:0.006 859,复制、粘贴实现l品种 蚜虫km的快捷计算。结果见图2中(F194:I197)。2.3.5 随机误差离差阵LeLe=le11le12le13le14le21le22le23le24le31le32le33le34le41le42le43le44其中lekm=4i=12j=1rt=1xijtkxijtm-1rij4i=12j=1Tij.kTij.m(k,m=1,2,3,4;rij=121012101251212)在B200中编辑公式:B22+F22+B62+F62+B102+F102+B142+F142-(B28/12+F28/10+B68/12+F68/10+B108/12+F108/5+B148/12+F148/12),数据框中显示le11的计算结果:0.139283,复制、粘贴实现lekm的计算。结果见图2中B200:E203。也可根据Le=W-L品种-L蚜虫-L品种 蚜虫计算之。(图2中F200:I203)。2.4 方差分析由于数据是对2种蚜虫在4个小麦品种(材料)上的生物学特性进行比较,故用固定模型检验1。所用公式如下:V品种=-(fe+f品种-p+f品种+12)lnLeLe+L品种近似服从2(pf品种).V蚜虫=-(fe+f蚜虫-p+f蚜虫+12)lnLeLe+L蚜虫近似服从2(pf蚜虫).V品种 蚜虫=-(fe+f品种 蚜虫-p+f品种 蚜虫+12)lnLeLe+L品种 蚜虫近似服从图3 在Excel表中进行多重比较过程界面表Fig.3The interface of multiple comparison in Excel table 2(pf品种 蚜虫),其中f为自由度,p为指标数,本文中为4。在Excel表 中(图3),依 次 计 算Le+L品种 3 蚜虫,Le+L品种,Le+L蚜虫阵,分 别 位 于871西 北 农 业 学 报 15卷(B206:E209),(F206:I209)和(B212:E215),再在G212编辑函数“=MDETERM(F188:I191)”计算L品种的行列式。用同样的方法计算L蚜虫,L品种 蚜虫,Le,Le+L品种 蚜虫,Le+L品种,Le+L蚜虫的行列式;将自由度即p值置于B217:B222,在C217中编辑“=-(B220+B217-(4+B217+1)/2)”计算-(fe+f品种-p+f品种+12),在D217中编辑“LN(G215/I212)”计算lnLeLe+L品种,在E217中编辑“=C217D217”可以得出V品种的值。同样,可以在E218,E219中得到V蚜虫,V品种 蚜虫的值,根据以上公式分析可知,两种蚜虫间差异显著,4个小麦品种间差异显著。2.5 多重比较以对4个小麦品种进行多重比较为例,所用到的公式如下:D2Aij=(?Xi.-?Xj.)TS-1e(?Xi.-?Xj.)FAij=br(fe-p+1)2pfeD2AijF(p,fe-p+1)图3中222行以下部分表示多重比较各步步骤,这儿需要说明的是在计算区域F234:I234中的 数 据 时,需 在F234中 先 编 辑“=MMUL T(B234:E234,A229:A232)”,再复制粘贴即可实现区域F234:I234数值的快捷计算。计算完后,将F234中公式改为“=MMUL T(B234:E234,A229:A232),复制粘贴即可实现区域F234:F239数值的快捷计算,将这一区域内的所有公式中的B:E换为 B:E,A:A换A:A,复制F235:F239,粘贴到G235:I239,即可完成该区域的计算。上文中“”为绝对引用符号,绝对引用指公式所引用的单元格是固定不变的,采用绝对引用,无论将其剪切,复制到哪儿,都只引用同一单元格2。3 讨 论本文介绍了如何应用Excel表中简单的复制、粘贴功能(相对应用,绝对引用)进行多元方差分析,应用此法,只要完成一组数据的某种计算,就可以一样对其他组数据进行快捷计算。为了说明操作方法,本文表格设计的较为繁琐,在熟练掌握后,可进一步精简,以提高工作效率。对于有VB语言基础的用户,可很容易地应用Excel提供的工具制作出更智能化的多元方差分析模板,相信其功能和易操作性不比其他统计软件逊色多少。本方法适合于具有多元统计基础和一般计算机操作知识的科研工作者使用。参考文献:1 袁志发,周静芋主编.多元统计分析 M.北京:科学出版社,2002.2 全国专业技术人员计算机应用能力考试专家委员会编写.Excel 97中文电子表格M.辽宁:辽宁人民出版社,辽宁电子出版社,2002.3 鲁建文,张 俊,田 涛,等.用Linest函数进行多元线性回归分析J.农业网络信息,2005(1):5758.4 霍志军,李菊艳,潘晓琳.Excel在农业生物统计分析中的应用J.现代化农业,2003,29(9):2830.5 郭文久.2000 Microsoft Excel方差分析的使用J.云南农业大学学报,2000,15(1):912,31.6 宫国军,徐德昌,孙野青,等.Microsoft Excel在LSR中的应用J.中国甜菜糖业,2004(2):2427.7 宫国军,刘巧红.Microsoft office Excel在LSD中的应用J.中国甜菜糖业,2004(1):4245.8 黄 剑,吴文君.利用Excel快速进行毒力测定中的致死中量计算和卡方检验J.昆虫知识,2004,41(6):594598.(上接第169页)参考文献:1 杨明欣.湿度测量处理变送器设计M.北京:电子工业出版,2003.2 滕召生,童调生.智能检测系统与数据融合M.北京:机械工业出版社,1999.3 李 娜.自动化仪表M.北京:燕山大学出版社,2004.4 闫宏伟.基于VB语言的串口通讯系统设计J.机电工程技术,2005,34(1):8588.5 赵志杰.粮食仓储库熏蒸控制系统智能网络控制器研究J.哈尔滨商业大学学报(自然科学版),2004,20(4):462464.6 李为民.我国粮食仓储业的发展历程与展望J.粮油仓储科技通讯,2005(1):24.7 陈良光.全数字测温及测湿技术在粮食仓储管理中的应用J.电气时代,2001(5):2021.8 陆 阳.Windows环境下实时测量系统的开发J.信息技术,2001(9):67.9712期 胡想顺等:在Excel表中进行多元方差分析
