2023年ExECL表格软件在样地调查计算中的应用.docx

天道酬勤 EXECL表格软件在样地调查计算中的应用 苏振海 摘 要：本文采用FORECAST、LOOKUP和LN等函数编写了样地调查计算模板，并通过设置数据有效性和保护工作表等功能，设置了数据输入的有效性检验，将模板需要输入的内容降到最低，不再需要复制粘贴过程，有效提高了工作效率，降低了误输入造成的错误，通过实验取得了良好的效果。 关键词：样地调查;EXECL;计算 中图分类号：S159       文献标识码：A DOI：10.19754/j.nyyjs.20230515023 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单元格输入公式：=A5xA5xB5或=A5^2xB5或POWER〔A5，2〕xB5均可，以下至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$40x〔A4^$C$41〕xD4^$C$42〞E5～E36复制公式，求算处各整化径阶的单株蓄积。 3.5.2 样地蓄积计算 在F4中输入公式“=E4xB4〞F5～F36复制公式，求出各径阶蓄积合计。在F39单元格中输入公式“=SUM〔F4∶F36〕〞，求出样地蓄积合计。 3.5.3 小班蓄积计算 在D41和D42单元格中分别输入样地总面积〔hm2〕和小班总面积〔hm2〕，在F41中输入“=F39/D41〞求出小班每公顷蓄积，在I41中输入“=E41xF41〞，求出小班蓄积。同样也可以简单地算出每公顷株数、小班总株数、每公顷断面积等指标。 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].农民致富之友，2023〔8〕：205. [2] 应启围.基于Excel的伐区样地调查数据计算方法[J].林业调查规划，2023〔5〕：4. [3] 王雍树.EXCEL软件在林木蓄积计算中的应用[J].绿色科技，2023〔7〕：216. 〔责任编辑 周康〕