基于Excel VBA實(shí)現(xiàn)角規(guī)調(diào)查小班蓄積量的自動(dòng)計(jì)算

徐禮來(lái)

（安徽省旌德縣林業(yè)局 242600）

VBA 的英文全稱是Visual Basic For Application，是由Microsoft（微軟）公司設(shè)計(jì)開發(fā)，并內(nèi)置于Microsoft Office 系列產(chǎn)品中的一套開發(fā)平臺(tái)。VBA 與Office 產(chǎn)品高度集成，用戶利用簡(jiǎn)單的語(yǔ)法和規(guī)則，通過(guò)簡(jiǎn)易快速的編程手法來(lái)實(shí)現(xiàn)高級(jí)的軟件功能[1]。小班蓄積量等于小班面積和小班每公頃蓄積量的乘積，小班每公頃蓄積量可用角規(guī)測(cè)樹、樣段調(diào)查法、樣地調(diào)查法、衛(wèi)片判讀法等方法確定[2]。角規(guī)是一種構(gòu)造簡(jiǎn)單、使用方便的測(cè)樹工具，主要用來(lái)測(cè)定森林每公頃胸高斷面積，計(jì)算森林疏密度和蓄積量，在我國(guó)森林調(diào)查中已得到廣泛應(yīng)用。其構(gòu)造由一根木棍和帶有缺口的金屬薄片組成，缺口寬度與棍長(zhǎng)一般成1∶50[3]，常用的棍長(zhǎng)有三種100、70、50cm 等三種。我國(guó)于1957年引進(jìn)角規(guī)，起初稱之為疏密度測(cè)定器，在試驗(yàn)研究的基礎(chǔ)上逐步得到了推廣[4]。小班每公頃蓄積量的計(jì)算是否正確，直接影響小班蓄積量，是二類調(diào)查質(zhì)量管理的重要內(nèi)容。由于電子計(jì)算機(jī)的普及，現(xiàn)整個(gè)森林經(jīng)理的內(nèi)業(yè)、外業(yè)工作已廣泛應(yīng)用電子計(jì)算機(jī)技術(shù)[5]，下面以Excel VBA 代碼編制為例，探索實(shí)現(xiàn)角規(guī)調(diào)查小班蓄積量的自動(dòng)計(jì)算技術(shù)。

1 傳統(tǒng)人工計(jì)算

野外調(diào)查時(shí)，根據(jù)林木分布特點(diǎn)和生長(zhǎng)狀況，選擇有代表性的地段布設(shè)調(diào)查點(diǎn)進(jìn)行調(diào)查，并在《角規(guī)樣地調(diào)查記錄表》中分別角規(guī)點(diǎn)和樹種填寫觀測(cè)株數(shù)、坡度、平均樹高。坡度超過(guò)120時(shí)，要通過(guò)《角規(guī)觀測(cè)值按坡度改算表》對(duì)觀測(cè)值進(jìn)行坡度改正[6]。因此，計(jì)算時(shí)要分別角規(guī)點(diǎn)和樹種，根據(jù)觀測(cè)株數(shù)、坡度，通過(guò)《角規(guī)觀測(cè)值按坡度改算表》對(duì)觀測(cè)值進(jìn)行改算，再求林分平均觀測(cè)改算值，又分別樹種和林分平均高，通過(guò)《常用樹種斷面積標(biāo)準(zhǔn)表》查找同樹種的公頃蓄積量，并乘以林分平均觀測(cè)改算值，后求和，即求算得小班每公頃蓄積量。由于森林資源規(guī)劃設(shè)計(jì)調(diào)查（簡(jiǎn)稱“二類調(diào)查”）工作量大，小班調(diào)查卡片內(nèi)業(yè)處理數(shù)據(jù)多，調(diào)查計(jì)算人員業(yè)務(wù)參差不齊，小班蓄積量計(jì)算過(guò)程需2 次查表引用和多次計(jì)算，易導(dǎo)致工作人員疲勞和查表計(jì)算結(jié)果易錯(cuò)誤。

2 VBA 自動(dòng)計(jì)算

