用excel规划求解并作灵敏度分析.docx

用excel规划求解并作灵敏度分析.docx

《用excel规划求解并作灵敏度分析.docx》由会员分享，可在线阅读，更多相关《用excel规划求解并作灵敏度分析.docx（7页珍藏版）》请在冰豆网上搜索。

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

题目

如何利用EXCEL求解线性规划

问题及其灵敏度分析

第8组

姓名学号

乐俊松090960125

孙然090960122

徐正超090960121

崔凯090960120

王炜垚090960118

蔡淼090960117

南京航空航天大学（贸易经济）系

2011年（5）月（3）日

摘要

线性规划是运筹学的重要组成部分，在工业、军事、经济计划等领域有着广泛的应用，但其手工求解方法的计算步骤繁琐复杂。

本文以实际生产计划投资组合最优化问题为例详细介绍了Excel软件的”规划求解”和“solvertable”功能辅助求解线性规划模型的具体步骤，并对其进行了灵敏度分析。

引言………………………………………………………………4

软件的使用步骤……………………………………………………….4

结果分析………………………………………………….9

结论与展望……………………………………………………………10

参考文献……………………………………………………………11

1.引言

对于整个运筹学来说，线性规划（LinearProgramming）是形成最早、最成熟的一个分支，是优化理论最基础的部分，也是运筹学最核心的内容之一。

它是应用分析、量化的方法，在一定的约束条件下，对管理系统中的有限资源进行统筹规划，为决策者提供最优方案，以便产生最大的经济和社会效益。

因此，将线性规划方法用于企业的产、销、研等过程成为了现代科学管理的重要手段之一。

[1]

Excel中的线性规划求解和solvertable功能并不作为命令直接显示在菜单中，因此，使用前需首先加载该模块。

具体操作过程为：

在Excel的菜单栏中选择“工具／加载宏”，然后在弹出的对话框中选择“规划求解”和“solvertable”，并用鼠标左键单击“确定”。

加载成功后，在菜单栏中选择“工具／规划求解”，便会弹出“规划求解参数”对话框。

在开始求解之前，需先在对话框中设置好各种参数，包括目标单元格、问题类型（求最大值还是最小值）、可变单元格以及约束条件等。

2软件的使用步骤

“规划求解”可以解决数学、财务、金融、经济、统计等诸多实

际问题，在此我们只举一个简单的应用实例，说明其具体的操作

方法。

某人有一笔资金可用于长期投资,可供选择的投资机会包括购买国库券、公司债券、投资房地产、购买股票或银行保值储蓄等。

投资者希望投资组合的平均年限不超过5年，平均的期望收益率不低于13%，风险系数不超过4，收益的增长潜力不低于10%。

问在满足上述要求的前提下投资者该如何选择投资组合使平均年收益率最高？

（不同的投资方式的具体参数如下表。

）

解：

设xi为第I种投资方式在总投资额中的比例，则模型如下：

MaxS=11x1+15x2+25x3+20x4+10x5+12x6+3x7

s.t.

3x1+10x2+6x3+2x4+x5+5x6≤5

11x1+15x2+25x3+20x4+10x5+12x6+3x7≥13

x1+3x2+8x3+6x4+x5+2x6≤4

15x2+30x3+20x4+5x5+10x6≥10

x1+x2+x3+x4+x5+x6+x7=1

x1,x2,x3,x4,x5,x6,x7≥0

在EXCEL表格中，建立线性规划模型可以通过以下几步

完成：

（1）首先将题目中所给数据输入工作表中，包括基础数据、

约束条件等已知信息，如图1所示，其中单元格B8、H8是可变

单元格，不需要输入任何数据或公式，最后的计算结果将显示

其中。

（2）将目标方程和约束条件的对应公式输入各单元格中，回

车后以下四个单元格均显示数字“0”。

B11=SUMPR0DUCT（B3：

H3，B8：

H8）

B14=SUMPR0DUCT（B2：

H2，B8：

H8）

B15=SUMPR0DUCT（B3：

H3，B8：

H8）

B16=SUMPR0DUCT（B4：

H4，B8：

H8）

B17=SUMPR0DUCT（B5：

H5，B8：

H8）

B18=SUM（B8：

H8）

线性规划问题的电子表格模型建好后，即可利用“规划求

解”功能进行求解。

针对图1的电子表格模型，在工具菜单中选择“规划求解”命令，弹出“规划求解参数”窗口。

在该对话框中，目标单元格选择B11，问题类型选择“最大值”，可变单元格选择B8：

H8，点击“添加”按钮，弹出“添加约束”对话框，根据所建模型，共有三个约束条件，针对约束一：

3x1+10x2+6x3+2x4+x5+5x6≤5，左端“单元格引用位置”应选择输入B14，右端输入C14，符号类型选择“<=”。

继续添加约束二、三，点击“添加”，分别选择：

B15≥C15，B16≤C16，B17≥C17，B18=C18完成后选择“确定”，回到“规划求解参数“。

求解参数右侧有一个“选项”按钮，利用它可以在求解之前

对求解过程做一些特定的设置。

本例中的线性规划模型对x1和

x2有非负约束的要求，点击“选项”按钮，弹出“规划求解选项”

对话框，该对话框中是关于求解问题的一些更细致的选项，其中

最重要的是“采用线性模型”和“假定非负”，确定选择这两项如

图5所示，这就告诉Excel求解的是一个线性规划问题，并且为

非负约束，这样它将拒绝可变单元格产生负值。

其他选项对于小

型计算通常是比较合适的，所以无需进行修改。

点击“确定”回到

“规划求解参数”对话框。

以上都做好之后点击求解。

规划求解之后点击solvertable功能，选择一维如图

跳出新界面后，第一行空格选定要想测定哪个系数的灵敏度设a34所在单元格。

第2行空格设定a34从0.1变换到10，精度为0.1。

第3行空格设定输出X1到X7和目标函数所对应的值。

第4行空格设定从D24单元格开始输出结果，然后求解。

如图

3结果分析

规划求解后问题答案自动显示在表格中，如图所示

得最优解：

X1=0.57143，X3=0.42857

平均年收益率=17%

即将57.1%的资金投入到国债，42.9%的资金投入到房地产，可以实现最大收益。

然后进行灵敏度分析，刚才求解中假设求a34的灵敏度（即股票系数的灵敏度），solvertable求解后显示如图。

由图可知，当a34>5.4时，问题的最优解还是X1和X3，由此可知，a34的灵敏度,为a34>5.4。

因此，若想测定其他系数的灵敏度，只需将solvertable的第一行空格选定相应的单元格便是。

4结论与展望

通过上述步骤可看出，利用Excel进行线性规划模型的求解简便、快捷，表中数值可根据用户要求自行设置，除了在合理安排产品的生产决策可使用外，对于研究如何合理使用企业各项经济资源，以及研究如何统筹安排，对人、财、物等现有资源进行优化组合、实现最大效能等均可参照使用，能有效地提高组织决策的速度及准确性，而Excel办公软件的普遍性优点使之更适合于促进科学决策的信息化水平。

[2]

5参考文献

1.《如何利用EXCEL求解线性规划问题及其灵敏度分析》孙爱萍王瑞梅

2.张纯义．Excel用于生产决策的线性规划法【J】．会计之友，

2005．1O．