使用数据表进行假设分析

借助 Excel 中的数据表，您可以轻松改变一两个输入并执行假设分析。数据表是一系列单元格，您可以在其中更改某些单元格中的值并对问题得出不同的答案。

有两种类型的数据表 -

  • 一变量数据表
  • 二变量数据表

如果你的分析问题有两个以上的变量，你需要使用Excel的场景管理工具。有关详细信息，请参阅本教程中的“使用场景管理器进行假设分析”一章。

一变量数据表

如果您想了解一个或多个公式中的一个变量的不同值将如何改变这些公式的结果，则可以使用单变量数据表。换句话说，通过单变量数据表，您可以确定更改一个输入如何改变任意数量的输出。借助示例您将理解这一点。

例子

有一笔贷款5,000,000，期限30年。您想了解不同利率下的每月付款 (EMI)。您可能还想知道第二年支付的利息和本金金额。

单变量数据表分析

使用一变量数据表进行分析需要分三步完成 -

步骤 1 - 设置所需的背景。

步骤 2 - 创建数据表。

步骤 3 - 执行分析。

让我们详细了解这些步骤 -

第1步：设置所需背景

  • 假设利率为12%。

  • 列出所有必需的值。

  • 为包含值的单元格命名，以便公式具有名称而不是单元格引用。

  • 使用 Excel 函数分别设置 EMI、累积利息和累积本金的计算 - PMT、CUMIPMT 和 CUMPRINC。

您的工作表应如下所示 -

设置所需的背景

您可以看到 C 列中的单元格的命名与 D 列中相应单元格中给定的名称相同。

第2步：创建数据表

  • 在 E 列下方的输入单元格中键入要替换的值列表，即利率，如下所示 -

创建数据表

    正如您所观察到的，利率值上方有一个空行。此行用于您要使用的公式。

  • 在值列上方的单元格和右侧的单元格中键入第一个函数 ( PMT )。在第一个函数右侧的单元格中键入其他函数（CUMIPMT 和 CUMPRINC ）。

    现在，利率值上方的两行如下所示 -

类型 功能

    数据表如下所示 -

下面的数据表

步骤 3：使用假设分析数据表工具进行分析

  • 选择包含要替换的公式和值的单元格区域，即选择区域 – E2:H13。

  • 单击功能区上的数据选项卡。

  • 单击数据工具组中的假设分析。

  • 在下拉列表中选择数据表。

进行分析

出现数据表对话框。

  • 单击列输入单元格框中的图标。
  • 单击单元格Interest_Rate，即 C2。
数据表

您可以看到列输入单元格被视为 $C$2。单击“确定”。

数据表填充了每个输入值的计算结果，如下所示 -

填写数据表

如果您可以支付54,000的EMI，您可以观察到12.6%的利率适合您。

二变量数据表

如果您想了解公式中两个变量的不同值将如何改变该公式的结果，则可以使用双变量数据表。换句话说，使用双变量数据表，您可以确定更改两个输入如何更改单个输出。借助示例您将理解这一点。

例子

有贷款五千万。您想知道利率和贷款期限的不同组合将如何影响每月还款额 (EMI)。

二变量数据表分析

使用二变量数据表进行分析需要分三个步骤完成 -

步骤 1 - 设置所需的背景。

步骤 2 - 创建数据表。

步骤 3 - 执行分析。

第1步：设置所需背景

  • 假设利率为12%。

  • 列出所有必需的值。

  • 命名包含值的单元格，以便公式具有名称而不是单元格引用。

  • 使用 Excel 函数 – PMT设置 EMI 的计算。

您的工作表应如下所示 -

设置背景

您可以看到 C 列中的单元格的命名与 D 列中相应单元格中的指定相同。

第2步：创建数据表

  • 类型=单元格 F2 中的 EMI。

设置电磁干扰

  • 从公式下方的单元格（即 F3）开始，在 F 列中输入第一个输入值列表，即利率。

  • 键入第二个输入值列表，即第 2 行的付款次数，从公式右侧的单元格（即 G2）开始。

    数据表如下所示 -

类型 输入值

使用假设分析工具数据表进行分析

  • 选择包含公式和要替换的两组值的单元格区域，即选择区域 – F2:L13。

  • 单击功能区上的数据选项卡。

  • 单击数据工具组中的假设分析。

  • 从下拉列表中选择数据表。

执行分析

出现数据表对话框。

  • 单击行输入单元格框中的图标。
  • 单击单元格NPER，即 C3。
  • 再次单击“行输入单元格”框中的图标。
  • 接下来，单击列输入单元格框中的图标。
  • 单击单元格 Interest_Rate，即 C2。
  • 再次单击列输入单元格框中的图标。
列输入单元框

您将看到行输入单元格被视为 $C$3，列输入单元格被视为 $C$2。单击“确定”。

数据表填充了两个输入值的每种组合的计算结果 -

重命名输入单元格框

如果您能支付54,000 EMI，那么12.2%的利率和288 EMI就适合您。这意味着贷款期限为24年。

数据表计算

每次重新计算包含数据表的工作表时，即使它们没有更改，也会重新计算数据表。要加快包含数据表的工作表中的计算速度，您需要将计算选项更改为自动重新计算工作表而不是数据表，如下一节中所述。

加快工作表中的计算速度

您可以通过两种方式加快包含数据表的工作表中的计算速度 -

  • 从 Excel 选项。
  • 来自功能区。

从 Excel 选项

  • 单击功能区上的“文件”选项卡。
  • 从左窗格的列表中选择选项。

出现 Excel 选项对话框。

  • 从左侧窗格中，选择“公式”。

  • 在“计算选项”部分的“工作簿计算”下选择“自动”选项，数据表除外。单击“确定”。

Excel 选项

从功能区

  • 单击功能区上的“公式”选项卡。

  • 单击“计算”组中的“计算选项” 。

  • 在下拉列表中选择“自动，数据表除外” 。

从功能区