用ecel规划求解并作灵敏度分析

1、题目如何利用EXC E L求解线性规划问题及其灵敏度分析第 8组姓名学号乐俊松0孙然0徐正超0崔凯0王炜垚0蔡淼0南京航空航天大学（贸易经济）系2011 年（5）月（3）日摘要线性规划是运筹学的重要组成部分，在工业、军事、经济计划等 领域有着广泛的应用，但其手工求解方法的计算步骤繁琐复杂。本文 以实际生产计划投资组合最优化问题为例详细介绍了Excel软件的”规划求解”和“ solvertable ”功能辅助求解线性规划模型的具体步 骤，并对其进行了灵敏度分析。目录引言 4软件的使用步骤 .4结 果 分 析9结论与展望 10参考文献 111. 引言对于整个运筹学来说，线性规划(Lin ear P

2、rogrammi ng)是形成最早、最成熟的一个分支，是优化理论最基础的部分，也是运筹学最核 心的内容之一。它是应用分析、量化的方法，在一定的约束条件下， 对管理系统中的有限资源进行统筹规划，为决策者提供最优方案，以 便产生最大的经济和社会效益。因此，将线性规划方法用于企业的 产、销、研等过程成为了现代科学管理的重要手段之一。1Excel中的线性规划求解和solvertable功能并不作为命令直接显示在菜 单中，因此，使用前需首先加载该模块。具体操作过程 为：在Excel的菜单栏中选择“工具/加载宏”，然后在弹出的对话 框中选择“规划求解”和“ solvertable ”，并用鼠标左键单击“确

3、 定”。加载成功后，在菜单栏中选择“工具/规划求解”，便会弹出“规划求解参数”对话框。在开始求解之前，需先在对话框中设置好各种参数，包括目标单元格、问题类型(求最大值还是最小值)、可变单元格以及约束条件等。2软件的使用步骤“规划求解”可以解决数学、财务、金融、经济、统计等诸多实际问题，在此我们只举一个简单的应用实例，说明其具体的操作方法。号限（年）率%系数力%1国库券311102公司债券10153153房地产6258304股票2206205短期存款11015某人有一笔资金可用于长期投资，可供选择的投资机会包括购买国 库券、公司债券、投资房地产、购买股票或银行保值储蓄等。投资 者希望投资组合的平

4、均年限不超过 5年，平均的期望收益率不低于 13%风险系数不超过4，收益的增长潜力不低于 10%问在满足上 述要求的前提下投资者该如何选择投资组合使平均年收益率最高 （不同的投资方投式资勺具体年收数如下表险）长潜解:长设诸蓄为第I 5种投资方式在总投资额中的比例，则模型如下:7 现金存款0300Max S=11x1+15x2 +25x3+20x4+10x5+12x6+3x73x1+10x2 + 6x3+ 2x4+ x5+ 5x6511x1+15x2+25x3+20x4+10x5+12x6+3x713x1+ 3x2 + 8x3 + 6x4+ x5+ 2x6415x2 +30x3 +20x4+5x

5、5 +10x610x1+ x2 + x3 + x4 + x5 + x6+ x7 = 1 x1,x2,x3,x4,x5,x6,x70在EXCE表格中，建立线性规划模型可以通过以下几步完成：(1)首先将题目中所给数据输入工作表中，包括基础数据、约束条件等已知信息，如图1所示，其中单元格B8 H8是可变 单元格，不需要输入任何数据或公式，最后的计算结果将显示 其中。ql决策变量基础数据(2)将目标方程和约束条件的对应公式输入各单元格中，回 车后以下四个单元格均显示数字“0”。B11=SUMPR0DUCT(：3H3, B8: H8)B14=SUMPR0DUCT(：2H2, B8: H8)B15=SUM

6、PR0DUCT(：3H3, B8: H8)B16=SUMPR0DUCT：4H4, B8: H8)B17=SUMPR0DUCT：5H5, B8: H8)B18=SUM(B8 H8)线性规划问题的电子表格模型建好后，即可利用“规划求解”功能进行求解。针对图1的电子表格模型，在工具菜单中选择“规划求解”命令，弹出“规划求解参数”窗口。在该对话框中，目 标单元格选择B11，问题类型选择“最大值”，可变单元格选择B8:H8,点击“添加”按钮，弹出“添加约束”对话框，根据所建模型，共有三个约束条件，针对约束一：3x1+10x2 + 6x3+ 2x4+x5+ 5x65，左端“单元格引用位置”应选择输入 B1

