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

1、使用规划求解确定最佳产品组合什么是 Excel 规划求解工具？当您想要查找做某件事的最佳方法时，使用的确实是规划求解。或者，更正规的讲法确实是，当您想要在电子表格的某些单元格中得到优化（最大化或最小化）某个目标的值时，使用的确实是规划求解。优化模型包括三部分：目标单元格、可变单元格和约束。目标单元格代表目的或目标。例如，最大化每月利润。可变单元格是电子表格中我们能够进行更改或调整以优化目标单元格的单元格。例如，每月每种产品的产量。约束是您置于可变单元格中的限制条件。例如，使用的资源不能超标，同时不能生产过剩的产品。如何确定哪种产品组合能够使利润最大化？公司通常需要确定每月（或每周）生产打算，列

2、出每种产品必须生产的数量。具体来讲确实是，产品组合问题涉及如何确定在每月应该生产的每种产品的数量以使利润最大化。产品组合通常必须满足以下约束：产品组合使用的资源不能超标。对每种产品的需求差不多上有限的。我们每月生产的产品不能超过需求的数量，因为生产过剩确实是白费（例如，易变质的药品）。让我们来解决以下产品组合示例问题。您能够在 prodmix.xls 文件中找到该问题的解决方案（该文件包含在中），如图 1 所示。图 1：产品组合示例。假定我们在一家医药公司工作，这家公司能够在他们的工厂生产六种产品。生产每种产品都需要人工和原材料。 图 1 的第 4 行显示了生产一磅的每种产品所需的人工小时数，

3、第 5 行显示了生产一磅的每种产品所需的原材料的磅数。例如，生产一磅的产品 1 需要 6 小时人工和 3.2 磅原材料。 第 6 行显示了每种药品每磅的价格，第 7 行显示了每磅的成本，第 9 行显示每磅可带来的利润。例如，产品 2 的价格是每磅 11.00 美元，每磅的单位成本是 5.70 美元，每磅的利润确实是 5.30 美元。 第 8 行显示了该月对每种药品的需求。例如，对产品 3 的需求为 1041 磅。 该月可提供 4500 人工工时和 1600 磅的原材料。该公司如何最大化它每月的利润？假如我们对规划求解一无所知，我们会通过构建一个电子表格，然后在其中跟踪每种产品组合以及与该产品组

4、合相关联的资源用量来处理这一问题。然后我们 会反复试验、不断地变化产品组合以优化利润，同时确保使用的人工或原材料可不能超标，并确保可不能生产出过剩药品。在此过程中，我们只在反复试验时期中使用了 规划求解。从全然上来讲，规划求解是一个能够完美地执行反复试验搜索的优化引擎。解决产品组合问题的关键是有效地计算与任一给定产品组合相关联的资源用量和利润。SUMPRODUCT 函数是我们能够用来执行此计算的一个重要工具。SUMPRODUCT 函数将单元格区域中相应的值相乘并返回这些值的总和。SUMPRODUCT 评估中使用的每个单元格区域都必须具有相同的维度，这意味着您能够对两行或两列使用 SUMPROD

5、UCT，而不是对一列或一行。作为如何在产品组合示例中使用 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 计算。在电子表格中对六种产品分

6、不输入这些公式是专门白费时刻的。想像一下，假如您正在对一家其工厂生产 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 中的公式用于计算原材料用量。类似地，通过以下计算方式能够得出我们的利润：（每磅药品

7、1 的利润）*（生产的药品 1 的磅数）+（每磅药品 2 的利润）*（生产的药品 2 的磅数）+.（每磅药品 6 的利润）*（生产的药品 6 的磅数）。在单元格 D12 中使用以下公式能够专门容易计算出利润： SUMPRODUCT(D9:I9,$D$2:$I$2)现在我们能够标识出产品组合规划求解模型的三个组成部分：目标单元格可变单元格约束我们的目标是使利润（在单元格 D12 中计算）最大化。生产的每种产品的磅数（在单元格区域 D2:I2 中列出）。使用的人工和原材料不能超标。也确实是讲，单元格 D14:D15（所用资源）必须小于或等于单元格 F14:F15 中的值（可用资源）。生产的药品不能

8、超过需求数量。也确实是讲，单元格 D2:I2（生产的每种药品的磅数）必须小于或等于对每种药品的需求（在单元格 D8:I8 中列出）。我们不能生产任何产量为负的药品。何将此模型输入到规划求解中？现在，我将向你们演示如何将目标单元格、可变单元格和约束输入规划求解。然后，你们只需单击“求解”按钮即可，规划求解将会找出可使利润最大化的产品组合。要开始操作，请选择“工具”菜单上的“规划求解”。（有关安装规划求解的讲明，请参阅。） 即会出现“规划求解参数” 对话框。要输入目标单元格，请在“设置目标单元格”框中单击，然后选择利润单元格（单元格 D12）。要输入可变单元格，请在“可变单元格”框中单击，然后指向

