excel做線性規(guī)劃

1、2007進行該功能加載的方法規(guī)劃求解加載宏是一個Microsoft Office Excel 加載項 （加載項：為 Microsoft Office 提供自定義命令或自定義功能的補充程序。 ）程序，安裝 Microsoft Office 或 Excel 后即可使用 該程序。但是，要在 Excel 中使用它，您需要先進行加載。1. 單擊“Ofice按鈕”按鈕圖像，然后單擊“ Exce選項”2. 單擊 加載項”然后在 管理”框中，選擇“ Excel加載宏”3. 單擊 “轉到 ”。4. 在“可用加載宏 ”框中，選中 “規(guī)劃求解加載項 ”復選框，然后單擊 “確定 ”。5. 提示 如果 “規(guī)劃求解加載項

2、 ”未在“可用加載宏 ”中列出，請單擊 “瀏覽”找到該加載宏。6. 如果出現(xiàn)一條消息， 指出您的計算機上當前未安裝規(guī)劃求解加載宏， 請單擊 “是”進行安裝。加載規(guī)劃求解加載宏后， “規(guī)劃求解 ”命令將出現(xiàn)在 “數(shù)據(jù) ”選項卡的 “分析”組中。第2章 線性規(guī)劃的計葬機求解及應用舉例Excel是分析和求解線性規(guī)劃問題一個很好的工具，它不僅可以很方便地將 線性規(guī)劃模型所有的參數(shù)錄入電子表格，而且可以利用規(guī)劃求解工具迅速找到模 型的解。最重要的是，在解好的模型中,任何參數(shù)的攻變都可以立即反映到模型的 解中，在不重新應用求解工具的情況下就可以知道許多信息，當然，即使重新求解 也只是點一下鼠標就可以了。另

3、外，作為Office家族的一員,Excel的普及性和易 學性也矣讓讀者感到利用計算機求解線性規(guī)劃十分容易。當然，除Excel外還有很 多求解線性規(guī)劃的計算機軟件，但Excel強大的功能、普及性和易學性足以滿足學 習運籌學的讀者理解線性規(guī)劃的計算機求解方法、幫助讀者們學習運籌學和在實 踐中解決線性規(guī)劃問題的要求。2.1線性規(guī)劃模型在電子表格中的布局線性規(guī)劃模型在電子表格中布局的好壞關系到問題可讀性和求解方便性的高 低。本節(jié)以第1童中的例1（資源分配問題）為例來說明一下如何在電子表格中描 述線性規(guī)劃模型，讓我們回顧一下第1童中例1的數(shù)學模型：maxZ = 4xj + 3z2乃W 6 2x2 <

4、; 8s.t.<（21）2工+ 3可 W 18習 2 0般來說，在與問題相關的表格的基礎上稍加調整就可以在電子表格中形成 一個十分清晰的模型描述.我們以表1.1為基礎在Excel電子表格中將上述問題 描述為圖21。在電子表格中我們比表1.1多加一行（第9行），用以存放決策變量的值，稱之 為最優(yōu)解。另外還多加了兩列,一列是E列,用以存放兩種產品已使用資源的數(shù) 量，我們命名為“合計”;另一列是F列，存放符號“V = ”，這一列中的符號并不參 與計算，只起提示作用，在第E列（E5、E6、E7單元格）中的數(shù)字實際上是模型（21）中約束函數(shù)不等<19>Bc1ril 01 賃濠分配麗耐產

5、品朋頗的敷1甲乙可供盪ff麗A06匣科B_ 02- 10 J8ifti2 V30'<= 18634tttW I=$umproduct(c5d5,c9d9)=sumproduct(c6 d6.c9 d9)圖2.1式左端的值,我們知道給定決策變址的值，約束函數(shù)左端表示資源實際被使用的數(shù) 址.舉例來說對T第一個約束(原料人)的實際使用MS：原料人的實際使用tt=X!Xl+x2X0崔電子表格中這個公式在E5單元格中表示為E5=C5*C9+D5*D9(2.2)注意任E5單元格中輸入公式時要在英文狀態(tài)下輸入等號右側(包括等號)的 內容并且在數(shù)字和符號之間不能有空格(公式輸入時字母不區(qū)分大小寫

