在Excel中,可以使用“方案管理器”创建不同的假设分析方案,来预测使用不同组合的输入值计算出的不同结果。创建方案后可以在“方案管理器”中方便地查看不同方案所对应的数据表的数值变化,还可以生成方案总结报表以供预测分析。

定义方案

下面通过一个具体案例来说明如何定义一个方案。如图22-7所示,这是一个简单的图书销售利润统计表,其中顾客折扣(单元格B2)、运费(单元格B3)和数量(单元格B4)是输入单元格。

Excel 假设分析方案应用技巧-Excel22

图22-7 图书销售利润统计表

在本例中,单价、进货折扣为固定值,单本书售价、单本书的利润、每种书的总利润、总利润需使用简单的公式计算得出,其公式分别如下:

  • 单本书售价:从左至右依次为“=B7*B2”、“=C7*B2”、“=D7*B2”、“=E7*B2”。
  • 单本书的利润:从左至右依次为“=B9-B7*(B2-B8-B3)”、“=C9-C7*(B2-C8-B3)”、“=D9-D7*(B2-D8-B3)”、“=E9-E7*(B2-E8-B3)”。
  • 每种书的总利润:从左至右依次为“=B4*B10”、“=B4*C10”、“=B4*D10”、“=B4*E10”。
  • 总利润:“=B11+C11+C11+D11”。

如果希望分析不同的顾客折扣、运费和数量下书籍销售的利润情况,则可以确定不同的方案,如可以分为“促销期”、“滞销期”、“常销期”三个方案,如表22-1所示。

表22-1 三个不同的方案

Excel 假设分析方案应用技巧-Excel22

创建方案

下面通过实例介绍如何创建假设分析的方案。

步骤1:单击“数据”|“预测”|“模拟分析”|“方案管理器”命令,打开“方案管理器”对话框,如图22-8所示。因为是第一次打开该对话框,此时会出现“未定义方案,若要增加方案,请选定‘添加’按钮”的提示。

步骤2:单击“添加”按钮,打开“添加方案”对话框。

步骤3:在“方案名”框中输入方案的名字,例如此处输入“促销期”。

步骤4:在“可变单元格”框中输入可变单元格的地址,此处按住Ctrl键的同时单击B2、B3、B4单元格,这样可以在“可变单元格”框中输入“$B$2,$B$3,$B$4”,如图22-9所示。

Excel 假设分析方案应用技巧-Excel22

图22-8 “方案管理器”对话框

Excel 假设分析方案应用技巧-Excel22

图22-9 输入可变单元格

“添加方案”对话框各选项简介如下。

  • 方案名:假设分析方案的名字,可以使用任意的名称,但最好能有助于识别方案的内容。
  • 可变单元格:在此输入引用单元格的地址,允许输入多个单元格,而且输入单元格不必是相邻的。也可以按住Ctrl键的同时单击要输入的单元格,Excel会自动完成输入。
  • 备注:默认会显示创建者的名字以及创建的日期,也可以根据实际情况输入其他内容或修改与删除内容。
  • 保护:当工作簿被保护且“保护工作簿”中的“结构”选项被选中时,这两个选项即生效。保护方案可以防止其他人更改此方案。如果选择隐藏方案,则被隐藏的方案不会在“方案管理器”中出现。

步骤5:单击“确定”按钮,打开“方案变量值”对话框,输入每个可变单元格的值,如图22-10所示。

Excel 假设分析方案应用技巧-Excel22

图22-10 输入方案变量值

Excel 假设分析方案应用技巧-Excel22

图22-11 创建的3个方案出现在“方案”下方的列表中

步骤6:单击“添加”按钮,返回“添加方案”对话框,继续添加其他方案(滞销期、常销期),并为每个方案输入可变单元格的值。

步骤7:输入第三个方案的变量值后,单击“方案变量值”对话框中的“确定”按钮,返回“方案管理器”对话框,可以看到刚才创建的3个方案出现在“方案”下方的列表中,如图22-11所示。

显示方案

当假设分析方案创建完毕,即可在“方案管理器”中查看与管理方案,本节介绍如何在工作表中显示各方案所对应的可变单元格的信息。

步骤1:单击“数据”|“预测”|“模拟分析”|“方案管理器”命令,打开“方案管理器”对话框。

步骤2:选中“方案”列表下方的一个方案,然后单击“显示”按钮,即可在工作表中显示该方案的结果,如图22-12所示。

Excel 假设分析方案应用技巧-Excel22

图22-12 从“方案”列表中选择要显示的方案