Microsoft office online中关于规划求解文档的整理.doc

《Microsoft office online中关于规划求解文档的整理.doc》由会员分享，可在线阅读，更多相关《Microsoft office online中关于规划求解文档的整理.doc（24页珍藏版）》请在三一办公上搜索。

1、Microsoft office online 中关于规划求解文档的整理Kingfisher2007年5月30日更改“规划求解”的求解方法适用于: Microsoft Office Excel 20031、在“工具”菜单上，单击“规划求解”。如果“规划求解”命令没有出现在“工具”菜单上，则需要安装“规划求解”加载宏 （加载项：为 Microsoft Office 提供自定义命令或自定义功能的补充程序。）。操作方法1、在“工具”菜单上，单击“加载宏”。 2、如果在“可用加载宏”框中没有所需的加载宏 （加载项：为 Microsoft Office 提供自定义命令或自定义功能的补充程序。），请单击“

2、浏览”，再找到该加载宏。3、在“可用加载宏”框中，选中待装载的加载宏旁边的复选框，再单击“确定”。 4、如果必要，请按安装程序中的指示进行操作。 2、在“规划求解参数”对话框中，单击“选项”。 3、在“规划求解选项”对话框中，设置下列一个或多个选项: 求解时间与迭代次数1、在“最长运算时间”框中，键入限定的最长求解时间（秒数）。 2、在“迭代次数”框中，键入限定的最大迭代次数。 注释 如果求解过程在求出结果之前即达到最长求解时间或最大迭代次数，“规划求解”会出现“显示中间结果”对话框。精度在“精度”框中，键入所要求的精度：该数值越小，精度越高。允许误差在“允许误差”框中，键入在求解中限定的误差

3、百分比。 收敛度在“收敛度”框中，键入“规划求解”最后五次迭代值之间相对变化量的限定值（相对变化量小于此值时即结束求解，得出结果）：此数值越小，相邻迭代结果之间允许的相对变化就越小。 注释 可使用对话框中的“帮助”按钮来获取有关其他选项的更多信息。4、单击“确定”。 5、在“规划求解参数”对话框中，单击“求解”或“关闭”。创建规划求解报表适用于: Microsoft Office Excel 20031、定义和解决问题。 操作方法1、在“工具”菜单上，单击“规划求解”。 2、如果“规划求解”命令没有出现在“工具”菜单中，则需要安装“规划求解”加载宏 （加载项：为 Microsoft Offic

4、e 提供自定义命令或自定义功能的补充程序。）程序。操作方法1、在“工具”菜单上，单击“加载宏”。 2、如果在“当前加载宏”列表框中没有所需加载宏 （加载项：为 Microsoft Office 提供自定义命令或自定义功能的补充程序。），请单击“浏览”按钮，再找到该加载宏。3、在“当前加载宏”框中，选中待装载的加载宏旁边的复选框，再单击“确定”。 4、如果必要，请按安装程序中的指示进行操作。 3、在“设置目标单元格”框中，输入目标单元格的单元格引用 （单元格引用：用于表示单元格在工作表上所处位置的坐标集。例如，显示在第 B 列和第 3 行交叉处的单元格，其引用形式为“B3”。）或名称 （名称：代

5、表单元格、单元格区域、公式或常量值的单词或字符串。名称更易于理解，例如，“产品”可以引用难于理解的区域“Sales!C20:C30”。）。目标单元格必须包含公式 （公式：单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号 (=) 开始。）。 4、请执行下列操作之一： 若要使目标单元格中数值最大，请单击“最大值”。 若要使目标单元格中数值最小，请单击“最小值”。若要使目标单元格中数值为确定值，请单击“值为”，再在编辑框中键入数值。5、在“可变单元格”框中，输入每个可变单元格的名称或引用，用逗号分隔不相邻的引用。可变单元格必须直接或间接与目标单元格相联系。最多可以指定

