EXCEL求解線性規(guī)劃問(wèn)題PPT課件

1、 一、一、EXCEL 基本知識(shí)基本知識(shí) 1、命名工作表、命名工作表 功能：功能： 存儲(chǔ)信息、進(jìn)行計(jì)算、排序數(shù)據(jù)、用圖或表的形存儲(chǔ)信息、進(jìn)行計(jì)算、排序數(shù)據(jù)、用圖或表的形式顯示數(shù)據(jù)、規(guī)劃求解、財(cái)會(huì)分析、概率與統(tǒng)計(jì)分析等式顯示數(shù)據(jù)、規(guī)劃求解、財(cái)會(huì)分析、概率與統(tǒng)計(jì)分析等等等（1）激活工作表）激活工作表1，單擊，單擊sheet 1 標(biāo)簽標(biāo)簽（2）命名工作表）命名工作表 或或 （3）Format / sheet Rename 顯示顯示Rename sheet 對(duì)話框?qū)υ捒颍?）鍵入工作表名）鍵入工作表名第1頁(yè)/共43頁(yè) 2、工作表、工作表 4、進(jìn)行計(jì)算、進(jìn)行計(jì)算 Excel-2003 256列列 6553

2、5 行行Excel-2007 16384列列 1048576行行 3、向單元格中輸入數(shù)據(jù)、向單元格中輸入數(shù)據(jù)常數(shù)：不能改變的文字或其它數(shù)據(jù)常數(shù)：不能改變的文字或其它數(shù)據(jù)公式：輸入到單元格中的數(shù)字值，或返回?cái)?shù)字值的表達(dá)式。公式：輸入到單元格中的數(shù)字值，或返回?cái)?shù)字值的表達(dá)式。（1）輸出數(shù)據(jù)時(shí)計(jì)算）輸出數(shù)據(jù)時(shí)計(jì)算A1 =456+789（2）根據(jù)其它單元格的數(shù)據(jù)進(jìn)行計(jì)算）根據(jù)其它單元格的數(shù)據(jù)進(jìn)行計(jì)算A3456 B3-789 C3=A3+B3第2頁(yè)/共43頁(yè) 5、在公式中引用其它單元格、在公式中引用其它單元格 在公式中可以引用本工作簿中任何單元格或單元格在公式中可以引用本工作簿中任何單元格或單元格組的數(shù)

3、組的數(shù) 據(jù)，也可以引用其它工作簿中任何單元格或據(jù)，也可以引用其它工作簿中任何單元格或單元格組的數(shù)據(jù)。引用單元格數(shù)據(jù)后，公式的運(yùn)算單元格組的數(shù)據(jù)。引用單元格數(shù)據(jù)后，公式的運(yùn)算值將隨著被引用的單元格數(shù)據(jù)變化而變化。值將隨著被引用的單元格數(shù)據(jù)變化而變化。（1）引用的類(lèi)型）引用的類(lèi)型三種類(lèi)型三種類(lèi)型 ：相對(duì)引用、相對(duì)引用、 絕對(duì)引用、混合引用絕對(duì)引用、混合引用第3頁(yè)/共43頁(yè)格式：格式： A3 、B6（2） 相對(duì)引用相對(duì)引用 使用相對(duì)引用后，系統(tǒng)將會(huì)記住建立公式的單元格和被使用相對(duì)引用后，系統(tǒng)將會(huì)記住建立公式的單元格和被引用的單元格的相對(duì)位置，在復(fù)制這個(gè)公式時(shí)，新的公式單引用的單元格的相對(duì)位置，在復(fù)制

