學(xué)生用_實(shí)驗(yàn)指導(dǎo)書(shū)_excel線(xiàn)性規(guī)劃實(shí)驗(yàn)

1、$誨彩也V丈禽實(shí)驗(yàn)指導(dǎo)書(shū)管理決策模型與方法學(xué)院（部）管理學(xué)院指導(dǎo)教師金玉蘭實(shí)驗(yàn)1 EXCEL線(xiàn)性規(guī)劃實(shí)驗(yàn)、實(shí)驗(yàn)?zāi)康?、掌握應(yīng)用Excel軟件求解線(xiàn)性規(guī)劃問(wèn)題;2、掌握應(yīng)用Excel軟件對(duì)線(xiàn)性規(guī)劃問(wèn)題進(jìn)行靈敏度分析;3、掌握應(yīng)用Excel軟件求解整數(shù)規(guī)劃問(wèn)題;4、掌握應(yīng)用Excel軟件求解0-1整數(shù)規(guī)劃問(wèn)題、實(shí)驗(yàn)設(shè)備、儀器及所需材料配置在Pentium川，存128M以上的電腦；裝有Microsoft Windows 操作系統(tǒng)及 MicrosoftOffice 2003工作軟件。三、實(shí)驗(yàn)原理"規(guī)劃求解”是Microsoft Excel中的一個(gè)加載宏，借助它可以求解許多運(yùn)籌學(xué)中的數(shù) 學(xué)規(guī)劃

2、問(wèn)題。安裝Office 2003的時(shí)候，系統(tǒng)默認(rèn)的安裝方式不會(huì)安裝該宏程序，需要用戶(hù)自己選擇 安裝。安裝方法為：從 Excel菜單中選擇“工具”7“加載宏”，打開(kāi)如下對(duì)話(huà)框：選擇其中的“規(guī)劃求解”后單擊“確定”按鈕，會(huì)出現(xiàn)提示：“這項(xiàng)功能目前尚未安裝， 是否現(xiàn)在安裝？”，選擇“是”，系統(tǒng)要你插入 Office 的安裝光盤(pán)，準(zhǔn)備好后單擊確定，很 快就會(huì)安裝完畢。于是，你會(huì)發(fā)現(xiàn)在“工具”菜單下多出一個(gè)名為“規(guī)劃求解”的子菜單， 說(shuō)明“規(guī)劃求解”功能已經(jīng)成功安裝。在EXCEI2007版本中，通過(guò)點(diǎn)擊“office按鈕”，“ EXCE選項(xiàng)”宀“加載項(xiàng)”宀轉(zhuǎn)到“EXCEL加載項(xiàng)”，然后加載【規(guī)劃求解加載