6、 200 个可变单元格。 6、若要使“规划求解”基于目标单元格自动设定可变单元格，请单击“推测”。7、在“约束”框中，输入任何要应用的约束条件 （约束条件：“规划求解”中设置的限制条件。可以将约束条件应用于可变单元格、目标单元格或其他与目标单元格直接或间接相关的单元格。）。 操作方法添加约束条件 1、在“规划求解参数”对话框的“约束”下，单击“添加”。2、在“单元格引用位置”框中，输入需要对其中数值进行约束的单元格引用 （单元格引用：用于表示单元格在工作表上所处位置的坐标集。例如，显示在第 B 列和第 3 行交叉处的单元格，其引用形式为“B3”。）或单元格区域的名称 （名称：代表单元格、单元格

7、区域、公式或常量值的单词或字符串。名称更易于理解，例如，“产品”可以引用难于理解的区域“Sales!C20:C30”。）。3、单击希望在引用单元格和约束条件 （约束条件：“规划求解”中设置的限制条件。可以将约束条件应用于可变单元格、目标单元格或其他与目标单元格直接或间接相关的单元格。）之间使用的关系（“=”、“Int”或“Bin”）。如果单击“Int”，则“约束值”框中会显示“整数”；如果单击“Bin”，则“约束值”框中会显示“二进制”。4、在“约束值”框中，键入数字、单元格引用或名称，或键入公式 （公式：单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号 (=)

8、 开始。）。5、请执行下列操作之一：若要接受约束条件并要添加其他的约束条件，请单击“添加”。若要接受约束条件并返回“规划求解参数”对话框，请单击“确定”。注意只能在对可变单元格的约束条件中应用“Int”和“Bin”关系。当“规划求解选项”对话框中的“采用线性模型”复选框被选中时，对约束条件的数量没有限制。对于非线性问题，每个可变单元格除了变量的范围和整数限制外，还可以有多达 100 个约束。更改或删除约束条件1、在“规划求解参数”对话框的“约束”下，单击要更改或删除的约束条件 （约束条件：“规划求解”中设置的限制条件。可以将约束条件应用于可变单元格、目标单元格或其他与目标单元格直接或间接相关的

9、单元格。）。2、单击“更改”，并进行所需的更改，或单击“删除”。8、单击“求解”，再执行下列操作之一： 若要在工作表中保存求解后的数值，请在“规划求解结果”对话框中，单击“保存规划求解结果”。 若要恢复原始数据，请单击“恢复为原值”。 提示按 Esc 可以中止求解过程，Microsoft Excel 将按最后找到的可变单元格的数值重新计算工作表。 2、若求出解,请在“报告”框中单击一种报表类型，再单击“确定”。 报表保存在工作簿中新生成的工作表上。运用“规划求解”定义并解答问题适用于: Microsoft Office Excel 20031、在“工具”菜单上，单击“规划求解”。 2、如果“规

10、划求解”命令没有出现在“工具”菜单中，则需要安装“规划求解”加载宏 （加载项：为 Microsoft Office 提供自定义命令或自定义功能的补充程序。）程序。操作方法1、在“工具”菜单上，单击“加载宏”。 2、如果在“可用加载宏”框中没有所需的加载宏 （加载项：为 Microsoft Office 提供自定义命令或自定义功能的补充程序。），请单击“浏览”，再找到该加载宏。3、在“可用加载宏”框中，选中待装载的加载宏旁边的复选框，再单击“确定”。 4、如果必要，请按安装程序中的指示进行操作。 3、在“设置目标单元格”框中，输入目标单元格的单元格引用 （单元格引用：用于表示单元格在工作表上所处

11、位置的坐标集。例如，显示在第 B 列和第 3 行交叉处的单元格，其引用形式为“B3”。）或名称 （名称：代表单元格、单元格区域、公式或常量值的单词或字符串。名称更易于理解，例如，“产品”可以引用难于理解的区域“Sales!C20:C30”。）。目标单元格必须包含公式 （公式：单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号 (=) 开始。）。 4、请执行下列操作之一： 若要使目标单元格中数值最大，请单击“最大值”。 若要使目标单元格中数值最小，请单击“最小值”。若要使目标单元格中数值为确定值，请单击“值为”，再在编辑框中键入数值。5、在“可变单元格”框中，键入每