4、這個(gè)公式時(shí)，新的公式單元和被引用的單元仍然保持這種相對(duì)位置。元和被引用的單元仍然保持這種相對(duì)位置。第4頁(yè)/共43頁(yè)（3）絕對(duì)引用）絕對(duì)引用 絕對(duì)引用是指被引用的單元與引用的公式單元的位置絕對(duì)引用是指被引用的單元與引用的公式單元的位置關(guān)系是絕對(duì)的，無(wú)論將這個(gè)公式復(fù)制到任何單元，公式所關(guān)系是絕對(duì)的，無(wú)論將這個(gè)公式復(fù)制到任何單元，公式所引用的還是原來(lái)單元格的數(shù)據(jù)。引用的還是原來(lái)單元格的數(shù)據(jù)。（4）混合引用）混合引用 格式：格式：$a$3 $d$5格式：格式： $A3 列是絕對(duì)的，行是相對(duì)的列是絕對(duì)的，行是相對(duì)的 B$ 3 列是相對(duì)的，行是絕對(duì)的列是相對(duì)的，行是絕對(duì)的第5頁(yè)/共43頁(yè)使用Excel進(jìn)行

5、求解1.關(guān)于“規(guī)劃求解”2.如何加載“規(guī)劃求解”3. “規(guī)劃求解”各參數(shù)設(shè)置4. “規(guī)劃求解”步驟5. 利用“規(guī)劃求解”解線性規(guī)劃問(wèn)題第6頁(yè)/共43頁(yè)2.如何加載“規(guī)劃求解”1) 在“工具”菜單上,單擊“加載宏”第7頁(yè)/共43頁(yè)2) 在彈出的對(duì)話框中的“可用加載宏”列表框中,選定待添加的加載宏“規(guī)劃求解”選項(xiàng)旁的復(fù)選框,然后單擊“確定”.單擊“確定”后,“工具”菜單下就會(huì)出現(xiàn)一項(xiàng)“規(guī)劃求解”第8頁(yè)/共43頁(yè)3. “規(guī)劃求解”各參數(shù)設(shè)置單擊“規(guī)劃求解”按鈕,將會(huì)出現(xiàn)以下規(guī)劃求解參數(shù)設(shè)置對(duì)話框第9頁(yè)/共43頁(yè) 單擊“添加”,顯示添加約束對(duì)話框第10頁(yè)/共43頁(yè)第11頁(yè)/共43頁(yè) 選項(xiàng):顯示”規(guī)劃求

6、解選項(xiàng)”對(duì)話框.在其中可以加載或保存規(guī)劃求解模型,并對(duì)規(guī)劃求解過(guò)程的高級(jí)屬性進(jìn)行控制第12頁(yè)/共43頁(yè)線性規(guī)劃求解步驟：1. 確定目標(biāo)函數(shù)系數(shù)存放單元格，并在這些單元格中輸入目標(biāo)確定目標(biāo)函數(shù)系數(shù)存放單元格，并在這些單元格中輸入目標(biāo)函數(shù)系數(shù)。函數(shù)系數(shù)。2. 確定決策變量存放單元格，并任意輸入一組數(shù)據(jù)；確定決策變量存放單元格，并任意輸入一組數(shù)據(jù)；3. 確定約束條件中左端項(xiàng)系數(shù)存放單元格，并輸入約束條件左確定約束條件中左端項(xiàng)系數(shù)存放單元格，并輸入約束條件左端項(xiàng)系數(shù)；端項(xiàng)系數(shù)；4. 在約束條件左端項(xiàng)系數(shù)存放單元格右邊的單元格中輸入約束在約束條件左端項(xiàng)系數(shù)存放單元格右邊的單元格中輸入約束條件左端項(xiàng)的計(jì)算

