Excel求解運(yùn)籌學(xué)問題簡(jiǎn)介

1、Excel 求解運(yùn)籌學(xué)問題方法簡(jiǎn)介Excel求解運(yùn)籌學(xué)問題方法簡(jiǎn)介 Excel中的規(guī)劃求解是功能強(qiáng)大的優(yōu)化和資源配置工具。它可以幫助人們求解運(yùn)籌學(xué)中的許多問題，特別是“規(guī)劃求解”模塊可以解決許多求極值、解方程的問題。 本附件除介紹“規(guī)劃求解”模塊的使用外，還提供給讀者“排隊(duì)論”與“存儲(chǔ)論”基本模塊。1 規(guī)劃求解 在使用“規(guī)劃求解”時(shí)，首先需要“規(guī)劃求解”出現(xiàn)在“工具”菜單中，如果沒有，則需要加載“規(guī)劃求解”宏。另外，目標(biāo)函數(shù)和約束函數(shù)必須要給出公式，變量的約束必須作為約束條件給出。規(guī)劃求解的特點(diǎn)： 表格輸入數(shù)據(jù)不能為分?jǐn)?shù)，當(dāng)遇到分?jǐn)?shù)時(shí)，必須化為小數(shù)輸入。 目標(biāo)單元格依賴一組單元格（可變單元格）

2、，或通過公式間接依賴于可變單元格，規(guī)劃求解可調(diào)整這組單元格來影響目標(biāo)單元格。 目標(biāo)單元格服從一定的約束和限制。約束條件不同，結(jié)果就不同。 可求解特定單元格的最大值或最小值或某個(gè)值。 對(duì)一個(gè)問題可以求出多個(gè)解。1.1加載“規(guī)劃求解”模塊 首先，打開Excel文件，進(jìn)入表格界面，單擊“工具（T）”，如果存在“規(guī)劃求解”項(xiàng)目，說明已經(jīng)加載（加載只需進(jìn)行一次，以后如果不人為刪除，就會(huì)保留在工具欄內(nèi)），可直接使用。圖1-1“加載宏”圖 如果不存在“規(guī)劃求解”項(xiàng)目，單擊“加載宏”，會(huì)出現(xiàn)如圖1-1所示“加載宏”圖框。單擊“規(guī)劃求解”，使復(fù)選框中出現(xiàn)對(duì)勾，再單擊“確定”，即完成了加載（注：若在Office軟

3、件裝入時(shí)，系統(tǒng)未選擇該工具模塊裝入，此時(shí)會(huì)引導(dǎo)讀者插入軟件安裝盤，依據(jù)系統(tǒng)提示操作即可）。1.2 線性規(guī)劃問題求解 為了便于說明，以一個(gè)線性規(guī)劃例題來說明這個(gè)過程。例1-1 某工廠在計(jì)劃期內(nèi)要安排甲、乙兩種產(chǎn)品的生產(chǎn)，已知生產(chǎn)單位產(chǎn)品所需成本分別為2千元和3千元；根據(jù)產(chǎn)品特性，產(chǎn)品總數(shù)不得少于350件，產(chǎn)品甲不得少于125件；又知生產(chǎn)這兩種產(chǎn)品需要某種鋼材，產(chǎn)品甲、乙每件分別需要鋼材2t、1t，鋼材的供應(yīng)量限制在600t。問題：工廠應(yīng)分別生產(chǎn)多少單位甲、乙產(chǎn)品才能使總成本最低？解： 容易建立如下線性規(guī)劃模型。設(shè)x1、x2分別為產(chǎn)品甲、乙的產(chǎn)量，模型為 (1) 數(shù)據(jù)輸入：如圖1-2所示。 圖1-