6、)。在式 (22)中有兩對相乘的數(shù)相加，實際上.Excel函數(shù)中有一個可以實現(xiàn)這一功能的 函數(shù)MsumproductM,它可以將2至30個大小形狀相同的單元格區(qū)域(每個單元格 區(qū)域用逗號隔開)中的對應數(shù)值型元素(非數(shù)值型元素及空單元格作0處理)相乘 后再相加這個函數(shù)是求解線性規(guī)劃問題中最常用的數(shù)學函數(shù)之一.例如，式 (22)用這個函數(shù)表達就是£5=sumproduct(C5 : D5,C9 J D9)利用Excel函數(shù)的復制功能，這個公式可以通過不同的單元格引用方法復制 到E6、E7和E8單元格中，免去fiM輸入公式的煩惱。因為是縱向復制,我們可 以把放有資源系數(shù)的單元恪區(qū)域相對引用

7、，把放有決策變址的單元格區(qū)域絕對引 用或混合引用，在這個例子中,E5單元格中的公式可以改成=sumproduct(C5 : D5,C$9 » D$9) 或=sumproduct(C5 D5» $C$9 $D$9)或=sumproduct($C5 » $D5,C$9：D$9)或=sumproduct($C5 » $D5,$C$9： $D$9)這樣,E5單元恪中的公式就可以通過拖動復制到E6、E7和E8單元格中。 從圖2.1中右側的小圖中我們可以看到E列各單元格中的公式，根據(jù)公式，我們不難看岀E8單元格中的值就是目標函數(shù)值.以圖2. 1這樣的格式把線性規(guī)劃模

8、型（2. 1）放到Excel工作表中后，我們可以 嘗試將一決策值輸入到C9和D9單元格中電子表格會立即在E5、E6和E7顯示 出資源的使用數(shù)域從而根據(jù)它們的值是否小于G列中對應行的數(shù)值來判斷這一 決策是否可行如果可行，還可以從E8單元格中的數(shù)據(jù)得知這一決策帶來的利潤 大小。住下一節(jié)中我們將利用Excel-規(guī)劃求解”工具迅速地找到模型的最優(yōu)解，不同的線性規(guī)劃問題在電子表恪中的布局方式是有一定區(qū)別和優(yōu)劣之分的， 本節(jié)在此只做個基本介紹我們還將通過以后各章節(jié)的例子詳細描述其他線性 規(guī)劃模型在Excel電子表格中布局的技巧.2.2用Excel規(guī)劃求解工具求解線性規(guī)劃模型Excel中有一個工具叫規(guī)劃求解

9、可以方便地求解線性規(guī)劃模型“規(guī)劃求解” 加載宏是Excel的一個可選加載模塊在安裝Excel時只有在選擇“定制安裝”或 完全安裝時才可以選擇裝入這個模塊。如果你現(xiàn)在的Excel窗口菜單欄的“工具” 菜單中沒“規(guī)劃求解”選項可以通過“工具”菜單的“加載宏”選項打開“加載宏”對 話框來添加“規(guī)劃求解"（見圖2.2）.圖2.2在應用規(guī)劃求解工具以帕要首先確認Excel電子表格中包括決策變赴、目 標函數(shù)、約束函數(shù)三種信息的單元格或單元格區(qū)域。圖2.1中的電子表格中就已經 有了這部分內容：決策變冊在C9和D9單元格中：目標函數(shù)的系數(shù)在第8行;約束 因數(shù)在第5、6和7行因為我們不知逍決策變址的值

10、是多少，所以就在決策變卅所 在的單元格中填上初始值“0”當然也可以什么都不填，系統(tǒng)會默認它為0,在求解 以后Excel會自動將它們替換成決策變址的最優(yōu)解下面我們接著上節(jié)的內容用 Excel規(guī)劃求解將第1章例1的資源分配問題解-遍。首先將要求解模型的所有相關信息和公式像圖2-1那樣填入電子表格中后 再選取“工具”、“規(guī)劃求解”命令后彈岀圖23所示的*規(guī)劃求解參數(shù)”對話框。圖2.3規(guī)劃求解參數(shù)”對話框的作用就是讓計算機知逍模型的毎個組成部分放在電 子表格的什么地方，我們可以通過鍵入單元格（或單元格區(qū)域）的地址或用鼠標在 電子表格相應的單元格（或單元格區(qū)域）單擊或拖動的辦法將有關信息加入到對話 框相

11、應的位置.下面我們分別對其中的選項略做解釋：1. 設K目標單元格在此文本框中應描定目標函數(shù)所在單元格的引用位論此目標單元格經求解 后快得某一持定數(shù)值、最大值或最小值。由此可見這個單元格必須包含公式.本 例中由目標函數(shù)在E8單元格所以輸入“E8”。輸入旨Excel會自動將其變?yōu)閳D2. 3所示的美元符號來固定這個地址.2. 等于在此指定是否需要對目標單元格求取最大值、最小值或某一指定數(shù)值。如果需 要辻目標函數(shù)為某一指定數(shù)值則要在右側編輯框中鍵入。本例是求目標函數(shù)最大 化所以選最大值.3.可變單元格可變單元格指定決策變址所在的各單元格、不含公式可以有多個區(qū)域或單元 格求解時其中的數(shù)值不斷調整直到滿足

