EXCEL規(guī)劃求解案例分析PPT通用課件

1、目標規(guī)劃問題及其數(shù)學模型？目標規(guī)劃問題及其數(shù)學模型？ 目標規(guī)劃問題的提出目標規(guī)劃問題的提出例1 某工廠生產兩種產品，受到原材料供應和設備工時的限制。在單件利潤等有關數(shù)據已知的條件下，要求制定一個獲利最大的生產計劃。具體數(shù)據如下： 產品 I II 限量原材料(kg/件) 5 10 60 設備工時(h/件) 4 4 40利潤(元/件) 6 8問該公司應制造兩種家電各多少件，使獲取的利潤為最大。 max Z= 6x1 +8x2 5x1 + 10 x2 60 4x1 + 4x2 40 x1 , x2 0st.設產品I和II的產量分別為x1和x2，其數(shù)學模型為：其最優(yōu)解，即最優(yōu)生產計劃為x18件，x22

2、件，max Z64元。 例2 假設在前面例子中，計劃人員被要求考慮如下意見： (1)由于產品II銷售疲軟，故希望產品II的產量不超過產品I 的一半； (2)原材料嚴重短缺，生產中應避免過量消耗； (3)最好能節(jié)約4h設備工時； (4)計劃利潤不少于48元。 類似這樣的多目標決策問題是典型的目標規(guī)劃問題。運用運用EXCEL求解線性規(guī)劃問題求解線性規(guī)劃問題outline1.關于關于“規(guī)劃求解規(guī)劃求解”2.如何加載如何加載“規(guī)劃求解規(guī)劃求解”3. “規(guī)劃求解規(guī)劃求解”各參數(shù)設置各參數(shù)設置4. “規(guī)劃求解規(guī)劃求解”步驟步驟5. 敏感性分析敏感性分析1. 關于關于“規(guī)劃求解規(guī)劃求解”Microsoft

3、Excel的“規(guī)劃求解”工具取自德克薩斯大學奧斯汀分校的Leon Lasdon和克里夫蘭州立大學的Allan Waren共同開發(fā)的Generalized Reduced Gradient(GRG2)非線性最優(yōu)化代碼. 線性和整數(shù)規(guī)劃問題取自Frontline Systems公司的John Waston和Dan Fylstra提供的有界變量單純形法和分支定界法 在計劃管理中常會遇到：人力資源的調在計劃管理中常會遇到：人力資源的調度、產品生產的安排、運輸線路的規(guī)劃、生度、產品生產的安排、運輸線路的規(guī)劃、生產材料的搭配、采購批次的確定等問題。產材料的搭配、采購批次的確定等問題。 這類問題有一個共同點

4、，即需要解決：這類問題有一個共同點，即需要解決：如何合理利用各種存在約束的資源，而獲得如何合理利用各種存在約束的資源，而獲得最佳的經濟效益最佳的經濟效益，也就是達到利潤最大、成，也就是達到利潤最大、成本最低等目標。這就是本本最低等目標。這就是本節(jié)節(jié)要解決的要解決的“在約在約束條件下尋求目標函數(shù)最優(yōu)解的規(guī)劃問題束條件下尋求目標函數(shù)最優(yōu)解的規(guī)劃問題”。1 1、約束條件的表達、約束條件的表達2 2、目標的數(shù)學描述、目標的數(shù)學描述3 3、應用、應用ExcelExcel的規(guī)劃的規(guī)劃求解工具對問題求解求解工具對問題求解 一般來講，規(guī)劃問題都具有如下特點：一般來講，規(guī)劃問題都具有如下特點：1.1. 所求問題

5、都有單一的目標（如求生產的最低所求問題都有單一的目標（如求生產的最低成本，求運輸?shù)淖罴崖肪€，求產品的最大盈成本，求運輸?shù)淖罴崖肪€，求產品的最大盈利，求產品周期的最短時間），要求求目標利，求產品周期的最短時間），要求求目標函數(shù)的最優(yōu)解。函數(shù)的最優(yōu)解。2.2. 對于問題涉及的對象（如路程、原材料等）對于問題涉及的對象（如路程、原材料等）存在有明確的可以用不等式表達約束條件。存在有明確的可以用不等式表達約束條件。3.3. 問題的表達可以描述為：一組約束條件（不問題的表達可以描述為：一組約束條件（不等式），和一個目標方程。等式），和一個目標方程。4.4. 利用利用ExcelExcel技術可以簡單的求得

6、問題滿足約束技術可以簡單的求得問題滿足約束條件求的目標最優(yōu)解。條件求的目標最優(yōu)解。規(guī)劃問題的特點（共性）2.如何加載如何加載“規(guī)劃求解規(guī)劃求解”1) 在“工具”菜單上,單擊“加載宏”2) 在彈出的對話框中的“可用加載宏”列表框中,選定待添加的加載宏“規(guī)劃求解”選項旁的復選框,然后單擊“確定”.單擊“確定”后,“工具”菜單下就會出現(xiàn)一項“規(guī)劃求解”3. “規(guī)劃求解規(guī)劃求解”各參數(shù)設置各參數(shù)設置單擊“規(guī)劃求解”按鈕,將會出現(xiàn)以下規(guī)劃求解參數(shù)設置對話框 單擊“添加”,顯示添加約束對話框 選項:顯示”規(guī)劃求解選項”對話框.在其中可以加載或保存規(guī)劃求解模型,并對規(guī)劃求解過程的高級屬性進行控制4. “規(guī)劃

