回归分析在EXCEL中的运用(张苇)

回归分析是对客观事物数量依存关系的分析，是农业科学研究中一种重要的统计分析方法，广泛地应用于社会经济现象变量之间的影响因素和关联的研究。

回归模型的建立就是为了全面揭示这种复杂的依存关系，准确地测定现象之间的数量变动，提高预测和控制的准确度。

１回归分析在ＥＸＣＥＬ中的求解
１．１３因素５水平最优设计回归模型
Ｙ＝ｂｏ＋ｎ
ｊ＝１
!ｂｊｘｊ＋ｎ
ｊ＝１
!ｂｊｊｘｊ２＋ｉ＜ｊ
!＋ｂｉｊｘｖｘｊ
其中Ｙ是因变量，Ｘｉ、Ｘｊ是自变量，ｂ０，ｂ１，ｂ２，．．．．．．ｂ９
是回归方程的参数。

式中各偏回归系数按矩阵运算求解得出，即ｂ＝（Ｘ
Ｔ
Ｘ）－１ＸＴＹ，这里ｂ为各偏回归系数矩阵，为结构矩阵、ＸＴ为结构矩阵的转置，为试验观察值。

利用矩阵求解回归方程是回归分析的一种较好方法，Ｅｘｃｅｌ软件中的函数为回归分析的矩阵求解给出了非常方便的操作过程。

１．２数据来源
水稻三因素五水平最优设计试验资料。

［１］
试验采
用Ｎ（ｘ１）、Ｐ２Ｏ５（ｘ２）、Ｋ２Ｏ（ｘ３）３因素５水平１２处理最优设计。

研究利用３因素５水平１２处理回归设计法，设置重复与小区拉丁方排列相结合，通过对建立的数学模型进行择优，可有效地提高试验的成功率；又可以实现不同年度的重复建模。

１．３操作程序
启动ＥＸＣＥＬ，新建工作薄。

按照图１所示输入原始数据及结构矩阵（Ｘ）数据。

分析：Ｃ＝［ＸＴ
Ｘ］－１
式中，Ｃ表示相关矩阵，Ｘ表示结
构矩阵，ＸＴ
为Ｘ的转置矩阵。

由矩阵的运算可知，求解
矩阵Ｃ可能看成求结构矩阵与它的转置矩阵相乘后的逆矩阵。

运用Ｅｘｃｅｌ提供的ＭＩＮＶＥＲＳＥ、ＭＭＵＬＴ、
ＭＤＥＴＥＲＭ等函数可以方便地完成矩阵的转置、矩阵的乘积、逆矩阵计算。

求解过程：
（１）在单元格Ｂ２：Ｍ１４区域按矩阵的顺序分别输入结构矩阵的元素。

在Ｍ３：Ｍ１４区域分别输入产量Ｙ矩阵的元素，在Ｃ１５：Ｎ１５区域存放Ｙ的转置矩阵。

操作方法：选定原数组（Ｍ３：Ｍ１４），点“编辑”菜单下的“复制”
，再选定以存放转置矩阵元素的区域（Ｃ１５：Ｎ１５），点“编辑”菜单下的“选择性粘贴”，再点“转置”、“确定”即
可。

（２）选定Ｃ１８：Ｎ２７区域以存放Ａ阵，即为Ｃ３：Ｌ１４的转置矩阵。

操作方法：选定原数组（Ｃ３：Ｌ１４），点“编辑”菜单下的“复制”，再选定以存放转置矩阵元素的区域（Ｃ１８：Ｎ２７），点“编辑”菜单下的“选择性粘贴”，再点“转置”、“确定”即可。

（３）选定Ｃ２９：Ｌ３８区域以存放矩阵Ｃ１８：Ｎ２７与矩阵Ｃ３：Ｌ１４乘积。