12、約束條件，并且“設置目標單元格”編輯#框中指定的單元格達到目標值?？勺儐卧癖仨氁私踊蜷g接與目標單元格相聯(lián)系, 本例的決策變址在C9和D9兩個單元格中，所以在此鍵入“C9：D9”單元格引用區(qū) 域。4. 推測單擊此按鈕自動定位“設世目標單元格”編輯框中公式引用的所有非公式單 元格并在“可變單元格”編輯框中輸入其引用.5. 約束任此列出當前的所有約束條件。到此為止，找Y還未添加模別的任何約束條 件所以圖2. 3中沒有顯示。6添加顯示“添加約束”對話框（見圖2.4）。在添加約束對話框中有三個選項，其中1）單元格引用位置指定需要約束其中數(shù)據(jù)的單元格或單元格區(qū)域一般在此 處添加約束函數(shù)不等式左側的函數(shù)表

13、達式的單元恪或單元格區(qū)域.本例輸入 ME5 x E7”.2）約束值.在此指定對“單元格引用位置”編輯框中輸入的內容的限制條件, 即對于單元格引用及其約束條件，選定相應的需要添加或修改的關系運算符 （V=、=、 =、Int或Bin）,然后在右側的編輯框中輸入數(shù)字、單元格或區(qū)域引用 及公式等約束條件。本例輸入“G5 “G7"。3）添加。單擊此按鈕可以在不返回“規(guī)劃求解參數(shù)”對話框的情況下繼續(xù)添加 其他約束條件。由于我們已經把所有的約束都一次添加上了 所以只需單擊“確 定”按鈕回到“規(guī)劃求解參數(shù)”對話框（見圖2.5）,我們發(fā)現(xiàn)“約束”一欄中巳經顯 示了我們剛剛添加的約束。圖2. 4注意由于

14、本例所有的不等式都是“V = ”所以可以利用單元格引用區(qū)域一次 忝加否則要分幾次添加約束另外盡管我們在圖21的電子表格中F5 ： F7單 元格中有“V = ”符號但它們不參與計算只起提示作用所以必須在添加約束對23圖25 話框中進行選擇。7丸改單擊后顯示"改變約束”對話框（見圖2- 6）。從本質上說改變約束”對話框與 添加約束”對話框沒有區(qū)別它們的各個選項都是一樣的。圖2.68.刪除刪除選定的約束條件。顯示“規(guī)劃求解選項”對話框（見圖2.7）,在其中裝入或保存規(guī)劃求解模型并 對求解運算的高級屬性進行設定。本例中的模型是線性的而且所有變量都是非負 的，所以在選中“采用線性模型”和“假定

15、非負”兩個復選框本對話框的其他選項采 用默認值對于求解大多數(shù)線性規(guī)劃問題就足夠了本例也不例外.設置完選項后, 單擊“確定”按鈕返回到圖2. 5的“規(guī)劃求解參數(shù)”對話框。由于“規(guī)劃求解選項”對話框中其他的選項-般采用默認值就可以了 又因為 其中的一些設論涉及到后而章節(jié)中的知識.所以下面關尸該對話框其他選項的介 紹有興趣了解的同學可以看一下否則可以跳過這部分.1）嵐氏運算時間.在此設定求解過程的時間?？奢斎氲挠么笾禐?2767（秒）. 默認值100（秒）可以滿足大多數(shù)小型規(guī)劃求解要求。 # #時抄- .n-蔓入M2 I 対翩0I幣助01）1i OWOOl廠自動按比腳8放Q） 廠杲示迭代務果蟲計Q正

16、切函載 rr«Di«wOJnl ” h J *,0暇定車負圖2.72）迭代次數(shù)在此設定求解過程中迭代運算的次數(shù)限制求解過程的時間.可 輸入的最大值為32767，默認值100次可滿足大多數(shù)小型規(guī)劃求解要求.3）耕度。在此輸入用于控制求解精度的數(shù)字，以確定約束條件單元格中的數(shù) 值是否滿足目標值或上下限。梢度值必須為小數(shù)（0到1之間），輸入數(shù)字的小數(shù)位 越少精度越低。例如0. 0001比001精度高。4）允許誤差。在此輸入滿足整數(shù)約束條件的目標單元格求解結果與最佳結果 間的允許百分偏差.這個選項只應用于具有整數(shù)約束條件的問題。設置的允許誤 差值越大求解過程就越快.5）收斂度在此輸

