Excel技巧应用篇：规划求解——以多个变量来寻求最佳方案！

本期我们来了解一下Excel的“规划求解”，这是一个相对较复杂的分析工具，但是在对数据的预测分析上，可谓功能强大，不仅可以针对多个变量进行方案的设计，同时在此工具中整合了“方案管理”的功能，为我们展示不同的方案以便进行对比，寻求符合工作实际需求的方案。

我们以如图所示的项目数据为例。

所期望的需求是：当B5单元格中的“Manuals”为12，并且E7单元格的“duration”为20时，是否E20单元格中的“Profit”仍能达到一个比较理想的结果。

当修改B5单元格中的数据为12后，E7单元格的数据变为30，要使其变成20，我们可以尝试对B7至B9单元格中的“staff”、“Working day”和“Working week”进行修改。

在Excel中有一个工具“单变量求解”（Excel的单变量求解，快速计算含公式的目标值所对应的变量！），它仅允许我们输入一个变量，而无法实现多个变量的求解，因此我们在此例中使用“规划求解”这一工具。

点击“数据”选项卡，在“分析”区域，点击“规划求解”。若在选项卡区域没有此工具，可以通过以下方式来添加。

1. 打开“Excel选项”对话框，点击“加载项”，再点击管理Excel加载项的“转到”。

2. 打开“加载项”对话框后，勾选“规划求解加载项”，点击“确定”。

添加好“规划求解”工具后，点击此工具按钮，打开其对话框。

首先“设置目标”，选择E7单元格，即对应的“duration”数据。

其次，设置目标值为20。在此我们有三种选择：最大值、最小值和目标值，可根据实际需要进行设置。

再次，选择“通过更改可变单元格”，即我们之前提到的多个变量。此例中我们选择的B7至B9单元格，如果变量是不相邻的单元格，可以通过按住Ctrl键来添加或逗号隔开。

点击“求解”后，会转到另一个对话框“规划求解结果”，或者说得到一个方案。

然而，我们看到此方案下虽然“duration”目标值达到20，并且通过调整B7至B9单元格中的数据来达到此目标，但“Projected Profit”并不理想。

尽管我们不会采纳此方案，但我们仍希望能将其保存，因此当我们点击“保存方案”时，会跳转到另一个对话框，要求我们为此方案命名，我们将其命名为“20 Weeks”，即此方案是“duration”为20的情况。

点击“确定”后，回到“规划求解结果”对话框，在此我们有多种可选操作：“保留规划求解的解”、“还原初值”、“返回规划求解对话框”、“制作报告大纲”等。

我们勾选“还原初值”，然后点击“确定”。

为了方便之后的数据对比，我们将初值的方案添加到“方案管理器”（Excel的方案管理器，快速查看多种方案下的数据变化！）中，即“duration”为30的情况。

我们继续通过“规划求解”来找到更加合适的方案，但这次我们有一些对变量的约束，即“staff”不超过12，“Working day”不超过8，“Working week”不超过6。

点击“规划求解”中的“添加”按钮。

在“添加约束”对话框中，“单元格引用”为B7单元格，选择小于等于（<=），“约束”为12。

以此类推，继续添加另外两个变量的约束。回到“规划求解”对话框后，我们在“遵守约束”框中可看到已添加的变量约束。

点击“求解”后，我们看到“duration”为21，三个变量也分别进行了相应的调整，但是“Projected Profit”仍不理想。

保存当前的方案，并命名为“21 Weeks”。

回到“规划求解结果”对话框中，勾选“还原初值”和“返回规划求解对话框”。

这一次我们将改变“设置目标”，将其设为“Projected Profit”，并且计算出其为“最大值”的方案。

点击“求解”后，我们看到“Projected Profit”为所能达到的最大值，在当前三个变量被约束的情况下，然而“duration”则变成了42，相当于上一个方案的两倍。

保存当前的方案并命名为“42 Weeks”。

回到“规划求解结果”对话框中，我们这次“保留规划求解的解”，点击“确定”。

我们最后再次点击“方案管理器”，通过“摘要”来展示不同的方案所得到的结果。

新生成的“方案摘要”工作表如下。

以上即是我们本期的主要内容——规划求解，要想熟练使用好此工具，还得应用到实际工作的案例中去，多多练习。

#新技能get#