矩阵乘法在Ｅｘｃｅｌ中通过函数
ＭＭＵＬＴ完成，操作方法是首先选定结果区域，而后在公式栏中输入“＝ＭＭＵＬＴ（Ｃ１８：Ｎ２７，Ｃ３：Ｌ１４）”，结果区域要根据原始矩阵的大小设定，如本例矩阵Ｃ１８：Ｎ２７
收稿日期：２００７－０８－３１
作者简介：张苇（１９６６－），男，本科，高级农艺师，研究方向：作物规范化栽培及计算机应用。

回归分析在ＥＸＣＥＬ中的运用
张苇
（福建省宁德市农业局，福建宁德３５２１００）
摘
要：举例说明利用ＥＸＣＥＬ软件进行回归分析的详细过程，说明应用ＥＸＣＥＬ进行回归分析无需复杂的编程便可实现统
计计算的精确化、自动化。

关键词：ＥＸＣＥＬ；回归分析中图分类号：ＴＰ３９９
文献标识码：Ｂ
文章编码：１６７２－６２５１（２００７）１２－０１７９－０２
图１
试验原始数据与结果
（１０行，１２列）与矩阵Ｃ３：Ｌ１４（１２行，１０列）相乘，则选定的结果区域需为（１０行，１０列）方可返回全部结果，即要求前一矩阵的列数等于后一矩阵的行数。

由于ＭＭＵＬＴ函数属于数组函数，在输入公式后需同时按Ｃｔｒｌ＋Ｓｈｉｆｔ＋Ｅｎｔｅｒ复合键，得到运算结果。

（４）选定Ｃ４０：Ｌ４９区域以存放矩阵Ｃ２９：Ｌ３８的逆阵，按Ｆ２进入编辑状态，在编辑窗口输入公式“＝ＭＩＮＶＥＲＳＥ（Ｃ４０：Ｌ４９）”，然后按下Ｃｔｒｌ＋Ｓｈｉｆｔ＋Ｅｎｔｅｒ复合键得到逆矩阵，即Ｃ为［ＸＴＸ］－１。

实际操作所得的结果见图２。

１．４常数项Ｂ值
各列Ｂ值（!ｘｙ）：即ＹＴ与Ｘｉ或Ｘｉ２或Ｘｉｊ相乘，其中ＹＴ为Ｙ的转置矩阵，填入图１。

计算过程：Ｂ０＝［ＹＴＸ０］＝４９９１，Ｅｘｃｅｌ操作方法与（３）相似，在Ｂ１６：Ｌ１６区域存放Ｂ值。

同理：Ｂ１＝４８８１３．６Ｂ１１＝６６８２３７．８Ｂ１２＝２４２５７０．６Ｂ２＝２３３２３．４Ｂ２２＝１６０２００．４Ｂ１３＝３６４１６５．５Ｂ３＝３５０９２．５Ｂ３３＝３５９３２５Ｂ２３＝１７５４８２１．５计算回归系数ｂ
ｂ＝ＣＢ即［ＸＴＸ］－１（逆阵）乘Ｂ阵。

选定Ｂ５１：Ｌ５５区域以存放矩阵Ｃ１６：Ｌ１６与矩阵Ｃ４０：Ｌ４９乘积。

操作方法与（３）相似见图３。

得：ｂ０＝３００．５２７１；ｂ１＝２５．８８７１８；ｂ２＝０．８８８２３９；ｂ３＝７．９０６２４２ｂ４＝－０．９６１０９；ｂ５＝－０．６６４３５；ｂ６＝－０．６３４８１；ｂ７＝－０．０７７３２；ｂ８＝－０．２１０４８；ｂ９＝０．６９５３２９１．６建立数学模型
将Ｂ值代入Ｙ＝ｂｏ＋
ｎ
Ｊ＝１
!＋ｂｊ＋ｎ
Ｊ＝１
!ｂｊｊｘｊ＋
ｉ＜ｊ
!ｂｉｊｘｉｘｊ得：
∧ｙ＝３００．５３＋２５．８９ｘ
１
＋０．８８８ｘ２＋７．９１ｘ３－０．０８ｘ１ｘ２－０．２１ｘ１ｘ３＋０．６９５ｘ２ｘ３－０．９６ｘ２１－０．６６ｘ２２－０．６３ｘ２３
理论值∧ｙ：将结构矩阵中的实际值代入回归方程式，得各处理的理论估计值ｙｉ填入图１中。

