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

1、    execl表格軟件在樣地調(diào)查計算中的應(yīng)用    蘇振海摘 要：本文采用forecast、lookup和ln等函數(shù)編寫了樣地調(diào)查計算模板，并通過設(shè)置數(shù)據(jù)有效性和保護工作表等功能，設(shè)置了數(shù)據(jù)輸入的有效性檢驗，將模板需要輸入的內(nèi)容降到最低，不再需要復(fù)制粘貼過程，有效提高了工作效率，降低了誤輸入造成的錯誤，通過實驗取得了良好的效果。關(guān)鍵詞：樣地調(diào)查;execl;計算：s159       ：adoi：10.19754/j.nyyjs.202005150231 研究目的樣地調(diào)查在森林規(guī)劃調(diào)查、森林采伐設(shè)計、森林資產(chǎn)評估、征占用

2、林地設(shè)計中是最常用的方法1，特別是對人工林調(diào)查設(shè)計中，比角規(guī)測樹方法更為準確。在樣地調(diào)查中，最重要的是調(diào)查樣地的活立木蓄積，這就需要在外業(yè)計算平均胸徑（平方平均數(shù)），通過平均胸徑確定測高樹分布，根據(jù)這2個因子利用二元材積表或材積模型進行樣地蓄積計算。2 模板基本功能記錄胸徑及樹高實測記錄;自動計算胸徑平方平均值;自動計算各徑階樹高及樣地平均樹高;根據(jù)樹種自動計算單株蓄積、樣地蓄積并推算小班蓄積、小班株數(shù)等;對可能產(chǎn)生的錯誤輸入或誤修改進行保護、檢測并提示。3 功能實現(xiàn)3.1 樹種選擇錄入采用模型計算蓄積時，樹種選擇決定了蓄積模型的參數(shù)，為方便實現(xiàn)自動計算功能和輸入，采取下拉菜單形式設(shè)計，通過點

3、選就可輸入樹種。具體方法如下。以筆者設(shè)計的表格樣式為例（下同），在a1單元格輸入“樹種選擇”，選擇b1作為顯示樹種名稱單元格，選中b1依次選擇工具欄中的“數(shù)據(jù)”-“數(shù)據(jù)有效性”，在彈出的界面中選擇“設(shè)置”，在“有效性條件”中選擇“允許”-“序列”在“來源”下選擇錄入表單的位置，如=$n$3$n$8（筆者只實驗了6個樹種）。為方便下面的編制，需在樹種名稱前增加序列值如“1遼東日落”或“a遼東日落”均可，并且表單按升序排列。后面會解釋原因。3.2 胸徑及測高記錄在單元格a3f3中分別輸入“徑階、株數(shù)、加權(quán)平方和、徑階高、徑階單株蓄積、徑階蓄積合計”;在單元格i3k3中分別輸入“樹種、胸徑、樹高”。

4、a4a36分別輸入670的整化徑階值（2cm），如胸徑徑階大于70，已經(jīng)超出蓄積模型適用范圍，可采取其它方法近似計算。3.3 計算胸徑平方平均值在c4單元格輸入公式：=a5*a5*b5或=a52*b5或power（a5，2）*b5均可，以下至c36復(fù)制公式即可。c39中輸入“=sum（c4c36）” （合計加權(quán)平方），b39輸入“=sum （b4b36）” （合計株數(shù)），d39輸入“= （c39/b39）0.5” （胸徑平方平均值）。3.4 計算各徑階平均樹高各徑階的平均樹高一般采用數(shù)式法，擬合樹高曲線再通過曲線求算各徑階平均樹高2。筆者沒用采用散點圖添加趨勢線擬合的方式進行求算，因為擬合后的

5、曲線參數(shù)每次都不一樣，需要在公式中手動修改參數(shù)值才能計算徑階平均高，增加了產(chǎn)生錯誤錄入的機會，也不方便操作。根據(jù)多年的制表經(jīng)驗，遼寧省的樹高曲線一般都滿足對數(shù)模型，因此直接將胸徑進行對數(shù)變換，采用forecast函數(shù)直接求算回歸值。forecast函數(shù)的用途是根據(jù)一條線性回歸擬合線返回一個預(yù)測值。表達式為forecast （x，known_y's，known_x's），其中x為需要進行預(yù)測的數(shù)據(jù)點，known_y's為因變量數(shù)組或數(shù)據(jù)區(qū)域，known_x's為自變量數(shù)組或數(shù)據(jù)區(qū)域。在l4單元格中輸入公式“=ln（j4）”l5l36復(fù)制公式。進行實測胸徑變換。在h

6、4單元格中輸入公式“=ln（a4）”h5h36復(fù)制公式。進行整化徑階變換。在d4單元格中輸入公式“=forecast（h4，$k$4$k$36，$l$4$l$36）”d5d36復(fù)制公式。進行徑階平均高求算。求算結(jié)果與擬合曲線后帶入整化徑階求算徑階平均高完全一致。如果后期需要打印輸出樹高曲線及模型，可采用散點圖加趨勢線方式，本文不再贅述。在e39單元格中輸入公式“=forecast（ln（d39），k4k36，l4l36）”，計算樣地平均樹高。3.5 計算蓄積3.5.1 單株蓄積計算遼寧省二元立木蓄積模型采用的都是山本式v=c0dc1hc2參數(shù)較少，使用方便。根據(jù)3.1中樹種選擇錄入結(jié)果自動查找

