某實(shí)驗(yàn)1用Excel求解線性規(guī)劃模型

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

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

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

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

選項(xiàng)

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

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

銷售價(jià)（元）350450

可變成本（元）300350

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

限制藥品ⅠⅡ資源限制

原料A（千克）21400

機(jī)器B（小時(shí)）11300

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

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

實(shí)用標(biāo)準(zhǔn)文案表Excel9.0運(yùn)算果告工表[習(xí)題.xls]Sheet2報(bào)的立2006-8-2419:22:29目標(biāo)單元格(最大值)單格

名

初

終$B$8

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

0

27500可變單元格單格

名

初

終$B$7$C$7

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

00

50250約束單格

名

單格

公

狀

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

原料A機(jī)器B原料C

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

5000$B$7$C$7

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

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

未到限制值未到限制值

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

名

終值

遞成

目式系

允的增

允的減$B$7$C$7

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

50250

00

50100

501E+30

5050約束單格

名

終值

陰價(jià)

約限值

允的增

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

原料A機(jī)器B原料C

350300250

05050

400300250

1E+302550

505050精彩文檔

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

名

值$B$8

最大利潤

27500單格

變名

值

下極

目式結(jié)

上極

目式結(jié)$B$7

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

50

0

25000

50

27500$C$7

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

x01

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

x25012

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

zx100x1

2

，由于我們最初賦予

x1

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

1

2

1

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

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

x25012

后,將

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

$3501

實(shí)用標(biāo)準(zhǔn)文案達(dá)到限制值數(shù)值為50D300$$41

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

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

x和x

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

x50,x2

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

x

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

0，x50,x2502

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

x

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

時(shí)，

x50,x2

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

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

b時(shí)y

。同理可得：當(dāng)

250時(shí)y50

；當(dāng)

時(shí)，

y

。外練習(xí)1、

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

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