Excel求解运筹学问题

第四步: 激活规划求解, 确定可变单元格和目标单元格
Unit Profit
Plant 1 Plant 2 Plant 3
Units Produced
Doors $300
Windows $500
Hours Used Per Unit Produced
1
0
0
2
3
2
Doors 1
Windows 1
Hours Used
Units Produced
Doors 1
Windows 1
Total Profit $800
E
5
Hours
6
Used
7 =SUMPRODUCT(C7:D7,UnitsProduced)
8 =SUMPRODUCT(C8:D8,UnitsProduced)
9 =SUMPRODUCT(C9:D9,UnitsProduced)
(4,3) (4,3) (4,3)
(4,3) (4,3) (4,3)
(4,3) (4,3) (4,3)
(4,3) (4,3) (4,3)
(4,3) (4,3) (4,3)
(4,3) (4,3) (4,3)
(4,3) (4,3) (4,3)
(4,3) (4,3) (4,3)
$500 (2,6) (2,6) (2,6) (2,6) (2,6) (2,6) (2,6) (4,3) (4,3) (4,3) (4,3) (4,3) (4,3) (4,3) (4,3) (4,3)
x1
4
s.t.3x1

2x2 2 x2
12 18
x1, x2 0
每周可利用时间
4小时 12小时 18小时
第一步：选择决策变量单元格 决策变量的初始值一般赋0，并用较醒目的颜色表示。
产品生产问题
Unit Profit
Doors $300
Windows $500
Plant 1 Plant 2 Plant 3
Unit Profit
Doors $300
Windows $500
Plant 1 Plant 2 Plant 3
Hours Used Per Unit Produced
1
0
0
2
3
2
Hours Used
2 12 18
Hours
Available
<=
4
<=
12
<=
18
Units Produced
Doors 2
初值 $3,600
终值 $3,600
可变单元格
单元格
名字
$C$12 Units Produced Doors
$D$12 Units Produced Windows
初值 2 6
终值 2 6
约束
百度文库单元格
名字
$E$7 Plant 1 Used
$E$8 Plant 2 Used
$E$9 Plant 3 Used
单元格值 公式
状态
2 $E$7<=$G$7 未到限制值
12 $E$8<=$G$8 到达限制值
18 $E$9<=$G$9 到达限制值
型数值 2 0 0
灵敏性报告
Microsoft Excel 9.0 敏感性报告 工作表 [Book1]Sheet1 报告的建立: 2006-7-18 10:04:40
可变单元格
1
0
0
2
3
2
Doors 1
Windows 1
Hours Used
1 2 5
Hours
Available
<=
1
<=
12
<=
18
Total Profit $800
第六步: 完成求解对话框 第七步:求解方式的选择
第八步: 从求解结果对话框选择所要的报告
Wyndor Glass Co. Product-Mix Problem
$600 (2,6) (2,6) (2,6) (2,6) (2,6) (2,6) (2,6) (2,6) (4,3) (4,3) (4,3) (4,3) (4,3) (4,3) (4,3) (4,3)
1 2 5
Hours
Available
<=
4
<=
12
<=
18
Total Profit $800
第五步: 增加约束条件
Unit Profit
Plant 1 Plant 2 Plant 3
Units Produced
Doors $300
Windows $500
Hours Used Per Unit Produced
2
6
13
14
15
16
Unit Profit
Optimal Units Produced
17
for Doors
Doors
Window s
18
2
6
19
$100
20
$200
21
$300
22
$400
23
$500
24
$600
25
$700
26
$800
27
$900
28
$1,000
E
F
G
Hours Used
2 12 18
Select these cells (B18:E28), before choosing the Solver Table.
(2) 有两个目标函数系数同时变动的影响
Select these cells (C17:H20), before choosing the
Solver Table.
C 1265 ="(" & DoorsProduced & ", " & WindowsProduced & ")"
450
300
6
0
500 1E+30
300
终 阴影 约束 允许的 允许的
值 价格 限制值 增量 减量
20
4 1E+30
2
12 150
12
6
6
18 100
18
6
6
极限值报告
Microsoft Excel 9.0 极限值报告 工作表 [Book1]Sheet1 报告的建立: 2006-7-18 10:04:47
第三步：约束条件左边项用函数表示
Unit Profit
Doors $300
Windows $500
Plant 1 Plant 2 Plant 3
Hours Used Per Unit Produced
1
0
0
2
3
2
Hours Used
1 <= 2 <= 5 <=
Hours Available
4 12 18
３．Save the Solver Table.xla file to the exact same location as the Solver.xla file (C:\program files\Microsoft Office\ Office\Library\Solver\Solver.xla) (If it is not, use the Find command to find the Solver.xla file).
单元格
名字
$C$12 Units Produced Doors
$D$12 Units Produced Windows
约束
单元格
名字
$E$7 Plant 1 Used
$E$8 Plant 2 Used
$E$9 Plant 3 Used
终 递减 目标式 允许的 允许的
值 成本 系数 增量 减量
2
0
300
Windows 6
Total Profit $3,600
求解结果报告\灵敏性报告\极限报告
求解结果报告
Microsoft Excel 9.0 运算结果报告 工作表 [Book1]Sheet1 报告的建立: 2006-7-18 10:04:33
目标单元格 (最大值)
单元格
名字
$G$12 Units Produced Total Profit
for Doors Doors Windows
4
3
$100
2
6
$200
2
6
$300
2
6
$400
2
6
$500
2
6
$600
2
6
$700
2
6
$800
4
3
$900
4
3
$1,000
4
3
Total Profit $5,500 $3,200 $3,400 $3,600 $3,800 $4,000 $4,200 $4,400 $4,700 $5,100 $5,500
Hours
Available
<=
4
<=
12
<=
18
Total Profit $3,600
Total Prof it $3,600
Select these cells (B18:E28), before choosing the Solver Table.
C
D
16
Optimal Units Produced
17
Doors
Window s
18 =DoorsProduced =Window sProduced
E Total Prof it =TotalProf it
(1) 只有一个目标函数系数变动的影响 门的单位利润从$100变到$1000，产品组合的变化
Unit Profit Optimal Units Produced
门和窗的利润同时变化时,最优解的变化
(2,6)
$100
门 $200 的 $300 单 $400 位 $500 利 $600 润 $700 变 $800 化 $900
$1,000
$1,100
$1,200
$1,300
$1,400
$1,500
$1,600
$100 (2,6) (4,3) (4,3) (4,3) (4,3) (4,3) (4,3) (4,3) (4,3) (4,3) (4,3) (4,3) (4,3) (4,3) (4,3) (4,3)
窗的单位 利润变化
$200
$300 $400
(2,6) (2,6) (2,6)
(2,6) (2,6) (2,6)
(4,3) (2,6) (2,6)
(4,3) (2,6) (2,6)
(4,3) (4,3) (2,6)
(4,3) (4,3) (4,3)
(4,3) (4,3) (4,3)
(4,3) (4,3) (4,3)
Hours Used Per Unit Produced
1
0
0
2
3
2
Units Produced
Doors 1
Windows 1
Hours Available
4 12 18
Total Profit $800
G
11
Total Profit
12 =SUMPRODUCT(UnitProfit,UnitsProduced)
４．Launch Excel.
５．Under the Tools menu, choose the "Add-Ins" command.
６．Click the Solver Table checkbox to have Solver Table load with Excel every time it is loaded.
Hours Used Per Unit Produced
1
0
0
2
3
2
Units Produced
Doors 0
Windows 0
Hours Available
4 12 18
第二步：目标单元格，用函数公式表示 并用较醒目的颜色表示。
Unit Profit
Doors $300
Windows $500
Plant 1 Plant 2 Plant 3
用EXCEL求解运筹学问题
主要内容
1. 用Excel Solver 求解线性规划 2. 用Excel Solver Table 进行敏感性分析 3. 用Excel Solver 求解运输问题和指派问题 4. 用Excel Solver求解网络问题 5. 用Excel Solver 做线性回归分析 6.用Excel Solver 进行决策分析
应用Solver Table 做敏感性分析
B
C
D
3
Doors
Window s
4
Unit Profit
$300
$500
5
6
Hours Used Per Unit Produced
7
Plant 1
1
0
8
Plant 2
0
2
9
Plant 3
3
2
10
11
Doors
Window s
12 Units Produced
1. 应用Excel 求解线性规划问题
(1)Excel Solver 的安装 Excel工具菜单中选择加载宏
加载宏以后, 在工具菜单中出现规划求解
(2) 求解如下的线性规划问题
某企业的产品生产数据如下表
分工厂
单位产品生产时间
门
窗
1
1小时
0
2
0
2小时
3
3小时
2小时
单位利润$
300
500
理论模型
max z 300x1 500x2
目标式
单元格
名字
$G$12 Units Produced Total Profit
值 $3,600
变量
单元格
名字
$C$12 Units Produced Doors
$D$12 Units Produced Windows
值 2 6
下限 目标式
极限 结果
0 3000
0
600
上限 目标式 极限 结果
2 3600 6 3600
2. 用Excel Solver Table 进行敏感性分析
Solver Table宏的安装使用
１．Be sure that the Solver is installed. If it is, it should appear under the Tools menu.
２．Quit Excel if it is currently running.