7、公式，計(jì)算出約束條件左端項(xiàng)對(duì)應(yīng)于目前決條件左端項(xiàng)的計(jì)算公式，計(jì)算出約束條件左端項(xiàng)對(duì)應(yīng)于目前決策變量的函數(shù)值。策變量的函數(shù)值。5. 在步驟在步驟4的的數(shù)據(jù)右邊輸入約束條件中右端項(xiàng)（即常數(shù)項(xiàng)）的的數(shù)據(jù)右邊輸入約束條件中右端項(xiàng)（即常數(shù)項(xiàng)）6. 確定目標(biāo)函數(shù)值存放單元格，并在該單元格中輸入目標(biāo)函數(shù)確定目標(biāo)函數(shù)值存放單元格，并在該單元格中輸入目標(biāo)函數(shù)值電容計(jì)算公式。值電容計(jì)算公式。第13頁(yè)/共43頁(yè)一個(gè)簡(jiǎn)單的例子 某工廠計(jì)劃生產(chǎn)兩種產(chǎn)品，利潤(rùn)分別為2和3，已知生產(chǎn)單位產(chǎn)品所需的設(shè)備臺(tái)時(shí)和A、B兩種原材料的消耗，如表產(chǎn)品產(chǎn)品1產(chǎn)品產(chǎn)品2設(shè)備設(shè)備128臺(tái)時(shí)臺(tái)時(shí)原材料原材料A4016KG原材料原材料B0412

8、KG 目標(biāo)是不超過(guò)資源限制的情況下，確定兩產(chǎn)品產(chǎn)量，得到最大利潤(rùn)。第14頁(yè)/共43頁(yè)建立數(shù)學(xué)公式（步驟一） 在工作表的頂部輸入數(shù)在工作表的頂部輸入數(shù)據(jù)據(jù) 確定每個(gè)決策變量所對(duì)確定每個(gè)決策變量所對(duì)應(yīng)的單元格位置應(yīng)的單元格位置 選擇單元格輸入公式，選擇單元格輸入公式，找到目標(biāo)函數(shù)的值找到目標(biāo)函數(shù)的值 確定約束單元格輸入公確定約束單元格輸入公式，計(jì)算每個(gè)約束條件式，計(jì)算每個(gè)約束條件左邊的值左邊的值 確定約束單元格輸入公確定約束單元格輸入公式，計(jì)算每個(gè)約束條件式，計(jì)算每個(gè)約束條件右邊的值右邊的值可采用可采用 復(fù)制粘貼復(fù)制粘貼 或或 直直接輸入接輸入 的方式導(dǎo)入數(shù)據(jù)。的方式導(dǎo)入數(shù)據(jù)。第15頁(yè)/共43頁(yè)建

9、立數(shù)學(xué)公式（步驟二） 在工作表的頂部輸入數(shù)在工作表的頂部輸入數(shù)據(jù)據(jù) 確定每個(gè)決策變量所對(duì)確定每個(gè)決策變量所對(duì)應(yīng)的單元格位置應(yīng)的單元格位置 選擇單元格輸入公式，選擇單元格輸入公式，找到目標(biāo)函數(shù)的值找到目標(biāo)函數(shù)的值 選擇一個(gè)單元格輸入公選擇一個(gè)單元格輸入公式，計(jì)算每個(gè)約束條件式，計(jì)算每個(gè)約束條件左邊的值左邊的值 選擇一個(gè)單元格輸入公選擇一個(gè)單元格輸入公式，計(jì)算每個(gè)約束條件式，計(jì)算每個(gè)約束條件右邊的值右邊的值圖中，規(guī)定B12、C12為可變單元格可變單元格存放決策變量的取值，可變單元格數(shù)目等于決策變量個(gè)數(shù)可變單元格存放決策變量的取值，可變單元格數(shù)目等于決策變量個(gè)數(shù)第16頁(yè)/共43頁(yè)建立數(shù)學(xué)公式（步驟三

