Excel基于樹高模型與二元材積公式聯(lián)動計算林分蓄積量

蔣興明

摘要：測定林分蓄積量是林分調(diào)查的主要目的之一，該文介紹使用Excel以樹高模型及二元材積公式聯(lián)動自動計算林分蓄積量的方法，以及使用相對樹高曲線模型計算林分蓄積量的方法。利用Excel的插入表格及定義名稱功能使得表格數(shù)據(jù)的引用不受數(shù)據(jù)增減變動的影響。

關(guān)鍵詞：插入表格;定義名稱;樹高模型;二元材積公式;相對樹高曲線模型;林分蓄積量

中圖分類號：TP317 文獻(xiàn)標(biāo)識碼：A

文章編號：1009-3044（2020）19-0219-03

開放科學(xué)（資源服務(wù)）標(biāo)識碼（OSID）：

1 背景

林分蓄積量是林分中所有活立木單株材積的合計（簡稱蓄積量），是衡量森林質(zhì)量的關(guān)鍵因子，是鑒定森林?jǐn)?shù)量的主要指標(biāo)，也是評價森林固碳能力的重要指標(biāo)[1]，測定林分蓄積量是林分調(diào)查的主要目的之一，它為森林經(jīng)營和采伐利用提供重要的數(shù)量依據(jù)。為了提高測定效率，一般采用預(yù)先編制好的立木材積表（簡稱材積表）確定蓄積量。材積表是我國森林資源清查和森林經(jīng)營中測定蓄積量的主要數(shù)表，材積表有一元材積表、二元材積表和三元材積表，其中根據(jù)材積與胸徑、樹高兩個因子的關(guān)系編制的材積表稱為二元材積表，它是最基本的材積表，又稱為標(biāo)準(zhǔn)材積表[2]。

傳統(tǒng)上，應(yīng)用二元材積表測算蓄積量，一般要經(jīng)過調(diào)查取得每個徑階的株數(shù)、平均胸徑和平均樹高，繪制胸徑一樹高曲線（簡稱樹高曲線），根據(jù)每個徑階在樹高曲線上查得所對應(yīng)的樹高（稱為徑階平均高），再依據(jù)徑階和徑階平均高在二元材積表中查出各徑階的單株平均材積（稱為單株材積），徑階單株材積乘以徑階的株數(shù)，計算出各徑階的林木材積，匯總各徑階材積即得林分蓄積量[2]。

本文以胸徑一樹高曲線模型（簡稱樹高模型）和二元材積公式聯(lián)立計算林分蓄積量，不必繪制樹高曲線，也不用查數(shù)表，而且，可以針對不同林分樹種選擇使用最優(yōu)樹高模型[3-4]，同樣可以選擇不同樹種所對應(yīng)二元材積公式，利用Excel（ 2016年版）關(guān)聯(lián)兩者，就可適用于不同林分樹種蓄積量的自動計算，具有現(xiàn)實意義。

2 創(chuàng)建工作表

2.1林分樹高調(diào)查表

將Sheetl命名為“林分樹高調(diào)查表”，用于錄入林分樹高調(diào)查的數(shù)據(jù)，數(shù)據(jù)可按徑階整理后的平均胸徑和平均樹高錄入，也可按單株實測的胸徑和樹高錄入。本文數(shù)據(jù)來源于西藏林芝云杉樹高曲線模型研究[4]，創(chuàng)建并錄人數(shù)據(jù)（見圖1一①）。

2.2 樹高模型表

將Sheet2命令為“樹高模型表”，用于錄入模型的參數(shù)及計算各個模型的林分徑階平均高。該表分兩個部分，一部分用于錄入樹高模型的參數(shù)（見圖2），一部分用于計算林分徑階平均高（見圖3）。林分徑階平均高的獲得，傳統(tǒng)方法是在樹高曲線上查得[5]，在此利用樹高模型計算林分徑階平均高，但不同樹種林分的最優(yōu)樹高模型不同，需要根據(jù)實際林分?jǐn)M合的最優(yōu)樹高模型編輯該表中的模型及參數(shù)。

