使用规划求解确定最佳产品组合.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
升级会员 