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

巧用Excel解决多元非线性回归分析 ?研究与开发? 农业网络信息 AGRICULTURE NETWORK INFORMATION 2011 年第 1 期 巧用 Excel 解决多元非线性回归分析 龚 江， 石培春， 李春燕 (石河子大学农学院，石河子 832003) 摘 要:非线性回归是回归分析的重要内容和难点，而多元非线性回归在农业生产中有重要的应用。应用 Excel“工具” 菜单“数据分析”选项中的“回归”分析工具，以二元二次非线性回归为例，阐述了用 Excel 做多元非线性回归的详细 过程，并与 SPSS 软件做的结果进行比较，证明使用 Excel 做多元非线性回归完全可行，且操作简单、易行，并就方程的 统计意义进行了分析。 关键词:Excel;多元;非线性回归 中图分类号:S126 文献标识码:A 文章编码:1672,6251 (2011 01,0046,03 ) Application of Excel Software in Multi-nonlinear Regress Analysis GONG 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， application of which was the important in agriculture production. In this paper, with the multi-linear regression analysis by “ data analysis”tool of Microsoft Excel as example, a 2 times nonlinear regress analysis’ process was described, and the results showed that the output was same s with SPSS software， then the statistical significance of the 2 times nonlinear regress equation was analyzed. Key words: Excel software; multi analysis; nonlinear regress 大量统计软件的问世， 使统计分析在科研领域迅 速普及应用。 众所周知， 统计软件如 SAS 、 SPSS 等虽 然功能强大， 但较难掌握， 并且市面上出售的统计软 件大都是盗版软件， 不但运行结果的可靠性无法保 证， 也侵犯了知识产权。 对于大多数科研工作者， 尤 其是基层的科研工作者来说， 经常使用的统计软件与 涉及的方法也很有限， 主要集中在方差分析、 回归与 相关分析等少数几种方法上， 并不需要包罗万象、 功 能强大的统计软件。 而正版统计软件也由于其价格不 菲， 难以被大多数科研工作者承受。 Excel 是 Office 家 族的一个成员， 是功能强大、 使用方便的电子表格式 数据综合管理与分析系统， 可用来 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 和整理试验数 据。 另外， Excel 也具备一些统计运算的功能 [1] “加 载 宏 ” 命 令 ， 在 “加 载 宏 ” 对 话 框 中 选 定 “分 析 工 具 库 ”， 再 按 “确 定 ” 钮 (见 图 1 )， “数 据 分 析 ” 这 一 项 就 出 现 在 工 具 菜 单 栏 中 ( 见 图 2 ) 。 若 Excel “工 具 ” 中 的 “加 载 宏 ” 没 有 “ 分 析 工 具 库 ” ， 则 将 Office Excel 中 文 专 业 版 光 盘 放 入 光 驱 中 ， 运 行 “ 安 装” 程序， 点击 “添加 / 删除” 按钮， 出现 “ Microsoft Office 维 护 ” 对 话 框 后 ， 在 “ 选 项 ” 一 栏 中 ， 选 中 “Microsoft Excel ”， 然后单击 “更改选项” 按钮， 出现 新的对话框， 再 选 中 “加 载 宏 ” 继 续 单 击 “更 改 选 项” 按钮， 在新的对话框中选取分析工具库， 确定即 可， 之后按照安装向导的指示即可顺利安装。 ， 若能 巧妙地使用， 也可以解决一些较为复杂的农业统计运 算问题， 如多元非线性回归的问题等， 其统计结果和 SPSS 软件结果一致。 1 Excel 统计功能的安装 单 击 Microsoft Excel 中 文 版 菜 单 栏 中 “工 具 ” 的 图 1 Excel 统计功能的安装 注: 新疆石河子大学农学院一类课程 “生物统计学” 支助。 作者简介: (1976- ， 硕士， 龚江 ) 男， 讲师， 研究方向: 生物统计教学和植物营养。 收稿日期: 2010-12-10 — 46 — 《农业网络信息》2011 年第 1 期 研究与开发 图 2 Excel“数据分析”功能 2 Excel 进行多元非线性回归 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<0.05 )， 表 明 存 在 真 实 的 (显 著的) 五元一次线性回归方程， 即存在真实的二元二 次非线性回归方程。 表 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 ， 因为 两者的单位和变化范围 (量纲) 不同， 不能直接通过 — 47 — 《农业网络信息》2011 年第 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 软 件 相 同 的 结 果 。 这 是 因 为 回 归 和 相关存在内在关系， 相关系数是 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 化 (去单位) 的 回归系数 [4]， 两者检验的可靠性必然是一致的， 这对于 基层科研工作者和对统计软件不熟悉的研究者提供了 一个新的、 快速的方法。 表 5 偏相关系数 307.4m3/666m2， 即全生育期中合理灌 水量和施氮量分 别为 304.80m3 667m-2 和 23.79kg 667m-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]. 现代化农 业 ,2003,(9):28-30. [2] 龚江 , 谢海霞 , 王海江 , 等 . 棉花高产水氮耦合效应研究 [J]. 新疆农 业科学 ,2010,(4):644-648. 17.8kg/666m2。 而 Excel 软件提供 “规划求解”， 在 Excel 菜单栏中 “工具” 的 “加载宏” 命令， 然后在 “加 载 宏 ” 对 话 框 中 选 定 “规 划 求 解 ”， 再 按 “确 定 ” 钮 [3] 毛达如 . 植物营养研究方法 ( 第 2 版 ) [M]. 北京 , 中 国 农业 大学 出 版社 ,2005. [4] 盖钧镒 . 试验统计方法 [M]. 北京 : 中国农业出版社 ,2000. — 48 — ?研究与开发? 农业网络信息 AGRICULTURE NETWORK INFORMATION 2011 年第 1 期 巧用 Excel 解决多元非线性回归分析 龚 江， 石培春， 李春燕 (石河子大学农学院，石河子 832003) 摘 要:非线性回归是回归分析的重要内容和难点，而多元非线性回归在农业生产中有重要的应用。应用 Excel“工具” 菜单“数据分析”选项中的“回归”分析工具，以二元二次非线性回归为例，阐述了用 Excel 做多元非线性回归的详细 过程，并与 SPSS 软件做的结果进行比较，证明使用 Excel 做多元非线性回归完全可行，且操作简单、易行，并就方程的 统计意义进行了分析。 关键词:Excel;多元;非线性回归 中图分类号:S126 文献标识码:A 文章编码:1672,6251 (2011 01,0046,03 ) Application of Excel Software in Multi-nonlinear Regress Analysis GONG 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， application of which was the important in agriculture production. In this paper, with the multi-linear regression analysis by “ data analysis”tool of Microsoft Excel as example, a 2 times nonlinear regress analysis’ process was described, and the results showed that the output was same s with SPSS software， then the statistical significance of the 2 times nonlinear regress equation was analyzed. Key words: Excel software; multi analysis; nonlinear regress 大量统计软件的问世， 使统计分析在科研领域迅 速普及应用。 众所周知， 统计软件如 SAS 、 SPSS 等虽 然功能强大， 但较难掌握， 并且市面上出售的统计软 件大都是盗版软件， 不但运行结果的可靠性无法保 证， 也侵犯了知识产权。 对于大多数科研工作者， 尤 其是基层的科研工作者来说， 经常使用的统计软件与 涉及的方法也很有限， 主要集中在方差分析、 回归与 相关分析等少数几种方法上， 并不需要包罗万象、 功 能强大的统计软件。 而正版统计软件也由于其价格不 菲， 难以被大多数科研工作者承受。 Excel 是 Office 家 族的一个成员， 是功能强大、 使用方便的电子表格式 数据综合管理与分析系统， 可用来记录和整理试验数 据。 另外， Excel 也具备一些统计运算的功能 [1] “加 载 宏 ” 命 令 ， 在 “加 载 宏 ” 对 话 框 中 选 定 “分 析 工 具 库 ”， 再 按 “确 定 ” 钮 (见 图 1 )， “数 据 分 析 ” 这 一 项 就 出 现 在 工 具 菜 单 栏 中 ( 见 图 2 ) 。 若 Excel “工 具 ” 中 的 “加 载 宏 ” 没 有 “ 分 析 工 具 库 ” ， 则 将 Office Excel 中 文 专 业 版 光 盘 放 入 光 驱 中 ， 运 行 “ 安 装” 程序， 点击 “添加 / 删除” 按钮， 出现 “ Microsoft Office 维 护 ” 对 话 框 后 ， 在 “ 选 项 ” 一 栏 中 ， 选 中 “Microsoft Excel ”， 然后单击 “更改选项” 按钮， 出现 新的对话框， 再 选 中 “加 载 宏 ” 继 续 单 击 “更 改 选 项” 按钮， 在新的对话框中选取分析工具库， 确定即 可， 之后按照安装向导的指示即可顺利安装。 ， 若能 巧妙地使用， 也可以解决一些较为复杂的农业统计运 算问题， 如多元非线性回归的问题等， 其统计结果和 SPSS 软件结果一致。 1 Excel 统计功能的安装 单 击 Microsoft Excel 中 文 版 菜 单 栏 中 “工 具 ” 的 图 1 Excel 统计功能的安装 注: 新疆石河子大学农学院一类课程 “生物统计学” 支助。 作者简介: (1976- ， 硕士， 龚江 ) 男， 讲师， 研究方向: 生物统计教学和植物营养。 收稿日期: 2010-12-10 — 46 — 《农业网络信息》2011 年第 1 期 研究与开发 图 2 Excel“数据分析”功能 2 Excel 进行多元非线性回归 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<0.05 )， 表 明 存 在 真 实 的 (显 著的) 五元一次线性回归方程， 即存在真实的二元二 次非线性回归方程。 表 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 ， 因为 两者的单位和变化范围 (量纲) 不同， 不能直接通过 — 47 — 《农业网络信息》2011 年第 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.79kg 667m-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]. 现代化农业 ,2003,(9):28-30. [2] 龚江 , 谢海霞 , 王海江 , 等 . 棉花高产水氮耦合效应研究 [J]. 新疆农 业科学 ,2010,(4):644-648. 17.8kg/666m2。 而 Excel 软件提供 “规划求解”， 在 Excel 菜单栏中 “工具” 的 “加载宏” 命令， 然后在 “加 载 宏 ” 对 话 框 中 选 定 “规 划 求 解 ”， 再 按 “确 定 ” 钮 [3] 毛达如 . 植物营养研究方法 ( 第 2 版 ) [M]. 北京 , 中 国 农业 大学 出 版社 ,2005. [4] 盖钧镒 . 试验统计方法 [M]. 北京 : 中国农业出版社 ,2000. — 48 —