用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