9、区域 D2:I2，该区域包含生产的每种药品的磅数。该对话框现在看起来应如下图所示。现在我们差不多能够向模型中添加约束了。单击“添加”按钮，您能够看到“添加约束”对话框。要添加资源用量约束，请在标记为“单元格引用位置”的框中单击，然后选择区域 D14:D15。从对话框中部的列表中选择“=”。在标记为“约束值”的框中单击，然后选择单元格区域 F14:F15。现在我们差不多确保当规划求解尝试对可变单元格使用不同的值时，规划求解将只考虑同时满足 D14 = F14（所用人工小于或等于可用人工）和 D15 = F15（所用原材料小于或等于可用原材料）的组合。 现在，在“添加约束”对话框中单击“添加”，以

10、输入需求约束。只需如下图所示填充“添加约束”对话框即可。添加这些约束能够确保当规划求解尝试对可变单元格值使用不同的组合时，规划求解将只考虑满足以下条件的组合：D2 = D8（药品 1 的产量小于或等于对药品 1 的需求量）E2 = E8（药品 2 的产量小于或等于对药品 2 的需求量）F2 = F8（药品 3 的产量小于或等于对药品 3 的需求量）G2 = G8（药品 4 的产量小于或等于对药品 4 的需求量）H2 = H8（药品 5 的产量小于或等于对药品 5 的需求量）I2 = I8（药品 6 的产量小于或等于对药品 6 的需求量）单击“添加约束”对话框中的“确定”。“规划求解参数”对话框

11、应如下图所示。在“规划求解选项”对话框中输入所有可变单元格都为非负值的约束，通过单击“规划求解参数”对话框中的“选项”按钮可打开该对话框。 选择“采纳线性模型”和“假定非负”选项，然后单击“确定”。选择“假定非负”选项可确保规划求解只考虑每个可变单元格都采纳非负值的可变单元格组合。 选择“采纳线性模型”的缘故是产品组合问题是一种称为线性模型的专门规划求解问题。差不多上，在以下情况下，规划求解模型差不多上线性模型：目标单元格是通过将表单的条件（可变单元格）*（约束）相加进行计算的。每种约束都满足线性模型要求。这意味着每种约束差不多上通过将表单的条件（可变单元格）*（约束）相加，然后将这些总和与某

12、个常量进行比较来评估的。那个规划求解问题什么缘故是线性的？ 我们的目标单元格（利润）计算方式为： （每磅药品 1 的利润）*（生产的药品 1 的磅数）+（每磅药品 2 的利润）*（生产的药品 2 的磅数）+.（每磅药品 6 的利润）*（生产的药品 6 的磅数）这种计算方式遵循一种模式，即目标单元格的值是通过将表单的各个条件（可变单元格）*（约束）相加得出的.我们的人工约束是通过将可用人工与通过以下公式得出的值进行比较来评估的： （每磅药品 1 使用的人工）*（生产的药品 1 的磅数）+ （每磅药品 2 使用的人工）*（生产的药品 2 的磅数）+ .（每磅药品 6 使用的人工）*（生产的药品 6

13、 的磅数）因此，人工约束是通过将表格的各个条件（可变单元格）*（约束）相加，然后将这类总和与某个常量进行比较来评估的。人工约束和原材料约束都满足线性模型要求。我们的需求约束采纳以下形式：（药品 1 的产量）=（药品 1 的需求量）（药品 2 的产量）=（药品 2 的需求量）.（药品 6 的产量）=（药品 6 的需求量）每种需求约束还都符合线性模型要求，因为每种约束差不多上通过将表单的各个条件（可变单元格）*（约束）相加，然后将这些总和与某个常量进行比较来评估的。差不多表明我们的产品组合模型是线性模型，我们还要关怀什么？假如规划求解模型是线性的，同时我们选择“采纳线性模型”，则规划求解保证能够找

14、到规划求解模型的最佳解决方案。假如规划求解模型不是线性的，则规划求解可能能够找到最佳解决方案，也可能找不到。假如规划求解模型是线性的，同时我们选择“采纳线性模型”，规划求解将使用一种特不高效的算法（单工方法）来找到该模型的最佳解决方案。假如规划求解模型是线性的，同时我们不选择“采纳线性模型”，规划求解将使用一种特不低效的算法（GRG2 方法），同时可能专门难找到该模型的最佳解决方案。选择“假定非负”选项可确保规划求解只考虑每个可变单元格都采纳非负值的可变单元格组合。 选择“采纳线性模型”的缘故是产品组合问题是一种称为线性模型的专门规划求解问题。差不多上，在以下情况下，规划求解模型差不多上线性模

15、型：目标单元格是通过将表单的条件（可变单元格）*（约束）相加进行计算的。每种约束都满足线性模型要求。这意味着每种约束差不多上通过将表单的条件（可变单元格）*（约束）相加，然后将这些总和与某个常量进行比较来评估的。那个规划求解问题什么缘故是线性的？ 我们的目标单元格（利润）计算方式为： （每磅药品 1 的利润）*（生产的药品 1 的磅数）+（每磅药品 2 的利润）*（生产的药品 2 的磅数）+.（每磅药品 6 的利润）*（生产的药品 6 的磅数）这种计算方式遵循一种模式，即目标单元格的值是通过将表单的各个条件（可变单元格）*（约束）相加得出的.我们的人工约束是通过将可用人工与通过以下公式得出的值

