EXECL表格软件在样地调查计算中的应用

苏振海

摘 要：本文采用FORECAST、LOOKUP和LN等函数编写了样地调查计算模板，并通过设置数据有效性和保护工作表等功能，设置了数据输入的有效性检验，将模板需要输入的内容降到最低，不再需要复制粘贴过程，有效提高了工作效率，降低了误输入造成的错误，通过实验取得了良好的效果。

关键词：样地调查;EXECL;计算

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

DOI：10.19754/j.nyyjs.20200515023

1 研究目的

样地调查在森林规划调查、森林采伐设计、森林资产评估、征占用林地设计中是最常用的方法[1]，特别是对人工林调查设计中，比角规测树方法更为准确。在样地调查中，最重要的是调查样地的活立木蓄积，这就需要在外业计算平均胸径（平方平均数），通过平均胸径确定测高树分布，根据这2个因子利用二元材积表或材积模型进行样地蓄积计算。

2 模板基本功能

记录胸径及树高实测记录;自动计算胸径平方平均值;自动计算各径阶树高及样地平均树高;根据树种自动计算单株蓄积、样地蓄积并推算小班蓄积、小班株数等;对可能产生的错误输入或误修改进行保护、检测并提示。

3 功能实现

3.1 树种选择录入

采用模型计算蓄积时，树种选择决定了蓄积模型的参数，为方便实现自动计算功能和输入，采取下拉菜单形式设计，通过点选就可输入树种。具体方法如下。

以笔者设计的表格样式为例（下同），在A1单元格输入“树种选择”，选择B1作为显示树种名称单元格，选中B1依次选择工具栏中的“数据”-“数据有效性”，在弹出的界面中选择“设置”，在“有效性条件”中选择“允许”-“序列”在“来源”下选择录入表单的位置，如=$N$3∶$N$8（笔者只实验了6个树种）。为方便下面的编制，需在树种名称前增加序列值如“1辽东日落”或“a辽东日落”均可，并且表单按升序排列。后面会解释原因。

3.2 胸径及测高记录

在单元格A3～f3中分别输入“径阶、株数、加权平方和、径阶高、径阶单株蓄积、径阶蓄积合计”;在单元格I3～K3中分别输入“树种、胸径、树高”。A4～A36分别输入6～70的整化径阶值（2cm），如胸径径阶大于70，已经超出蓄积模型适用范围，可采取其它方法近似计算。

3.3 计算胸径平方平均值

在C4单元格输入公式：=A5*A5*B5或=A5^2*B5或POWER（A5，2）*B5均可，以下至C36复制公式即可。

C39中输入“=SUM（C4∶C36）” （合计加权平方），B39输入“=SUM （B4∶B36）” （合计株数），D39输入“= （C39/B39）^0.5” （胸径平方平均值）。

3.4 计算各径阶平均树高

各径阶的平均树高一般采用数式法，拟合树高曲线再通过曲线求算各径阶平均树高[2]。笔者没用采用散点图添加趋势线拟合的方式进行求算，因为拟合后的曲线参数每次都不一样，需要在公式中手动修改参数值才能计算径阶平均高，增加了产生错误录入的机会，也不方便操作。根据多年的制表经验，辽宁省的树高曲线一般都满足对数模型，因此直接将胸径进行对数变换，采用FORECAST函数直接求算回归值。FORECAST函数的用途是根據一条线性回归拟合线返回一个预测值。表达式为FORECAST （x，known_y's，known_x's），其中X为需要进行预测的数据点，Known_y's为因变量数组或数据区域，Known_x's为自变量数组或数据区域。

在L4单元格中输入公式“=LN（J4）”L5～L36复制公式。进行实测胸径变换。

在H4单元格中输入公式“=LN（A4）”H5～H36复制公式。进行整化径阶变换。

在D4单元格中输入公式“=FORECAST（H4，$K$4∶$K$36，$L$4∶$L$36）”D5～D36复制公式。进行径阶平均高求算。

求算结果与拟合曲线后带入整化径阶求算径阶平均高完全一致。如果后期需要打印输出树高曲线及模型，可采用散点图加趋势线方式，本文不再赘述。在E39单元格中输入公式“=FORECAST（LN（D39），K4∶K36，L4∶L36）”，计算样地平均树高。

3.5 计算蓄积

3.5.1 单株蓄积计算

辽宁省二元立木蓄积模型采用的都是山本式V=c0Dc1Hc2参数较少，使用方便。根据3.1中树种选择录入结果自动查找3个参数。方法如下：

在C40单元格中输入公式“=LOOKUP（B1，N3∶N8，O3∶O8）”查取参数c0

在C41单元格中输入公式“=LOOKUP（B1，N3∶N8，P3∶P8）”查取参数c1

在C42单元格中输入公式“=LOOKUP（B1，N3∶N8，Q3∶Q8）”查取参数c2

O3～Q8单元格为录入好的相应树种的二元材积表参数值。

这里LOOKUP函数，要查询一个明确的值的时候，查询列必须按升序排列。这也是为什么在3.1中，要求在树种名称前加“1”、“a”等方便用于排序的前缀的原因。