17、入收斂度數(shù)值當朋近五次迭代時目標單元格中數(shù)值的變 化小丁“收斂度”編輯框中設遼的數(shù)值時“規(guī)劃求解”停止運行收斂度只應用于非 線性規(guī)劃問題并且必須由一個在0（寥）和1之間的小數(shù)表示。設理的數(shù)值越小， 收斂度就越高例如,0. 0001表示比001更小的相對差別.收斂度越小“規(guī)劃求 解"得到結果所需的時間就越長.6）采用線性模型當模型中的所有關系都是線性的，并且希里解決線性優(yōu)化 問題時選中此復選框可加速求解進程.7）顯示迭代結果.如杲選中此復選框，每進行一次迭代后都將中斷“規(guī)劃求 解”并顯示當前的迭代結果。8）自動按比例縮放。當輸入和輸出值數(shù)就差別很大時可以使用此功能。例 如對一項百萬羌元

18、投資的盈利百分比進行放大.9）假定非負。對尸在“添加約束”對話框的“約束值”編輯眶中沒有設賈下限的 可變單元格假定其下限為0（零）.10）估計。指定在每個一維搜索中用來得到基本變址初始估值的逼近方案。 正切函數(shù)。使用正切向址線性外推。次方程.用二次函數(shù)外推法,提高非線性規(guī)劃問題的計算精度。11）導數(shù)。指定用于估計目標函數(shù)和約束函數(shù)偏導數(shù)的差分方案.向前差分。用于大多數(shù)約束條件數(shù)值變化相對緩慢的問題。中心差分。用于約束條件變化迅速特別是接近限定值的問題.雖然此選項 要求更多的計算，但在“規(guī)劃求解"不能返回有效解時也許會有幫助.12）搜索。指定每次的迭代算法以確定搜索方向。牛頓法。用準牛

19、頓法迭代需耍的內存比共純法多但所需的迭代次數(shù)少。井輪法。比牛頓法需要的內存少，但要達到指定精度需要較多次的迭代運 n-當問題較大或內存有限或單步迭代進程序緩慢時，用此選項。13）裝入模型。顯示“裝入模型”對話框，輸入對所要調入模型的引用。14）保存模型。顯示“保存模型”對話框，輸入模型的保存位置。只有當需要在 工作表上保存多個模型時才單擊此命令。第一個模型會自動存儲。10. 關閉關閉對話框不進行規(guī)劃求解，但保留通過“選項”、“添加”、“更改”或“刪除”按 鈕所做的修改。11. 全部重設清除規(guī)劃求解中的當前設置將所有的設置恢復為初始值。12. 求解對定義好的冋題進行求解.單擊“求解'鍵后

20、，經過幾秒鐘的計算（小型問題）, 弼出“規(guī)劃求解結果”對話框（圖2.8）.圖2.8當規(guī)劃求解得到答案時“規(guī)劃求解結果”對話框中會給出下面兩條求解結果 信息：1）“規(guī)劃求解“找到-個解，可滿足所有的約束及盤優(yōu)化要求。這表明按照“規(guī) 劃求解選項”對話框中設世的精度所有約束條件都已満足并且只要有可能目標 單元格中將得到極大值或極小值。2）“規(guī)劃求解”收斂于當前結果，并滿足全部約束條件。這表明目標單元格中 的數(shù)值在最近五次求解過程中的變化量小于“規(guī)劃求解選項”對話框中*收斂度”設 直的值?！笆諗慷取敝性O置的值越小,“規(guī)劃求解”在計算時就會越精細，但求解過程 將花費更多的時間。當規(guī)劃求解不能得到最佳結果

21、時，在“規(guī)劃求解結果”對話框中就會顯示下述 信息：1滿足所有約束條件?！耙?guī)劃求解”不能進一步優(yōu)化結果。這表明僅得到近似 值，送代過程無法得到比顯示結果更精確的數(shù)值:或是無法進一步提高精度，或是 精度值設置得太小，請在“規(guī)劃求解選項”對話框中試著設置較大的精度值,再運行2）求解達到最長運算時間后停止。這表明在達到最長運算時間限制時，沒有 得到滿意的結果。如果要保存當前結果并節(jié)省下次計算的時間，請單擊“保存規(guī)劃 求解”或“保存方案”選項。3）求解達到最大迭代次數(shù)后停止。這表明在達到最大迭代次數(shù)時，沒有得到 滿意的結果。增加迭代次數(shù)也許有用,但是應該先檢査結果數(shù)值來確定冋題的原 因。如果要保存當前值

