運(yùn)籌學(xué)實(shí)驗(yàn)3用Excel求解線性規(guī)劃模型

1、實(shí)驗(yàn)三、用excel求解線性規(guī)劃模型線性規(guī)劃問(wèn)題用手工求解工作量很大，而且沒(méi)有較高的數(shù)學(xué)基礎(chǔ)很難理解其計(jì)算過(guò)程和方法，但是借助excel“規(guī)劃求解”工具，就能輕而易舉地求得結(jié)果。excel最多可解200個(gè)變量、600個(gè)約束條件的問(wèn)題。下面我們以一實(shí)例介紹利用excel規(guī)劃求解工具怎樣快速解決具體的經(jīng)濟(jì)決策問(wèn)題。一、實(shí)驗(yàn)?zāi)康?、 掌握如何建立線性規(guī)劃模型。2、 掌握用excel求解線性規(guī)劃模型的方法。3、 掌握如何借助于excel對(duì)線性規(guī)劃模型進(jìn)行靈敏度分析，以判斷各種可能的變化對(duì)最優(yōu)方案產(chǎn)生的影響。4、 讀懂excel求解線性規(guī)劃問(wèn)題輸出的運(yùn)算結(jié)果報(bào)告和敏感性報(bào)告。二、 實(shí)驗(yàn)內(nèi)容1、 工具規(guī)劃

2、求解命令規(guī)劃求解加載宏是excel的一個(gè)可選安裝模塊，在安裝excel時(shí)，只有在選擇“完全/定制安裝”時(shí)才可選擇裝入這個(gè)模塊。在安裝完成進(jìn)入excel后還要用工具加載宏命令選中“規(guī)劃求解”，以后在工具菜單下就增加了一條規(guī)劃求解命令。使用規(guī)劃求解命令的一般步驟為：第一步：在選取工具規(guī)劃求解命令后，彈出圖1所示“規(guī)劃求解參數(shù)”對(duì)話框，其中各選項(xiàng)說(shuō)明如表1。圖1“規(guī)劃求解參數(shù)”對(duì)話框表1“規(guī)劃求解參數(shù)”對(duì)話框選項(xiàng)選項(xiàng)名說(shuō)明設(shè)置目標(biāo)單元格選取計(jì)算問(wèn)題的目標(biāo)函數(shù)，并含有計(jì)算公式的單元格等于按問(wèn)題目標(biāo)進(jìn)行選擇。如利潤(rùn)問(wèn)題，選取“最大值”可變單元格決策變量所在各單元格、不含公式，可以有多個(gè)區(qū)域或單元格約束增

3、加、修改、刪除各個(gè)約束等式或不等式，一個(gè)一個(gè)地與圖2切換填入或修改添加選擇后彈出圖2所示對(duì)話框更改選擇后彈出圖3所示對(duì)話框刪除刪除所選定的約束條件選項(xiàng)決定采用線性模型還是非線性模型求解約束條件中的單元格引用位置，可從鍵盤直接錄入，也可用鼠標(biāo)拖放選取。圖2圖3第二步：完成圖1所示的一切填入項(xiàng)目后，單擊“選項(xiàng)”按鈕，在彈出的“規(guī)劃求解選項(xiàng)”對(duì)話框中若是線性模型則選取“采用線性規(guī)?！边x項(xiàng)按鈕，再單擊“確定”按鈕回到圖1。圖4第三步：在圖1中單擊“求解”按鈕，經(jīng)計(jì)算完成后彈出“規(guī)劃求解結(jié)果”對(duì)話框（圖5）。圖5第四步：在圖5中單擊“確定”按鈕，則只將優(yōu)化計(jì)算結(jié)果顯示在表格設(shè)置中的可變單元格（決策變量）

