使用数据表进行假设分析
使用数据表进行假设分析
使用 Excel 中的数据表，您可以轻松改变一两个输入并执行假设分析。数据表是一系列单元格，您可以在其中更改某些单元格中的值并针对问题提出不同的答案。
有两种类型的数据表 –
- 一变量数据表
- 二变量数据表
如果您的分析问题中有两个以上的变量，则需要使用 Excel 的 Scenario Manager Tool。有关详细信息，请参阅本教程中的“使用场景管理器进行假设分析”一章。
一变量数据表
如果您想查看一个或多个公式中一个变量的不同值将如何改变这些公式的结果，可以使用单变量数据表。换句话说，使用单变量数据表，您可以确定改变一个输入如何改变任意数量的输出。您将在示例的帮助下理解这一点。
例子
有一笔贷款 5,000,000 美元，期限为 30 年。您想知道不同利率的月还款额 (EMI)。您可能还想知道第二年支付的利息和本金金额。
单变量数据表分析
单变量数据表分析需要分三步完成 –
步骤 1 – 设置所需的背景。
步骤 2 – 创建数据表。
步骤 3 – 执行分析。
让我们详细了解这些步骤 –
第一步：设置需要的背景
-
假设利率为 12%。
-
列出所有必需的值。
-
为包含值的单元格命名，以便公式具有名称而不是单元格引用。
-
分别使用 Excel 函数 PMT、CUMIPMT 和 CUMPRINC 设置 EMI、累积利息和累积本金的计算。
您的工作表应如下所示 –
您可以看到 C 列中的单元格命名为 D 列中相应单元格中给出的名称。
步骤 2：创建数据表
-
在 E 列下方的输入单元格中键入要替换的值列表，即利率，如下所示 –
-
在值列上方的单元格和右侧的一个单元格中键入第一个函数 ( PMT )。在第一个函数右侧的单元格中键入其他函数（CUMIPMT 和 CUMPRINC）。
现在，利率值上方的两行如下所示 –
如您所见，利率值上方有一个空行。此行用于您要使用的公式。
数据表如下所示 –
步骤 3：使用假设分析数据表工具进行分析
-
选择包含要替换的公式和值的单元格范围，即选择范围 – E2:H13。
-
单击功能区上的数据选项卡。
-
单击数据工具组中的假设分析。
-
在下拉列表中选择数据表。
出现数据表对话框。
- 单击列输入单元格框中的图标。
- 单击单元格Interest_Rate，即 C2。
您可以看到 Column 输入单元格被视为 $C$2。单击确定。
数据表中填充了每个输入值的计算结果，如下所示 –
如果您可以支付 54,000 的 EMI，您可以观察到 12.6% 的利率适合您。
二变量数据表
如果您想查看公式中两个变量的不同值将如何改变该公式的结果，可以使用双变量数据表。换句话说，使用双变量数据表，您可以确定更改两个输入如何更改单个输出。您将在示例的帮助下理解这一点。
例子
有50,000,000 的贷款。您想知道利率和贷款期限的不同组合将如何影响每月付款 (EMI)。
二元数据表分析
使用两变量数据表进行分析需要分三步完成 –
步骤 1 – 设置所需的背景。
步骤 2 – 创建数据表。
步骤 3 – 执行分析。
第一步：设置需要的背景
-
假设利率为 12%。
-
列出所有必需的值。
-
命名包含值的单元格，以便公式将具有名称而不是单元格引用。
-
使用 Excel 函数PMT设置 EMI 计算。
您的工作表应如下所示 –
您可以看到 C 列中的单元格命名为 D 列中相应单元格中给出的名称。
步骤 2：创建数据表
-
键入= EMI在小区F2。
-
从公式下方的单元格开始，输入第一个输入值列表，即 F3 列中的利率。
-
输入第二个输入值列表，即第 2 行的付款次数，从公式右侧的单元格开始，即 G2。
数据表如下所示 –
使用假设分析工具数据表进行分析
-
选择包含公式的单元格范围和要替换的两组值，即选择范围 – F2:L13。
-
单击功能区上的数据选项卡。
-
单击数据工具组中的假设分析。
-
从下拉列表中选择数据表。
出现数据表对话框。
- 单击行输入单元格框中的图标。
- 单击单元格NPER，即 C3。
- 再次单击行输入单元格框中的图标。
- 接下来，单击列输入单元格框中的图标。
- 单击单元格 Interest_Rate，即 C2。
- 再次单击列输入单元格框中的图标。
您将看到 Row 输入单元格被视为 &dollarC&dollar3，而 Column 输入单元格被视为 &dollarC&dollar2。单击确定。
数据表中填充了两个输入值的每个组合的计算结果 –
如果可以支付54,000的EMI，12.2%的利率和288 EMI很适合你。这意味着贷款的期限为 24 年。
数据表计算
每次重新计算包含数据表的工作表时，都会重新计算数据表，即使它们没有更改。要加快包含数据表的工作表中的计算，您需要将计算选项更改为自动重新计算工作表而不是数据表，如下一节所述。
加速工作表中的计算
您可以通过两种方式加快包含数据表的工作表中的计算 –
- 从 Excel 选项。
- 从功能区。
从 Excel 选项
- 单击功能区上的“文件”选项卡。
- 从左窗格的列表中选择选项。
出现 Excel 选项对话框。
-
从左窗格中，选择公式。
-
在“计算选项”部分的“工作簿计算”下选择“自动”选项，数据表除外。单击确定。
从功能区
-
单击功能区上的公式选项卡。
-
单击计算组中的计算选项。
-
在下拉列表中选择自动，数据表除外。
