巧用Excel解决多元非线性回归分析

农业网络信息AGRICULTURE NETWORK INFORMATION·研究与开发·2011年第1期巧用Excel 解决多元非线性回归分析龚江，石培春，李春燕(石河子大学农学院，石河子832003)摘要：非线性回归是回归分析的重要内容和难点，而多元非线性回归在农业生产中有重要的应用。

应用Excel “工具”菜单“数据分析”选项中的“回归”分析工具，以二元二次非线性回归为例，阐述了用Excel 做多元非线性回归的详细过程，并与SPSS 软件做的结果进行比较，证明使用Excel 做多元非线性回归完全可行，且操作简单、易行，并就方程的统计意义进行了分析。

关键词：Excel ；多元；非线性回归中图分类号：S126文献标识码：A文章编码：1672－6251（2011）01－0046－03Application of Excel Software in Multi-nonlinear Regress AnalysisGONG Jiang,SHI Peichun,LI Chunyan(Agriculture College of Shihezi Univerity,Shihezi 832003)Abstract:Nonlinear regress analysis was a difficult and significant method of regress analysis ，the application of which was important in agriculture production.In this paper,with the multi-linear regression analysis by “data analysis ”tool of Microsoft Excel as example,a 2times nonlinear regress analysis ’s process was described,and the results showed that the output was same with SPSS software ，then the statistical significance of the 2times nonlinear regress equation was analyzed.Key words:Excel software;multi analysis;nonlinear regress注：新疆石河子大学农学院一类课程“生物统计学”支助。

作者简介：龚江（1976-），男，硕士，讲师，研究方向：生物统计教学和植物营养。

收稿日期：2010-12-10大量统计软件的问世，使统计分析在科研领域迅速普及应用。

众所周知，统计软件如SAS 、SPSS 等虽然功能强大，但较难掌握，并且市面上出售的统计软件大都是盗版软件，不但运行结果的可靠性无法保证，也侵犯了知识产权。

对于大多数科研工作者，尤其是基层的科研工作者来说，经常使用的统计软件与涉及的方法也很有限，主要集中在方差分析、回归与相关分析等少数几种方法上，并不需要包罗万象、功能强大的统计软件。

而正版统计软件也由于其价格不菲，难以被大多数科研工作者承受。

Excel 是Office 家族的一个成员，是功能强大、使用方便的电子表格式数据综合管理与分析系统，可用来记录和整理试验数据。

另外，Excel 也具备一些统计运算的功能[1]，若能巧妙地使用，也可以解决一些较为复杂的农业统计运算问题，如多元非线性回归的问题等，其统计结果和SPSS 软件结果一致。

1Excel 统计功能的安装单击Microsoft Excel 中文版菜单栏中“工具”的“加载宏”命令，在“加载宏”对话框中选定“分析工具库”，再按“确定”钮（见图1），“数据分析”这一项就出现在工具菜单栏中（见图2）。

若Excel “工具”中的“加载宏”没有“分析工具库”，则将Office Excel 中文专业版光盘放入光驱中，运行“安装”程序，点击“添加/删除”按钮，出现“MicrosoftOffice 维护”对话框后，在“选项”一栏中，选中“Microsoft Excel ”，然后单击“更改选项”按钮，出现新的对话框，再选中“加载宏”继续单击“更改选项”按钮，在新的对话框中选取分析工具库，确定即可，之后按照安装向导的指示即可顺利安装。

图1Excel统计功能的安装图2Excel“数据分析”功能2Excel进行多元非线性回归Excel“数据分析”功能中的回归为线性回归，直接应用并不能解决非线性回归的问题，需要将数据进行线性转化才能进行。

如研究水肥耦合效应对棉花产量的影响[2]（见表1），可用一个包含交互项的二元二次数学模型来描述：，其中，y为回归值，即作物产量；x1，x2为两个自变量，即对作物的施肥和灌水量。

由于在施肥和灌水两种因素的影响下，其作物产量是这两个不同因素共同作用的结果，所以因变量与两种因素x1和x2间的函数关系可用二次效应曲面来描述[3]。

可将二元二次非线性回归模型转变成一个五元一次线性回归模型，即由2个自变量变成5个自变量，其数据格式如表2所示。

打开Excel“数据分析”功能中的回归，把y输入到因变量“y值输入区域”，将“x1到x5”一起输入到自变量“x值输入区域”，点确定输出结果。

表3为线性回归分析的方差分析表，F值为30.6，其显著水平为0.009（p<0.05），表明存在真实的（显著的）五元一次线性回归方程，即存在真实的二元二次非线性回归方程。

