EXCEL求解線性規(guī)劃問題

第五章利用EXCEL求解線性規(guī)劃問題

建立線性規(guī)劃問題的模型利用EXCEL求解線性規(guī)劃問題分析運(yùn)算結(jié)果（敏感性分析）目的：

一、EXCEL基本知識(shí)

1、命名工作表

功能：存儲(chǔ)信息、進(jìn)行計(jì)算、排序數(shù)據(jù)、用圖或表的形式顯示數(shù)據(jù)、規(guī)劃求解、財(cái)會(huì)分析、概率與統(tǒng)計(jì)分析等等（1）激活工作表1，單擊sheet1標(biāo)簽（2）命名工作表或（3）Format/sheetRename顯示Renamesheet對(duì)話框（4）鍵入工作表名

2、工作表

4、進(jìn)行計(jì)算

Excel-2003256列65535行Excel-200716384列

1048576行3、向單元格中輸入數(shù)據(jù)常數(shù)：不能改變的文字或其它數(shù)據(jù)公式：輸入到單元格中的數(shù)字值，或返回?cái)?shù)字值的表達(dá)式。（1）輸出數(shù)據(jù)時(shí)計(jì)算A1=456+789（2）根據(jù)其它單元格的數(shù)據(jù)進(jìn)行計(jì)算A3—456B3-----789C3=A3+B3

5、在公式中引用其它單元格

在公式中可以引用本工作簿中任何單元格或單元格組的數(shù)據(jù)，也可以引用其它工作簿中任何單元格或單元格組的數(shù)據(jù)。引用單元格數(shù)據(jù)后，公式的運(yùn)算值將隨著被引用的單元格數(shù)據(jù)變化而變化。（1）引用的類型三種類型：相對(duì)引用、絕對(duì)引用、混合引用格式：A3、B6（2）相對(duì)引用使用相對(duì)引用后，系統(tǒng)將會(huì)記住建立公式的單元格和被引用的單元格的相對(duì)位置，在復(fù)制這個(gè)公式時(shí)，新的公式單元和被引用的單元仍然保持這種相對(duì)位置。（3）絕對(duì)引用絕對(duì)引用是指被引用的單元與引用的公式單元的位置關(guān)系是絕對(duì)的，無論將這個(gè)公式復(fù)制到任何單元，公式所引用的還是原來單元格的數(shù)據(jù)。（4）混合引用

格式：$a$3$d$5格式：$A3列是絕對(duì)的，行是相對(duì)的