10、） 在工作表的頂部輸入數(shù)在工作表的頂部輸入數(shù)據(jù)據(jù) 確定每個(gè)決策變量所對(duì)確定每個(gè)決策變量所對(duì)應(yīng)的單元格位置應(yīng)的單元格位置 選擇單元格輸入公式，選擇單元格輸入公式，找到目標(biāo)函數(shù)的值找到目標(biāo)函數(shù)的值 確定約束單元格輸入公確定約束單元格輸入公式，計(jì)算每個(gè)約束條件式，計(jì)算每個(gè)約束條件左邊的值左邊的值 確定約束單元格輸入公確定約束單元格輸入公式，計(jì)算每個(gè)約束條件式，計(jì)算每個(gè)約束條件右邊的值右邊的值在目標(biāo)單元格中，需要填入計(jì)算目標(biāo)函數(shù)值的公式。在目標(biāo)單元格中，需要填入計(jì)算目標(biāo)函數(shù)值的公式。第17頁(yè)/共43頁(yè)建立數(shù)學(xué)公式（步驟四） 在工作表的頂部輸入數(shù)在工作表的頂部輸入數(shù)據(jù)據(jù) 確定每個(gè)決策變量所對(duì)確定每個(gè)決策

11、變量所對(duì)應(yīng)的單元格位置應(yīng)的單元格位置 選擇單元格輸入公式，選擇單元格輸入公式，找到目標(biāo)函數(shù)的值找到目標(biāo)函數(shù)的值 確定約束單元格輸入公確定約束單元格輸入公式，計(jì)算每個(gè)約束條件式，計(jì)算每個(gè)約束條件左邊的值左邊的值 確定約束單元格輸入公確定約束單元格輸入公式，計(jì)算每個(gè)約束條件式，計(jì)算每個(gè)約束條件右邊的值右邊的值在約束單元格中，需要填入計(jì)算約束函數(shù)值的公式。在約束單元格中，需要填入計(jì)算約束函數(shù)值的公式。第18頁(yè)/共43頁(yè)建立數(shù)學(xué)公式（步驟五） 在工作表的頂部輸入數(shù)在工作表的頂部輸入數(shù)據(jù)據(jù) 確定每個(gè)決策變量所對(duì)確定每個(gè)決策變量所對(duì)應(yīng)的單元格位置應(yīng)的單元格位置 選擇單元格輸入公式，選擇單元格輸入公式，找到

12、目標(biāo)函數(shù)的值找到目標(biāo)函數(shù)的值 確定約束單元格輸入公確定約束單元格輸入公式，計(jì)算每個(gè)約束條件式，計(jì)算每個(gè)約束條件左邊的值左邊的值 確定約束單元格輸入公確定約束單元格輸入公式，計(jì)算每個(gè)約束條件式，計(jì)算每個(gè)約束條件右邊的值右邊的值第19頁(yè)/共43頁(yè)調(diào)用 規(guī)劃求解 模塊 選擇工具下拉菜單 選擇規(guī)劃求解選項(xiàng)（事先需用Office安裝盤(pán)安裝規(guī)劃求解的功能）第20頁(yè)/共43頁(yè)填寫(xiě)目標(biāo)單元格和可變單元格 出現(xiàn)規(guī)劃求解參數(shù)對(duì)話框 在在目標(biāo)單元格目標(biāo)單元格中輸入中輸入B14B14 在在等于等于選擇最大選擇最大 在在可變單元格可變單元格中輸入中輸入B12:C12B12:C12 選擇選擇添加添加在上圖顯示的界面中，需

13、要輸入目標(biāo)單元格、可變單元格，添加約束條件，另外還可能需要進(jìn)行選項(xiàng)設(shè)置。第21頁(yè)/共43頁(yè)添加約束 在添加約束對(duì)話框中，在單元格引用位置中輸入B17，選擇=，在約束值中輸入D17。選擇添加 第三個(gè)條件添加完畢后，選擇確定 當(dāng)規(guī)劃求解參數(shù)對(duì)話框重新出現(xiàn)時(shí)，選擇選項(xiàng)第22頁(yè)/共43頁(yè)“選項(xiàng)”設(shè)置 當(dāng)選項(xiàng)對(duì)話框出現(xiàn)時(shí)，選擇假設(shè)非負(fù)。選擇確定第23頁(yè)/共43頁(yè)用Excel求解 出現(xiàn)規(guī)劃求解參數(shù)對(duì)話框，選擇求解。第24頁(yè)/共43頁(yè)保存求解結(jié)果 當(dāng)求解結(jié)果對(duì)話框出現(xiàn)時(shí)，選擇保存規(guī)劃求解結(jié)果。選擇確定。第25頁(yè)/共43頁(yè)運(yùn)算結(jié)果報(bào)告列出目標(biāo)單元格和可變單元格以及它們的初始值、最終結(jié)果、約束條件和有關(guān)約束條件

