基于EXCEL規劃求解一鍵選優樹高曲線模型

蔣興明

摘要：描述林分胸徑與樹高關系的樹高曲線模型很多，不同樹種林分的最優樹高曲線模型不同，Excel規劃求解可求解非線性模型的參數，獲得更優的參數估計，但一次只能求解一個模型，為了提高效率，減少重復操作，基于Excel的VBA，利用一個按鈕調用規劃求解（Solver.xlam）程序實現一鍵選出最優的模型。

關鍵詞：Excel函數;規劃求解;樹高曲線模型;評價指標;VBA代碼

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

文章編號：1009-3044（2020）17-0072-04

1 背景

林分調查因子之間，存在著一定的相關關系，其中樹高和胸徑是林分生長的兩個重要指標，二者間成正相關，胸徑-樹高模型在林業生產實踐與科學研究中具有極為重要的作用[1]。樹高和胸徑的相關曲線稱為樹高曲線，描述樹種樹高曲線的模型為樹高曲線模型，樹高曲線模型并不是唯一的，研究時必須根據樹種林分類型的不同選取不同的樹高曲線模，最適合樹種林分的樹高曲線是最優樹高曲線模型[2]，如何選取樹種的最優樹高曲線模型則極為關鍵。

一些學者研究了各種樹高模型在不同林分的最優擬合[3-8]，還有其他學者使用其他多個模型，如果對這些模型逐個單獨計算擬合，從中選優，計算工作量必定很大。限于篇幅，本文僅選用其中的25個模型（見圖1-②、圖4）作示例，采用Excel（2016版）的規劃求解（Solver.xlam）進行一鍵批量計算完成選優，提高了計算選優的效率。

2 規劃求解工具介紹

規劃求解是數學建模中的一項重要內容，其原理是在符合約束條件的要求下不斷調整可變量，從而改變與可變量關聯的其他數據，進一步影響目標值，最終使目標值達到設置的控制值，即為達到最優解[9-10]。

Excel的“規劃求解”工具，是在滿足工作表上其他公式（約束條件）單元格的值時，可通過更改其他單元格（決策變量）的值來確定目標單元格中公式（目標公式）的優化（最大或最小）值，“規劃求解”工具能進行非線性方程的擬合獲得更優的參數估計， 而且操作簡單，易于掌握[11-12]。

“規劃求解”工具在使用前需要先加載。打開Excel后，轉到“文件”選項卡的“選項”對話框，選擇左側列表的“加載項”，單擊“轉到”按鈕打開“加載項”對話框，勾選“規劃求解加載項”，加載好的規劃求解工具在“數據”選項卡的分析組中。

3 創建胸徑樹高整理表

將Sheet1命名為“胸徑樹高整理表”，用來錄入林分調查所得的胸徑-樹高整理結果。本文使用《測樹學》第3版（孟憲宇）表2-13測高記錄表的數據為例（見圖1-①）。為了方便引用數據，利用“名稱管理器”對C列（胸徑）和D列（樹高）分別定義名稱如下：

名稱 ? ? ? ? ? ? ? ? ? ? ? 引用位置

胸徑 ? ? ? ?=OFFSET（胸徑樹高整理表！$C：$C，2，，COUNT（胸徑樹高整理表！$C：$C））

樹高 ? ? ? ?=OFFSET（胸徑樹高整理表！$D：$D，2，，COUNT（胸徑樹高整理表！$D：$D））

4 樹高曲線模型規劃求解

根據規劃求解的“使無約束變量為非負數”復選框，將樹高曲線模型分為兩類：一類是模型中參數不為非負數的模型（此稱為參數非負模型，見圖1-②），另一類則是參數無約束條件的模型（此稱為無約束模型，見圖4）。

4.1 參數非負模型規劃求解

4.1.1 創建參數非負模型計算工作表

將Sheet2命名為“參數非負模型”，用于規劃求解參數非負模型的參數值及模型評價的指標值（見圖1-②、④）的處理，現在先將1～2行和A～D列的內容先完成，其他稍后處理。

4.1.2 創建參數非負模型預測值計算工作表

將Sheet3命名為“參數非負模型預測值”，該工作表用于計算各個參數非負模型的預測值（見圖2）。各列中計算公式按模型結構式編輯（見表1）。

2.完成公式首行輸入后進行快速填充各列，公式中的符號均為英文輸入法的符號，下同。