H列的公式是用連接符“&”將模型參數(shù)按模型結(jié)構(gòu)式連接成表達(dá)式（見圖2）。比如H3單元格要表達(dá)的模型是線性H=aD+b模型，其連接式：=”=”&D3&”*D+（“&E3&”）”顯示的表達(dá)式：=0.455855492*D+（2.740860608）。從I列起，各列用于計算各個模型的林分徑階平均高（見圖3），稍后再處理表中內(nèi)容。

2.3 材積參數(shù)表

將Sheet3命名為“材積公式參數(shù)表”，用于錄入不同樹種二元材積公式的參數(shù)（見圖4）。二元材積模型比較多[6]，本文使用與國家二元材積模型相同的材積公式[7]，即：

v一aDbH‘

（1）

V-材積

D-胸徑（徑階中值）

H-樹高（徑階平均高）

a、b、c——常數(shù)

根據(jù)生產(chǎn)實踐需要，可不斷完善常用樹種的二元材積公式表[8-9]，作為計算材積時選擇使用。

2.4 林分蓄積量計算表

將sheet4工作表命名為“林分蓄積量表”，該工作表由一個主表格（見圖5-①）和一個輔助表格（見圖5一②）兩部分構(gòu)成。主表格用于計算蓄積量，輔助表格用于選擇樹高模型及樹種材積公式，兩表格聯(lián)動完成蓄積量的計算，主表的計算將隨輔助表格對樹高模型及樹種組的選擇而變，實現(xiàn)不同林分樹種蓄積量的計算。主表格只需完成表頭及表標(biāo)題部分的創(chuàng)建，輔助表格輸入有底紋單元格部分的內(nèi)容，其他內(nèi)容稍后處理。

3 插入表格

林分不同，調(diào)查的數(shù)據(jù)多少不確定，為了方便對各列數(shù)據(jù)的引用，且不受數(shù)據(jù)增減變動的影響，數(shù)據(jù)更新時，關(guān)聯(lián)的內(nèi)容也會動態(tài)更新，需對Sheetl（林分樹高調(diào)查表）作“插入表格”處理。具體方法是：通過菜單插入表格（見圖1一②）打開創(chuàng)建表對話框（或者Ctrl+T），在表數(shù)據(jù)的來源中框選表區(qū)域$A$2：$D$19，同時勾選表包含標(biāo)題（見圖1一③），確定后在菜單欄就有了表格工具，將“篩選按鈕”前的勾取消，并選擇一個合適的表格樣式（見圖1一⑤），同時將表名稱改為“林分樹高表”（見圖1-④）。

按上述方法“插入表格”處理時，表格只要有標(biāo)題行就可以進(jìn)行。將Sheet2（樹高模型表）中處理樹高模型及參數(shù)區(qū)域部分（不包含表頭，下同）“插入表格”，并改表名稱為“模型參數(shù)表”（見圖2），同時將模型徑階平均高部分（見圖3）“插入表格”，改表名稱為“模型徑階高表”。將Sheet3（材積公式參數(shù)表）插入表格的表名稱改為“材積參數(shù)表”，Sheet4（林分蓄積量表）只對主表格進(jìn)行“插入表格”處理，表名稱改為“蓄積計算表”，輔助表格不處理。

4 定義名稱

上述插入表格為動態(tài)引用各列數(shù)據(jù)提供了方便，但為了減少引用公式的繁雜性，便于閱讀和修改，按Ctrl+F3打開“名稱管理器”，對相關(guān)內(nèi)容的名稱定義見表1。

5 完善表格內(nèi)容

5.1 模型徑階高表