２模型检验
２．１Ｆ检验
计算公式：Ｆ＝
Ｕ／ｎ
Ｑ／（ｍ－ｎ－１）
总离散平方和：矩阵表示式Ｓ＝［Ｙ］Ｔ［Ｙ］（式中，Ｙ为
Ｙ实际值－Ｙ平均值）
－ｙ
（平均值）
＝!ｙｉ／１２＝４１５．９２
Ｓ＝ＹＴＹ＝［ｙ－ｙ#］Ｔ［ｙ－ｙ#］＝３５９１６．９１６８（Ｅｘｃｅｌ操作方法与（３）相似）
回归平方和：矩阵表示式Ｕ＝ＢＴＬＹ＝ＢＴｘＴＹ（式中，Ｙ
为ｙ
回归值－
ｙ回归平均值）；
∧ｙ（回归平均值）＝!∧ｙｉ／１２＝４１６．８５
Ｕ＝ＢＴＬＹ＝ＢＴｘＴＹ＝３６１０１．５７９７（Ｅｘｃｅｌ操作方法与（３）相似）
剩余平方和：矩阵表示式Ｑ＝［ｙ－∧ｙ］Ｔ［ｙ－∧ｙ］（式中，Ｙ
为ｙ
实际值－
ｙ回归值）
Ｑ＝［ｙ－∧ｙ］Ｔ［ｙ－∧ｙ］＝１６０．４６４７（Ｅｘｃｅｌ操作方法与（３）相似）
则：Ｆ＝
Ｕ／ｎ
Ｑ／（ｍ－ｎ－１）
＝３５７５６．４５２１／９
１６０．４６４７／１２－９－１
＝４９．５３＊＊２．２复相关系数Ｒ
Ｒ＝１－Ｑ／Ｓ
$＝Ｕ／Ｓ
$＝３５７５６．４５２１／３５９１６．９１６８
$＝０．９９７８
图２
图３回归系数计算结果
表１试验方差分析
DF SS MS F F 0.1 FÁ
Â
Á
9 35756.4521 3972.94 49.539.38 19.38
2 160.4647 80.2324
11 35916.9168
注：＊置信度９０°显著；＊＊置信度９５％显著
（下转第１８２页）
（上接第１８０页）
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
２．３标准误
回归标准误Ｓｅ＝Ｑ／（ｍ－ｎ－１）
"
将Ｑ＝［ｙ－∧ｙ］Ｔ［ｙ－∧ｙ］＝１６０．４６４７（ｍ＝１２，ｎ＝９）代入得：
Ｓｅ＝１６０．４６４７
１２－９－１
"＝８．９６
３总结
ＥＸＣＥＬ没有提供专门计算矩阵的模块，但可利用其提供的函数功能方便地计算回归模型的系数和检验。

本文仅以回归分析在ＥＸＣＥＬ中的实现过程为例，说明操作过程，有关本文举例具体解释可以参见相关文献。

参考文献
［１］张苇．应用３因素５水平最优设计建立水稻施肥模型的研究［Ｊ］．土壤通报，２００６，３７（６）：１１３４￣１１３６．
［２］张琪，等．通径分析在ＥＸＣＥＬ和ＳＰＳＳ中的实现［Ｊ］．农业网络信息，２００７，（３）：１０９￣１１０．
［３］周晓农．矩阵在回归分析中的应用［Ｊ］．金筑大学学报（综合版），１９９９，３３（１）：１０５￣１１０．
属性来重新确定当前记录的位置。

仅向前Ｒｅｃｏｒｄｓｅｔ对象只支持ＭｏｖｅＮｅｘｔ方法。

