EXCEL数学规划建模

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

要使用Excel 应首先安装Microsoft

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

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

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

宏]，选择“规划求解”，如图所示.点击[确

定]后，在工具菜单中将增加[规划求解]

选项.

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

实际例子：某药品厂生产两种药品，药品1和药品2，要用到设备ABCD ，药品1需要四种设备的台时数分别为2、1、4、0，药品2需要台时数分别为2、2、0、4，设备ABCD 可用资源量分别为12、8、16、12，药品1和药品2单位利润分别为200和300，求两种药品生产量分别为多少获得最大利润。

建立电子表格模型时既可以直接利用问题中所给的数据和信息，也可以利用已建立的代数模型.本例的代数模型为：

目标函数 21300200x x Z +=max

⎪⎪⎪⎩⎪⎪⎪⎨⎧≥≤≤≤+≤+0

,124164821222..21212121x x x x x x x x t s

图2显示了将该例的数据转送到电子表格中后所建立的电子表格数学模型（本例是一个线性规划模型）.

其中显示数据的单元格称为数据单元格，包括生

产每单位药品Ⅰ和Ⅱ所需要的4种设备的台时数（单元格C5：D8），药品Ⅰ和Ⅱ的单位利润（单元格C9：D9），4种设备可用的台时数（单元格G5：G8）.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

部分设置在电子表格的何处（单

击暂时隐藏对话框，再从工作

表中选定单元格，然后再次单击

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

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

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

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

的“添加”按钮，弹出图所示的添加约束对话框.由于各种设备台时的总需求量均不应超过可用台时数的限制，故单元格E5到E8必须小于或等于对应的单元格G5到G8.即在添加约束对话框的左端输入范围E5：E8（可用选中单元格的方式），中间选择<=（点开下拉列表进行选择），右端输入范围G5：G8.如果模型中还包含其他类型的函数约束，则可点击“添加”按钮以弹出一个新的添加约束对话框，根据输出单元格与约束值之间的关系在对话框中间的下拉列表中选择适当的约束类型，以增加新的约束.但本例中已无其他约束了，所以只要点击“确定”按钮返回“规划求解”对话框.如果需要修改或删除已添加的约束，可选中该约束后点击“更改”或“删除”按钮.

到现在为止“规划求解”对话框已根据的电子表格描述了整个模型.但在求

图 13-8

解模型前还需要进行最后一个程序，

点击“选项”按钮弹出图所示的选项

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

何求解问题的细节的选项.对于决策

变量取值非负的线性规划模型，最主

要的选项是“采用线性模型”和“假

定非负”选项.关于其他选项，对小型

问题来说接受图中所示的默认值通常比较合适，点击“确定”按钮返回“规划求解”对话框.

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

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

对话框会显示“规划求解找不到可行解”

或“设定的单元格值不能集中”.对话框

还显示了产生各种报告的选项，

后面将会介绍.选择“保存规划

求解结果” 并点击“确定” 按

钮，返回电子表格模型.

求解模型之后，如图所示，

“规划求解”用最优解和最优值代替了可变单元格和目标单元格中的初始值.因此，最优解是生产4公斤药品Ⅰ和2公斤药品Ⅱ，最优值为1400元.