實用標準文案實一用Excel求解線性劃型線性規(guī)劃問題用手工求解工作量大沒有較高的數(shù)學基礎很難理解其計算過程和方法，但是借助E“規(guī)劃求”工具，就能輕而易舉地求得結果Excel最多可解200個變量600個約束條件的問題下我們以一實例介紹利Excel規(guī)劃求解工具怎樣快速解決具體的經(jīng)濟決策問題。一

實驗目1、掌握如何建立線性規(guī)劃模型2、掌握用xcel求解線性規(guī)劃模的方法。3、掌握如何借助于Excel對線性劃模型進行靈敏度分析，以判斷各種可能的變化對最優(yōu)方案產(chǎn)生的影響。4、讀懂Excel求解線性規(guī)劃問題出的運算結果報告和敏感性報告。二、

實驗內(nèi)、[工]規(guī)求]令規(guī)劃求解加載宏是xcel的一個選安裝模塊，在安Excel，只有在選擇“完/定制安裝”時才可選擇裝入這個模。在安裝完成進Excel還要用[工具][加載宏]命令選中“規(guī)劃求解”，以后在[工具]菜單下就增加了一[規(guī)劃求命令。使用規(guī)劃求解命令的一般步驟：第步在選取工具[規(guī)劃求解]命后，彈出1示“規(guī)劃求解參數(shù)”對話框，其中各選項說明如表1。選名設置目標單元格等于可變單元格約束添加更改刪除精彩文檔

圖“劃解數(shù)對框表“劃解數(shù)對框項說選取計算問題的目標函數(shù)，并含計算公式的單元格按問題目標進行選擇。如利潤問，選取“最大值”決策變量所在各單元格、不含公，可以有多個區(qū)域或單元格增加修改刪除各個約束等式不等式一個一個地與切換填入或修改選擇后彈出圖所示對話框選擇后彈出圖所示對話框刪除所選定的約束條件

選項

實用標準文案決定采用線性模型還是非線性模求解約束條件中的單元格引用位置，從鍵盤直接錄入，也可用鼠標拖放選取。圖2圖3第步完成圖所示的一切填入項目，單擊“選項”按鈕，在彈出的“規(guī)劃求解選項”對話框中若是線性模型則選“采用線性規(guī)模”選項按鈕，再單擊“確定”按鈕回到圖1。圖第步在圖中單“求解按鈕經(jīng)計算完成后彈“規(guī)劃求解結果對話圖圖精彩文檔

實用標準文案第步在圖中單擊“確定”按鈕，只將優(yōu)化計算結果顯示在表格設置中的可變單元格（決策變量）和目標單元格目標函數(shù)）內(nèi)。在圖“報告框中有個選項個選項對應著一個報告報告以單一工作表記載，它們不僅能給出優(yōu)化結果，甚至給出更重要信息，例如影子價格等。、產(chǎn)品產(chǎn)種構化題數(shù)模示例一家制藥廠生產(chǎn)兩種產(chǎn)品品Ⅰ和藥品Ⅱ個產(chǎn)品要用到一種相同的原料A，并要經(jīng)過一道相同的工序，在機B上裝因為這兩種產(chǎn)品可以使用同樣的機器，所以它們可以輪換使用設備，從而使生產(chǎn)設施得到較充分的利用。表2藥品和藥品的售價、可變本和貢獻藥品ⅠⅡ

銷售價（元）350450

可變成本（元）300350

對利潤的貢獻50100表3兩種藥品在機器上加工兩產(chǎn)品的時間以及原材料A和B

限制藥品ⅠⅡ資源限制

原料A（千克）21400

機器B（小時）11300

原料C（千克）01250問該制藥廠應該如何安排生產(chǎn)計才能使企業(yè)的利潤最大。我們知道，如果分別設藥品Ⅰ和品Ⅱ的生產(chǎn)數(shù)量為x1和x2，那么該問題的線性規(guī)劃模型如下：maxZ50xx原料Ax300(機器)st原料C)0(品I的最低產(chǎn)量)0(品II的最低產(chǎn)量)表設與式明根據(jù)本問題的規(guī)模和條件，擬設如中A︰E所示形式：⑴區(qū)域3︰6和3︰E5為原始數(shù)區(qū)，輸入如1所示的原始數(shù)據(jù)。表精彩文檔