4、和目標(biāo)單元格（目標(biāo)函數(shù)）內(nèi)。在圖5的“報(bào)告”框中有3個(gè)選項(xiàng)，每個(gè)選項(xiàng)對(duì)應(yīng)著一個(gè)報(bào)告，各報(bào)告以單一工作表記載，它們不僅能給出優(yōu)化結(jié)果，甚至還給出更重要信息，例如影子價(jià)格等。2、 產(chǎn)品生產(chǎn)品種結(jié)構(gòu)優(yōu)化問(wèn)題數(shù)學(xué)模型示例：一家制藥廠生產(chǎn)兩種產(chǎn)品：藥品和藥品。每個(gè)產(chǎn)品要用到一種相同的原料a，并要經(jīng)過(guò)一道相同的工序，在機(jī)器b上包裝 。因?yàn)檫@兩種產(chǎn)品可以使用同樣的機(jī)器，所以它們可以輪換使用設(shè)備，從而使其生產(chǎn)設(shè)施得到較充分的利用。表2 藥品和藥品的售價(jià)、可變成本和貢獻(xiàn)藥品銷售價(jià)（元）可變成本（元）對(duì)利潤(rùn)的貢獻(xiàn)35030050450350100表3 兩種藥品在機(jī)器上加工兩種產(chǎn)品的時(shí)間以及原材料a和b限制藥品原料

5、a（千克）機(jī)器b（小時(shí)）原料c（千克）210111資源限制400300250問(wèn)該制藥廠應(yīng)該如何安排生產(chǎn)計(jì)劃才能使企業(yè)的利潤(rùn)最大。 我們知道，如果分別設(shè)藥品和藥品的生產(chǎn)數(shù)量為x1和x2，那么該問(wèn)題的線性規(guī)劃模型如下：表格設(shè)置與公式說(shuō)明根據(jù)本問(wèn)題的規(guī)模和條件，擬設(shè)置如表1中a1e8所示形式：區(qū)域b3c6和e3e5為原始數(shù)據(jù)區(qū)，輸入如表1中所示的原始數(shù)據(jù)。表4在單元格b8內(nèi)輸入數(shù)學(xué)模型中目標(biāo)函數(shù)的計(jì)算公式，并求最大值。單元格b7c7分別作為藥品和藥品的產(chǎn)量（即決策變量x1、x2），即可變單元格。其初始值設(shè)為0，求解過(guò)程中計(jì)算機(jī)會(huì)自動(dòng)輸入各組試驗(yàn)值。區(qū)域d3d5內(nèi)的各單元格依次輸入三個(gè)約束條件對(duì)應(yīng)式的

6、左側(cè)部分。操作步驟第一步：選擇工具規(guī)劃求解命令，彈出圖1所示對(duì)話框。根據(jù)本問(wèn)題的性質(zhì)，在“設(shè)置目標(biāo)單元格”文本框內(nèi)填入$b$8,在“等于”選項(xiàng)后選取“最大值”，在“可變單元格”文本框內(nèi)填入$b$7$c$7。第二步：?jiǎn)螕簟疤砑印卑粹o，彈出圖2所示對(duì)話框。該步驟的任務(wù)是要把前面數(shù)學(xué)模型中的全部約束條件一個(gè)一個(gè)地填入圖1所示的“約束”列表框內(nèi)。圖2所示就是填入三個(gè)資源約束條件的情形：在左邊“單元格引用位置”文本框內(nèi)填入$d$3$d$5（可直接錄入、或用鼠標(biāo)拖入）、單擊中間向下小箭頭并選取符號(hào)“=”、在右方“約束值”文本框內(nèi)錄入$e$3$e$5，也可以錄入數(shù)字400，300，250，最后單擊“確定”

7、按鈕或回車鍵，回到圖1。這樣就完成了約束條件$d$3$d$5=0，即兩決策變量的值必須大于0，最后如圖1所示。第四步：在圖1中單擊“選項(xiàng)”按鈕，彈出圖4對(duì)話框。因本例題屬于線性規(guī)劃問(wèn)題，選取“采用線性模型”按鈕，再單擊“確定”按鈕，回到圖1。第五步：在圖1中選取“求解”按鈕或擊回車鍵，excel進(jìn)入規(guī)劃求解運(yùn)行過(guò)程，屏幕左下角狀態(tài)條上逐次顯示運(yùn)行過(guò)程報(bào)告。一旦計(jì)算結(jié)束，彈出圖5的對(duì)話框。在圖5內(nèi)可以有四種選擇：若單擊“確定”按鈕或擊回車鍵，則顯示如表2的結(jié)果。可變單元格$b$7$c$7內(nèi)顯示最優(yōu)生產(chǎn)計(jì)劃，即藥品生產(chǎn)50件和藥品生產(chǎn)100件，可獲得最大利潤(rùn)27500元；單元格$d$3$d$5分別