4、2 數(shù)據(jù)輸入界面 在輸入界面中，第1、4、7行是說明文字，無論輸入什么內(nèi)容或不輸入均不會(huì)影響計(jì)算。其中，單元格B2、C2為決策變量初值，對(duì)于線性規(guī)劃求解，初值可任?。籅5、C5是目標(biāo)函數(shù)系數(shù)；B8、C8、B9、C9、B10、C10為各約束函數(shù)的左端系數(shù)；E8、E9、E10為約束右端項(xiàng)。以上數(shù)據(jù)均直接從鍵盤輸入。關(guān)鍵的是，E5、D8、D9、D10分別為目標(biāo)函數(shù)和約束左端三個(gè)函數(shù)的計(jì)算公式。E5的輸入如圖1-3所示，見紅色箭頭指示。E5格的公式 圖1-3 E5公式輸入方式D8、D9、D10用同樣的方式可以輸入：=B8*B2+C8*C2，=B8*B2+C8*C2，=B8*B2+C8*C2。(2) 調(diào)

5、用“規(guī)劃求解”模塊：?jiǎn)螕簟肮ぞ撸═）”欄的“規(guī)劃求解”，得到圖1-4“規(guī)劃求解參數(shù)”框。 圖1-4 規(guī)劃求解參數(shù)框 首先設(shè)置目標(biāo)單元格，這里$E$5即E5。其余設(shè)置如圖1-4所示，值得說明的是設(shè)置可變單元格通過單擊“推測(cè)（G）”來完成，設(shè)置約束通過單擊“添加（A）”來完成。 圖1-5 規(guī)劃求解選項(xiàng)框 圖1-6 規(guī)劃求解結(jié)果選擇框 在這一步要通過單擊“選項(xiàng)（0）”來設(shè)置有關(guān)信息，單擊“選項(xiàng)（0）”后可得到如圖1-5所示的框。在這里只需單擊“采用線性模型（M）”即可完成，然后點(diǎn)“確定”，回到“規(guī)劃求解參數(shù)”框。(3) 解線性規(guī)劃模型：在“規(guī)劃求解參數(shù)”框單擊“求解（S）”，得到如圖1-6所示的“規(guī)

6、劃求解結(jié)果”框，其中“報(bào)告（R）”只選“運(yùn)算結(jié)果報(bào)告”和“敏感性報(bào)告”即可。單擊“確定”。 Excel文檔中產(chǎn)生兩個(gè)新表：“運(yùn)算結(jié)果報(bào)告”（圖1-7）和“敏感性報(bào)告”（圖1-8）。 圖1-7 運(yùn)算結(jié)果報(bào)告 圖1-8 敏感性報(bào)告 運(yùn)算結(jié)果報(bào)告 ( 圖1-7 ) 中，列出了線性規(guī)劃的最優(yōu)值（800）、最優(yōu)解（x1=250, x2=100）,以及約束松弛變量的值（s1 = 0, s2 = 125, s3 = 0）。 敏感性報(bào)告 ( 圖1-8 ) 中，列出了線性規(guī)劃的對(duì)偶價(jià)格（影子價(jià)格、陰影價(jià)格4、0、1）；關(guān)于目標(biāo)函數(shù)的單因素靈敏性信息：c1當(dāng)前值為2，當(dāng)其他參數(shù)均不變時(shí)，它最多增加1，減少時(shí)可趨于

7、負(fù)無窮，最優(yōu)解不會(huì)變；c2當(dāng)前值為3，當(dāng)其他參數(shù)均不變時(shí)，它最多增加可趨于正無窮，最多減少1時(shí)，最優(yōu)解不會(huì)變； 關(guān)于約束右端項(xiàng)：b1當(dāng)前值為350，當(dāng)其他參數(shù)均不變時(shí)，它最多增加125，最多減少50時(shí)，對(duì)偶價(jià)格（影子價(jià)格）不會(huì)變；b2當(dāng)前值為125，當(dāng)其他參數(shù)均不變時(shí)，它最多增加125，減少時(shí)可趨于負(fù)無窮，對(duì)偶價(jià)格（影子價(jià)格）不會(huì)變；b3當(dāng)前值為600，當(dāng)其他參數(shù)均不變時(shí)，它最多增加100，最多減少125時(shí)，對(duì)偶價(jià)格（影子價(jià)格）不會(huì)變。1.3 運(yùn)輸問題求解例1-2 某公司從三個(gè)產(chǎn)地A1、A2、A3 將物品運(yùn)往四個(gè)銷地B1、B2、B3、B4，各產(chǎn)地的產(chǎn)量、各銷地的銷量和各產(chǎn)地運(yùn)往各銷地每件物品的

