EXECL表格軟件在樣地調(diào)查計算中的應用

蘇振海

摘 要：本文采用FORECAST、LOOKUP和LN等函數(shù)編寫了樣地調(diào)查計算模板，并通過設置數(shù)據(jù)有效性和保護工作表等功能，設置了數(shù)據(jù)輸入的有效性檢驗，將模板需要輸入的內(nèi)容降到最低，不再需要復制粘貼過程，有效提高了工作效率，降低了誤輸入造成的錯誤，通過實驗取得了良好的效果。

關(guān)鍵詞：樣地調(diào)查;EXECL;計算

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

DOI：10.19754/j.nyyjs.20200515023

1 研究目的

樣地調(diào)查在森林規(guī)劃調(diào)查、森林采伐設計、森林資產(chǎn)評估、征占用林地設計中是最常用的方法[1]，特別是對人工林調(diào)查設計中，比角規(guī)測樹方法更為準確。在樣地調(diào)查中，最重要的是調(diào)查樣地的活立木蓄積，這就需要在外業(yè)計算平均胸徑（平方平均數(shù)），通過平均胸徑確定測高樹分布，根據(jù)這2個因子利用二元材積表或材積模型進行樣地蓄積計算。

2 模板基本功能

記錄胸徑及樹高實測記錄;自動計算胸徑平方平均值;自動計算各徑階樹高及樣地平均樹高;根據(jù)樹種自動計算單株蓄積、樣地蓄積并推算小班蓄積、小班株數(shù)等;對可能產(chǎn)生的錯誤輸入或誤修改進行保護、檢測并提示。

3 功能實現(xiàn)

3.1 樹種選擇錄入

采用模型計算蓄積時，樹種選擇決定了蓄積模型的參數(shù)，為方便實現(xiàn)自動計算功能和輸入，采取下拉菜單形式設計，通過點選就可輸入樹種。具體方法如下。

以筆者設計的表格樣式為例（下同），在A1單元格輸入“樹種選擇”，選擇B1作為顯示樹種名稱單元格，選中B1依次選擇工具欄中的“數(shù)據(jù)”-“數(shù)據(jù)有效性”，在彈出的界面中選擇“設置”，在“有效性條件”中選擇“允許”-“序列”在“來源”下選擇錄入表單的位置，如=$N$3∶$N$8（筆者只實驗了6個樹種）。為方便下面的編制，需在樹種名稱前增加序列值如“1遼東日落”或“a遼東日落”均可，并且表單按升序排列。后面會解釋原因。

3.2 胸徑及測高記錄

在單元格A3～f3中分別輸入“徑階、株數(shù)、加權(quán)平方和、徑階高、徑階單株蓄積、徑階蓄積合計”;在單元格I3～K3中分別輸入“樹種、胸徑、樹高”。A4～A36分別輸入6～70的整化徑階值（2cm），如胸徑徑階大于70，已經(jīng)超出蓄積模型適用范圍，可采取其它方法近似計算。

3.3 計算胸徑平方平均值

在C4單元格輸入公式：=A5*A5*B5或=A5^2*B5或POWER（A5，2）*B5均可，以下至C36復制公式即可。

C39中輸入“=SUM（C4∶C36）” （合計加權(quán)平方），B39輸入“=SUM （B4∶B36）” （合計株數(shù)），D39輸入“= （C39/B39）^0.5” （胸徑平方平均值）。

3.4 計算各徑階平均樹高

各徑階的平均樹高一般采用數(shù)式法，擬合樹高曲線再通過曲線求算各徑階平均樹高[2]。筆者沒用采用散點圖添加趨勢線擬合的方式進行求算，因為擬合后的曲線參數(shù)每次都不一樣，需要在公式中手動修改參數(shù)值才能計算徑階平均高，增加了產(chǎn)生錯誤錄入的機會，也不方便操作。根據(jù)多年的制表經(jīng)驗，遼寧省的樹高曲線一般都滿足對數(shù)模型，因此直接將胸徑進行對數(shù)變換，采用FORECAST函數(shù)直接求算回歸值。FORECAST函數(shù)的用途是根據(jù)一條線性回歸擬合線返回一個預測值。表達式為FORECAST （x，known_y's，known_x's），其中X為需要進行預測的數(shù)據(jù)點，Known_y's為因變量數(shù)組或數(shù)據(jù)區(qū)域，Known_x's為自變量數(shù)組或數(shù)據(jù)區(qū)域。

