Excel中的数据表：创建一维和二维数据表

当您有一个依赖于多个变量的复杂公式，并希望高效地了解更改这些输入如何影响结果时，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：在列中列出利率

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

步骤3：创建数据表

1. 在单元格E2中输入以下公式： =B4.
   
   注意：B4是主公式所在的单元格，这是您希望在改变利率时看到其结果变化的公式。
   
2. 选择包含公式、利率列表和相邻结果单元格的范围（例如，选择D2到E10）。见截图：
   
3. 转到功能区，点击 数据 选项卡，然后点击 假设分析 > 数据表，见截图：
   数据表"/>
4. 在 数据表 对话框中，点击 列输入单元格 框（因为我们使用列作为输入值），并选择公式中引用的变量单元格。在此示例中，我们选择包含利率的B1。最后，点击 确定 按钮，见截图：
   
5. Excel将自动填充每个变量值（不同的利率）旁边空单元格的相应结果。见截图：
   
6. 根据需要将结果应用所需的数字格式（货币）。现在，列向数据表已成功创建，您可以快速浏览结果，评估利率变化时哪些月付款金额对您来说是可负担的。见截图：
   

行向数据表

在Excel中创建行向数据表涉及安排您的数据，使变量值列在行中而不是列中。以上述示例为参考，让我们继续完成在Excel中创建行向数据表的步骤。

步骤1：在行中列出利率

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

步骤2：创建数据表

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

一维数据表的多公式

在Excel中创建具有多个公式的一维数据表，可以让您看到单个输入的变化如何同时影响多个不同的公式。在上述示例中，如果您想看到利率变化对还款和总利息的影响怎么办？以下是设置方法：

步骤1：添加新公式以计算总利息

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

=B4*B2*12-B3

步骤2：安排数据表的源数据

在列中，列出您要测试的不同利率，并在右侧留出至少两列空白列用于结果。见截图：

步骤3：创建数据表：

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

使用 Kutools AI 解锁 Excel 魔法

• 智能执行：执行单元格操作、分析数据和创建图表——所有这些都由简单命令驱动。
• 自定义公式：生成量身定制的公式，优化您的工作流程。
• VBA 编码：轻松编写和实现 VBA 代码。
• 公式解释：轻松理解复杂公式。
• 文本翻译：打破电子表格中的语言障碍。

通过人工智能驱动的工具增强您的 Excel 能力。立即下载，体验前所未有的高效！

创建二维数据表

Excel中的二维数据表显示了两组变量值的不同组合对公式结果的影响，展示了公式中两个输入的同时变化如何影响其结果。

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

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

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

步骤1：设置两个变化的变量

1. 在列中，列出您要测试的不同利率。见截图：
   
2. 在行中，输入不同的贷款金额值，刚好在列值上方（从公式单元格右边的一个单元格开始），见截图：
   

步骤2：创建数据表

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

使用数据表的关键点

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

使用数据表的其他操作

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

删除数据表

由于数据表结果是通过数组公式计算的，您不能从数据表中删除单个值。然而，您只能删除整个数据表。

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

编辑数据表结果

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

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

手动重新计算数据表

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

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

转到 公式 选项卡，然后点击 计算选项 > 除数据表外自动，见截图：

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

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

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