8、運(yùn)費(fèi)（百元）如表1-1 所示。表1-1 運(yùn)輸費(fèi)用表銷地產(chǎn)地B1B2B3B4產(chǎn)量/tA13113107A219284A3741059銷量/t365620（產(chǎn)銷平衡）問應(yīng)如何調(diào)運(yùn)，可使得總運(yùn)輸費(fèi)最小?解：對(duì)這個(gè)典型的運(yùn)輸問題，可用“規(guī)劃求解”來計(jì)算求解。首先，運(yùn)輸問題模型本身就是線性規(guī)劃模型，因此在計(jì)算方面，只需注意在它的決策變量輸入時(shí)，列成矩陣形式即可。 圖1-9是此問題的數(shù)據(jù)輸入界面。B2到E4是運(yùn)輸費(fèi)用矩陣，B10到E12是運(yùn)輸變量矩陣（這里輸入的初值可以任取，此處全部取值是1），B7到E7是銷地的銷量限制，H10到H12是產(chǎn)地的產(chǎn)量限制。I3是目標(biāo)函數(shù)，其公式輸入同線性規(guī)劃，如圖1-9中所

9、示；B14到E14、G10到G12分別為運(yùn)往銷地的約束函數(shù)、產(chǎn)地運(yùn)出的約束函數(shù)。 圖1-9 運(yùn)輸問題輸入界面 在“規(guī)劃求解參數(shù)”界面，決策變量即運(yùn)輸變量可直接用矩陣輸入，銷量約束和產(chǎn)量約束均用向量形式表示，如圖1-10所示。 圖1-10 運(yùn)輸問題規(guī)劃求解參數(shù)界面 單擊“求解（S）”后，即可得到問題的解和最優(yōu)值，如圖1-11所示。圖1-11 問題的解數(shù)據(jù) 由圖1-11，可得到此問題的解為：A1到B1運(yùn)輸2t、A1到B3運(yùn)輸5t、A2到B1運(yùn)輸1t、A2到B4運(yùn)輸3t、A3到B2運(yùn)輸6t、A3到B4運(yùn)輸3t，總費(fèi)用85百元。1.4 整數(shù)規(guī)劃求解例1-3 求解下列整數(shù)規(guī)劃問題 解：對(duì)這個(gè)整數(shù)規(guī)劃問題

10、，可用“規(guī)劃求解”來計(jì)算求解。輸入與線性規(guī)劃問題的輸入完全一樣，如圖1-12所示。 圖1-12例1-3輸入表格 本問題的不同之處，就是在添加約束時(shí)，設(shè)置變量為“int”，則界面自動(dòng)會(huì)顯示“整數(shù)”，如圖1-13所示。圖1-13“添加約束”中的符號(hào)選擇有5項(xiàng)，單擊“ ”時(shí)即可顯示“<=, =, >=, int, bin”，分別表示“小于等于，等于，大于等于，整數(shù)，二進(jìn)制即0、1變量”。 圖1-13 添加約束界面 設(shè)置完成的“規(guī)劃求解參數(shù)”界面如圖1-14所示。設(shè)置過程總體上與線性規(guī)劃問題求解輸入設(shè)置沒有差別，這里不再贅述。單擊“確定”后，可得到計(jì)算結(jié)果： 圖1-14 例1-3規(guī)劃求解參

11、數(shù)界面例1.3 求解下列整數(shù)規(guī)劃問題 解：對(duì)這個(gè)混合整數(shù)規(guī)劃問題，亦可用“規(guī)劃求解”來計(jì)算求解。輸入與線性規(guī)劃問題的輸入完全一樣，如圖1-15所示。 圖1-15 例1-4輸入數(shù)據(jù)表格 然后，設(shè)這個(gè)計(jì)算參數(shù)，如圖1-16所示。 圖1-16 例1-4規(guī)劃求解參數(shù)界面 單擊“確定”后，可得到計(jì)算結(jié)果： 2 排隊(duì)論問題求解 Excel在表格中可以使用公式，進(jìn)一步還可以編程，因而可以求解大部分規(guī)范化的運(yùn)籌學(xué)問題。本節(jié)介紹求解排隊(duì)論問題的Excel文檔，由于試用了“宏”，因此當(dāng)系統(tǒng)提示是否啟用宏時(shí)，請(qǐng)單擊“啟用宏”。2.1 M/M/1模型(1) M/M/1/¥/¥模型例2-1 某公交一