当使用Ｍｏｖｅ方法访问每个记录（或枚举Ｒｅｃｏｒｄｓｅｔ）时，可使用ＢＯＦ和ＥＯＦ属性查看是否移动已经超过了Ｒｅｃｏｒｄｓｅｔ的开始或结尾。

３．２．４Ｒｅｃｏｒｄｓｅｔ对象可支持两类更新：立即更新和批更新。

使用立即更新，一旦调用Ｕｐｄａｔｅ方法，对数据的所有更改将被立即写入基本数据源。

也可以使用ＡｄｄＮｅｗ和Ｕｐｄａｔｅ方法将值的数组作为参数传递，同时更新记录的若干字段。

如果提供者支持批更新，可以使提供者将多个记录的更改存入缓存，然后使用Ｕｐ－ｄａｔｅＢａｔｃｈ方法在单个调用中将它们传送给数据库。

这种情况应用于使用ＡｄｄＮｅｗ、Ｕｐｄａｔｅ和Ｄｅｌｅｔｅ方法所做的更改。

调用ＵｐｄａｔｅＢａｔｃｈ方法后，可以使用Ｓｔａｔｕｓ属性检查任何数据冲突并加以解决。

３．３游标的正确选择与使用
３．３．１仅向前游标型游标的特点与使用仅向前游标型游标占用最少的资源，因而能得到最高的性能．它也是ｒｅｃｏｒｄｓｅｔ缺省的游标类型，唯一的缺点是只能单向移动游标．只能从头到尾单向遍历记录集，亦即只能使用ＭｏｖｅＮｅｘｔ这样的方法，而不能使用ＭｏｖｅＰｒｅｖ．比方说，如果企图在一个仅向前游标型的ｒｅｃｏｒｄｓｅｔ里使用ＭｏｖｅＰｒｅｖ的方法，将得到一个错误提示，它告诉无法利用消防水龙带型的游标来完成的操作．（所谓的消防水龙带型游标，是指记录集中的记录就是水龙带里的水一样，只能朝一个方向运动．）要使用一个仅向前游标型的游标，应该键入：
ｒｓ．ＣｕｒｓｏｒＴｙｐｅ＝ａｄＯｐｅｎＦｏｒｗａｒｄＯｎｌｙ３．３．２静态游标游标的特点与使用它允许向任何一个方向移动游标，但如果用户在查看数据集同时，数据集发生了更新，删除或添加的操作，它将无法实时反映数据集的变化．它就如同数据集在用户在请求数据的时刻的映像（也叫快照，ｓｎａｐｓｈｏｔ）．如果数据集的游标是静态游标型的，可以使用各种各样的遍历数据集的命令，诸如ＭｏｖｅＦｉｒｓｔ，ＭｏｖｅＰｒｅｖ，ＭｏｖｅＬａｓｔ，ＲｅｃｏｒｄＣｏｕｎｔ等等．可以键入下面的程序来使用静态游标型游标：
ｒｓ．ＣｕｒｓｏｒＴｙｐｅ＝ａｄＯｐｅｎＳｔａｔｉｃ
３．３．３动态游标和键集游标型游标的特点与使用动态游标和键集游标型游标可以实时查看数据的更动．但这只是在ＶｉｓｕａｌＢＡＳＩＣ应用程序起作用，对于Ｗｅｂ页面则一点儿用都没有．一旦用户发出数据请求，就同静态型游标一样数据集中得到那一时刻的数据映像．如果用户不刷新他们的浏览器页面，想持续地反应数据的变化是不可能的。

由于Ｗｅｂ浏览方式的特殊性，可能根本用不到动态游标和键集游标型游标型的游标，它们比Ｓｔａｔｉｃ或ｆｏｒｗａｒｄ－ｏｎｌｙ型的游标更贪婪地攫取系统的资源．如果需要各方向遍历的数据集，或是想得知数据集中的记录数，就采用静态型游标，否则就用仅向前游标型游标型的。

４总结
总之，在创建ｒｅｃｏｒｄｓｅｔ时，应该选择合适的游标！这样不但减小服务器的工作量，同时也能加快打开数据库的速度。