14、的信息。初值和終值分別指單元格在本次求解前的數(shù)值和求解后的數(shù)值。第26頁(yè)/共43頁(yè)敏感性分析報(bào)告（1）可變單元格中 “單元格”指決策變量所在單元格的地址 “名字”是決策變量的名稱 “終值”是決策變量的終值,即最優(yōu)值 “遞減成本”指最優(yōu)解中等于0的變量，對(duì)應(yīng)的目標(biāo)函數(shù)中的系數(shù)增加或減少多少，最優(yōu)解不再為0 “目標(biāo)式系數(shù)”目標(biāo)函數(shù)中的系數(shù)，為已知條件 “允許的增量”與“允許的減量”表示目標(biāo)函數(shù)中的系數(shù)在增量與減量的變化范圍內(nèi)變化時(shí)，最優(yōu)解保持不變（注：最優(yōu)值發(fā)生變化）第27頁(yè)/共43頁(yè)敏感性分析報(bào)告（2）約束單元格 “單元格”指約束條件左邊所在單元格的地址 “名字”是約束條件左邊的名稱 “終值”是

15、約束條件左邊的值 “陰影價(jià)格”指約束條件右邊增加或減少一個(gè)單位，目標(biāo)函數(shù)值增加或減少的數(shù)量 “約束的限制值”指約束條件右邊的值，為已知條件 “允許的增量，減量”表示約束條件右邊在允許的范圍內(nèi)變化時(shí)，影子價(jià)格不變，即約束條件右邊的值每增加1個(gè)單位，目標(biāo)函數(shù)值的增加仍然為影子價(jià)格的大小。因此，右端項(xiàng)在一定范圍內(nèi)變化時(shí)，影子價(jià)格不變，目標(biāo)函數(shù)值的變動(dòng)等于右端項(xiàng)變動(dòng)值乘以影子價(jià)格第28頁(yè)/共43頁(yè)極限值報(bào)告解釋列出目標(biāo)單元格和可變單元格以及它們的數(shù)值、上下限和目標(biāo)值。含有整數(shù)約束條件的模型不能生成本報(bào)告。其中，下限是在滿足約束條件和保持其它可變單元格數(shù)值不變的情況下，某個(gè)可變單元格可以取到的最小值。上

