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

《使用规划求解确定最佳产品组合》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

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评估中使用的每个单元格区域都必须具有相同的维度，这意味着您可以对两行或两列使用SUMPRODUCT，而不是对一列或一行。作为如何在产品

5、组合示例中使用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中的公式用于计算原材料用量。类似地，通过以下计算方式可以得出我们的利润：（每磅药品1的利润）*（生产的药品1的磅数）+（每磅药品2的利润）*（生产的药品2的磅数）+..

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