基于Excel擬合樹高曲線方程自動計算林分平均高

蔣興明

摘要：根據(jù)森林調(diào)查標(biāo)準(zhǔn)地調(diào)查法所獲得的胸徑一樹高數(shù)據(jù)，用Excel擬合動態(tài)樹高曲線，然后利用Excel的函數(shù)提取擬合樹高曲線方程中的參數(shù)來自動計算林分平均高和徑階平均高，且計算結(jié)果會隨所選擇曲線方程類型的不同以及胸徑一樹高數(shù)據(jù)的增減變動均能自動更新計算結(jié)果，避免了因數(shù)據(jù)每次發(fā)生變化后都需要重復(fù)操作，省時高效。

關(guān)鍵詞：Excel;樹高曲線方程;林分平均高;徑階平均高

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

文章編號：1009-3044（2020）13-0247-03

1背景

林分中林木高度與胸徑之間存在著正相關(guān)關(guān)系，呈現(xiàn)出一定的變化規(guī)律，為了反映林分樹高與胸徑變化規(guī)律，以縱坐標(biāo)為樹高，橫坐標(biāo)為胸徑，即可建立胸徑一樹高曲線，即樹高曲線，二元材積表法計算林分蓄積量以及二元材積表導(dǎo)算一元材積表時都需要樹高曲線。圖解法繪制樹高曲線的傳統(tǒng)方法是用坐標(biāo)紙手工繪制樹高曲線，在樹高曲線上查取林分平均所對應(yīng)的樹高即是林分平均高，同樣在樹高曲線上可查得各徑階對應(yīng)的徑階平均樹高。求林分平均樹高還可使用數(shù)式法，即利用胸徑和樹高相關(guān)模型。此外可通過專業(yè)調(diào)查軟件完成，但其不夠普及和被廣泛應(yīng)用，基于Excel的大眾化和強(qiáng)大的數(shù)據(jù)處理功能，可很方便使用Excel擬合樹高曲線。本文以表1胸徑一樹高數(shù)據(jù)為例，介紹Excel（2016版）擬合樹高曲線方程參數(shù)的動態(tài)提取及林分平均高及徑階平均高的自動計算，解決了每次數(shù)據(jù)發(fā)生變動都需要重復(fù)操作的難題。

2擬合樹高曲線

2.1錄入數(shù)據(jù)

新建“林分平均高自動計算”的Excel工作薄，將sheetl工作表命名為“樹高曲線”，將胸徑一樹高調(diào)查整理表（見表1）中的數(shù)據(jù)錄入A、B、C三列（見圖1）。

2.2繪制散點圖

依據(jù)B、c列數(shù)據(jù)，利用菜單欄的“插人”圖表功能，建立胸徑一樹高的散點圖。

2.3添加趨勢線

為散點圖添加趨勢線，趨勢線包括指數(shù)、線性、對數(shù)、多項式、乘冪、移動平均等6種，勾選“顯示公式”和“顯示R平方值”，則散點圖中并會添加相應(yīng)的趨勢線、回歸方程和擬合程度指標(biāo)R2。

2.4選擇樹高曲線方程

R2取值范圍在0～l之間，當(dāng)趨勢線的R²等于l或接近1時，其可靠性最高。繪制樹高曲線就是以平均胸徑為自變量、平均樹高為因變量所建立的回歸方程，所以選擇6種回歸方程中Rz最大者作為樹高曲線。并將“趨勢預(yù)測”向前、向后各推延1個周期。

2.5設(shè)置樹高曲線圖表格式

2.5.1設(shè)置水平軸的格式

對“坐標(biāo)軸選項”進(jìn)行設(shè)置，水平軸“邊界”的“最小值”與每木調(diào)查的起測徑階值相同，“最大值”不做設(shè)置，“單位”為徑階距l(xiāng)cm、2cm或4cm，本例設(shè)置為2，水平軸刻度值“數(shù)字”設(shè)為整數(shù)，即不保留小數(shù)，取消千分位分隔符。垂直（值）軸不做修改。

2.5.2設(shè)置曲線方程的格式

對圖表中的曲線方程進(jìn)行設(shè)置，數(shù)字類別選擇“數(shù)字”格式，小數(shù)位數(shù)為8位或與材積公式參數(shù)的小數(shù)位數(shù)一致，取消千分位分隔符。最后再適當(dāng)調(diào)整曲線方程的字體大小。

