基于EXCEL规划求解一键选优树高曲线模型

蒋兴明

摘要：描述林分胸径与树高关系的树高曲线模型很多，不同树种林分的最优树高曲线模型不同，Excel规划求解可求解非线性模型的参数，获得更优的参数估计，但一次只能求解一个模型，为了提高效率，减少重复操作，基于Excel的VBA，利用一个按钮调用规划求解（Solver.xlam）程序实现一键选出最优的模型。

关键词：Excel函数;规划求解;树高曲线模型;评价指标;VBA代码

中图分类号：TP317      文献标识码：A

文章编号：1009-3044（2020）17-0072-04

1 背景

林分调查因子之间，存在着一定的相关关系，其中树高和胸径是林分生长的两个重要指标，二者间成正相关，胸径-树高模型在林业生产实践与科学研究中具有极为重要的作用[1]。树高和胸径的相关曲线称为树高曲线，描述树种树高曲线的模型为树高曲线模型，树高曲线模型并不是唯一的，研究时必须根据树种林分类型的不同选取不同的树高曲線模，最适合树种林分的树高曲线是最优树高曲线模型[2]，如何选取树种的最优树高曲线模型则极为关键。

一些学者研究了各种树高模型在不同林分的最优拟合[3-8]，还有其他学者使用其他多个模型，如果对这些模型逐个单独计算拟合，从中选优，计算工作量必定很大。限于篇幅，本文仅选用其中的25个模型（见图1-②、图4）作示例，采用Excel（2016版）的规划求解（Solver.xlam）进行一键批量计算完成选优，提高了计算选优的效率。

2 规划求解工具介绍

规划求解是数学建模中的一项重要内容，其原理是在符合约束条件的要求下不断调整可变量，从而改变与可变量关联的其他数据，进一步影响目标值，最终使目标值达到设置的控制值，即为达到最优解[9-10]。

Excel的“规划求解”工具，是在满足工作表上其他公式（约束条件）单元格的值时，可通过更改其他单元格（决策变量）的值来确定目标单元格中公式（目标公式）的优化（最大或最小）值，“规划求解”工具能进行非线性方程的拟合获得更优的参数估计， 而且操作简单，易于掌握[11-12]。

“规划求解”工具在使用前需要先加载。打开Excel后，转到“文件”选项卡的“选项”对话框，选择左侧列表的“加载项”，单击“转到”按钮打开“加载项”对话框，勾选“规划求解加载项”，加载好的规划求解工具在“数据”选项卡的分析组中。

3 创建胸径树高整理表

将Sheet1命名为“胸径树高整理表”，用来录入林分调查所得的胸径-树高整理结果。本文使用《测树学》第3版（孟宪宇）表2-13测高记录表的数据为例（见图1-①）。为了方便引用数据，利用“名称管理器”对C列（胸径）和D列（树高）分别定义名称如下：

名称                       引用位置

胸径        =OFFSET（胸径树高整理表！$C：$C，2，，COUNT（胸径树高整理表！$C：$C））

树高        =OFFSET（胸径树高整理表！$D：$D，2，，COUNT（胸径树高整理表！$D：$D））

4 树高曲线模型规划求解

根据规划求解的“使无约束变量为非负数”复选框，将树高曲线模型分为两类：一类是模型中参数不为非负数的模型（此称为参数非负模型，见图1-②），另一类则是参数无约束条件的模型（此称为无约束模型，见图4）。

4.1 参数非负模型规划求解

4.1.1 创建参数非负模型计算工作表

将Sheet2命名为“参数非负模型”，用于规划求解参数非负模型的参数值及模型评价的指标值（见图1-②、④）的处理，现在先将1～2行和A～D列的内容先完成，其他稍后处理。

4.1.2 创建参数非负模型预测值计算工作表

将Sheet3命名为“参数非负模型预测值”，该工作表用于计算各个参数非负模型的预测值（见图2）。各列中计算公式按模型结构式编辑（见表1）。

2.完成公式首行输入后进行快速填充各列，公式中的符号均为英文输入法的符号，下同。

3.在“参数非负模型”工作表对参数赋初值后按F9可刷新计算。 ]