22、并節(jié)省下次計算的時間，請單擊“保存規(guī)劃求解''或“保存方 案”選項。4）目標單元格中數(shù)值不收斂。這表明即使?jié)M足全部約束條件，目標單元格數(shù) 值也只是有增有減但不收斂。這可能是在設置冋題時忽略了一項或多項約束條件。 請檢査工作表中的當前值，確定數(shù)值發(fā)散的原因，并且檢査約束條件，然后再次求 解。5）“規(guī)劃求解”未找到合適結果。這表明在滿足約束條件和精度要求的條件 下,“規(guī)劃求解”無法得到合理的結果，這可能是約束條件不一致所致。請檢査約束 條件仝式或類熨選擇是否有誤。6）“規(guī)劃求解”應用戶要求而中止。這表明在暫停求解過程之后，或在單步執(zhí) 行規(guī)劃求解時，單擊了“顯示中間結果”對話框中的“

23、停止”按鈕。7）無法滿足設定的“采用線性模型”條件。這表明求解時選中了“采用線性模 型”復選框，但是最后計算結果并不滿足線性模型。計算結果對工作表中的公式無 效。如果要驗證問題是否為非線性的,請選定“自動按比例縮放”復選框，然后再運 行一次。如果又一次岀現(xiàn)同樣信息，請清除“采用線性模型”復選框，再運行一次。8> “規(guī)劃求解”在目標或約束條件單元格中發(fā)現(xiàn)錯誤值。這表明在最近的一次 運算中一個或多個公式的運算結果有誤。請找到包含錯誤值的目標單元格或約束 條件單元格，修改其中的公式或內容，以得到合理的運算結果。還有可能是在“添加 約束”或“改變約束”對話框中鍵入了無效的名稱或公式,或者在“約束

24、”編輯框中直 接鍵入“integer”或“binary”。如果要將數(shù)值約束為皺數(shù)，請在比較操作符列表中 單擊“Int”。如果要將數(shù)值約束為二進制數(shù),請單擊“Bin”。9）內存不夠.Microsoft Excel無法獲得規(guī)劃求解所需的內存。請關閉一些文 件或應用程序再試一次。10）其他的Excel例程正在使用SOLVER.DLL。這表明有多個Microsoft Excel任務在運行，其中一個任務正在使用SOLVER. DLL .SOLVER. DLL同時只 能供一個任務使用。另外，在“規(guī)劃求解結果”對話框中還有四個選項，1）保存“規(guī)劃求解”結果。單擊此選項，接受求解結果，并將其輸入可變單元格 中

25、。2）恢復為原值.單擊此選項，在可變單元格中恢復初始值"3）報告.創(chuàng)建選定類型的報吿（有三個報吿可供選擇），并將每份報告存放到 工作簿中單獨的一電子表格上。運算結果報告。列岀目標單元格和可變單元格以及它們的初始值、最終結 果、約束條件和有關約束條件的信息。敏感性報告。在“規(guī)劃求解參數(shù)”對話框的“目標單元格”編輯框中所指定的 公式的徽小變化，以及約束條件的微小變化對求解結果都會有一定的影響。 此報吿提供關于求解結果對這些微小變化的敏感性的信息。含有整數(shù)約束 條件的模型不能生成本報告。對于非線性模型,此報告提供縮減拂度和拉格 朗日乘數(shù),對于線性模型，此報告中將包含縮減成本、影子價格（機會

26、成本）、 目標系數(shù)（允許有小世增減額）以及右側約束區(qū)域。關于影子價恪等的詳細 內容,后面章節(jié)中有論述。極限值報告。列出目標單元格和可變單元格以及它們的數(shù)值、上下限和目標 值。含有整數(shù)約束條件的模型不能生成本報吿。下限是在滿足約束條件和保 持其他可變單元格數(shù)值不變的情況下某個可變單元格可以取到的最小值。 上限是在這種情況下可以取到的最大值"4）保存方案.打開“保存方案”對話框,并通過Microsoft Excel的"方案管理 器”保存單元格數(shù)值。本例中，像圖2. 8吿訴我們“規(guī)劃求解”找到一個最優(yōu)解,可以滿足所有的約束 及目標的最大化要求，選中“保存規(guī)劃求解結果”單選框，然后