8、給出了各種資源的用量，只有原料a有50千克的剩余。表5若選擇“運(yùn)算結(jié)果報(bào)告”，excel顯示“運(yùn)算結(jié)果報(bào)告”，其中表示求解本問(wèn)題中已經(jīng)連續(xù)第幾次選擇該選項(xiàng)，我們這里給出的是“運(yùn)算結(jié)果報(bào)告1”（如表6），即在同一文件內(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)告”（如表7）。敏感性報(bào)告表的限制式中“陰影價(jià)格”（經(jīng)濟(jì)學(xué)中稱影子價(jià)格、shadow p

9、rice）是一個(gè)有特別意義的經(jīng)濟(jì)指標(biāo)。表6microsoft excel 9.0 運(yùn)算結(jié)果報(bào)告工作表 習(xí)題一.xlssheet2報(bào)告的建立: 2006-8-24 19:22:29目標(biāo)單元格 (最大值)單元格名字初值終值$b$8目標(biāo)函數(shù)027500可變單元格單元格名字初值終值$b$7決策變量 產(chǎn)品1050$c$7決策變量 產(chǎn)品20250約束單元格名字單元格值公式狀態(tài)型數(shù)值$d$3原料a350$d$3=$e$3未到限制值50$d$4機(jī)器b300$d$4=$e$4到達(dá)限制值0$d$5原料c250$d$5=0未到限制值50$c$7決策變量 產(chǎn)品2250$c$7=0未到限制值250影子價(jià)格的經(jīng)濟(jì)學(xué)意義是

10、，使在最優(yōu)利用下的緊缺資源增加1個(gè)單位，將為企業(yè)創(chuàng)造的利潤(rùn)。用影子價(jià)格與各緊缺資源的市場(chǎng)價(jià)格相比較，可以為企業(yè)是否購(gòu)買緊缺資源擴(kuò)大生產(chǎn)提供決策依據(jù)。有剩余的資源影子價(jià)格為0。表7microsoft excel 9.0 敏感性報(bào)告工作表 習(xí)題一.xlssheet2報(bào)告的建立: 2006-8-25 11:01:22可變單元格終遞減目標(biāo)式允許的允許的單元格名字值成本系數(shù)增量減量$b$7決策變量 產(chǎn)品1500505050$c$7決策變量 產(chǎn)品225001001e+3050約束終陰影約束允許的允許的單元格名字值價(jià)格限制值增量減量$d$3原料a35004001e+3050$d$4機(jī)器b3005030025

11、50$d$5原料c250502505050若選擇“極限值報(bào)告”，excel顯示“極限值報(bào)告”（如表8）。除了給出最優(yōu)決策對(duì)應(yīng)最優(yōu)目標(biāo)值信息外，還顯示各決策變量的上、下限值及其對(duì)應(yīng)目標(biāo)式結(jié)果。表8microsoft excel 9.0 極限值報(bào)告工作表 習(xí)題一.xlssheet2報(bào)告的建立: 2006-8-25 11:02:45目標(biāo)式單元格名字值$b$8最大利潤(rùn) 27500變量下限目標(biāo)式上限目標(biāo)式單元格名字值極限結(jié)果極限結(jié)果$b$7決策變量 產(chǎn)品1500250005027500$c$7決策變量 產(chǎn)品225002500249.999999927499.999993、讀懂excel求解線性規(guī)劃問(wèn)題輸

12、出的運(yùn)算結(jié)果報(bào)告和敏感性報(bào)告利用excel求解線性規(guī)劃問(wèn)題系統(tǒng)將提供三個(gè)計(jì)算結(jié)果報(bào)告，即運(yùn)算結(jié)果報(bào)告、敏感性報(bào)告、極限值報(bào)告。這三個(gè)報(bào)告中的前兩個(gè)報(bào)告非常重要，下面我們將結(jié)合教材第二章線性規(guī)劃的對(duì)偶理論與靈敏度分析的內(nèi)容講述如何看excel求解線性規(guī)劃問(wèn)題輸出的運(yùn)算結(jié)果報(bào)告和敏感性報(bào)告。讀懂運(yùn)算結(jié)果報(bào)告運(yùn)算結(jié)果報(bào)告比較容易看懂，可變單元格$b$7和$c$7分別表示兩個(gè)決策變量，即藥品和藥品的產(chǎn)量，在計(jì)算時(shí)，由于我們最初賦予，所以單元格$b$7和$c$7的初值為0，求得最優(yōu)解后，$b$7和$c$7的值分別為50和250，即，表示使目標(biāo)函數(shù)值最大的計(jì)劃是生產(chǎn)50個(gè)單位的藥品和250個(gè)單位的藥品。目