12、个可变单元格的名称或引用，用逗号分隔不相邻的引用。可变单元格必须直接或间接与目标单元格相联系。最多可以指定 200 个可变单元格。 6、如果要使“规划求解”根据目标单元格自动设定可变单元格，请单击“推测”。7、在“约束”框中，输入任何要应用的约束条件 （约束条件：“规划求解”中设置的限制条件。可以将约束条件应用于可变单元格、目标单元格或其他与目标单元格直接或间接相关的单元格。）。 操作方法添加约束条件 1、在“规划求解参数”对话框的“约束”下，单击“添加”。2、在“单元格引用位置”框中，输入需要对其中数值进行约束的单元格引用 （单元格引用：用于表示单元格在工作表上所处位置的坐标集。例如，显示在

13、第 B 列和第 3 行交叉处的单元格，其引用形式为“B3”。）或单元格区域的名称 （名称：代表单元格、单元格区域、公式或常量值的单词或字符串。名称更易于理解，例如，“产品”可以引用难于理解的区域“Sales!C20:C30”。）。3、单击希望在引用单元格和约束条件 （约束条件：“规划求解”中设置的限制条件。可以将约束条件应用于可变单元格、目标单元格或其他与目标单元格直接或间接相关的单元格。）之间使用的关系（“=”、“int”或“bin”）。如果单击“int”，则“约束值”框中会显示“整数”；如果单击“bin”，则“约束值”框中会显示“二进制”。4、在“约束值”框中，键入数字、单元格引用或名称，

14、或键入公式 （公式：单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号 (=) 开始。）。5、请执行下列操作之一：若要接受约束条件并要添加其他的约束条件，请单击“添加”。若要接受约束条件并返回“规划求解参数”对话框，请单击“确定”。注意只能在对可变单元格的约束条件中应用“int”和“bin”关系。当“规划求解选项”对话框中的“采用线性模型”复选框被选中时，对约束条件的数量没有限制。对于非线性问题，每个可变单元格除了变量的范围和整数限制外，还可以有多达 100 个约束。更改或删除约束条件1、在“规划求解参数”对话框的“约束”下，单击要更改或删除的约束条件 （约束条件

15、：“规划求解”中设置的限制条件。可以将约束条件应用于可变单元格、目标单元格或其他与目标单元格直接或间接相关的单元格。）。2、单击“更改”，并进行所需的更改，或单击“删除”。8、单击“求解”，再执行下列操作之一： 若要在工作表中保存求解后的数值，请在“规划求解结果”对话框中，单击“保存规划求解结果”。 若要恢复原始数据，请单击“恢复为原值”。 提示按 Esc 可以中止求解过程，Microsoft Excel 将按最后找到的可变单元格的数值重新计算工作表。使用规划求解确定最佳产品组合适用于: Microsoft Office Excel 2003适用于Microsoft Office Excel

16、2003Microsoft Excel 2000 和 2002本文改编自Microsoft Excel Data Analysis and Business Modeling（Wayne L. Winston 著）。 这本具有教学风格的图书是 Wayne Winston 根据一系列演示文稿编写而成。Wayne Winston 是一位知名的统计学家和贸易学教授，专门研究 Excel 具有创新性的实际应用。所以这本书对您开启思路可能会有所帮助。 请访问 Microsoft 学习工具（英文）购买此书。本文内容什么是 Excel 规划求解工具？ 如何确定哪种产品组合可以使利润最大化？ 如何将此模型输入

17、到规划求解中？ 规划求解模型总是有解决方案吗？ 如果设置目标单元格的值未收敛，意味着什么？ 自我测试 示例文件您可以从 Microsoft Office Online 中示例文本（英文）与 Microsoft Excel Data Analysis and Business Modeling 中的摘录有关的示例文件。本文使用了 prodmix.xls 和 s25_1.xls 至 s25_5.xls 文件。什么是 Excel 规划求解工具？当您想要寻找做某件事的最佳方法时，使用的就是规划求解。或者，更正规的说法就是，当您想要在电子表格的某些单元格中得到优化（最大化或最小化）某个目标的值时，使用的