2.1 模塊和流程

二類調(diào)查小班區(qū)劃條件為：權(quán)屬、地類、森林類別及林種、生態(tài)公益林的事權(quán)與保護(hù)等級(jí)、林業(yè)工程級(jí)別、起源、優(yōu)勢(shì)樹種（組）比例相差二成以上、齡級(jí)、郁閉度、立地條件類型。結(jié)合旌德縣森林樹種結(jié)構(gòu)特點(diǎn)，角規(guī)樣地調(diào)查計(jì)算模塊主要分為：杉-松-闊、松-闊-闊、杉-闊-闊3 個(gè)模塊。下面，以杉-松-闊模塊為例來(lái)說(shuō)明。

角規(guī)調(diào)查小班蓄積量自動(dòng)計(jì)算流程見圖1。常用表編制是指編制角規(guī)觀測(cè)值按坡度改算表和常用樹種斷面積標(biāo)準(zhǔn)表；角規(guī)樣地調(diào)查記錄表設(shè)置是指根據(jù)式樣表將表格設(shè)置為Excel 表格；公式編輯是指通過(guò)VBA 編制角規(guī)觀測(cè)值按坡度改算表及常用樹種斷面積標(biāo)準(zhǔn)表中相應(yīng)的數(shù)據(jù)引用和計(jì)算公式代碼；角規(guī)樣地調(diào)查記錄表復(fù)制是指根據(jù)同模塊的小班數(shù)據(jù)，復(fù)制相應(yīng)的表格及公式；計(jì)算應(yīng)用是指將小班角規(guī)調(diào)查記錄數(shù)據(jù)錄入表中，小班蓄積量自動(dòng)計(jì)算。

圖1 角規(guī)調(diào)查小班蓄積量自動(dòng)計(jì)算流程

2.2 表格編制及VBA 設(shè)置應(yīng)用

2.2.1 常用表編制

根據(jù)安徽省林業(yè)勘察設(shè)計(jì)院1985年3月《林業(yè)調(diào)查設(shè)計(jì)常用手冊(cè)》中的角規(guī)觀測(cè)值按坡度改算表，打開excel，將excel 工作簿命名為角規(guī)調(diào)查小班蓄積量自動(dòng)計(jì)算，在sheet1 工作表中進(jìn)行如下設(shè)置表格（表1），坡度從120、140…460，觀測(cè)值從0.5、1.0…30.0,表中區(qū)為改算值，將此工作表命名為角規(guī)表；根據(jù)安徽省林業(yè)廳2012年4月《安徽省森林資源規(guī)劃設(shè)計(jì)調(diào)查實(shí)施細(xì)則》中的常用樹種斷面積標(biāo)準(zhǔn)表，在sheet2 中進(jìn)行如下設(shè)置表格（表2），松包括馬尾松、濕地松、火炬松等樹種，闊包括硬闊、軟闊等樹種，杉主要指杉木樹種，各樹種數(shù)據(jù)范圍如表2所示，并將此工作表命名為蓄積量表。此兩個(gè)表主要用于VBA 代碼編制查找引用的工具表。

表1 角規(guī)表

表2 蓄積量表

2.2.2 記錄表編制

根據(jù)《安徽森林資源規(guī)劃設(shè)計(jì)調(diào)查實(shí)施細(xì)則》中的森林資源規(guī)劃設(shè)計(jì)小班調(diào)查卡片式樣，結(jié)合自動(dòng)計(jì)算設(shè)計(jì)的需要，將角規(guī)樣地調(diào)查記錄表按如下設(shè)置，具體操作步驟是：打開角規(guī)調(diào)查小班蓄積量自動(dòng)計(jì)算Excel 工作簿，點(diǎn)擊sheet3，在此工作表中將角規(guī)樣地調(diào)查記錄表設(shè)置如下圖（表3），并將單元格區(qū)域（G4∶I9,J9∶l9,M9∶P9,R4∶Z9）設(shè)置為淺藍(lán)色區(qū)域，提示使用者這些單元格數(shù)據(jù)結(jié)果會(huì)自動(dòng)計(jì)算顯示，將單元格區(qū)域M4∶P8 設(shè)為淺綠色區(qū)域，提示使用者不用錄入計(jì)算數(shù)據(jù)，最后將sheet3 重命為杉松闊角規(guī)樣地調(diào)查記錄表。