27、單擊確定犍,可以得 到求解的結果（見圖2.9）我們看到圖2.1中的C6和D6單元格中的“0”已經被圖2. 9中相應的單元格 內的最優(yōu)解“6”和“2”替代，根據(jù)這個最優(yōu)解,E8單元格中的最優(yōu)值“30”也計算了 出來。這些信息告訴我們，工廠應該安排生產甲產品6件,乙產品2件，能夠在有限 的資源限制下獲得最大的利潤30（百元九下面我們將第】章中的例2和例3用Excel按上面的方法解一遍，熟悉一下27aJ bwD1| f2 J3兩種產品所爲資原的數(shù)q甲乙可供資潦5原料A106 J66JR料B024 <=8T設備2312188單位利潤4AMi9l最優(yōu)解6圖2.9剛剛講過的規(guī)劃求解方法。我們回顧一下

28、第1章中例2的成本效益平衡問題，它的模型是minZ = 0. 84 + 0. 5x2xt>0 : = 1,2其中廠表示混合飼料中玉米的數(shù)雉亞表示混合飼料中紅曹的數(shù)址，目標是確定 xkx：的值使得成本Z = 08町+05x：最低。這個問題在Excel電子表格中的布局和求解選項設置以及求解結果見圖2. 10。 從最優(yōu)解中我們得知將1公斤玉米和3公斤紅薯配成的飼料是在滿足最低的 營養(yǎng)成分要求的悄況下達到成本最低的目標,成本是23（元）。G最低成分要求8:50.5uneodjct(CH>5CS9W) wnprodjctCCWX.CtMJW)破水化合 皈白質 壤他命AB鈕2021161 hl

29、Wt rr mod IT圖 2. 10第1章中例3的數(shù)學模型與前兩個例子有所不同，所以在電子表格中的布局 也有所區(qū)別，添力約束時也更復雜。如果單純地根據(jù)它的數(shù)學模型在電子表格中布 局，這個模型在Excel電子表格中可以按照前兩個例干那樣將所有變量放在同一 行上，但這樣會占很多列，又不好區(qū)分各變量的含義，所以我們以表13為基礎建 三張表。第一張表用來存放變址的系數(shù)（圖2.11中最上方的表其中D6 : F9單元格 區(qū)域中的數(shù)字就是單位運輸成本），在表示不存在的運輸方式的單元格中我們用一 個足夠大的數(shù)（這里用“1000"）來表示不存在或不允許運輸?shù)倪\輸費用，使計算機 不可能分配運輸量給這個

30、單元格。第二張表用來存放運輸能力的上限圖2.11中第二張的表，其中D16 : F19 單元格區(qū)域中的數(shù)字就是運輸上限），在沒有運輸限制的地方，我們也用一個足夠 大的數(shù)字（這里用“1000”,實際上由于最大的運輸量是100,所以“100”也足夠了， 用“1000”可以將這些位置同其他位置，特別是單元格中數(shù)字是100的位匱區(qū)分開 來。），使得所有可能的運輸量都能通過。第三張表用來存放決策變就（圖2.11中的最下面那個表，其中D26 : F29單 元格區(qū)域就是表示各種運輸方案的決策變量）。圖2. 11中的“規(guī)劃求解參數(shù)”對話框中的四個約束中第1個是容量約束，相當 于模型中的f M 80, f z W

31、 80, 了u M 80,兀5 W 90, f M 90后三個約束是半衡約束,其中第2個約束相當于模型中的f A + fz4 + f3k =+ f45第3個約束相當于/is + As + 幾=120幾 + A = 130第4個約束相當于/15 + fu = 100fv> + fa = 80/3< + As = 70面非負約束則在“規(guī)劃求解選項”對話框中的“假定非負”復選框中設置.從“規(guī)劃求解”結果知道，最優(yōu)運輸方案是:從工廠甲運30個單位的新產品到 倉庫4,從工廠乙運40個單位的新產品到倉庫3,從工廠甲、乙、丙分別運70.80. 30個單位的新產品到配送中心，再從配送中心向倉庫A和

32、倉庫B各運90個單位 的新產品?？梢砸? 1599的最低成本完成運輸。規(guī)劃求解婁設It目標單元格（D |$G$30 3M 等于：最大值e G量小值d） c -可夷韋元絡: 一一|gl:$F$29$D$26 $F$29 <= $D$16 $F$19$D$30 = $G$29JE$30 $F$30 二 $E$32 $F$32$G$28 $G$28 = SIS28 $IJ28=UM(D2&D29) «SUM(E26£29) »SUM(F26;F29)圖2112.3線性規(guī)劃問題的建模與應用舉例第丨貳和本貶的前面部分圍繞三個例了講解了線性規(guī)劃問題的圖解法和計算

