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

蔣興明

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

關鍵詞：Excel;樹高曲線方程;林分平均高;徑階平均高

中圖分類號：TP311 文獻標識碼：A

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

1背景

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

2擬合樹高曲線

2.1錄入數據

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

2.2繪制散點圖

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

2.3添加趨勢線

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

2.4選擇樹高曲線方程

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

2.5設置樹高曲線圖表格式

2.5.1設置水平軸的格式

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

2.5.2設置曲線方程的格式

對圖表中的曲線方程進行設置，數字類別選擇“數字”格式，小數位數為8位或與材積公式參數的小數位數一致，取消千分位分隔符。最后再適當調整曲線方程的字體大小。

此外將圖表的標題修改為“樹高曲線”，添加次要網格線，最后再根據自己的需要設置趨勢線的顏色、線寬線型，調整圖表的大小和圖表的位置。

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

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

2.6.1定義名稱

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

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

3計算平均高

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

3.1提取曲線方程R2值和參數

3.1.1提取R2值

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

3.1.2提取參數

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

3.1.3列示曲線公式

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

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

4結論與討論

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