学生用_实验指导书_excel线性规划实验

实验指导书

《管理决策模型与方法》

学院（部）管理学院

指导教师金玉兰

实验1 EXCEL 线性规划实验

一、实验目的

1、掌握应用Excel软件求解线性规划问题；

2、掌握应用Excel软件对线性规划问题进行灵敏度分析；

3、掌握应用Excel软件求解整数规划问题；

4、掌握应用Excel软件求解0-1整数规划问题。

二、实验设备、仪器及所需材料

配置在Pentium Ⅲ，存128M以上的电脑；装有Microsoft Windows操作系统及Microsoft Office 2003工作软件。

三、实验原理

“规划求解”是Microsoft Excel 中的一个加载宏，借助它可以求解许多运筹学中的数学规划问题。

安装Office 2003 的时候，系统默认的安装方式不会安装该宏程序，需要用户自己选择安装。安装方法为：从Excel 菜单中选择“工具”→“加载宏”，打开如下对话框：

选择其中的“规划求解”后单击“确定”按钮，会出现提示：“这项功能目前尚未安装，是否现在安装?”，选择“是”，系统要你插入Office 的安装光盘，准备好后单击确定，很快就会安装完毕。于是，你会发现在“工具”菜单下多出一个名为“规划求解”的子菜单，说明“规划求解”功能已经成功安装。

在EXCEl2007版本中，通过点击“office按钮”，“EXCEL选项”→“加载项”→转到“EXCEL

加载项”，然后加载【规划求解加载项】便可以加载规划求解的宏。

在EXCEl2010版本中，通过点击“文件”选项卡打开“Excel选项”对话框，单击左侧 “加载项”标签，在右侧单击“转到”按钮，打开“加载宏”对话框，勾选“规划求解加载项”复选框，单击“确定”按钮，即可在工具栏的“数据”选项卡中出现 “分析”选项组，上面就有了“规划求解”按钮。

利用“规划求解”功能，就可以进行线性规划问题的求解。

例如：用EXCEL 求解数学规划问题

12121

212maxZ 2328416..4120, 0

x x x x x s t x x x =++≤⎧⎪≤⎪⎨≤⎪⎪≥≥⎩

步骤：

1． 将模型中的目标函数和约束条件的系数输入到单元格中；为了使我们在操作过程中看得

更清楚，可以附带输入相应的标识符，并给表格加上边框。如下图所示：

2．在E4 单元格（目标值）输入“=SUMPRODUCT($C$3:$D$3, C4:D4)”；其中，SUMPRODUCT 函数的功能是将数组间对应的元素相乘，并返回乘积之和，即SUMPRODUCT($C$3:$D$3, C4:D4)＝C3×C4＋D3×D4；$C$3:$D$3 表示这几个单元格为绝对引用。

3．用拖动的方式将E4 单元格的公式复制到E5～E7，如下图：

4．从“工具”选择“规划求解”，打开“规划求解参数”窗口。Office 2007中，从“数据”选择“规划求解”，打开“规划求解参数”窗口。

5．将窗口中的目标单元格设为$E$4，可变单元格设为$C$3:$D$3，目标为求最大值，如下图：

6．单击“添加”按钮，打开“添加约束”对话框；将单元格引用位置设为$E$5:$E$7，

约束值设为$G$5:$G$7，不等式符号为<=，如下图：

7．选择“确定”返回“规划求解参数”窗口；单击“选项”按钮，弹出“规划求解选项”窗口，并选中其中的“采用线性模型”和“假定非负”两项，其余选项可保留默认值。如下图：

8．选择“确定”按钮返回“规划求解参数”窗口；单击“求解”按钮，系统弹出

“规划求解结果”对话框，如下图：

9．将对话框右边“报告”下的全部容选中（也可不选）后，单击“确定”按钮完成计算，结果如下图。可知该问题的最优解为：x1＝4，x2＝2，max Z =14。

四、实验容

（一）用EXCEL 求解线性规划问题

121212

1212maxZ 12102160131340..322600, 0

x x x x x x s t x x x x =++≤⎧⎪+≤⎪⎨

+≤⎪⎪≥≥⎩

（二）用EXCEL 求解线性规划问题，并进行灵敏度分析

1212121212maxZ 2030240240..250, 0

=++≤⎧⎪+≤⎪⎨

+≤⎪⎪≥≥⎩x x x x x x s t x x x x

（三）用EXCEL 求解整数规划问题

12

1212maxZ 320.5 4.5..2314

0, 1,2=++≤⎧⎪+≤⎨⎪≥=⎩i

x x x x s t x x x i 且为整数

（四）用EXCEL 求解0－1整数规划问题

如果每一个客户只需要一个项目负责人，那么怎么进行分配才能使项目负责人完成这三

（五）用EXCEL 求解下列资源分配问题

某昼夜服务的公交线路每天各时间段所需司机人数如下：

设司机在各时间段一开始时上班，并连续工作八小时，问该公交线路该怎样安排司机人数，既能满足工作需要，又配备最少司机？数学模型如下，求用EXCEL 求解。

1234561612233445

56minZ 807080..5020300, 1,2,3,4,5,6

=++++++≥⎧⎪

+≥⎪⎪+≥⎪+≥⎨⎪+≥⎪⎪+≥⎪

≥=⎩i x x x x x x x x x x x x s t x x x x x x x

i 且为整数

（六）请各位同学按照学号选做以下题目

要求：请至少完成与学号尾数相对应题号的题目。选做其他题目，则按题目数量和

准确率进行加分。