代遠(yuǎn)大*，鐘鳴

（重慶市涪陵區(qū)永勝林場，重慶，408000）

用Excel 實(shí)現(xiàn)二元材積的自動(dòng)化處理

代遠(yuǎn)大*，鐘鳴

（重慶市涪陵區(qū)永勝林場，重慶，408000）

二元材積表，是根據(jù)每種樹木的材積與胸徑、樹高兩個(gè)因子的函數(shù)關(guān)系編制的，也叫標(biāo)準(zhǔn)材積表。在林業(yè)調(diào)查規(guī)劃設(shè)計(jì)工作中，樹木的材積，一般通過二元材積表去獲取，對二元材積表具有嚴(yán)重的依賴性。既然樹種的材積與胸徑、樹高兩個(gè)因子存在函數(shù)關(guān)系，即可通過各樹種的材積參數(shù)，按統(tǒng)一的公式計(jì)算出材積。結(jié)合計(jì)算機(jī)的應(yīng)用，設(shè)計(jì)一個(gè)Excel工作薄文檔，建立各樹種的材積參數(shù)表，可以實(shí)現(xiàn)材積的自動(dòng)化生成，完全擺脫對物理數(shù)表的依賴。

二元材積表；Excel；自動(dòng)化處理；設(shè)計(jì)研究

引言

林業(yè)屬于工程系列的一個(gè)重要技術(shù)類別，需要開展調(diào)查規(guī)劃、科學(xué)實(shí)驗(yàn)、科學(xué)推廣等工作，涉及大量的數(shù)據(jù)運(yùn)算處理，二元材積的計(jì)算就是其中的一項(xiàng)重要工作。隨著計(jì)算機(jī)應(yīng)用的普及，通過Microsoft Office Excel電子表格的簡單設(shè)計(jì)，可以實(shí)現(xiàn)二元材積自動(dòng)化計(jì)算處理，極大地提高工作效率。

1 需求分析

在林業(yè)調(diào)查規(guī)劃設(shè)計(jì)工作中，樹木材積的計(jì)算是一項(xiàng)重要內(nèi)容。通過外業(yè)調(diào)查，測得各樹種單株樹木的樹高、胸徑，在內(nèi)業(yè)工作中，要對每株樹木的材積進(jìn)行計(jì)算統(tǒng)計(jì)。

傳統(tǒng)的方法是：分別樹種，根據(jù)樹高和胸徑值，從二元材積表中查得對應(yīng)的材積值，然后進(jìn)行計(jì)算統(tǒng)計(jì)。這種方法工作量大，效率低下，勞神費(fèi)時(shí)，容易產(chǎn)生查表、手工轉(zhuǎn)記或錄入等人為因素形成的錯(cuò)誤，并且完全依賴于二元材積表。一旦二元材積表本損毀或丟失后，就無法正常開展工作。

如何根據(jù)各樹種的樹高、胸徑兩個(gè)林木調(diào)查因子，通過二元材積計(jì)算公式，高效智能地自動(dòng)化計(jì)算單株樹木材積，擺脫對二元材積表的依賴，是一個(gè)值得探究的技術(shù)問題。計(jì)算機(jī)應(yīng)用設(shè)計(jì)，能夠不折不扣地執(zhí)行人們預(yù)先設(shè)定的運(yùn)算方法，只要保證設(shè)計(jì)思路正確、測試驗(yàn)證結(jié)果正確，就能反復(fù)地運(yùn)用，孜孜不倦地執(zhí)行，保證材積計(jì)算的精準(zhǔn)度，避免人為因素產(chǎn)生的錯(cuò)誤結(jié)果。

2 開發(fā)環(huán)境

Microsoft Office Excel 2007 中文版。

3 設(shè)計(jì)依據(jù)

（1）二元立木材積計(jì)算公式：V=A*D^B*H^C

其中：V —— 材積（單位：立方米），D —— 胸徑（單位：厘米），H —— 樹高（單位：米）

A、B、C ——各樹種材積參數(shù)

（2）樹種材積參數(shù)來源：各樹種的A、B、C三個(gè)參數(shù)，其中的柳杉、落葉松來源于四川省林業(yè)廳《四川省二元材積表》編制參數(shù)，其余樹種來源于原中華人民共國農(nóng)林部LY 208-77部頒材積表編制參數(shù)。

4 設(shè)計(jì)過程

（1）新建一個(gè)工作簿文件，命名為“二元材積表.xlsx”；新建兩個(gè)工作表，表標(biāo)簽名稱分別命名為“材積處理”、“樹種參數(shù)”。

（2）“樹種參數(shù)”工作表設(shè)計(jì)：將各樹種A、B、C三個(gè)材積參數(shù)分別錄入到“樹種參數(shù)”工作表中；將A2：A13單元格區(qū)域名稱定義為“樹種”；將A2：D13單元格區(qū)域名稱定義為“參數(shù)”；該工作表編輯完成后，在使用過程中，不再需要進(jìn)行任何后期操作，為此，應(yīng)對本工作表的所有單元格進(jìn)行鎖定，并對該工作表進(jìn)行加密保護(hù)，讓工作表數(shù)據(jù)相對安全穩(wěn)定，防止誤操作，造成數(shù)值參數(shù)變化，引發(fā)難以查辨的錯(cuò)誤?！皹浞N參數(shù)”工作表的設(shè)計(jì)效果如附圖1所示。

