13.3　方案管理器

假设分析是在单元格中更改值以查看这些更改将如何影响工作表中公式结果的过程。Excel 2013附带了三种假设分析工具：模拟运算表、单变量求解和方案管理器。模拟运算表和方案管理器可获取一组输入值并确定可能的结果。其中，模拟运算表仅可以处理一个或两个变量，而一个方案可具有多个变量。

方案管理器是Excel保存并可以在工作表单元格中自动替换的一组值。Excel 2013方案管理器中的每一个方案在变量与公式计算定义的基础上，能够通过定义一系列可变单元格和对应各变量的取值构成一个方案。利用方案管理器，用户可以根据工作需要在工作表中创建和保存不同的值，然后切换到其中的任一新方案来查看不同的结果。在创建或收集到所需的全部方案后，还可以创建综合性的方案摘要报告。

在Excel 2013中，可以使用“数据”选项卡下的“数据分析”选项组中的“模拟分析”下拉列表中的“方案管理器”命令进行方案管理。

下面通过示例5，具体介绍如何在Excel 2013中使用方案管理器来进行方案管理。

示例5：

此例采用示例3中所得各项计算结果（见图13.14）为原始数据，要使销售利润达到6000万元，设定两种不同的方案。一种为增加销售总额至25000万元；另一种为在销售总额和销售成本不变的条件下，降低销售费用60万元。要求运用方案管理器比较两种方案的可行性，原始数据如图13.14所示。

使用方案管理器的具体操作步骤如下：

①命名单元格。在创建方案之前，为了使创建的方案能够明确地显示有关变量以及为了将来进行方案总结时便于查阅，有必要先给有关变量所在单元格进行命名。单元格命名的具体步骤如下：

在存放变量数据的左侧单元格中输入对应名称，选取需要命名的有关单元格区域。因为此例中需要进行单元格命名的数据区域为B2:B5，所以选定单元格区域A2:B5，转到“公式”选项卡，在“定义的名称”选项组中单击“根据所需内容创建”按钮；在弹出的如图13.26所示的“以选定区域创建名称”对话框中，勾选“最左列”复选框，如图13.27所示；单击“确定”按钮，即可完成对单元格的命名。

13.3　方案管理器 - 图1

图　13.26　“以选定区域创建名称”对话框

13.3　方案管理器 - 图2

图　13.27　勾选“最左列”复选框

②创建方案。创建方案是方案分析的关键，应该根据时间问题的需要和可行性创建一组方案。在步骤1完成对单元格命名的基础上，便可逐个创建所需的方案了。创建方案的具体操作步骤如下：

1）在原工作表中，转到“数据”选项卡，在“数据工具”选项组中单击“模拟分析”按钮，在弹出的如图13.28所示的下拉列表中选择“方案管理器”命令，弹出如图13.29所示的“方案管理器”对话框。

13.3　方案管理器 - 图3

图　13.28　“模拟分析”按钮下拉列表

13.3　方案管理器 - 图4

图　13.29　“方案管理器”对话框

2）单击图13.29所示的“方案管理器”对话框的“添加”按钮，弹出如图13.30所示的“添加方案”对话框；在该对话框的“方案名”文本框中输入输入方案的名称“增加销售总额”，在“可变单元格”编辑框中选取B2单元格，得到如图13.31所示的“编辑方案”对话框；单击“确定”按钮，弹出如图13.32所示的“方案变量值”对话框；在该对话框中输入需要达到的销售总额25000，方案变量值输入如图13.33所示；再单击“确定”按钮，即可完成对“增加销售总额”方案的创建，并弹出如图13.34所示的“方案管理器”对话框。选择单击“显示”或“关闭”按钮，选择是否更改原始数据为“方案管理器”管理结果。单击“关闭”按钮，将直接关闭“方案管理器”对话框；单击“显示”按钮，则会显示方案管理结果，如图13.35所示。

13.3　方案管理器 - 图5

图　13.30　“添加方案”对话框

13.3　方案管理器 - 图6

图　13.31　“编辑方案”对话框

13.3　方案管理器 - 图7

图　13.32　“方案变量值”对话框

13.3　方案管理器 - 图8

图　13.33　方案变量值输入

13.3　方案管理器 - 图9

图　13.34　“方案管理器”对话框

13.3　方案管理器 - 图10

图　13.35　方案管理结果

3）按照步骤2）继续创建“降低销售费用”方案，则在如图13.36所示的“编辑方案”对话框中的“方案名”输入框中输入方案名称“降低销售费用”，在“可变单元格”编辑框中选取B3单元格，且假定这个单元格中没有公式，只要是数值且数值为60即可。单击“确定”按钮，弹出如图13.37所示的“方案变量值”对话框；在该对话框中输入数值60，数值输入如图13.38所示；再单击“确定”按钮，即可完成对“降低销售费用”方案的创建，并弹出如图13.39所示的“方案管理器”对话框。

13.3　方案管理器 - 图11

图　13.36　“编辑方案”对话框

13.3　方案管理器 - 图12

图　13.37　“方案变量值”对话框

13.3　方案管理器 - 图13

图　13.38　数值输入

13.3　方案管理器 - 图14

图　13.39　“方案管理器”对话框

③创建方案摘要。在完成对方案的创建后，通常需要通过方案摘要报告来对方案进行直观的表示与分析。创建方案摘要的具体步骤为：

在图13.39所示的“方案管理器”对话框中单击“摘要”按钮，弹出如图13.40所示的“方案摘要”对话框；在该对话框的“报表类型”处单击选定“方案摘要”单选按钮，在“结果单元格”编辑框中选取B5单元格；单击“确定”按钮，即可得到如图13.41所示的方案摘要结果。在图13.41所示的“方案摘要”中，“当前值”列显示的是在建立方案汇总时，可变单元格原来的数值。每组方案的可变单元格均以灰色底纹突出显示。根据各个方案模拟数据计算出的目标值也同时显示在总结中。比较两个方案的结果单元格“利润”值可知，“增加销售总额”方案将获利6000万元，而“降低销售费用”方案获利2948.6万元。由此可见，“增加销售总额”方案对目标值的影响相对较大，说明“增加销售总额”方案的可行性更好。

13.3　方案管理器 - 图15

图　13.40　“方案摘要”对话框

13.3　方案管理器 - 图16

图　13.41　方案摘要结果

此外，用户还可以直接在数据表中显示方案。进入方案管理器，如果数据表已经存在设置好的方案，就可以在方案管理器中看到已经存在的所有方案了。选中方案中的某个方案名称，单击窗口底部的“显示”按钮，就可以直接在数据表中看到该方案的计算结果了。