12、卡通充值站，有1個(gè)服務(wù)員，前來充值的顧客按泊松分布到達(dá)，平均每小時(shí)45人，每次充值服務(wù)的時(shí)間服從負(fù)指數(shù)分布，平均為1min。求： 1) 到達(dá)時(shí)，不需等待即可接收充值服務(wù)的概率p0； 2) 平均排隊(duì)等待充值的和站內(nèi)總的平均顧客數(shù)Lq、L； 3) 顧客為了充值等待和逗留的時(shí)間Wq、W； 4) 顧客到來需要等待的概率pW。解：首先，可以確定此問題的模型為：M/M/1/¥/¥模型。參數(shù) l = 45/60 = 0.75，m = 1。調(diào)用“Excel文檔/排隊(duì)論模型/M-M-1”中的 “M/M/1”表，如圖2-1所示,輸入?yún)?shù)，即可得到有關(guān)的結(jié)果。 圖2-1 M/M/1/¥

13、/¥模型求解 于是，我們得到解為： 1) 到達(dá)時(shí)，不需等待即可接收充值服務(wù)的概率 p0 =0.25； 2) 平均排隊(duì)等待充值的和站內(nèi)總的平均顧客數(shù) Lq = 2.25，L = 3.0； 3) 顧客為了充值等待和逗留的時(shí)間 Wq = 3min，W = 4min； 4) 顧客到來需要等待的概率 pW =0.75 。(2) M/M/1/N/¥模型例2-2 一個(gè)小理發(fā)店只有1名理發(fā)師，除理發(fā)用椅外店里還準(zhǔn)備了3三把座椅供顧客等待時(shí)休息。已知理發(fā)時(shí)間服從負(fù)指數(shù)分布，每名顧客的平均理發(fā)時(shí)間為20min。來理發(fā)顧客的到達(dá)服從泊松分布，平均每小時(shí)2人。求： 1) 顧客到達(dá)時(shí)，不需等待即可理

14、發(fā)的概率p0； 2) 平均排隊(duì)等待理發(fā)的和理發(fā)店內(nèi)總的平均顧客數(shù)Lq、L； 3) 顧客為了理發(fā)等待和逗留的時(shí)間Wq、W； 4) 顧客到來需要等待的概率 pW 和 顧客損失率。解：首先，可以確定此問題的模型為：M/M/1/4/¥模型。參數(shù) l = 2，m = 60、20=3。調(diào)用“Excel文檔/排隊(duì)論模型/M-M-1”中的 “M/M/1/N”表，如圖2-2所示輸入?yún)?shù)，即可得到有關(guān)的結(jié)果。 圖2-2 M/M/1/4/¥模型求解于是，我們得到解： 1) 顧客到達(dá)時(shí)，不需等待即可理發(fā)的概率p0 = 0.3839； 2) 平均排隊(duì)等待理發(fā)的和理發(fā)店內(nèi)總的平均顧客數(shù) Lq=0.62

15、56，L=1.2417； 3) 顧客為了理發(fā)等待和逗留的時(shí)間 Wq=0.3385h=20.3077min，W=0.6718h=40.3077min； 4) 顧客到來需要等待的概率 pW =0.6161， 顧客損失率 = 。2.2 M/M/c模型(1) M/M/c/¥/¥模型例2-3 某公交一卡通充值站，有2個(gè)服務(wù)員，前來充值的顧客按泊松分布到達(dá)，平均每小時(shí)45人，每次充值服務(wù)的時(shí)間服從負(fù)指數(shù)分布，平均為1分鐘。求： 1) 沒有顧客充值服務(wù)的概率p0； 2) 平均排隊(duì)等待充值的和站內(nèi)總的平均顧客數(shù)Lq、L； 3) 顧客為了充值等待和逗留的時(shí)間Wq、W； 4) 顧客到來需要等待

