Excel電子表格在數(shù)學(xué)規(guī)劃中的應(yīng)用

【摘 要】利用Excel的“規(guī)劃求解”工具可以很好地解決包括線性規(guī)劃和非線性規(guī)劃在內(nèi)的數(shù)學(xué)規(guī)劃計算問題。本文以實際生產(chǎn)計劃的規(guī)劃問題為例介紹了在Excel中實現(xiàn)“規(guī)劃求解”的具體步驟，建立數(shù)學(xué)規(guī)劃模型的基本原則以及在表格中輸入數(shù)據(jù)應(yīng)注意的問題。

【關(guān)鍵詞】Excel；規(guī)劃求解；線性規(guī)劃；規(guī)劃模型

在實際工作中，如：生產(chǎn)與銷售、人員調(diào)度、材料調(diào)配、運輸管理等問題涉及很多的因素、無數(shù)方案需要就算、比較和分析，希望能夠合理利用有限的人力、物力、財力等資源，得到最佳的經(jīng)濟效果，包括達(dá)到產(chǎn)量最高、利潤最大、成本最小、資源消耗最少等目標(biāo)。這些問題都屬于數(shù)學(xué)規(guī)劃研究的內(nèi)容，具體包括：線性規(guī)劃、非線性規(guī)劃、多目標(biāo)規(guī)劃、動態(tài)規(guī)劃、組合優(yōu)化等，被廣泛應(yīng)用到各個領(lǐng)域中。除了一些專業(yè)化的軟件如Matlab、LINDO等，Excel的規(guī)劃求解工具是一個不需用戶編程即可實現(xiàn)對數(shù)據(jù)進行因果分析的有效而簡潔的工具。

規(guī)劃問題歸結(jié)起來一般分為兩類：一是確定了某個任務(wù)，研究如何使用最少的人力、物力、財力完成它；二是研究如何在已有人力、物力、財力的有限資源下獲得最大的收益。

1.規(guī)劃模型的建立

在進行規(guī)劃求解時首先要將實際問題數(shù)學(xué)化、模型化，也就是將實際問題用一組決策變量、一組用不等式或等式表示的約束條件以及目標(biāo)函數(shù)來表示，這是求解規(guī)劃問題的關(guān)鍵。其中，決策變量是指每個規(guī)劃問題都有一組需要求解的未知數(shù)（x1，x2，…，xn），這組決策變量的一組確定值就代表一個具體的規(guī)劃方案；約束條件是指對于規(guī)劃問題的決策變量通常都有一定的限制條件，通常是用包含決策變量的不等式或等式來表示；目標(biāo)函數(shù)是指每個問題都有一個明確的目標(biāo)，通常是用與決策變量有關(guān)的表達(dá)式表示。

從問題提出到模型建立一般經(jīng)歷的過程為：尋求決策，即應(yīng)回答的問題是什么；確定決策變量；確定優(yōu)化目標(biāo)，用決策變量表示的利潤、成本等；尋找約束條件，決策變量之間、決策變量與常量之間的關(guān)系；構(gòu)成數(shù)學(xué)模型，將目標(biāo)以及約束放在一起，寫成數(shù)學(xué)表達(dá)式。

實例：某廠制定下一年度生產(chǎn)計劃。按照合同，第1季度到第4季度需要分別向客戶供貨80、60、60、90。該廠季度最大生產(chǎn)能力為150，生產(chǎn)費用為：，x為季度生產(chǎn)的數(shù)量。從函數(shù)可以看出生產(chǎn)規(guī)模越大，平均生產(chǎn)費用越低，若生產(chǎn)數(shù)量大于交貨數(shù)量，多余部分可以下季度交貨，但需支付每個15元的存儲費用。如何安排各季度的產(chǎn)量，既保證完成供貨合同，同時又使得各種費用總體最少？

Step1：需要回答的問題是什么？

A.各季度的產(chǎn)量如何安排？B.費用是多少？

Step2：決策變量？

各個季度的產(chǎn)量，分別用x1，x2，x3和x4表示。

Step3：目標(biāo)函數(shù)

目標(biāo)：費用最小，總費用為生產(chǎn)費用與存儲費用之和。

Step4：約束條件

交貨數(shù)量約束與生產(chǎn)能力約束

Step5：數(shù)學(xué)模型

其中y為實際生產(chǎn)數(shù)量與交貨數(shù)量的差。

2.“規(guī)劃求解”工具的安裝及數(shù)據(jù)、公式的輸入

默認(rèn)安裝Office辦公軟件時，不加載規(guī)劃求解工具，使用前必須進行加載。操作步驟有：Step1：打開“加載宏”對話框。單擊菜單“工具”，“加載宏”，系統(tǒng)彈出“加載宏”對話框。Step2：選擇“規(guī)劃求解”復(fù)選框，單擊“確定”按鈕。安裝完后，在“工具”菜單中會出現(xiàn)“規(guī)劃求解”命令。

建立好規(guī)劃模型后，就可以將規(guī)劃好的模型基本數(shù)據(jù)和公式輸入到工作表中，具體步驟如下：

