运筹学上机指导书--EXCEL部分

1)首先将基础数据、决策变量、目标方程、约束条件输入工作簿中。 2)在工具菜单中选择规划求解命令将出现规划求解参数窗口。在设置目标单元格的位置输入目 标单元格的代号；选定最大或最小；在可变单元格中输入决策变量单元格的代号；见图 3-2。 3)点击添加按钮出现添加约束对话框，在单元格引用位置输入约束的 LHS，选择约束符号类型， 在约束值位置输入相应的 RHS，如此重复添加各个约束条件；见图 3-3。 4)点击选项按钮进入规划求解选项框，选定采用线性模型和假定非负选项框，然后点击确定； 见图 3-4。 5)点击求解按钮进入规划求解结果对话框，选定保存规划求解结果复选框，点击确定按钮则得 到求解的结果，见图 3-5。
② 对偶问题的最优解；
③ 目标函数价值系数的变化范围；
④ 右端常数的变化范围。
（2）对产品I进行改进，改进后系数列向量为（9，12，4）T，价值系数为 4.5
① 原问题的最优解（包括决策变量和松弛变量）、最优值；
② 对偶问题的最优解；
③ 目标函数价值系数的变化范围；
④ 右端常数的变化范围；
⑤ 对原问题的最优解有什么影响。
首先检查你的 Excel“工具”菜单中是否有“规划求解”项，如果没有该项，则通过“工具”
菜单中的“加载宏”选项添加。下面以例 1 为例说明用 Excel 求解规划模型的过程。
【例 1】利用原材料和设备资源生产甲乙产品的生产计划问题，原始数据如下表：
甲
乙
资源约束
原材料
1
2
8
设备 A
4
0
16
设备 B
0
4
max z = 3 x1 + 2 x2 + 2.9x3
⎧8 x1 + 2 x2 + 10 x3 ≤ 300
⎪⎪⎪⎨120xx1 1++153
x x
2 2
+ +
8 x3 ≤ 400 10 x3 ≤ 420
⎪⎩ x1 , x2 , x3 ≥ 0
（1）求解：① 原问题的最优解（包括决策变量和松弛变量）、最优值；
1、
max z = x1 + 3 x2
5x1 +10x2 ≤50 x1 + x2 ≥ 1
x2 ≤ 4
x1 , x2 ≥ 0
① 原问题的最优解（包括决策变量和松弛变量）、最优值；
② 对偶问题的最优解；
③ 目标函数价值系数的变化范围；
④ 右端常数的变化范围。
（注：第②③④问从灵敏度分析表得出，下题同）
2、
图 3-6 运算结果报告窗口 在图 3-6 中有上中下三部分内容，其中上面是目标函数的优化结果值，中间是决策变量的优化 结果值，下面是约束条件在最优结果下的状态描述。 除了上述运算结果报告之外，在图 3-5 中还可以选择敏感性报告选择项，点击确定后就会出现 相应的敏感性报告如图 3-7 所示。在此报告中分成上下两部分，上部分是对决策变量目标系数的灵 敏度分析，给出了目标系数的当前值和允许的增量和减量；下半部分是对各个约束条件右端常数项 的灵敏度分析，给出了约束左端的实际值、右端常数项的当前值以及允许的增量和允许的减量。
+ 2
)
+
P3
d
+ 4
解：（1）第一步优化
⎧ ⎪
x1
⎪ x1
+
x2
+
d
− 1
−
d
+ 1
= 10
+
d
− 2
−
d
+ 2
=
4
⎪ ⎨5 x1
+ 3x2
+
d
− 3
−
d
+ 3
= 56
⎪ ⎪
x1
+
x2
+
d
− 4
−
d
+ 4
= 12
⎪⎩
x1
,
x
2
,
x
s
,
d
− i
,
d
+ i
≥
0, i
=
1,2,3
首先将基础数据、目标函数和约束条件格式化到 EXCEL 表中，见图 3－10。实线单元格中为已
图 3－10
图 3－11
7
运筹学实验指导书（一）
图 3－12 图 3－13
图 3－14
8
运筹学实验指导书（一）
图 3－15
4.3 最大流问题、最短路问题和网络计划问题的 Excel 优化求解
以上三种问题的求解思路非常相似，此处仅举网络计划问
B
题为例，其它两中方法见课本。
2 11 5
【例 3】寻找网络图 3－16 的关键路线
运筹学实验指导书（一）
运用 EXCEL 求解运筹学问题
一、实验性质和教学目的
本实验是管理及经济类本科生运筹学课程的上机操作实验，实验的内容是本科生阶段运筹学 Ⅰ的所有内容，主要包括线性规划、整数规划、运输问题、目标规划、动态规划、图与网络、网络 计划等。实验目的在于使学生掌握应用计算机工具解决运筹学模型优化求解的方法步骤，熟悉各种 运筹学优化软件的使用，特别是 Excel 优化功能的使用，为今后在实际工作中解决大型的实际问题 优化模型奠定基础。同时，通过熟悉优化软件的操作激发同学的学习兴趣，提高本课程的教学效果。
12
单件利润
2
3
其规划模型如下：
max z = 2x1 + 3x2
⎧x1 + 2x2 ≤ 8
⎪⎪4 ⎨
x1
⎪
4 x2
≤ 16 ≤ 12
⎪⎩x1, x2 ≥ 0
1. 在 Excel 电子表格中建立线性规划模型 把线性规划模型转化为 Excel 电子表格文件形式。其表现形式可以多种多样，但应保持模型的
组织性、逻辑性、直观性、易操作性。为此，把制作表格的过程分成四个部分：数据、决策变量、 目标方程、约束。见图 3-1
知数据，虚线单元格中为决策变量，细线单元格中为正负偏差变量，双线单元格中为第一次优化目
标函数，实际值为各个目标约束中的目标实现值，合计值为各个目标约束的左端项。
6
运筹学实验指导书（一） 利用规划求解功能进行第一步优化，规划求解参数框设置见图 3－11。注意：可变单元格应该 包括决策变量和正负偏差变量；通过选项选择“采用线性模型”和“假定非负”。求解之后的结果 见图 3－12。 （2）第二步优化 第二步优化与第一步优化的差别在于，在规划求解的过程中，目标单元格设定为 D12，增加一 个约束条件$F$6＝0，见图 3－13。求解出来的结果见图 3－14。 （3）第三步优化 在第二步优化的基础上，在规划求解过程中，将目标单元格设定为 D13，再增加两个约束条件 $E$4＝6 和$E$5＝0，见图 3－15。求解出来的结果与第二步优化结果相同。
（EXCEL 部分） （第五版修改稿） 孔造杰 赵文燕 编纂
河北工业大学管理学院
2009 年 9 月
目录
一、实验性质和教学目的 .................................................................................................... 2 二、实验软件 ........................................................................................................................ 2 三、软件使用说明 ................................................................................................................ 2 四、实验内容 ...................................................................................................................... 10 五、报告要求 ...................................................................................................................... 13
（二）整数规划：写出下面问题的最优解和最优值
（1）
max z = 4x1 + 6x2 + 2x3
来自百度文库⎧4x1 − 4x2 ≤ 5
⎪⎪− ⎪⎨−
x1 x1
+ +
6x2 ≤ 5 x2 + x3
≤
5
决策变量值（相应的单元格为值）的方程式及运算结果。 4)约束方程：通常将每一个约束分成左端项 LHS、右端此项 RHS 和约束符号三部分分别放在三
个单元格中，任何常量和决策变量元素的结合均可加入到约束中，但对于每一个约束而言，LHS 和 RHS 都必须非空（至少有一个元素），包括非负条件在内。一个较好的处理方法就是将 LHS 作为一列， 而将 RHS 作为相邻的另一列，中间为符号说明列。 2. 在电子表格中优化线性规划模型
二、实验软件
软件名称：MS-office Excel 电子表格软件 开 发 者：Microsoft 软件内容：Office Excel 规划求解软件包及相关挂接软件包
三、软件使用说明
Excel 电子表格中的工具菜单中有一个“规划求解”选项，它可以通过简单的程序方法在一个
表格中求解线性规划等规划模型。
在图 3-5 所示规划求解结果窗口中，提示规划求解找到一解可以满足所有约束及最优状况，点 击确定按钮就会将优化的结果显示在 Excel 的界面中，决策变量及目标函数的位置就会出现相应的 优化结果值。优化的结果还可以以另一种形式出现，即在该窗口中选择运算结果报告项，点击确定
4
运筹学实验指导书（一） 就会出现相应的结果报告如图 3-6。
A6
F6
8G
求解思路见图 3－17，图中阴影部分为求解 1
C 10
3
D 5
E 44
6
之后的结果，公式、名称定义和求解思路在下部。
图 3－16
图 3－17 关键路线为：1→2→5→6，或者说：A→B→G，路线长为 25
9
四、实验内容
运筹学实验指导书（一）
（一） 线性规划问题: 用EXCEL表求解下面各题，并从求解结果中读出下面要求的各项，明确写 出结果。例如：原问题最优解为X*=（4，2）T
图 3-7 敏感性报告窗口 4. 其它规划模型的 Excel 求解方法
4.1 运输问题用 EXCEL 求解 此处给出一个产销不平衡运输问题示例。图 3－8 是将运输问题规划到 EXCEL 表中，求解结果略。
5
运筹学实验指导书（一）
图 3－8 4.2 整数规划及 0-1 规划模型的求解 在用 Excel 求解整数规划和一般 0-1 规划时，基本上与解线性规划相同，不同之处就是要把决 策变量的取整数要求或取 0-1 值的要求作为约束条件输入到模型中，通过添加约束窗口实现这一要 求，如图 3-9 所示。在此对话框中，左侧输入要求取整数决策变量的位置，在符号选择下拉列表中 选择 int(integer)项，则右侧自动出现整数二字，如果是 0-1 变量则在符号下拉列表中选择 bin(binary),右端就会自动出现二进制三个字。其它操作同线性规划。 匈牙利法的形式和运输问题类似，只需将变量变为 0－1，输入量和输出量分别变为 1 既可。
1)数据部分：数据是模型处理的基础，原始数据通过计算而生成其他数据，为了便于数据的使 用，应尽可能将数据集中安排在一个便于组织的表格中；2)决策变量：决策变量通过名称等对元素 加以区分，并将最优计算结果自动填入其中，为此，每一个单元格对应一个决策变量，并在决策变
2
运筹学实验指导书（一）
量的上面或旁边设置说明文字来进行标记，以便于区别。 3)目标方程：该部分包括目标价值所必要的元素，目标方程中将含有数据部分的数据和未知的
函数进行优化，然后再优化次一级的目标，这时要把上一级及更高级的优化结果作为约束加入到本
等级的优化过程中，依此类推直至最后一级目标优化完毕为止，最后一级的优化结果就是整个目标
规划优化的结果。下面用例题说明。 【例 2】 用 EXCEL 求解多目标规划问题
min z
=
P1
d
− 3
+
P2
(
2d
+ 1
+
3d
图 3-9 限定决策变量取整
4.2 目标规划模型的 Excel 求解
目标规划是解决多目标规划问题的较好的方法，由于规划目标的多样性以及规划目标的优先等
级的不可逾越性，在用 Excel 求解目标规划时通常采用逐级优化法。逐级优化法是基于各个目标的
优先等级逐次优化，首先优化优先等级最高的目标，这时以该等级目标方程中的偏差变量作为目标
决策变量
图 3-1 格式化规划模型到 Excel 中 目标方程
3
基础数据
此单元格代表 产品 I 的产量需
此单元格代表产 品 II 的产量 此单元格代表目 标函数，格中输 入的是公式
这三个单元格分 别填入约束条件 左端公式
运筹学实验指导书（一）
图 3-3 添加约束窗口
图 3-4 规划求解选项窗口
图 3-5 规划求解结果窗口 3. 优化结果及灵敏度分析