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

2、規(guī)劃求解可調整這組單元格來影響目標單元格。 目標單元格服從一定的約束和限制。約束條件不同，結果就不同。 可求解特定單元格的最大值或最小值或某個值。 對一個問題可以求出多個解。1.1加載“規(guī)劃求解”模塊 首先，打開Excel文件，進入表格界面，單擊“工具（T）”，如果存在“規(guī)劃求解”項目，說明已經加載（加載只需進行一次，以后如果不人為刪除，就會保留在工具欄內），可直接使用。圖1-1“加載宏”圖 如果不存在“規(guī)劃求解”項目，單擊“加載宏”，會出現如圖1-1所示“加載宏”圖框。單擊“規(guī)劃求解”，使復選框中出現對勾，再單擊“確定”，即完成了加載（注：若在Office軟件裝入時，系統(tǒng)未選擇該工具模塊裝入

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

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

5、）”欄的“規(guī)劃求解”，得到圖1-4“規(guī)劃求解參數”框。 圖1-4 規(guī)劃求解參數框 首先設置目標單元格，這里$E$5即E5。其余設置如圖1-4所示，值得說明的是設置可變單元格通過單擊“推測（G）”來完成，設置約束通過單擊“添加（A）”來完成。 圖1-5 規(guī)劃求解選項框 圖1-6 規(guī)劃求解結果選擇框 在這一步要通過單擊“選項（0）”來設置有關信息，單擊“選項（0）”后可得到如圖1-5所示的框。在這里只需單擊“采用線性模型（M）”即可完成，然后點“確定”，回到“規(guī)劃求解參數”框。(3) 解線性規(guī)劃模型：在“規(guī)劃求解參數”框單擊“求解（S）”，得到如圖1-6所示的“規(guī)劃求解結果”框，其中“報告（R）”

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

7、3，當其他參數均不變時，它最多增加可趨于正無窮，最多減少1時，最優(yōu)解不會變； 關于約束右端項：b1當前值為350，當其他參數均不變時，它最多增加125，最多減少50時，對偶價格（影子價格）不會變；b2當前值為125，當其他參數均不變時，它最多增加125，減少時可趨于負無窮，對偶價格（影子價格）不會變；b3當前值為600，當其他參數均不變時，它最多增加100，最多減少125時，對偶價格（影子價格）不會變。1.3 運輸問題求解例1-2 某公司從三個產地A1、A2、A3 將物品運往四個銷地B1、B2、B3、B4，各產地的產量、各銷地的銷量和各產地運往各銷地每件物品的運費（百元）如表1-1 所示。表1

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

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

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

11、題，亦可用“規(guī)劃求解”來計算求解。輸入與線性規(guī)劃問題的輸入完全一樣，如圖1-15所示。 圖1-15 例1-4輸入數據表格 然后，設這個計算參數，如圖1-16所示。 圖1-16 例1-4規(guī)劃求解參數界面 單擊“確定”后，可得到計算結果： 2 排隊論問題求解 Excel在表格中可以使用公式，進一步還可以編程，因而可以求解大部分規(guī)范化的運籌學問題。本節(jié)介紹求解排隊論問題的Excel文檔，由于試用了“宏”，因此當系統(tǒng)提示是否啟用宏時，請單擊“啟用宏”。2.1 M/M/1模型(1) M/M/1/模型例2-1 某公交一卡通充值站，有1個服務員，前來充值的顧客按泊松分布到達，平均每小時45人，每次充值服務的

12、時間服從負指數分布，平均為1min。求： 1) 到達時，不需等待即可接收充值服務的概率p0； 2) 平均排隊等待充值的和站內總的平均顧客數Lq、L； 3) 顧客為了充值等待和逗留的時間Wq、W； 4) 顧客到來需要等待的概率pW。解：首先，可以確定此問題的模型為：M/M/1/模型。參數 l = 45/60 = 0.75，m = 1。調用“Excel文檔/排隊論模型/M-M-1”中的 “M/M/1”表，如圖2-1所示,輸入參數，即可得到有關的結果。 圖2-1 M/M/1/模型求解 于是，我們得到解為： 1) 到達時，不需等待即可接收充值服務的概率 p0 =0.25； 2) 平均排隊等待充值的和站