13、標(biāo)單元格$b$8表示目標(biāo)函數(shù)，由于我們最初賦予，所以目標(biāo)函數(shù)的初值為0，求得最優(yōu)解后，目標(biāo)函數(shù)的值為27500，即，如果生產(chǎn)50個(gè)單位的藥品和250個(gè)單位的藥品能使企業(yè)利潤(rùn)達(dá)到最大值27500元。在單元格$d$3、$d$4、$d$5我們分別輸入了三個(gè)約束條件的左邊項(xiàng)，即，和，隨后在使用工具規(guī)劃求解時(shí)，在規(guī)劃求解參數(shù)窗口，我們輸入了,而$e$3=400、$e$4=300、$e$5=250,從而完成了約束條件的輸入。當(dāng)求得最優(yōu)解后,將代入約束方程得：，未達(dá)到限制值，型數(shù)值為50；，達(dá)到限制值，型數(shù)值為0；，達(dá)到限制值，型數(shù)值為0。讀懂敏感性報(bào)告可變單元格$b$7、$c$7分別表示兩個(gè)決策變量，在求

14、得最優(yōu)解后，即敏感性報(bào)告中所顯示的$b$7、$c$7的終值分別為50和250。在目標(biāo)函數(shù)中的系數(shù)為50，允許的增量為50，允許的減量為50，意思是當(dāng)在目標(biāo)函數(shù)中的系數(shù)在50的基礎(chǔ)上再增加50或減少50，該線性規(guī)劃問(wèn)題的最優(yōu)解不變，即在其他條件不變的情況下，當(dāng)時(shí)，仍然是線性規(guī)劃問(wèn)題的最優(yōu)解。同理，在目標(biāo)函數(shù)中的系數(shù)為100，允許的增量為1e+30，允許的減量為50，意思是當(dāng)在目標(biāo)函數(shù)中的系數(shù)在100的基礎(chǔ)上再增加1e+30或減少50，該線性規(guī)劃問(wèn)題的最優(yōu)解不變，即在其他條件不變的情況下，當(dāng)時(shí)，仍然是線性規(guī)劃問(wèn)題的最優(yōu)解。當(dāng)求得最優(yōu)解后，單元格$d$3、$d$4、$d$5的值分別為350，300和

15、250，即50個(gè)單位的藥品和250個(gè)單位的藥品，消耗了原料a350千克、機(jī)器b300小時(shí)，原料c250千克。由于企業(yè)擁有的原料a、機(jī)器b，原料c的數(shù)量分別是：400千克300小時(shí)和250千克，所以生產(chǎn)50個(gè)單位的藥品和250個(gè)單位的藥品消耗掉了企業(yè)的擁有的全部機(jī)器b設(shè)備時(shí)數(shù)和原料c，但原料a還有50千克的剩余。三種資源的陰影價(jià)格分別為0、50、50，即該線性規(guī)劃問(wèn)題的對(duì)偶問(wèn)題的最優(yōu)解為意思是：保持其他生產(chǎn)條件不變，每增加1個(gè)單位的原料a使目標(biāo)函數(shù)增加的數(shù)量為0，每增加1個(gè)小時(shí)的設(shè)備b使目標(biāo)函數(shù)增加的數(shù)量為50，每增加1個(gè)單位的原料c使目標(biāo)函數(shù)增加的數(shù)量為50。約束限制值分別為400、300和2