7、求解規(guī)劃求解”步驟步驟例例1. 雅致家具廠生產計劃優(yōu)化問題雅致家具廠生產計劃優(yōu)化問題雅致家具廠生產4種小型家具，由于該四種家具具有不同的大小、形狀、重量和風格，所以它們所需要的主要原料（木材和玻璃）、制作時間、最大銷售量與利潤均不相同。該廠每天可提供的木材、玻璃和工人勞動時間分別為600單位、1000單位與400小時，詳細的數(shù)據資料見下表。應如何安排這四種家具的日產量，使得該廠的日利潤最大？表1 雅致家具廠基本數(shù)據家 具 類 型 1 2 3 4可提供量勞動時間（小時/件） 2 1 3 2 400小時木材（單位/件） 4 2 1 2 600單位單位玻璃（單位/件） 6 2 1 2 1000單位單

8、位單位利潤（元/件） 60 20 40 30 最大銷售量（件） 100 200 50 100解：依題意，設置四種家具的日產量分別為決策變量1234,x x x x約束條件為三種資源的供應量限制和產品銷售量限制。據此，列出下面的線性規(guī)劃模型：，目標要求是日利潤最大化，12341234123412341234123460204030422600622100021324001001. .20025031004,0MaxZxxxxxxxxxxxxxxxxxs txxxx x x x （木木材材約約束束）（玻玻璃璃約約束束）（勞勞動動時時間間約約束束）（家家具具 需需求求量量約約束束）（家家具具 需需求

9、求量量約約束束）（家家具具 需需求求量量約約束束）（家家具具 需需求求量量約約束束）（非非負負約約束束）其中1234,x x x x分別為四種家具的日產量。SUMPRODUCT函數(shù)函數(shù) SUMPRODUCT的意思是:乘積之和乘積之和 在給定的幾組數(shù)組中，將數(shù)組間對應的元素相乘，并返回乘積之和。 語法語法 SUMPRODUCT（array1,array2,array3, .） Array1,array2,array3, . 為 2 到 30 個數(shù)組，其相應元素需要進行相乘并求和。下面介紹用下面介紹用Excel中的中的“規(guī)劃求解規(guī)劃求解”功能求此題。功能求此題。第一步第一步 在在Excel中描述問

10、題、建立模型，如下圖所示。中描述問題、建立模型，如下圖所示。=SUMPRODUCT(B6:E6,$B$15:$E$15)第二步第二步 在在“工具工具”菜單中選擇菜單中選擇“規(guī)劃求解規(guī)劃求解”。第三步第三步 在在“規(guī)劃求解參數(shù)規(guī)劃求解參數(shù)”對話框進行選擇如下圖。對話框進行選擇如下圖。第四步第四步 點擊點擊“選項選項”按鈕，彈出按鈕，彈出“規(guī)劃求解選項規(guī)劃求解選項”對話框。對話框。第五步第五步 選擇選擇“采用線性模型采用線性模型”和和“假定非負假定非負”，單擊單擊“確定確定”，返回下圖。單擊，返回下圖。單擊“求解求解”，即可解，即可解決此題。決此題。最后結果如下頁圖所示。最后結果如下頁圖所示。用E

11、xcel求解得對應的敏感性報告（靈敏度分）析如下表所示。最優(yōu)解c+ c-cb對偶最優(yōu)解實際使用量+ b- b遞減成本指目標函數(shù)中決策變量的系數(shù)必須改進多少才能得到該決策變量的正數(shù)解，改進對最大值為增加，對最小值為減少。 在實際問題中，規(guī)劃模型中的大多數(shù)數(shù)在實際問題中，規(guī)劃模型中的大多數(shù)數(shù)據是測量、統(tǒng)計、評估或決策而得出來的。據是測量、統(tǒng)計、評估或決策而得出來的。因此有必要分析當這些數(shù)據發(fā)生波動時會因此有必要分析當這些數(shù)據發(fā)生波動時會對最優(yōu)解和最優(yōu)值產生什么影響。這就是對最優(yōu)解和最優(yōu)值產生什么影響。這就是靈敏度分析。靈敏度分析。5. 敏感性分析敏感性分析出現(xiàn)以下假設，上述案例如何決策？出現(xiàn)以下假

12、設，上述案例如何決策？? ? （2）家具廠是否愿意出）家具廠是否愿意出10元的加班費，讓元的加班費，讓某工人加班某工人加班1小時？小時？ （3）如果可提供的工人勞動時間變?yōu)椋┤绻商峁┑墓と藙趧訒r間變?yōu)?98小時，該廠的日利潤有何變化？小時，該廠的日利潤有何變化？ （4）該廠應優(yōu)先考慮購買何種資源？）該廠應優(yōu)先考慮購買何種資源？ （5）若因市場變化，第一種家具的單位利）若因市場變化，第一種家具的單位利潤從潤從60元下降到元下降到55元，問該廠的生產計劃元，問該廠的生產計劃及日利潤將如何變化？及日利潤將如何變化？本問題的敏感性報告如上頁表所示。由上述敏感性報告可進行靈敏度分析，并回答題目中的問題由上述敏感性報告可進行靈敏度分析，并回答題目中的問題(2)一一(5)。(2)由敏感性報告可知，勞動時間的影子價格為12元，即在勞動時