EXCEL求解線性規(guī)劃問題ppt課件.ppt

第五章利用EXCEL求解線性規(guī)劃問題,建立線性規(guī)劃問題的模型利用EXCEL求解線性規(guī)劃問題分析運算結(jié)果（敏感性分析）,目的：,1,一、EXCEL基本知識,1、命名工作表,功能：存儲信息、進行計算、排序數(shù)據(jù)、用圖或表的形式顯示數(shù)據(jù)、規(guī)劃求解、財會分析、概率與統(tǒng)計分析等等,（1）激活工作表1，單擊sheet1標簽,（2）命名工作表或（3）Format/sheetRename顯示Renamesheet對話框,（4）鍵入工作表名,2,2、工作表,4、進行計算,Excel-2003256列65535行Excel-200716384列1048576行,3、向單元格中輸入數(shù)據(jù),常數(shù)：不能改變的文字或其它數(shù)據(jù),公式：輸入到單元格中的數(shù)字值，或返回數(shù)字值的表達式。,（1）輸出數(shù)據(jù)時計算,A1=456+789,（2）根據(jù)其它單元格的數(shù)據(jù)進行計算,A3456B3-789C3=A3+B3,3,5、在公式中引用其它單元格,在公式中可以引用本工作簿中任何單元格或單元格組的數(shù)據(jù)，也可以引用其它工作簿中任何單元格或單元格組的數(shù)據(jù)。引用單元格數(shù)據(jù)后，公式的運算值將隨著被引用的單元格數(shù)據(jù)變化而變化。,（1）引用的類型,三種類型：,相對引用、絕對引用、混合引用,4,格式：A3、B6,（2）相對引用,使用相對引用后，系統(tǒng)將會記住建立公式的單元格和被引用的單元格的相對位置，在復(fù)制這個公式時，新的公式單元和被引用的單元仍然保持這種相對位置。,5,（3）絕對引用,絕對引用是指被引用的單元與引用的公式單元的位置關(guān)系是絕對的，無論將這個公式復(fù)制到任何單元，公式所引用的還是原來單元格的數(shù)據(jù)。,（4）混合引用,格式：$a$3$d$5,格式：$A3列是絕對的，行是相對的B$3列是相對的，行是絕對的,6,使用Excel進行求解,1.關(guān)于“規(guī)劃求解”2.如何加載“規(guī)劃求解”3.“規(guī)劃求解”各參數(shù)設(shè)置4.“規(guī)劃求解”步驟5.利用“規(guī)劃求解”解線性規(guī)劃問題,7,2.如何加載“規(guī)劃求解”,1)在“工具”菜單上,單擊“加載宏”,8,2)在彈出的對話框中的“可用加載宏”列表框中,選定待添加的加載宏“規(guī)劃求解”選項旁的復(fù)選框,然后單擊“確定”.單擊“確定”后,“工具”菜單下就會出現(xiàn)一項“規(guī)劃求解”,9,3.“規(guī)劃求解”各參數(shù)設(shè)置,單擊“規(guī)劃求解”按鈕,將會出現(xiàn)以下規(guī)劃求解參數(shù)設(shè)置對話框,10,單擊“添加”,顯示添加約束對話框,11,12,選項:顯示”規(guī)劃求解選項”對話框.在其中可以加載或保存規(guī)劃求解模型,并對規(guī)劃求解過程的高級屬性進行控制,13,線性規(guī)劃求解步驟：,1.確定目標函數(shù)系數(shù)存放單元格，并在這些單元格中輸入目標函數(shù)系數(shù)。2.確定決策變量存放單元格，并任意輸入一組數(shù)據(jù)；3.確定約束條件中左端項系數(shù)存放單元格，并輸入約束條件左端項系數(shù)；4.在約束條件左端項系數(shù)存放單元格右邊的單元格中輸入約束條件左端項的計算公式，計算出約束條件左端項對應(yīng)于目前決策變量的函數(shù)值。5.在步驟4的的數(shù)據(jù)右邊輸入約束條件中右端項（即常數(shù)項）6.確定目標函數(shù)值存放單元格，并在該單元格中輸入目標函數(shù)值電容計算公式。,14,一個簡單的例子,某工廠計劃生產(chǎn)兩種產(chǎn)品，利潤分別為2和3，已知生產(chǎn)單位產(chǎn)品所需的設(shè)備臺時和A、B兩種原材料的消耗，如表,目標是不超過資源限制的情況下，確定兩產(chǎn)品產(chǎn)量，得到最大利潤。,15,建立數(shù)學公式（步驟一）,在工作表的頂部輸入數(shù)據(jù)確定每個決策變量所對應(yīng)的單元格位置選擇單元格輸入公式，找到目標函數(shù)的值確定約束單元格輸入公式，計算每個約束條件左邊的值確定約束單元格輸入公式，計算每個約束條件右邊的值,可采用復(fù)制粘貼或直接輸入的方式導(dǎo)入數(shù)據(jù)。,16,建立數(shù)學公式（步驟二）,在工作表的頂部輸入數(shù)據(jù)確定每個決策變量所對應(yīng)的單元格位置選擇單元格輸入公式，找到目標函數(shù)的值選擇一個單元格輸入公式，計算每個約束條件左邊的值選擇一個單元格輸入公式，計算每個約束條件右邊的值,圖中，規(guī)定B12、C12為可變單元格,可變單元格存放決策變量的取值，可變單元格數(shù)目等于決策變量個數(shù),17,建立數(shù)學公式（步驟三）,在工作表的頂部輸入數(shù)據(jù)確定每個決策變量所對應(yīng)的單元格位置選擇單元格輸入公式，找到目標函數(shù)的值確定約束單元格輸入公式，計算每個約束條件左邊的值確定約束單元格輸入公式，計算每個約束條件右邊的值,在目標單元格中，需要填入計算目標函數(shù)值的公式。,18,建立數(shù)學公式（步驟四）,在工作表的頂部輸入數(shù)據(jù)確定每個決策變量所對應(yīng)的單元格位置選擇單元格輸入公式，找到目標函數(shù)的值確定約束單元格輸入公式，計算每個約束條件左邊的值確定約束單元格輸入公式，計算每個約束條件右邊的值,在約束單元格中，需要填入計算約束函數(shù)值的公式。,19,建立數(shù)學公式（步驟五）,在工作表的頂部輸入數(shù)據(jù)確定每個決策變量所對應(yīng)的單元格位置選擇單元格輸入公式，找到目標函數(shù)的值確定約束單元格輸入公式，計算每個約束條件左邊的值確定約束單元格輸入公式，計算每個約束條件右邊的值,20,調(diào)用規(guī)劃求解模塊,選擇工具下拉菜單選擇規(guī)劃求解選項（事先需用Office安裝盤安裝規(guī)劃求解的功能）,21,填寫目標單元格和可變單元格,出現(xiàn)規(guī)劃求解參數(shù)對話框在目標單元格中輸入B14在等于選擇最大在可變單元格中輸入B12:C12選擇添加,在上圖顯示的界面中，需要輸入目標單元格、可變單元格，添加約束條件，另外還可能需要進行選項設(shè)置。,22,添加約束,在添加約束對話框中，在單元格引用位置中輸入B17，選擇=，在約束值中輸入D17。選擇添加第三個條件添加完畢后，選擇確定當規(guī)劃求解參數(shù)對話框重新出現(xiàn)時，選擇選項,23,“選項”設(shè)置,當選項對話框出現(xiàn)時，選擇假設(shè)非負。選擇確定,24,用Excel求解,出現(xiàn)規(guī)劃求解參數(shù)對話框，選擇求解。,25,保存求解結(jié)果,當求解結(jié)果對話框出現(xiàn)時，選擇保存規(guī)劃求解結(jié)果。選擇確定。,26,運算結(jié)果報告,列出目標單元格和可變單元格以及它們的初始值、最終結(jié)果、約束條件和有關(guān)約束條件的信息。初值和終值分別指單元格在本次求解前的數(shù)值和求解后的數(shù)值。,27,敏感性分析報告（1）,可變單元格中“單元格”指決策變量所在單元格的地址“名字”是決策變量的名稱“終值”是決策變量的終值,即最優(yōu)值“遞減成本”指最優(yōu)解中等于0的變量，對應(yīng)的目標函數(shù)中的系數(shù)增加或減少多少，最優(yōu)解不再為0“目標式系數(shù)”目標函數(shù)中的系數(shù)，為已知條件“允許的增量”與“允許的減量”表示目標函數(shù)中的系數(shù)在增量與減量的變化范圍內(nèi)變化時，最優(yōu)解保持不變（注：最優(yōu)值發(fā)生變化）,28,敏感性分析報告（2）,約束單元格“單元格”指約束條件左邊所在單元格的地址“名字”是約束條件左邊的名稱“終值”是約束條件左邊的值“陰影價格”指約束條件右邊增加或減少一個單位，目標函數(shù)值增加或減少的數(shù)量“約束的限制值”指約束條件右邊的值，為已知條件“允許的增量，減量”表示約束條件右邊在允許的范圍內(nèi)變化時，影子價格不變，即約束條件右邊的值每增加1個單位，目標函數(shù)值的增加仍然為影子價格的大小。因此，右端項在一定范圍內(nèi)變化時，影子價格不變，目標函數(shù)值的變動等于右端項變動值乘以影子價格,29,極限值報告解釋,列出目標單元格和可變單元格以及它們的數(shù)值、上下限和目標值。含有整數(shù)約束條件的模型不能生成本報告。其中，下限是在滿足約束條件和保持其它可變單元格數(shù)值不變的情況下，某個可變單元格可以取到的最小值。上限是在這種情況下可以取到的最大值。,30,延伸,下面對目標系數(shù)同時變動以及約束右端值同時變動的情況分別作延伸。（1）目標系數(shù)c同時變動的百分之百法則：如果目標函數(shù)系數(shù)同時變動，計算出每一系數(shù)變動量占該系數(shù)同方向可容許變動范圍的百分比，而后將各個系數(shù)的變動百分比相加，如果所得的和不超過百分之一百，最優(yōu)解不會改變；如果超過百分之一百，則不能確定最優(yōu)解是否改變。,如果x1對應(yīng)的目標系數(shù)c1由2變?yōu)?.8，x2對應(yīng)的目標系數(shù)c2由3變?yōu)?.5，運用百分之百法則，根據(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,（2）約束右端值b同時變動的百分之百法則：同時改變幾個或所有函數(shù)約束的約束右端值，如果這些變動的幅度不大，那么可以用影子價格預(yù)測變動產(chǎn)生的影響。為了判別這些變動的幅度是否允許，計算每一變動占同方向可容許變動范圍的百分比，如果所有的百分比之和不超過百分之一百，那么影子價格還是有效的；如果所有的百分比之和超過百分之一百，那就無法確定影子價格是否有效。,如果右端值b分別由8,16,12變?yōu)?.5,15，和11，運用百分之百法則: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,已知一個線性規(guī)劃問題的靈敏度分析報告如下變動單元格,約束條件,2012年12月管理創(chuàng)新實驗班期末考試題,（1）寫出該問題的最優(yōu)解（2）分析當x1的目標系數(shù)減少5，同時x2的目標系數(shù)增加4時，最優(yōu)解是否改變。（3）分析當?shù)谝毁Y源約束右端值增加30，同時第二資源約束右端值增加4和第三資源約束右端值減少15時，目標函數(shù)值的改變量。,33,解：（1）最優(yōu)解為x1=0,x2=12.4,x3=9.5（2）x1的目標系數(shù)減少5，占允許減少的百分比=5/=0%，x2的目標系數(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%，因此影子價格仍然有效。因此目標函數(shù)值改變量為300+42.8151.2=11.218=6.8,34,練習：話務(wù)員排班問題,某尋呼公司雇用了多名話務(wù)員工作，他們每天工作3節(jié)，每節(jié)3小時，每節(jié)開始時間為午夜、凌晨3點鐘、凌晨6點鐘，上午9點、中午12點、下午3點、6點、9點，為方便話務(wù)員上下班，管理層安排每位話務(wù)員每天連續(xù)工作3節(jié)，根據(jù)調(diào)查，對于不同的時間，由于業(yè)務(wù)量不同，需要的話務(wù)員的人數(shù)也不相同，公司付的薪水也不相同，有關(guān)數(shù)據(jù)見表。,35,問：如何安排話務(wù)員才能保證服務(wù)人數(shù)，又使總成本最低？,36,解：這個問題實際上是一個成本效益平衡問題。管理層在向客戶提供滿意服務(wù)水平的同時要控制成本，因此必須尋找成本與效益的平衡。由于每節(jié)工作時間為3小時，一天被分為8班，每人連續(xù)工作3節(jié)，各班時間安排如下表：,37,38,為了建立數(shù)學模型，對應(yīng)于一般成本效益平衡問題，我們首先必須明確包含的活動數(shù)目，活動一個單位