Sheet2中各個模型徑階平均高的計算公式是按樹高模型結(jié)構(gòu)式進(jìn)行編輯的。比如I列是以線性模型計算林分平均高，先在13單元格中輸入公式“=IFERROR（IF（林分徑階=””，””，ROUND（$D$3*林分徑階+$E$3，2）），””）”，向下快速填充的行數(shù)不得少于Sheetl中數(shù)據(jù)的行數(shù)相同，只可多不可少。式中“林分徑階”是定義名稱（見表1），用ROUNDO函數(shù)保留2位小數(shù)，當(dāng)Sheet2的“模型徑階高表”多于Sheetl（林分樹高調(diào)查表）數(shù)據(jù)行時，多余部分單元格顯示#VLUE！，IFERRORO函數(shù)是讓這部分出錯時不顯示。其他列的公式輸入后回車即會自動填充，具體公式不再贅述。

5.2 蓄積量計算輔助表格

輔助表格的數(shù)據(jù)來源主要是對“樹高模型表”和“材積參數(shù)表”的引用。

15、111是利用“數(shù)據(jù)驗證”創(chuàng)建的下拉列表，序列的來源分別是：“=模型名稱”和“=材積樹種組”。對模型參數(shù)和材積公式參數(shù)的引用具體見表2。

N6單元格引用的是Sheet2中C列的樹高模型結(jié)構(gòu)式，結(jié)構(gòu)式是用插入公式編輯器編輯的對象，對其引用方法是：利用“插圖”下的“屏幕剪輯”（或其他截圖軟件）隨意截取大小適當(dāng)?shù)膱D片放在該單元格中，并調(diào)整大小，在該截圖被選中的情況下，在“編輯欄”中輸入“=模型方程”。

當(dāng)在輔助表中選擇樹高模型時，Sheet2中與之對應(yīng)的內(nèi)容要突出顯示，以方便閱讀。具體方法是：先要選擇需要高亮顯示區(qū)域中的任一單元格，此處為“模型參數(shù)表”區(qū)域，然后利用開始菜單下“條件格式”的“管理規(guī)則”，打開“條件格式規(guī)則管理器”，“新建規(guī)則”選擇“使用公式確定要設(shè)置格式的單元”，輸入“=$B3=蓄積量表！$1$5”并設(shè)置格式后確定，在管理器的“應(yīng)用于”中輸入“=模型參數(shù)表”后回車。同法設(shè)置“模型徑階高表”，公式為“=I$2=蓄積量表！$1$5”，應(yīng)用于為“=模型徑階高表”。對Sheet3中“材積參數(shù)表”也進(jìn)行高亮顯示設(shè)置，公式為“=$B3=蓄積量表！$I$1l”，應(yīng)用于為“=材積參數(shù)表”。

在主表格A3單元格中輸入“=IFERROR（IF（林分徑階=””，””，林分徑階），””）”，然后快速填充，填充的行數(shù)不得少于Sheetl（林分樹高調(diào)查表）中數(shù)據(jù)的行數(shù)相同。

D列的單株材積是A列“徑階”和C列“徑階平均高”代人（1）式編輯的計算公式，E列徑階材積是“株數(shù)”與“單株材積”的乘積，F(xiàn)列是計算實測樣木的材積，在實踐中，當(dāng)按單株樣木記錄樹高調(diào)查時，可將樣木的實測直徑和實測樹高代人（1）計算材積[10]，相關(guān)公式見表3。

6 相對樹高曲線模型

對于單層純林而言，徑階平均高與林分平均高的比值稱為相對樹高，各徑階中值（簡稱徑階）與林分平均直徑的比值稱為相對直徑，相對直徑一相對樹高之間的相關(guān)曲線模型即為相對樹高曲線模型。應(yīng)用相對樹高曲線模型法估測林分材積，在不增加野外實測樹高曲線工作量的條件下，能排除用一元材積表導(dǎo)致的大偏差，具有顯著提高精度和效率的作用[11-12]。

