信息技术应用 用Excel解线性规划问题举例教学内容分析

下面我们通过一个例子来解释怎样用“规划求解”来求解数学规划问题。

例1 公司通常需要确定每月（或每周）生产计划，列出每种产品必须生产的数量。具体来说就是，产品组合问题就是要确定公司每月应该生产的每种产品的数量以使利润最大化。产品组合通常必须满足以下约束：



产品组合使用的资源不能超标。 

对每种产品的需求都是有限的。我们每月生产的产品不能超过需求的数量，因为生产过剩就是浪费（例如，易变质的药品）。 下面，我们来考虑让某医药公司的最优产品组合问题。该公司有六种可以生产的药品，相关数据如下表所示。

消耗系数

产品1 产品2 劳动力（小时）

6 5 原料（磅）

3.2 2.6 单位利润（元）

6 5.3 需求量（磅）

960 928 表1 产品3 产品4 产品5 产品6 4 3 2.5 1.5 1.5 0.8 0.7 0.3 5.4 4.2 3.8 1.8 1041 977 1084 1055 现有

4500 1600

设该公司生产药品1～6的产量分别为x1,x2,规划模型为

,x6（磅），则最优产品组合的线性maxz6x15.3x25.4x34.2x43.8x51.8x66x15x24x33x42.5x51.5x645003.2x2.6x1.5x0.8x0.7x0.3x1600123456x1960x2928

x31041s..tx4977x51084x61055xj0,1j6

下面用规划求解加载宏来求解这个问题：

首先，如下如所示，在Excel工作表内输入目标函数的系数、约束方程的系数、右端常数项；

1

系数矩阵

目标函数的系数

系数矩阵

右端常数其次，选定目标函数单元、可变单元、约束函数单元，定义目标函数、约束函数

目标函数单元

约束函数单元

可变单元

其中，劳动力约束函数的定义公式是“=MMULT(B3:G3, J5:J10)”，原料约束函数的定义公式是“＝MMULT(B4:G4,J5:J10)”，目标函数的定义公式是“MMULT(B5:G5, J5:J10)”。

注：函数MMULT(B3:G3, J5:J10)的意义是：单元区B3:G3表示的行向量与单元区J5:J10表示的列向量的内积。这一要特别注意的是，第一格单元区必须是行，第二格单元区必须是列，并且两个单元区所含的单元格个数必须相等。

最后，打开规划求解参数设定对话框设定模型

（1）（2）目标函数和可边单元的设定很简单，在此就不再赘述

（3）约束条件的设定

(3.1) 约束条件6x15x24x33x42.5x51.5x64500的设定：

3.2x2.6x1.5x0.8x0.7x0.3x1600123456

2

x1960x9282x31041（3.2）约束条件的设定

x9774x51084x61055

（3.3）约束条件x10,x20,,x60的设定

3

这里值得注意的是：



我们采用向量的形式设定同向不等式，并且不等式两边可以一个时行向量，另一个是列向量；



对所有分量都是0的向量，我们可以用一个0来代替。

（4）求解：我们选择保存三个报告

得到的三张份告如下

4

5

补充说明：

a)

如果数学规划模型中包含整型变量或0－1型变量，只需要在设定约束条件一步中设定相应的变量是整型变量或0－1型变量即可。例如，假定在上例中变量x1,x2是整型变量，只需要增加如下图所示的整型约束设定即可。不过要注意的是，含整型变量或0－1型变量的问题是不能进行敏感性分析的。

b)

求解报告中把输出目标函数单元、约束函数单元、和可边单元上方第一个输有文字单元及左边第一个输有文字单元内全部文字都作为对解释目标函数单元、约束函数单元、和可边单元的解释，例如，把Excel表改为

6

则求解报告输出变为

c)

另外，非线性规划模型的求解方法类似，在此不再赘述。

五、“规划求解”疑难解答

1、尚未找到满足要求的结果，“规划求解”即停止了运行

由于下列任意一个原因，“规划求解”在找到答案前，可能停止运行：



中断了求解过程。 

在单击“求解”之前，选中了“规划求解选项”对话框中的“显示迭代结果”选项。 

在单步迭代过程中，或达到最长运算时间或最大迭代次数时，单击了“停止”按钮。 

选中了“规划求解选项”对话框中的“采用线性模型”复选框，但问题是非线性的。 

在“规划求解参数”对话框的“设置目标单元格”框中指定的数值不收敛地增加或减少。 

需要让“规划求解”运行更长的时间以求得结果。请调整“规划求解选项”对话框中的“最长运算时间”或“迭代次数”的设置。 