33、 機求解方法為使讀者進一步了解線性規(guī)劃問題的建模與求解我們舉例如下. 31 例1農場灌溉問題。某公司有四個農場，每個農場的耕地作物需要用水灌溉，因灌溉條件限制，農 場的最大水資源供應址有一定限制，各農場的總耕地面積與最大水資源供應就如 表2. 1所示。該地區(qū)適合種植的農作物有棉花、玉米和高粱，三種農作物每種作物 每單位種植面積的凈收入和耗水雖以及每種作物最大允許種植面積如表2. 2所 示。由于水資源短缺公司需統(tǒng)一調配水資源，為了保持公正,規(guī)定每個農場受灌溉 而積占農場總耕地面積的比例相同公司管理層面臨的決策問題還是如何確定各 農場種植各種作物的面積使得在滿足以上各種限制的條件下，公司總收入最大

34、.表2.1農場耕地麵枳（畝）最大水資瀝供應雖（噸）I40006000260009000350005500445005000*2>2作物單位種植面積收入（元）單位面積耗水it（噸最大允許種植Off積（畝）棉花80026000X米6001-55500高集45015000解:我們首先建立此問題的線性規(guī)劃模型。由于此問題是決定四個農場中毎個 農場種植三種農作物的面積，我們引入決策變量“G=l,2,3,4；j=l,2,3表示第 i個農場種植第 > 種作物的面積，目標是使總收入Z = 800Cr“ 十龍21 十龍31+耳1）+ 600 （百2 + 工22 + 兀32 + *42）+ 450 （

35、2“ + 工幻十竝3 + m）饋大化，且滿足下列約束條件：1）農場的耕地面積約束，巾 + 62 十 x13 < 4000（農場 1）21 + 22 + ±23 C 6000（農場 2）久31 +力32 +乜3 W 5000（農場3）尢伙十莎“ +龍43 W 4500（農場4）2）農場最大供水董約束：2工“ + 1. 5xI2 十 x13 W 6000（農場 1）2n + 1. 5xw 卜兀3 < 9000（農場 2）2g + 1- 5x32 + 63 < 5500（農場 3）2x4i + 1.5x42 + g < 5000（農場 4）3）農作物的種植面積約束：

36、jrn十Xu + X31 +夂八M 6000（農作物I,棉花）x12 + X22 + 工32 + 工42 W 5500（農作物 2,玉米）xu +工旳十女$ < 5000（農作物3,高粱即各農作物種植面積不超過最大允許種植面積。4）種植作物面積占總耕地面積比例約束：工11 + % + S3 _ % + 玄22 4 帀34000 一 6000=21 + 匚訛十攵23 _ 乂3】+ 龍射+乞3360005000工31 + 乂32 七 Zj3 = % 十工42 + 乞435000=4500即各農場種植作物面積（灌溉面積）占總耕地面積的比例相同。5）決策變址的非負約束：xti > 0,1

37、= 1,2,3,4= 1,2,3現(xiàn)在我們用Excel電子表格求解以上何題，具體過程如下，將表2.1和表22的數(shù)據(jù)錄入到Excel電子表格中（見圖2.12）,在D5 » F8 單元格區(qū)域放置決策變量，目標單元格是G13。應用“規(guī)劃求解”后，我們得到一個 最優(yōu)解，由圖中陰彫部分的數(shù)據(jù)可知，農場1種植棉花1346.15畝、玉米203& 46 畝不種高粱;農場2種植棉花3923. 08畝、高粱115385畝，不種玉米農場3種 植玉米253& 46畝、髙粱1692. 31畝，不種棉花；農場*種植棉花730. 769畝、玉米 923. 077畝、高粱2153. 85畝?？色@總收入

38、1035萬元。例2證券投資問題。一證券投資者將1000萬元資金用于證券投資，已知各種證券G4、B、C、Z）、E、 F）的評級、到期年限、每年稅后收益如表23所示。*2.3證券名稱證券類別評級到期年限每年稅后牧益（）A地方債券2943n基金2124.4c國績153.2D國債143.0E地方債券433.2F基金544.535農場L PZE E nmnBnaraisnRnnainimne1鴛鑑4用河供1213合計55005500600I錦花薜3離梁I便用而積_«u嚴大允訐種fi積單位科枳收入用穴67509000S5005000<»<-45000.84615386000

