使用规划求解确定最佳产品组合

使用规划求解确定最佳产品组合

什么是 Excel 规划求解工具？

当您想要寻找做某件事的最佳方法时，使用的就是规划求解。或者，更正规的说法就是，当您想要在电子表格的某些单元格中得到优化（最大化或最小化）某个目标的值时，使用的就是规划求解。

优化模型包括三部分：目标单元格、可变单元格和约束。

•目标单元格代表目的或目标。例如，最大化每月利润。

•可变单元格是电子表格中我们可以进行更改或调整以优化目标单元格的单元格。例如，每月每种产品的产量。

•约束是您置于可变单元格中的限制条件。例如，使用的资源不能超标，并且不能生产过剩的产品。如何确定哪种产品组合可以使利润最大化？

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

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

•对每种产品的需求都是有限的。我们每月生产的产品不能超过需求的数量，因为生产过剩就是浪费（例如，易变质的药品）。

让我们来解决以下产品组合示例问题。您可以在 prodmix.xls 文件中找到该问题的解决方案（该文件包含在示例文件下载中），如图 1 所示。

图 1：产品组合示例。

假定我们在一家医药公司工作，这家公司可以在他们的工厂生产六种产品。生产每种产品都需要人工和原材料。

•图 1 的第 4 行显示了生产一磅的每种产品所需的人工小时数，第 5 行显示了生产一磅的每种产品所需的原材料的磅数。例如，生产一磅的产品 1 需要 6 小时人工和 3.2 磅原材料。

•第 6 行显示了每种药品每磅的价格，第 7 行显示了每磅的成本，第 9 行显示每磅可带来的利润。

例如，产品 2 的价格是每磅 11.00 美元，每磅的单位成本是 5.70 美元，每磅的利润就是 5.30 美元。

•第 8 行显示了该月对每种药品的需求。例如，对产品 3 的需求为 1041 磅。

该月可提供 4500 人工工时和 1600 磅的原材料。该公司如何最大化它每月的利润？

如果我们对规划求解一无所知，我们会通过构建一个电子表格，然后在其中跟踪每种产品组合以及与该产品组合相关联的资源用量来处理这一问题。然后我们会反复试验、不断地变化产品组合以优化利润，同时确保使用的人工或原材料不会超标，并确保不会生产出过剩药品。在此过程中，我们只在反复试验阶段中使用了规划求解。从根本上来说，规划求解是一个可以完美地执行反复试验搜索的优化引擎。

解决产品组合问题的关键是有效地计算与任一给定产品组合相关联的资源用量和利润。SUMPRODUCT 函数是我们可以用来执行此计算的一个重要工具。SUMPRODUCT 函数将单元格区域中相应的值相乘并返回这些值的总和。SUMPRODUCT 评估中使用的每个单元格区域都必须具有相同的维度，这意味着您可以对两行或两列使用 SUMPRODUCT，而不是对一列或一行。

作为如何在产品组合示例中使用 SUMPRODUCT 函数的示例，让我们尝试计算一下我们的资源用量。通过以下计算方式可以得出人工用量：

（每磅药品 1 使用的人工）*

（生产的药品 1 的磅数）+

（每磅药品 2 使用的人工）*

（生产的药品 2 的磅数）+

...

（每磅药品 6 使用的人工）*

（生产的药品 6 的磅数）

在我们的电子表格中，我们可能会通过 D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 来计算人工用量（非常繁锁）。类似地，原材料用量可以通过 D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5 计算。在电子表格中对六种产品分别输入这些公式是很浪费时间的。想像一下，如果您正在对一家其工厂生产 50 种产品的公司执行这样的计算，会花费多长时间？

计算人工和原材料用量的一种更为简单的方法是将 D14 中的公式复制到 D15 中：

SUMPRODUCT($D$2:$I$2,D4:I4)

该公式会计算 D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4（这是我们的人工用量），这要比手动输入简单得多！

请注意，我对区域 D2:I2 使用了 $ 符号，以便在我复制公式时，我仍然可以从第 2 行中取下产品组合。单元格 D15 中的公式用于计算原材料用量。

类似地，通过以下计算方式可以得出我们的利润：

（每磅药品 1 的利润）*

（生产的药品 1 的磅数）+

（每磅药品 2 的利润）*

（生产的药品 2 的磅数）+

...

（每磅药品 6 的利润）*

（生产的药品 6 的磅数）。

在单元格 D12 中使用以下公式可以很容易计算出利润：

SUMPRODUCT(D9:I9,$D$2:$I$2)

现在我们可以标识出产品组合规划求解模型的三个组成部分：

目标单元格可变单元格约束

我们的目标是使利润（在单元格 D12 中计算）最大化。生产的每种产品的磅

数（在单元格区域

D2:I2 中列出）。

•使用的人工和原材料不能超标。也就是说，单元格

D14:D15（所用资源）必须小于或等于单元格 F14:F15

中的值（可用资源）。

•生产的药品不能超过需求数量。也就是说，单元格

D2:I2（生产的每种药品的磅数）必须小于或等于对

每种药品的需求（在单元格 D8:I8 中列出）。

•我们不能生产任何产量为负的药品。

何将此模型输入到规划求解中？

现在，我将向你们演示如何将目标单元格、可变单元格和约束输入规划求解。然后，你们只需单击“求解”按钮即可，规划求解将会找出可使利润最大化的产品组合。

1.要开始操作，请选择“工具”菜单上的“规划求解”。（有关安装规划求解的说明，请参阅使用

Excel 规划求解工具进行优化的说明。）

即会出现“规划求解参数”对话框。