表3 角規(guī)樣地調(diào)查記錄表

2.2.3 自動(dòng)引用和計(jì)算代碼編制

小班各樹種（組）每公頃蓄積量計(jì)算，需要根據(jù)樹種（組）角規(guī)觀測(cè)值和坡度查找相應(yīng)的坡度改算值，并求各樹種（組）的平均改算值，再根據(jù)林分平均樹高在《常用樹種斷面積標(biāo)準(zhǔn)表》中查找相應(yīng)樹種的每公頃蓄積標(biāo)準(zhǔn)值，這二者之積為各組成樹種（組）每公頃蓄積量。為了實(shí)現(xiàn)小班蓄積量自動(dòng)計(jì)算，VBA 代碼可按如下流程（圖2）進(jìn)行編制。

森林認(rèn)證是推動(dòng)可持續(xù)林業(yè)管理的市場(chǎng)性手段，為此，全國(guó)志愿性馬來(lái)西亞木材認(rèn)證體系得以建立，對(duì)森林管理實(shí)踐進(jìn)行獨(dú)立審核、對(duì)可持續(xù)森林管理的完成度進(jìn)行評(píng)估、對(duì)林產(chǎn)公司林產(chǎn)品木材來(lái)源進(jìn)行審核。

圖2 VBA 代碼編制流程圖

2.2.3.1 改正后株數(shù)（面積）自動(dòng)引用和平均改算值自動(dòng)計(jì)算代碼編制

區(qū)域G4∶I8 需根據(jù)調(diào)查坡度和觀測(cè)株數(shù)，調(diào)查坡度大于12°，需進(jìn)行觀測(cè)株數(shù)改算；調(diào)查坡度小于12°，不需改算。改正后株數(shù)（面積）平均值計(jì)算是分別對(duì)G4∶G8、H4∶H8、I4∶I8 三個(gè)區(qū)域求平均。依據(jù)表1和表3，下面以G4 自動(dòng)引用和以G9 單元格平均值計(jì)算為例：

G4 單元格數(shù)據(jù)自動(dòng)引用的主要代碼：Range（"G4"）.Select:ActiveCell.FormulaR1C1="=IF（RC[-4]=0,0,IF（RC6<12,RC[-4],INDEX（ 角規(guī)表!R4C2:R63C19,MATCH（RC[-4],角規(guī)表!R4C1:R63C1,0）,MATCH（RC6,角規(guī)表!R3C2:R3C19,0））））"。

G9 單元格平均改算值自動(dòng)計(jì)算的主要代碼：Range（"G9"）.Select:ActiveCell.FormulaR1C1=“=IF（SUM（R[-5]C:R[-1]C）=0,"""",ROUND（SUM（R[-5]C:R[-1]C）/COUNTIF（R[-5]C[19]:R[-1]C[19],"">0""）,2））"。

2.2.3.2 樹高平均值自動(dòng)計(jì)算代碼編制

J4∶L8 單元格區(qū)域?yàn)槿斯し謽浞N填寫樹高值區(qū)，J9、K9、L9 分別對(duì)相應(yīng)樹種樹高值求平均，以J9 單元格樹高平均值自動(dòng)計(jì)算為例。

J9 單元格樹高平均值自動(dòng)計(jì)算的主要代碼：Range（"J9"）.Select:ActiveCell.FormulaR1C1="=IF（SUM（R[-5]C:R[-1]C）=0,"""",ROUND（SUM（R[-5]C:R[-1]C）/COUNTA（R[-5]C:R[-1]C）,0））"。

2.2.3.3 每公頃蓄積自動(dòng)計(jì)算代碼編制