3、項(xiàng)】便可以加載規(guī)劃求解的宏。PD-Mt.oF Ofh£0 COIM A did I仔 XP O F«VI ko rofflfis走X潔住n心S.-.*CTranslailciin AddinCDfflce pscfajal Syrnfaaill I nput AcJd -In- OHlct Wide vrribol triptrl*見(jiàn)創(chuàng) F 昨 feiDlfelEJd kg 曰冊(cè)困耳祝碼昆加耐1<Oc：. .offiicxorfrce 12*3 口 CDffic cXQfFic 01 e?V . .Iflc eO f IF1 e X 1A(Cz. . AO Me o

4、1Ef 去 IVenterrnf II* 雷SBB. SS霊. 二9二"C:. r.stih: 01 ceXOflc# C:B.fl cs ISXLlbraryLjil C t. s IVUic ro ft Of fit i I 口 口 k.u i口. :x I am耳叭匡ly雪Sell atp va»n.5cllain m rotool. k la m&DSSS.IZ5:Acrobat PDFiMgiker Off-Ice COiVl Adciin左陽(yáng)U:AdobeincoripciFT&ciffiKiDjPriOQr«m AlAciDbAcro

5、bn* 7、0%PO貫3,k*rOfficwPdiA£r<ib at POFlMakdr Of+l£ o 匚021 Adclih在EXCEI2010版本中，通過(guò)點(diǎn)擊“文件”選項(xiàng)卡打開(kāi)“ Excel選項(xiàng)”對(duì)話(huà)框，單擊左側(cè)“加載項(xiàng)”標(biāo)簽，在右側(cè)單擊“轉(zhuǎn)到”按鈕， 打開(kāi)“加載宏”對(duì)話(huà)框， 勾選“規(guī)劃求解加載項(xiàng)”復(fù)選框，單擊“確定”按鈕，即可在工具欄的“數(shù)據(jù)”選項(xiàng)卡中出現(xiàn)“分析”選項(xiàng)組,上面就有了 “規(guī)劃求解”按鈕。利用“規(guī)劃求解”功能，就可以進(jìn)行線(xiàn)性規(guī)劃問(wèn)題的求解。例如：用EXCE:L求解數(shù)學(xué)規(guī)劃問(wèn)題maxZ2X3x2Xi2x284%16s.t4x212Xi0, X20步驟

6、：1.將模型中的目標(biāo)函數(shù)和約束條件的系數(shù)輸入到單元格中；為了使我們?cè)诓僮鬟^(guò)程中看得更清楚，可以附帶輸入相應(yīng)的標(biāo)識(shí)符，并給表格加上邊框。如下圖所示：2. 在E4 單元格(目標(biāo)值)輸入 “ =SUMPR0DUCT($C$)$3, C4: D4)” ;其中，SUMPRODUC函數(shù)的功能是將數(shù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和， 即SUMPR0DUCT($C$D$3, C4: D4)=C3X C4+ D3X D4; $C$3: $D$3表示這幾個(gè)單元格為絕對(duì)引用。3. 用拖動(dòng)的方式將 E4單元格的公式復(fù)制到 E5E7,如下圖：E4 - A =SUl«PRODUCT(jq3:JB$3rC4:D

7、4)ABCD廠EFGH12Xxlh2ZbI 34C2305A120<=86016740 <-12'8卜4從“工具”選擇“規(guī)劃求解”，打開(kāi)“規(guī)劃求解參數(shù)”窗口。Office 2007中，從“數(shù)據(jù)”選擇“規(guī)劃求解”，打開(kāi)“規(guī)劃求解參數(shù)”窗口。5.將窗口中的目標(biāo)單元格 設(shè)為$E$4,可變單元格 設(shè)為$C$3:$D$3,目標(biāo)為求最大值，如下圖:6.單擊“添加”按鈕，打開(kāi)“添加約束”對(duì)話(huà)框；將單元格引用位置 設(shè)為$E$5:$E$7,約束值設(shè)為$G$5$G$7,不等式符號(hào)為 <=,如下圖：7選擇“確定”返回“規(guī)劃求解參數(shù)”窗口；單擊“選項(xiàng)”按鈕，彈出“規(guī)劃求解選項(xiàng)”窗口，并選中其

8、中的“圖：米用線(xiàn)性模型”和“假定非負(fù)”兩項(xiàng)，其余選項(xiàng)可保留默認(rèn)值。如下&選擇“確定”按鈕返回“規(guī)劃求解參數(shù)”窗口；單擊“求解”按鈕，系統(tǒng)彈出 “規(guī)劃求解結(jié)果”對(duì)話(huà)框，如下圖：髓乎解找到-蹴可滿(mǎn)足所有的約束鵡憂(yōu)報(bào)告®敏感性報(bào)告 極限值報(bào)告保存方案幫肋QD9 將對(duì)話(huà)框右邊“報(bào)告”下的全部容選中（也可不選）后，單擊“確定”按鈕完成計(jì)算, 結(jié)果如下圖?？芍搯?wèn)題的最優(yōu)解為：xi = 4, x2= 2, max Z =14。ABCDEFGH1pl_Xxlx2Zb344 c23145 18<=86 J_A416<=167 J斗8<-12r s i四、實(shí)驗(yàn)容（一）用EXC

