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

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

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

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

使用“规划求解”之前

1. 变量单元格

2. 约束条件单元格

3. 目标单元格

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

使用“规划求解”之后

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

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

定义问题后，在“规划求解参数”对话框中选择“选项”。
在“选项”对话框中，选中“显示迭代结果检查”框以查看每个试用解决方案的值，然后选择“确定”。
在“ 规划求解参数 ”对话框中，选择“求解”。
在“ 显示试用解决方案 ”对话框中，执行以下操作之一。
- 若要停止解决方案进程并显示“ 规划求解结果 ”对话框，请选择“停止”。
- 若要继续解决方案过程并显示下一个试用解决方案，请选择“继续”。

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

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

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

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

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

通用还原梯度 (GRG) 非线性： 用于平滑非线性问题。
LP Simplex： 用于线性问题。
进化： 用于非平滑问题。

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

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

示例规划求解计算

标注 1 变量单元格

标注 2 约束单元格

标注 3 目标单元格

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

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

选择“数据 > 规划求解”。
在 “设置目标”中，输入目标单元格的单元格引用或名称。

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

执行以下步骤之一。

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

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

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

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

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

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

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

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

选择“求解”，然后执行下列操作之一。

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

注意:

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

选择“数据 > 规划求解”。
定义问题后，在“ 规划求解参数 ”对话框中，选择“选项”。
选中“显示迭代结果检查”框以查看每个试用解决方案的值，然后选择“确定”。
在“ 规划求解参数 ”对话框中，选择“求解”。

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

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

选择“数据 > 规划求解”。

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

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

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

选择“数据 > 规划求解”。
选择“加载/保存”，输入模型区域的单元格区域，然后选择“ 保存 ”或“ 加载”。

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

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

选择“数据 > 规划求解”。
在 “选择求解方法 ”弹出菜单上，选择以下选项之一：

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