16、的概率pW。解：首先，可以確定此問題的模型為：M/M/2/¥/¥模型。參數(shù) l = 45/60 = 0.75，m = 1。調(diào)用“Excel文檔/排隊(duì)論模型/M-M-c”中的 “M/M/c”表，如圖2-3所示,輸入?yún)?shù)，即可得到有關(guān)的結(jié)果。 圖2-3 M/M/c/¥/¥模型求解于是，得到解 1) 沒有顧客充值服務(wù)的概率 p0 = 0.4545； 2) 平均排隊(duì)等待充值的和站內(nèi)總的平均顧客數(shù) Lq=0.1227，L=0.8727； 3) 顧客為了充值等待和逗留的時(shí)間 Wq = 0.1636min、W = 1.1636min； 4) 顧客到來需要等待的概率 p

17、W = 0.2045 。(2) M/M/c/N/¥模型例2-4 一個(gè)小理發(fā)店只有2名理發(fā)師，除理發(fā)用椅外店里還準(zhǔn)備了3三把座椅供顧客等待時(shí)休息。已知理發(fā)時(shí)間服從負(fù)指數(shù)分布，每名顧客的平均理發(fā)時(shí)間為20min。來理發(fā)顧客的到達(dá)服從泊松分布，平均每小時(shí)2人。求： a) 顧客到達(dá)時(shí)，不需等待即可理發(fā)的概率p0； b) 平均排隊(duì)等待理發(fā)的和理發(fā)店內(nèi)總的平均顧客數(shù)Lq、L； c) 顧客為了理發(fā)等待和逗留的時(shí)間Wq、W； d) 顧客損失率。解：首先，可以確定此問題的模型為：M/M/2/5/¥模型。參數(shù) l = 2，m = 60/20=3。調(diào)用“Excel文檔/排隊(duì)論模型/M-M-c”中

18、的 “M/M/c/N”表，如圖2-2所示輸入?yún)?shù)，即可得到有關(guān)的結(jié)果。 圖2-4 M/M/2/5/¥模型求解于是，我們得到解： 1) 顧客到達(dá)時(shí)，不需等待即可理發(fā)的概率 p0 = 0.5010； 2) 平均排隊(duì)等待理發(fā)的和理發(fā)店內(nèi)總的平均顧客數(shù) Lq= 0.0742，L= 0.7409； 3) 顧客為了理發(fā)等待和逗留的時(shí)間 Wq=0.0373h=2.24min，W=0.3706h=22.24min； 4) 顧客損失率 =。2.3 M/G/1模型例2-5 某公司的1位顧客咨詢接待人員，前來咨詢的顧客按泊松分布到達(dá)，平均每小時(shí)21人，每人次的接待時(shí)間服從正態(tài)分布 N( 2, 1.22 )

19、。求： 1) 顧客到達(dá)時(shí)，不需等待即可接收服務(wù)的概率 p0； 2) 平均排隊(duì)等待的和接待室內(nèi)總的平均顧客數(shù)Lq、L； 3) 顧客為了咨詢等待和逗留的時(shí)間Wq、W； 4) 顧客到來需要等待的概率pW。解：首先，可以確定此問題的模型為：M/G/1/¥/¥模型。參數(shù) l = 21/60 = 0.35，m = 1/2=0.5。調(diào)用“Excel文檔/排隊(duì)論模型/M-G-1”，如圖2-5所示輸入?yún)?shù)，即可得到有關(guān)的結(jié)果。 圖2-5 M/G/1/¥/¥模型求解于是，得到解 a) 到達(dá)時(shí)，不需等待即可接收服務(wù)的概率 p0 = 0.3； b) 平均排隊(duì)等待的和接待室內(nèi)總的

