本文讨论如何使用规划求解（一种 Microsoft Excel 加载项程序，可用于进行 If 分析）来确定最佳产品组合。
如何确定可最大化利润的每月产品组合？
公司通常需要确定每月要生产的每个产品的数量。 最简单的形式是， 产品 组合问题涉及如何确定应在一个月内生产的每个产品的数量，以最大化利润。 产品组合通常必须遵守以下约束：
-
产品组合使用的资源不能超过可用资源。
-
每种产品的需求有限。 在一个月内，我们生产的产品不能超过需求要求，因为多余生产被浪费 (例如，可消费的) 。
现在，让我们解决产品混合问题的以下示例。 可以在文件文件中找到此问题的解决方案，Prodmix.xlsx图 27-1 中所示。
假设我们为一家药物公司工作，该公司在工厂中生产六种不同的产品。 每个产品的生产需要人工和原始材料。 图 27-1 中的第 4 行显示生产每个产品一磅所需的人工工时，第 5 行显示生产每个产品一磅所需的原始材料。 例如，生产一磅产品 1 需要 6 小时的人工和 3.2 磅的原始数据。 对于每个药物，每磅的价格按第 6 行给出，第 7 行中给出每磅的单位成本，第 9 行中给出每磅利润贡献。 例如，产品 2 每磅的售价为 $11.00，每磅产生的单位成本为 $5.70，每磅利润为 $5.30。 第 8 行提供每个月对每种药物的需求。 例如，产品 3 的需求是 1041 磅。 本月提供 4500 小时的人工和 1600 磅的原始数据。 该公司如何最大化其每月利润？
如果我们对 Excel 规划求解一点不知道，我们将通过构建一个工作表来跟踪与产品组合相关的利润和资源使用情况来攻击此问题。 然后，我们将使用试用和错误来改变产品组合以优化利润，而不使用比可用量更多的人工或原始材料，并且不会生产超过需求的任何药物。 我们仅在此过程中使用"规划求解"处于试用和错误阶段。 实际上，规划求解是一个优化引擎，可以顺利执行试错搜索。
解决产品组合问题的一个关键是有效计算与任何给定产品组合关联的资源使用情况和利润。 我们可用于进行此计算的一个重要工具是 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 个产品，那么需要多久。 计算人工和原始数据使用情况的一种更简单的方法就是将公式 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 磅)
使用公式 SUMPRODUCT (D9：I9，$D $2：$I $2， 在单元格 D12 中轻松计算利润) 。
我们现在可以识别产品混合规划求解模型的三个组件。
-
目标单元格。 我们的目标是最大化在单元格 D12 (计算利润) 。
-
更改单元格。 单元格区域 D2：I2 (中列出每个产品所生成的)
-
约束。 我们具有以下约束：
-
请勿使用比可用材料更多的人工或原始材料。 也就是说，单元格 D14：D15 () 中的值必须小于或等于单元格 F14：F15 中的值 (可用资源) 。
-
不要产生比需求更多的药物。 也就是说，每个药物 () 的单元格 D2：I2 (中的值必须小于或等于单元格 D8：I8 (中列出的每个药物的需求) 。
-
我们无法产生任何药物负量。
-
我将展示如何在规划求解中输入目标单元格、更改单元格和约束。 然后，只需单击"求解"按钮即可找到最大化利润的产品组合！
若要开始，请单击"数据"选项卡，在"分析"组中单击"规划求解"。
注意: 如第 26 章"Excel 规划求解优化简介"一样，通过单击"Microsoft Office 按钮"，然后单击"Excel 选项"，然后单击"加载项"来安装规划求解。 在"管理"列表中，单击"Excel 加载项"，选中"规划求解加载项"框，然后单击"确定"。
将显示"规划求解参数"对话框，如图 27-2 所示。
单击"设置目标单元格"框，然后选择单元格 D12 中的 (单元格) 。 单击"通过更改单元格"框，然后指向区域 D2：I2，其中包含每种药物产生的磅值。 对话框现在应如图 27-3 所示。
现在，我们已准备好向模型添加约束。 单击"添加"按钮。 你将看到"添加约束"对话框，如图 27-4 所示。
若要添加资源使用限制，请单击"单元格引用"框，然后选择区域 D14：D15。 从<列表中选择 =。 单击"约束"框，然后选择单元格区域 F14：F15。 "添加约束"对话框现在应如图 27-5 所示。
现在，我们已确保，当规划求解尝试更改单元格的不同值时，仅考虑满足 D14<=F14 (使用的人工的组合小于或等于可用的人工) ，并且 D15<=F15 (使用的原始数据小于或等于) 可用的原始数据。 单击"添加"以输入需求约束。 填写"添加约束"对话框，如图 27-6 所示。
添加这些约束可确保当规划求解尝试对更改的单元格值使用不同的组合时，仅考虑满足以下参数的组合：
-
D2<=D8 (药物 1 的生成量小于或等于对药物 1)
-
E2<=E8 (药物 2 的生产量小于或等于对药物 2 产品的需求)
-
F2<=F8 (制造药物 3 的量小于或等于对药物 3 产品的需求)
-
G2<=G8 (制造药物 4 的量小于或等于对药物 4 产品的需求)
-
H2<=H8 (制造药物 5 的量小于或等于对药物 5 产品的需求)
-
I2<=I8 (制造药物 6 的量小于或等于对药物 6 产品的需求)
在"添加约束"对话框中单击"确定"。 "规划求解"窗口应如图 27-7 所示。
我们在"规划求解选项"对话框中输入一个约束，即更改单元格必须是非负单元格。 单击"规划求解参数"对话框中的"选项"按钮。 选中"假定线性模型"框和"假定非负值"框，如下一页图 27-8 所示。 单击“确定”。
选中"假定非负值"框可确保规划求解仅考虑更改单元格的组合，其中每个更改的单元格都假定非负值。 我们选中了"假设线性模型"框，因为产品混合问题是一种特殊类型的规划求解问题，称为 线性模型。 实际上，规划求解模型在下列条件下是线性的：
-
通过将更改单元格的字词相加 (*) * (计算) 。
-
每个约束都满足"线性模型要求"。 这意味着，通过将更改单元格 (* () 常量的 (相加，将求和与常量进行比较) 计算每个约束。
此规划求解问题为什么是线性的？ 目标单元格 (利润) 计算为
(每磅药物 1 利润) * (生产药物 1) +
(每磅药物 2 利润) * (所生产的药物 2) + ...
(每磅 6 磅) * (6 磅)
此计算 (遵循这样一种模式：通过将更改单元格) ** (常量形式的术语相加来派生目标) 。
通过比较从每磅药物1) * (使用每磅药物) + (一磅药物 2 的 (方用值来评估我们的人工约束) * (药物 2 生产) + ... (一磅（每磅药物6) * (6磅) 生产给可用人工）。
因此，通过将更改单元格 ) (* ( 常量的 (相加，将求和与常量进行比较) 计算人工约束。 人工约束和原始数据约束都满足线性模型要求。
我们的需求约束的形式
(药物 1) <= (药物 1 需求)
(药物 2) <= (2 需求)
§
(药物 6 生成) <= (6 需求)
每个需求约束还满足线性模型要求，因为每个约束都通过将 (更改单元格 ) * ( 常量) 的形式术语相加，以及将总和与常量进行比较进行评估。
在表明我们的产品混合模型是线性模型后，我们为什么应该关注？
-
如果规划求解模型是线性的，并且我们选择"假设线性模型"，则保证规划求解找到"规划求解"模型的最佳解决方案。 如果规划求解模型不是线性的，规划求解可能会找到或找不到最佳解决方案。
-
如果规划求解模型是线性的，并且我们选择"假定线性模型"，则规划求解使用一种非常高效的算法 (简单的方法) 以查找模型的最佳解决方案。 如果规划求解模型是线性的，并且我们未选择"假定线性模型"，则规划求解使用效率极低的算法 (GRG2 方法) 并且可能难以找到模型的最佳解决方案。
在"规划求解选项"对话框中单击"确定"后，我们将返回到"规划求解"主对话框，如上图 27-7 所示。 单击"求解"时，"规划求解"将计算最佳 (（如果存在) 混合模型的解决方案）。 如我在第 26 章所指出，产品混合模型的最佳解决方案是一组更改单元格值 (每个药物所生成的) ，从而在所有可行解决方案集上最大化利润。 同样，可行的解决方案是一组满足所有约束的变更单元格值。 图 27-9 中显示的更改单元格值是一种可行的解决方案，因为所有生产级别都是非负的，生产级别不会超过需求，资源使用不会超过可用资源。
下一页图 27-10 中显示的更改单元格值表示不可行的解决方案，原因如下：
-
我们生产的药物 5 多于其需求。
-
我们的人工比可用资源要多。
-
我们的原始材料比可用材料要多。
单击"求解"后，规划求解可以快速找到图 27-11 中显示的最佳解决方案。 需要选择"保留规划求解解决方案"以保留工作表中的最佳解决方案值。
我们的药物公司可以通过生产 596.67 磅药物 4、1084 磅药物 5，并且没有任何其他药物，来最大化其每月利润 6，625.20 美元！ 我们无法确定我们能否以其他方式实现 $6，625.20 的最大利润。 我们可以确定，由于我们的资源和需求有限，本月无法达到 6，627.20 美元以上。
假设必须满足每个 产品 的需求。 (请参阅文件 Prodmix.xlsx.) 中的"不可行解决方案"工作表 然后，我们必须将需求约束从D2：I2<=D8：I8更改为D2：I2>=D8：I8。 为此，请打开"规划求解"，选择 D2：I2<=D8：I8 约束，然后单击"更改"。 将显示"更改约束"对话框，如图 27-12 所示。
选择>=，然后单击"确定"。 现在，我们确保规划求解将仅考虑更改满足所有需求的单元格值。 单击"求解"时，会看到消息"规划求解找不到可行的解决方案"。 此消息并不意味着模型出错，而是由于资源有限，无法满足所有产品的需求。 规划求解只是告诉我们，如果我们想要满足每个产品的需求，则需要添加更多的人工和/或更多的原始材料。
让我们看看，如果允许对每种产品无限需求，并且允许每一种药物产生负数量，会发生什么情况。 (可以在文件 Prodmix.xlsx.) 中的 "设置值不收敛"工作表上看到此规划求解问题) 若要查找此情况的最佳解决方案，请打开"规划求解"，单击"选项"按钮，并清除"假定非负值"框。 在"规划求解参数"对话框中，选择需求约束 D2：I2<=D8：I8，然后单击"删除"以删除约束。 单击"求解"时，规划求解返回消息"设置单元格值不收敛"。 此消息意味着，如果要将目标单元格最大化 (如示例中所示) ，则存在具有任意较大目标单元格值的可行解决方案。 (如果要最小化目标单元格，则消息"设置单元格值不收敛"表示存在具有任意较小目标单元格值的可行解决方案。) 在我们的情况下，通过允许对药物进行负面生产，我们实际上"创建"了可用于生产大量其他药物的资源。 鉴于我们的无限需求，这使我们能够无限盈利。 在真实情况下，我们无法无限赚钱。 简而言之，如果看到"设置值不收敛"，则模型确实出错。
-
假设我们的药物公司可以购买最多 500 小时的人工，每小时可以比当前人工成本多购买 1 美元。 如何最大化利润？
-
在芯片制造工厂中，A、B、C 和 D (四名技术人员) 三个产品 (产品 1、2 和 3) 。 本月，芯片制造商可以销售 80 个单位的产品 1、50 个单位的产品 2，以及最多 50 个单位的产品 3。 技术人员 A 只能生产产品 1 和 3。 技术人员 B 只能生产产品 1 和 2。 技术员 C 只能生产产品 3。 技术人员 D 只能生产产品 2。 对于所生产的每个单位，产品贡献以下利润：产品 1，$6;产品 2，$7;产品 3，$10。 每个技术人员 (产品) 的小时数如下：
产品
技术员 A
技术员 B
技术员 C
D 技术人员
1
2
2.5
无法执行
无法执行
2
无法执行
3
无法执行
3.5
3
3
无法执行
4
无法执行
-
每个技术员每月最多可以工作 120 小时。 芯片制造商如何最大化其每月利润？ 假设可以生成一小部分单位。
-
计算机制造工厂生产鼠标、键盘和视频游戏游戏杆。 下表提供了每个单位利润、每个单位的人工使用情况、每月需求以及每单位机器时间使用情况：
鼠标
键盘
游戏杆
利润/单位
$8
$11
$9
人工使用/单位
.2 小时
.3 小时
0.24 小时
计算机时间/单位
.04 小时
.055 小时
.04 小时
每月需求
15,000
27,000
11,000
-
每个月总共提供 13，000 个人工小时和 3000 小时的机器时间。 制造商如何最大化工厂的每月利润贡献？
-
解决我们的药物示例，假设每个药物的最低需求必须达到 200 个单位。
-
Jason 制造钻石钻石、钻石和钻石。 他想要每月最多工作 160 小时。 他拥有 800 磅钻石。 下面列出了生产每种产品所需的利润、人工时间和钻石量。 如果每个产品的需求不受限制，Jason 如何最大化其利润？
产品
单利润
每个单位的人工工时
每单位的钻石量
因此，我们
￥3,000
.35
1.2
因此，我们
$200
.15
.75
百亿亿
￥100
.05
.5
