用Excel求解运筹学问题

极限值报告
Microsoft Excel 9.0 极限值报告 工作表 [Book1]Sheet1 报告的建立: 2006-7-18 10:04:47
目标式 单元格 名字 $G$12 Units Produced Total Profit
值 $3,600
Fra Baidu bibliotek
变量 单元格 名字 $C$12 Units Produced Doors $D$12 Units Produced Windows
C D Optimal Units Produced 16 17 Doors Windows 18 =DoorsProduced =WindowsProduced
E Total Prof it =TotalProf it
(1) 只有一个目标函数系数变动的影响
门的单位利润从$100变到$1000，产品组合的变化
约束 单元格 名字 $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
目标单元格 (最大值) 单元格 名字 $G$12 Units Produced Total Profit
初值 $3,600
终值 $3,600
可变单元格 单元格 名字 $C$12 Units Produced Doors $D$12 Units Produced Windows
初值 2 6
终值 2 6
1. 应用Excel 求解线性规划问题
(1)Excel Solver 的安装 Excel工具菜单中选择加载宏
加载宏以后, 在工具菜单中出现规划求解
(2) 求解如下的线性规划问题
某企业的产品生产数据如下表
分工厂 1 2 单位产品生产时间 每周可利用时间 4小时 12小时
门
1小时 0
窗
0 2小时
3
Unit Profit Optimal Units Produced for Doors $100 $200 $300 $400 $500 $600 $700 $800 $900 $1,000 Doors 4 2 2 2 2 2 2 2 4 4 4 Windows 3 6 6 6 6 6 6 6 3 3 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
Unit Profit Doors $300 Windows $500 Hours Available 4 12 18
Plant 1 Plant 2 Plant 3
Hours Used Per Unit Produced 1 0 0 2 3 2 Doors 0 Windows 0
Units Produced
窗的单位 利润变化 $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) (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)
Units Produced
Total Prof it =SUMPRODUCT(UnitProf it,UnitsProduced)
第三步：约束条件左边项用函数表示
Unit Profit Doors $300 Windows $500 Hours Used 1 2 5 Hours Available 4 12 18 Total Profit $800
第二步：目标单元格，用函数公式表示 并用较醒目的颜色表示。
Unit Profit Doors $300 Windows $500 Hours Available 4 12 18 Total Profit $800
G 11 12
Plant 1 Plant 2 Plant 3
Hours Used Per Unit Produced 1 0 0 2 3 2 Doors 1 Windows 1
用EXCEL求解运筹学问题
主 要 内 容
1. 用Excel Solver 求解线性规划
2. 用Excel Solver Table 进行敏感性分析
3. 用Excel Solver 求解运输问题和指派问题
4. 用Excel Solver求解网络问题
5. 用Excel Solver 做线性回归分析
6.用Excel Solver 进行决策分析
Select these cells (B18:E28), before choosing the Solver Table.
(2) 有两个目标函数系数同时变动的影响
Select these cells (C17:H20), before choosing the Solver Table.
C 25 16 ="(" & DoorsProduced & ", " & WindowsProduced & ")"
门和窗的利润同时变化时,最优解的变化
(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)
５．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.
应用Solver Table 做敏感性分析
单位利润$
3小时
300
2小时
500
18小时
理论模型
max z 300x1 500x2 4 x1 2 x2 12 s.t. 3 x1 2 x2 18 x1 , x2 0
第一步：选择决策变量单元格
决策变量的初始值一般赋0，并用较醒目的颜色表示。
产品生产问题
第四步: 激活规划求解, 确定可变单元格和目标单元格
Unit Profit
Doors $300
Windows $500 Hours Used 1 2 5 Hours Available 4 12 18 Total Profit $800
Plant 1 Plant 2 Plant 3
Hours Used Per Unit Produced 1 0 0 2 3 2 Doors 1 Windows 1
<= <= <=
Units Produced
第六步: 完成求解对话框
第七步:求解方式的选择
第八步: 从求解结果对话框选择所要的报告
Wyndor Glass Co. Product-Mix Problem
Unit Profit Doors $300 Windows $500 Hours Used 2 12 18 Hours Available 4 12 18 Total Profit $3,600
Plant 1 Plant 2 Plant 3
Hours Used Per Unit Produced 1 0 0 2 3 2 Doors 1 Windows 1
<= <= <=
Units Produced
E 5 6 7 8 9
Hours Used =SUMPRODUCT(C7:D7,UnitsProduced) =SUMPRODUCT(C8:D8,UnitsProduced) =SUMPRODUCT(C9:D9,UnitsProduced)
<= <= <=
Units Produced
第五步: 增加约束条件
Unit Profit
Doors $300
Windows $500 Hours Used 1 2 5 Hours Available 1 12 18 Total Profit $800
Plant 1 Plant 2 Plant 3
Hours Used Per Unit Produced 1 0 0 2 3 2 Doors 1 Windows 1
３．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). ４．Launch Excel.
可变单元格 单元格 名字 $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 4 1E+30 2 12 150 12 6 6 18 100 18 6 6 终 递减 目标式 允许的 允许的 值 成本 系数 增量 减量 2 0 300 450 300 6 0 500 1E+30 300
值 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.
Units Produced
Unit Prof it f or Doors $100 $200 $300 $400 $500 $600 $700 $800 $900 $1,000
Optimal Units Produced Doors Windows 2 6
Total Prof it $3,600
Select these cells (B18:E28), before choosing the Solver Table.
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Unit Prof it C Doors $300 D Windows $500 E F G Plant 1 Plant 2 Plant 3 Hours Used Per Unit Produced 1 0 0 2 3 2 Doors 2 Windows 6 Hours Used 2 12 18 <= <= <= Hours Av ailable 4 12 18 Total Prof it $3,600
Plant 1 Plant 2 Plant 3
Hours Used Per Unit Produced 1 0 0 2 3 2 Doors 2 Windows 6
<= <= <=
Units Produced
求解结果报告\灵敏性报告\极限报告
求解结果报告
Microsoft Excel 9.0 运算结果报告 工作表 [Book1]Sheet1 报告的建立: 2006-7-18 10:04:33