用Excel 实现二元材积的自动化处理

代远大*，钟鸣

（重庆市涪陵区永胜林场，重庆，408000）

用Excel 实现二元材积的自动化处理

代远大*，钟鸣

（重庆市涪陵区永胜林场，重庆，408000）

二元材积表，是根据每种树木的材积与胸径、树高两个因子的函数关系编制的，也叫标准材积表。在林业调查规划设计工作中，树木的材积，一般通过二元材积表去获取，对二元材积表具有严重的依赖性。既然树种的材积与胸径、树高两个因子存在函数关系，即可通过各树种的材积参数，按统一的公式计算出材积。结合计算机的应用，设计一个Excel工作薄文档，建立各树种的材积参数表，可以实现材积的自动化生成，完全摆脱对物理数表的依赖。

二元材积表；Excel；自动化处理；设计研究

引言

林业属于工程系列的一个重要技术类别，需要开展调查规划、科学实验、科学推广等工作，涉及大量的数据运算处理，二元材积的计算就是其中的一项重要工作。随着计算机应用的普及，通过Microsoft Office Excel电子表格的简单设计，可以实现二元材积自动化计算处理，极大地提高工作效率。

1 需求分析

在林业调查规划设计工作中，树木材积的计算是一项重要内容。通过外业调查，测得各树种单株树木的树高、胸径，在内业工作中，要对每株树木的材积进行计算统计。

传统的方法是：分别树种，根据树高和胸径值，从二元材积表中查得对应的材积值，然后进行计算统计。这种方法工作量大，效率低下，劳神费时，容易产生查表、手工转记或录入等人为因素形成的错误，并且完全依赖于二元材积表。一旦二元材积表本损毁或丢失后，就无法正常开展工作。

如何根据各树种的树高、胸径两个林木调查因子，通过二元材积计算公式，高效智能地自动化计算单株树木材积，摆脱对二元材积表的依赖，是一个值得探究的技术问题。计算机应用设计，能够不折不扣地执行人们预先设定的运算方法，只要保证设计思路正确、测试验证结果正确，就能反复地运用，孜孜不倦地执行，保证材积计算的精准度，避免人为因素产生的错误结果。

2 开发环境

Microsoft Office Excel 2007 中文版。

3 设计依据

（1）二元立木材积计算公式：V=A*D^B*H^C

其中：V —— 材积（单位：立方米），D —— 胸径（单位：厘米），H —— 树高（单位：米）

A、B、C ——各树种材积参数

（2）树种材积参数来源：各树种的A、B、C三个参数，其中的柳杉、落叶松来源于四川省林业厅《四川省二元材积表》编制参数，其余树种来源于原中华人民共国农林部LY 208-77部颁材积表编制参数。

4 设计过程

（1）新建一个工作簿文件，命名为“二元材积表.xlsx”；新建两个工作表，表标签名称分别命名为“材积处理”、“树种参数”。

（2）“树种参数”工作表设计：将各树种A、B、C三个材积参数分别录入到“树种参数”工作表中；将A2：A13单元格区域名称定义为“树种”；将A2：D13单元格区域名称定义为“参数”；该工作表编辑完成后，在使用过程中，不再需要进行任何后期操作，为此，应对本工作表的所有单元格进行锁定，并对该工作表进行加密保护，让工作表数据相对安全稳定，防止误操作，造成数值参数变化，引发难以查辨的错误。“树种参数”工作表的设计效果如附图1所示。

（3）对“材积处理”工作表进行如下设计

1）对树种名称单元格进行数据有效性设置

A3至A12单元格为树种名称，为保证输入的树种名称与树种参数工作表中的树种名称一致，应对区域中的单元格进行有效性验证设置。进入有效性设置窗口，在允许下面的下拉列表框中选择“序列”，在随后增加的来源下面的文本框中输入“=树种”，点出“确定”按钮完成设置。通过这样的数据有效性验证设置后，从A3至A12的全部单元格，将只能从右面的下拉列表框中选择树种，与“树种参数”表的树种完全一致，保证了树种名称的精确匹配，以便准确地获取对应的各树种参数。

②外出务工的农民，多数是受过良好教育和拥有一技之长的高素质青壮年。实际务农多数为妇女与老人，身体素质差，组织协调能力弱，使农村坑塘建设所需人力资本严重稀缺，难以承担坑塘建设的人力需求。

2）对材积单元格进行公式设置

在D3单元格中输入以下公式：

=IF(OR(A3="",B3="",C3=""),"",ROUND(VLOOKUP(A3,参数,2,0)*C3^VLOOKUP(A3,参数,3,0)*B3^VLOOKUP(A3,参数,4,0),3))

公式的主要功能：根据D3单元格中的树种名称，在树种参数表中查找到与之对应的A、B、C三个参数值，再根据B3单元格的树高值H、C3单元格的胸径值 D，通过二元材积公式V=A*D^B*H^C计算出该树种、该规格的单株材积。

公式的核心实现：主要利用VLOOKUP（）函数，通过A3单元格的树种名，从树种参数表中查找到这个树种，然后从同一行中取第二列数据返回参数A值、取第三列数据返回参数B值、取第四列数据返回参数 C值。三次使用该函数分别返回三个参数，按材积公式自动参与运算处理。

