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

巧用Excel解决多元非线性回归分析 巧用 Excel 解决多元非线性回归分析 龚 江，石培春，李春燕 (石河子大学农学院，石河子 832003) 摘 要:非线性回归是回归分析的重要内容和难点，而多元非线性回归在农业生产中有重要的。应应用用 Exce“l工具” 菜单数据分析选项中的回归分析工具，以二元二次非线性回归为例，阐述了 用Excel 做多元非线性回归的详细 “”“” 过程，并与 SPSS软 件做的结果进行比较，证明使用 Excel 做多元非线性回归完全可行，且操作简、单易行，并就方程的 统计意义进行了分析。 关键词:Exce;l多元;非线性回归 中图分类号:S126 文献标识码:A 文章编码:1672,625(12011)01,0046,03 Application of Excel Software in Multi-nonlinear Regress Analysis GONG Jiang, SHI Peichun, LI Chunyan (Agriculture Collegofe Shihezi Univerity, Shihezi 832003) Abstract: Nonlinear regress analysis wdiaffs icult a and significant methodof regress analys，isthe application of which was importnta in agriculture production. In this paper,w ith the multi-linear regression analybysis “ data analysis”tool of Microsoft Excel as example, a 2 times nonlinear regressanal ysis’s process was described, the and results showedtha t the output wassame with SPSS softwa，then re the sttistaical significanceof the 2 times nonlinear regress eqution awas naalyzed. Key words: Excel software;multi analysis; nonlinear re gress 大量统计软件的问世, 使统计分析在科研领域迅命令, 在 “加载宏” 对话框中选定 “ 分析“加载宏” 速普及应用。 众所周知, 统计软件如 SAS、 SPSS等 虽 工具库” , 再按 “ 确定” 钮 , 见图 1, , “ 数据分析” 然功能强大, 但较难掌握, 并且市面上出售的统计软 这一项就出现 在工具菜单栏中 , 见 图 2, 。 若 Excel 件大都是盗版软件 , 不但运行结果的可靠性 无 法保 “ 工具” 中的 “ 加载宏” 没 有 “ 分 析 工 具 库 ” , 则 将证, 也侵犯了知识产权。 对于大多数科研工作者, 尤 OfficeE xcel 中文专业版光盘放入 光 驱中 , 运 行 “ 安 其是基层的科研工作者来说, 经常使用的统计软件与 装” 程序, 点击 “添加/删除” 按钮, 出现 “Microsoft 涉及的方法也很有限, 主要集中在方差分析、 回归与 Office维 护 ” 对 话 框 后 , 在 “ 选 项 ” 一 栏 中 , 选 中 相关分析等少数几种方法上, 并不需要包罗万象、 功 “MicrosofEt xcel”, 然后单击 “更改选项” 按钮, 出现 能强大的统计软件。 而正版统计软件也由于其价格不 新的对话框 , 再 选 中 “ 加 载 宏 ” 继 续 单 击 “ 更 改 选 菲, 难以被大多数科研工作者承受。按钮, 在新的对话框中选取分析工具库, 确定即项”Excel 是 Office家 可, 之后按照安装向导的指示即可顺利安装。 族的一个成员, 是功能强大、 使用方便的电子表格式 数据综合管理与分析系统, 可用来记录和整理试验数 [1] , 若能据。 另外, Excel 也具备一些统计运算的功能 巧妙地使用, 也可以解决一些较为复杂的农业统计运 算问题, 如多元非线性回归的问题等, 其统计结果和 SPSS软件结果一 致。 1 Exce 统计功能的安装l 单击 MicrosoftE xcel 中文版菜单栏中 “ 工具” 的 图 1 Excel统 计功能的安装 图 2 Excel数据分析功能 “” 2 Excel 进行多元非线性回归 Excel “数据分析” 功能中的回归为线性回归, 直 接应用并不能解决非线性回归的问题, 需要将数据进 行线性转化才能进行。 如研究水肥耦合效应对棉花产 [2]量的影响:见表 1: , 可用一个包含交互项的二元二次 数学模型来描述: , 其中, y 为回归值, 即作物产量; x, x为两个自变 12 量, 即对作物的施肥和灌水量。 由于在施肥和灌水两 种因素的影响下, 其作物产量是这两个不同因素共同 作用的结果, 所以因变量与两种因素x 和 x间的函数 1 2 [3]关系可用二次效应曲面来描述。 :1: 将其还原回去即得二元二次回归方程为: :2: 其 中 : y 为 籽 棉 产 量 , N 为 施 氮 量 , W 为 灌 水 [2]可将二元二次非线性回归模型 量。 此方程与 SPSS软件所得到的结果完全一 致。 转 变 成 一 个 五元一次线性回归模型 3 方程分析 , 即 由 2 个 自 变 量 3.1 方程的意义 变成 5 个自变量, 其数据格式如表2 所示。 式 :2: 中的一次项 W 和 N 的系数为正表明单独 打开 Excel “ 数据分析” 功能中的回归 , 把 y 输 增加灌水量 :t=8.78**: 和施氮量 :t=1.51*, 多元回 入到因变量 “y 值输入区域” , 将 “x到 x” 一起输入 归显著水平可放大到p =0.25: 对棉花籽棉产量有增加 1 5 到自变量 “x 值输入区域” , 点确定输出结果。 作用; 交互项的系数为-0.0029 :t=-0.54, p>0.25, 不 表 3 为线性回归分析的方差分析表, F 值为 30.6, 显著: , 表示灌水量和施氮量的交互作 用是微弱负效 其显著水平为0.009 :p<0.05: ,表明存在真实 的 : 显 应; 二次项的系数为负, 说明过多的水氮投入并不利 著的: 五元一次线性回归方程, 即存在真实的二元二 于棉花增产。 次非线性回归方程。 进一步分析, 两因素相比灌水量对棉花产量的影 表 4 为 Excel 软件五元一次线性回归方程回归系 响大于施氮量的影响 : 灌水量 t > 施氮量 t: , 注 意并 数的检验表, 通过表 4 得到棉花产量的五元一次线性 非因为灌水的系数7.111 大于施肥的系 数 3.064, 因为 回归方程为: 两者的单位和变化范围 :量纲: 不同, 不能直接通过 系数大小来判断哪个因素对产量的影响大、 与产量关 , 见图 1,, 假 定 单 元 格 “A1” 和 “A2” 为 施 肥 ,N, 与 灌 水系更密切 ,一般科技工作者误认为系数大的对产量影 ,W,,在单元格“B1”输入含有施肥,N,和灌水,W,单元 响大,, 这需要通过 SPSS偏相关 或通径分析来判断。 格的因变量 y, , 通过 SPPS软件进行偏相关分 析 ,见表 5,, x,施肥, 注 意 “ N” 和 “ W” 只 需 要 输 入 单 元 格 “ A1” 和 1 与 y 偏相关系数 为 0.6578 , 显著性 p=0.22,8 , x, 灌 “A2”,, 并将其设为 “规划求解” 的 “目标单元格”, 2 水, 与 y 偏相关系数为 0.9811 ,p=0.03,, 相比 x显著 并令其 “最大”, 设定单元格 “A1” 和 “A2” 为可变 2 性更高, 表明灌水与棉花产量更密切, 仔细对比回归 单元格, 然后点 “求解” 按钮, 在单元格 “B1” 输出 2x的系数的显著性 ,p=0.2276, 和偏相关系数显著性 产量 y 的最大值为 405.8kg/666m, 在单元格 “A1” 和 1 2 “ A2” 输 出 施 肥 量 和 灌 水 量 分 别 为 17.8kg/666m和 ,, 种软件的小数精确度不一样, 是一样的,p=0.2282 32307.4m/666m, 即全生育期中合理灌水量和施氮量分 同理 x也是一样的。 表明可以直接通过回归系数的显 2 32 2 --别为 304.80m667m和 23.79kg667m时, 得到最高 ??著性来判断两者 ,自变量, 与产量 ,因变量, 的密切 2-产量为 412.06kg667m。 ?程度, 而不需要通过复杂的偏相关或通径分析来判 断, 节省了时间, 也不必使用 SPSS等高级软 件, 就 4 结束语 这是因为回归和可以得到与 SPSS软件相同的结 果。 以二元二次非线性回归为例,介绍了使用 Excel 相关存在内在关系, 相关系数是 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 化 ,去单位, 的软件 解决非线性回归问 题 的 方 法 , 其 他 诸 如 一 元 二[4]回归系数, 两者检验的可靠性必然是一致的, 这对于 次、 三元二次以及多元多次非线性回归问题, 其方法 基层科研工作者和对统计软件不熟悉的研究者提供了 都是等同的。 一个新的、 快速的方法。 其SPSS正版软件提供了多元非线性回归模 块, 得到的二元二次非线性回归与 Excel 一样, 而且 SPSS 表 5 偏相关系数 软件解 决多元非线性回归问题除了应用回归求 方 程 外, 还得与偏相关、 通径分析等结合才能说清楚多元 非线性回归问题, 对于一般科技工作者, 按照本研究 介绍的方法使用 Excel 就能轻松解决非线性回归问题。 3.2 对方程求极值 二元二次回归方程式 ,2, 是 1 个开口向下的曲 面, 因此有个最大值, 即得到最高产量时的合理灌水参考文献 量和施氮量, 也就是进行试验的目的。 传统方法为, [1] 霍志军, 李菊艳, 潘晓琳.Excel 在农业生物统计分析中的应用 分别对二元二次回归方程进行x ,施肥, 和 x,灌水, [J].现代化农业,2003,(9):28-30. 1 2 2 的求导, 即可得到最高产量为4 05.8kg/666m时全生育 [2] 龚江,谢海霞,王海江,等.棉花高产水氮耦合效应研究[J].新疆农 32 期中合理灌水量和 施 氮 量 分 别 为 307.4m/666m和 业科学,2010,(4):644-648. 217.8kg/666m。 而 Excel 软件提供 “规划求解”, 在 Ex- [3] 毛达如.植物营养研究方法(第 2 版) [M].北京,中国农业大学出 cel 菜单栏中 “工具” 的 “加载宏” 命令, 然后在 “加 版社,2005. 载宏” 对话框中选定 “规划求解”, 再按 “ 确定” 钮 [4] 盖钧镒.试验统计方法[M].北京:中国农业出版社,2000.