在E4单元格中输入公式“=$C$40*（A4^$C$41）*D4^$C$42”E5～E36复制公式，求算处各整化径阶的单株蓄积。

3.5.2 样地蓄积计算

在F4中输入公式“=E4*B4”F5～F36复制公式，求出各径阶蓄积合计。在F39单元格中输入公式“=SUM（F4∶F36）”，求出样地蓄积合计。

3.5.3 小班蓄积计算

在D41和D42单元格中分别输入样地总面积（hm2）和小班总面积（hm2），在F41中输入“=F39/D41”求出小班每公顷蓄积，在I41中输入“=E41*F41”，求出小班蓄积。同样也可以简单地算出每公顷株数、小班总株数、每公顷断面积等指标。

4 表格整理

4.1 隐藏不必要显示的单元格

对于计算的中间过程及无用的列可全部隐藏，如G、H、L列和表单列N～Q列。

4.2 数据有效性设置

以测高记录的胸径输入列J列为例。首先用鼠标选择J4-J36选择工具栏中的“数据”-“数据有效性”，在弹出的界面中选择“设置”，在“有效性条件”中选择“允许”-“小数”，在“数据（D）”下拉菜单中选择“介于”，“最小值”输入5，“最大值”输入70.9，然后将“忽略空值”前面的勾去掉。这样设置后，录入者输入不在范围内的数值、其它字符格式内容或清空都会显示错误提示。如果在“出错警告”的“错误信息”中输入“应介于5～70.9”，就会在出错时弹出提示，否则显示系统默认内容。同样方法设置K列，条件为“小数”≥1.3，“忽略空值”前的勾保留;B列条件为“整数”≥0，“忽略空值”前的勾保留;B41单元格，条件为“小数”，介于最小值“0.0001”，最大值“=E41”（表示样地总面积必须大于0且小于等于小班面积），“忽略空值”前面的勾去掉;E41单元格，条件为“小数”，大于或等于最小值“=D41”，“忽略空值”前面的勾去掉。到此，所有需要设置有效性的单元格都设置完毕（其它“错误信息”内容略）。

4.3 保护工作表

将表格中可修改输入的部分填充成绿色，并填写初始值，其余不允许修改的进行保护。保护方法如下。

点击工具中“审阅”-“允许用户编辑区域”在弹出界面上点击“新建”，在新弹出界面上点击“引用单元格”下右侧按钮用鼠标进行选择。点击“确定”返回上级界面点击“保护工作表”可设置密码，和用户可进行的操作[3]。整理前后的界面如图1、2。

5 模板扩充、修改

模板扩充修改主要是修改参数值或是增加新的树种参数，只要将保护状态解除，取消所有隐藏列，然后修改参数表相应位置，并且修改引用该位置的公式选取范围即可，主要涉及B1、C40、C41、C42单元格内的公式。

6 使用的注意事项

测高记录输入胸径列（J列）内不能出现空值，笔者已经预设了“5”作为初始值。因为在变换时采用的是LN函数，所以必须大于“0”的数值格式。后面的树高可以是空值。用来占位的初始值只要是在允许输入范围内，无论是多大都不影响对径阶高的回归预测。实测胸径和树高输入时，位置和大小没有顺序要求，只要求一一对应。

在要删除上次输入內容时，虽然设置了空值的检测，但使用Delete键或使用清除内容功能仍会产生错误，如果产生了错误，只需正常填写（J列必须都有值），不影响计算精度。使用Backspace键删除会提示错误信息。

树种名称的选择可先可后，不影响计算结果。

7 模板优缺点

7.1 模板优点

该模板在使用时只需输入径阶检尺记录、测高记录和样地、小班面积，不需要进行参数修改、复制粘贴等过程，操作简单，方便外业使用;设置了模板保护、误输入检测、提示信息，十分适合基层人员使用;对系统几乎没有要求，只要能使用EXCEL软件或APP都可以使用;扩充修改十分方便，按照本文提示操作，只要基本掌握EXCEL软件的基层人员都可实现。

7.2 模板缺点

不能实现分树种一次计算出各树种蓄积和树种组成功能;树高曲线模拟统一采用对数模型，可能存在不是最优选模型问题;蓄积模型都是山本式，如果出现新的树种蓄积模型不是山本式的，模板的修改还需要一定的技术，基层人员很难自己修改。

8 结束语

本方法主要使用了FORECAST、LOOKUP和LN等函数，将人工输入内容降到最少，有效降低一线设计人员及内业计算人员的劳动强度，同时保证了输入的准确性。经过在全省多个林场内测试，其求算结果与传统方法完全一样，节约了大量的工作时间，取得了良好的效果。

参考文献

[1] 冯家祥，袁金霞，王志勃.森林资源调查应用excel表格函数计算标准地调查因子的方法[J].农民致富之友，2019（8）：205.

[2] 应启围.基于Excel的伐区样地调查数据计算方法[J].林业调查规划，2009（5）：4.

[3] 王雍树.EXCEL软件在林木蓄积计算中的应用[J].绿色科技，2017（7）：216.

（责任编辑 周康）