13、內總的平均顧客數 Lq = 2.25，L = 3.0； 3) 顧客為了充值等待和逗留的時間 Wq = 3min，W = 4min； 4) 顧客到來需要等待的概率 pW =0.75 。(2) M/M/1/N/模型例2-2 一個小理發(fā)店只有1名理發(fā)師，除理發(fā)用椅外店里還準備了3三把座椅供顧客等待時休息。已知理發(fā)時間服從負指數分布，每名顧客的平均理發(fā)時間為20min。來理發(fā)顧客的到達服從泊松分布，平均每小時2人。求： 1) 顧客到達時，不需等待即可理發(fā)的概率p0； 2) 平均排隊等待理發(fā)的和理發(fā)店內總的平均顧客數Lq、L； 3) 顧客為了理發(fā)等待和逗留的時間Wq、W； 4) 顧客到來需要等待的概率

14、pW 和 顧客損失率。解：首先，可以確定此問題的模型為：M/M/1/4/模型。參數 l = 2，m = 60、20=3。調用“Excel文檔/排隊論模型/M-M-1”中的 “M/M/1/N”表，如圖2-2所示輸入參數，即可得到有關的結果。 圖2-2 M/M/1/4/模型求解于是，我們得到解： 1) 顧客到達時，不需等待即可理發(fā)的概率p0 = 0.3839； 2) 平均排隊等待理發(fā)的和理發(fā)店內總的平均顧客數 Lq=0.6256，L=1.2417； 3) 顧客為了理發(fā)等待和逗留的時間 Wq=0.3385h=20.3077min，W=0.6718h=40.3077min； 4) 顧客到來需要等待的概

15、率 pW =0.6161， 顧客損失率 = 。2.2 M/M/c模型(1) M/M/c/模型例2-3 某公交一卡通充值站，有2個服務員，前來充值的顧客按泊松分布到達，平均每小時45人，每次充值服務的時間服從負指數分布，平均為1分鐘。求： 1) 沒有顧客充值服務的概率p0； 2) 平均排隊等待充值的和站內總的平均顧客數Lq、L； 3) 顧客為了充值等待和逗留的時間Wq、W； 4) 顧客到來需要等待的概率pW。解：首先，可以確定此問題的模型為：M/M/2/模型。參數 l = 45/60 = 0.75，m = 1。調用“Excel文檔/排隊論模型/M-M-c”中的 “M/M/c”表，如圖2-3所示,

16、輸入參數，即可得到有關的結果。 圖2-3 M/M/c/模型求解于是，得到解 1) 沒有顧客充值服務的概率 p0 = 0.4545； 2) 平均排隊等待充值的和站內總的平均顧客數 Lq=0.1227，L=0.8727； 3) 顧客為了充值等待和逗留的時間 Wq = 0.1636min、W = 1.1636min； 4) 顧客到來需要等待的概率 pW = 0.2045 。(2) M/M/c/N/模型例2-4 一個小理發(fā)店只有2名理發(fā)師，除理發(fā)用椅外店里還準備了3三把座椅供顧客等待時休息。已知理發(fā)時間服從負指數分布，每名顧客的平均理發(fā)時間為20min。來理發(fā)顧客的到達服從泊松分布，平均每小時2人。求

17、： a) 顧客到達時，不需等待即可理發(fā)的概率p0； b) 平均排隊等待理發(fā)的和理發(fā)店內總的平均顧客數Lq、L； c) 顧客為了理發(fā)等待和逗留的時間Wq、W； d) 顧客損失率。解：首先，可以確定此問題的模型為：M/M/2/5/模型。參數 l = 2，m = 60/20=3。調用“Excel文檔/排隊論模型/M-M-c”中的 “M/M/c/N”表，如圖2-2所示輸入參數，即可得到有關的結果。 圖2-4 M/M/2/5/模型求解于是，我們得到解： 1) 顧客到達時，不需等待即可理發(fā)的概率 p0 = 0.5010； 2) 平均排隊等待理發(fā)的和理發(fā)店內總的平均顧客數 Lq= 0.0742，L= 0.7