實用標準文案⑵在單元格B8內(nèi)輸入數(shù)學模型中標函數(shù)的計算公式，并求最大值。⑶單元格7︰7分別作為藥品Ⅰ藥品Ⅱ的產(chǎn)即決策變x1x2即可變單元格。其初始值設為，求解過程中計機會自動輸入各組試驗值。⑷區(qū)域3︰5內(nèi)的各單元格依次輸入三個約束條件對應式的左側部分。操步第步選擇工具[規(guī)劃求命令彈出圖所示對話框根據(jù)本問題的性質(zhì)“設置目標單元格”文本框內(nèi)填入$B$8,在“等于”選項后選取“最大值”，在“可變單元格”文本框內(nèi)填入$B$7︰C$7。第步單擊“添加”按鈕，彈出圖所示對話框。該步驟的任務是要把前面數(shù)學模型中的全部約束條件一個一個地填1所示“約束列表框內(nèi)圖所示就是填入三個資源約束條件的情形：在左邊“單元引用位置”文本框內(nèi)填︰（可直接錄入、或用鼠標拖入）、單擊中間向下小箭并選取符號”、在右方“約束值”文本框內(nèi)錄$E$3︰E$5，也可以錄入數(shù)字400，300250最后單擊“確定”按鈕或回車鍵，回到圖1。這樣就完成了約束條件$D$3︰$D$5<=$E$3$E$5錄入。第步重復第二步，錄入$B$7︰C$7>=0，即兩決策變量的值必須大于0，最后如圖所示。第步在圖中單擊“選”按鈕，彈出對框。因本例題屬于線性規(guī)劃問題，選取“采用線性模型”按鈕，再單“確定”按鈕，回到1。第步在圖中選取“求解按鈕或擊回車鍵Excel入規(guī)劃求解運行過程，屏幕左下角狀態(tài)條上逐次顯示運行過程告一旦計算結束彈圖的對話框在圖5內(nèi)可以有四種選擇：⑴若單擊“確定”按鈕或擊回車，則顯示如2結果。可變單元格$B$7︰C$7內(nèi)顯示最優(yōu)生產(chǎn)計劃即藥品Ⅰ生產(chǎn)5件和藥品Ⅱ生100可得最大利潤7500元單元格$D$3︰D$5分別給出了各種資源用量，只有原有50千克的剩余。表⑵若選擇“運算結果報告”顯示“運算結果報<n><n>表示求解本問題中已經(jīng)連續(xù)第幾次選擇該選項我們這里給出的“運算結果報告表即在同一文件內(nèi)首次選擇該選項表6比較面地報告了優(yōu)化結果信息包目標單元格的位置名稱、初值和終值，可變單元格的位置名稱、初值和終值，約束單元格位置、名稱、單元格內(nèi)計算結果、單元格相應約束式、運結果達到的狀態(tài)（型數(shù)值0表示到達限制值、否則未到達限制值⑶若選擇“敏感性報告”Excel示“敏感性報<n>7敏感性報告表的限制式中“陰影價格濟學中影子價格ShadowPrice）是一個有特別意義的經(jīng)濟指標。精彩文檔

實用標準文案表Excel9.0運算果告工表[習題.xls]Sheet2報的立2006-8-2419:22:29目標單元格(最大值)單格

名

初

終$B$8

目標函數(shù)

0

27500可變單元格單格

名

初

終$B$7$C$7

決策變量產(chǎn)品1決策變量產(chǎn)品2

00

50250約束單格

名

單格

公

狀

型值$D$3$D$4$D$5

原料A機器B原料C

350$D$3<=$E$3未到限制值300$D$4<=$E$4到達限制值250$D$5<=$E$5到達限制值

5000$B$7$C$7

決策變量產(chǎn)品1決策變量產(chǎn)品2

50$B$7>=0250$C$7>=0

未到限制值未到限制值

50250影子價格的經(jīng)濟學意義是在優(yōu)利用下的緊缺資源增加1個單位為企業(yè)創(chuàng)造的利潤。用影子價格與各緊缺資源的市場格相比較為企業(yè)是否購買緊缺資源擴大生產(chǎn)提供決策依據(jù)。有剩余的資源影子價格0表Excel9.0敏感報工表習題.xls]Sheet2報的立2006-8-2511:01:22可變單元格單格

