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

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

《使用规划求解确定最佳产品组合.docx》由会员分享，可在线阅读，更多相关《使用规划求解确定最佳产品组合.docx（16页珍藏版）》请在冰豆网上搜索。

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

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

什么是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规划求解工具进行优化的说明。

）

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

2.要输入目标单元格，请在“设置目标单元格”框中单击，然后选择利润单元格（单元格D12）。

要输入可变单元格，请在“可变单元格”框中单击，然后指向区域D2:

I2，该区域包含生产的每种药品的磅数。

该对话框现在看起来应如下图所示。

3.现在我们已经可以向模型中添加约束了。

单击“添加”按钮，您可以看到“添加约束”对话框。

4.要添加资源用量约束，请在标记为“单元格引用位置”的框中单击，然后选择区域D14:

D15。

从对话框中部的列表中选择“<=”。

在标记为“约束值”的框中单击，然后选择单元格区域F14:

F15。

现在我们已经确保当规划求解尝试对可变单元格使用不同的值时，规划求解将只考虑同时满足D14<=F14（所用人工小于或等于可用人工）和D15<=F15（所用原材料小于或等于可用原材料）的组合。

5.现在，在“添加约束”对话框中单击“添加”，以输入需求约束。

只需如下图所示填充“添加约束”对话框即可。

添加这些约束可以确保当规划求解尝试对可变单元格值使用不同的组合时，规划求解将只考虑满足以下条件的组合：

oD2<=D8（药品1的产量小于或等于对药品1的需求量）

oE2<=E8（药品2的产量小于或等于对药品2的需求量）

oF2<=F8（药品3的产量小于或等于对药品3的需求量）

oG2<=G8（药品4的产量小于或等于对药品4的需求量）

oH2<=H8（药品5的产量小于或等于对药品5的需求量）

oI2<=I8（药品6的产量小于或等于对药品6的需求量）

6.单击“添加约束”对话框中的“确定”。

“规划求解参数”对话框应如下图所示。

7.在“规划求解选项”对话框中输入所有可变单元格都为非负值的约束，通过单击“规划求解参数”对话框中的“选项”按钮可打开该对话框。

选择“采用线性模型”和“假定非负”选项，然后单击“确定”。

为什么要选择这些选项？

选择“假定非负”选项可确保规划求解只考虑每个可变单元格都采用非负值的可变单元格组合。

选择“采用线性模型”的原因是产品组合问题是一种称为线性模型的特殊规划求解问题。

基本上，在以下情况下，规划求解模型都是线性模型：

∙目标单元格是通过将表单的条件（可变单元格）*（约束）相加进行计算的。

∙每种约束都满足线性模型要求。

这意味着每种约束都是通过将表单的条件（可变单元格）*（约束）相加，然后将这些总和与某个常量进行比较来评估的。

这个规划求解问题为什么是线性的？

我们的目标单元格（利润）计算方式为：

（每磅药品1的利润）*

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

（每磅药品2的利润）*

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

...

（每磅药品6的利润）*

（生产的药品6的磅数）

这种计算方式遵循一种模式，即目标单元格的值是通过将表单的各个条件（可变单元格）*（约束）相加得出的.

我们的人工约束是通过将可用人工与通过以下公式得出的值进行比较来评估的：

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

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

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

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

...

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

（生产的药品6的磅数）

因此，人工约束是通过将表格的各个条件（可变单元格）*（约束）相加，然后将这类总和与某个常量进行比较来评估的。

人工约束和原材料约束都满足线性模型要求。

我们的需求约束采用以下形式：

（药品1的产量）<=（药品1的需求量）

（药品2的产量）<=（药品2的需求量）

...

（药品6的产量）<=（药品6的需求量）

每种需求约束还都符合线性模型要求，因为每种约束都是通过将表单的各个条件（可变单元格）*（约束）相加，然后将这些总和与某个常量进行比较来评估的。

已经表明我们的产品组合模型是线性模型，我们还要关心什么？

∙如果规划求解模型是线性的，并且我们选择“采用线性模型”，则规划求解保证可以找到规划求解模型的最佳解决方案。

如果规划求解模型不是线性的，则规划求解可能可以找到最佳解决方案，也可能找不到。