18、409； 3) 顧客為了理發(fā)等待和逗留的時間 Wq=0.0373h=2.24min，W=0.3706h=22.24min； 4) 顧客損失率 =。2.3 M/G/1模型例2-5 某公司的1位顧客咨詢接待人員，前來咨詢的顧客按泊松分布到達，平均每小時21人，每人次的接待時間服從正態(tài)分布 N( 2, 1.22 )。求： 1) 顧客到達時，不需等待即可接收服務的概率 p0； 2) 平均排隊等待的和接待室內總的平均顧客數Lq、L； 3) 顧客為了咨詢等待和逗留的時間Wq、W； 4) 顧客到來需要等待的概率pW。解：首先，可以確定此問題的模型為：M/G/1/模型。參數 l = 21/60 = 0.35，

19、m = 1/2=0.5。調用“Excel文檔/排隊論模型/M-G-1”，如圖2-5所示輸入參數，即可得到有關的結果。 圖2-5 M/G/1/模型求解于是，得到解 a) 到達時，不需等待即可接收服務的概率 p0 = 0.3； b) 平均排隊等待的和接待室內總的平均顧客數 Lq = 1.1107，L = 1.8107； c) 顧客為了咨詢等待和逗留的時間 Wq=3.173min，W = 5.1733min； d) 顧客到來需要等待的概率 pW =0.7。2.4 M/M/c/m/m模型例2-6 某機加工車間有6臺相同的機器，每臺機器平均20min需加油一次，由于工作強度是隨機的，機器缺油時自動停機，

20、停機時間服從負指數分布。每個維修保養(yǎng)工完成1臺機器的加油平均需要2min，加油時間服從負指數分布，現有2個維修保養(yǎng)工。求： 1) 系統(tǒng)里平均等待和正在加油的機器數； 2) 一臺機器缺油而停機等待加油的平均時間； 3) 2個維修保養(yǎng)工都空閑的概率。解：首先，可以確定此問題的模型為：M/M/2/6/6模型。參數 l = 1/20=0.5，m = 1/2=0.5。調用“Excel文檔/排隊論模型/M-M-c-m-m”中的表，如圖2-6所示輸入參數，即可得到有關的結果。 圖2-6 M/M/2/6/6模型求解于是，得到解 1) 系統(tǒng)里平均等待和正在加油的機器數 Lq=0.0227，L=0.5661； 2

21、) 一臺機器缺油而停機等待加油的平均時間 Wq = 0.0834min； 3) 2個維修保養(yǎng)工都空閑的概率 p0 = 0.5602。3 存儲論確定型問題求解 本節(jié)通過例題介紹求解四個確定型存儲模型的Excel文檔。3.1 不允許缺貨的訂購型存儲問題例3-1某企業(yè)生產的產品中有一外購件，年需求量為 60000 件，單價為 35 元。該外購件可在市場立即采購得到，并設不允許缺貨。已知每組織一次采購需 720 元，每件每年的存儲費為該件單價的20%。試求經濟定貨批量及每年最小的存儲加上采購的總費用。解：此問題是不允許缺貨的訂購模型，用“存貯論例”文檔中的“經濟訂購不允許缺貨”表進行計算。見圖3-1。其中的參數： 年單件存儲費 c1=35元20%=7元 訂貨費 c3=720 年需求 D=60000件 圖3-1 不允許缺貨的訂購模型求解3.2 不允許缺貨的生產型存儲問題例3-2 一條生產線如果全部用于某種型號產品時，其年生產能力為臺。據預測對該型號產品的年需求量為 臺，并在全年內需求基本保持平衡，因此該生產線將用于多品種的輪番生產。已知在生產線上更換一種產品時，需準備結束費 1350 元。該產品每臺成本為 45 元，年存儲費用為產品成本的 24%，不允許發(fā)生供應短缺。求使費用最小的該產品的生產批量。解：此問題是不允許缺貨的生產模型，用“存儲論例”文