20、平均顧客數(shù) Lq = 1.1107，L = 1.8107； c) 顧客為了咨詢等待和逗留的時(shí)間 Wq=3.173min，W = 5.1733min； d) 顧客到來需要等待的概率 pW =0.7。2.4 M/M/c/m/m模型例2-6 某機(jī)加工車間有6臺(tái)相同的機(jī)器，每臺(tái)機(jī)器平均20min需加油一次，由于工作強(qiáng)度是隨機(jī)的，機(jī)器缺油時(shí)自動(dòng)停機(jī)，停機(jī)時(shí)間服從負(fù)指數(shù)分布。每個(gè)維修保養(yǎng)工完成1臺(tái)機(jī)器的加油平均需要2min，加油時(shí)間服從負(fù)指數(shù)分布，現(xiàn)有2個(gè)維修保養(yǎng)工。求： 1) 系統(tǒng)里平均等待和正在加油的機(jī)器數(shù)； 2) 一臺(tái)機(jī)器缺油而停機(jī)等待加油的平均時(shí)間； 3) 2個(gè)維修保養(yǎng)工都空閑的概率。解：首先，可

21、以確定此問題的模型為：M/M/2/6/6模型。參數(shù) l = 1/20=0.5，m = 1/2=0.5。調(diào)用“Excel文檔/排隊(duì)論模型/M-M-c-m-m”中的表，如圖2-6所示輸入?yún)?shù)，即可得到有關(guān)的結(jié)果。 圖2-6 M/M/2/6/6模型求解于是，得到解 1) 系統(tǒng)里平均等待和正在加油的機(jī)器數(shù) Lq=0.0227，L=0.5661； 2) 一臺(tái)機(jī)器缺油而停機(jī)等待加油的平均時(shí)間 Wq = 0.0834min； 3) 2個(gè)維修保養(yǎng)工都空閑的概率 p0 = 0.5602。3 存儲(chǔ)論確定型問題求解 本節(jié)通過例題介紹求解四個(gè)確定型存儲(chǔ)模型的Excel文檔。3.1 不允許缺貨的訂購(gòu)型存儲(chǔ)問題例3-1某

22、企業(yè)生產(chǎn)的產(chǎn)品中有一外購(gòu)件，年需求量為 60000 件，單價(jià)為 35 元。該外購(gòu)件可在市場(chǎng)立即采購(gòu)得到，并設(shè)不允許缺貨。已知每組織一次采購(gòu)需 720 元，每件每年的存儲(chǔ)費(fèi)為該件單價(jià)的20%。試求經(jīng)濟(jì)定貨批量及每年最小的存儲(chǔ)加上采購(gòu)的總費(fèi)用。解：此問題是不允許缺貨的訂購(gòu)模型，用“存貯論例”文檔中的“經(jīng)濟(jì)訂購(gòu)不允許缺貨”表進(jìn)行計(jì)算。見圖3-1。其中的參數(shù)： 年單件存儲(chǔ)費(fèi) c1=35元20%=7元 訂貨費(fèi) c3=720 年需求 D=60000件 圖3-1 不允許缺貨的訂購(gòu)模型求解3.2 不允許缺貨的生產(chǎn)型存儲(chǔ)問題例3-2 一條生產(chǎn)線如果全部用于某種型號(hào)產(chǎn)品時(shí)，其年生產(chǎn)能力為600000臺(tái)。據(jù)預(yù)測(cè)對(duì)該型號(hào)產(chǎn)品的年需求量為 250000 臺(tái)，并在全年內(nèi)需求基本保持平衡，因此該生產(chǎn)線將用于多品種的輪番生產(chǎn)。已知在生產(chǎn)線上更換一種產(chǎn)品時(shí)，需準(zhǔn)備結(jié)束費(fèi) 1350 元。該產(chǎn)品每臺(tái)成本為 45 元，年存儲(chǔ)費(fèi)用為產(chǎn)品成本的 24%，不允許發(fā)生供應(yīng)短缺。求使費(fèi)用最小的該產(chǎn)品的生產(chǎn)批量。解：此問題是不允許缺貨的生產(chǎn)模型，用“存儲(chǔ)論例”文檔中的“經(jīng)濟(jì)生產(chǎn)不允許缺貨”表進(jìn)行