運(yùn)用Excel 等軟件求解線性規(guī)劃模型的比較研究

羅云中 宗勝亮

（蘭州大學(xué)管理學(xué)院 甘肅省蘭州市 730000）

管理運(yùn)籌學(xué)是經(jīng)濟(jì)學(xué)類和管理學(xué)類專業(yè)一門十分重要的基礎(chǔ)課程，通過對現(xiàn)實(shí)狀況的觀察總結(jié)基本規(guī)律后，以數(shù)學(xué)方法進(jìn)行量化分析，通過建立數(shù)學(xué)模型和求解獲得最優(yōu)的解決方法，輔助決策者進(jìn)行科學(xué)決策，在經(jīng)濟(jì)管理類專業(yè)的人才培養(yǎng)中占據(jù)十分重要的地位。線性規(guī)劃是管理運(yùn)籌學(xué)最重要的領(lǐng)域之一，它主要研究線性約束條件下線性目標(biāo)函數(shù)的最大值和最小值問題。隨著計(jì)算機(jī)技術(shù)的快速發(fā)展和應(yīng)用，線性規(guī)劃有了很多的求解模塊和工具，使用這些軟件能快速對線性規(guī)劃問題進(jìn)行求解。目前，求解線性規(guī)劃問題的大型軟件主要有Matlab、Mathematica 等；還有LINGO 和LINDO 等專業(yè)軟件，能處理大型多變量的矩陣運(yùn)算；EXCEL 也有模塊（插件）來處理小型的線性規(guī)劃問題；Python 作為最流行的開源腳本語言，也有scipy、pulp 和pyomo 等多個(gè)庫可以用來求解線性規(guī)劃問題。

1 線性規(guī)劃模型及軟件求解

本文以如下線性規(guī)劃模型為例，該模型具有10 個(gè)決策變量8 個(gè)約束條件，分別運(yùn)用Excel、Lingo 和Python 軟件這三個(gè)工具進(jìn)行求解。

1.1 Excel求解線性規(guī)劃模型

1.1.1 Excel 規(guī)劃求解介紹

Excel 規(guī)劃求解模塊是一款隨Office 軟件一同發(fā)行的規(guī)劃求解模塊（Solver），該模塊免費(fèi)使用不需要單獨(dú)激活，內(nèi)置有單純型法、對偶單純型法等算法，可以用來求解線性規(guī)劃、整數(shù)規(guī)劃和非線性規(guī)劃問題。

1.1.2 加載規(guī)劃求解模塊

Excel 中封裝的“規(guī)劃求解”模塊（插件）是可選安裝，一般不會默認(rèn)安裝，在安裝完office 之后，需要通過Excel選項(xiàng)--加載項(xiàng)的方式來安裝規(guī)劃求解模塊，成功安裝完畢后在數(shù)據(jù)菜單會出現(xiàn)規(guī)劃求解插件，之后啟動(dòng)office 會自動(dòng)加載規(guī)劃求解模塊。下面以Excel2016 版本為例，簡單演示如何加載規(guī)劃求解模塊，其他版本office 加載方式類似。

第一步，打開文件菜單，點(diǎn)擊左下角的選項(xiàng)按鈕；

第二步，在Excel 選項(xiàng)對話框左側(cè)選擇加載項(xiàng)，右下管理選擇Excel 加載項(xiàng)，點(diǎn)擊右側(cè)轉(zhuǎn)到按鈕；

第三步，在加載宏對話框勾選規(guī)劃求解加載項(xiàng)，點(diǎn)擊確定按鈕；

第四步，安裝成功后在數(shù)據(jù)菜單會新增分析選項(xiàng)卡，分析選項(xiàng)卡新增規(guī)劃求解插件。

1.1.3 Excel 線性規(guī)劃求解實(shí)現(xiàn)

（1）創(chuàng)建求解模板。

針對以上的線性規(guī)劃模型，需要在EXCEL 中制作10 個(gè)決策變量和10 個(gè)約束條件的線性規(guī)劃求解模板來求解這個(gè)模型。下面是模板制作步驟：

第1 步，在Excel 編輯區(qū)劃分目標(biāo)函數(shù)區(qū)，約束條件區(qū)和最優(yōu)解區(qū)和最優(yōu)值區(qū)四個(gè)功能區(qū)。

第2 步，在約束條件實(shí)際值M7：M16 中輸入公式。M7單元格輸入公式M7=SUMPRODUCT(C7:L7,$C$20:$L$20)，通過填充柄將公式填充到M16。