名

終值

遞成

目式系

允的增

允的減$B$7$C$7

決策變量產(chǎn)品1決策變量產(chǎn)品2

50250

00

50100

501E+30

5050約束單格

名

終值

陰價

約限值

允的增

允的減$D$3$D$4$D$5

原料A機器B原料C

350300250

05050

400300250

1E+302550

505050精彩文檔

實用標準文案⑷若選擇“極限值報告”Excel顯“極限值報<n>表8除了給出最優(yōu)決策對應最優(yōu)目標值信息外，還顯示決策變量的上、下限值及其對應目標式結果。表Excel9.0極限報工表[習題.xls]Sheet2報的立2006-8-2511:02:45目式單格

名

值$B$8

最大利潤

27500單格

變名

值

下極

目式結

上極

目式結$B$7

決策變量產(chǎn)品1

50

0

25000

50

27500$C$7

決策變量產(chǎn)品225002500249.999999927499.99999、讀Excel求線規(guī)問輸?shù)乃愎麍蠛透袌罄肊xcel求解線性規(guī)劃問題系將提供三個計算結果報告，即運算結果報告、敏感性報告極限值報告這三個報告的前兩個報告非常重要下我們將結合教材第二章線性規(guī)劃的對偶理論與靈敏度分析的容講述如何Excel求解線性規(guī)劃問題輸出的運算結果報告和敏感性報告。⑴懂算果告運算結果報告比較容易看懂，可單元$B$7和$C$7分別示兩個決策變量，即藥品Ⅰ和藥品Ⅱ的產(chǎn)量，在計算時，于我們最初賦予

x01

，所以單元格$B$7和C$7的初值為0，求得最優(yōu)解后$的值分別為50和250，即

x25012

，表示使目標函數(shù)值最大的計劃是生50個位的藥品Ⅰ和250個單位的藥品Ⅱ。目標單元格$B$8表示目標函數(shù)

zx100x1

2

，由于我們最初賦予

x1

，所以目標函數(shù)的初值為0求得最優(yōu)解后，標函數(shù)的值為7500，即果生產(chǎn)50個單位的藥品Ⅰ和250個單位的藥品Ⅱ能使企業(yè)利達到最大值27500元。在單元格我們別輸入了三個約束條件的左邊項

1

2

1

2和x，后在使工][劃解時，在規(guī)求參]口，我們輸入了2D$3E$3,$D$4D$5E$5

,而E$3=400、$E$4=300、$E$5=250,從完成了約束條件的輸入求得最優(yōu)

x25012

后,將

x入約束方程得：2精彩文檔

$3501

實用標準文案達到限制值數(shù)值為50D300$$41

，達到限制值，型數(shù)值為0；$DxE2

，達到限制值，型數(shù)值為0。⑵懂感報可變單元格B$7$C$7分別表示兩個決策變量

x和x

在求得最優(yōu)解后

x50,x2

即敏感性報告中所顯示的B$7$C$7終值分別為50和250x在目標函數(shù)中的系數(shù)為50，允許的增量為50允許的減量為50，意思是當

