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

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

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

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

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

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

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

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

引言

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

1 需求分析

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

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

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

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

Microsoft Office Excel 2007 中文版。

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

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

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

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

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

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

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

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

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

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

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

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

2）對(duì)材積單元格進(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ù)種名稱(chēng)，在樹(shù)種參數(shù)表中查找到與之對(duì)應(yīng)的A、B、C三個(gè)參數(shù)值，再根據(jù)B3單元格的樹(shù)高值H、C3單元格的胸徑值 D，通過(guò)二元材積公式V=A*D^B*H^C計(jì)算出該樹(shù)種、該規(guī)格的單株材積。

公式的核心實(shí)現(xiàn)：主要利用VLOOKUP（）函數(shù)，通過(guò)A3單元格的樹(shù)種名，從樹(shù)種參數(shù)表中查找到這個(gè)樹(shù)種，然后從同一行中取第二列數(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)對(duì)材積數(shù)據(jù)進(jìn)行四舍五入，保留到小數(shù)后3位；OR（）函數(shù)，用于檢測(cè)樹(shù)種是否選擇，樹(shù)高、胸徑是否填寫(xiě)完整，如果三項(xiàng)數(shù)據(jù)不齊全，返回值為T(mén)RUE， 否則返回值為FALSE；IF（）函數(shù)，用于控制在什么情況下開(kāi)始計(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ù)該工作表：對(duì)本工作表中除樹(shù)種、樹(shù)高、胸徑輸入?yún)^(qū)域(A3至C12單元格區(qū)域）以外的所有單元格進(jìn)行鎖定，并對(duì)該工作表進(jìn)行加密保護(hù)。讓數(shù)據(jù)輸入?yún)^(qū)以外單元格（特別是材積公式單元格）得到相對(duì)的安全保護(hù)，防止誤操作造成公式丟失或公式變化。

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

5 測(cè)試驗(yàn)證

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

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

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

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

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

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

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

本電子數(shù)表的設(shè)計(jì)，僅提供一種設(shè)計(jì)思路和設(shè)計(jì)模式。根據(jù)這種設(shè)計(jì)模式，可以依據(jù)不同地區(qū)、不同樹(shù)種，在不同時(shí)期頒布的、更新的二元材積參數(shù)，對(duì)本設(shè)計(jì)中的“樹(shù)種參數(shù)”表進(jìn)行參數(shù)更新或樹(shù)種補(bǔ)充，“材積處理”工作表的設(shè)計(jì)結(jié)構(gòu)和材積公式設(shè)置將保持不變，設(shè)計(jì)的適用范圍（地區(qū)、樹(shù)種）增大，適用擴(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)算處理過(guò)程進(jìn)行智能化實(shí)現(xiàn)，將數(shù)百頁(yè)不便永久保存、不易隨時(shí)攜帶的二元材積物理數(shù)表，轉(zhuǎn)化為簡(jiǎn)單的電子數(shù)表，便于復(fù)制保存，便于多人共享使用，讓林業(yè)調(diào)查工作人員完全擺脫了對(duì)物理數(shù)表的依賴(lài)，實(shí)現(xiàn)了二元材積的自動(dòng)化高效處理，極大地降低了腦力勞動(dòng)強(qiáng)度，提高了材積數(shù)據(jù)的精準(zhǔn)化處理水平，提高了林業(yè)調(diào)查設(shè)計(jì)的工作效率。

10 結(jié)束語(yǔ)

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

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

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

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

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

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

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

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

[5]黃海. EXCEL公式函數(shù)圖表VBA一本通[M]. 北京： 中國(guó)青年出版社,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月；籍貫重慶涪陵，高級(jí)工程師；大學(xué)專(zhuān)科。

Email：73858245@qq.com