假设分析数据表

使用Excel中的数据表,您可以轻松改变一个或两个输入并执行假设分析.数据表是一系列单元格,您可以在其中更改某些单元格中的值,并为问题提供不同的答案.

有两种类型的数据表和减号;

  • 单变量数据表

  • 双变量数据表

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

单变量数据表

如果要查看一个或多个公式中一个变量的不同值如何更改这些公式的结果,可以使用单变量数据表.换句话说,使用单变量数据表,您可以确定更改一个输入如何更改任意数量的输出.您将在示例的帮助下理解这一点.

示例

任期内可获得5,000,000贷款30年.您想知道各种利率的每月付款(EMI).您可能也有兴趣了解第二年支付的利息金额和本金.

使用单变量数据表进行分析

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

步骤1 : 设置所需的背景.

第2步 : 创建数据表.

步骤3 : 执行分析.

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

步骤1:设置所需背景

  • 假设利率为12%.

  • 列出所有必需的值.

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

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

您的工作表应如下所示 :

设置所需背景

您可以看到C列中的单元格在D列的相应单元格中命名为.

步骤2:创建数据表

  • 键入值列表,即您想要的利率在E列下方的输入单元格中进行滴定,如下所示;

创建数据表

    如您所见,利率值上方有一个空行.此行用于您要使用的公式.

  • 在上面一行的单元格中键入第一个函数( PMT ),并将一个单元格键入价值栏的权利.在第一个函数右侧的单元格中键入其他函数( CUMIPMT和CUMPRINC ).

    现在,利率值上方的两行显示为跟随 : 去;

类型函数

    数据表如下所示 :

数据表下面

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

  • 选择包含要替换的公式和值的单元格范围,即选择范围 -  E2:H13.

  • 单击功能区上的DATA选项卡.

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

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

Do Analysis

数据表对话框出现.

  • 单击列输入单元格框中的图标.

  • 单击单元格 Interest_Rate ,即C2.

数据表

你可以看到列输入单元格被视为$ C $ 2.单击OK.

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

填写数据表

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

双变量数据表

如果要查看公式中两个变量的不同值将如何使用,可以使用双变量数据表更改该公式的结果.换句话说,使用双变量数据表,您可以确定更改两个输入如何更改单个输出.您将在示例的帮助下理解这一点.

示例

贷款额为50,000,000.您想知道利率和贷款期限的不同组合将如何影响每月付款(EMI).

使用双变量数据表进行分析

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

步骤1 : 设置所需的背景.

第2步 : 创建数据表.

步骤3 : 执行分析.

步骤1:设置所需背景

  • 假设利率为12%.

  • 列出所有必需的值.

  • 名称包含值的单元格,以便公式将具有名称而不是单元格引用.

  • 使用Excel函数设置EMI的计算 -   PMT .

您的工作表应如下所示 :

设置背景

您可以看到C列中的单元格在D列中的相应单元格中命名为

第2步:创建数据表

  • 类型 = EMI 在单元格F2中.

设置EMI

  • 输入第一个输入值列表,即.F列F下方的利率,从公式下方的单元格开始,即F3.

  • 输入第二个输入值列表,即支付数量第2行,从公式右边的单元格开始,即G2.

    数据表如下所示 :

输入值类型

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

  • 选择包含公式的单元格范围和要替换的两组值,即选择范围 -  F2:L13.

  • 单击功能区上的DATA选项卡.

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

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

执行分析

数据Ta出现对话框.

  • 单击行输入单元格框中的图标.

  • 单击单元格 NPER ,即C3.

  • 再次单击行输入单元格框中的图标.

  • 下一步,单击列输入单元格框中的图标.

  • 单击单元格Interest_Rate,即C2.

  • 再次单击列中的图标输入单元格框.

列输入单元格框

您将看到Row输入单元格被视为$ C$ 3和Column输入单元格被视为$ C$ 2.单击确定.

数据表将填充两个输入值的每个组合的计算结果 :

重命名输入单元格框

如果你能支付54,000的EMI,12.2%和288 EMI的利率适合你.这意味着贷款期限为24年.

数据表计算

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

加快工作表中的计算

您可以通过两种方式减去包含数据表的工作表中的计算;

  • 来自Excel选项.

  • 来自功能区.

从Excel选项

  • 单击功能区上的"文件"选项卡.

  • 选择选项从左侧窗格的列表中.

出现Excel选项对话框.

  • 从左侧窗格中选择公式.

  • 选择自动选项除外对于数据表,在计算选项部分的工作簿计算下.单击确定.

Excel Options

从功能区

  • 单击功能区上的FORMULAS选项卡.

  • 单击"计算"组中的计算选项.

  • 选择自动除外下拉列表中的数据表.

从功能区