Excel 高级数据分析 - 使用场景管理器进行假设分析

当您在敏感性分析中有两个以上的变量时，场景管理器非常有用。场景管理器为正在考虑的变量的每组输入值创建场景。场景可帮助您分析一组可能的结果，支持以下内容 −

改变多达 32 个输入集。
合并来自多个不同工作表或工作簿的场景。

如果要分析超过 32 个输入集，并且值仅代表一个或两个变量，则可以使用数据表。尽管它仅限于一个或两个变量，但数据表可以包含任意多个不同的输入值。请参阅本教程中的使用数据表进行假设分析。

场景

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

例如，您可以有多个不同的预算方案来比较各种可能的收入水平和支出。您还可以从不同的来源获得不同的贷款方案，比较各种可能的利率和贷款期限。

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

在您拥有所有需要的场景后，您可以创建场景总结报告 −

整合了来自所有场景的信息。
这让您可以并排比较这些场景。

场景管理器

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

要使用场景管理器创建分析报告，您必须按照以下步骤操作 −

步骤1 − 定义一组初始值并确定您想要改变的输入单元格，称为更改单元格。

步骤2 − 创建每个场景，为场景命名并为该场景的每个更改输入单元格输入值。

步骤3 − 选择输出单元格，称为要跟踪的结果单元格。这些单元格包含初始值集中的公式。公式使用不断变化的输入单元格。

场景管理器创建一个包含每个场景的输入和输出值的报告。

场景的初始值

在创建多个不同的场景之前，您需要定义一组场景所基于的初始值。

设置场景初始值的步骤是 −

定义包含输入值的单元格。
适当地命名输入单元格。
识别具有常量值的输入单元格。
指定常量输入的值。
识别具有变化值的输入单元格。
指定变化输入的初始值。
定义包含结果的单元格。结果单元格包含公式。
适当地命名结果单元格。
将公式放在结果单元格中。

考虑前面贷款的例子。现在，进行如下操作 −

为贷款金额定义一个单元格。
- 此输入值对于所有场景都是恒定的。
- 将单元格命名为 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 列中给出的名称相同。

创建场景

设置场景的初始值后，您可以使用场景管理器创建场景，如下所示 −

单击功能区上的"数据"选项卡。
点击数据工具组中的假设分析。
从下拉列表中选择方案管理器。

场景管理器对话框出现。您可以观察到它包含一条消息 −

"未定义任何场景。选择"添加到"。

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

创建第一个场景，初始值如下 −

单击场景管理器对话框中的添加按钮。

出现添加方案对话框。

在场景名称下，输入场景 1。
在"更改单元格"下，按下 Ctrl 键输入单元格的引用，即 C3、C4 和 C5。

对话框的名称更改为"编辑场景"。

编辑注释为 – 初始值框中的文本。
在"保护"下选择"防止更改"选项，然后单击"确定"。

出现场景值对话框。您定义的初始值出现在每个不断变化的单元格框中。

创建了具有初始值的

场景 1。

在不断变化的单元格中创建另外三个具有不同值的场景，如下所示 −

在场景值对话框中单击添加按钮。

出现添加场景对话框。请注意，C3、C4、C5 出现在"更改单元格"框中。

在场景名称框中，键入场景 2。
将注释中的文本编辑为 – 不同的利率。
在"保护"下选择"防止更改"，然后单击"确定"。

出现场景值对话框。初始值出现在不断变化的单元格中。将 Interest_Rate 的值更改为 0.13 并单击添加。

出现添加场景对话框。请注意，C3、C4、C5 出现在更改单元格下方的框中。

在场景名称框中，键入场景 3。
将注释框中的文本编辑为 – 不同的付款次数。
在"保护"下选择"防止更改"，然后单击"确定"。

场景值对话框出现。初始值出现在不断变化的单元格中。将 NPER 的值更改为 300，然后单击添加。

出现添加场景对话框。请注意，C3、C4、C5 出现在"更改单元格"框中。

在场景名称框中，键入场景 4。
将注释框中的文本编辑为 – 不同类型的付款。
在"保护"下选择"防止更改"，然后单击"确定"。

出现场景值对话框。初始值出现在不断变化的单元格中。将类型的值更改为 1。单击确定，因为您已经添加了所有要添加的场景。

出现场景管理器对话框。在方案下的框中，您将找到您创建的所有方案的名称。

单击场景 1。如您所知，场景 1 包含初始值。

现在，单击摘要。场景摘要对话框出现。

场景总结报告

Excel 提供两种类型的场景摘要报告 −

场景摘要。
场景数据透视表报告。

在"场景摘要"对话框中，您可以找到这两种报告类型。

在报告类型下选择场景摘要。

场景摘要

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

场景摘要报告出现在新的工作表中。该工作表被命名为场景摘要。

您可以在场景摘要报告中观察到以下内容 −

改变单元格 − 登记所有用作更改单元格的单元格。正如您已将单元格命名为 Interest_Rate、NPER 和 Type，这些似乎使报告有意义。否则，将仅列出单元格引用。
结果单元格 − 显示指定的结果单元格，即 EMI。
当前值 − 它是第一列，列出了在创建摘要报告之前在场景管理器对话框中选择的场景的值。
对于您创建的所有场景，变化的单元格将以灰色突出显示。
在 EMI 行中，将显示每个场景的结果值。

您可以通过显示您在创建场景时添加的评论来使报告更有意义。