表4为Excel软件五元一次线性回归方程回归系数的检验表，通过表4得到棉花产量的五元一次线性回归方程为：（1）将其还原回去即得二元二次回归方程为：（2）其中：y为籽棉产量，N为施氮量，W为灌水量。

此方程与SPSS软件所得到的结果完全一致[2]。

3方程分析3.1方程的意义式（2）中的一次项W和N的系数为正表明单独增加灌水量（t=8.78**）和施氮量（t=1.51*，多元回归显著水平可放大到p=0.25）对棉花籽棉产量有增加作用；交互项的系数为-0.0029（t=-0.54，p>0.25，不显著），表示灌水量和施氮量的交互作用是微弱负效应；二次项的系数为负，说明过多的水氮投入并不利于棉花增产。

进一步分析，两因素相比灌水量对棉花产量的影响大于施氮量的影响（灌水量t>施氮量t），注意并非因为灌水的系数7.111大于施肥的系数3.064，因为两者的单位和变化范围（量纲）不同，不能直接通过系数大小来判断哪个因素对产量的影响大、与产量关系更密切（一般科技工作者误认为系数大的对产量影响大），这需要通过SPSS偏相关或通径分析来判断。

通过SPPS软件进行偏相关分析（见表5），x1（施肥）与y偏相关系数为0.6578（显著性p=0.228），x2（灌水）与y偏相关系数为0.9811（p=0.03），相比x2显著性更高，表明灌水与棉花产量更密切，仔细对比回归x1的系数的显著性（p=0.2276）和偏相关系数显著性（p=0.228，2种软件的小数精确度不一样）是一样的，同理x2也是一样的。

表明可以直接通过回归系数的显著性来判断两者（自变量）与产量（因变量）的密切程度，而不需要通过复杂的偏相关或通径分析来判断，节省了时间，也不必使用SPSS等高级软件，就可以得到与SPSS软件相同的结果。

这是因为回归和相关存在内在关系，相关系数是标准化（去单位）的回归系数[4]，两者检验的可靠性必然是一致的，这对于基层科研工作者和对统计软件不熟悉的研究者提供了一个新的、快速的方法。

3.2对方程求极值二元二次回归方程式（2）是1个开口向下的曲面，因此有个最大值，即得到最高产量时的合理灌水量和施氮量，也就是进行试验的目的。

传统方法为：分别对二元二次回归方程进行x1（施肥）和x2（灌水）的求导，即可得到最高产量为405.8kg/666m2时全生育期中合理灌水量和施氮量分别为307.4m3/666m2和17.8kg/666m2。

而Excel软件提供“规划求解”，在Ex-cel菜单栏中“工具”的“加载宏”命令，然后在“加载宏”对话框中选定“规划求解”，再按“确定”钮（见图1），假定单元格“A1”和“A2”为施肥（N）与灌水（W），在单元格“B1”输入含有施肥（N）和灌水（W）单元格的因变量y（，注意“N”和“W”只需要输入单元格“A1”和“A2”），并将其设为“规划求解”的“目标单元格”，并令其“最大”，设定单元格“A1”和“A2”为可变单元格，然后点“求解”按钮，在单元格“B1”输出产量y的最大值为405.8kg/666m2，在单元格“A1”和“A2”输出施肥量和灌水量分别为17.8kg/666m2和307.4m3/666m2，即全生育期中合理灌水量和施氮量分别为304.80m3·667m-2和23.79kg·667m-2时，得到最高产量为412.06kg·667m-2。

4结束语以二元二次非线性回归为例，介绍了使用Excel 软件解决非线性回归问题的方法，其他诸如一元二次、三元二次以及多元多次非线性回归问题，其方法都是等同的。

SPSS正版软件提供了多元非线性回归模块，其得到的二元二次非线性回归与Excel一样，而且SPSS 软件解决多元非线性回归问题除了应用回归求方程外，还得与偏相关、通径分析等结合才能说清楚多元非线性回归问题，对于一般科技工作者，按照本研究介绍的方法使用Excel就能轻松解决非线性回归问题。

参考文献[1]霍志军,李菊艳,潘晓琳.Excel在农业生物统计分析中的应用[J].现代化农业,2003,(9):28-30.[2]龚江,谢海霞,王海江,等.棉花高产水氮耦合效应研究[J].新疆农业科学,2010,(4):644-648.[3]毛达如.植物营养研究方法(第2版)[M].北京,中国农业大学出版社,2005.[4]盖钧镒.试验统计方法[M].北京:中国农业出版社,2000.表5偏相关系数。