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

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

建立線性規(guī)劃問題的模型利用EXCEL求解線性規(guī)劃問題分析運(yùn)算結(jié)果〔敏感性分析〕目的：1精選ppt一、EXCEL根本知識

1、命名工作表

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

2、工作表

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

Excel-2003256列65535行Excel-200716384列

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

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

在公式中可以引用本工作簿中任何單元格或單元格組的數(shù)據(jù)，也可以引用其它工作簿中任何單元格或單元格組的數(shù)據(jù)。引用單元格數(shù)據(jù)后，公式的運(yùn)算值將隨著被引用的單元格數(shù)據(jù)變化而變化?！?〕引用的類型三種類型：相對引用、絕對引用、混合引用4精選ppt格式：A3、B6〔2〕相對引用

使用相對引用后，系統(tǒng)將會記住建立公式的單元格和被引用的單元格的相對位置，在復(fù)制這個公式時，新的公式單元和被引用的單元仍然保持這種相對位置。5精選ppt〔3〕絕對引用

絕對引用是指被引用的單元與引用的公式單元的位置關(guān)系是絕對的，無論將這個公式復(fù)制到任何單元，公式所引用的還是原來單元格的數(shù)據(jù)。〔4〕混合引用

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

B$3列是相對的，行是絕對的6精選ppt使用Excel進(jìn)行求解1.關(guān)于“規(guī)劃求解〞2.如何加載“規(guī)劃求解〞3.“規(guī)劃求解〞各參數(shù)設(shè)置4.“規(guī)劃求解〞步驟5.利用“規(guī)劃求解〞解線性規(guī)劃問題7精選ppt2.如何加載“規(guī)劃求解〞1)在“工具〞菜單上,單擊“加載宏〞8精選ppt2)在彈出的對話框中的“可用加載宏〞列表框中,選定待添加的加載宏“規(guī)劃求解〞選項(xiàng)旁的復(fù)選框,然后單擊“確定〞.單擊“確定〞后,“工具〞菜單下就會出現(xiàn)一項(xiàng)“規(guī)劃求解〞9精選ppt3.“規(guī)劃求解〞各參數(shù)設(shè)置單擊“規(guī)劃求解〞按鈕,將會出現(xiàn)以下規(guī)劃求解參數(shù)設(shè)置對話框10精選ppt單擊“添加〞,顯示添加約束對話框11精選ppt12精選ppt選項(xiàng):顯示〞規(guī)劃求解選項(xiàng)〞對話框.在其中可以加載或保存規(guī)劃求解模型,并對規(guī)劃求解過程的高級屬性進(jìn)行控制13精選ppt線性規(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)對應(yīng)于目前決策變量的函數(shù)值。5.在步驟4的的數(shù)據(jù)右邊輸入約束條件中右端項(xiàng)〔即常數(shù)項(xiàng)〕6.確定目標(biāo)函數(shù)值存放單元格，并在該單元格中輸入目標(biāo)函數(shù)值電容計(jì)算公式。14精選ppt一個簡單的例子某工廠方案生產(chǎn)兩種產(chǎn)品，利潤分別為2和3，生產(chǎn)單位產(chǎn)品所需的設(shè)備臺時和A、B兩種原材料的消耗，如表產(chǎn)品1產(chǎn)品2設(shè)備128臺時原材料A4016KG原材料B0412KG目標(biāo)是不超過資源限制的情況下，確定兩產(chǎn)品產(chǎn)量，得到最大利潤。15精選ppt建立數(shù)學(xué)公式〔步驟一〕在工作表的頂部輸入數(shù)據(jù)確定每個決策變量所對應(yīng)的單元格位置選擇單元格輸入公式，找到目標(biāo)函數(shù)的值確定約束單元格輸入公式，計(jì)算每個約束條件左邊的值確定約束單元格輸入公式，計(jì)算每個約束條件右邊的值可采用‘復(fù)制粘貼’或‘直接輸入’的方式導(dǎo)入數(shù)據(jù)。16精選ppt建立數(shù)學(xué)公式〔步驟二〕在工作表的頂部輸入數(shù)據(jù)確定每個決策變量所對應(yīng)的單元格位置選擇單元格輸入公式，找到目標(biāo)函數(shù)的值選擇一個單元格輸入公式，計(jì)算每個約束條件左邊的值選擇一個單元格輸入公式，計(jì)算每個約束條件右邊的值圖中，規(guī)定B12、C12為可變單元格可變單元格存放決策變量的取值，可變單元格數(shù)目等于決策變量個數(shù)17精選ppt建立數(shù)學(xué)公式〔步驟三〕在工作表的頂部輸入數(shù)據(jù)確定每個決策變量所對應(yīng)的單元格位置選擇單元格輸入公式，找到目標(biāo)函數(shù)的值確定約束單元格輸入公式，計(jì)算每個約束條件左邊的值確定約束單元格輸入公式，計(jì)算每個約束條件右邊的值在目標(biāo)單元格中，需要填入計(jì)算目標(biāo)函數(shù)值的公式。18精選ppt建立數(shù)學(xué)公式〔步驟四〕在工作表的頂部輸入數(shù)據(jù)確定每個決策變量所對應(yīng)的單元格位置選擇單元格輸入公式，找到目標(biāo)函數(shù)的值確定約束單元格輸入公式，計(jì)算每個約束條件左邊的值確定約束單元格輸入公式，計(jì)算每個約束條件右邊的值在約束單元格中，需要填入計(jì)算約束函數(shù)值的公式。19精選ppt建立數(shù)學(xué)公式〔步驟五〕在工作表的頂部輸入數(shù)據(jù)確定每個決策變量所對應(yīng)的單元格位置選擇單元格輸入公式，找到目標(biāo)函數(shù)的值確定約束單元格輸入公式，計(jì)算每個約束條件左邊的值確定約束單元格輸入公式，計(jì)算每個約束條件右邊的值20精選ppt調(diào)用‘規(guī)劃求解’模塊選擇工具下拉菜單選擇規(guī)劃求解選項(xiàng)〔事先需用Office安裝盤安裝規(guī)劃求解的功能〕21精選ppt填寫目標(biāo)單元格和可變單元格出現(xiàn)規(guī)劃求解參數(shù)對話框在目標(biāo)單元格中輸入B14在等于選擇最大在可變單元格中輸入B12:C12選擇添加在上圖顯示的界面中，需要輸入目標(biāo)單元格、可變單元格，添加約束條件，另外還可能需要進(jìn)行選項(xiàng)設(shè)置。22精選ppt添加約束在添加約束對話框中，在單元格引用位置中輸入B17，選擇<=，在約束值中輸入D17。選擇添加第三個條件添加完畢后，選擇確定當(dāng)規(guī)劃求解參數(shù)對話框重新出現(xiàn)時，選擇選項(xiàng)23精選ppt“選項(xiàng)〞設(shè)置中選項(xiàng)對話框出現(xiàn)時，選擇假設(shè)非負(fù)。選擇確定24精選ppt用Excel求解出現(xiàn)規(guī)劃求解參數(shù)對話框，選擇求解。25精選ppt保存求解結(jié)果當(dāng)求解結(jié)果對話框出現(xiàn)時，選擇保存規(guī)劃求解結(jié)果。選擇確定。26精選ppt運(yùn)算結(jié)果報告列出目標(biāo)單元格和可變單元格以及它們的初始值、最終結(jié)果、約束條件和有關(guān)約束條件的信息。初值和終值分別指單元格在本次求解前的數(shù)值和求解后的數(shù)值。27精選ppt敏感性分析報告〔1〕可變單元格中“單元格〞指決策變量所在單元格的地址“名字〞是決策變量的名稱“終值〞是決策變量的終值,即最優(yōu)值“遞減本錢〞指最優(yōu)解中等于0的變量，對應(yīng)的目標(biāo)函數(shù)中的系數(shù)增加或減少多少，最優(yōu)解不再為0“目標(biāo)式系數(shù)〞目標(biāo)函數(shù)中的系數(shù)，為條件“允許的增量〞與“允許的減量〞表示目標(biāo)函數(shù)中的系數(shù)在增量與減量的變化范圍內(nèi)變化時，最優(yōu)解保持不變〔注：最優(yōu)值發(fā)生變化〕28精選ppt敏感性分析報告〔2〕約束單元格“單元格〞指約束條件左邊所在單元格的地址“名字〞是約束條件左邊的名稱“終值〞是約束條件左邊的值“陰影價格〞指約束條件右邊增加或減少一個單位，目標(biāo)函數(shù)值增加或減少的數(shù)量“約束的限制值〞指約束條件右邊的值，為條件“允許的增量，減量〞表示約束條件右邊在允許的范圍內(nèi)變化時，影子價格不變，即約束條件右邊的值每增加1個單位，目標(biāo)函數(shù)值的增加仍然為影子價格的大小。因此，右端項(xiàng)在一定范圍內(nèi)變化時，影子價格不變，目標(biāo)函數(shù)值的變動等于右端項(xiàng)變動值乘以影子價格29精選ppt極限值報告解釋列出目標(biāo)單元格和可變單元格以及它們的數(shù)值、上下限和目標(biāo)值。含有整數(shù)約束條件的模型不能生本錢報告。其中，下限是在滿足約束條件和保持其它可變單元格數(shù)值不變的情況下，某個可變單元格可以取到的最小值。上限是在這種情況下可以取到的最大值。30精選ppt延伸下面對目標(biāo)系數(shù)同時變動以及約束右端值同時變動的情況分別作延伸?！?〕目標(biāo)系數(shù)c同時變動的百分之百法那么：如果目標(biāo)函數(shù)系數(shù)同時變動，計(jì)算出每一系數(shù)變動量占該系數(shù)同方向可容許變動范圍的百分比，而后將各個系數(shù)的變動百分比相加，如果所得的和不超過百分之一百，最優(yōu)解不會改變；如果超過百分之一百，那么不能確定最優(yōu)解是否改變。如果x1對應(yīng)的目標(biāo)系數(shù)c1由2變?yōu)?.8，x2對應(yīng)的目標(biāo)系數(shù)c2由3變?yōu)?.5，運(yùn)用百分之百法那么，根據(jù)敏感性分析報告c1由2變?yōu)?占允許減量的百分比為(2-1.8)/0.5=40%C2由3變?yōu)?.5占允許增加量的百分比為(3.5-3)/1=50%.改變的百分比和為90%，沒有超過100%，因此最優(yōu)解不變。31精選ppt〔2〕約束右端值b同時變動的百分之百法那么：同時改變幾個或所有函數(shù)約束的約束右端值，如果這些變動的幅度不大，那么可以用影子價格預(yù)測變動產(chǎn)生的影響。為了判別這些變動的幅度是否允許，計(jì)算每一變動占同方向可容許變動范圍的百分比，如果所有的百分比之和不超過百分之一百，那么影子價格還是有效的；如果所有的百分比之和超過百分之一百，那就無法確定影子價格是否有效。如果右端值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%，因此影子價格仍然有效。32精選ppt單元格變量名最終值減少成本目標(biāo)系數(shù)允許增加值允許減少值$B$9x10－2.862.81E+30$C$9x212.4097.83.4$D$9x39.50122.61.5一個線性規(guī)劃問題的靈敏度分析報告如下變動單元格單元格名稱最終值影子價格右端值允許增加值允許減少值$E$4第一資源約束580701E+3012$E$5第二資源約束372.8371531$E$6第三資源約束601.2601250約束條件2021年12月管理創(chuàng)新實(shí)驗(yàn)班期末考試題〔1〕寫出該問題的最優(yōu)解〔2〕分析當(dāng)x1的目標(biāo)系數(shù)減少5，同時x2的目標(biāo)系數(shù)增加4時，最優(yōu)解是否改變?！?〕分析當(dāng)?shù)谝毁Y源約束右端值增加30，同時第二資源約束右端值增加4和第三資源約束右端值減少15時，目標(biāo)函數(shù)值的改變量。33精選ppt解：〔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)解不變?！?〕第一資源約束右端值增加30，占允許增加的30/∞=0%，第二資源約束右端值增加4，占允許增加的4/15=26.7%，第三資源約束右端值減少15，占允許減少的15/50=30%。改變的百分比和為56.7%，沒有超過100%，因此影子價格仍然有效。因此目標(biāo)函數(shù)值改變量為30×0+4×2.8–15×1.2=11.2–18=–6.834精選ppt練習(xí)：話務(wù)員排班問題某尋呼公司雇用了多名話務(wù)員工作，他們每天工作3節(jié)，每節(jié)3小時，每節(jié)開始時間為午夜、凌晨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)查，對于不同的時間，由于業(yè)務(wù)量不同，需要的話務(wù)員的人數(shù)也不相同，公司付的薪水也不相同，有關(guān)數(shù)據(jù)見表。35精選ppt問：如何安排話務(wù)員才能保證效勞人數(shù)，又使總本錢最低？36精選ppt解：這個問題實(shí)際上是一個本錢效益平衡問題。管理層在向客戶提供滿意效勞水平的同時要控制本錢，因此必須尋找本錢與效益的平衡。由于每節(jié)工作時間為3小時，一天被分為8班，每人連續(xù)工作3節(jié)，各班時間安排如下表：37精選ppt38精選ppt為了建立數(shù)學(xué)模型，對應(yīng)于一般本錢效益平衡問題，我們首先必須明確包含的活動數(shù)目，活動一個單位是對應(yīng)于分派一個話務(wù)員到該班次收，效益的水平對應(yīng)于時段。收益水平就是該時段里上下班的話務(wù)員數(shù)目，各活動的單位效