基于EXCEL規(guī)劃求解一鍵選優(yōu)樹高曲線模型

蔣興明

摘要：描述林分胸徑與樹高關(guān)系的樹高曲線模型很多，不同樹種林分的最優(yōu)樹高曲線模型不同，Excel規(guī)劃求解可求解非線性模型的參數(shù)，獲得更優(yōu)的參數(shù)估計，但一次只能求解一個模型，為了提高效率，減少重復(fù)操作，基于Excel的VBA，利用一個按鈕調(diào)用規(guī)劃求解（Solver.xlam）程序?qū)崿F(xiàn)一鍵選出最優(yōu)的模型。

關(guān)鍵詞：Excel函數(shù);規(guī)劃求解;樹高曲線模型;評價指標(biāo);VBA代碼

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

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

1 背景

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

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

2 規(guī)劃求解工具介紹

規(guī)劃求解是數(shù)學(xué)建模中的一項重要內(nèi)容，其原理是在符合約束條件的要求下不斷調(diào)整可變量，從而改變與可變量關(guān)聯(lián)的其他數(shù)據(jù)，進(jìn)一步影響目標(biāo)值，最終使目標(biāo)值達(dá)到設(shè)置的控制值，即為達(dá)到最優(yōu)解[9-10]。

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

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

3 創(chuàng)建胸徑樹高整理表

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

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

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

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

4 樹高曲線模型規(guī)劃求解

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

4.1 參數(shù)非負(fù)模型規(guī)劃求解

4.1.1 創(chuàng)建參數(shù)非負(fù)模型計算工作表

將Sheet2命名為“參數(shù)非負(fù)模型”，用于規(guī)劃求解參數(shù)非負(fù)模型的參數(shù)值及模型評價的指標(biāo)值（見圖1-②、④）的處理，現(xiàn)在先將1～2行和A～D列的內(nèi)容先完成，其他稍后處理。

4.1.2 創(chuàng)建參數(shù)非負(fù)模型預(yù)測值計算工作表

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

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

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

4.1.3 定義參數(shù)非負(fù)模型預(yù)測值名稱

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

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

4.1.4 參數(shù)非負(fù)模型評價指標(biāo)計算

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

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

4.1.5 參數(shù)非負(fù)模型參數(shù)賦初值

作為計算模型參數(shù)的單元格若不賦初值，規(guī)劃求解時默認(rèn)為零，但有些模型不賦初值則會出現(xiàn)預(yù)測值和評價指標(biāo)計算出錯提示，尤其是殘差平方和（SSE）公式計算出錯，就無法進(jìn)行規(guī)劃求解，因為殘差平方和（SSE）是規(guī)劃求解的目標(biāo)公式，此時就需要對參數(shù)賦予適當(dāng)?shù)某踔礫13-14]。初值的選擇不同，會影響規(guī)劃求解收斂的速度[15]和精度，有時還會出現(xiàn)異常結(jié)果[16]。賦初值后按F9鍵刷新計算。

參考文獻(xiàn)：

[1] 杜志， 甘世書. 基于BP神經(jīng)網(wǎng)絡(luò)的杉木和馬尾松樹高曲線模型研究[J]. 中南林業(yè)調(diào)查規(guī)劃， 2017， 36（4）： 36-39.

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

[3] 孟憲宇. 測樹學(xué)[M]. 北京： 中國林業(yè)出版社， 2006.

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

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

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

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

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

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

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

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

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

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

[14] 徐海霞， 任紅松， 袁繼勇， 等. 用EXCEL及其“規(guī)劃求解”功能擬合曲線方程[J]. 農(nóng)業(yè)網(wǎng)絡(luò)信息， 2004（2）： 37-39.

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

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

【通聯(lián)編輯：謝媛媛】