7、3個參數(shù)。方法如下：在c40單元格中輸入公式“=lookup（b1，n3n8，o3o8）”查取參數(shù)c0在c41單元格中輸入公式“=lookup（b1，n3n8，p3p8）”查取參數(shù)c1在c42單元格中輸入公式“=lookup（b1，n3n8，q3q8）”查取參數(shù)c2o3q8單元格為錄入好的相應(yīng)樹種的二元材積表參數(shù)值。這里lookup函數(shù)，要查詢一個明確的值的時候，查詢列必須按升序排列。這也是為什么在3.1中，要求在樹種名稱前加“1”、“a”等方便用于排序的前綴的原因。在e4單元格中輸入公式“=$c$40*（a4$c$41）*d4$c$42”e5e36復(fù)制公式，求算處各整化徑階的單株蓄積。3.5

8、.2 樣地蓄積計算在f4中輸入公式“=e4*b4”f5f36復(fù)制公式，求出各徑階蓄積合計。在f39單元格中輸入公式“=sum（f4f36）”，求出樣地蓄積合計。3.5.3 小班蓄積計算在d41和d42單元格中分別輸入樣地總面積（hm2）和小班總面積（hm2），在f41中輸入“=f39/d41”求出小班每公頃蓄積，在i41中輸入“=e41*f41”，求出小班蓄積。同樣也可以簡單地算出每公頃株數(shù)、小班總株數(shù)、每公頃斷面積等指標。4 表格整理4.1 隱藏不必要顯示的單元格對于計算的中間過程及無用的列可全部隱藏，如g、h、l列和表單列nq列。4.2 數(shù)據(jù)有效性設(shè)置以測高記錄的胸徑輸入列j列為例。首先用

9、鼠標選擇j4-j36選擇工具欄中的“數(shù)據(jù)”-“數(shù)據(jù)有效性”，在彈出的界面中選擇“設(shè)置”，在“有效性條件”中選擇“允許”-“小數(shù)”，在“數(shù)據(jù)（d）”下拉菜單中選擇“介于”，“最小值”輸入5，“最大值”輸入70.9，然后將“忽略空值”前面的勾去掉。這樣設(shè)置后，錄入者輸入不在范圍內(nèi)的數(shù)值、其它字符格式內(nèi)容或清空都會顯示錯誤提示。如果在“出錯警告”的“錯誤信息”中輸入“應(yīng)介于570.9”，就會在出錯時彈出提示，否則顯示系統(tǒng)默認內(nèi)容。同樣方法設(shè)置k列，條件為“小數(shù)”1.3，“忽略空值”前的勾保留;b列條件為“整數(shù)”0，“忽略空值”前的勾保留;b41單元格，條件為“小數(shù)”，介于最小值“0.0001”，最大

10、值“=e41”（表示樣地總面積必須大于0且小于等于小班面積），“忽略空值”前面的勾去掉;e41單元格，條件為“小數(shù)”，大于或等于最小值“=d41”，“忽略空值”前面的勾去掉。到此，所有需要設(shè)置有效性的單元格都設(shè)置完畢（其它“錯誤信息”內(nèi)容略）。4.3 保護工作表將表格中可修改輸入的部分填充成綠色，并填寫初始值，其余不允許修改的進行保護。保護方法如下。點擊工具中“審閱”-“允許用戶編輯區(qū)域”在彈出界面上點擊“新建”，在新彈出界面上點擊“引用單元格”下右側(cè)按鈕用鼠標進行選擇。點擊“確定”返回上級界面點擊“保護工作表”可設(shè)置密碼，和用戶可進行的操作3。整理前后的界面如圖1、2。5 模板擴充、修改模板

11、擴充修改主要是修改參數(shù)值或是增加新的樹種參數(shù)，只要將保護狀態(tài)解除，取消所有隱藏列，然后修改參數(shù)表相應(yīng)位置，并且修改引用該位置的公式選取范圍即可，主要涉及b1、c40、c41、c42單元格內(nèi)的公式。6 使用的注意事項測高記錄輸入胸徑列（j列）內(nèi)不能出現(xiàn)空值，筆者已經(jīng)預(yù)設(shè)了“5”作為初始值。因為在變換時采用的是ln函數(shù)，所以必須大于“0”的數(shù)值格式。后面的樹高可以是空值。用來占位的初始值只要是在允許輸入范圍內(nèi)，無論是多大都不影響對徑階高的回歸預(yù)測。實測胸徑和樹高輸入時，位置和大小沒有順序要求，只要求一一對應(yīng)。在要刪除上次輸入內(nèi)容時，雖然設(shè)置了空值的檢測，但使用delete鍵或使用清除內(nèi)容功能仍會產(chǎn)

12、生錯誤，如果產(chǎn)生了錯誤，只需正常填寫（j列必須都有值），不影響計算精度。使用backspace鍵刪除會提示錯誤信息。樹種名稱的選擇可先可后，不影響計算結(jié)果。7 模板優(yōu)缺點7.1 模板優(yōu)點該模板在使用時只需輸入徑階檢尺記錄、測高記錄和樣地、小班面積，不需要進行參數(shù)修改、復(fù)制粘貼等過程，操作簡單，方便外業(yè)使用;設(shè)置了模板保護、誤輸入檢測、提示信息，十分適合基層人員使用;對系統(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)容降到最少，有效降低一線設(shè)計人員及內(nèi)業(yè)計算人員的勞動強度，同時保證了輸入的準確性。經(jīng)過在全省多個林場內(nèi)測試，其求算結(jié)果與傳統(tǒng)方法完全一