蔣興明

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

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

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

文章編號(hào)：1009-3044（2020）17-0072-04

1 背景

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

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

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

規(guī)劃求解是數(shù)學(xué)建模中的一項(xiàng)重要內(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ī)劃求解”工具，是在滿足工作表上其他公式（約束條件）單元格的值時(shí)，可通過(guò)更改其他單元格（決策變量）的值來(lái)確定目標(biāo)單元格中公式（目標(biāo)公式）的優(yōu)化（最大或最?。┲?，“規(guī)劃求解”工具能進(jìn)行非線性方程的擬合獲得更優(yōu)的參數(shù)估計(jì)， 而且操作簡(jiǎn)單，易于掌握[11-12]。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

對(duì)COUNT（）函數(shù)計(jì)數(shù)結(jié)果減1，是因?yàn)榈谝恍袨閿?shù)字編號(hào)?！靶蛱?hào)_非負(fù)模型”是對(duì)“參數(shù)非負(fù)模型”工作表中A列“序號(hào)”進(jìn)行定義名稱，為后面對(duì)模型計(jì)數(shù)使用。

4.1.4 參數(shù)非負(fù)模型評(píng)價(jià)指標(biāo)計(jì)算

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

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

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

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

參考文獻(xiàn)：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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