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

利用Excel内部函数LINEST进行多元回归和多项式回归分析 l 贝 任 编 辑 毛 由 丽 48 经验交流CAOYEYUXUMU2007年第8期总第141期革业与畜牧_ 厕两都画皴贼避行 画画参甄画画蹄 周自玮 (云南省肉牛和牧草研究中心,昆明小哨650212) 摘要:多元回归和多项式回归在生物统计中具有重要地位,但当自变量超过3个时,相关参数的计算十分复 杂.本文通过实例详细介绍了利用Micr0s0ftExcel内部函数LINEST对多项式及多元回归分析的方法,该方法具有 快速,简单,准确的特点. 关键词:多元回归;多项式回归;MicrosoftExcel;LINEST函数 中图分类号:$126文献标识码:B文章编号:1673—8403(2007)08—0048—05 研究一个依变量与多个自变量之间的关系在统计 学上称为多元回归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=y—b一6一……一6 — Ebi(公式2),b,b……b分别为Y对x,x 收稿日期:2006一l1—07 基金项目:本文由云南省自然基金项目(2004C0063M)德 宏狼尾草特性及栽培技术研究资助 作者简介:周自玮(1966一),副研究员,硕士生导师,云 南农业大学在读博士,长期从事牧草研究工作. ……X的偏回归系数.多元回归方程的建立,必须使 ∑(y-y)为最小,即使 Q=Z(),)z_∑(y-b-b~x2-…一一bmXm) =∑{(),一)一b(一)一bz(一)一……一6 m_}为最小.因此,需分别对b,b,……b求 偏导数,并令之为0,可得到正规方程组: b,SS,. +b:SPx+6. …… +6. 6-.s+6+6+……+6=5 6-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强 +...+b~xm+b.中已知的y值集合.如果数组known_ys制设为0.如果Const为TRUE或省略,b将按正常计 在单独一列中,则known_xts的每一列被视为一个独算.如果Const为FALSE,b将被设为0,并同时调 立的变量.如果数组known—Ys在单独一行中,则整b值使Y=blx.+b2x+..+bmx. kn0wn一t的每一行被视为一个独立的变量.Stats为一逻辑值,用于指定是否返回附加回 Kn0wn_xts是关系表达式y=blx+b2x+…归统计值.如果Stats为TRUE,则LINEST函数返回 +b~xm+b中已知的可选x值集合.数组known_xs可附加回归统计值.如果Stats为FALSE或省略, 以包含一组或