在L4單元格中輸入公式“=LN（J4）”L5～L36復制公式。進行實測胸徑變換。

在H4單元格中輸入公式“=LN（A4）”H5～H36復制公式。進行整化徑階變換。

在D4單元格中輸入公式“=FORECAST（H4，$K$4∶$K$36，$L$4∶$L$36）”D5～D36復制公式。進行徑階平均高求算。

求算結(jié)果與擬合曲線后帶入整化徑階求算徑階平均高完全一致。如果后期需要打印輸出樹高曲線及模型，可采用散點圖加趨勢線方式，本文不再贅述。在E39單元格中輸入公式“=FORECAST（LN（D39），K4∶K36，L4∶L36）”，計算樣地平均樹高。

3.5 計算蓄積

3.5.1 單株蓄積計算

遼寧省二元立木蓄積模型采用的都是山本式V=c0Dc1Hc2參數(shù)較少，使用方便。根據(jù)3.1中樹種選擇錄入結(jié)果自動查找3個參數(shù)。方法如下：

在C40單元格中輸入公式“=LOOKUP（B1，N3∶N8，O3∶O8）”查取參數(shù)c0

在C41單元格中輸入公式“=LOOKUP（B1，N3∶N8，P3∶P8）”查取參數(shù)c1

在C42單元格中輸入公式“=LOOKUP（B1，N3∶N8，Q3∶Q8）”查取參數(shù)c2

O3～Q8單元格為錄入好的相應樹種的二元材積表參數(shù)值。

這里LOOKUP函數(shù)，要查詢一個明確的值的時候，查詢列必須按升序排列。這也是為什么在3.1中，要求在樹種名稱前加“1”、“a”等方便用于排序的前綴的原因。

在E4單元格中輸入公式“=$C$40*（A4^$C$41）*D4^$C$42”E5～E36復制公式，求算處各整化徑階的單株蓄積。

3.5.2 樣地蓄積計算

在F4中輸入公式“=E4*B4”F5～F36復制公式，求出各徑階蓄積合計。在F39單元格中輸入公式“=SUM（F4∶F36）”，求出樣地蓄積合計。

3.5.3 小班蓄積計算

在D41和D42單元格中分別輸入樣地總面積（hm2）和小班總面積（hm2），在F41中輸入“=F39/D41”求出小班每公頃蓄積，在I41中輸入“=E41*F41”，求出小班蓄積。同樣也可以簡單地算出每公頃株數(shù)、小班總株數(shù)、每公頃斷面積等指標。

4 表格整理

4.1 隱藏不必要顯示的單元格

對于計算的中間過程及無用的列可全部隱藏，如G、H、L列和表單列N～Q列。

4.2 數(shù)據(jù)有效性設置

以測高記錄的胸徑輸入列J列為例。首先用鼠標選擇J4-J36選擇工具欄中的“數(shù)據(jù)”-“數(shù)據(jù)有效性”，在彈出的界面中選擇“設置”，在“有效性條件”中選擇“允許”-“小數(shù)”，在“數(shù)據(jù)（D）”下拉菜單中選擇“介于”，“最小值”輸入5，“最大值”輸入70.9，然后將“忽略空值”前面的勾去掉。這樣設置后，錄入者輸入不在范圍內(nèi)的數(shù)值、其它字符格式內(nèi)容或清空都會顯示錯誤提示。如果在“出錯警告”的“錯誤信息”中輸入“應介于5～70.9”，就會在出錯時彈出提示，否則顯示系統(tǒng)默認內(nèi)容。同樣方法設置K列，條件為“小數(shù)”≥1.3，“忽略空值”前的勾保留;B列條件為“整數(shù)”≥0，“忽略空值”前的勾保留;B41單元格，條件為“小數(shù)”，介于最小值“0.0001”，最大值“=E41”（表示樣地總面積必須大于0且小于等于小班面積），“忽略空值”前面的勾去掉;E41單元格，條件為“小數(shù)”，大于或等于最小值“=D41”，“忽略空值”前面的勾去掉。到此，所有需要設置有效性的單元格都設置完畢（其它“錯誤信息”內(nèi)容略）。