4.1.3 定义参数非负模型预测值名称

为了方便引用各个模型的预测值，利用“名称管理器”（Ctrl+F3）分别对各个模型的预测值进行名称定义（见表2）。

对COUNT（）函数计数结果减1，是因为第一行为数字编号。“序号_非负模型”是对“参数非负模型”工作表中A列“序号”进行定义名称，为后面对模型计数使用。

4.1.4 参数非负模型评价指标计算

现在再来处理“参数非负模型”工作表中的模型评价指标。不同模型规划求解后，拟合质量如何，要通过评价指标来进行判断，篇幅所限，在此只选5个评价指标作为参考（见图1-⑤，其中残差平方和作为参数估计时的目标指标，各个评价指标的计算公式见表3。

当评价指标计算公式首行输完后，向下快速填充，但要逐行修改公式中的预测值001～预测值010，使预测值编号与模型序号对应。若出现“#REF！”等出错提示，是因为参数单元格未赋初值。

4.1.5 参数非负模型参数赋初值

作为计算模型参数的单元格若不赋初值，规划求解时默认为零，但有些模型不赋初值则会出现预测值和评价指标计算出错提示，尤其是残差平方和（SSE）公式计算出错，就无法进行规划求解，因为残差平方和（SSE）是规划求解的目标公式，此时就需要对参数赋予适当的初值[13-14]。初值的选择不同，会影响规划求解收敛的速度[15]和精度，有时还会出现异常结果[16]。赋初值后按F9键刷新计算。

参考文献：

[1] 杜志， 甘世书. 基于BP神经网络的杉木和马尾松树高曲线模型研究[J]. 中南林业调查规划， 2017， 36（4）： 36-39.

[2] 陈立莉. 树种树高曲线模型的研究[D]. 哈尔滨： 东北林业大学， 2013.

[3] 孟宪宇. 测树学[M]. 北京： 中国林业出版社， 2006.

[4] 罗佳， 戴成栋， 田育新， 等. 碳汇林主要建群种树高和胸径生长模型构建[J]. 湖南林业科技， 2016， 43（6）： 46-50.

[5] 王明亮， 李希菲. 非线性树高曲线模型的研究[J]. 林业科学研究， 2000， 13（1）： 74-79.

[6] 胥辉， 全宏波， 王斌. 思茅松标准树高曲线的研究[J]. 西南林学院学报， 2000， 20（2）： 74-77.

[7] 向玮， 吕勇， 邱林. 湖南黄丰桥林场杉木树高曲线模拟研制[J]. 中南林业调查规划， 2007， 26（1）： 16-18.

[8] 尹惠妍， 张志伟， 杨小林， 等. 西藏林芝云杉树高曲线模型研究[J]. 江苏农业科学， 2020， 48（2）： 150-154.

[9] 陈鹏程， 刘建新. Excel规划求解在陶瓷配方设计中的应用[J]. 佛山陶瓷， 2019， 29（9）： 44-46.

[10] 曹樹国. Excel在非线性方程求解过程中的应用[J]. 计算机与信息技术， 2007， 15（9）： 80-81.

[11] 余亮. 利用Excel软件进行非线性拟合的非编程方法[J]. 微型机与应用， 2000， 19（5）： 16-17.

[12] 贺晓鹏， 贺浩华， 朱昌兰. 用Excel拟合Richards方程[J]. 计算机与农业， 1998（3）： 16-18.

[13] 胡亮. 非线性拟合的初值问题[J]. 吉首大学学报（自然科学版）， 2003， 24（1）： 37-39.

[14] 徐海霞， 任红松， 袁继勇， 等. 用EXCEL及其“规划求解”功能拟合曲线方程[J]. 农业网络信息， 2004（2）： 37-39.

[15] 郑国清， 孙书安， 刘九芬. 纯非线性回归模型参数估计的新方法及其应用[J]. 河南农业大学学报， 1995， 29（2）： 200-204.

[16] 颜清， 彭小平. 工程实验数据的非线性拟合方法[J]. 计算机与应用化学， 2015， 32（3）： 365-368.

【通联编辑：谢媛媛】