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 行中,将显示每个场景的结果值。

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

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

点击加号按钮

来自不同来源的场景

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

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

  • 打开主工作簿,其中有初始值。

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

出现场景管理器对话框。

来自不同来源的场景

如您所见,没有场景,因为您还没有添加任何场景。 点击合并

"合并场景"对话框出现。

合并场景

如您所见,在合并场景下,您有两个框 −

  • Book
  • Sheet

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

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

合并场景对话框

选择书籍 – Bank1_Scenarios

显示 Bank1 工作表。 在对话框的底部,显示了在源工作表中找到的场景数。 单击"确定"。

选择书籍

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

主工作簿

点击合并按钮。 出现合并场景对话框。 现在,从 Book 框中的下拉列表中选择 Bank2_Scenarios

显示 Bank2 工作表。 在对话框的底部,显示了在源工作表中找到的场景数。 单击"确定"。

源工作表显示

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

场景

点击合并按钮。 出现合并场景对话框。 现在,从 Book 框中的下拉列表中选择 Bank3_Scenarios

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

场景发现

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

场景合并

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

单击摘要按钮。 场景摘要 对话框出现。

  • Select Scenario summary.
  • 在"结果单元格"框中,键入 C6 并单击"确定"。
结果单元格框

场景摘要报告出现在主工作簿的新工作表上。

新工作表摘要

显示场景

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

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

  • 选择您要显示的场景。 点击显示

显示场景

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

选定场景

场景数据透视表

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

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

  • 在报表类型下选择场景数据透视表报表

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

场景数据透视表

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

工作表上的场景数据透视表报告