利用Excel内部函数LINEST进行多元回归和多项式回归分析.doc

利用Excel内部函数LINEST进行多元回归和多项式回归分析l贝任编辑毛由丽48经验交流CAOYEYUXUMU2007年第8期总第141期革业与畜牧_厕两都画皴贼避行画画参甄画画蹄周自玮(云南省肉牛和牧草研究中心,昆明小哨650212)摘要:多元回归和多项式回归在生物统计中具有重要地位,但当自变量超过3个时,相关参数的计算十分复杂.本文通过实例详细介绍了利用Micr0s0ftExcel内部函数LINEST对多项式及多元回归分析的方法,该方法具有快速,简单,准确的特点.关键词:多元回归;多项式回归;MicrosoftExcel;LINEST函数中图分类号:$126文献标识码:B文章编号:16738403(2007)08004805研究一个依变量与多个自变量之间的关系在统计学上称为多元回归I1J,多项式回归方程可以转化为多元回归方程,即设x1=xl,x2=x1,X3-X1,X4=Xl,X5-X,Xn-X,这样就可以把任意多项式回归转化为多元回归方程来求解.多元回归和多项式分析在生物统计学上有着极其重要的地位.但多元回归与多项式回归的偏回归系数的计算比较复杂,尤其是当自变量达3个以上的回归分析相当困难.因此,多元回归与多项式回归分析需利用计算机进行统计分析,通常利用计算机进行多元回归与多项式回归分析需要编程,对一般科技工作者相对困难.邵碧雄等同介绍了利用MicrosoftExcel2000内部函数进行多元及多项式回归分析的方法,相对简单,快捷.笔者现介绍一种同样利用MicrosoftExcel内部函数LINEST进行多元回归与多项式回归分析的方法,此法仅用一个MicrosoftExcel内部函数,更为简单.1多元回归与多项式回归分析的原理在多元回归中,多元线性方程为:;=6.+6+bzx:+bmx(公式1),其中bo=yb一6一一6Ebi(公式2),b,bb分别为Y对x,x收稿日期:2006一l107基金项目:本文由云南省自然基金项目(2004C0063M)德宏狼尾草特性及栽培技术研究资助作者简介:周自玮(1966一),副研究员,硕士生导师,云南农业大学在读博士,长期从事牧草研究工作.X的偏回归系数.多元回归方程的建立,必须使(y-y)为最小,即使Q=Z(),)z_(y-b-bx2-一一bmXm)=(),一)一b(一)一bz(一)一一6m_为最小.因此,需分别对b,b,b求偏导数,并令之为0,可得到正规方程组:b,SS,.+b:SPx+6.+6.6-.s+6+6+6=56-s+6+6+6.=.s(公式3)6-.s+6+6+6,.=.s上式中,SS是变量X的离均差平方和,即SSt=一)2分别位于对角线上.sPt是自变量两变相互的乘积和,即=)()=Y.x,xi-Y.x/n(n为x.的数据个数);SP是x,Y两变量的乘积和,即S)(y-y)=E,xy一,/n.解公式3这个正规方程组即可求得b,b,b.,b之值,代入公式2中即可求得b.,然后代入公式1中即可得到回归方程.正规方程组求解可用消元法,行列式法和矩阵等多种方法,本文采用MicrosoftExcel内部函数LINEST求解.2LINEST函数LINEST函数使用最小二乘法对已知数据进行最佳线性拟合,并返回描述此曲线的数组.因为此函数返回数值数组,所以必须以数组公式的形式输人.向量(即必须为一行或一列).如果省略known_xs,语法:LINEST(known-Yts,known_xs,const,则假设该数组为1,2,3,其数值个数与stats)known_ys相同.其中,Known_yt是关系表达式y=blx.+b2xConst为一逻辑值,用于指定是否将常量b强+.+bxm+b.中已知的y值集合.如果数组known_ys制设为0.如果Const为TRUE或省略,b将按正常计在单独一列中,则known_xts的每一列被视为一个独算.如果Const为FALSE,b将被设为0,并同时调立的变量.如果数组knownYs在单独一行中,则整b值使Y=blx.+b2x+.+bmx.kn0wn一t的每一行被视为一个独立的变量.Stats为一逻辑值,用于指定是否返回附加回Kn0wn_xts是关系表达式y=blx+b2x+归统计值.如果Stats为TRUE,则LINEST函数返回+bxm+b中已知的可选x值集合.数组known_xs可附加回归统计值.如果Stats为FALSE或省略,以包含一组或多组变量.如果只用到一个变量,只要LINEST函数只返回系数b和常量bo.known-yt和known_xts维数相同,它们可以是任何形回归分析值输出格式见表1,不管数值区域如何状的区域.如果用到多个变量,则known_ys必须为变化,其相对位置是固定不变的.表1LINEST输出的格式及各项意义的系数x的系数x的系数X1的系数常数坝SeSe1S.Se1S.b复相关系数Y的标准差F统计值离回归自由度同恫卓青离同归平青弄口注:Se巾,Se,Sese.,Se分别为对应的x巾,x.,xxl系数及常数项的标准差.当只有一个自变量x时,可直接利用下面公式得并输人相关数据(见图1).I,Iinj廿亡估霪篓氢篓羹廿l_DiU,1.叶竿/rHY干田隹)cJqL且120头南阳黄牛体长,胸围和体重资科表斜率=INDEX(LINEST(kn0wn_ys,known_xs),);手序号体长(xl:胸围()体重(y)1138176430Y轴截距=INDEX(LINEST(known_ys,known_xs),2).42145165481数据的离散程度决定了LINEST函数计算的精确6531551784844148185501度.数据越接近线性,LINEST模型就越精确.51361764196143166393i.INEST数倬用最/,二乘法来判定最适合数据的模97140166382型.当只有一个自变量时,b和b0是根据下面的公81411603519139170406式计算的:1013617142211140178452一n(y)一()(12134171403一n(一()1313818245114134169397()(x2)一()(1715138161387一三6,2),-.16141170420其中x和y是样本平均值.一201713716738718146183468现以徐继初主编的生物统计及试验设计11422211913917240520142175433页,20头南阳黄牛体长,胸围和体重资料的回归计图120头南阳黄牛体长,胸围和体重数据输入算表为例,说明LINEST函数的应用方法.在B24单元格中输人:LINEST(D3:D22,运行MicrosoftExcel2003程序,选中A1E1单B3:C22.TRUE.TRUE),并确定,选定元格,点击鼠标右键,选择设置单元格格式,再B24D28,然后按F键,并按Ctrl+Shifl+Enter选择对齐,然后在合并单元格前打勾,按键,返回结果见图2.各输出值的意义为:B24=胸确定.输人20头南阳黄牛体长,胸围和体重资料,围(x9的系数,C24为的系数,D24为常数项;I责任编辑毛由丽49经验交漉CAOYEYUXUMU2007年第8期总第141期革业与畜牧图2LINEST输出结果B25,C25,D25分别为相应系数及常数项的标准误差;B26为复相关系数,C26为Y的标准误差;B27为计算出的F值,C27为离回归自由度;B28,C28分别为回归平方和及离回归平方和.将各参数代人公式1中,得到回归方程:y一640.258+3.330x.+3.4641孙与原书一致.以上是二元回归方程的求解,相对简单,现以贵州农学院主编的生物统计附试验设196页表99为例,分别做二,三,四,五,六,七项式的回归分析,说明多元回归和多项式回归的分析方法.运行MicrosoftExcel并在工作表中录入相应数据(见图3).F21ABCDEGH1重庆市种畜场牛群各月份产犊母牛平均产奶量数据表2X1=XX2=X2X-X3x4:x4Xs=X5x6-X6x7=x7y(平均产奶311i1ii13833.4342481632641283811.58539278124372921873769.478416642561O244096163843565.747525125625312515625781253481.99863621612967776466562799363372.8297493432401168071176498235433476.761086451240963276826214420971523466.221l98172965615904953144147829693395.42121O1OO100010000i00000i000000100000003807.0813111211331i46411610511771561194871713817.0314121441728207362488322985984358318083884.52图3重庆市种畜场牛群各月份产犊母牛平均产奶量数据表选择A16单元格,输入=LINEST(H3:H14,A3:G14,TRUE,TRUE)并确定,选中A16H20单元格,按F2,再按Ctrl+Shifl+Enter,即得七项式回归模型参数(见图4).对照表1,可得相应七项式各参数及复相关系数,F统计值.代人公式1可得多项式方程:y=3730.163+89.1316x+67A869x一76.6075x+21.719x4-2.8253x5+0.1786_0.0044x,相关系数r=-O.8907,F=4.6588.ABCDEFGH16-0.004440.i78634-2.8253221.719-76.607567.4868689.131633730.1630:170.0200250.91246916.82791】I61.0471851.29992436.4523397.9651707.67998l80.890746lO7.2582#N/A#N/A#N/A#N/A#N/A#N/Al94.6588474#N/A#N/A#N/A#N/A#N/A#N/A20375178.146017.28#N/A#N/A#N/A#N/A#N/A#N/A图4七项式的输出结果同理,A22中输入=LINEST(H3:H14,A3:各参数.将各对数代人公式1中,得六项式回归方程:F14,TRUE,TRUE),选择A22G26,得到六项式图5六项式回归方程参数值责任编辑毛虫哪一革业与畜牧27年第8期总第141期CAOYEYUXUMU经验交麓y=3401.816+773x一44l-323x+105.822x3-13.380x一列),采用相同的方法可得出五,四,三,二项式回0.879x5-0.0232x,r=-O.8894,F=6.7017.每次只需归模型的各参数及方程,各参数分别见表2,代人公变换X值的数据区域和输出范围(均为向左退一式1即可得到相应的回归方程.表2回归方程的偏回归系数及常数项值五项式四项式三项式二项式b03738.3553655.8874033.9464117.201B.166.7041289.228140.589204.937B,一75.4713一l31.5623.89215.786B5.603016.3620.610B0.30370.6059B一0.027992.1回归方程的显着l生检验量数据表中,同样计算各自由度,然后利用FINV函设试验有k个处理,各处理有n个重复,则总自数,在Excel任意单元格中输人相应置信度和自由度,由度(d)=nl,回归自由度(dfr):k,离回归自例如在J18中输人FINV(0.O1,7,4),在J19中由度(dc)=nk一1.在20头南阳黄牛体长,胸围和体输人FINV(0.05,7,4),分别可得到F.l(7.)和重资料表中,df,=19,d=2,越:17.在A30和A31Fo的临界值,同理分别将各项式的相应自由度代中分别输入F0.05及F0.01,在B30中输人=FINV人FINV函数,可得到不同自由度在不同置信度范围(0.05,2,17)按Enter键(意思是返回自由的临界值(见表3).从表3可看出,七项式回归方度为2,17,置信度为0.05的临界F值),在B31中程中,Y与x,x,X,X4,x,X6,X7的回归关系不显输人=FINV(0.O1,2,17)按Enter键,分别着,F=4.658847<Fo.o5(7,4)=6.094211;六项式方程中,得到F:,Fol0I(:,的临界值,即F:.7):3.5915,Y对x,x,x,X4,x,x的回归关系达显着水平,F.=6.1121,F=25.180O>Foo.,说明x】(体长),F=6.70166>Fo.o5(6s)=4.387374;在五项式方程中,x2(胸围)与y(体重)极显着相关.F=9.076404>17oo-5.)=8.745895,说明Y与x,x2,在重庆市种畜场牛群各月份产犊母牛平均产奶x,X4,x的回归关系达极显着水平.表3各回归方程显着性检验及拟合性测定表多项式项数变异来源dfFFFr七项式总变异114.65884714.975766.0942110.890746回归7离回归4六项式总变异116.7016610.672254.3873740.889405回归6离回归5五项式总变异119.0764048.7458954.3873740.883228回归5离回归6四项武总变异1113.069527.8466454.1203120.881913回归4离回归7三项式总变异1110.658297.5909924.0661810.799874回归3离回归8二项式总变异1116.892298.0215174.2564950.789644回归2离回归9-责任藿毛虫响51径编辑毛虫帅52经验交流CAOYEYUXUMU2007年第8期总第141期革业与畜牧一同理,四项式,三项式和二项式方程中,因变量各参数,对多元回归来说,利用这一函数使计算更为简与自变量之间的回归关系均达极显着水平.从表3还便,快捷,便于一般科技工作者掌握.利用该函数,无可看出,除七项式因变量与自变量之间的回归关系不论多项式有多少项或多元回归中有多少个自变量,均可显着外,其他多项式方程的拟合性较高,但四项式,快速,准确地计算出相关的回归参数.五项式,六项式方程的拟合性比三项式,二项式好.使用LINEST函数时,返回值的相对位置是固定分析结果与邵碧雄等的结论完全一致.不变的,必须记清返回数值代表的相应参数,否则无3.小结法理解返回数值的意义.LINEST函数使用最小二乘法对已知数据进行最佳参考资料线性拟合,并返回描述此曲线的参组.因为此函数返1徐继初.生物统计及试验设计一全国高等农林专科统编教材回数值数组,所以必须以数组公式的形式输入.在输入M.北京:农业出版社,1992.112.120.函;舌,如直接按Enter键只能得到个数值,必须23贵州农学院.生物统计附试验设计(第二版)M.北京:农业选定输入公式的单元格及相应数据输出区域,按F2出版社.1998.196.后,再同时按Ctrl+Shifl+Enter才能得到数组值.只33邵碧雄,叶左局.利用Excel软件进行多元回归与多项式回要公式中各数组区域正确,可直接得到相应回归方程的归分析J.广东奶业,2006,(2):11-15.AnalysisofMultipleandPolynodalRegressionProcedurebyInnerF眦ti0nLlNESTofExce1ZHOUZi-wei(YunnanBeefcattle&PastureResearchCentre,Kunming650212,China)Abstract:MultipleandpolynomialregressionplaysanimportantroleinbiologicalStatistic.Butitisverydifficultfolcoefficientcalculationwhenindependentvariablesareover3.InthispaperamethodofmultipleandpolynomialregressionprocedureanalysisbyInnerFunctionLINESTofMicrosoftExcelwasintroducedclearly.Keywords:Multiple