B$3列是相對(duì)的，行是絕對(duì)的使用Excel進(jìn)行求解1.關(guān)于“規(guī)劃求解”2.如何加載“規(guī)劃求解”3.“規(guī)劃求解”各參數(shù)設(shè)置4.“規(guī)劃求解”步驟5.利用“規(guī)劃求解”解線性規(guī)劃問題2.如何加載“規(guī)劃求解”1)在“工具”菜單上,單擊“加載宏”2)在彈出的對(duì)話框中的“可用加載宏”列表框中,選定待添加的加載宏“規(guī)劃求解”選項(xiàng)旁的復(fù)選框,然后單擊“確定”.單擊“確定”后,“工具”菜單下就會(huì)出現(xiàn)一項(xiàng)“規(guī)劃求解”3.“規(guī)劃求解”各參數(shù)設(shè)置單擊“規(guī)劃求解”按鈕,將會(huì)出現(xiàn)以下規(guī)劃求解參數(shù)設(shè)置對(duì)話框單擊“添加”,顯示添加約束對(duì)話框選項(xiàng):顯示”規(guī)劃求解選項(xiàng)”對(duì)話框.在其中可以加載或保存規(guī)劃求解模型,并對(duì)規(guī)劃求解過程的高級(jí)屬性進(jìn)行控制線性規(guī)劃求解步驟：1.確定目標(biāo)函數(shù)系數(shù)存放單元格，并在這些單元格中輸入目標(biāo)函數(shù)系數(shù)。2.確定決策變量存放單元格，并任意輸入一組數(shù)據(jù)；3.確定約束條件中左端項(xiàng)系數(shù)存放單元格，并輸入約束條件左端項(xiàng)系數(shù)；4.在約束條件左端項(xiàng)系數(shù)存放單元格右邊的單元格中輸入約束條件左端項(xiàng)的計(jì)算公式，計(jì)算出約束條件左端項(xiàng)對(duì)應(yīng)于目前決策變量的函數(shù)值。5.在步驟4的的數(shù)據(jù)右邊輸入約束條件中右端項(xiàng)（即常數(shù)項(xiàng)）6.確定目標(biāo)函數(shù)值存放單元格，并在該單元格中輸入目標(biāo)函數(shù)值電容計(jì)算公式。一個(gè)簡(jiǎn)單的例子某工廠計(jì)劃生產(chǎn)兩種產(chǎn)品，利潤(rùn)分別為2和3，已知生產(chǎn)單位產(chǎn)品所需的設(shè)備臺(tái)時(shí)和A、B兩種原材料的消耗，如表產(chǎn)品1產(chǎn)品2設(shè)備128臺(tái)時(shí)原材料A4016KG原材料B0412KG目標(biāo)是不超過資源限制的情況下，確定兩產(chǎn)品產(chǎn)量，得到最大利潤(rùn)。建立數(shù)學(xué)公式（步驟一）在工作表的頂部輸入數(shù)據(jù)確定每個(gè)決策變量所對(duì)應(yīng)的單元格位置選擇單元格輸入公式，找到目標(biāo)函數(shù)的值確定約束單元格輸入公式，計(jì)算每個(gè)約束條件左邊的值確定約束單元格輸入公式，計(jì)算每個(gè)約束條件右邊的值可采用‘復(fù)制粘貼’或‘直接輸入’的方式導(dǎo)入數(shù)據(jù)。建立數(shù)學(xué)公式（步驟二）在工作表的頂部輸入數(shù)據(jù)確定每個(gè)決策變量所對(duì)應(yīng)的單元格位置選擇單元格輸入公式，找到目標(biāo)函數(shù)的值選擇一個(gè)單元格輸入公式，計(jì)算每個(gè)約束條件左邊的值選擇一個(gè)單元格輸入公式，計(jì)算每個(gè)約束條件右邊的值圖中，規(guī)定B12、C12為可變單元格可變單元格存放決策變量的取值，可變單元格數(shù)目等于決策變量個(gè)數(shù)建立數(shù)學(xué)公式（步驟三）在工作表的頂部輸入數(shù)據(jù)確定每個(gè)決策變量所對(duì)應(yīng)的單元格位置選擇單元格輸入公式，找到目標(biāo)函數(shù)的值確定約束單元格輸入公式，計(jì)算每個(gè)約束條件左邊的值確定約束單元格輸入公式，計(jì)算每個(gè)約束條件右邊的值在目標(biāo)單元格中，需要填入計(jì)算目標(biāo)函數(shù)值的公式。建立數(shù)學(xué)公式（步驟四）在工作表的頂部輸入數(shù)據(jù)確定每個(gè)決策變量所對(duì)應(yīng)的單元格位置選擇單元格輸入公式，找到目標(biāo)函數(shù)的值確定約束單元格輸入公式，計(jì)算每個(gè)約束條件左邊的值確定約束單元格輸入公式，計(jì)算每個(gè)約束條件右邊的值在約束單元格中，需要填入計(jì)算約束函數(shù)值的公式。建立數(shù)學(xué)公式（步驟五）在工作表的頂部輸入數(shù)據(jù)確定每個(gè)決策變量所對(duì)應(yīng)的單元格位置選擇單元格輸入公式，找到目標(biāo)函數(shù)的值確定約束單元格輸入公式，計(jì)算每個(gè)約束條件左邊的值確定約束單元格輸入公式，計(jì)算每個(gè)約束條件右邊的值調(diào)用‘規(guī)劃求解’模塊選擇工具下拉菜單選擇規(guī)劃求解選項(xiàng)（事先需用Office安裝盤安裝規(guī)劃求解的功能）填寫目標(biāo)單元格和可變單元格出現(xiàn)規(guī)劃求解參數(shù)對(duì)話框在目標(biāo)單元格中輸入B14在等于選擇最大在可變單元格中輸入B12:C12選擇添加在上圖顯示的界面中，需要輸入目標(biāo)單元格、可變單元格，添加約束條件，另外還可能需要進(jìn)行選項(xiàng)設(shè)置。添加約束在添加約束對(duì)話框中，在單元格引用位置中輸入B17，選擇<=，在約束值中輸入D17。選擇添加第三個(gè)條件添加完畢后，選擇確定當(dāng)規(guī)劃求解參數(shù)對(duì)話框重新出現(xiàn)時(shí)，選擇選項(xiàng)“選項(xiàng)”設(shè)置當(dāng)選項(xiàng)對(duì)話框出現(xiàn)時(shí)，選擇假設(shè)非負(fù)。選擇確定用Excel求解出現(xiàn)規(guī)劃求解參數(shù)對(duì)話框，選擇求解。保存求解結(jié)果當(dāng)求解結(jié)果對(duì)話框出現(xiàn)時(shí)，選擇保存規(guī)劃求解結(jié)果。選擇確定。運(yùn)算結(jié)果報(bào)告列出目標(biāo)單元格和可變單元格以及它們的初始值、最終結(jié)果、約束條件和有關(guān)約束條件的信息。初值和終值分別指單元格在本次求解前的數(shù)值和求解后的數(shù)值。敏感性分析報(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ā)生變化）敏感性分析報(bào)告（2）約束單元格“單元格”指約束條件左邊所在單元格的地址“名字”是約束條件左邊的名稱“終值”是約束條件左邊的值“陰影價(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à)格極限值報(bào)告解釋列出目標(biāo)單元格和可變單元格以及它們的數(shù)值、上下限和目標(biāo)值。含有整數(shù)約束條件的模型不能生成本報(bào)告。其中，下限是在滿足約束條件和保持其它可變單元格數(shù)值不變的情況下，某個(gè)可變單元格可以取到的最小值。上限是在這種情況下可以取到的最大值。