9、EL求解線(xiàn)性規(guī)劃問(wèn)題maxZ 12x1 10x22x1 x216013xi 13X240s.t3x1 2x2260x10, x20（二）用EXCEL求解線(xiàn)性規(guī)劃問(wèn)題，并進(jìn)行靈敏度分析maxZ 20x1 30x22x1X2402x240s.tX2250, X20（三）用EXCEL求解整數(shù)規(guī)劃問(wèn)題maxZ 3x1 2x2x10.5x24.5s.t 2x1 3x214N 0且為整數(shù),i 1,2（四）用EXCEL求解01整數(shù)規(guī)劃問(wèn)題如果每一個(gè)客戶(hù)只需要一個(gè)項(xiàng)目負(fù)責(zé)人，那么怎么進(jìn)行分配才能使項(xiàng)目負(fù)責(zé)人完成這三個(gè)項(xiàng)目的所需的總時(shí)間最短？下表總結(jié)每個(gè)人完成各任務(wù)所需時(shí)間。項(xiàng)目負(fù)責(zé)人客戶(hù)（任務(wù)）1231.特瑞

10、101592.卡爾91853.邁克孟德6143（五）用EXCEL求解下列資源分配問(wèn)題某晝夜服務(wù)的公交線(xiàn)路每天各時(shí)間段所需司機(jī)人數(shù)如下班次時(shí)間所需人數(shù)16: 0010: 0080210: 0014: 0070314: 0018: 0080418: 0022: 0050522: 002: 002062: 006: 0030設(shè)司機(jī)在各時(shí)間段一開(kāi)始時(shí)上班，并連續(xù)工作八小時(shí)，問(wèn)該公交線(xiàn)路該怎樣安排司機(jī)人數(shù)，既能滿(mǎn)足工作需要，又配備最少司機(jī)？數(shù)學(xué)模型如下，求用EXCEL求解。minZ X x2 x3 x4 x5 x6X80X270X2X380S.tX3X450x4X520X5X630Xi0且為整數(shù),i12

11、3,4,5,6（六）請(qǐng)各位同學(xué)按照學(xué)號(hào)選做以下題目要求：請(qǐng)至少完成與學(xué)號(hào)尾數(shù)相對(duì)應(yīng)題號(hào)的題目。選做其他題目，則按題目數(shù)量和 準(zhǔn)確率進(jìn)行加分。學(xué)號(hào)尾數(shù)必做題A必做題B學(xué)號(hào)尾數(shù)必做題A必做題B01055521186632267713398844479910題目 1：maxZ5x12x2 3x3x4x12x23x3 x48s.t. 3x14x2x3 x5=7x1,x2,x3,x4,x5 0題目 2：minZx12x2x1x34s.t.x2x43x12x2+x58xi0, i1,2,3, 4,5題目 3：maxZx12x2x1+ x22x1x21s.t. 1x23x10, x20題目 4：maxZ40

12、x190x29x17x256s.t. 7x120x2 70xi0且都為整數(shù) , i1,2題目5:maxZ5x18x2xix26s.t5為9x245xi0且為整數(shù),i 1,2題目6：maxZ4x13x25x3xi3x22x332x1X23x35s.t2x1x224x22x38人=0或1 , i 1,2,3題目7:由甲、乙、丙、丁四人去完成 A、B、C、D四項(xiàng)工作，每人做且做其中一項(xiàng)工作，每 人完成各項(xiàng)工作的工時(shí)由下表給出。 怎樣指派工作，才能使四個(gè)人完成四個(gè)項(xiàng)目的總時(shí)間最 少。請(qǐng)用excel求解該問(wèn)題。工作A工作B工作C工作D甲312106乙1461511丙781613丁8101510題目8：工作A工作B工作C工作D甲25283134乙29333228丙d36272629丁29313327由甲、乙、丙、丁四人去完成 人完成各項(xiàng)工作的工時(shí)由下表給出。A、B、C、D四項(xiàng)工作，每人做且做其中一項(xiàng)工作，每 怎樣指派工作，才能使四個(gè)人完成四個(gè)項(xiàng)目的總時(shí)間最少。請(qǐng)用excel求解該問(wèn)題。題目9:maxZ3x12x2 102x13X2+x422x25s.tX1X32Xi0, i123,4X2,X3取整數(shù)題目10:mi nZ 2x1 5x2+