公式中嵌套的其它辅助函数：ROUND（）函数，用于实现对材积数据进行四舍五入，保留到小数后3位；OR（）函数，用于检测树种是否选择，树高、胸径是否填写完整，如果三项数据不齐全，返回值为TRUE， 否则返回值为FALSE；IF（）函数，用于控制在什么情况下开始计算材积，如果 OR()函数返回值为 TRUE，则不进行材积计算，D3单元格为空（不显示任何值），如果OR()函数返回值为FALSE，则允许进行材积计算，在D3单元格中显示材积值。

4）材积合计单元格D13公式设置： =SUM(D3：D12) 。

5）锁定相关单元格、保护该工作表：对本工作表中除树种、树高、胸径输入区域(A3至C12单元格区域）以外的所有单元格进行锁定，并对该工作表进行加密保护。让数据输入区以外单元格（特别是材积公式单元格）得到相对的安全保护，防止误操作造成公式丢失或公式变化。

“材积处理”工作表的设计效果如附图2所示。

5 测试验证

通过对以上两个工作表的格式设计、参数录入和材积计算公式的设置，即可实现对二元材积的自动化处理。设计完成后，根据林区外业调查获得的的每木调查数据，将树种、树高和胸径值分别录入到“材积处理”工作表中，自动生成了单株树木材积、合计材积等数据。将本设计生成的每木材积数据与《四川省二元材积表》进行查算比对，结果完全一致，测试验证通过。测试验证效果如附图3所示。

6 树种参数扩展适用范围

根据《四川省二元材积表》编制说明，本设计中涉及的各树种参数，可按以下树种名录进行树种扩展适用：

马尾松适用于火炬松、葵花松、湿地松、国外松；杉木适用于水杉；栎类适用于青杠、斛栎、蒙古栎；桉树适用于直干桉、兰桉；桦木适用于光皮桦、野樱桃；落叶松适用于日本落叶松、长白落叶松；阔叶树适用于香樟、刺槐、枫香、水冬瓜、山胡椒、毛泡桐、栲树、大头茶、山矾、软阔、硬阔、楠木、桤木要、杨树、泡桐、黑杨、香椿、红椿、木荷、榆、苦楝、川楝、黄角树、千丈、枫杨、麻柳、小叶榕、女贞、鹅掌秋等。

7 设计适用地区

本二元材积自动化处理的电子数表设计，主要适用于现四川省、重庆市区域内相关树种的二元材积计算。

8 设计适用性扩展

本电子数表的设计，仅提供一种设计思路和设计模式。根据这种设计模式，可以依据不同地区、不同树种，在不同时期颁布的、更新的二元材积参数，对本设计中的“树种参数”表进行参数更新或树种补充，“材积处理”工作表的设计结构和材积公式设置将保持不变，设计的适用范围（地区、树种）增大，适用扩展性较强。

9 设计结论

Excel电子表格的强大数据处理功能，与林业调查规划设计工作的实际应用相结合，将复杂的数据运算处理过程进行智能化实现，将数百页不便永久保存、不易随时携带的二元材积物理数表，转化为简单的电子数表，便于复制保存，便于多人共享使用，让林业调查工作人员完全摆脱了对物理数表的依赖，实现了二元材积的自动化高效处理，极大地降低了脑力劳动强度，提高了材积数据的精准化处理水平，提高了林业调查设计的工作效率。

10 结束语

通过Excel与林业调查工作的应用结合，将二元材积物理数表转化为简单的电子数表，可以完全摆脱对物理数表的依赖，实现二元材积的自动化处理，提高林业工作效率。

图1 树种参数工作表设计图

图2 材积处理工作表设计图

图3 测试验证结果图

[1]中华人民共和国农林部. 立木材积表(LY208―77)[S]. 北京： 中国标准出版社,1978.

[2]四川省质量技术监督局. 四川省地方标准批准发布公告2012年第6号(总第21号)[EB].

[3]周庆麟,李幼乂,王建发,等. Excel应用大全[M]. 北京： 人民邮电出版社,2008. 3.

[4]Excel Home. Excel2007应用大全[M]. 北京： 人民邮电出版社,2012.3.

[5]黄海. EXCEL公式函数图表VBA一本通[M]. 北京： 中国青年出版社,2008.

Automatic Processing of Two Variables Volume for Wood by Excel

DAI Yuanda*,ZHONG Ming
（Yongsheng forest farm,Fuling District,Chongqing,Chongqing Fuling,408000,China）

s：The Two variables volume table is based on the functional relationship between the volume of each tree and the diameter of the breast and the height of the tree,which is also called the standard volume table. In the forest survey planning and design work,the volume of trees is generally obtained through the Two variables volume table,which has a serious dependence on the Two variables volume table. Since there is a functional relationship between the volume of tree species and the two factors of DBH and tree height,the volume can be calculated according to the volume parameter of each tree species. With the application of computer,the design of a Excel workbook document,a volume parameter table of each species,can realize the automatic generation of volume,completely get rid of dependence on the physical table.

Two variables volume table for wood; Excel; automatic processing; design research

TP311

A

1672-9129(2017)06-0050-03

10.19551/j.cnki.issn1672-9129.2017.06.017

代远大,钟鸣. 用Excel实现二元材积的自动化处理[J]. 数码设计,2017,6(6)： 50-52.

Cite：DAI Yuanda,ZHONG Ming. Automatic Processing of Two Variables Volume for Wood by Excel[J]. Peak Data Science,2017,6(6)： 50-52.

2017-01-23；

2017-03-05。

代远大，男，生于1966年2月；籍贯重庆涪陵，高级工程师；大学专科。

Email：73858245@qq.com