16、限是在這種情況下可以取到的最大值。 第29頁(yè)/共43頁(yè)延伸下面對(duì)目標(biāo)系數(shù)同時(shí)變動(dòng)以及約束右端值同時(shí)變動(dòng)的情況分別作延伸。（1）目標(biāo)系數(shù)c同時(shí)變動(dòng)的百分之百法則： 如果目標(biāo)函數(shù)系數(shù)同時(shí)變動(dòng)，計(jì)算出每一系數(shù)變動(dòng)量占該系數(shù)同方向可容許變動(dòng)范圍的百分比，而后將各個(gè)系數(shù)的變動(dòng)百分比相加，如果所得的和不超過(guò)百分之一百，最優(yōu)解不會(huì)改變；如果超過(guò)百分之一百，則不能確定最優(yōu)解是否改變。如果x1對(duì)應(yīng)的目標(biāo)系數(shù)c1由2變?yōu)?.8，x2對(duì)應(yīng)的目標(biāo)系數(shù)c2由3變?yōu)?.5，運(yùn)用百分之百法則，根據(jù)敏感性分析報(bào)告c1由2變?yōu)?占允許減量的百分比為 (2-1.8)/0.5=40%C2由3變?yōu)?.5占允許增加量的百分比為 (3.

17、5-3)/1=50%. 改變的百分比和為90%，沒(méi)有超過(guò)100%，因此最優(yōu)解不變。第30頁(yè)/共43頁(yè)（2）約束右端值b同時(shí)變動(dòng)的百分之百法則： 同時(shí)改變幾個(gè)或所有函數(shù)約束的約束右端值，如果這些變動(dòng)的幅度不大，那么可以用影子價(jià)格預(yù)測(cè)變動(dòng)產(chǎn)生的影響。為了判別這些變動(dòng)的幅度是否允許，計(jì)算每一變動(dòng)占同方向可容許變動(dòng)范圍的百分比，如果所有的百分比之和不超過(guò)百分之一百，那么影子價(jià)格還是有效的；如果所有的百分比之和超過(guò)百分之一百，那就無(wú)法確定影子價(jià)格是否有效。如果右端值b分別由8, 16, 12變?yōu)?.5,15，和11，運(yùn)用百分之百法則:b1由8變?yōu)?.5占允許增量的百分比為 (8.5-8)/2=25%b2

18、由16變?yōu)?5占允許減量的百分比為 (16-15)/8=12.5%b3由12變?yōu)?1占允許減量的百分比為 (12-11)/4=25%. 改變的百分比和為62.5%，沒(méi)有超過(guò)100%，因此影子價(jià)格仍然有效。第31頁(yè)/共43頁(yè)單元格變量名最終值減少成本目標(biāo)系數(shù)允許增加值允許減少值$B$9x102.862.81E+30$C$9x212.4097.83.4$D$9x39.50122.61.5已知一個(gè)線性規(guī)劃問(wèn)題的靈敏度分析報(bào)告如下已知一個(gè)線性規(guī)劃問(wèn)題的靈敏度分析報(bào)告如下變動(dòng)單元格變動(dòng)單元格單元格名 稱最終值影子價(jià)格右端值允許增加值允許減少值$E$4第一資源約束580701E+3012$E$5第二資源約

19、束372.8371531$E$6第三資源約束601.2601250約束條件2012年12月管理創(chuàng)新實(shí)驗(yàn)班期末考試題（1）寫(xiě)出該問(wèn)題的最優(yōu)解（2）分析當(dāng)x1的目標(biāo)系數(shù)減少5，同時(shí)x2的目標(biāo)系數(shù)增加4時(shí)，最優(yōu)解是否改變。（3）分析當(dāng)?shù)谝毁Y源約束右端值增加30，同時(shí)第二資源約束右端值增加4和第三資源約束右端值減少15時(shí)，目標(biāo)函數(shù)值的改變量。第32頁(yè)/共43頁(yè)解：（1）最優(yōu)解為x1=0, x2=12.4, x3=9.5（2） x1的目標(biāo)系數(shù)減少5，占允許減少的百分比=5/=0%，x2的目標(biāo)系數(shù)增加4,占允許增加的百分比=4/7.8=51.2%。改變的百分比和為51.2%，沒(méi)有超過(guò)100%，因此最優(yōu)解不變。（3）第一資源約束右端值增加30，占允許增加的30 /=0%，第二資源約束右端值增加4 ，占允許增加的4/15=26.7%，第三資源約束右端值減少15，占允許減少的15/50=30%。改變的百分比和為56.7%，沒(méi)有超過(guò)100%，因此影子價(jià)格仍然有效。因此目標(biāo)函數(shù)值改變量為 30 30 0 + 4 0 + 4 2.8 15 2.8 15 1.2=11.2 1.2=11.2 18= 18= 6.86.8第33頁(yè)/共43頁(yè)練習(xí)：話務(wù)員排班問(wèn)題 某尋呼公