数据透视表中的假设分析

---

---

使用Excel中的数据透视表，您可以轻松更改一个或两个输入值并执行假设分析。数据透视表是一个单元格区域，您可以更改其中某些单元格的值，从而得出问题的不同答案。

数据透视表有两种类型：

• 单变量数据透视表
• 双变量数据透视表

如果您的分析问题中有多于两个变量，则需要使用Excel的场景管理器工具。详情请参阅本教程中的章节 – 使用场景管理器进行假设分析。

单变量数据透视表

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

示例

有一笔为期30年的5,000,000贷款。您想知道不同利率下的月供（EMI）。您可能还想知道第二年支付的利息和本金金额。

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

使用单变量数据透视表进行分析需要三个步骤：

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

步骤2 - 创建数据透视表。

步骤3 - 执行分析。

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

步骤1：设置所需的背景

• 假设利率为12%。

• 列出所有所需的值。

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

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

您的工作表应如下所示：

您可以看到C列中的单元格名称如D列中相应的单元格所示。

步骤2：创建数据透视表

• 键入您想要替换输入单元格的值列表（即利率），如下所示，在E列下方：

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

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

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

数据透视表如下所示：

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

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

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

• 在“数据工具”组中单击“假设分析”。

• 在下拉列表中选择“数据透视表”。

数据透视表对话框出现。

• 单击“列输入单元格”框中的图标。
• 单击单元格Interest_Rate（C2）。

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

数据透视表将填充每个输入值的计算结果，如下所示：

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

Explore our latest online courses and learn new skills at your own pace. Enroll and become a certified expert to boost your career.

双变量数据透视表

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

示例

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

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

使用双变量数据透视表进行分析需要三个步骤：

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

步骤2 - 创建数据透视表。

步骤3 - 执行分析。

步骤1：设置所需的背景

• 假设利率为12%。

• 列出所有所需的值。

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

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

您的工作表应如下所示：

您可以看到C列中的单元格名称如D列中相应的单元格所示。

步骤2：创建数据透视表

• 在单元格F2中键入=EMI。

• 键入第一个输入值列表（即利率），在公式下方（即F3）的F列中向下键入。

• 键入第二个输入值列表（即付款次数），在公式右侧（即G2）的第2行中向右键入。

  数据透视表如下所示：

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

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

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

• 在“数据工具”组中单击“假设分析”。

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

数据透视表对话框出现。

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

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

数据透视表将填充每种组合的两个输入值的计算结果：

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

数据透视表计算

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

加快工作表中的计算速度

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

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

从Excel选项

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

Excel选项对话框出现。

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

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

从功能区

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

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

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