第3 步在最優(yōu)值單元格N20 單元格錄入計(jì)算公式

N20=SUMPRODUCT(C5:L5,C20:L20)。

第4 步設(shè)置規(guī)劃求解參數(shù)對話框，完成模板制作。具體過程如圖1 所示。

圖1：線性規(guī)劃求解模板

（2）輸入數(shù)據(jù)模板求解。

第1 步，在制作好的線性規(guī)劃模板中錄入數(shù)學(xué)模型目標(biāo)函數(shù)系數(shù)、約束條件系數(shù)和常數(shù)項(xiàng)數(shù)據(jù)。第2 步，通過規(guī)劃求解插件設(shè)定規(guī)劃求解參數(shù)。在規(guī)劃求解參數(shù)對話框中，設(shè)置目標(biāo)中輸入$N$20，選擇到最大值?？勺儐卧褫斎牖蛘哌x擇$C$20:$L$20。遵守約束按添加按鈕，在添加約束對話框單元格引用中把$M$7：$M$11 單元格選定，約束條件選擇小于等于，約束把$O$7：$O$11 單元格選定。勾選“使無約束變量為負(fù)數(shù)”單選框。求解方法選擇“單純線性規(guī)劃”，選項(xiàng)保持默認(rèn)。具體過程如圖2 所示。

圖2：規(guī)劃求解參數(shù)

（3）求解結(jié)果。

點(diǎn)擊求解按鈕，后會彈出規(guī)劃求解結(jié)果對話框提示找到一解可滿足所有的約束及最優(yōu)狀況，報(bào)告中可以選擇運(yùn)算報(bào)告、敏感性報(bào)告和極限值報(bào)告單獨(dú)輸出或者一起輸出，點(diǎn)確定按鈕和選擇運(yùn)算結(jié)果報(bào)告后在求解模板中C20：L20 單元格會顯示最優(yōu)解，N20 單元格會顯示最優(yōu)值的求解結(jié)果。輸出運(yùn)算報(bào)告如圖3 所示，由此可知，最優(yōu)解（x1=1200,x2=0,x3=230.0493,x4=500,x5=324.1379,x6=0,x7=500,x8=858.6207,x9=324.1379,x10=5714.4286），最優(yōu)值為1200.567。

圖3：Excel 規(guī)劃求解結(jié)果

1.2 Lingo求解線性規(guī)劃模型

1.2.1 Lingo 軟件簡介

Lingo 是Linear Interactive and General Optimizer 的縮寫。由美國Lindo 公司推出的一款專門用于求解運(yùn)籌學(xué)優(yōu)化問題的軟件包。Lingo 軟件除了能求解線性規(guī)劃問題外，還能求解非線性規(guī)劃問題、線性和非線性混合問題，功能十分強(qiáng)大。Lingo 軟件內(nèi)置了十幾個(gè)內(nèi)部函數(shù)，還能與Excel 和數(shù)據(jù)庫等軟件交換數(shù)據(jù)，運(yùn)算執(zhí)行速度快，操作方便簡單。模型輸入簡練，約束條件右面可以包含變量，能最大程度滿足數(shù)學(xué)模型的輸入，通過引入集的方式，給決策變量下標(biāo)形式更簡單直觀的方式表達(dá)模型，更容易理解和維護(hù)。整體上代碼量較少，對編程能力要求不高，易于掌握。

1.2.2 Lingo 軟件求解實(shí)現(xiàn)

（1） Lingo 程序基本語法規(guī)則。

Lingo 程序的使用主要規(guī)則如下：

以“MAX=”或者“MIN=”開始來定義目標(biāo)函數(shù)是最大值或者最小值問題。

決策變量和系數(shù)之間用“*”代表“×”連接，為了美觀之間使用空格。

變量名不區(qū)分大小寫，但必須以字母開始，變量名使用有意義單詞或詞語，不超過32 個(gè)字符。

表達(dá)式用“;”表示結(jié)束。表達(dá)式過長時(shí)可以回車多行顯示。

使用“<”來表示“小于等于”， 用“>”表示“大于等于”。

數(shù)據(jù)段以DATA:開始，以ENDDATA 表示數(shù)據(jù)段結(jié)束。

（2） 求解具體實(shí)現(xiàn)過程。

對以上線性規(guī)劃模型的Lingo 軟件求解具體實(shí)現(xiàn)過程，在Lingo Model 界面中輸入以下內(nèi)容：