x

在目標函數(shù)中的系數(shù)在50的基礎上再增加50或減50，該線性規(guī)劃問題的最優(yōu)解不變，即在其他條件不變的情況下當

0，x50,x2502

仍然是線性規(guī)劃問題的最優(yōu)解。理，x在目標函數(shù)中的系數(shù)為100，允許的增量為1E+30允許的減量為50意思是當

x

在目標函數(shù)中的系數(shù)在100的基上再增加1E+30減少50該線性規(guī)劃問題的最優(yōu)解不變，即在其他條件不變的情況下50

時，

x50,x2

仍然是線性規(guī)劃問題的最優(yōu)解。當求得最優(yōu)解后，單元$D$3$D$4$D$5的值分別為350，300和250，即50個單位的藥品Ⅰ和250個單位的品Ⅱ耗了原料A350克機器B300小時原料C250千克。由于企業(yè)擁有的原料A、器B，原料數(shù)量分別是400千克300小時和250千克，所以生產(chǎn)50個單位的藥品和250單位的藥品Ⅱ消耗掉了企業(yè)的擁有的全部機器B設備時數(shù)和原料C，但原料A還50千克剩余。三種資源的陰影價格分別為0、50，即該線性規(guī)劃問題的對偶問題的最優(yōu)解為y50,23

意思是保持其他生產(chǎn)條件不變每增加1個單的原料A目標函數(shù)增加的數(shù)量為0每增加1個小時的設備B使標函數(shù)增加的數(shù)量為每增加1個單位的原料使目標函數(shù)增加的數(shù)為50。約束限制值分別為400、和250即企業(yè)擁有的原料A機器B，原料C的數(shù)量分別是：400千克300小時和250千。原料A允許的增量為1E+30，允許的減量為50，意思是原料在現(xiàn)有數(shù)量的基礎上再加1E+30或少50原料的影子價格不變，即在其他條件不變的情況下，當

b時y

。同理可得：當

250時y50

；當

時，

y

。外練習1、

利用Excel試算教材后面的習題然后與手工計算的結果進行對比。2、練習利用線性規(guī)劃模型制訂體計劃。例：紅西紅柿公司是一家園藝工具生產(chǎn)商，公司主要是將購進的原材料制造多用途的園藝工具生產(chǎn)線需要的工具和場地是有限定的紅西紅柿公司的生產(chǎn)能力要由勞動力人數(shù)決定公司的產(chǎn)品需求季節(jié)性很強需求最旺的時間在春季該公司決利用總體計劃來克服需求季節(jié)性變動的障礙時實現(xiàn)利潤最大化。公司的選擇是，在淡建立庫存，當旺季到來時增加工人，簽訂轉(zhuǎn)包合同，交積壓訂單登記入冊，以后再將產(chǎn)品達顧客。為了知道如何利用總體計劃中的這些選擇西紅柿”供應鏈的副總裁從預測個月的需求著手工作，如9所示。該公司以0美元的單價銷售其工。公司月的庫存1000工具，在月初有80名精彩文檔

實用標準文案員工。每個月每條生產(chǎn)線有20個作日，每個工人每小時收4美元。每個工人每天工作小時，其余時間休息。如前所述生產(chǎn)線的生產(chǎn)能力主要取決于工人工作總時數(shù)。所以，機器的生產(chǎn)能力并不限制生產(chǎn)線的產(chǎn)能力勞動法規(guī)定人每月加班不能超過10小時。各種成本如表10所示。目前，紅西紅柿公司在轉(zhuǎn)包合同庫存缺貨或積壓上沒有什么限制。所有庫存缺貨被積累起來由下一個月生產(chǎn)出來產(chǎn)品來滿足庫成本在當月庫存結清時才計入供應鏈管理者的目標是，制定出一個最理的總體計劃，它將使庫存量6月底至少為00個單位，即月底沒有庫存缺貨