（3）對“材積處理”工作表進(jìn)行如下設(shè)計(jì)

1）對樹種名稱單元格進(jìn)行數(shù)據(jù)有效性設(shè)置

A3至A12單元格為樹種名稱，為保證輸入的樹種名稱與樹種參數(shù)工作表中的樹種名稱一致，應(yīng)對區(qū)域中的單元格進(jìn)行有效性驗(yàn)證設(shè)置。進(jìn)入有效性設(shè)置窗口，在允許下面的下拉列表框中選擇“序列”，在隨后增加的來源下面的文本框中輸入“=樹種”，點(diǎn)出“確定”按鈕完成設(shè)置。通過這樣的數(shù)據(jù)有效性驗(yàn)證設(shè)置后，從A3至A12的全部單元格，將只能從右面的下拉列表框中選擇樹種，與“樹種參數(shù)”表的樹種完全一致，保證了樹種名稱的精確匹配，以便準(zhǔn)確地獲取對應(yīng)的各樹種參數(shù)。

②外出務(wù)工的農(nóng)民，多數(shù)是受過良好教育和擁有一技之長的高素質(zhì)青壯年。實(shí)際務(wù)農(nóng)多數(shù)為婦女與老人，身體素質(zhì)差，組織協(xié)調(diào)能力弱，使農(nóng)村坑塘建設(shè)所需人力資本嚴(yán)重稀缺，難以承擔(dān)坑塘建設(shè)的人力需求。

2）對材積單元格進(jìn)行公式設(shè)置

在D3單元格中輸入以下公式：

=IF(OR(A3="",B3="",C3=""),"",ROUND(VLOOKUP(A3,參數(shù),2,0)*C3^VLOOKUP(A3,參數(shù),3,0)*B3^VLOOKUP(A3,參數(shù),4,0),3))

公式的主要功能：根據(jù)D3單元格中的樹種名稱，在樹種參數(shù)表中查找到與之對應(yīng)的A、B、C三個(gè)參數(shù)值，再根據(jù)B3單元格的樹高值H、C3單元格的胸徑值 D，通過二元材積公式V=A*D^B*H^C計(jì)算出該樹種、該規(guī)格的單株材積。

公式的核心實(shí)現(xiàn)：主要利用VLOOKUP（）函數(shù)，通過A3單元格的樹種名，從樹種參數(shù)表中查找到這個(gè)樹種，然后從同一行中取第二列數(shù)據(jù)返回參數(shù)A值、取第三列數(shù)據(jù)返回參數(shù)B值、取第四列數(shù)據(jù)返回參數(shù) C值。三次使用該函數(shù)分別返回三個(gè)參數(shù)，按材積公式自動(dòng)參與運(yùn)算處理。

公式中嵌套的其它輔助函數(shù)：ROUND（）函數(shù)，用于實(shí)現(xiàn)對材積數(shù)據(jù)進(jìn)行四舍五入，保留到小數(shù)后3位；OR（）函數(shù)，用于檢測樹種是否選擇，樹高、胸徑是否填寫完整，如果三項(xiàng)數(shù)據(jù)不齊全，返回值為TRUE， 否則返回值為FALSE；IF（）函數(shù)，用于控制在什么情況下開始計(jì)算材積，如果 OR()函數(shù)返回值為 TRUE，則不進(jìn)行材積計(jì)算，D3單元格為空（不顯示任何值），如果OR()函數(shù)返回值為FALSE，則允許進(jìn)行材積計(jì)算，在D3單元格中顯示材積值。

4）材積合計(jì)單元格D13公式設(shè)置： =SUM(D3：D12) 。

5）鎖定相關(guān)單元格、保護(hù)該工作表：對本工作表中除樹種、樹高、胸徑輸入?yún)^(qū)域(A3至C12單元格區(qū)域）以外的所有單元格進(jìn)行鎖定，并對該工作表進(jìn)行加密保護(hù)。讓數(shù)據(jù)輸入?yún)^(qū)以外單元格（特別是材積公式單元格）得到相對的安全保護(hù)，防止誤操作造成公式丟失或公式變化。

“材積處理”工作表的設(shè)計(jì)效果如附圖2所示。

5 測試驗(yàn)證

通過對以上兩個(gè)工作表的格式設(shè)計(jì)、參數(shù)錄入和材積計(jì)算公式的設(shè)置，即可實(shí)現(xiàn)對二元材積的自動(dòng)化處理。設(shè)計(jì)完成后，根據(jù)林區(qū)外業(yè)調(diào)查獲得的的每木調(diào)查數(shù)據(jù)，將樹種、樹高和胸徑值分別錄入到“材積處理”工作表中，自動(dòng)生成了單株樹木材積、合計(jì)材積等數(shù)據(jù)。將本設(shè)計(jì)生成的每木材積數(shù)據(jù)與《四川省二元材積表》進(jìn)行查算比對，結(jié)果完全一致，測試驗(yàn)證通過。測試驗(yàn)證效果如附圖3所示。

