2023年多元非线性回归【巧用Excel解决多元非线性回归分析】.docx

2023年多元非线性回归【巧用Excel解决多元非线性回归分析】 ·探讨与开发· 农业网络信息 AGRICULTURENETWORKINFORMATION 2023年第1期 巧用Excel解决多元非线性回来分析 龚江，石培春，李春燕 (石河子高校农学院，石河子832023) 摘 要：非线性回来是回来分析的重要内容和难点，而多元非线性回来在农业生产中有重要的应用。应用Excel“工具” 菜单“数据分析”选项中的“回来”分析工具，以二元二次非线性回来为例，阐述了用Excel做多元非线性回来的具体过程，并与SPSS软件做的结果进行比较，证明运用Excel做多元非线性回来完全可行，且操作简洁、易行，并就方程的统计意义进行了分析。 关键词：Excel；多元；非线性回来中图分类号：S126 文献标识码：A 文章编码：16726251（2023）01004603 ApplicationofExcelSoftwareinMulti-nonlinearRegressAnalysis GONGJiang,SHIPeichun,LIChunyan (AgricultureCollegeofShiheziUniverity,Shihezi832023) Abstract:Nonlinearregressanalysiswasadifficultandsignificantmethodofregressanalysis，theapplicationofwhichwasimportantinagricultureproduction.Inthispaper,withthemulti-linearregressionanalysisby“dataanalysis”toolofMicrosoftExcelasexample,a2timesnonlinearregressanalysissprocesswasdescribed,andtheresultsshowedthattheoutputwassamewithSPSSsoftware，thenthestatisticalsignificanceofthe2timesnonlinearregressequationwasanalyzed.Keywords:Excelsoftware;multianalysis;nonlinearregress 大量统计软件的问世，使统计分析在科研领域快速普及应用。众所周知，统计软件如SAS、SPSS等虽然功能强大，但较难驾驭，并且市面上出售的统计软件大都是盗版软件，不但运行结果的牢靠性无法保证，也侵扰了学问产权。对于大多数科研工作者，尤其是基层的科研工作者来说，常常运用的统计软件与涉及的方法也很有限，主要集中在方差分析、回来与相关分析等少数几种方法上，并不须要应有尽有、功能强大的统计软件。而正版统计软件也由于其价格不菲，难以被大多数科研工作者承受。Excel是Office家族的一个成员，是功能强大、运用便利的电子表格式数据综合管理与分析系统，可用来记录和整理试验数据。另外，Excel也具备一些统计运算的功能 1 “加载宏”吩咐，在“加载宏”对话框中选定“分析工具库”，再按“确定”钮（见图1）， “数据分析” 这一项就出现在工具菜单栏中（见图2）。若Excel“工具”中的“加载宏”没有“分析工具库”，则将 OfficeExcel中文专业版光盘放入光驱中，运行“安装”程序，点击“添加/删除”按钮，出现“Microsoft Office维护”对话框后，在“选项”一栏中，选中“MicrosoftExcel”，然后单击“更改选项”按钮，出现新的对话框，再选中“加载宏”接着单击“更改选项”按钮，在新的对话框中选取分析工具库，确定即可，之后根据安装向导的指示即可顺当安装。 ，若能 奇妙地运用，也可以解决一些较为困难的农业统计运算问题，如多元非线性回来的问题等，其统计结果和 SPSS软件结果一样。 1Excel统计功能的安装 单击MicrosoftExcel中文版菜单栏中“工具”的 图1Excel 统计功能的安装 注：新疆石河子高校农学院一类课程“生物统计学”支助。 作者简介：龚江（1976-），男，硕士，讲师，探讨方向：生物统计教学和植物养分。收稿日期：2023-12-10 农业网络信息2023年第1期 探讨与开发 图2Excel“数据分析”功能 2Excel进行多元非线性回来 Excel“数据分析”功能中的回来为线性回来，直 接应用并不能解决非线性回来的问题，须要将数据进行线性转化才能进行。如探讨水肥耦合效应对棉花产量的影响2（见表1），可用一个包含交互项的二元二次数学模型来描述： ， 其中，y为回来值，即作物产量；x1，x2为两个自变量，即对作物的施肥和灌水量。由于在施肥和灌水两种因素的影响下，其作物产量是这两个不同因素共同作用的结果，所以因变量与两种因素x1和x2间的函数关系可用二次效应曲面来描述3。 （1） 将其还原回去即得二元二次回来方程为： （2） 其中：y为籽棉产量，N为施氮量，W为灌水 可将二元二次非线性回来模型 转变成一个五元一次线性回来模型 ，即由2个自变量 变成5个自变量，其数据格式如表2所示。 打开Excel“数据分析”功能中的回来，把y输入到因变量“y值输入区域”，将“x1到x5”一起输入到自变量“x值输入区域”，点确定输出结果。 表3为线性回来分析的方差分析表，F值为30.6，其显著水平为0.009（p 表4为Excel软件五元一次线性回来方程回来系数的检验表，通过表4得到棉花产量的五元一次线性回来方程为： 量。此方程与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，因为两者的单位和改变范围（量纲）不同，不能干脆通过 农业网络信息2023年第1期 探讨与开发 系数大小来推断哪个因素对产量的影响大、与产量关系更亲密（一般科技工作者误认为系数大的对产量影响大），这须要通过SPSS偏相关或通径分析来推断。通过SPPS软件进行偏相关分析（见表5），x1（施肥）与y偏相关系数为0.6578（显著性p=0.228），x2（灌水）与y偏相关系数为0.9811（p=0.03），相比x2显著性更高，表明灌水与棉花产量更亲密，细致对比回来 （见图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和 x1的系数的显著性（p=0.2276）和偏相关系数显著性（p=0.228，2种软件的小数精确度不一样）是一样的，同理x2也是一样的。表明可以干脆通过回来系数的显著性来推断两者（自变量）与产量（因变量）的亲密程度，而不须要通过困难的偏相关或通径分析来推断，节约了时间，也不必运用SPSS等高级软件，就可以得到与SPSS软件相同的结果。这是因为回来和相关存在内在关系，相关系数是标准化（去单位）的回来系数4，两者检验的牢靠性必定是一样的，这对于基层科研工作者和对统计软件不熟识的探讨者供应了一个新的、快速的方法。 表5偏相关系数 307.4m3/666m2，即全生育期中合理灌水量和施氮量分别为304.80m3··667m-2和23.79kg667m-2时，得到最高产量为412.06kg·667m-2。 4结束语 以二元二次非线性回来为例，介绍了运用Excel 软件解决非线性回来问题的方法，其他诸如一元二次、三元二次以及多元多次非线性回来问题，其方法都是等同的。 SPSS正版软件供应了多元非线性回来模块，其得到的二元二次非线性回来与Excel一样，而且SPSS软件解决多元非线性回来问题除了应用回来求方程外，还得与偏相关、通径分析等结合才能说清晰多元非线性回来问题，对于一般科技工作者，根据本探讨 3.2对方程求极值 二元二次回来方程式（2）是1个开口向下的曲 介绍的方法运用Excel就能轻松解决非线性回来问题。 面，因此有个最大值，即得到最高产量时的合理灌水量和施氮量，也就是进行试验的目的。传统方法为：分别对二元二次回来方程进行x1（施肥）和x2（灌水）的求导，即可得到最高产量为405.8kg/666m2时全生育期中合理灌水量和施氮量分别为307.4m3/666m2和 参考文献 1霍志军,李菊艳,潘晓琳.Excel在农业生物统计分析中的应用J.现代化农业,2023,(9):28-30. 2龚江,谢海霞,王海江,等.棉花高产水氮耦合效应探讨J.新疆农 业科学,2023,(4):644-648. 17.8kg/666m2。而Excel软件供应“规划求解”，在Ex-cel菜单栏中“工具”的“加载宏”吩咐，然后在“加载宏”对话框中选定“规划求解”，再按“确定”钮 3毛达如.植物养分探讨方法(第2版)M.北京,中国农业高校出 版社,2023. 4盖钧镒.试验统计方法M.北京:中国农业出版社,2000.