用excel规划求解并作灵敏度分析.docx
- 文档编号:11968842
- 上传时间:2023-06-03
- 格式:DOCX
- 页数:16
- 大小:159.12KB
用excel规划求解并作灵敏度分析.docx
《用excel规划求解并作灵敏度分析.docx》由会员分享,可在线阅读,更多相关《用excel规划求解并作灵敏度分析.docx(16页珍藏版)》请在冰点文库上搜索。
用excel规划求解并作灵敏度分析
题目
如何利用EXCEL求解线性规划
问题及其灵敏度分析
第
8组
姓名
学号
乐俊松
090960125
孙然
090960122
徐正超
090960121
崔凯
090960120
王炜垚
090960118
蔡淼
090960117
南京航空航天大学(贸易经济)系
2011年(5)月(3)日
摘要
线性规划是运筹学的重要组成部分,在工业、军事、经济计划等领域有着广泛的应用,但其手工求解方法的计算步骤繁琐复杂。
本文以实际生产计划投资组合最优化问题为例详细介绍了Excel软件的”
规划求解”和“solvertable”功能辅助求解线性规划模型的具体步骤,并对其进行了灵敏度分析。
引言
软件的使用步骤
.4
结果分析
结论与展望
10
参考文献
11
1.引
对于整个运筹学来说,线性规划(LinearProgramming)是形成最早、最成熟的一个分支,是优化理论最基础的部分,也是运筹学最核心的内容之一。
它是应用分析、量化的方法,在一定的约束条件下,对管理系统中的有限资源进行统筹规划,为决策者提供最优方案,以便产生最大的经济和社会效益。
因此,将线性规划方法用于企业的产、销、研等过程成为了现代科学管理的重要手段之一。
⑴
Excel中的线性规划求解和solvertable功能并不作为命令直接显示在菜单中,因此,使用前需首先加载该模块。
具体操作过程为:
在Excel的菜单栏中选择“工具/加载宏”,然后在弹出的对话框中选择“规划求解”和“solvertable”,并用鼠标左键单击“确定”。
加载成功后,在菜单栏中选择“工具/规划求解”,便会弹出“规划求解参数”对话框。
在开始求解之前,需先在对话框中设置好各种参数,包括目标单元格、问题类型(求最大值还是最小值)、可变单元格以及约束条件等。
2软件的使用步骤
“规划求解”可以解决数学、财务、金融、经济、统计等诸多实际问题,在此我们只举一个简单的应用实例,说明其具体的操作方法。
某人有一笔资金可用于长期投资,可供选择的投资机会包括购买国
库券、公司债券、投资房地产、购买股票或银行保值储蓄等。
投资
者希望投资组合的平均年限不超过5年,平均的期望收益率不低于
13%风险系数不超过4,收益的增长潜力不低于10%问在满足上
述要求的前提下投资者该如何选择投资组合使平均年收益率最高?
(不同的投资方式的具体参数如下表。
)
序号
投资方式
投资期限(年)
年收益
率%
风险
系数
增长潜
力%
1
国库券
3
11
1
0
2
公司债券
10
15
3
15
3
房地产
6
25
8
30
4
股票
2
20
6
20
5
短期存款
1
10
1
5
6
长期储蓄
5
12
2
10
7
现金存款
0
3
0
0
解:
设Xi为第I种投资方式在总投资额中的比例,则模型如下:
MaxS=11x1+15x2+25x3+20x4+10x5+12x6+3x7s.t.
3x1+10x2+6x3+2x4+x5+5x65
11x1+15x2+25x3+20x4+10x5+12x6+3x713
x1+3x2+8x3+6x4+x5+2x64
15x2+30x3+20x4+5x5+10x610
x1+x2+x3+x4+x5+x6+x7=1
x1,x2,x3,x4,x5,x6,x70
在EXCE表格中,建立线性规划模型可以通过以下几步完成:
(1)首先将题目中所给数据输入工作表中,包括基础数据、
约束条件等已知信息,如图1所示,其中单元格B&H8是可变单元格,不需要输入任何数据或公式,最后的计算结果将显示
A
C
D
F
G
H
11
国辎
公司情券
房览产
股票
更期存款
现会存款
2
扯鞠限〔年〕
3
1D
6
2
i|
5
C
3
年益率区
11
15
25
亦
10
12
3
风殓系数
1
3
8
6
1
2
U
5
憎长潜力〔協〕
0
15
30
20
或
10
C
6
S
0
D
Q
Q
0
Q
-e-
9
11
0
12
约東量
14
授费離限〔年〕
0
5
.15
0
13
1&
M数
0
q
17
増长潜力区
0
10
J约束条件丿
诃
系数
0
1
其中。
决策变量
基础数据
(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,符号类型选择“<=”。
继续添加约束二、三,点击“添加”,分别选择:
B15C15,B16C16,B17C17,B18=C18完成后选择“确定”,
回到“规划求解参数“。
求解参数右侧有一个“选项”按钮,利用它可以在求解之前对求解过程做一些特定的设置。
本例中的线性规划模型对x1和
x2有非负约束的要求,点击“选项”按钮,弹出“规划求解选项”对话框,该对话框中是关于求解问题的一些更细致的选项,其中最重要的是“采用线性模型”和“假定非负”,确定选择这两项如图5所示,这就告诉Excel求解的是一个线性规划问题,并且为非负约束,这样它将拒绝可变单元格产生负值。
其他选项对于小型计算通常是比较合适的,所以无需进行修改。
点击“确定”回到“规划求解参数”对话框。
彳呆存模型
jWgbOi)I
以上都做好之后点击求解
理划求解站果
報护找到」解・町满足所有的约束
确宦
Typeoftable
取消
规划求解之后点击solvertable功能,选择一维如图
Youcaricreateaonewayortvjo^aytable,dependinganwhetheryouvjantbotestthesensitivityofoutputftoone常tvwinputs»
*Onewaytable
—Twowaytable
跳出新界面后,第一行空格选定要想测定哪个系数的灵敏度设a34所在单元格。
第2行空格设定a34从0.1变换到10,精度为0.1。
第3行空格设定输出X1到X7和目标函数所对应的值。
第4行空格设定从D24单元格开始输出结果,然后求解。
如图
IfyoualreadyranaonewaySolverTableonthissheetsthsprevioussettingsareshown.OF匚oursejyoucanenternewvaluerIFyoulike・
Inputcell:
Valuesofinputtousefortable
4BaseinputvaluesonFonowingi
rninimurnvalue:
ri
Maximumvalue:
Inorernent:
C:
Usethevalusfbelow(separatewithcommas)
Input曲ILies:
Output匚ell(s):
$e$s4H$a,$B$n-
Locatiotiolftable;|$d$24_(upperlefttellgFtable}
Note:
Becareful.Thetable^Jillwriteo^eranythinginItsbvay!
Youmightwanttodeleteanyoldtablesbeforecreating吕nynewones.
3结果分析
规划求解后问题答案自动显示在表格中,如图所示
A
B
C
D
E
F
GH
1
国库券
Q司债券
房地产
股票
短期存款
长期祐蓄
现金存款
2
3
4
投资期限(呂
3
10
6
2
1
5
0
年收益率(%)
11
15
25
20
10
12
3
凤睑系数
1
3
8
6
1
2
0
5
増长潜力匕)
0
15
30
20
5
10
0
6
S
投资金额比例0.57143
00.42SE700
00
9
11
总收益17
12
13nstffi
K
约東量
按资期限「哲
4.28571
5
年收益率〔%)
17
13
风险系数
4
4
17
増长潸力〔%〕
12.8571
10
13
系数
1
1
得最优解:
X1=0.57143,X3=0.42857
平均年收益率=17%即将57.1%的资金投入到国债,42.9%的资金投入到房地产,可以实现最大收益。
然后进行灵敏度分析,刚才求解中假设求a34的灵敏度(即股票系
数的灵敏度),solvertable求解后显示如图
■■
KJLJrlOMXMCJGm
aimm芒誇I.nllT
it
I_l
s了亍
ss^
°:
[-lo0:
-:
i=l口
-■lm;x£7l■■'.-■?
0;4---*-J:
±x111i1
©
0-ol=PT:
一00^-0
111
丄1丄丄1
X-1I1
uoLJhrsIII00c
IJOMoM600口口nn0nM0-0u000000oobo
Qc二.<=□ocooc口-rlcQcM00000000^n-a-n-
□・^lESSTl
◎・-J2WS?
丄y21.P
O.42S&71420
O.£12SB71
O・<12813?
142^O_a2SE71<32y■J・4空直£F!
_◎2J口.aabbT3qz旦
O-崛!
213吕〒:
LQN日
匚99『•制y些ym93M2I-.LI32222aq4Tgcd1u11X.11儿11TT7TT77Y77■ITLITXGEF.liE>'"IrHrwosNCCM「MNNHMbi:
:
L!
2
O-<1^-9
G
IJ
u
O
o
OrinOo
Qb电:
Z吕曰*71勺它勺0IO.439BST1
sis
1Ml3
1qB
1-J2
1di3
13
4
71ri
T1
ITr_T777r
3SL-EB匚BL-
-IW・:
■I.二?
•-;'..»..
4Q
11
77
qad4af
11ALL11
777T/7
F■.'KBL.!
口3R
3333
4-J4/
777rsn-£b
0-Mo'
3:
<
”.-1
sc's
由图可知,当a34>5.4时,问题的最优解还是X1和X3,由此可知,a34的灵敏度,为a34>5.4。
因此,若想测定其他系数的灵敏度,只需将solvertable的第一行空
格选定相应的单元格便是。
4结论与展望
通过上述步骤可看出,利用Excel进行线性规划模型的求解简便、快捷,表中数值可根据用户要求自行设置,除了在合理安排产品的生产决策可使用外,对于研究如何合理使用企业各项经济资源,以及研究如何统筹安排,对人、财、物等现有资源进行优化组合、实现最大效能等均可参照使用,能有效地提高组织决策的速度及准确性,而Excel办公软件的普遍性优点使之更适合于促进科学决策的信息化水平。
[2]
5参考文献
1.《如何利用EXCEL求解线性规划问题及其灵敏度分析》孙爱萍
王瑞梅
2.张纯义.Excel用于生产决策的线性规划法【J】.会计之友,
2005.1O.
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- excel 规划 求解 灵敏度 分析