16、50，即企業(yè)擁有的原料a、機(jī)器b，原料c的數(shù)量分別是：400千克300小時(shí)和250千克。原料a的允許的增量為1e+30，允許的減量為50，意思是原料a在現(xiàn)有數(shù)量的基礎(chǔ)上再增加1e+30或減少50，原料a的影子價(jià)格不變，即在其他條件不變的情況下，當(dāng)時(shí)，。同理可得：當(dāng)時(shí)，；當(dāng)時(shí)，。三、 課外練習(xí)1、利用excel試算教材后面的習(xí)題，然后與手工計(jì)算的結(jié)果進(jìn)行對(duì)比。2、練習(xí)利用線性規(guī)劃模型制訂總體計(jì)劃。例：紅西紅柿公司是一家園藝工具生產(chǎn)商，公司主要是將購(gòu)進(jìn)的原材料制造成多用途的園藝工具。由于生產(chǎn)線需要的工具和場(chǎng)地是有限定的，紅西紅柿公司的生產(chǎn)能力主要由勞動(dòng)力人數(shù)決定。該公司的產(chǎn)品需求季節(jié)性很強(qiáng)，需求最

17、旺的時(shí)間在春季。該公司決定利用總體計(jì)劃來(lái)克服需求季節(jié)性變動(dòng)的障礙，同時(shí)實(shí)現(xiàn)利潤(rùn)最大化。公司的選擇是，在淡季建立庫(kù)存，當(dāng)旺季到來(lái)時(shí)增加工人，簽訂轉(zhuǎn)包合同，交積壓訂單登記入冊(cè)，以后再將產(chǎn)品送達(dá)顧客。為了知道如何利用總體計(jì)劃中的這些選擇，“紅西紅柿”供應(yīng)鏈的副總裁從預(yù)測(cè)下6個(gè)月的需求著手工作，如表9所示。該公司以40美元的單價(jià)銷售其工具。公司在1月的庫(kù)存為1000個(gè)工具，在1月初有80名員工。每個(gè)月每條生產(chǎn)線有20個(gè)工作日，每個(gè)工人每小時(shí)收入4美元。每個(gè)工人每天工作8小時(shí)，其余時(shí)間休息。如前所述，生產(chǎn)線的生產(chǎn)能力主要取決于工人工作總時(shí)數(shù)。所以，機(jī)器的生產(chǎn)能力并不限制生產(chǎn)線的生產(chǎn)能力。根據(jù)勞動(dòng)法規(guī)定，

18、工人每月加班不能超過(guò)10小時(shí)。各種成本如表10所示。目前，紅西紅柿公司在轉(zhuǎn)包合同、庫(kù)存缺貨或積壓上沒(méi)有什么限制。所有庫(kù)存缺貨被積累起來(lái)，由下一個(gè)月生產(chǎn)出來(lái)的產(chǎn)品來(lái)滿足。庫(kù)存成本在當(dāng)月庫(kù)存結(jié)清時(shí)才計(jì)入。供應(yīng)鏈管理者的目標(biāo)是，制定出一個(gè)最合理的總體計(jì)劃，它將使庫(kù)存量在6月底至少為500個(gè)單位，即6月底沒(méi)有庫(kù)存缺貨，而是至少有500個(gè)單位的庫(kù)存量。問(wèn)企業(yè)應(yīng)該如何制定總體計(jì)劃才能使總成本最小。表9 “紅西紅柿”工具的需求月份123456需求預(yù)測(cè)160030003200380022002200表10“紅西紅柿”工具公司的成本項(xiàng)目成本原材料成本每單位10美元庫(kù)存成本每月每單位2美元庫(kù)存缺貨或積壓的邊際成本每月每單位5美元雇用或者培訓(xùn)勞動(dòng)力成本每個(gè)工人300美元解雇員工成本每個(gè)工人500美元需要的勞動(dòng)時(shí)間每單位4美元日常工作成本每小時(shí)4美元加班成本每小時(shí)6美元轉(zhuǎn)包成本每單位30美元3、捷運(yùn)公司在下一年度的14月的4個(gè)月內(nèi)擬租用倉(cāng)庫(kù)堆放物資。已知各月份所需倉(cāng)庫(kù)面積如表1所示。倉(cāng)庫(kù)租借費(fèi)用隨合同期而定，期限越長(zhǎng)，折扣越大，具體數(shù)據(jù)見表2。租借倉(cāng)庫(kù)的合同每月初都可辦理，每份合