延伸下面對(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)百分比相加，如果所得的和不超過百分之一百，最優(yōu)解不會(huì)改變；如果超過百分之一百，則不能確定最優(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.5-3)/1=50%.改變的百分比和為90%，沒有超過100%，因此最優(yōu)解不變。（2）約束右端值b同時(shí)變動(dòng)的百分之百法則：同時(shí)改變幾個(gè)或所有函數(shù)約束的約束右端值，如果這些變動(dòng)的幅度不大，那么可以用影子價(jià)格預(yù)測(cè)變動(dòng)產(chǎn)生的影響。為了判別這些變動(dòng)的幅度是否允許，計(jì)算每一變動(dòng)占同方向可容許變動(dòng)范圍的百分比，如果所有的百分比之和不超過百分之一百，那么影子價(jià)格還是有效的；如果所有的百分比之和超過百分之一百，那就無法確定影子價(jià)格是否有效。如果右端值b分別由8,16,12變?yōu)?.5,15，和11，運(yùn)用百分之百法則:b1由8變?yōu)?.5占允許增量的百分比為(8.5-8)/2=25%b2由16變?yōu)?5占允許減量的百分比為(16-15)/8=12.5%b3由12變?yōu)?1占允許減量的百分比為(12-11)/4=25%.改變的百分比和為62.5%，沒有超過100%，因此影子價(jià)格仍然有效。單元格變量名最終值減少成本目標(biāo)系數(shù)允許增加值允許減少值$B$9x10－2.862.81E+30$C$9x212.4097.83.4$D$9x39.50122.61.5已知一個(gè)線性規(guī)劃問題的靈敏度分析報(bào)告如下變動(dòng)單元格單元格名

稱最終值影子價(jià)格右端值允許增加值允許減少值$E$4第一資源約束580701E+3012$E$5第二資源約束372.8371531$E$6第三資源約束601.2601250約束條件2012年12月管理創(chuàng)新實(shí)驗(yàn)班期末考試題（1）寫出該問題的最優(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ù)值的改變量。解：（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%，沒有超過100%，因此最優(yōu)解不變。（3）第一資源約束右端值增加30，占允許增加的30/∞=0%，第二資源約束右端值增加4，占允許增加的4/15=26.7%，第三資源約束右端值減少15，占允許減少的15/50=30%。改變的百分比和為56.7%，沒有超過100%，因此影子價(jià)格仍然有效。因此目標(biāo)函數(shù)值改變量為30×0+4×2.8–15×1.2=11.2–

18=–

6.8練習(xí)：話務(wù)員排班問題某尋呼公司雇用了多名話務(wù)員工作，他們每天工作3節(jié)，每節(jié)3小時(shí)，每節(jié)開始時(shí)間為午夜、凌晨3點(diǎn)鐘、凌晨6點(diǎn)鐘，上午9點(diǎn)、中午12點(diǎn)、下午3點(diǎn)、6點(diǎn)、9點(diǎn)，為方便話務(wù)員上下班，管理層安排每位話務(wù)員每天連續(xù)工作3節(jié)，根據(jù)調(diào)查，對(duì)于不同的時(shí)間，由于業(yè)務(wù)量不同，需要的話務(wù)員的人數(shù)也不相同，公司付的薪水也不相同，有關(guān)數(shù)據(jù)見表。問：如何安排話務(wù)員才能保證服務(wù)人數(shù)，又使總成本最低？解：這個(gè)問題實(shí)際上是一個(gè)成本效益平衡問題。管理層在向客戶提供滿意服務(wù)水平的同時(shí)要控制成本，因此必須尋找成本與效益的平衡。由于每節(jié)工作時(shí)間為3小時(shí)，一天被分為8班，每人連續(xù)工作3節(jié)，各班時(shí)間安排如下表：為了建立數(shù)學(xué)模型，對(duì)應(yīng)于一般成本效益平衡問題，我們首先必須明確包含的活動(dòng)數(shù)目，活動(dòng)一個(gè)單位是對(duì)應(yīng)于分派一個(gè)話務(wù)員到該班次收，效益的水平對(duì)應(yīng)于時(shí)段。收益水平就是該時(shí)段里