3.在“參數非負模型”工作表對參數賦初值后按F9可刷新計算。 ]

4.1.3 定義參數非負模型預測值名稱

為了方便引用各個模型的預測值，利用“名稱管理器”（Ctrl+F3）分別對各個模型的預測值進行名稱定義（見表2）。

對COUNT（）函數計數結果減1，是因為第一行為數字編號。“序號_非負模型”是對“參數非負模型”工作表中A列“序號”進行定義名稱，為后面對模型計數使用。

4.1.4 參數非負模型評價指標計算

現在再來處理“參數非負模型”工作表中的模型評價指標。不同模型規劃求解后，擬合質量如何，要通過評價指標來進行判斷，篇幅所限，在此只選5個評價指標作為參考（見圖1-⑤，其中殘差平方和作為參數估計時的目標指標，各個評價指標的計算公式見表3。

當評價指標計算公式首行輸完后，向下快速填充，但要逐行修改公式中的預測值001～預測值010，使預測值編號與模型序號對應。若出現“#REF！”等出錯提示，是因為參數單元格未賦初值。

4.1.5 參數非負模型參數賦初值

作為計算模型參數的單元格若不賦初值，規劃求解時默認為零，但有些模型不賦初值則會出現預測值和評價指標計算出錯提示，尤其是殘差平方和（SSE）公式計算出錯，就無法進行規劃求解，因為殘差平方和（SSE）是規劃求解的目標公式，此時就需要對參數賦予適當的初值[13-14]。初值的選擇不同，會影響規劃求解收斂的速度[15]和精度，有時還會出現異常結果[16]。賦初值后按F9鍵刷新計算。

參考文獻：

[1] 杜志， 甘世書. 基于BP神經網絡的杉木和馬尾松樹高曲線模型研究[J]. 中南林業調查規劃， 2017， 36（4）： 36-39.

[2] 陳立莉. 樹種樹高曲線模型的研究[D]. 哈爾濱： 東北林業大學， 2013.

[3] 孟憲宇. 測樹學[M]. 北京： 中國林業出版社， 2006.

[4] 羅佳， 戴成棟， 田育新， 等. 碳匯林主要建群種樹高和胸徑生長模型構建[J]. 湖南林業科技， 2016， 43（6）： 46-50.

[5] 王明亮， 李希菲. 非線性樹高曲線模型的研究[J]. 林業科學研究， 2000， 13（1）： 74-79.

[6] 胥輝， 全宏波， 王斌. 思茅松標準樹高曲線的研究[J]. 西南林學院學報， 2000， 20（2）： 74-77.

[7] 向瑋， 呂勇， 邱林. 湖南黃豐橋林場杉木樹高曲線模擬研制[J]. 中南林業調查規劃， 2007， 26（1）： 16-18.

[8] 尹惠妍， 張志偉， 楊小林， 等. 西藏林芝云杉樹高曲線模型研究[J]. 江蘇農業科學， 2020， 48（2）： 150-154.

[9] 陳鵬程， 劉建新. Excel規劃求解在陶瓷配方設計中的應用[J]. 佛山陶瓷， 2019， 29（9）： 44-46.

[10] 曹樹國. Excel在非線性方程求解過程中的應用[J]. 計算機與信息技術， 2007， 15（9）： 80-81.

[11] 余亮. 利用Excel軟件進行非線性擬合的非編程方法[J]. 微型機與應用， 2000， 19（5）： 16-17.

[12] 賀曉鵬， 賀浩華， 朱昌蘭. 用Excel擬合Richards方程[J]. 計算機與農業， 1998（3）： 16-18.

[13] 胡亮. 非線性擬合的初值問題[J]. 吉首大學學報（自然科學版）， 2003， 24（1）： 37-39.

[14] 徐海霞， 任紅松， 袁繼勇， 等. 用EXCEL及其“規劃求解”功能擬合曲線方程[J]. 農業網絡信息， 2004（2）： 37-39.

[15] 鄭國清， 孫書安， 劉九芬. 純非線性回歸模型參數估計的新方法及其應用[J]. 河南農業大學學報， 1995， 29（2）： 200-204.

[16] 顏清， 彭小平. 工程實驗數據的非線性擬合方法[J]. 計算機與應用化學， 2015， 32（3）： 365-368.

【通聯編輯：謝媛媛】