Excel Data Analysis 简明教程

What-If Analysis with Scenario Manager

情景管理器对于在敏感性分析中具有两个以上变量的情况下非常有用。情景管理器为所选变量的每组输入值创建情景。情景帮助你探索一系列可能的结果，支持以下内容-

最多可更改 32 组输入。
合并来自多个不同工作表或工作簿的情景。

如果你想分析超过 32 组输入，并且这些值仅代表一到两个变量，你可以使用数据表。尽管它仅限于一到两个变量，但数据表可以包含任意数量不同的输入值。请参阅本教程中的 What-If Analysis with Data Tables 。

Scenarios

情景是一组由 Excel 保存的可以在工作表中自动替换的值。你可以在工作表上将不同组的值创建并保存为情景，然后在这些情景之间切换以查看不同的结果。

例如，你可以有几个不同的预算情景，它们比较各种可能的收入水平和支出。你还可以有来自不同来源的不同贷款情景，这些情景比较各种可能的利率和贷款期限。

如果想要在情景中使用的信息来自不同来源，你可以将此信息收集到单独的工作簿中，然后将不同工作簿中的情景合并到一个工作簿中。

拥有所需的所有情景后，你可以创建一个情景汇总报告-

它结合了所有情景的信息。
这让你可以逐个比较不同情景。

Scenario Manager

情景管理器是 Excel 中的假设分析工具之一。

要使用情景管理器创建一个分析报告，您必须遵循以下步骤−

Step 1 − 定义初始值的集合，并找出您想要改变的输入单元格，称为可变单元格。

Step 2 − 创建每个情景，命名情景，并为该情景的每个可变输入单元格输入值。

Step 3 − 选择您想要跟踪的输出单元格，称为结果单元格。这些单元格包含初始值集合中的公式。这些公式使用可变输入单元格。

情景管理器创建一份报告，其中包含每个情景的输入和输出值。

Initial Values for Scenarios

在创建若干不同情景之前，您需要定义一组情景将基于其的初始值。

设置情景的初始值的步骤为 −

定义包含输入值的单元格。
适当地命名输入单元格。
找出具有恒定值的输入单元格。
指定恒定输入的值。
找出具有可变值的输入单元格。
指定可变输入的初始值。
定义包含结果的单元格。结果单元格包含公式。
适当地命名结果单元格。
将公式放在结果单元格中。

考虑前面的贷款示例。现在，按照以下步骤进行 -

为贷款金额定义一个单元格。此输入值对于所有情景都是恒定的。将单元格命名为 Loan_Amount。将值指定为 5,000,000。
为利率、付款次数和类型（月初或月末付款）定义单元格。这些输入值将在情景中发生变化。将单元格命名为 Interest_Rate、NPER 和 Type。在这些单元格中将分析的初始值分别指定为 12%、360 和 0。
为 EMI 定义单元格。这是结果值。将单元格命名为 EMI。在此单元格中放入公式为 - =PMT (Interest_Rate/12, NPER, Loan_Amount, 0, Type)

您的工作表如下所示 -

您会看到输入单元格和结果单元格在 C 列中，名称如 D 列中给出的那样。

Creating Scenarios

在为情景设置初始值后，您可以使用情景管理器创建情景，如下所示 -

单击功能区上的“数据”选项卡。
单击“数据工具”组中的“假设分析”。
从下拉列表中选择“情景管理器”。

将出现“情景管理器”对话框。您会看到其中包含一条消息 -

“No Scenarios defined. Choose Add to.”

您需要为情景管理器中每组变化值创建情景。最好使用初始值定义第一个情景，因为它使您能够在显示不同情景时随时切换回初始值。

按照以下步骤使用初始值创建第一个情景 -

单击“情景管理器”对话框中的 Add 按钮。

将出现 Add Scenario 对话框。

在“情景名称”下，键入“情景 1”。
在“变化单元格”下，在按住 Ctrl 键的情况下输入单元格引用，即 C3、C4 和 C5。

对话框名称更改为“编辑情景”。

在 Comment as – Initial Values 框中编辑文本。
选择“保护”下的“禁止更改”选项，然后单击确定。

将出现 Scenario Values 对话框。您定义的初始值将出现在每个改变单元格框中。

用初始值创建 Scenario 1 。

创建三个不同场景，如下所示，场景中的值各不相同：

单击“方案值”对话框中的 Add 按钮。

将显示“添加方案”对话框。请注意，“C3、C4、C5”将显示在“变化单元格”框中。

在“方案名称”框中，输入“方案 2”。
在 Comment 中编辑文本为——不同的利率。
选择“保护”下的“禁止更改”，然后单击“确定”。

将显示 Scenario Values 对话框。初始值将显示在改变单元格中。将 Interest_Rate 的值更改为 0.13 ，然后单击 Add 。

将显示 Add Scenario 对话框。请注意，“C3、C4、C5”将显示在“变化单元格”下的框中。

在“方案名称”框中，输入“方案 3”。
在 Comment 框中编辑文本为——付款次数不同。
选择“保护”下的“禁止更改”，然后单击“确定”。

将显示“方案值”对话框。初始值将显示在改变单元格中。将 NPER 的值更改为 300，然后单击 Add 。

将显示 Add Scenario 对话框。请注意，“C3、C4、C5”将显示在“变化单元格”框中。

在“方案名称”框中，输入“方案 4”。
在 Comment 框中编辑文本为——付款类型不同。
选择“保护”下的“禁止更改”，然后单击“确定”。

将显示 Scenario Values 对话框。初始值将显示在改变单元格中。将“类型”的值更改为 1。单击“确定”，因为您已添加所有要添加的方案。

Scenario Manager 对话框出现。在情景下的框中，你将找到你已创建的所有情景的名称。

单击情景 1。正如你所了解的，情景 1 包含初始值。

Scenario Summary Reports

Excel 提供两种类型的“情景摘要”报表 -

Scenario summary.
Scenario PivotTable report.

在“情景摘要”对话框中，你可以找到这两个“报表”类型。

选择“报表类型”下的“情景摘要”。

Scenario Summary

在 Result cells 框中，选择单元格 C6 （在此，我们已放置 PMT 函数）。单击“确定”。

“情景摘要”报表会出现在一个新的工作表中。工作表命名为“情景摘要”。

在“情景摘要”报表中，你可以观察到以下内容 -

Changing Cells - 登记所有用作变更单元格的单元格。由于你已将单元格命名为 Interest_Rate、NPER 和 Type，因此这些内容似乎有意义。否则，只将列出单元格引用。
Result Cells - 显示指定的结果单元格，即 EMI。
Current Values - 第一列并在“摘要”报表创建前在“情景管理器”对话框中选定的情景的值中登记。
对于你已创建的所有情景，变更单元格将以灰色高亮显示。
在 EMI 行中，将显示每个情景的结果值。