7、4,右端输入C14，符号类型选择“ <=”。继续添加约束二、三，点击“添加”， 分别选择：B15 C15, B16 C16, B17 C17, B18=C18成后选择“确定”，回到“规划求解参数“。求解参数右侧有一个“选项”按钮，利用它可以在求解之前对求解过程做一些特定的设置。本例中的线性规划模型对x1和x2有非负约束的要求，点击“选项”按钮，弹出“规划求解选项” 对话框，该对话框中是关于求解问题的一些更细致的选项，其中 最重要的是“采用线性模型”和“假定非负”，确定选择这两项如 图5所示，这就告诉Excel求解的是一个线性规划问题，并且为 非负约束，这样它将拒绝可变单元格产生负值。其他

8、选项对于小 型计算通常是比较合适的，所以无需进行修改。点击“确定”回到“规划求解参数”对话框。頊长运算时面Q):迭代沁F ©:榕度允许误差輕：收敘度:V采用拔性模型7假定非炭-拮计、5正切函数r -i二次方程©ion 秒100_.000CCI1S笔消'1俣存模型®二7 Wig 自动按比例縮戲QD ；_|显示谨代结果込)搜素。牛锁法忆以上都做好之后点击求解理划求解踣果规划求解找到1解.可漓足所有的妁束艮量忧.VSjIja报告匹）a保存规划求解鰭果屯）Q恢亘为傥值追定I 取清!保存方窠堡丄二:帮肋卫P_l运算结果报告 碱屈i生报肯 很限值报告规划求解之后点击s

9、olvertable 功能，选择一维如图Type of tableYou cari create a oneway or tvjoay table, depending on whether you vjant bo test the sensitivitv of outputs to one or tvw inputs. Oneway table" Twor/ay table跳出新界面后，第一行空格选定要想测定哪个系数的灵敏度设a34所在单元格 第2行空格设定a34从变换到10，精度为。第3行空格设定输出X1到X7和目标函数所对应的值。第4行空格设定从D24单元格开始输出结果，然后

10、求解。如图3结果分析规划求解后问题答案自动显示在表格中，如图所示ABCDEF iGH国库券Q司债券唐地产股票遇期存款长期储蓄现金存款23_46投资期限c呂31062150年收益率(%)1险系数138&120増长港力紀0153020510000S9投资金额比例0. 5714300. 428570011总收益171213约東量iq4.2857151516年收益率%)1713风险系数4417増长潸力%12. 85711018系数11得最优解：X1=, X3=平均年收益率=17%即将勺资金投入到国债，的资金投入到房地产，可以实现最大收、人益。然后进行灵敏度分析，刚才求

11、解中假设求a34的灵敏度（即股票系数的灵敏度），solvertable 求解后显示如图。X IXQ'30KO1| E6-f-匕 容色丘零 U 串 S：书 JZJ£5S申H牢 K申 u车 mCV7TV?TT 曰1丄1丄 11 1111 X I ©h: ;TFTYT丫 JLn-o-uoooo mouo也11【？口.01-尸丄厂孑丄T Jen :2s 二旌亘¥ hs菖忌2蔓sg宁 fs2置mQerlTs孟口 -a= 一 mTNHC-rlndn,JLI4 亠匸 It - d 台 utlT*_lT*_llI Li宀 r+b-r三口弓ufhfiUEJsLr :wdsz

12、umli WE :二 口 HE J芝二吕工 :了二.：L;ygMCGC nMTsvgo 丄至s士 mMsxgQ-S工 m Ur -n占“唱哇74|吐407三 1 s:呂s:Mg:s:s-6>GEMMCooggc 吕父！>-! V «01 Jcl-lblo-rll.lH 丄；丄 rl 丄 T06OP0GPOO0O0O0Qcllcdc©-IlnoclQ QmLVJllic:*rvril-tlFo100_cf&&#&-j&MMyy<j93 z X3z222只 s22223 2 3 z 2 已*T-J44 1 _x 1 i TTTT

13、rSBs ySy 32'2 z20 M-4理44经0 n n- _u n-clnM.l富孑亍耳丈字茅石斗£中手耳孑富£2孑挙 lq4qu*l<la<laqa4al4-J<lMM14<JqqTlT<i<ld s 11 1 X 1 1 X .1 i 11 丄11.L111LL1IH1丄丄1丄lp l7,r7T77?7l>7l>r7-rrT,f7777r7¥b-77-77, ?&&£ 曰 BLFZ&'GBjG&mvF;Fi!n5.!5,S中旨曰“氏一一: 6666 dridII匸 AS 5do:6s:6666( 5o:o:rl<lw 心 丁 K> £<>丄吞'3.4E«STKI £!. W 珂三殆T?-L1I312K*-匸 f 艮0 _:叮m 忙仁讥T.AT.T.T-队IJ.1-由图可知，当a34时，问题的最优解还是 X1和X3,由此可知，a34 的灵敏度，为a34。因此，若想测定其他系数的灵敏度，只需将 solvertable 的第一行 空格选定相应的单元格便是。4结论与展望通过上述步骤可看出，利用 Excel进行线性规划模型的求解简便、快 捷，表中数值可根据用户要求自行设置，除了在合