林分平均直徑、林分平均高是林分調(diào)查時的必測因子。應(yīng)用相對樹高曲線模型可以求得各徑階平均高，就可應(yīng)用二元材積公式計算林分蓄積。方法是：每木調(diào)查獲得徑階、株數(shù)分布，同時測定林分平均高，計算出林分平均直徑，就可將各徑階除以林分平均直徑求得相對直徑，將相對直徑代人相對樹高曲線模型計算相對樹高，再將相對樹高乘以林分平均高，可得各徑階平均高。有了每木調(diào)查的徑階和計算所得的徑階平均高，就可用二元立木材積公式計算各徑階材積和林分蓄積[13]。

據(jù)此，將圖1一⑥中的胸徑一欄的值改為相對直徑，刪除樹高欄，樹高模型表（見圖2）中模型參數(shù)改用相對樹高模型的參數(shù)，模型徑階高（見圖3）的各模型計算式中的直徑值要改用相對直徑值，則模型計算的樹高就為相對樹高，蓄積量計算表中徑階平均高的值（見圖5）要改為相對樹高與林分平均高的乘積，刪除樣木材積欄。這樣就可使用相對樹高曲線模型與二元材積公式進(jìn)行林分蓄積量的計算。

7 結(jié)論與討論

本文以求林分蓄積量探討兩個方程聯(lián)立的Excel解決方法。由于林分樹種千差萬別，最優(yōu)樹高模型和二元材積公式也就因林而異，通過建立計算輔助表的方式就可適用于不同林分樹種蓄積量計算。使用時，錄入樹高調(diào)查數(shù)據(jù)后，在輔助表中選擇樹高模型和樹種組即可自動完成。但存在當(dāng)“林分樹高表”增加數(shù)行超過“模型徑階高表”“蓄積量計算表”原有行數(shù)時需要手動調(diào)整擴(kuò)大。

參考文獻(xiàn)：

[1]蘇迪，高心丹.基于無人機(jī)航測數(shù)據(jù)的森林郁閉度和蓄積量估測[J].林業(yè)工程學(xué)報，2020，34（1）： 156-163.

[2]孟憲宇.測樹學(xué)[M].北京：中國林業(yè)出版社，2006.

[3]覃陽平，李華，李永亮，等.云南省主要針葉樹種樹高曲線模型研建[J].林業(yè)資源管理，2019（4）： 46-51.

[4]尹惠妍，張志偉，楊小林，等，西藏林芝云杉樹高曲線模型研究[J].江蘇農(nóng)業(yè)科學(xué)，2020，48（2）： 150-154.

[5]陳森，幾種繪制樹高曲線方法的比較[Jl.云南林業(yè)調(diào)查規(guī)劃，1985，10（2）： 21-26.

[6]劉治平，曹宗英，張旭華，等，甘肅省人工云杉林二元立木材積方程的編制研究[J].林業(yè)資源管理，2020（1）： 172-176.

[7]中華人民共和國農(nóng)林部.LY 208-77，立木材積表[s].1978.

[8]曾偉生，我國主要樹種二元立木材積表的檢驗[J].林業(yè)資源管理，2018（5）： 35-41.

[9]肖永林.編制云南省一元立木材積表技術(shù)總結(jié)[Jl.云南林業(yè)調(diào)查規(guī)劃，1981，6（4）： 1-7.

[10]代遠(yuǎn)大，鐘鳴，用Excel實現(xiàn)二元材積的自動化處理[Jl.數(shù)碼設(shè)計，2017（6）： 50-52.

[11]駱期邦，吳志德，相對樹高曲線模型的研究和應(yīng)用[Jl.中南林業(yè)調(diào)查規(guī)劃，1995，14（2）： 1-3，8.

[12]黃其城，福州市杉木人工林相對樹高曲線模型研究[J]_安徽農(nóng)學(xué)通報，2017，23（4）： 58-59，65.

[13]宋永俊，相對樹高曲線法在計算林分蓄積上的應(yīng)用[Jl.云南林業(yè)調(diào)查規(guī)劃，1996（1）： 7-11.

【通聯(lián)編輯：謝媛媛】