对于具有整数约束条件的问题，应该减小“规划求解选项”对话框中的“允许误差”的设置，使“规划求解”找到更好的整数解。 

对于非线性问题，应该减小“规划求解选项”对话框中的“收敛度”的设置，使目标单元格数值变化缓慢时，“规划求解”仍可以运行，最终找到较好的结果。

7



应该选中“规划求解选项”对话框中的“自动按比例缩放”复选框，可能一些输入数值相差几个数量级，或输入和输出数值相差几个数量级。 当“规划求解”停止运行时，在“规划求解结果”对话框中显示出完成信息。单击“保存规划求解结果”或“恢复为原值”，进行所需的更改，然后再运行一次。

2、可变单元格与约束条件或目标单元格中的数值差别很大

当可变单元格的典型数值与约束单元格或目标单元格中的数值相差几个数量级时，请选中“规划求解选项”对话框中的“自动按比例缩放”复选框。对于非线性问题，在单击“规划求解参数”对话框中的“求解”之前，请确认可变单元格的初始数值与期望的最终数值的数量级相同。

3、未得到预期的结果

对于非线性问题，在可变单元格中尝试不同的初始值可能会有帮助，特别是在“规划求解”结果与期望的数值差别很大时。预先将可变单元格的数值设置为预期的最优值，可以减少求解时间。

对于线性模型（也就是当“规划求解选项”对话框的“采用线性模型”复选框被选中时），改变可变单元格的初始值不会影响最终数值或求解时间。

4、“规划求解”得到的结果与以前的结果不同

“规划求解”显示如下消息：“规划求解已收敛到当前结果。满足所有约束条件”。这表明目标单元格中的数值在最近五次求解过程中的变化量小于“规划求解选项”对话框中“收敛度”设置的值。“收敛度”中设置的值越小，“规划求解”在计算时就会越精细，但求解过程将花费更多的时间。

5、“规划求解”不能达到最优解

下面列出“规划求解”显示的完整消息。



“规划求解”不能改进当前解。所有约束条件都得到了满足

这表明仅得到近似值，迭代过程无法得到比显示结果更精确的数值；或是无法进一步提高精度，或是精度值设置得太小，请在“规划求解选项”对话框中试着设置较大的精度值，然后再运行一次。



求解达到最长运算时间后停止

这表明在达到最长运算时间限制时，没有得到满意的结果。若要保存当前结果并节省下次计算的时间，请单击“保存规划求解”或“保存方案”选项。



求解达到最大迭代次数后停止

8

这表明在达到最大迭代次数时，没有得到满意的结果。增加迭代次数也许有用，但是应该先检查结果数值来确定问题的原因。若要保存当前值并节省下次计算的时间，请单击“保存规划求解”或“保存方案”选项。



目标单元格中的数值不收敛

这表明即使满足全部约束条件，目标单元格数值也只是有增或有减但不收敛。这可能是在设置问题时忽略了一项或多项约束条件。请检查工作表中的当前值，确定数值发散的原因，并且检查约束条件，然后再次求解。



“规划求解”未找到合适结果

这表明在满足约束条件和精度要求的条件下，“规划求解”无法得到合理的结果，这可能是约束条件不一致所致。请检查约束条件公式或类型选择是否有误。



“规划求解”应用户要求而中止

这表明在暂停求解过程之后，或在单步执行规划求解时，单击了“显示中间结果”对话框中的“停止”。



无法满足设定的“采用线性模型”条件

这表明求解时选中了“采用线性模型”复选框，但是“规划求解”最后计算结果并不满足线性模型。计算结果对工作表中的公式无效。若要验证问题是否为非线性的，请选中“自动按比例缩放”复选框，然后再运行一次。如果又一次出现同样信息，请清除“采用线性模型”复选框，然后再运行一次。



“规划求解”在目标或约束条件单元格中发现错误值

这表明在最近的一次运算中，一个或多个公式的运算结果有误。请找到包含错误值的目标单元格或约束条件单元格，更改其中的公式或内容，以得到合理的运算结果。

还有可能是在“添加约束”或“改变约束”对话框中键入了无效的名称或公式，或者在“约束”框中直接键入了“integer”或“binary”。若要将数值约束为整数，请在比较运算符列表中单击“Int”。若要将数值约束为二进制数，请单击“Bin”。



内存不足以求解问题

Microsoft Excel 无法获得“规划求解”所需的内存。请关闭一些文件或应用程序，再试一次。



其他的 Microsoft Excel 实例正在使用 SOLVER.DLL 这表明有多个 Microsoft Excel 会话正在运行，其中一个会话正在使用 SOLVER.DLL。SOLVER.DLL 同时只能供一个会话使用。

9