文档介绍：The document was prepared on January 2, 2021
使用规划求解确定最佳产品组合
使用规划求解确定最佳产品组合
什么是 Excel 规划求解工具
当您想要寻找做某件事的最佳方法时，使用的就是规划求解。或者，更正规的说法就是，当您想要在电子表格的某些单元格中得到优化（最大化或最小化）某个目标的值时，使用的就是规划求解。
优化模型包括三部分：目标单元格、可变单元格和约束。
目标单元格代表目的或目标。例如，最大化每月利润。
可变单元格是电子表格中我们可以进行更改或调整以优化目标单元格的单元格。例如，每月每种产品的产量。
约束是您置于可变单元格中的限制条件。例如，使用的资源不能超标，并且不能生产过剩的产品。
如何确定哪种产品组合可以使利润最大化
公司通常需要确定每月（或每周）生产计划，列出每种产品必须生产的数量。具体来说就是，产品组合问题涉及如何确定在每月应该生产的每种产品的数量以使利润最大化。产品组合通常必须满足以下约束：
产品组合使用的资源不能超标。
对每种产品的需求都是有限的。我们每月生产的产品不能超过需求的数量，因为生产过剩就是浪费（例如，易变质的药品）。
让我们来解决以下产品组合示例问题。您可以在 文件中找到该问题的解决方案（该文件包含在中），如图 1 所示。
图 1：产品组合示例。
假定我们在一家医药公司工作，这家公司可以在他们的工厂生产六种产品。生产每种产品都需要人工和原材料。
图 1 的第 4 行显示了生产一磅的每种产品所需的人工小时数，第 5 行显示了生产一磅的每种产品所需的原材料的磅数。例如，生产一磅的产品 1 需要 6 小时人工和 磅原材料。
第 6 行显示了每种药品每磅的价格，第 7 行显示了每磅的成本，第 9 行显示每磅可带来的利润。例如，产品 2 的价格是每磅 美元，每磅的单位成本是 美元，每磅的利润就是 美元。
第 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