6 樹種參數(shù)擴(kuò)展適用范圍

根據(jù)《四川省二元材積表》編制說明，本設(shè)計(jì)中涉及的各樹種參數(shù)，可按以下樹種名錄進(jìn)行樹種擴(kuò)展適用：

馬尾松適用于火炬松、葵花松、濕地松、國外松；杉木適用于水杉；櫟類適用于青杠、斛櫟、蒙古櫟；桉樹適用于直干桉、蘭桉；樺木適用于光皮樺、野櫻桃；落葉松適用于日本落葉松、長白落葉松；闊葉樹適用于香樟、刺槐、楓香、水冬瓜、山胡椒、毛泡桐、栲樹、大頭茶、山礬、軟闊、硬闊、楠木、榿木要、楊樹、泡桐、黑楊、香椿、紅椿、木荷、榆、苦楝、川楝、黃角樹、千丈、楓楊、麻柳、小葉榕、女貞、鵝掌秋等。

7 設(shè)計(jì)適用地區(qū)

本二元材積自動(dòng)化處理的電子數(shù)表設(shè)計(jì)，主要適用于現(xiàn)四川省、重慶市區(qū)域內(nèi)相關(guān)樹種的二元材積計(jì)算。

8 設(shè)計(jì)適用性擴(kuò)展

本電子數(shù)表的設(shè)計(jì)，僅提供一種設(shè)計(jì)思路和設(shè)計(jì)模式。根據(jù)這種設(shè)計(jì)模式，可以依據(jù)不同地區(qū)、不同樹種，在不同時(shí)期頒布的、更新的二元材積參數(shù)，對本設(shè)計(jì)中的“樹種參數(shù)”表進(jìn)行參數(shù)更新或樹種補(bǔ)充，“材積處理”工作表的設(shè)計(jì)結(jié)構(gòu)和材積公式設(shè)置將保持不變，設(shè)計(jì)的適用范圍（地區(qū)、樹種）增大，適用擴(kuò)展性較強(qiáng)。

9 設(shè)計(jì)結(jié)論

Excel電子表格的強(qiáng)大數(shù)據(jù)處理功能，與林業(yè)調(diào)查規(guī)劃設(shè)計(jì)工作的實(shí)際應(yīng)用相結(jié)合，將復(fù)雜的數(shù)據(jù)運(yùn)算處理過程進(jìn)行智能化實(shí)現(xiàn)，將數(shù)百頁不便永久保存、不易隨時(shí)攜帶的二元材積物理數(shù)表，轉(zhuǎn)化為簡單的電子數(shù)表，便于復(fù)制保存，便于多人共享使用，讓林業(yè)調(diào)查工作人員完全擺脫了對物理數(shù)表的依賴，實(shí)現(xiàn)了二元材積的自動(dòng)化高效處理，極大地降低了腦力勞動(dòng)強(qiáng)度，提高了材積數(shù)據(jù)的精準(zhǔn)化處理水平，提高了林業(yè)調(diào)查設(shè)計(jì)的工作效率。

10 結(jié)束語

通過Excel與林業(yè)調(diào)查工作的應(yīng)用結(jié)合，將二元材積物理數(shù)表轉(zhuǎn)化為簡單的電子數(shù)表，可以完全擺脫對物理數(shù)表的依賴，實(shí)現(xiàn)二元材積的自動(dòng)化處理，提高林業(yè)工作效率。

圖1 樹種參數(shù)工作表設(shè)計(jì)圖

圖2 材積處理工作表設(shè)計(jì)圖

圖3 測試驗(yàn)證結(jié)果圖

[1]中華人民共和國農(nóng)林部. 立木材積表(LY208―77)[S]. 北京： 中國標(biāo)準(zhǔn)出版社,1978.

[2]四川省質(zhì)量技術(shù)監(jiān)督局. 四川省地方標(biāo)準(zhǔn)批準(zhǔn)發(fā)布公告2012年第6號(總第21號)[EB].

[3]周慶麟,李幼乂,王建發(fā),等. Excel應(yīng)用大全[M]. 北京： 人民郵電出版社,2008. 3.

[4]Excel Home. Excel2007應(yīng)用大全[M]. 北京： 人民郵電出版社,2012.3.

[5]黃海. EXCEL公式函數(shù)圖表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

代遠(yuǎn)大,鐘鳴. 用Excel實(shí)現(xiàn)二元材積的自動(dòng)化處理[J]. 數(shù)碼設(shè)計(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。

代遠(yuǎn)大，男，生于1966年2月；籍貫重慶涪陵，高級工程師；大學(xué)?？啤?/p>

Email：73858245@qq.com