使用场景管理器进行假设分析

---

在敏感性分析中有两个以上变量的情况下，场景管理器很有用。场景管理器为所考虑的变量的每组输入值创建场景。场景可帮助您探索一组可能的结果，支持以下内容 –

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

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

场景

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

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

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

拥有所需的所有场景后，您可以创建一个场景摘要报告 –

• 这包含了来自所有场景的信息。
• 这让您可以并排比较场景。

场景经理

Scenario Manager 是 Excel 中的假设分析工具之一。

要使用 Scenario Manager 创建分析报告，您必须按照以下步骤操作 –

步骤 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。

对话框的名称更改为 Edit Scenario。

• 编辑注释为 – 初始值框中的文本。

• 选择“保护”下的“阻止更改”选项，然后单击“确定”。

该方案值对话框出现对话框。您定义的初始值出现在每个变化的单元格框中。

创建具有初始值的场景 1。

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

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

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

• 在场景名称框中，输入场景 2。

• 将评论中的文本编辑为 – 不同的利率。

• 在保护下选择阻止更改，然后单击确定。

该方案值对话框出现对话框。初始值出现在不断变化的单元格中。将Interest_Rate的值更改为0.13并单击Add。

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

• 在场景名称框中，输入场景 3。

• 将注释框中的文本编辑为 – 不同的编号。付款。

• 在保护下选择阻止更改，然后单击确定。

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

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

• 在场景名称框中，输入场景 4。

• 将评论框中的文本编辑为 – 不同的付款类型。

• 在保护下选择阻止更改，然后单击确定。

该方案值对话框出现对话框。初始值出现在不断变化的单元格中。将 Type 的值更改为 1。因为您已经添加了所有要添加的场景，所以单击 OK。

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

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



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

情景总结报告

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

• 情景总结。
• 情景数据透视表报告。

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

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

场景总结

在结果单元格框中，选择单元格C6（这里，我们已经放置了PMT函数）。单击确定。

方案摘要报告出现在新工作表中。该工作表被命名为 Scenario Summary。

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

• 更改单元格– 列出用作更改单元格的所有单元格。由于您已将单元命名为 Interest_Rate、NPER 和 Type，这些似乎使报告有意义。否则，只会列出单元格引用。

• 结果单元格– 显示指定的结果单元格，即 EMI。

• 当前值– 它是第一列，并列出在创建摘要报告之前在场景管理器对话框中选择的场景的值。

• 对于您创建的所有场景，不断变化的单元格将以灰色突出显示。

• 在 EMI 行中，将显示每个场景的结果值。

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

• 单击包含场景名称的行左侧的 &plus 按钮。场景的注释显示在场景名称下方的行中。

不同来源的场景

假设您从三个不同的来源获得场景，并且您需要在主工作簿中准备场景摘要报告。您可以通过将不同工作簿中的场景合并到主工作簿中来实现。按照下面给出的步骤 –

• 假设场景在工作簿 Bank1_Scenarios、Bank2_Scenarios 和 Bank3_Scenarios 中。打开三个工作簿。

• 打开主工作簿，其中包含初始值。

• 单击主工作簿中的数据 > 假设分析 > 场景管理器。

该方案管理器对话框。

如您所见，没有任何场景，因为您尚未添加任何场景。单击合并。

出现合并方案对话框。

如您所见，在合并场景下，您有两个框 –

• 书
• 床单

您可以从包含要添加到结果中的场景的特定工作簿中选择特定工作表。单击Book的下拉箭头以查看工作簿。

注意– 应打开相应的工作簿以显示在此列表中。

选择书 – Bank1_Scenarios。

显示银行 1 表。在对话框的底部，显示在源表中找到的场景数量。单击确定。

出现场景管理器对话框。合并到主工作簿的两个方案将列在方案下。

单击合并按钮。该合并方案对话框出现对话框。现在，从 Book 框的下拉列表中选择Bank2_Scenarios。

Bank2 表被显示。在对话框的底部，显示在源表中找到的场景数量。单击确定。

该方案管理器对话框。合并到主工作簿的四个场景列在场景下。

单击合并按钮。该合并方案对话框出现对话框。现在，从 Book 框的下拉列表中选择Bank3_Scenarios。

显示 Bank3 表。在对话框的底部，将显示在源表中找到的场景数量。单击确定。

出现场景管理器对话框。合并到主工作簿的五个场景将列在场景下。

现在，您拥有了生成场景摘要报告所需的所有场景。

单击摘要按钮。该方案摘要对话框。

• 选择场景摘要。
• 在结果单元格框中，键入 C6 并单击确定。

方案摘要报告出现在主工作簿中的新工作表上。

展示场景

假设您正在展示您的场景，并且您希望从一个场景动态切换到另一个场景，并显示相应场景的一组输入值和结果值。

• 单击数据工具组中的数据 > 假设分析 > 场景管理器。出现场景管理器对话框。出现场景列表。

• 选择要显示的场景。单击显示。

工作表上的值将更新为所选方案的值。重新计算结果值。

场景数据透视表报告

您还可以以数据透视表的形式查看场景报告。

• 单击场景管理器对话框中的摘要按钮。出现场景摘要对话框。

• 在报告类型下选择方案数据透视表报告。

• 在结果单元格框中键入 C6 。

场景数据透视表报告出现在新工作表上。