N9 單元格杉類每公頃蓄積自動(dòng)計(jì)算的主要 代 碼 ： Range（"N9"）.Select:ActiveCell.FormulaR1C1="=（IF（RC[-4]="""","""",ROUND（LOOKUP（RC[-4],蓄積量表!R5C1:R18C1,蓄積量表!R5C4:R18C4）*RC[-7],1）））"。

M9 單元格各樹種的每公頃蓄積自動(dòng)求和的主要代碼：Range（"M9"）.Select:ActiveCell.FormulaR1C1="=SUM（RC[1]:RC[3]）"。

2.2.3.4 樹種組成系數(shù)自動(dòng)計(jì)算代碼編制

各樹種的每公頃蓄積與樣地各樹種的每公頃蓄積之和的比值，以十分法表示。杉、松、闊對(duì)應(yīng)的系數(shù)在合并的單元格R4∶R9、S4∶S9、T4∶T9。下面以合并的單元格R4∶R9 為例。

R4∶R9 合并的單元格樹種組成系數(shù)自動(dòng)計(jì)算的主要代碼：Range（"R4∶R9"）.Select∶ActiveCell.FormulaR1C1="=IF（SUM（RC[-15]∶R[4]C[-15]）=0,"""",IF（R[5]C[-8]="""","""",ROUND（R[5]C[-4]/R[5]C13*10,0）））"。

2.2.3.5 小班蓄積量自動(dòng)計(jì)算代碼編制

純林：小班蓄積量=小班每公頃蓄積量×小班面積；混交林：小班蓄積量=各組成樹種（組）每公頃蓄積量之和×小班面積，各組成樹種（組）小班蓄積量=小班蓄積量×各組成樹種（組）組成系數(shù)。合并單元格V4∶V9、W4∶W9、X4∶X9 分別計(jì)算杉、松、闊樹種組小班蓄積量，合并單元格U4∶U9 計(jì)算小班總蓄積量。下面以合并的單元格V4∶V9 和U4∶U9 為例。

合并單元格V4∶V9 自動(dòng)計(jì)算杉類小班蓄積量主要代碼：Range（"V4:V9"）.Select:ActiveCell.FormulaR1C1="=IF（SUM（RC[-19]:R[4]C[-19]）=0,0,IF（R[5]C[-12]="""","""",ROUND（RC[-4]*RC[-1]/10,0）））"。

合并單元格U4:U9 自動(dòng)計(jì)算小班總蓄積量主要代碼：Range（"U4:U9"）.Select:ActiveCell.FormulaR1C1="=ROUND（RC[-4]*R[5]C[-8],0）":Range（"V4:V9"）.Select:ActiveCell.FormulaR1C1="=IF（SUM（RC[-19]:R[4]C[-19]）=0,0,IF（R[5]C[-12]="""","""",ROUND（RC[-4]*RC[-1]/10,0）））"。

2.2.4 表格和公式復(fù)制

2013年興隆鄉(xiāng)全鄉(xiāng)二類調(diào)查林地面積0.56 萬(wàn)hm2，共區(qū)劃小班1258 個(gè)，其中正小班979 個(gè)、副小班279 個(gè)。為了計(jì)算查數(shù)方便，通過(guò)VBA 操作，對(duì)角規(guī)調(diào)查記錄表格和公式進(jìn)行復(fù)制，生成表格，以后只需錄入保存就可以了。主要VBA 代碼編制如下：

Rows（"4:9"）.Select:Range（"B4"）.Activate:S election.Copy:Dim amount As String: amount=InputBox（"請(qǐng)輸入有角規(guī)數(shù)據(jù)的正副小班合計(jì)數(shù):"）:For i=1 To amount:Cells（4+6*i,1）.Select:ActiveCell.PasteSpecial:Next。

2.3 VBA 應(yīng)用

在2013年旌德縣森林資源二類調(diào)查中，全縣林地面積6.5 萬(wàn)hm2，共區(qū)劃小班14863個(gè)，其中正小班12986 個(gè)，副小班1877 個(gè)。從事這項(xiàng)工作的人都知道，小班卡片內(nèi)業(yè)處理是一個(gè)相當(dāng)大的工作量。該縣5 個(gè)鄉(xiāng)鎮(zhèn)的林業(yè)調(diào)查人員積極參加角規(guī)調(diào)查小班蓄積量的自動(dòng)計(jì)算程序應(yīng)用，參加應(yīng)用者有剛畢業(yè)參加工作的年輕大學(xué)生，也有工作三十多年、年齡達(dá)50 多歲的老林業(yè)工作者，使用之后都說(shuō)“操作簡(jiǎn)單，結(jié)果正確，大大降低了工作量，效果好”。具體步驟是：打開已編制有角規(guī)表和蓄積量表的角規(guī)調(diào)查小班蓄積量自動(dòng)計(jì)算excel 工作簿，在杉松闊角規(guī)調(diào)查記錄工作表中，點(diǎn)擊[開發(fā)工具]→[宏]，彈出宏對(duì)話框（如圖3），選中宏名為公式編制，點(diǎn)擊[執(zhí)行]按扭，自動(dòng)計(jì)算公式自動(dòng)填入角規(guī)調(diào)查記錄表相應(yīng)的單元格內(nèi)；同樣，表格公式復(fù)制也是如此操作。這些完成之后，就可以進(jìn)行角規(guī)調(diào)查記錄數(shù)據(jù)錄入計(jì)算，錄入完成后，只需對(duì)錄入數(shù)據(jù)進(jìn)行核對(duì)，核對(duì)無(wú)誤后，可根據(jù)小班號(hào)將淺藍(lán)區(qū)域的計(jì)算結(jié)果直接填入小班調(diào)查卡片中。

本次二類調(diào)查對(duì)樹種組成，要求由兩個(gè)樹種（組）組成，優(yōu)勢(shì)樹種（組）占65%以上劃為純林，優(yōu)勢(shì)樹種（組）達(dá)不到65%的劃為混交林；由三個(gè)或三個(gè)以上樹種（組）組成，其中不足1成的樹種（組）可加至齡級(jí)相同的樹種（組）合并計(jì)算。樹種組成按十分法填寫，如10 杉、6馬4 櫟、5 杉5 檫等。填寫小班卡片樹種組成時(shí)，根據(jù)上面的要求，稍加注意填寫，即可。

圖3 宏對(duì)話框

3 結(jié)語(yǔ)

目前，Excel 應(yīng)用范圍廣泛，利用Excel VBA 進(jìn)行二次開發(fā)，編制杉松闊模塊的角規(guī)調(diào)查小班蓄積量自動(dòng)計(jì)算的VBA 代碼，可以實(shí)現(xiàn)角規(guī)調(diào)查小班蓄積量的自動(dòng)計(jì)算，節(jié)省了二類調(diào)查小班蓄積量?jī)?nèi)業(yè)處理的大量時(shí)間和精力，提高了工作質(zhì)量。同理，照此方法，也可以編制松闊闊、杉闊闊模塊的角規(guī)調(diào)查小班蓄積量自動(dòng)計(jì)算的VBA 代碼，還可以進(jìn)一步處理，將小班蓄積量數(shù)據(jù)引用到小班登記表中，省略重新錄入工作，具有較高的實(shí)用價(jià)值。

[1] 鐘聲，李遠(yuǎn)祥，鄭開敏編著.Excel2007VBA 入門與提高實(shí)例大全[M].重慶：電腦報(bào)電子音像出版社,2009.

[2] 安徽省林業(yè)廳.安徽森林資源規(guī)劃設(shè)計(jì)調(diào)查實(shí)施細(xì)則[M],2003.

[3] 安徽省林業(yè)勘察設(shè)計(jì)院.林業(yè)調(diào)查設(shè)計(jì)常用手冊(cè)[M],1985.

[4] 北京林業(yè)大學(xué).測(cè)樹學(xué)[M].北京∶中國(guó)林業(yè)出版社,1993.

[5] 于政中主編.森林經(jīng)理學(xué)[M].北京∶中國(guó)林業(yè)出版社,1993.

[6] 安徽省林業(yè)廳.安徽森林資源規(guī)劃設(shè)計(jì)調(diào)查實(shí)施細(xì)則[M],2012.