运用“规划求解”定义并求解问题

规划求解是 Microsoft Excel 加载项程序，可用于模拟分析。使用“规划求解”查找一个单元格（称为目标单元格）中公式的优化（最大或最小）值，受限或受制于工作表上其他公式单元格的值。 “规划求解”与一组用于计算目标和约束单元格中公式的单元格（称为决策变量或变量单元格）一起工作。 “规划求解”调整决策变量单元格中的值以满足约束单元格上的限制，并产生您对目标单元格期望的结果。

简单来说，使用“规划求解”可通过更改其他单元格来确定一个单元格的最大值或最小值。例如，你可以更改计划的广告预算金额，并查看对计划利润额产生的影响。

Windows macOS Web



注意: Excel 2007 之前的规划求解版本将目标单元格称为“目标单元格”，将决策变量单元格称为“更改单元格”或“可调整单元格”。对 Excel 2010 的规划求解加载项进行了许多改进，因此，如果你使用的是 Excel 2007，你的体验将略有不同。

在以下示例中，每个季度的广告级别影响销售的单位数，间接确定销售收入、关联费用和利润。 “规划求解”可以更改广告的季度预算（决策变量单元格 B5:C5），最多 200,000 人民币的总预算限制（单元格 F5），直到总利润（目标单元格 F7）达到最大可能数量。变量单元格中的值用于计算每个季度的利润，因此它们与公式目标单元格 F7、=SUM (Q1 Profit:Q2 Profit) 相关。

使用“规划求解”之前

1. 变量单元格

2. 约束条件单元格

3. 目标单元格

运行“规划求解”后得到的新数值如下。

使用“规划求解”之后

在“数据”选项卡的“分析”组中，单击“规划求解”。

注意: 如果“规划求解”命令或“分析”组不可用，则需要激活“规划求解”加载项。请参阅：如何激活规划求解加载项。
在“设置目标”框中,输入目标单元格的单元格引用或名称。目标单元格必须包含公式。
执行下列操作之一：
- 若要使目标单元格的值尽可能大，请单击“最大值”。
- 若要使目标单元格的值尽可能小，请单击“最小值”。
- 若要使目标单元格为确定值，请单击“值”，然后在框中键入数值。
- 在“通过更改可变单元格”框中，输入每个决策变量单元格区域的名称或引用。用逗号分隔不相邻的引用。可变单元格必须直接或间接与目标单元格相关联。最多可以指定 200 个可变单元格。
在“遵守约束”框中，通过执行下列操作输入任何要应用的约束：
1. 在“规划求解参数”对话框中，单击“添加”。
2. 在“单元格引用”框中，输入要对其中数值进行约束的单元格区域的单元格引用或名称。
3. 单击所引用单元格与约束之间所需的 (< =、 =、 >=、 int、 bin 或 dif ) 的关系。如果单击 int，整数将显示在 “约束 ”框中。如果单击 bin， 二进制文件 将显示在 “约束 ”框中。如果单击 dif， 所有差异 将显示在 “约束 ”框中。
4. 如果在“约束”框中选择关系 <=、= 或 >=，请键入数字、单元格引用或名称、公式。
5. 执行下列操作之一：
  - 要接受约束并添加另一个约束，请单击“添加”。
  - 要接受约束条件并返回“规划求解参数”对话框，请单击“确定”。
    注意只能为决策变量单元格上的约束条件应用 int、bin 和 dif 关系。
    
    通过执行下列操作可以更改或删除现有的约束：
6. 在“规划求解参数”对话框中，单击要更改或删除的约束条件。
7. 单击“更改”并进行更改，或单击“删除”。
单击“求解”，再执行下列操作之一：
- 若要在工作表中保存求解值，请在“规划求解结果”对话框中单击“保存规划求解的解”。
- 若要在单击“求解”之前恢复原值，请单击“恢复原值”。
- 您可以按 Esc 键中断求解过程。 Excel 利用找到的有关决策变量单元格的最后值重新计算工作表。
- 要在“规划求解”找到解决方案后创建基于您的解决方案的报告，您可以单击“报表”框中的报告类型，然后单击“确定”。此报告是在工作簿中的一个新工作表上创建的。如果“规划求解”未找到解决方案，则只有部分报表可用或全部不可用。
- 要将决策变量单元格值保存为可以稍后显示的方案，请在“规划求解结果”对话框中单击“保存方案”，然后在“方案名”框中键入方案的名称。