點(diǎn)擊slove 按鈕求解后，得到Solver Status 和Solution Report 兩個(gè)彈出框，目標(biāo)值和迭代次數(shù)等信息。如圖4 所示的結(jié)果報(bào)告給出的信息比較詳細(xì)，提示找到了全局最優(yōu)解、目標(biāo)值、變量、變量值以及靈敏度分析的Reduced Cost（表示當(dāng)變量有微小變動(dòng)時(shí), 目標(biāo)函數(shù)的變化率）、Slack or Surplus（松弛量或剩余量，常數(shù)項(xiàng)有剩余的資源）、Dual Price（對偶價(jià)格，維持最優(yōu)解不變的情況下，常數(shù)項(xiàng)每增加一個(gè)單位而使最優(yōu)目標(biāo)函數(shù)得到改進(jìn)的數(shù)量）。

圖4：LINGO 求解結(jié)果

從Lingo 對此線性規(guī)劃模型求解報(bào)告得到結(jié)果最優(yōu)值為1200.567，最優(yōu)解為（X1=1200，X2=0，X3=230.0493，X4=500，X5=324.1379，X6=0，X7=500，X8=858.6207，X9=324.1379，X10=571.4286）。

1.3 Python求解線性規(guī)劃模型

1.3.1 Python 簡介

Python 是由荷蘭人Guido van Rossum 于1989 年圣誕節(jié)開發(fā)的一門解釋性腳本語言。它繼承了傳統(tǒng)編譯語言的強(qiáng)大性和通用性，同時(shí)有借鑒了腳本語言和解釋語言的易用性。它的特點(diǎn)是只有較少的關(guān)鍵字，結(jié)構(gòu)簡單；代碼定義清晰；源代碼容易維護(hù)；擁有豐富的標(biāo)準(zhǔn)庫和第三方模塊；跨平臺兼容性好；能在互動(dòng)模式下支持代碼的輸入和調(diào)試；可擴(kuò)展性強(qiáng)，可以使用C 或C++完成部分程序，在Python 程序中調(diào)用；支持GUI 編程；可以將Python 嵌入到C/C++程序等。Python 廣泛應(yīng)用于工業(yè)界和學(xué)術(shù)界，在web 網(wǎng)絡(luò)開發(fā)、自動(dòng)化運(yùn)維、網(wǎng)絡(luò)爬蟲和圖像圖形處理得到很多運(yùn)用，得益于大數(shù)據(jù)分析、機(jī)器學(xué)習(xí)和人工智能應(yīng)用的迅速發(fā)展和全民大數(shù)據(jù)理念接受，Python 語言得到飛速的發(fā)展，在TIOBE 的語言排行榜上已經(jīng)是第一名，超過JAVA、C 和C++語言。

1.3.2 Python 求解線性規(guī)劃問題的實(shí)現(xiàn)

Python 中能求解線性規(guī)劃的庫比較多，有Scipy 庫、Pulp 庫和Pyomo 庫等，本文介紹Scipy 庫來求解線性規(guī)劃模型。要運(yùn)行python 程序首先需要安裝python 解釋器，可以安裝python 官方的IDLE，也可以安裝Anaconda 或者Pycharm 解釋器，本文作者推薦使用Anaconda 解釋器，該解釋器內(nèi)置了Python 基本庫和依賴包，初學(xué)者不用學(xué)習(xí)安裝各種依賴包。

在Anaconda 中求解線性規(guī)劃問題，第1 步，需要引入Scipy 庫的和numpy 庫。第2 步，因?yàn)樵搸熘荒芮蠼庾钚』瘑栴}和約束關(guān)系小于等于的約束條件，需要將目標(biāo)函數(shù)乘以-1 轉(zhuǎn)化為求最小化問題，將大于等于約束條件兩邊乘以-1轉(zhuǎn)化為小于等于。第3 步，把目標(biāo)函數(shù)系數(shù)組合成一維數(shù)組C，每個(gè)決策變量系數(shù)用“,”分開；將每個(gè)約束條件組成的一維數(shù)組組合成二維數(shù)組A，約束條件也用“,”分開；每個(gè)約束條件常數(shù)項(xiàng)組成一維數(shù)組b；約定決策變量的取值范圍。第4 步，調(diào)用optimize 模塊的linprog 方法并求解。第5步輸出模型結(jié)果。

Scipy 求解線性規(guī)劃的具體代碼實(shí)現(xiàn)如下：

