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

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

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

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

4、險(xiǎn)系數(shù)不超過(guò)4，收益的增長(zhǎng)潛力不低于10%。問(wèn)在滿足上述要求的前提下投資者該如何選擇投資組合使平均年收益率最高？（不同的投資方式的具體參數(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ī)劃模型可以通過(guò)以下幾步完成：(1)首先將題目中所給數(shù)據(jù)輸入工作表中，包括基礎(chǔ)數(shù)據(jù)、約束條件等已知信息，如圖1所示，其中單元格B8、H8是可變單元格，不需要輸入任何數(shù)據(jù)或公式，最后的計(jì)算結(jié)果將顯示其中?；A(chǔ)數(shù)據(jù)決策變量目標(biāo)方程約束條件(2)將目標(biāo)方程和約束條件的對(duì)應(yīng)公式輸入各單元格中，回車后以下四個(gè)單元格均顯示數(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ī)劃問(wèn)題的電子表格模型建好后，即可利用“規(guī)劃求解”功能進(jìn)行求解。針對(duì)圖1的電子表格模型，在工具菜單中選擇“規(guī)劃求解”命令，彈出“規(guī)劃求解參數(shù)”窗口。在該對(duì)話框中，目標(biāo)單元格選擇B11，問(wèn)題類型選擇“最大值”，可變單元格選擇B8：H8，點(diǎn)擊“添加”按鈕，彈出“添加約束”對(duì)話框， 根據(jù)所建模型，共有三個(gè)約束條件，針對(duì)約束一：3x1+10x2 + 6x3+ 2x4+ x5+ 5x6 £ 5，左端“單元格引用位置”應(yīng)

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

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

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