用excel規(guī)劃求解并作靈敏度分析

1、題目如何利用EXC E L求解線性規(guī)劃問題及其靈敏度分析第 8 組姓名 學(xué)號 樂俊松 090960125孫然 090960122徐正超 090960121崔凱 090960120王煒垚 090960118蔡淼 090960117南京航空航天大學(xué)（貿(mào)易經(jīng)濟）系2011年（5）月（3）日摘要線性規(guī)劃是運籌學(xué)的重要組成部分，在工業(yè)、軍事、經(jīng)濟計劃等領(lǐng)域有著廣泛的應(yīng)用，但其手工求解方法的計算步驟繁瑣復(fù)雜。本文以實際生產(chǎn)計劃投資組合最優(yōu)化問題為例詳細介紹了Excel軟件的”規(guī)劃求解”和“solvertable”功能輔助求解線性規(guī)劃模型的具體步驟，并對其進行了靈敏度分析。目錄引言 4軟件的使用步驟 .4結(jié)

2、果分析 . 9結(jié)論與展望10參考文獻 111. 引言對于整個運籌學(xué)來說，線性規(guī)劃(Linear Programming)是形成最早、最成熟的一個分支，是優(yōu)化理論最基礎(chǔ)的部分，也是運籌學(xué)最核心的內(nèi)容之一。它是應(yīng)用分析、量化的方法，在一定的約束條件下，對管理系統(tǒng)中的有限資源進行統(tǒng)籌規(guī)劃，為決策者提供最優(yōu)方案，以便產(chǎn)生最大的經(jīng)濟和社會效益。因此，將線性規(guī)劃方法用于企業(yè)的產(chǎn)、銷、研等過程成為了現(xiàn)代科學(xué)管理的重要手段之一。1Excel中的線性規(guī)劃求解和solvertable功能并不作為命令直接顯示在菜單中，因此，使用前需首先加載該模塊。具體操作過程為：在Excel的菜單欄中選擇“工具加載宏”，然后在彈出

3、的對話框中選擇“規(guī)劃求解”和“solvertable”，并用鼠標左鍵單擊“確定”。加載成功后，在菜單欄中選擇“工具規(guī)劃求解”，便會彈出“規(guī)劃求解參數(shù)”對話框。在開始求解之前，需先在對話框中設(shè)置好各種參數(shù)，包括目標單元格、問題類型(求最大值還是最小值)、可變單元格以及約束條件等。2 軟件的使用步驟“規(guī)劃求解”可以解決數(shù)學(xué)、財務(wù)、金融、經(jīng)濟、統(tǒng)計等諸多實際問題，在此我們只舉一個簡單的應(yīng)用實例，說明其具體的操作方法。 某人有一筆資金可用于長期投資,可供選擇的投資機會包括購買國庫券、公司債券、投資房地產(chǎn)、購買股票或銀行保值儲蓄等。投資者希望投資組合的平均年限不超過5年，平均的期望收益率不低于13%，風

4、險系數(shù)不超過4，收益的增長潛力不低于10%。問在滿足上述要求的前提下投資者該如何選擇投資組合使平均年收益率最高？（不同的投資方式的具體參數(shù)如下表。）解：設(shè)xi為第I種投資方式在總投資額中的比例，則模型如下：Max S=11x1+15x2 +25x3+20x4+10x5+12x6+3x7s.t. 3x1+10x2 + 6x3+ 2x4+ x5+ 5x6 £ 511x1+15x2+25x3+20x4+10x5+12x6+3x7 ³ 13x1+ 3x2 + 8x3 + 6x4+ x5+ 2x6 £ 415x2 +30x3 +20x4+5x5 +10x6 ³10

