Excel規(guī)劃求解的使用

1、Excel軟件“規(guī)劃求解”的使用用Excel軟件的“規(guī)劃求解”功能可以方便地求解線性規(guī)劃、整數(shù)規(guī)劃和非線性規(guī)劃問(wèn)題。但如果安裝 Office 97時(shí)采用的是典型安裝方法，則【工具】菜單中是 無(wú)“規(guī)劃求解”功能項(xiàng)的。可參照2.8中介紹的方法將未安裝的組件安裝完整。下面以第八章例8.1為例介紹用Excel求解線性規(guī)劃的操作步驟和運(yùn)行輸出結(jié) 果的分析。一.求解線性規(guī)劃的操作過(guò)程1 .輸入數(shù)據(jù)、公式和說(shuō)明文字(1)在工作表中按圖9.7所示格式輸入必要的說(shuō)明文字(圖中粗體字部分)和LP模型的原始數(shù)據(jù)(圖中虛線框所示單元格內(nèi)，注意并不需要化為標(biāo)準(zhǔn)型)；圖中F4是放置目標(biāo)函數(shù)的單元格，B5:D5是放置決策變

2、量Xi、X2、X3 (既“可變單元 格”)的區(qū)域。2C crosoifl iExcel -定量法上視案例-工-! x|宣文件如編髓5視圖5 抽入中格式 工具(D數(shù)揩如國(guó)口如 TOC o 1-5 h z 幫助呼U1M A |_B C _J= D J_ EF二例Bl：線性規(guī)劃的一般求解方法、,匚 | 口產(chǎn)品甲為產(chǎn)品乙出產(chǎn)品丙為修4憚位利潤(rùn)：132引和詞總額：|S 產(chǎn)量.O3d 012al3序能力瓦9InL 2 &序序序工工T.1 1L圖9.7(2)在F4單元格內(nèi)輸入目標(biāo)函數(shù) Xo的計(jì)算公式：=B4*B5+C4*C5+D4*D5或 =SUMPRODUCT (B4:D4,B5:D5)其中SUMPROD

3、UCT ()函數(shù)返回兩個(gè)或多個(gè)區(qū)域(即數(shù)組)中對(duì)應(yīng)單元格乘 積之和的值。該函數(shù)可在 Excel的“數(shù)學(xué)和三角函數(shù)”中找到。(1)在E8單元格中輸入第一個(gè)約束條件左端的計(jì)算公式:=B8*$B$5+ C8*$C$5+D8*$D$5或 =SUMPRODUCT (B8:D8,$B$5:$D$5)然后拖曳E8的填充柄將公式復(fù)制到 E9、E10單元格(注意公式中的B5、C5、D5 或B5:D5要使用絕對(duì)引用)。當(dāng)模型中的變量數(shù)較多時(shí)，使用 SUMPRODUCT ()函數(shù)可大大加快以上兩個(gè) 公式的輸入速度。說(shuō)明：圖中粗線框是表示要輸入公式的單元格。用 Excel求解線性規(guī)劃的數(shù)據(jù) 輸入格式可由用戶自行設(shè)計(jì)，

4、但以上介紹的格式不僅與我們所熟悉的 LP模型相似， 便于理解和使用；而且便于在對(duì)話框中輸入約束條件。 按以上格式輸入說(shuō)明文字后， 還可以使系統(tǒng)所輸出的三個(gè)運(yùn)行結(jié)果報(bào)告更具可讀性。.選【工具】一 “規(guī)劃求解”，“打開(kāi)規(guī)劃求解參數(shù)”對(duì)話框，見(jiàn)圖 9.8。圖9.8(1)在“設(shè)置目標(biāo)單元格”文本框中輸入目標(biāo)單元格(建議用鼠標(biāo)選定的方法 輸入，下同)，并選系統(tǒng)默認(rèn)的“最大值”單選紐；(2)在“可變單元格”文本框中輸入B5:D5 (既指定決策變量所在的單元格).單擊“約束”框中的添加按紐，打開(kāi)“添加約束”對(duì)話框，見(jiàn)圖 99萍加約束單元格引用位置約束值:|SE16:ES10|=0 （方法同上）；說(shuō)明：對(duì)有整

5、數(shù)約束要求的決策變量，應(yīng)再將相應(yīng)的可變單元格設(shè)為 “int ”約 束（即求解純整數(shù)規(guī)劃或混合整數(shù)規(guī)劃問(wèn)題）；對(duì)模型中的0-1變量，則應(yīng)再將相應(yīng) 的可變單元格設(shè)為“ bin”約束（即二進(jìn)制變量）。（3）單擊確定，返回“規(guī)劃求解參數(shù)”對(duì)話框，見(jiàn)圖 9.10。圖 9.101.單擊選項(xiàng)按紐，打開(kāi)“規(guī)劃求解選項(xiàng)”對(duì)話框，見(jiàn)圖 9.11現(xiàn)劃求解選項(xiàng)何采用嬲性模型也 r假定非負(fù)r自劭按比例得觸也 r顯示退低結(jié)果估計(jì)1導(dǎo)數(shù)1費(fèi)索行正切函數(shù)6向前差分口汗牛頓法r二次方程r中心差分r共輾法也圖 9.119.12選“采用線性模型” 復(fù)選框（選擇該選項(xiàng)后，系統(tǒng)將使用單純形法求解，否則 就使用非線性規(guī)劃的算法求解，不僅

6、速度慢，精度低，而且輸出的敏感性報(bào)告內(nèi)容 也少）。單擊確定，系統(tǒng)運(yùn)算結(jié)束后打開(kāi)“規(guī)劃求解結(jié)果”對(duì)話框，見(jiàn)圖圖 9.124.在“報(bào)告”列表框中選定所有3個(gè)報(bào)告，并單擊確定，系統(tǒng)在工作表中顯示運(yùn)行結(jié)果并生成3個(gè)輸出報(bào)告的工作表，見(jiàn)圖9.13案例直1手知文件如 編指理 觀圖值插kcd格式。1am 盈拈如 窗口 幫助如型JX例日.1：線性規(guī)劃的一般求解方法產(chǎn)品甲X1產(chǎn)品乙12247單位利潤(rùn).1產(chǎn)里100產(chǎn)品丙修司前嗣總額. 法01350包務(wù)工序能力hl2:3:序序序工匚工0110143 6 24 g 42JJ14 4 乂敏感樣孤告/根限值差告1 Ml./運(yùn)|1|圖 9.13二.輸出結(jié)果分析由圖9.13

7、,可知本問(wèn)題的最優(yōu)解為：Xi*=0, X2*=100, X3*=230, X0*=1350即每天生產(chǎn)乙產(chǎn)品100件，丙產(chǎn)品230件，不生產(chǎn)甲產(chǎn)品，每天總利潤(rùn)為 1350元。 在以上最優(yōu)生產(chǎn)方案下，工序1和2的能力都已用完，而工序3則每天還有20分鐘 的剩余能力（僅用去400分鐘）。系統(tǒng)輸出的“運(yùn)算結(jié)果報(bào)告”、“敏感性報(bào)告”和“極限值報(bào)告”三張工作表分別見(jiàn)圖9.14、圖9.15、圖9.16I X Hi eroEfflfi-定量法上機(jī)案例11=【匚1刈為文件膽）偏輯舊視圖中插入格式：1具數(shù)據(jù)囪口如幫助 比兇ABCDEFG6目可約標(biāo)單元格（最大值）7S單元格$F$4名字初值利澗忌甑：XQ0轆1350

8、g10變單元格11單兀格名字初值終值12$B$5產(chǎn)量：產(chǎn)品甲XI0013$C$5產(chǎn)量：產(chǎn)品乙X2010014$D$5產(chǎn)量：產(chǎn)品丙X302301616束17單元格名字單元格值公式狀態(tài) 型數(shù)值ISJE$8工序 1 : EaijXj430褪仁$F$8 到達(dá)限取值019$E$9工序2: SaijXj460p9=0到達(dá)限制值0二 223icjs產(chǎn)量：產(chǎn)品乙X2100 1C$5=0未到限制值100$D?5產(chǎn)量：產(chǎn)品丙應(yīng)230 |D$5=0將限制值 230看起A會(huì)與宜一希成特招,松限值相的八|f川圖 9.14圖9.14所示“運(yùn)算結(jié)果報(bào)告”中三道工序約束的“型數(shù)值”給出的是最優(yōu)解中 松弛變量的值，即三道工序時(shí)

9、間的富裕量。圖 9.15圖9.15所示“敏感性報(bào)告”中“可變單元格”下的“遞減成本”給出的是最優(yōu) 單純形迭代中各決策變量檢驗(yàn)數(shù)的負(fù)值；”允許的增量”和“允許的減量”則給出了 在不影響當(dāng)前最優(yōu)基的條件下各決策變量目標(biāo)函數(shù)系數(shù)Cj的可變動(dòng)范圍；而“約束”下的“陰影價(jià)格”則給出了各種資源的“影子價(jià)格”,其含義將將在第十章中介紹“允許的增量”和“允許的減量”則給出了在不影響當(dāng)前最優(yōu)基的條件下各有限資 源數(shù)量（約束條件右端常數(shù)）的可變動(dòng)范圍。以上敏感性分析的內(nèi)容將在第十一章 中詳細(xì)介紹。EkcwI 定量法上機(jī)塞例kIeTTriTx刊文件 編輯如 視圖9 播人9 格式工具G 數(shù)據(jù)皿 囪口處）幫助-|g|

10、X|A67891011121314目標(biāo)式 單元格名字值 $F$4 利潤(rùn)兌1350變量 單元格名字值 $8$5 產(chǎn)量： 0 iC15 _尸亳 100 $D$5 產(chǎn)量：230下限目標(biāo)式 極限結(jié)果013500H500200上限目標(biāo)式 極限結(jié)果Q 由100135023013501 JJif 7 工1認(rèn)敏感性弗告限值能占1 ：例Ml圖 9.16圖9.16所示“極限值報(bào)告”給出了各決策變量分別取下限和上限值時(shí)對(duì)目標(biāo)函 數(shù)的影響。三.關(guān)于“規(guī)劃求解選項(xiàng)”對(duì)話框中各可選項(xiàng)的說(shuō)明：“最長(zhǎng)運(yùn)算時(shí)間”一一可輸入的最大值為 32767 （秒），默認(rèn)值100 （秒）可以 滿足大多數(shù)小型規(guī)劃求解要求?！暗螖?shù)”一一設(shè)定

