Excel 假设分析方案应用技巧

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

定义方案

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

图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　三个不同的方案

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

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

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

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

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

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

图22-9　输入可变单元格

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

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

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