当您有一个依赖于多个变量的复杂公式并且想要了解更改这些输入如何有效地影响结果时，Excel 中的假设分析数据表是一个强大的工具。它使您可以快速浏览所有可能的结果。以下是有关如何在 Excel 中创建数据表的分步指南。此外，我们将讨论有效使用数据表的一些关键点，并演示其他操作，例如删除、编辑和重新计算数据表。

Excel中的数据表是什么？

创建单变量数据表

• 面向列的数据表
• 面向行的数据表
• 一变量数据表的多个公式

创建二变量数据表

使用数据表的要点

使用数据表的其他操作

• 删除数据表
• 编辑数据表结果
• 手动重新计算数据表

Excel中的数据表是什么？

在 Excel 中，数据表是假设分析工具之一，可让您试验公式的不同输入值并观察公式输出的变化。该工具对于探索各种场景和进行敏感性分析非常有用，特别是当公式依赖于多个变量时。

Excel中有两种类型的数据表：

一变量数据表：这使您可以分析一个变量的不同值将如何影响公式。您可以采用面向行或面向列的格式设置单变量数据表，具体取决于您是要跨行还是跨列改变输入。

二变量数据表：此类型允许您查看更改两个不同变量对公式结果的影响。在双变量数据表中，您可以改变行和列上的值。

创建单变量数据表

在 Excel 中创建单变量数据表是一项宝贵的技能，可用于分析单个输入的变化如何影响各种结果。本节将指导您完成创建面向列、面向行和多公式一变量数据表的过程。

惊人的！ 在 Excel 中使用高效的选项卡，如 Chrome、Edge、Firefox 和 Safari！
每天节省50％的时间，并减少数千次鼠标单击！

---

面向列的数据表

当您想要测试单个变量的不同值如何影响公式的输出（变量值在一列中列出）时，面向列的数据表非常有用。让我们考虑一个简单的财务示例：

假设您正在考虑一笔 50,000 美元的贷款，您计划在 3 年（相当于 36 个月）内偿还。为了根据您的工资评估每月还款额是可以承受的，您有兴趣探索不同的利率将如何影响您每月需要支付的金额。

第 1 步：设置基本公式

为了计算还款额，这里我将利息设置为5%。在单元格 B4 中，输入 PMT 公式，它根据利率、期数和贷款金额计算每月还款额。看截图：

= -PMT($B$1/12, $B$2*12, $B$3)

步骤 2：在一栏中列出利率

在一栏中列出您要测试的不同利率。例如，在一列中以 4% 为增量列出从 11% 到 1% 的值，并在右侧至少留出一个空白列作为结果。看截图：

第三步：创建数据表

1. 在单元格 E2 中，输入以下公式： = B4.
   
   备注: B4 是主公式所在的单元格，您希望看到该公式的结果随着利率的变化而变化。
   
2. 选择包含公式、利率列表和结果相邻单元格的范围（例如，选择 D2 到 E10）。看截图：
   
3. 转到功能区，单击 时间 选项卡，然后单击 假设分析 > 数据表，请参见屏幕截图：
   
4. 在 数据表 对话框，点击 列 输入单元格 框（因为我们使用一列作为输入值），然后选择公式中引用的变量单元格。在此示例中，我们选择包含利率的 B1。最后，点击 OK 按钮，请参见屏幕截图：
   
5. Excel 将自动用相应的结果填充每个变量值（不同利率）旁边的空白单元格。看截图：
   
6. 根据您的需要将所需的数字格式应用于结果（货币）。现在，列式数据表已成功创建，您可以快速查看结果来评估当利率变化时您可以承受哪些每月还款额。看截图：
   

面向行的数据表

在 Excel 中创建面向行的​​数据表需要对数据进行排列，以便变量值跨行列出，而不是沿列列出。以上面的例子为参考，我们继续按照步骤完成Excel中行式数据表的创建。

步骤 1：连续列出利率

将变量值（利率）放在一行中，确保左侧至少有一个空列用于公式，下方至少有一个空行用于结果，请参见屏幕截图：

第三步：创建数据表

1. 在单元格 A9 中，输入以下公式： = B4.
   
2. 选择包含公式、利率列表和结果相邻单元格的范围（例如，选择 A8 到 I9）。然后，单击 时间 > 假设分析 > 数据表.
3. 在 数据表 对话框，点击 行输入单元格 框（因为我们使用一行作为输入值），然后选择公式中引用的变量单元格。在此示例中，我们选择包含利率的 B1。最后，点击 OK 按钮，请参见屏幕截图：
   
4. 根据您的需要将所需的数字格式应用于结果（货币）。现在，面向行的数据表已创建，请参见屏幕截图：
   

一变量数据表的多个公式

在 Excel 中创建包含多个公式的单变量数据表可让您了解更改单个输入如何同时影响多个不同的公式。在上面的示例中，如果您想查看还款利率和总利息的利率变化，该怎么办？设置方法如下：

第 1 步：添加新公式来计算总利息

在单元格 B5 中，输入以下公式来计算总利息：