即最優(yōu)值為1200.56，最優(yōu)解為（X1=1200，X2=0，X3=230.0493，X4=500，X5=324.1379，X6=0，X7=500，X8=858.6207，X9=324.1379，X10=571.4286）

2 結(jié)果分析

通過運(yùn)用Excel 軟件的運(yùn)籌規(guī)劃模塊、Lingo 軟件和Python 語言的Scipy 模塊這三種不同方法對線性規(guī)劃模型進(jìn)行求解，計(jì)算的最優(yōu)值和最優(yōu)解都是一樣，但求解過程和操作還存在一定的差別，現(xiàn)對這三種方法處理線性規(guī)劃模型進(jìn)行對比分析。

2.1 Excel規(guī)劃求解模塊優(yōu)點(diǎn)和不足

Excel 規(guī)劃求解模塊，操作通過菜單操作，可視化程度較高，步驟簡單，使用難度最低。Office 平臺廣泛，幾乎所有臺式電腦都安裝有Office，只需要通過加載的方式就能運(yùn)行規(guī)劃求解程序，不用專門下載軟件，可以求解線性規(guī)劃問題、整數(shù)規(guī)劃（包括混合整數(shù)規(guī)劃）、0-1 整數(shù)規(guī)劃問題和非線性規(guī)劃問題，使用方式能最便捷。但Excel 規(guī)劃求解以行或者列代表決策變量，當(dāng)線性規(guī)劃模型中有較多變量時(shí)操作不方便，在輸入約束條件和目標(biāo)函數(shù)時(shí)容易出現(xiàn)錯(cuò)誤。結(jié)果輸出時(shí)中文翻譯也不太貼切，初學(xué)者或者偶爾使用時(shí)不容易明白結(jié)果真正意義，造成誤解。Excel 規(guī)劃求解模塊在決策變量數(shù)量、約束條件數(shù)量和求解時(shí)間上有限制，最多200個(gè)決策變量和包括變量上下界約束在內(nèi)最多100 個(gè)約束條件，求解時(shí)間也不能超過30 秒，這些因素了Excel 規(guī)劃求解模塊不能處理大型的實(shí)際問題。

2.2 Lingo軟件優(yōu)點(diǎn)和不足

Lingo 軟件結(jié)構(gòu)形式簡單，符合線性規(guī)劃模型的書寫習(xí)慣，容易閱讀、了解和修改?？梢詮腅xcel 或數(shù)據(jù)庫讀取數(shù)據(jù)計(jì)算，結(jié)果也可以輸出到Excel 或者數(shù)據(jù)庫。Lingo 求解器十分強(qiáng)大，會根據(jù)模型自動(dòng)選擇線性、非線性或整數(shù)優(yōu)化算法。但Lingo 軟件需要單獨(dú)安裝，試用版本決策變量只有30 個(gè)，軟件購買費(fèi)用較高，對于高校教學(xué)來說是一筆巨大的投入。使用過程還是需要一定的代碼編寫，對編程零基礎(chǔ)的學(xué)生來說有存在一定的挑戰(zhàn)。

2.3 Python的優(yōu)點(diǎn)和不足

Python 語言是免費(fèi)開源的程序，變量數(shù)量和約束條件數(shù)量均無限制，可以處理特別巨大的線性規(guī)劃模型，并且自帶的Python 編程語言能靈活處理復(fù)雜的數(shù)據(jù)輸入等功能，但scipy 的linprog 模塊只能處理最小化問題，輸出結(jié)果可讀性也較差。

總體來說，這三個(gè)工具都能很好地求解線性規(guī)劃模型，但相比而言，Lingo 軟件在處理線性規(guī)劃模型時(shí)更加專業(yè)，輸出結(jié)果也更加合理，可讀性更強(qiáng)；Python 更適合于大型或者巨型線性規(guī)劃模型；EXCEL 插件適用于較小的線性規(guī)劃模型使用，但優(yōu)點(diǎn)是不需要特別安裝，也不需要有編程基礎(chǔ)都能順利將線性規(guī)劃模型求解出來。

3 結(jié)語

因此，建議在日常的課堂教學(xué)過程中還是可以使用Excel 來教學(xué)，對學(xué)生們學(xué)習(xí)要求難度比較低，容易上手，但在處理實(shí)踐生活中或者商業(yè)化項(xiàng)目時(shí)，決策變量和約束條件比較多的時(shí)候，建議使用Python 或者Lingo 來處理大型線性規(guī)劃模型會更加合適。