Step1：輸入有關(guān)參數(shù)。在工作表B4：B7單元格區(qū)域輸入1到4季度的應(yīng)交貨數(shù)量。在C4：C7單元格區(qū)域存放需要計算求解的各季度生產(chǎn)數(shù)量，可填入初始值與應(yīng)交貨數(shù)量相同。在G4：G7單元格區(qū)域輸入生產(chǎn)能力限制數(shù)量。

Step2：建立計算公式。在D4單元格建立1季度生產(chǎn)費用公式：=80+98*C4-0.12*C4^2，自動填充到D5至D8單元格中。在E4單元格建立1季度存儲數(shù)量公式：=C4-B4，在E5單元格建立第2季度存儲數(shù)量公式：=E4+C5-B5，即等于1季度的存儲數(shù)量加上2季度的生產(chǎn)數(shù)量減去2季度的應(yīng)交貨數(shù)量，并填充到E6和E7中，計算3季度和4季度存儲數(shù)量。在F4單元格中輸入1季度倉儲費用公式：=15*E4，并通過鼠標(biāo)拖拽方式填充到F5至F7中。在H4單元格中建立1季度可交貨數(shù)量的公式：=C4。在H5單元格為2季度可交貨數(shù)量：=E4+C5，即1季度存儲量加2季度生產(chǎn)數(shù)量。將其公式填充到H6、H7單元格中，計算3季度和4季度可交貨數(shù)量。在B8：F8單元格中輸入計算應(yīng)交貨數(shù)量、生產(chǎn)數(shù)量、生產(chǎn)費用、存儲費用合計的公式。最后在B2單元格中輸入計算目標(biāo)函數(shù)的公式：=D8+F8，即生產(chǎn)費用和存儲費用總和。

從初始方案即按照應(yīng)交貨數(shù)量安排每個季度生產(chǎn)的計算結(jié)果可以看出，生產(chǎn)費用為25485，沒有倉儲費用，總費用為25485。雖然沒有倉儲費用，但這也不一定就是最佳方案。

若換一種方案生產(chǎn)，生產(chǎn)數(shù)量分別為：120，40，40，90，實驗得到總費用25785，其中存在倉儲費用900。顯然，此方案還不如初始方案好。

可選的方案有很多，但究竟選哪種最好，人工窮舉是不現(xiàn)實的。利用Excel提供的規(guī)劃求解工具求解，快速找到最優(yōu)解。

3.規(guī)劃模型的最優(yōu)解及結(jié)果分析

應(yīng)用規(guī)劃求解工具的操作步驟為：

Step1：單擊“工具”菜單，選擇“規(guī)劃求解”，彈出“規(guī)劃求解參數(shù)”對話框。

Step2：設(shè)置目標(biāo)函數(shù)。將鼠標(biāo)輸入焦點放在”設(shè)置目標(biāo)單元格“上，選擇B2單元格，自動的帶上絕對地址引用$B$2，并選擇“最小值”單選按鈕。

Step3：設(shè)置決策變量。指定“可變單元格”為決策變量所在的單元格區(qū)域：$C$4：$C$7。最多選定200個單元格。

Step4：設(shè)置約束條件。單擊“約束”框中的“添加”按鈕，彈出“添加約束”對話框。按照分析完的數(shù)學(xué)模型依次添入的約束條件有：$H$4>=$B$4；$H$5>=$B$5；$H$6>=$B$6；$H$=$B$7；$C$4<=$G$4；$C$5<=$G$5；$C$6<=$G$6；$C$7<=$G$7。Excel中允許最多添加100個約束條件。

Step5：求解并保存結(jié)果。單擊“求解”按鈕，會彈出“規(guī)劃求解結(jié)果”對話框?？梢员４嬉?guī)劃求解結(jié)果及生成運算結(jié)果報告、敏感性報告和極限值報告。本例得到的結(jié)果如下表1所示。

第1季度到第4季度生產(chǎn)數(shù)量分別是140、0、60和90，目標(biāo)函數(shù)值即最低總費用為24945，這便是最優(yōu)解。

最后通過生成的運算結(jié)果報告、敏感性報告和極限值報告可以進一步了解規(guī)劃求解的一些細(xì)節(jié)。

4.結(jié)束語

Excel的“規(guī)劃求解”工具能夠幫助一般工作人員在不需要更多了解復(fù)雜數(shù)學(xué)求解過程的情況下，進行線性、非線性等規(guī)劃問題求解，而且也可以不必掌握更專業(yè)、更復(fù)雜的求解軟件，只需要在Excel的數(shù)據(jù)表中設(shè)置好目標(biāo)函數(shù)、決策變量和約束條件等內(nèi)容即可?？梢娭灰_地建立數(shù)學(xué)模型，熟練運用Excel的工具，就能快速得到結(jié)果。

參考文獻(xiàn)：

[1]顧運筠.Excel規(guī)劃求解的兩類應(yīng)用[J].計算機應(yīng)用與軟件，2005，22（1）：137-139.

[2]劉連香，陳鑫源.線性規(guī)劃與Excel求解[J].時代報告（下半月），2012（2）：20.

[3]張瑩.EXCEL在線性規(guī)劃中的應(yīng)用[J].科技信息，2009（33）：64.

[4]鄭小玲等.Excel在信息管理中的應(yīng)用[M].北京：人民郵電出版社，2004.