此外將圖表的標(biāo)題修改為“樹高曲線”，添加次要網(wǎng)格線，最后再根據(jù)自己的需要設(shè)置趨勢線的顏色、線寬線型，調(diào)整圖表的大小和圖表的位置。

2.6制作動態(tài)樹高曲線

森林調(diào)查時，不同的林分，其徑階分布、株數(shù)多少是不固定的，為滿足不同林分都可使用，需要將樹高曲線制作成動態(tài)的，讓樹高曲線可隨數(shù)據(jù)的增減而動態(tài)變動。

2.6.1定義名稱

2.6.2動態(tài)樹高曲線

在散點圖表上單擊鼠標(biāo)右鍵，“選擇數(shù)據(jù)”快捷方式，進(jìn)入數(shù)據(jù)源對話框，點選“編輯”打開編輯數(shù)據(jù)系列對話框，重新編輯修改x軸系列值為“=林分平均高自動計算.xlsxr！胸徑”;Y軸系列值為“=林分平均高自動計算.xlsx'！樹高”。這樣，樹高曲線即可隨著B列和c列的數(shù)據(jù)增減而變動，實現(xiàn)樹高曲線的動態(tài)化。

3計算平均高

此處的平均高僅指林分條件平均高和林分徑階平均高。林分條件平均高是根據(jù)林分平均胸徑在樹高曲線上查得的樹高。林分徑階平均高是根據(jù)各個徑階中值在樹高曲線上所對應(yīng)的樹高，它是二元材積表法計算林分蓄積量的主要因子。傳統(tǒng)方法是在手繪樹高曲線上查得，費時費勁效率低，精度低還易出錯。也可在Excel中利用擬合的曲線方程，代人徑階中值手工輸入計算公式計算徑階平均高，但是，胸徑一樹高數(shù)據(jù)發(fā)生變動則擬合曲線方程的參數(shù)值也會發(fā)生改變，這時，就得重新輸入新的計算公式，不夠人性化。若能自動提取曲線方程的參數(shù)，則可自動利用曲線方程完成林分條件平均高和各徑階平均高的計算。

3.1提取曲線方程R2值和參數(shù)

3.1.1提取R2值

注：對提取的值用ROUNDO函數(shù)四舍五入保留8位小數(shù);公式中的符號均為英文輸入法狀態(tài)下的符號（下同），否則會出錯。公式中的“樹高”是c列樹高的定義名稱，“胸徑”是B列胸徑的定義名稱，R²值提取結(jié)果見圖2-②。圖1中，E19-J19也是利用上述提取公式所得。

3.1.2提取參數(shù)

二次多項式（簡稱二次式，下同）有A、B、C三個參數(shù)，三次多項式（簡稱三次式，下同）有A、B、C、D四個參數(shù)，其他曲線方程只有A和B兩個參數(shù)。各參數(shù)提取結(jié)果見圖2一③，提取曲線方程參數(shù)的公式如下（保留8位小數(shù)）：

3.1.3列示曲線公式

用字符連接符（&）將所提取的參數(shù)按曲線方程表達(dá)式的形式連接成公式形式。公式連接后的結(jié)果見圖2一④，各個曲線

至此，完成了平均高的自動計算。使用時，先錄入胸徑一樹高調(diào)查結(jié)果，之后在圖表中雙擊趨勢線打開“趨勢線選項”，選擇恰當(dāng)?shù)那€作為擬合的樹高曲線，然后，在D17單元格的下拉列表中選擇相應(yīng)的曲線類型即可計算林分條件平均高和徑階平均高。

4結(jié)論與討論

通過定義“樹高”和“胸徑”名稱，實現(xiàn)擬合的樹高曲線可隨數(shù)據(jù)的增減變化而實時變動，利用Excel函數(shù)提取出擬合方程的參數(shù)，可得到了樹高曲線方程，這樣就可利用樹高曲線方程實現(xiàn)林分的平均高和徑階平均高計算的自動化，解決了因不同林分調(diào)查的數(shù)據(jù)不同，重復(fù)擬合樹高曲線和計算的麻煩，提高了計算效率，在生產(chǎn)實踐中具有現(xiàn)實意義。