∙如果规划求解模型是线性的，并且我们选择“采用线性模型”，规划求解将使用一种非常高效的算法（单工方法）来找到该模型的最佳解决方案。

如果规划求解模型是线性的，并且我们不选择“采用线性模型”，规划求解将使用一种非常低效的算法（GRG2方法），并且可能很难找到该模型的最佳解决方案。

选择“假定非负”选项可确保规划求解只考虑每个可变单元格都采用非负值的可变单元格组合。

选择“采用线性模型”的原因是产品组合问题是一种称为线性模型的特殊规划求解问题。

基本上，在以下情况下，规划求解模型都是线性模型：

o目标单元格是通过将表单的条件（可变单元格）*（约束）相加进行计算的。

o每种约束都满足线性模型要求。

这意味着每种约束都是通过将表单的条件（可变单元格）*（约束）相加，然后将这些总和与某个常量进行比较来评估的。

这个规划求解问题为什么是线性的？

我们的目标单元格（利润）计算方式为：

（每磅药品1的利润）*

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

（每磅药品2的利润）*

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

...

（每磅药品6的利润）*

（生产的药品6的磅数）

这种计算方式遵循一种模式，即目标单元格的值是通过将表单的各个条件（可变单元格）*（约束）相加得出的.

我们的人工约束是通过将可用人工与通过以下公式得出的值进行比较来评估的：

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

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

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

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

...

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

（生产的药品6的磅数）

因此，人工约束是通过将表格的各个条件（可变单元格）*（约束）相加，然后将这类总和与某个常量进行比较来评估的。

人工约束和原材料约束都满足线性模型要求。

我们的需求约束采用以下形式：

（药品1的产量）<=（药品1的需求量）

（药品2的产量）<=（药品2的需求量）

...

（药品6的产量）<=（药品6的需求量）

每种需求约束还都符合线性模型要求，因为每种约束都是通过将表单的各个条件（可变单元格）*（约束）相加，然后将这些总和与某个常量进行比较来评估的。

已经表明我们的产品组合模型是线性模型，我们还要关心什么？

o如果规划求解模型是线性的，并且我们选择“采用线性模型”，则规划求解保证可以找到规划求解模型的最佳解决方案。

如果规划求解模型不是线性的，则规划求解可能可以找到最佳解决方案，也可能找不到。

o如果规划求解模型是线性的，并且我们选择“采用线性模型”，规划求解将使用一种非常高效的算法（单工方法）来找到该模型的最佳解决方案。

如果规划求解模型是线性的，并且我们不选择“采用线性模型”，规划求解将使用一种非常低效的算法（GRG2方法），并且可能很难找到该模型的最佳解决方案。

8.单击“规划求解选项”对话框中的“确定”后，我们将返回到主“规划求解”对话框。

当我们单击“求解”时，规划求解将为我们的产品组合模型计算出一个最佳解决方案（如果有）。

产品组合模型问题的最佳解决方案是所有可行解决方案集中可使利润最大化的一组可变单元格值（生产的每种药品的磅数）。

同样，可行解决方案是一组满足所有约束的可变单元格值。

图2中显示的可变单元格值就是一个可行解决方案，因为所有产品级别都是非负值，产品级别都没有超出需求，而且资源用量也没有超出可用资源。

图2：

符合约束的可行的产品组合问题解决方案。

由于以下原因，图3中显示的可变单元格值代表一个不可行的解决方案：

∙生产的产品5的数量大于需求数量。

∙使用的人工大于可用人工。

∙使用的原材料大于可用原材料。

图3：

不符合我们定义的约束的不可行的产品组合问题解决方案。

单击“求解”后，规划求解会迅速找出最佳解决方案，如图4所示。

您需要选择“保存规划求解解决方案”以将最佳解决方案值保留在电子表格中。

图4：

产品组合问题的最佳解决方案。

通过生产596.67磅的药品4、1084磅的药品5而不生产任何其他药品，我们的医药公司每月可获得最高利润6,625.20美元！

我们无法确定通过其他方法是否可以获得6,625.20美元的最高利润。

但我们可以确定，在我们有限的资源和需求条件下，这个月的利润根本不可能超出6,625.20美元。