39、 0.84615385000 0.84615381153.862538.46 1892.31923.0? 2153.85900056005000總收入00003384 6154 <-4000 0.84615385076 923】 4230 76923807 6923觀劃求*r參裁9=SUM(D5：D8) =SUM(E5：E8) -SUMCF5J8)5 | =<35/15 =SUMPRODUCT(D5：F5J>$ 12T$ 12)6 , =<56/16 =SUMPRODUCT(D6：F6J)$ 1 F$ 12)7 ； =G7/P =SUMPRODUCT(D7f7.D$ 1

40、2$ 12)8 | =G8/I8 =SUMPRODUCT(D8J8J)$ 1 ZFt 12)設置目標元祜©|$切3 AWT量小值®F元格謝|$M5$F$8Ctffl 2.12管理層對該投資者提出下列要求：1）國債投資額不能少于300萬元.2）投資證券的平均評級不超過1.5.3）投資證券的平均到期年限不超過5年。問：每種證券投資多少可以使得稅后收益最大？解：引入決策變懂分別表示證券£、B、C、D、E、F的投資 金額（單位：萬元）相應的目標函數(shù)（稅后收益）為：Z = 9 X 0. 043心 + 】2 X 0 04 4心 + 5 X 0. 032xc 4-4X0. 03

41、“ 4-3X0. 032在 + 4 X0. 045c約束條件為：1）資金總額約束：工人+才+壯+工°+竝+工fW 10002）國債投資額約束：工+才°23003）證券平均評級約束： # 2xa + 2x/ + m 十 4x£ + 5c / . r:；;W bXa + 工3 + " + Hq + Ze + 工尸這是一個非線性約束，很容易轉化為以下線性約束：0. 5對+0. 5心0. 5xc0* 5工°+2 5孔+3 5qW04）證券平均到期年限約束：9心 + 12心 + 5氐 + 4才° + 3壯 + 4“ < §才人

42、+ 丁8 + 光 + HQ + 工&十力尸7它等價于線性約束：4X4 + 7心 2xe Q W05）非負約束：0 *xb0»xc0.xd0»X£0，工尸玄0用Excel電子表格求解以上問題過程如下：將表2. 3中的數(shù)據(jù)錄入電子表格中（見圖2. 13） 在D5 : D10單元格區(qū)域放¥ 決策變址，目標單元格是G11。應用“規(guī)劃求解”后我們得到一個最優(yōu)解，由圖中P 彫部分的數(shù)據(jù)可知.x = 200%xw = 0xc 0%xd=725xe=0%xf = 75» 也就是說 E 投資者只選擇A.D和F證券進行投資投資額分別是200.725和75萬

43、元，可獲彳!.A 丨 IJ證券投資甸JS2A BCDEF地方偵券基工72S3000072507510001000評級到明茸險每年稅后收益94 30%2124.40%I53 20%143 00%43 |3 20%544 50%0<ss<ssa評級釣果年限約東目標 37 RgjiT于r «>MI mg元格 2 n=D7+D8 =SUM(D5Q 10) =SUMPR0DUCT(E5£10$D5：$D 10)-1.5*SUM($D5：$D 10)F1 11 SUMPRODUCTQflORHDlORStJMODStl|$D$5 $D$1CQD ' T tc$

44、13 SD$ti：mtl <S SD$13 $F$13圖 2. 13 # 呆大的稅后收益177.9萬元。因為有兩個非線性約束變?yōu)榫€性約束，使得在電子表格中相關的數(shù)據(jù)不夠直 觀，清仔細體會電子表格中公式和約束的意義,想想它忙與上文總結的各約束之間 的關系。例3話務員排班問題"某尋呼公司雇用了多名話務員工作，他們每天工作3節(jié)，每節(jié)3小時,每節(jié)開 始時間為午夜、凌.晨3點鐘、凌晨6點鐘，上午9點、中午12點、下午3點、6點、9 點,為方便話務員上下班管理層安排每位話務員每天邊連續(xù)工作3節(jié)，根據(jù)調査, 對丁不同的時間，由于業(yè)務就不同，需要的話務員的人數(shù)也不相同,公司付的薪水 也不相同，有關數(shù)據(jù)見表2. 4。I：作時何03點36點69點 9-12點）2-15點1518點1821點210點最低需i8求人數(shù)（人）152518薪水（元）2630282039 # 冋：如何安排話務員才能保證服務人數(shù)又使總成本帰低？解;這個問題實際上是一個成本效益平衡問題。管理層在向客戶提供滿盤服務 水平的同時要控制成本,因此必須尋找成本與效益的平術。由于毎節(jié)工作時間為3 小時一天被分為8班每人連續(xù)工作3節(jié)，各班時間安排如下（見表2. 5）。衰25時段班次234567最低需求人數(shù)36972匕515