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

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

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

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

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

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

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

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

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

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

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

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

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