《利用Excel软件进行多元回归与多项式回归分析》由会员分享，可在线阅读，更多相关《利用Excel软件进行多元回归与多项式回归分析（5页珍藏版）》请在金锄头文库上搜索。
1、2006第2期 广东奶业营养与饲养 多项式回归方程可以转化为多元回归方程, 即设x 1= x1,x2= x1 2 ,x 3= x3,x4= x1 4 ,x 5= x1x2, , 这样就可 以把任意多项式回归转化为多元回归方程来求 解。 2多元回归与多项式回归分析的步骤 为了便于读者更加容易掌握计算方法, 本文以 一实例进行说明。该例题来源于全国高等农业院 校教材 生物统计附试验设计 第二版( 贵州农学院 多元回归与多项式回归分析在生物统计学上 有着极其重要的地位。多元回归与多项式回归的 偏回归系数的计算比较复杂, 特别是当自变量达3 个以上的回归分析给计算带来很大的不便。故多 元回归与多项式回
2、归分析一般是借助计算机进行 计算的。以往利用计算机进行多元回归与多项式 回归分析需要编程,对普通畜牧工作者来说是比 较困难的。为此,笔者把自己利用M i c r o s o f t E x c e l 2 0 0 0软件进行多元回归与多项式回归分析的一些 体会与同行分享, 不足之处敬请指正。 1多元回归与多项式回归分析的原理 设y为一依变量, 它受x 1,x2,x3, xm等m个 自变量的影响,我们可以在它们之间配置一个多 元回归方程式: y 9= b 0+ b1x1+ b2x2+ b3x3+ bmxm ( 公式1) 其中b 0为常数项, b 0= y - b1x1- b2x2- b3x3-
3、bmxm ( 公式2) b 1,b2,b3bm必须满足下列的正规方程式: 利用 E x c e l 软件进行多元回归 与多项式回归分析 邵碧雄, 叶左局 ( 广州珠江牛奶有限公司,广州番禺区万顷沙5 1 1 4 6 2) ( 公式3) 公式3中的S S x是指变量x的离均差的平方和 ( 计算公式:S S x= (x - x) 2 ) ,S P x y是指x、y两变量的 离均差的乘积和( 计算公式:S P x y= (x - x) (y - y)= x y - x y / n) 。 解这个正规方程组, 即可得出b 1,b2,b3bm 各值, 代入公式2求得b 0, 然后再一起代入公式1, 就得到
4、多次回归方程式。 对这个正规方程组的解法有很多, 在这里笔者 是用矩阵法来求解的。上述方程组的解, 用矩阵 可以表示为: ( 公式4) !“# 2006第2期 广东奶业 表2正规方程组各系数及常数项的数组分布表 2 . 3计算正规方程组各系数与常数项 在工作表的A 3:F 7各个单元格中计算出如表 2所示的各正规方程组的系数及常数项的值, 注意 各个数值的位置。单元格A 3,B4,C5,D6,E7的值分 别为x 1,x2,x3,x4,x5各自变量的离均差平方和, 其余 各单元格的数值分别各自变量两两相互的离均差 乘积和, 并依此对角线为轴左右对称相等( 如表2 中A 3:E 7区域) 。而F
5、3:F 7为常数项, 分别等于各 自变量同依变量y的离均差乘积和。 营养与饲养 表1产犊月份与产奶量数据表 主编, 农业出版社出版) , 第1 9 6页表9 - 9重庆市 种畜场牛群各月份产犊母牛平均产奶量的数据 ( 见表1) , 分析产奶量与产犊月份间的回归关系。 该例题在文中只进行了二项式分析, 为得到更加 有代表性的回归曲线, 笔者分别做出二、 三、 四、 五 项式的回归分析, 然后挑选出最适合的回归方程 式。 2 . 1根据例题的散点分布图, 可以看出产犊月份与 产奶量间是呈曲线分布, 故应作多项式回归分析。 设x 1= x,x2= x 2 ,x 3= x 3 ,x 4= x 4 ,x
6、 5= x 5 ,x为产犊月份,y为 平均3 0 5天产奶量。 2 . 2启动M i c r o s o f t E x c e l 2 0 0 0软件, 选择一个空白 的工作表, 如S h e e t 1。在工作表的A 9:F 2 4区域内 输入如表1所示的数据。A 2 4:F 2 4分别为对应列 的平均值( 函数为:A V E R A G E) ,B 1 1:E 2 2区域中 每列各单元格的值等于A 1 1:A 2 2同行单元格数值 的2、3、4或5次方( 函数为:P O WE R) 。 !“# 2006第2期 广东奶业 表4回归方程的偏回归系数与常数项值 在A 3单元格中输入函数:= D
7、 E V S Q(A 1 1:A 2 2) , 计算出自变量x 1的离均差平方和; 在B 4单元格中 输入函数:= D E V S Q(B 1 1:B 2 2) , 计算出自变量x 2 的离均差平方和, 按此方法依次在C 5、D 6、E 7单元 格中分别用函数计算出自变量x 3、x4、x5的离均差 平方和; 在B 3单元格中输入公式:= S U M P R O D U C T (A 1 1:A 2 2,B 1 1:B 2 2)- S U M(A 1 1:A 2 2) S U M(B 1 1: B 2 2) C O U N T(A 1 1:A 2 2) 计算出自变量x 1与x2的 离均差乘积和(
8、 公式中的S U M P R O D U C T函数是返 回相应数组或区域的乘积和,C O U N T是计算所包 含区域数值的个数, 即n的值) , 修改该公式中的 数值区域就能很容易计算出各自变量两两相互的 离均差乘积和以及各自变量同依变量y的离均差 乘积和; 在G 7单元格中输入函数:= D E V S Q(F 1 1: F 2 2) , 计算出y变量的离均差平方和; 表2中对角 线下方各单元格的值等于以此对角线为轴对称单 元格的值。具体的计算结果见表3。 2 . 4计算回归方程的偏回归系数与常数项 在J 4单元格中输入公式:= M M U L T (M I N V E R S E(A 3
9、:E 7) ,F 3:F 7) , 确定后可返回一个 数值( 公式中的M I N V E R S E函数是返回矩阵的逆, M M U L T函数是返回两矩阵的乘积) , 然后选中J 4: J 8单元格, 并把光标放在公式编辑栏的最后面, 同 时按下C t r l+S h i f t+E n t e r 三个按键, 即可返回 一组数组值( 见表4中J 4:J 8单元格的数值) , 该数 组即为五项回归方程式的各个偏回归系数。按以 上方法, 只要修改公式中的数组区域, 就可以把四 项、 三项、 二项回归方程式的偏回归系数求出( 见 表4) 。 表3正规方程组各系数及常数项值 营养与饲养 !“# 2
10、006第2期 广东奶业 2 . 5显著性检验及准确度测定, 选择回归方程 多元回归的显著性检验与准确度测定具体计 算方法请参考全国高等农业院校教材 生物统计附 试验设计 第二版第1 8 3页内容, 本文只简单介绍 用E x c e l进行显著性检验与准确度测定的过程。 在R 3单元格中输入公式:= G 7或= D E V S Q (F 1 1:F 2 2) ,R 4中输入:= M 4 *F 3 + F 4*M 5 ,R 5中输 入:= R 3 - R 4,S 4中输入:= R 4 / Q 4,S 5中输入:= R 5 / Q 5,T 5中输入:= S 4 / S 5, 就可以相应求出S S、M
11、 S及 F值; 在W5单元格中输入:= S Q R T(R 4 / R 3) ,X 5中 输入:= R 4 / R 3, 可以求出R及R 2的值; 在 V 4单元 格中输入:= F I N V(0 . 0 5,Q 4,Q 5) ,V 5中输入:= F I N V (0 . 0 1,Q 4,Q 5) , 即可计算出0 . 0 5与0 . 0 1置信度的 临界F值(F I N V函数是返回F概率分布的反函数 值, 即查表的F值。公式中的0 . 0 5、0 . 0 1是指置信 度,Q 4为回归的自由度, 而Q 5为离回归的自由 度) 。用此方法分别计算出其它多项式S S、M S、F 等值。详见表5中
12、的数值。 从表5中的数值可以看出, 各个回归方程式是 非常显著的, 准确度也较高, 但四项式与五项式回 归方程的准确度比二项式与三项式回归方程的准 确度要高, 而四项式回归方程式比五项式回归方程 式使用更为方便, 故选择四项式回归方程式来估计 奶牛产犊月份与产奶量之间的关系更为合理( 回归 方程式为:y = 3 6 5 5 . 8 8 7 4 + 2 8 9 . 2 2 7 6 x 1- 1 3 1 . 5 6 1 7 x2+ 1 6 . 3 6 2 4 x 3- 0 . 6 0 5 9 x4) 。( 下转第3 6页) 在J 3单元格中输入公式:= F 2 4 - J 4 A 2 4 - J
13、5 B 2 4 - J 6 C 2 4 - J 7 D 2 4 - J 8 E 2 4; 在K 3单元格中输 入:= F 2 4 - K 4 A 2 4 - K 5 B 2 4 - K 6 C 2 4 - K 7 D 2 4; 在 L 3单元格中输入:= F 2 4 - L 4 A 2 4 -L 5 B 2 4 - L 6 C 2 4; 在H 3单元格中输入= F 2 4 - M 4 A 2 4 - M 5 B 2 4; 即可以分别把五项、 四项、 三项、 二项式回归方程 式的常数项求出。 分别把各种回归方程式的偏回归系数及常数 项代入公式:y N= b 0+ b1x1+ b2x2+ b3x3
14、+ bmxm就可以求 出相应的回归方程式。 通过这种方法计算出的二项式回归方程式为: y = 4 1 1 7 . 2 0 1 4 - 2 0 4 . 9 3 6 7 x 1+ 1 5 . 7 8 5 7 x2, 与教材上所求 得的回归方程式是一致的。 表5各个回归方程式显著性检验及准确度测定表 营养与饲养 !“# 2006第2期 广东奶业 广东奶业2 0 0 6年第一期第6页, 营养成分与牛奶化学成分的关系 的作者 应为 “易涛、 唐海晏” , 特此致歉。 更正启事 ( 上接第1 4页) 3小结与讨论 3 . 1M M U L T函数是指返回两数组a r r a y 1与a r r a y 2
15、 的矩阵乘积,a r r a y 1的列数必须与a r r a y 2的行数相 同, 而且两数组中都只能包含数值。返回后的矩阵 乘积的行数与a r r a y 1的行数相同, 列数与a r r a y 2的 列数相同。在单元格中输入M M U L T或 M I N V E R S E等数组函数后, 如果直接按E n t e r 键, 只能返回一个数值, 必须同时按下C t r l+S h i f t+ E n t e r 后才能返回一个数组值。 3 . 2笔者在本文中是制作了二项式( 二元) 至五项 式( 五元) 回归方程的计算模式, 基本能满足畜牧统 计上的多项式( 多元) 回归分析的需要。
16、在制作该 计算模式时, 读者应尽量避免在输入公式时使用绝 对地址, 应使用相对地址。这样可以保证在插入或 删除行、 列或单元格时, 公式不用重新输入。如果 在单元格A 1 1:A 2 2之间插入或删除行, 并录入或 修改自变量x与依变量y的数值后, 能很快计算出 多项式( 多元) 回归方程式。因此, 这个计算模式就 成为一个多项式( 多元) 回归分析的小程序, 可以重 复使用。 3 . 3F I N V函数是指返回F概率分布的反函数值, 即是平时查F值表中的值, 可以省却读者查F值 表的麻烦。在E x c e l 2 0 0 0有许多这样的统计函数, 可很方便地进行统计分析, 如返回t值表的函数为 “T I N V” 、 返回协方差的函数为 “C O V A R” 等, 读者不 防试一试。 3 . 4以上介绍的是多元回归与多项式回归分析最 基本的步骤, 在实际工作中可能还需要做复相关系 数与偏回归系数的显著性检验、 标准偏回归系数与 偏相关系数的计算
