基于Excel拟合树高曲线方程自动计算林分平均高

来源:用户上传      作者:蒋兴明

　　摘要：根据森林调查标准地调查法所获得的胸径一树高数据，用Excel拟合动态树高曲线，然后利用Excel的函数提取拟合树高曲线方程中的参数来自动计算林分平均高和径阶平均高，且计算结果会随所选择曲线方程类型的不同以及胸径一树高数据的增减变动均能自动更新计算结果，避免了因数据每次发生变化后都需要重复操作，省时高效。
　　关键词：Excel;树高曲线方程;林分平均高;径阶平均高
　　中图分类号：TP311 文献标识码：A
　　文章编号：1009-3044（2020）13-0247-03
　　1背景
　　林分中林木高度与胸径之间存在着正相关关系，呈现出一定的变化规律，为了反映林分树高与胸径变化规律，以纵坐标为树高，横坐标为胸径，即可建立胸径一树高曲线，即树高曲线，二元材积表法计算林分蓄积量以及二元材积表导算一元材积表时都需要树高曲线。图解法绘制树高曲线的传统方法是用坐标纸手工绘制树高曲线，在树高曲线上查取林分平均所对应的树高即是林分平均高，同样在树高曲线上可查得各径阶对应的径阶平均树高。求林分平均树高还可使用数式法，即利用胸径和树高相关模型。此外可通过专业调查软件完成，但其不够普及和被广泛应用，基于Excel的大众化和强大的数据处理功能，可很方便使用Excel拟合树高曲线。本文以表1胸径一树高数据为例，介绍Excel（2016版）拟合树高曲线方程参数的动态提取及林分平均高及径阶平均高的自动计算，解决了每次数据发生变动都需要重复操作的难题。
　　2拟合树高曲线
　　2.1录入数据
　　新建“林分平均高自动计算”的Excel工作薄，将sheetl工作表命名为“树高曲线”，将胸径一树高调查整理表（见表1）中的数据录入A、B、C三列（见图1）。
　　2.2绘制散点图
　　依据B、c列数据，利用菜单栏的“插人”图表功能，建立胸径一树高的散点图。
　　2.3添加趋势线
　　为散点图添加趋势线，趋势线包括指数、线性、对数、多项式、乘幂、移动平均等6种，勾选“显示公式”和“显示R平方值”，则散点图中并会添加相应的趋势线、回归方程和拟合程度指标R2。
　　2.4选择树高曲线方程
　　R2取值范围在0～l之间，当趋势线的R2等于l或接近1时，其可靠性最高。绘制树高曲线就是以平均胸径为自变量、平均树高为因变量所建立的回归方程，所以选择6种回归方程中Rz最大者作为树高曲线。并将“趋势预测”向前、向后各推延1个周期。
　　2.5设置树高曲线图表格式
　　2.5.1设置水平轴的格式
　　对“坐标轴选项”进行设置，水平轴“边界”的“最小值”与每木调查的起测径阶值相同，“最大值”不做设置，“单位”为径阶距lcm、2cm或4cm，本例设置为2，水平轴刻度值“数字”设为整数，即不保留小数，取消千分位分隔符。垂直（值）轴不做修改。
　　2.5.2设置曲线方程的格式
　　对图表中的曲线方程进行设置，数字类别选择“数字”格式，小数位数为8位或与材积公式参数的小数位数一致，取消千分位分隔符。最后再适当调整曲线方程的字体大小。
　　此外将图表的标题修改为“树高曲线”，添加次要网格线，最后再根据自己的需要设置趋势线的颜色、线宽线型，调整图表的大小和图表的位置。
　　2.6制作动态树高曲线
　　森林调查时，不同的林分，其径阶分布、株数多少是不固定的，为满足不同林分都可使用，需要将树高曲线制作成动态的，让树高曲线可随数据的增减而动态变动。
　　2.6.1定义名称
　　2.6.2动态树高曲线
　　在散点圖表上单击鼠标右键，“选择数据”快捷方式，进入数据源对话框，点选“编辑”打开编辑数据系列对话框，重新编辑修改x轴系列值为“=’林分平均高自动计算.xlsxr！胸径”;Y轴系列值为“=’林分平均高自动计算.xlsx'！树高”。这样，树高曲线即可随着B列和c列的数据增减而变动，实现树高曲线的动态化。
　　3计算平均高
　　此处的平均高仅指林分条件平均高和林分径阶平均高。林分条件平均高是根据林分平均胸径在树高曲线上查得的树高。林分径阶平均高是根据各个径阶中值在树高曲线上所对应的树高，它是二元材积表法计算林分蓄积量的主要因子。传统方法是在手绘树高曲线上查得，费时费劲效率低，精度低还易出错。也可在Excel中利用拟合的曲线方程，代人径阶中值手工输入计算公式计算径阶平均高，但是，胸径一树高数据发生变动则拟合曲线方程的参数值也会发生改变，这时，就得重新输入新的计算公式，不够人性化。若能自动提取曲线方程的参数，则可自动利用曲线方程完成林分条件平均高和各径阶平均高的计算。
　　3.1提取曲线方程R2值和参数
　　3.1.1提取R2值
　　注：对提取的值用ROUNDO函数四舍五入保留8位小数;公式中的符号均为英文输入法状态下的符号（下同），否则会出错。公式中的“树高”是c列树高的定义名称，“胸径”是B列胸径的定义名称，R2值提取结果见图2-②。图1中，E19-J19也是利用上述提取公式所得。
　　3.1.2提取参数
　　二次多项式（简称二次式，下同）有A、B、C三个参数，三次多项式（简称三次式，下同）有A、B、C、D四个参数，其他曲线方程只有A和B两个参数。各参数提取结果见图2一③，提取曲线方程参数的公式如下（保留8位小数）：
　　3.1.3列示曲线公式
　　用字符连接符（&）将所提取的参数按曲线方程表达式的形式连接成公式形式。公式连接后的结果见图2一④，各个曲线
　　至此，完成了平均高的自动计算。使用时，先录入胸径一树高调查结果，之后在图表中双击趋势线打开“趋势线选项”，选择恰当的曲线作为拟合的树高曲线，然后，在D17单元格的下拉列表中选择相应的曲线类型即可计算林分条件平均高和径阶平均高。
　　4结论与讨论
　　通过定义“树高”和“胸径”名称，实现拟合的树高曲线可随数据的增减变化而实时变动，利用Excel函数提取出拟合方程的参数，可得到了树高曲线方程，这样就可利用树高曲线方程实现林分的平均高和径阶平均高计算的自动化，解决了因不同林分调查的数据不同，重复拟合树高曲线和计算的麻烦，提高了计算效率，在生产实践中具有现实意义。
转载注明来源:https://www.xzbu.com/8/view-15267122.htm