18、就是规划求解。优化模型包括三部分：目标单元格、可变单元格和约束。目标单元格代表目的或目标。例如，最大化每月利润。 可变单元格是电子表格中我们可以进行更改或调整以优化目标单元格的单元格。例如，每月每种产品的产量。 约束是您置于可变单元格中的限制条件。例如，使用的资源不能超标，并且不能生产过剩的产品。 如何确定哪种产品组合可以使利润最大化？公司通常需要确定每月（或每周）生产计划，列出每种产品必须生产的数量。具体来说就是，产品组合问题涉及如何确定在每月应该生产的每种产品的数量以使利润最大化。产品组合通常必须满足以下约束：产品组合使用的资源不能超标。 对每种产品的需求都是有限的。我们每月生产的产品不能

19、超过需求的数量，因为生产过剩就是浪费（例如，易变质的药品）。 让我们来解决以下产品组合示例问题。您可以在 prodmix.xls 文件中找到该问题的解决方案（该文件包含在示例文件下载中），如图 1 所示。图 1：产品组合示例。假定我们在一家医药公司工作，这家公司可以在他们的工厂生产六种产品。生产每种产品都需要人工和原材料。 图 1 的第 4 行显示了生产一磅的每种产品所需的人工小时数，第 5 行显示了生产一磅的每种产品所需的原材料的磅数。例如，生产一磅的产品 1 需要 6 小时人工和 3.2 磅原材料。 第 6 行显示了每种药品每磅的价格，第 7 行显示了每磅的成本，第 9 行显示每磅可带来的

20、利润。例如，产品 2 的价格是每磅 11.00 美元，每磅的单位成本是 5.70 美元，每磅的利润就是 5.30 美元。 第 8 行显示了该月对每种药品的需求。例如，对产品 3 的需求为 1041 磅。 该月可提供 4500 人工工时和 1600 磅的原材料。该公司如何最大化它每月的利润？如果我们对规划求解一无所知，我们会通过构建一个电子表格，然后在其中跟踪每种产品组合以及与该产品组合相关联的资源用量来处理这一问题。然后我们会反复试验、不断地变化产品组合以优化利润，同时确保使用的人工或原材料不会超标，并确保不会生产出过剩药品。在此过程中，我们只在反复试验阶段中使用了规划求解。从根本上来说，规划

21、求解是一个可以完美地执行反复试验搜索的优化引擎。解决产品组合问题的关键是有效地计算与任一给定产品组合相关联的资源用量和利润。SUMPRODUCT 函数是我们可以用来执行此计算的一个重要工具。SUMPRODUCT 函数将单元格区域中相应的值相乘并返回这些值的总和。SUMPRODUCT 评估中使用的每个单元格区域都必须具有相同的维度，这意味着您可以对两行或两列使用 SUMPRODUCT，而不是对一列或一行。作为如何在产品组合示例中使用 SUMPRODUCT 函数的示例，让我们尝试计算一下我们的资源用量。通过以下计算方式可以得出人工用量：（每磅药品 1 使用的人工）*（生产的药品 1 的磅数）+（每

22、磅药品 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 中： SUMPRODUC