16、进行比较来评估的： （每磅药品 1 使用的人工）*（生产的药品 1 的磅数）+ （每磅药品 2 使用的人工）*（生产的药品 2 的磅数）+ .（每磅药品 6 使用的人工）*（生产的药品 6 的磅数）因此，人工约束是通过将表格的各个条件（可变单元格）*（约束）相加，然后将这类总和与某个常量进行比较来评估的。人工约束和原材料约束都满足线性模型要求。我们的需求约束采纳以下形式：（药品 1 的产量）=（药品 1 的需求量）（药品 2 的产量）=（药品 2 的需求量）.（药品 6 的产量）=（药品 6 的需求量）每种需求约束还都符合线性模型要求，因为每种约束差不多上通过将表单的各个条件（可变单元格）*（

17、约束）相加，然后将这些总和与某个常量进行比较来评估的。差不多表明我们的产品组合模型是线性模型，我们还要关怀什么？假如规划求解模型是线性的，同时我们选择“采纳线性模型”，则规划求解保证能够找到规划求解模型的最佳解决方案。假如规划求解模型不是线性的，则规划求解可能能够找到最佳解决方案，也可能找不到。假如规划求解模型是线性的，同时我们选择“采纳线性模型”，规划求解将使用一种特不高效的算法（单工方法）来找到该模型的最佳解决方案。假如规划求解模型是线性的，同时我们不选择“采纳线性模型”，规划求解将使用一种特不低效的算法（GRG2 方法），同时可能专门难找到该模型的最佳解决方案。单击“规划求解选项”对话框

18、中的“确定”后，我们将返回到主“规划求解”对话框。当我们单击“求解”时，规划求解将为我们的产品组合模型计算出一个最佳解决方案（假如有）。产品组合模型问题的最佳解决方案是所有可行解决方案集中可使利润最大化的一组可变单元格值（生产的每种药品的磅数）。同样，可行解决方案是一组满足所有约束的可变单元格值。图 2 中显示的可变单元格值确实是一个可行解决方案，因为所有产品级不差不多上非负值，产品级不都没有超出需求，而且资源用量也没有超出可用资源。图 2：符合约束的可行的产品组合问题解决方案。由于以下缘故，图 3 中显示的可变单元格值代表一个不可行的解决方案：生产的产品 5 的数量大于需求数量。使用的人工大

19、于可用人工。使用的原材料大于可用原材料。图 3：不符合我们定义的约束的不可行的产品组合问题解决方案。单击“求解”后，规划求解会迅速找出最佳解决方案，如图 4 所示。您需要选择“保存规划求解解决方案”以将最佳解决方案值保留在电子表格中。图 4：产品组合问题的最佳解决方案。通过生产 596.67 磅的药品 4、1084 磅的药品 5 而不生产任何其他药品，我们的医药公司每月可获得最高利润 6,625.20 美元！我们无法确定通过其他方法是否能够获得 6,625.20 美元的最高利润。但我们能够确定，在我们有限的资源和需求条件下，那个月的利润全然不可能超出 6,625.20 美元。规划求解模型总是有

20、解决方案吗？假定必须满足对每种产品的需求，那么我们就必须将我们的需求约束从 D2:I2 = D8:I8。要更改此约束，请 打开规划求解。 单击“D2:I2 =”，然后单击“确定”。现在我们能够确保规划求解将只考虑符合所有需求的可变单元格值。当您单击“求解”时，您将会看到“规划求解找不到可行的解决方案”消息。该消息意味着使用我们有限的资源，无法满足对所有产品的需求。我们的模型并没有错！规划求解只是要告诉我们，假如我们想要满足对每种产品的需求，我们就需要增加更多的人工、更多的原材料或两者都要增加。假如设置目标单元格的值未收敛，意味着什么？让我们看看假如我们同意对每种药品无限制的需求，同时同意每种药

21、品的产量为负，会发生什么情况。要找出针对这种情况的最佳解决方案，请执行以下操作：打开规划求解。单击“选项”按钮，然后清除“假定非负”复选框。在“规划求解参数”对话框中，单击需求约束“D2:I2 = D8:I8”，然后单击“删除”以删除该约束。当您单击“求解”时，规划求解将返回“设置目标单元格的值未收敛”消息。该消息意味着假如要最大化目标单元格（像我们的示例中一样），会存在具有任意大的目标单元格值的可行解决方案。（假如要最小化目标单元格，该消息则意味着存在具有任意小的目标单元格值的可行解决方案。） 在这种情况下，通过同意药品的产量为负，我们实际上“制造”了可用于生产任意大数量的其他药品的资源。假设我们的需求没有限制，这就使得我们能够制造无限的利润。而现实中，我们是不可能制造无限利润的。简而言之，假如您看到了“设置目标单元格的值未收敛”，就表示您的模型有错误。自我测试s25_1.xls 至 s25_5.xls 文件中提供了这些问题的解决方案，这些文件包含在中。假定我们的医药公司能够按每小时 1 美元的价格购买了 500 小时的人工。它们将如何利用这次机会？在一家芯片