4.3 保護工作表

將表格中可修改輸入的部分填充成綠色，并填寫初始值，其余不允許修改的進行保護。保護方法如下。

點擊工具中“審閱”-“允許用戶編輯區(qū)域”在彈出界面上點擊“新建”，在新彈出界面上點擊“引用單元格”下右側(cè)按鈕用鼠標進行選擇。點擊“確定”返回上級界面點擊“保護工作表”可設置密碼，和用戶可進行的操作[3]。整理前后的界面如圖1、2。

5 模板擴充、修改

模板擴充修改主要是修改參數(shù)值或是增加新的樹種參數(shù)，只要將保護狀態(tài)解除，取消所有隱藏列，然后修改參數(shù)表相應位置，并且修改引用該位置的公式選取范圍即可，主要涉及B1、C40、C41、C42單元格內(nèi)的公式。

6 使用的注意事項

測高記錄輸入胸徑列（J列）內(nèi)不能出現(xiàn)空值，筆者已經(jīng)預設了“5”作為初始值。因為在變換時采用的是LN函數(shù)，所以必須大于“0”的數(shù)值格式。后面的樹高可以是空值。用來占位的初始值只要是在允許輸入范圍內(nèi)，無論是多大都不影響對徑階高的回歸預測。實測胸徑和樹高輸入時，位置和大小沒有順序要求，只要求一一對應。

在要刪除上次輸入內(nèi)容時，雖然設置了空值的檢測，但使用Delete鍵或使用清除內(nèi)容功能仍會產(chǎn)生錯誤，如果產(chǎn)生了錯誤，只需正常填寫（J列必須都有值），不影響計算精度。使用Backspace鍵刪除會提示錯誤信息。

樹種名稱的選擇可先可后，不影響計算結(jié)果。

7 模板優(yōu)缺點

7.1 模板優(yōu)點

該模板在使用時只需輸入徑階檢尺記錄、測高記錄和樣地、小班面積，不需要進行參數(shù)修改、復制粘貼等過程，操作簡單，方便外業(yè)使用;設置了模板保護、誤輸入檢測、提示信息，十分適合基層人員使用;對系統(tǒng)幾乎沒有要求，只要能使用EXCEL軟件或APP都可以使用;擴充修改十分方便，按照本文提示操作，只要基本掌握EXCEL軟件的基層人員都可實現(xiàn)。

7.2 模板缺點

不能實現(xiàn)分樹種一次計算出各樹種蓄積和樹種組成功能;樹高曲線模擬統(tǒng)一采用對數(shù)模型，可能存在不是最優(yōu)選模型問題;蓄積模型都是山本式，如果出現(xiàn)新的樹種蓄積模型不是山本式的，模板的修改還需要一定的技術(shù)，基層人員很難自己修改。

8 結(jié)束語

本方法主要使用了FORECAST、LOOKUP和LN等函數(shù)，將人工輸入內(nèi)容降到最少，有效降低一線設計人員及內(nèi)業(yè)計算人員的勞動強度，同時保證了輸入的準確性。經(jīng)過在全省多個林場內(nèi)測試，其求算結(jié)果與傳統(tǒng)方法完全一樣，節(jié)約了大量的工作時間，取得了良好的效果。

參考文獻

[1] 馮家祥，袁金霞，王志勃.森林資源調(diào)查應用excel表格函數(shù)計算標準地調(diào)查因子的方法[J].農(nóng)民致富之友，2019（8）：205.

[2] 應啟圍.基于Excel的伐區(qū)樣地調(diào)查數(shù)據(jù)計算方法[J].林業(yè)調(diào)查規(guī)劃，2009（5）：4.

[3] 王雍樹.EXCEL軟件在林木蓄積計算中的應用[J].綠色科技，2017（7）：216.

（責任編輯 周康）