规划求解模型总是有解决方案吗？

假定必须满足对每种产品的需求，那么我们就必须将我们的需求约束从D2:

I2<=D8:

I8更改为D2:

I2>=D8:

I8。

要更改此约束，请

1.打开规划求解。

2.单击“D2:

I2<=D8:

I8”约束，然后单击“更改”。

“改变约束”对话框随即出现。

3.在中部的框中，选择“>=”，然后单击“确定”。

现在我们可以确保规划求解将只考虑符合所有需求的可变单元格值。

当您单击“求解”时，您将会看到“规划求解找不到可行的解决方案”消息。

该消息意味着使用我们有限的资源，无法满足对所有产品的需求。

我们的模型并没有错！

规划求解只是要告诉我们，如果我们想要满足对每种产品的需求，我们就需要增加更多的人工、更多的原材料或两者都要增加。

如果设置目标单元格的值未收敛，意味着什么？

让我们看看如果我们允许对每种药品无限制的需求，并且允许每种药品的产量为负，会发生什么情况。

要找出针对这种情况的最佳解决方案，请执行以下操作：

1.打开规划求解。

2.单击“选项”按钮，然后清除“假定非负”复选框。

3.在“规划求解参数”对话框中，单击需求约束“D2:

I2<=D8:

I8”，然后单击“删除”以删除该约束。

当您单击“求解”时，规划求解将返回“‘设置目标单元格’的值未收敛”消息。

该消息意味着如果要最大化目标单元格（像我们的示例中一样），会存在具有任意大的目标单元格值的可行解决方案。

（如果要最小化目标单元格，该消息则意味着存在具有任意小的目标单元格值的可行解决方案。

）

在这种情况下，通过允许药品的产量为负，我们实际上“创造”了可用于生产任意大数量的其他药品的资源。

假设我们的需求没有限制，这就使得我们可以创造无限的利润。

而现实中，我们是不可能创造无限利润的。

简而言之，如果您看到了“‘设置目标单元格’的值未收敛”，就表示您的模型有错误。

自我测试

s25_1.xls至s25_5.xls文件中提供了这些问题的解决方案，这些文件包含在示例文件下载中。

1.假定我们的医药公司可以按每小时1美元的价格购买了500小时的人工。

它们将如何利用这次机会？

2.在一家芯片制造厂，有四位技术员（A、B、C和D）生产三种产品（产品1、2和3）。

芯片制造商每月可以销售80件产品1，50件产品2，产品3最多可销售50件。

技术员A只能生产产品1和3。

技术员B只能生产产品1和2。

技术员C只能生产产品3。

技术员D只能生产产品2。

对于生产的每件产品，产品1、2和3的利润分别为6美元、7美元和10美元。

下表显示了制造每件产品每个技术员需要花费的时间（小时）。

产品

技术员A

技术员B

技术员C

技术员D

1

2

2.5

不能做

不能做

2

不能做

3

不能做

3.5

3

3

不能做

4

不能做

3.

4.每名技术人员每月最大工作时间为120小时。

芯片制造商如何最大化它每月的利润？

5.一家计算机制造工厂生产鼠标、键盘和视频游戏操纵杠。

下表给出了该工厂的每件利润、每件人工工时、每月需求及每件占用的机器时间：

鼠标

键盘

操纵杆

利润/件

$8

$11

$9

人工使用/件

.2小时

.3小时

.24小时

机器时间/件

.04小时

.055小时

.04小时

每月需求

15,000

25,000

11,000

6.

7.每月共提供了13,000个人工工时和3,000小时的机器使用时间。

制造商如何最大化工厂每月的利润？

8.解析我们的药品示例，假定必须满足每种药品的最低需求200件。

9.张森是制作钻石手镯、项链和耳环的宝石商。

他每个月最多需要160人工工时。

他有800盎司的钻石。

下面给出了每种产品的利润、生产每种产品所需的人工工时和钻石盎司量。

如果对每种产品的需求是无限量的，张森如何最大化他的利润？

产品

单件利润

每件人工工时

每件钻石盎司量

手镯

$300

.35

1.2

项链

$200

.15

.75

耳环

$100

.05

.5