5、x1+ x2 + x3 + x4 + x5 + x6+ x7 = 1x1,x2,x3,x4,x5,x6,x7 ³0在EXCEL表格中，建立線性規(guī)劃模型可以通過以下幾步完成：(1)首先將題目中所給數(shù)據(jù)輸入工作表中，包括基礎(chǔ)數(shù)據(jù)、約束條件等已知信息，如圖1所示，其中單元格B8、H8是可變單元格，不需要輸入任何數(shù)據(jù)或公式，最后的計算結(jié)果將顯示其中?；A(chǔ)數(shù)據(jù)決策變量目標方程約束條件(2)將目標方程和約束條件的對應(yīng)公式輸入各單元格中，回車后以下四個單元格均顯示數(shù)字“0”。B11=SUMPR0DUCT(B3：H3，B8：H8）B14=SUMPR0DUCT(B2：H2，B8：H8）B15=SUMP

6、R0DUCT(B3：H3，B8：H8）B16=SUMPR0DUCT(B4：H4，B8：H8）B17=SUMPR0DUCT(B5：H5，B8：H8）B18=SUM(B8：H8)線性規(guī)劃問題的電子表格模型建好后，即可利用“規(guī)劃求解”功能進行求解。針對圖1的電子表格模型，在工具菜單中選擇“規(guī)劃求解”命令，彈出“規(guī)劃求解參數(shù)”窗口。在該對話框中，目標單元格選擇B11，問題類型選擇“最大值”，可變單元格選擇B8：H8，點擊“添加”按鈕，彈出“添加約束”對話框， 根據(jù)所建模型，共有三個約束條件，針對約束一：3x1+10x2 + 6x3+ 2x4+ x5+ 5x6 £ 5，左端“單元格引用位置”應(yīng)

7、選擇輸入B14，右端輸入C14，符號類型選擇“<=”。繼續(xù)添加約束二、三，點擊“添加”，分別選擇：B15³C15，B16 £C16，B17³C17，B18=C18完成后選擇“確定”，回到“規(guī)劃求解參數(shù)“。求解參數(shù)右側(cè)有一個“選項”按鈕，利用它可以在求解之前對求解過程做一些特定的設(shè)置。本例中的線性規(guī)劃模型對x1和x2有非負約束的要求，點擊“選項”按鈕，彈出“規(guī)劃求解選項”對話框，該對話框中是關(guān)于求解問題的一些更細致的選項，其中最重要的是“采用線性模型”和“假定非負”，確定選擇這兩項如圖5所示，這就告訴Excel求解的是一個線性規(guī)劃問題，并且為非負約束，這樣它將

8、拒絕可變單元格產(chǎn)生負值。其他選項對于小型計算通常是比較合適的，所以無需進行修改。點擊“確定”回到“規(guī)劃求解參數(shù)”對話框。 以上都做好之后點擊求解。 規(guī)劃求解之后點擊solvertable功能，選擇一維如圖跳出新界面后，第一行空格選定要想測定哪個系數(shù)的靈敏度設(shè)a34所在單元格。第2行空格設(shè)定a34從0.1變換到10，精度為0.1。第3行空格設(shè)定輸出X1到X7和目標函數(shù)所對應(yīng)的值。第4行空格設(shè)定從D24單元格開始輸出結(jié)果，然后求解。如圖3 結(jié)果分析規(guī)劃求解后問題答案自動顯示在表格中，如圖所示得最優(yōu)解：X1=0.57143，X3=0.42857平均年收益率=17%即將57.1%的資金投入到國債，42

9、.9%的資金投入到房地產(chǎn)，可以實現(xiàn)最大收益。然后進行靈敏度分析，剛才求解中假設(shè)求a34的靈敏度（即股票系數(shù)的靈敏度），solvertable求解后顯示如圖。 由圖可知，當a34>5.4時，問題的最優(yōu)解還是X1和X3，由此可知，a34的靈敏度,為a34>5.4。 因此，若想測定其他系數(shù)的靈敏度，只需將solvertable的第一行空格選定相應(yīng)的單元格便是。4 結(jié)論與展望通過上述步驟可看出，利用Excel進行線性規(guī)劃模型的求解簡便、快捷，表中數(shù)值可根據(jù)用戶要求自行設(shè)置，除了在合理安排產(chǎn)品的生產(chǎn)決策可使用外，對于研究如何合理使用企業(yè)各項經(jīng)濟資源，以及研究如何統(tǒng)籌安排，對人、財、物等現(xiàn)有資源進行優(yōu)化組合、實現(xiàn)最大效