定义了问题之后，请在“规划求解参数”对话框中单击“选项”。
在“选项”对话框中，选中“显示迭代结果”复选框以查看每个试解的结果，然后单击“确定”。
在“规划求解参数”对话框中，单击“求解”。
在“显示中间结果”对话框中，请执行下列操作之一：
- 要停止求解过程并显示“规划求解结果”对话框，请单击“停止”。
- 要继续求解过程并显示下一个中间结果，请单击“继续”。

在“规划求解参数”对话框中，单击“选项”。
为对话框中“所有方法”、“GRG 非线性”和“进化”选项卡上的任意选项选择或输入值。

在“规划求解参数”对话框中，单击“加载/保存”。
为模型范围输入单元格区域，然后单击“保存”或“加载”。

在保存模型时，为要放置该问题模型的空单元格区域中垂直范围的第一个单元格输入引用。装入模型时，输入包含问题模型的整个单元格区域的引用。

提示: 可以通过保存工作簿，使用工作表在“ 规划求解参数 ”对话框中保存最后一个选择。工作簿中的每个工作表可能都有自己的规划求解器选择，并且所有选项都已保存。还可以通过单击“ 加载/保存 ”为工作表定义多个问题，以单独保存问题。

您可以在“规划求解参数”对话框中选择以下三种算法或求解方法中的任意一种：

广义简约梯度 (GRG) 非线性 用于平滑非线性问题。
LP Simplex 用于线性问题。
进化用于非平滑问题。

重要: 应首先启用规划求解加载项。有关详细信息，请参阅加载规划求解加载项。

