运筹学问题的Excel建模及求解

运筹学问题的Excel建模及求解

第十三章 运筹学问题的Excel建模及求解 学习运筹学的目的在于学会用运筹学的方法解决实践中的管理问题，注重学以致用.很多实际问题利用人工计算要经过长时间的艰苦工作才能完成甚至根本无法求解，但若使用运筹学软件则瞬间就能解决.因此在学习过程中不仅要掌握运筹学的基本理论和计算方法，还要充分利用现代化的手段和技术.

微软的电子表格软件（Microsoft Excel）为展示和分析许多运筹学问题提供了一个功能强大而直观的工具，它现在已经被应用于管理实践中.

本章将重点介绍如何建立和求解规划问题的电子表格模型，对于解决大量的中、小规模的实际规划问题，电子表格软件是远远优于传统的代数算法的.

第一节 Excel中的规划求解工具

本节中，我们将举例说明如何使用微软Excel以电子表格的形式建立线性规划模型，并利用Excel中的规划求解工具对模型求解.

一、在Excel中加载规划求解工具

要使用Excel应首先安装Microsoft

Office，然后从屏幕左下角的[开始]—[程

序]中找到Microsoft Excel并启动.在

Excel的主菜单中点击[工具]—[加载

宏]，选择“规划求解”，如图13-1所示.

点击[确定]后，在工具菜单中将增加[规

划求解]选项. 图 13-1

二、在Excel中建立线性规划模型

我们以例2-1为例说明如何在电子表格中建立该问题的线性规划模型.建立电子表格模型时既可以直接利用问题中所给的数据和信息，也可以利用已建立的代数模型.本例的代数模型为：

运筹学问题的Excel建模及求解

目标函数 max Z 200x1 300x2

2x1 2x2 12 x 2x2 8 1 s.t. 4x1 16

4x2 12 x1,x2 0

图 13-2 图 13-3

图13-2显示了将该例的数据转送到电子表格中后所建立的电子表格数学模型（本例是一个线性规划模型）.其中显示数据的单元格称为数据单元格，包括生产每单位药品Ⅰ和Ⅱ所需要的4种设备的台时数（单元格C5：D8），药品Ⅰ和Ⅱ的单位利润（单元格C9：D9），4种设备可用的台时数（单元格G5：G8）.

我们要做的决策是两种药品各生产多少；对这一决策的约束条件是生产两种药品所需的4种设备台时的限制；判断这些决策的优劣程度的指标是生产这两种药品所获得的总利润（决策目标）.

如图13-3所示，将决策变量（药品Ⅰ、Ⅱ的产量）分别放入单元格C10和D10，正好在两种药品所在列的数据单元格的下面.由于不知道这些产量会是多少，故在图13-3中均设为零（空白的单元格默认取值为零.实际上，除负值外的任何一个试验解都可以）.以后在寻找产量最佳组合时这些数值会被改变.因此，含有需要做出决策的单元格称为可变单元格.

两种药品所需的4种设备台时总数分别放入单元格E5至E8，正好在对应数据单元格的右边.由于所需的各种设备台时总数取决两种药品的实际产量，如：E5=C5×C10+D5×D10（可直接将公式写入E5，也可利用SUMPRODUCT 函数，E5=SUMPRODUCT（C5：D5，C10：D10），此函数可以计算若干维数相同的数组的彼此对应元素乘积之和），因此当产量为零时所需各种设备台时的总数也为零.由于E5至E8单元格每个都给出了依赖于可变单元格（C10和D10）的输出结果，它们因此被称为输出单元格.作为输出单元格的结果，4

种设备台时数的总需求

运筹学问题的Excel建模及求解

量不应超过其可用台时数的限制，所以用F列中的 来表示.

