Excel 高级数据分析 - 使用数据表进行假设分析

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

数据表有两种类型 −

单变量数据表
双变量数据表

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

单变量数据表

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

示例

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

单变量数据表分析

单变量数据表分析需要分三步完成 −

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

步骤 2 − 创建数据表。

步骤 3 − 执行分析。

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

第 1 步:设置所需背景

假设利率为12%。
列出所有必需的值。
命名包含值的单元格，这样公式将有名称而不是单元格引用。
分别使用 Excel 函数 – PMT、CUMPMT 和 CUMPRINC 设置 EMI、累积利息和累积本金的计算。

你的工作表应该如下所示 −

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

第 2 步:创建数据表

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

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

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

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

数据表如下所示 −

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

选择包含要替换的公式和值的单元格范围，即选择范围 - E2:H13。
单击功能区上的"数据"选项卡。
单击数据工具组中的假设分析。
在下拉列表中选择数据表。

数据表对话框出现。

单击"列输入单元格"框中的图标。
单击单元格 Interest_Rate，即 C2。

您可以看到 Column 输入单元格被取为 $C$2。单击"确定"。

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

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

双变量数据表

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

示例

有一笔贷款 50,000,000。您想知道利率和贷款期限的不同组合将如何影响月供 (EMI)。

双变量数据表分析

双变量数据表分析需要分三步完成 −

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

步骤 2 − 创建数据表。

步骤 3 − 执行分析。

第 1 步:设置所需背景

假设利率为12%。
列出所有必需的值。
命名包含值的单元格，这样公式将有名称而不是单元格引用。
使用 Excel 函数 - PMT 设置 EMI 的计算。

您的工作表应如下所示 −

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

第 2 步:创建数据表

在单元格 F2 中键入 =EMI。

键入第一个输入值列表，即 F 列下方的利率，从公式下方的单元格开始，即 F3。
键入第二个输入值列表，即第 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 数据分析教程