23、T($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 中使用以下公式可以很容易计算出利润： SUMPRODU

24、CT(D9:I9,$D$2:$I$2)现在我们可以标识出产品组合规划求解模型的三个组成部分：目标单元格可变单元格约束我们的目标是使利润（在单元格 D12 中计算）最大化。生产的每种产品的磅数（在单元格区域 D2:I2 中列出）。使用的人工和原材料不能超标。也就是说，单元格 D14:D15（所用资源）必须小于或等于单元格 F14:F15 中的值（可用资源）。 生产的药品不能超过需求数量。也就是说，单元格 D2:I2（生产的每种药品的磅数）必须小于或等于对每种药品的需求（在单元格 D8:I8 中列出）。 我们不能生产任何产量为负的药品。 如何将此模型输入到规划求解中？现在，我将向你们演示如何将目标

25、单元格、可变单元格和约束输入规划求解。然后，你们只需单击“求解”按钮即可，规划求解将会找出可使利润最大化的产品组合。1、要开始操作，请选择“工具”菜单上的“规划求解”。（有关安装规划求解的说明，请参阅使用 Excel 规划求解工具进行优化的说明。） 即会出现“规划求解参数” 对话框。2、要输入目标单元格，请在“设置目标单元格”框中单击，然后选择利润单元格（单元格 D12）。要输入可变单元格，请在“可变单元格”框中单击，然后指向区域 D2:I2，该区域包含生产的每种药品的磅数。该对话框现在看起来应如下图所示。3、现在我们已经可以向模型中添加约束了。单击“添加”按钮，您可以看到“添加约束”对话框。

26、4、要添加资源用量约束，请在标记为“单元格引用位置”的框中单击，然后选择区域 D14:D15。从对话框中部的列表中选择“=”。在标记为“约束值”的框中单击，然后选择单元格区域 F14:F15。现在我们已经确保当规划求解尝试对可变单元格使用不同的值时，规划求解将只考虑同时满足 D14 = F14（所用人工小于或等于可用人工）和 D15 = F15（所用原材料小于或等于可用原材料）的组合。 5、现在，在“添加约束”对话框中单击“添加”，以输入需求约束。只需如下图所示填充“添加约束”对话框即可。添加这些约束可以确保当规划求解尝试对可变单元格值使用不同的组合时，规划求解将只考虑满足以下条件的组合：D2

27、 = D8（药品 1 的产量小于或等于对药品 1 的需求量） E2 = E8（药品 2 的产量小于或等于对药品 2 的需求量） F2 = F8（药品 3 的产量小于或等于对药品 3 的需求量） G2 = G8（药品 4 的产量小于或等于对药品 4 的需求量） H2 = H8（药品 5 的产量小于或等于对药品 5 的需求量） I2 = I8（药品 6 的产量小于或等于对药品 6 的需求量） 6、单击“添加约束”对话框中的“确定”。“规划求解参数”对话框应如下图所示。7、在“规划求解选项”对话框中输入所有可变单元格都为非负值的约束，通过单击“规划求解参数”对话框中的“选项”按钮可打开该对话框。 选

28、择“采用线性模型”和“假定非负”选项，然后单击“确定”。为什么要选择这些选项？选择“假定非负”选项可确保规划求解只考虑每个可变单元格都采用非负值的可变单元格组合。 选择“采用线性模型”的原因是产品组合问题是一种称为线性模型的特殊规划求解问题。基本上，在以下情况下，规划求解模型都是线性模型：目标单元格是通过将表单的条件（可变单元格）*（约束）相加进行计算的。 每种约束都满足线性模型要求。这意味着每种约束都是通过将表单的条件（可变单元格）*（约束）相加，然后将这些总和与某个常量进行比较来评估的。 这个规划求解问题为什么是线性的？ 我们的目标单元格（利润）计算方式为： （每磅药品 1 的利润）*（生

29、产的药品 1 的磅数）+（每磅药品 2 的利润）*（生产的药品 2 的磅数）+.（每磅药品 6 的利润）*（生产的药品 6 的磅数）这种计算方式遵循一种模式，即目标单元格的值是通过将表单的各个条件（可变单元格）*（约束）相加得出的.我们的人工约束是通过将可用人工与通过以下公式得出的值进行比较来评估的： （每磅药品 1 使用的人工）*（生产的药品 1 的磅数）+ （每磅药品 2 使用的人工）*（生产的药品 2 的磅数）+ .（每磅药品 6 使用的人工）*（生产的药品 6 的磅数）因此，人工约束是通过将表格的各个条件（可变单元格）*（约束）相加，然后将这类总和与某个常量进行比较来评估的。人工约束和

30、原材料约束都满足线性模型要求。我们的需求约束采用以下形式：（药品 1 的产量）=（药品 1 的需求量）（药品 2 的产量）=（药品 2 的需求量）.（药品 6 的产量）=（药品 6 的需求量）每种需求约束还都符合线性模型要求，因为每种约束都是通过将表单的各个条件（可变单元格）*（约束）相加，然后将这些总和与某个常量进行比较来评估的。已经表明我们的产品组合模型是线性模型，我们还要关心什么？如果规划求解模型是线性的，并且我们选择“采用线性模型”，则规划求解保证可以找到规划求解模型的最佳解决方案。如果规划求解模型不是线性的，则规划求解可能可以找到最佳解决方案，也可能找不到。 如果规划求解模型是线性的

31、，并且我们选择“采用线性模型”，规划求解将使用一种非常高效的算法（单工方法）来找到该模型的最佳解决方案。如果规划求解模型是线性的，并且我们不选择“采用线性模型”，规划求解将使用一种非常低效的算法（GRG2 方法），并且可能很难找到该模型的最佳解决方案。 8、单击“规划求解选项”对话框中的“确定”后，我们将返回到主“规划求解”对话框。当我们单击“求解”时，规划求解将为我们的产品组合模型计算出一个最佳解决方案（如果有）。产品组合模型问题的最佳解决方案是所有可行解决方案集中可使利润最大化的一组可变单元格值（生产的每种药品的磅数）。同样，可行解决方案是一组满足所有约束的可变单元格值。图 2 中显示的可

32、变单元格值就是一个可行解决方案，因为所有产品级别都是非负值，产品级别都没有超出需求，而且资源用量也没有超出可用资源。图 2：符合约束的可行的产品组合问题解决方案。由于以下原因，图 3 中显示的可变单元格值代表一个不可行的解决方案：生产的产品 5 的数量大于需求数量。 使用的人工大于可用人工。 使用的原材料大于可用原材料。 图 3：不符合我们定义的约束的不可行的产品组合问题解决方案。单击“求解”后，规划求解会迅速找出最佳解决方案，如图 4 所示。您需要选择“保存规划求解解决方案”以将最佳解决方案值保留在电子表格中。图 4：产品组合问题的最佳解决方案。通过生产 596.67 磅的药品 4、1084

33、 磅的药品 5 而不生产任何其他药品，我们的医药公司每月可获得最高利润 6,625.20 美元！我们无法确定通过其他方法是否可以获得 6,625.20 美元的最高利润。但我们可以确定，在我们有限的资源和需求条件下，这个月的利润根本不可能超出 6,625.20 美元。规划求解模型总是有解决方案吗？假定必须满足对每种产品的需求，那么我们就必须将我们的需求约束从 D2:I2 = D8:I8。要更改此约束，请 1、打开规划求解。 2、单击“D2:I2 =”，然后单击“确定”。 现在我们可以确保规划求解将只考虑符合所有需求的可变单元格值。当您单击“求解”时，您将会看到“规划求解找不到可行的解决方案”消息

34、。该消息意味着使用我们有限的资源，无法满足对所有产品的需求。我们的模型并没有错！规划求解只是要告诉我们，如果我们想要满足对每种产品的需求，我们就需要增加更多的人工、更多的原材料或两者都要增加。如果设置目标单元格的值未收敛，意味着什么？让我们看看如果我们允许对每种药品无限制的需求，并且允许每种药品的产量为负，会发生什么情况。要找出针对这种情况的最佳解决方案，请执行以下操作：1、打开规划求解。 2、单击“选项”按钮，然后清除“假定非负”复选框。 3、在“规划求解参数”对话框中，单击需求约束“D2:I2 = D8:I8”，然后单击“删除”以删除该约束。 当您单击“求解”时，规划求解将返回“设置目标单

35、元格的值未收敛”消息。该消息意味着如果要最大化目标单元格（像我们的示例中一样），会存在具有任意大的目标单元格值的可行解决方案。（如果要最小化目标单元格，该消息则意味着存在具有任意小的目标单元格值的可行解决方案。） 在这种情况下，通过允许药品的产量为负，我们实际上“创造”了可用于生产任意大数量的其他药品的资源。假设我们的需求没有限制，这就使得我们可以创造无限的利润。而现实中，我们是不可能创造无限利润的。简而言之，如果您看到了“设置目标单元格的值未收敛”，就表示您的模型有错误。自我测试s25_1.xls 至 s25_5.xls 文件中提供了这些问题的解决方案，这些文件包含在示例文件下载中。1、假定

36、我们的医药公司可以按每小时 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技术员

37、C技术员 D122.5不能做不能做2不能做3不能做3.533不能做4不能做每名技术人员每月最大工作时间为 120 小时。芯片制造商如何最大化它每月的利润？3、一家计算机制造工厂生产鼠标、键盘和视频游戏操纵杠。下表给出了该工厂的每件利润、每件人工工时、每月需求及每件占用的机器时间： 鼠标键盘操纵杆利润/件$8$11$9人工使用/件.2 小时.3 小时.24 小时机器时间/件.04 小时.055 小时.04 小时每月需求15,00025,00011,000每月共提供了 13,000 个人工工时和 3,000 小时的机器使用时间。制造商如何最大化工厂每月的利润？4、解析我们的药品示例，假定必须满足每

38、种药品的最低需求 200 件。 5、张森是制作钻石手镯、项链和耳环的宝石商。他每个月最多需要 160 人工工时。他有 800 盎司的钻石。下面给出了每种产品的利润、生产每种产品所需的人工工时和钻石盎司量。如果对每种产品的需求是无限量的，张森如何最大化他的利润？ 产品单件利润每件人工工时每件钻石盎司量手镯$300.351.2项链$200.15.75耳环$100.05.5加载“规划求解”模型适用于: Microsoft Office Excel 2003在加载某个模型之前，必须已经保存了该模型。1、在“工具”菜单上，单击“规划求解”。 2、在“规划求解参数”对话框中，单击“选项”。 3、在“规划求

39、解选项”对话框中，单击“装入模型”。 4、在编辑框中输入对包含规划求解模型的整个单元格区域的引用。使用 Excel 规划求解工具进行优化的说明适用于: Microsoft Office Excel 2003适用于Microsoft Office Excel 2003Microsoft Excel 2000 和 2002本文改编自Microsoft Excel Data Analysis and Business Modeling（Wayne L. Winston 著）。 这本具有教学风格的图书是 Wayne Winston 根据一系列演示文稿编写而成。Wayne Winston 是一位知名的统

40、计学家和贸易学教授，专门研究 Excel 具有创新性的实际应用。所以这本书对您开启思路可能会有所帮助。 请访问 Microsoft 学习工具（英文） 购买此书。本文内容什么是优化？定义优化模型 安装和运行规划求解 自我测试 什么是优化？一家大型药品公司如何确定他们在印第安纳波利斯工厂每月的产品组合以最大化公司的利润？ 如果 Microsoft 在三个地点生产 Xbox 控制手柄，他们如何最大限度地降低成本并满足对 Xbox 控制手柄的需求？ 如何定价 Xbox 控制手柄和游戏可以使 Xbox 销售利润最大化？ Microsoft 在随后的五年里要实施 20 项战略性计划，这将占用大量资金和有技

41、能的编程人员。他们没有足够的资源来同时实施这 20 个项目。他们应该实施哪些项目？ 博彩公司如何设置 NFL 球队的最佳“级别”以确定准确的让分？ 在高科技股票、价值型股票、债券、现金和黄金之间，我应该如何分配我的退休投资组合？ 在上述所有情形中，我们都需要找出最佳的操作方法。更正规的说法就是，我们需要在电子表格的某些单元格中得到优化（最大化或最小化）某个目标的值。Excel 规划求解工具可帮助您解决优化问题。定义优化模型优化模型包括三部分：目标单元格、可变单元格和约束。 目标单元格目标单元格代表目的或目标。我们需要最小化或最大化目标单元格。在药品公司的产品组合示例中，工厂主管可能希望最大化工

42、厂每月的利润。衡量利润的单元格就是目标单元格。下表列出了本文开头描述的每种情况的目标单元格。模型最大化或最小化目标单元格医药公司产品组合最大化每月利润Xbox 运送最小化配送成本Xbox 价格最大化Xbox 控制手柄和游戏的利润Microsoft 项目计划最大化选中项目带来的净现值 (NPV)NFL 级别最小化通过级别和实际比赛得分推测出来的分数差异退休投资组合最小化投资组合的风险待添加的隐藏文字内容1请记住，在有些情况下，您可能有多个目标单元格。例如，Microsoft 可能有一个次要目标，即最大化 Xbox 的市场份额。可变单元格可变单元格是电子表格中我们可以进行更改或调整以优化目标单元格

43、的单元格。在医药公司示例中，工厂主管可以每月调整每种产品的产量。记录这些数量的单元格就是本模型中的可变单元格。下表列出了本文开头描述的模型相应的可变单元格定义。模型可变单元格医药公司产品组合每月每种产品的产量Xbox 运送每月每家工厂运送给每位客户的产量Xbox 价格控制手柄和游戏价格Microsoft 项目计划选中的那些项目NFL 级别球队级别退休投资组合投资于每种资产类别的金额约束约束是您置于可变单元格中的限制条件。在我们的产品组合示例中，产品组合的使用资源（如原材料和人工）不能超标。另外，生产出的产品数量也不能超出人们的购买能力。在大多数规划求解模型中，有一个隐含的约束就是所有可变单元都

44、必须是非负数。在以后章节中我将详细讨论非负性约束。请记住，规划求解模型不能不需要一些约束。下表列出了本章开头提出的问题的约束。模型约束医药公司产品组合产品组合使用的资源不能超标 不能生产过剩产品Xbox 运送每月从各工厂出厂的件数不能超过该工厂的生产能力 确保每家客户都能收到所需数量的 XboxXbox 价格价格不能太偏离竞争对手的价格Microsoft 项目计划选中项目所使用的资金或有技能的编程人员不能超标NFL 级别无退休投资组合将资金全部投资于某一领域（可能是现金） 预计获取至少百分之十的投资回报安装和运行规划求解要安装规划求解，请单击“工具”菜单上的“加载宏”，然后选择“规划求解”加载

45、宏复选框。单击“确定”，Excel 将安装规划求解。安装该加载宏后，您可以通过单击“工具”菜单上的“规划求解”来运行规划求解。 下图显示了“规划求解参数”对话框，您可以在其中输入要应用到优化模型的目标单元格、可变单元格和约束。注释您可以通过本文“请参阅”一节中列出的各个规划求解模型文章来详细了解如何执行此操作。输入目标单元格、可变单元格和约束后，规划求解会执行哪些操作呢？要回答这个问题，您需要了解一些有关规划求解专业术语的背景。可变单元格的符合该模型约束的任意指定均被称为可行的解决方案。例如，在我们的产品组合示例中，符合以下三个条件的任何产品组合都将是可行的解决方案：组合使用的原材料和人工没有

46、超标。 组合产出的每种产品没有过剩。 每种产品的产量为非负数。 基本上来说，规划求解会搜索所有可行的解决方案并找到具有“最佳”目标单元格值（最大优化为最大值，最小优化为最小值）的可行解决方案。这类解决方案被称为最佳解决方案。有些规划求解模型没有最佳解决方案，有些只有一个解决方案，而有些规划求解模型则有多个（实际上是无数个）最佳解决方案。 了解如何使用规划求解的最好方法是查看详细的示例。在本文的“请参阅”一节中，您可以找到其他文章的链接，这些文章介绍了如何使用规划求解来解决许多重要的业务（和非业务）问题。自我测试对于下面描述的每种情形，请标识出目标单元格、可变单元格和约束。我借贷了 10 万美元，15 年按揭。年利率为 8 分。我每月支付还款。如何确定我每月要支付的按揭额？ 一家自动化公司如何在不同的广告形式之间分配它的广告预算？ 一个城市应该在哪设立它唯一的一家医院？ 医药公司应该如何为他们的产品分配销售力量？ 一家医药公司拨出 20 亿美元来购买一些生物工程公司。他们应该购买哪些公司？ 医药公司需要支付的税率取决于生产某个产品的国家/地区。医药公司如何确定应该在哪里生产药品？ 重新