=B4*B2*12-B3

步骤2：整理数据表的源数据

在一列中，列出您要测试的不同利率，并在右侧至少留出两个空白列作为结果。看截图：

第三步：创建数据表：

1. 在单元格 E2 中，输入以下公式： = B4 创建对原始数据中还款计算的引用。
2. 在单元格 F2 中，输入以下公式： = B5 创建对原始数据的总兴趣的引用。
   
3. 选择包含公式、利率列表和结果相邻单元格的范围（例如，选择 D2 到 F10）。然后，单击 时间 > 假设分析 > 数据表.
4. 在 数据表 对话框，点击 列输入单元格 框（因为我们使用一列作为输入值），然后选择公式中引用的变量单元格。在此示例中，我们选择包含利率的 B1。最后，点击 OK 按钮，请参见屏幕截图：
   
5. 根据您的需要将所需的数字格式应用于结果（货币）。您可以根据不同的变量值查看每个公式的结果。
   

创建二变量数据表

Excel 中的双变量数据表显示两组变量值的不同组合对公式结果的影响，演示公式的两个输入的更改如何同时影响其结果。

在这里，我画了一个简单的草图，以帮助您更好地理解二变量数据表的外观和结构。

在创建一变量数据表的示例的基础上，现在让我们继续学习如何在 Excel 中创建二变量数据表。

在下面的数据集中，我们有利率、贷款期限和贷款金额，并使用以下公式计算了每月还款额： =-PMT($B$1/12, $B$2*12, $B$3) 以及。在这里，我们将重点关注数据中的两个关键变量：利率和贷款金额，以观察这两个因素的变化如何同时影响还款金额。

第 1 步：设置两个变化变量

1. 在一栏中列出您要测试的不同利率。看截图：
   
2. 在一行中，在列值上方输入不同的贷款金额值（从公式单元格右侧的一个单元格开始），请参阅屏幕截图：
   

第三步：创建数据表

1. 将公式放在列出变量值的行和列的交叉点处。在这种情况下，我将输入这个公式： = B4 进入单元格 E2。看截图：
   
2. 选择包括贷款金额、利率、公式单元格以及将显示结果的单元格的范围。
   
3. 然后，单击 时间 > 假设分析 > 数据表。在数据表对话框中：
   • 在 行输入单元格 框中，选择对行中变量值的输入单元格的单元格引用（在此示例中，B3 包含贷款金额）。
   • 在 列输入单元格l 框中，选择对列中变量值的输入单元格的单元格引用（B1 包含利率）。
   • 然后，单击 OK 按钮。
     
4. 现在，Excel 将使用每种贷款金额和利率组合的结果填充数据表。它可以直接了解贷款金额和利率的不同组合如何影响每月还款额，使其成为财务规划和分析的宝贵工具。
   
5. 最后，您应该根据需要将所需的数字格式应用于结果（货币）。
   

使用数据表的要点

• 新创建的数据表必须与原始数据位于同一工作表中。
• 数据表的输出取决于源数据集中的公式单元格，对此公式单元格的任何更改都会自动更新输出。
• 一旦使用数据表计算出值，就无法撤消 按Ctrl + Z。但是，您可以手动选择所有值并将其删除。
• 数据表生成数组公式，因此表中的各个单元格无法更改或删除。

使用数据表的其他操作

创建数据表后，您可能需要执行其他操作来有效管理它，包括删除数据表、修改其结果以及执行手动重新计算。

删除数据表

由于数据表结果是使用数组公式计算的，因此您无法从数据表中删除单个值。但是，您只能删除整个数据表。

选择所有数据表单元格或仅选择包含结果的单元格，然后按 删除 键盘上的键。

编辑数据表结果

事实上，无法直接编辑数据表中的各个单元格，因为它们包含 Excel 自动生成的数组公式。

要进行更改，您通常需要删除现有数据表，然后创建一个包含所需修改的新数据表。这涉及调整基本公式或输入值，然后再次设置数据表以反映这些更改。

手动重新计算数据表

通常，每次进行更改时，Excel 都会重新计算所有打开的工作簿中的所有公式。如果包含大量变量值和复杂公式的大型数据表导致 Excel 工作簿速度变慢。

为了避免每次工作簿中发生更改时 Excel 自动对所有数据表执行计算，您可以将计算模式从自动切换为手动。请按以下步骤操作：

去 公式 标签，然后单击 计算选项 > 自动（数据表除外），请参见屏幕截图：

修改此设置后，当您重新计算整个工作簿时，Excel 将不再自动更新数据表中的计算。

如果您需要手动更新数据表，只需选择显示结果的单元格（包含 TABLE() 公式的单元格），然后按 F9 键。

通过遵循本指南中概述的步骤并记住要点，您可以有效地利用数据表来满足您的数据分析需求。如果您有兴趣探索更多 Excel 提示和技巧，我们的网站提供了数千个教程， 请点击此处访问它们。 感谢您的阅读，我们期待在未来为您提供更多有用的信息！