在以下示例中，每个季度的广告级别影响销售的单位数，间接确定销售收入、关联费用和利润。求解器可以更改广告 (决策变量单元格 B5：C5) 的季度预算， (单元格 D5) 的总预算限制为 20,000 美元，直到目标单元格 D7) 的总利润 (达到最大可能金额。变量单元格中的值用于计算每个季度的利润，因此它们与公式目标单元格 D7， =SUM (Q1 利润：Q2 利润) 相关。

示例规划求解计算

标注 1 变量单元格

标注 2 约束单元格

标注 3 目标单元格

运行“规划求解”后得到的新数值如下。

使用新值进行示例规划求解计算

在Excel 2016 for Mac中：单击“数据 > 规划求解”。

在 Excel for Mac 2011：单击“数据”选项卡，在“分析”下，单击“规划求解”。
在 “设置目标”中，输入目标单元格的单元格引用或名称。

注意: 目标单元格必须包含公式。

执行下列操作之一：

若要	要执行的操作
使目标单元格的值尽可能大	单击“ 最大”。
使目标单元格的值尽可能小	单击“ 最小值”。
将目标单元格设置为特定值	单击“ 值”，然后在框中键入值。

在“通过更改可变单元格”框中，输入每个决策变量单元格区域的名称或引用。用逗号分隔非相邻引用。

可变单元格必须直接或间接与目标单元格相关联。最多可以指定 200 个可变单元格。

在“ 约束主体 ”框中，添加要应用的任何约束。

若要添加约束，请执行以下步骤：

在“规划求解参数”对话框中，单击“添加”。
在“单元格引用”框中，输入要对其中数值进行约束的单元格区域的单元格引用或名称。
在 “<= 关系”弹出菜单上，选择所引用单元格与约束之间所需的关系。如果选择 <=、 =或 >=，请在 “约束 ”框中键入数字、单元格引用或名称或公式。

注意: 只能在决策变量单元格的约束中应用 int、bin 和 dif 关系。
执行下列操作之一：

若要	要执行的操作
接受约束并添加另一个约束	单击“添加”。
接受约束并返回到“ 规划求解参数 ”对话框	单击“确定”。

单击“ 求解”，然后执行下列操作之一：

若要	要执行的操作
将解决方案值保留在工作表上	单击“ 规划求解结果”对话框中的“保留规划求解解决方案 ”。
还原原始数据	单击“ 还原原始值”。

注意:

若要中断解决方案过程，请按 ESC 。 Excel 使用为可调整单元格找到的最后值重新计算工作表。
要在“规划求解”找到解决方案后创建基于您的解决方案的报告，您可以单击“报表”框中的报告类型，然后单击“确定”。报表是在工作簿中的新工作表上创建的。如果规划求解找不到解决方案，则创建报表的选项不可用。
若要将调整单元格值保存为稍后可显示的方案，请在“规划求解结果”对话框中单击“保存方案”，然后在“方案名称”框中键入方案的名称。

在Excel 2016 for Mac中：单击“数据 > 规划求解”。

在 Excel for Mac 2011：单击“数据”选项卡，在“分析”下，单击“规划求解”。
定义问题后，在“ 规划求解参数 ”对话框中，单击“ 选项”。
选中“显示迭代结果检查”框以查看每个试用解决方案的值，然后单击“确定”。
在“规划求解参数”对话框中，单击“求解”。

在“ 显示试用解决方案 ”对话框中，执行以下操作之一：

若要	要执行的操作
停止解决方案进程并显示“ 规划求解结果 ”对话框	单击“ 停止”。
继续解决方案过程并显示下一个试用解决方案	单击“继续”。

在Excel 2016 for Mac中：单击“数据 > 规划求解”。

在 Excel for Mac 2011：单击“数据”选项卡，在“分析”下，单击“规划求解”。

单击“ 选项”，然后在“ 选项” 或“ 规划求解选项 ”对话框中，选择以下一个或多个选项：

若要	要执行的操作
设置解决方案时间和迭代	在“ 所有方法 ”选项卡上的“ 解决限制”下，在“ 最大时间 (秒) ”框中，键入要为解决方案时间允许的秒数。然后，在“ 迭代 ”框中，键入要允许的最大迭代次数。注意: 如果解决方案过程达到规划求解找到解决方案之前的最大迭代时间或次数，规划求解将显示“ 显示试用解决方案 ”对话框。
设置精度	在“ 所有方法 ”选项卡上的“ 约束精度 ”框中，键入所需的精度程度。数字越小，精度越高。
设置收敛程度	在 “GRG 非线性 ”或“ 进化 ”选项卡上的“ 收敛 ”框中，键入要在规划求解停止使用解决方案之前在最近五次迭代中允许的相对更改量。数字越小，允许的相对变化就越少。

单击“确定”。
在“ 规划求解参数 ”对话框中，单击“ 求解 ”或“ 关闭”。

在Excel 2016 for Mac中：单击“数据 > 规划求解”。

在 Excel for Mac 2011：单击“数据”选项卡，在“分析”下，单击“规划求解”。
单击“ 加载/保存”，输入模型区域的单元格区域，然后单击“ 保存 ”或“ 加载”。

在保存模型时，为要放置该问题模型的空单元格区域中垂直范围的第一个单元格输入引用。装入模型时，输入包含问题模型的整个单元格区域的引用。

提示: 可以通过保存工作簿，使用工作表在“ 规划求解参数 ”对话框中保存最后一个选择。工作簿中的每个工作表可能都有自己的规划求解器选项，并且所有选项都已保存。还可以通过单击“ 加载/保存 ”为工作表定义多个问题，以单独保存问题。

在Excel 2016 for Mac中：单击“数据 > 规划求解”。

在 Excel for Mac 2011：单击“数据”选项卡，在“分析”下，单击“规划求解”。
在 “选择求解方法 ”弹出菜单上，选择以下选项之一：

求解方法	说明
GRG (通用还原梯度) 非线性	默认选项，适用于使用大多数 Excel 函数（IF、CHOOSE、LOOKUP 和其他“步骤”函数）的模型。
Simplex LP	对于线性编程问题，请使用此方法。模型应在依赖于变量单元格的公式中使用 SUM、SUMPRODUCT、 + 和 *。
进化	当模型使用 IF、CHOOSE 或 LOOKUP 以及依赖于变量单元格的参数时，此方法基于遗传算法是最佳方法。