11、求解過(guò)程中迭代運(yùn)算的次數(shù)。可輸入的最大值為32767, 默認(rèn)值100次，可滿足大多數(shù)小型規(guī)劃求解要求?！熬取币灰惠斎胗糜诳刂魄蠼饩鹊臄?shù)字，以確定約束條件單元格中的數(shù)值 是否滿足目標(biāo)值或上下限。默認(rèn)值為 0.000001?！霸试S誤差”一一輸入滿足整數(shù)約束條件的目標(biāo)單元格求解結(jié)果與最佳結(jié)果問(wèn)的允許百分偏差。此選項(xiàng)只應(yīng)用于具有整數(shù)約束條件的問(wèn)題。默認(rèn)值為5%0“收斂度”一一輸入收斂度數(shù)值，當(dāng)最近五次迭代時(shí)，目標(biāo)單元格中數(shù)值的變 化小于“收斂度”編輯框中設(shè)置的數(shù)值時(shí)，“規(guī)劃求解”停止運(yùn)行。收斂度只應(yīng)用于 非線性規(guī)劃問(wèn)題，默認(rèn)值為0.001“采用線性模型”一一當(dāng)模型中的所有關(guān)系都是線性的，并且希望解決線性優(yōu) 化問(wèn)題或?qū)Ψ蔷€性問(wèn)題進(jìn)行線性逼近時(shí)，選中此復(fù)選框可加速求解進(jìn)程。“顯示迭代結(jié)果”一一如果選中此復(fù)選框，每進(jìn)行一次迭代后都將中斷“規(guī)劃求解”，并顯示當(dāng)前的迭代結(jié)果?！白詣?dòng)按比例縮放”一一當(dāng)輸入和輸出值數(shù)量差別很大時(shí)，可以使用此功能?！凹俣ǚ秦?fù)”一一對(duì)于在“添加約束”對(duì)話框的“約束值”編輯框中沒(méi)有設(shè)置 下限的