两种药品的总利润作为决策目标进入单元格E9，正好位于用来帮助计算总利润的数据单元格的右边.类似于E列的其他输出单元格，E9 = C9×C10+D9×D10或E9 = SUMPRODUCT（C9：D9，C10：D10）.由于它是在对产量做出决策时目标值定为尽可能大的特殊单元格，所以被称为目标单元格.

根据对上述建模过程的总结，在电子表格中建立线性规划模型的步骤可归纳如下：

1．收集问题的数据，并将数据输入电子表格的数据单元格；

2．确定需要做出的决策，并且指定可变单元格显示这些决策；

3．确定对这些决策的限制（约束条件），并将以数据和决策表示的被限制的结果放入输出单元格；

4．选择要输入目标单元格的以数据和决策表示的决策目标.

三、应用电子表格求解线性规划模型

上例的求解过程可通过在Excel的工具菜单中选择“规划求解”开始.“规划求解”对话框如图13-4所示.

“规划求解”开始前，可通

过键入单元格地址或选中单元

格的方式确定模型的每个组成

部分设置在电子表格的何处（单击暂时隐藏对话框，再从工作

图 13-4 表中选定单元格，然后再次单击

）.如目标单元格地址为E9，可变单元

格地址范围为C10：D10，并选中最大值（M）

表示要最大化目标单元格.

约束条件的设定可通过点击对话框中的图 13-5

“添加”按钮，弹出图13-5所示的添加约束对话框.由于各种设备台时的总需求量均不应超过可用台时数的限制，故单元格E5到E8必须小于或等于对应的单元格G5到G8.即在添加约束对话框的左端输入范围E5：E8

（可用选中单元格的方

运筹学问题的Excel建模及求解

式），中间选择<=（点开下拉列表进行选择），右端输入范围G5：G8.如果模型中还包含其他类型的函数约束，则可点击“添加”按钮以弹出一个新的添加约束对话框，根据输出单元格与约束值之间的关系在对话框中间的下拉列表中选择适当的约束类型，以增加新的约束.但本例中已无其他约束了，所以只要点击“确定”按钮返回“规划求解”对话框.如果需要修改或删除已添加的约束，可选中该约束后点击“更改”或“删除” 按钮.

到现在为止“规划求解”对话框

已根据图13-3的电子表格描述了整

个模型（见图13-4）.但在求解模型

前还需要进行最后一个程序，点击“选

项”按钮弹出图13-6所示的选项对话

框，这个对话框中是一些关于如何求

解问题的细节的选项.对于决策变量取图 13-6

值非负的线性规划模型，最主要的选项是“采用线性模型”和“假定非负”选项，（见图13-6）.关于其他选项，对小型问题来说接受图中所示的默认值通常比较合适，点击“确定”按钮返回“规划求解”对话框.

现在可以点击“规划求解”对话框中的“求解”按钮了，它会在后台开始对问题进行求解.对于一个小型问题，几秒钟之后“规划求解”就会显示运行结果.如图13-7所示，它会显示已经找到了一

个最优解.如果模型没有可行解或没有最

优解，对话框会显示“规划求解找不到可

行解”或“设定的单元格值不能集中”.

图 13-7

对话框还显示了产生各种报告

的选项，后面将会介绍.选择“保

存规划求解结果” 并点击“确

定” 按钮，返回电子表格模型.

求解模型之后，如图13-8

所示，

“规划求解”用最优解和图 13-8

运筹学问题的Excel建模及求解

最优值代替了可变单元格和目标单元格中的初始值.因此，最优解是生产4公斤药品Ⅰ和2公斤药品Ⅱ，最优值为1400元，与图解法的结果一致.

图13-9显示的是例2-2的电子表格模型及求解过程.

图 13-9

这个问题的电子表格模型建立与求解过程与例2-1描述的基本相同，数据单元格（C5：E8）、（C9：E9）和（H5：H8）分别存放三种原料B1、B2、B3每斤所含四种营养成分的数量、每斤原 …… 此处隐藏：2742字，全部文档内容请下载后查看。喜欢就下载吧 ……