KutoolsforOffice — 一套方案，五大工具。事半功倍。

Excel 中的数据表：创建单变量和双变量数据表

作者Xiaoyang修改日期

当您面对一个依赖多个变量的复杂公式，并希望高效掌握输入值变化对结果的影响时，Excel 的模拟分析数据表便成为一款强大利器——让您一眼尽览所有可能的结果。以下是在 Excel 中创建数据表的分步指南，同时我们还将介绍高效使用数据表的关键要点，并演示如何删除、编辑及重新计算数据表等操作。

创建数据表

Excel 中的数据表是什么？

创建单变量数据表

创建双变量数据表

使用数据表的关键要点

使用数据表的其他操作

Excel 中的数据表是什么？

在 Excel 中，数据表是模拟分析的利器之一，让您轻松尝试不同的输入值，并直观观察公式结果的变化。无论是探索多种场景，还是进行敏感性分析——尤其是在公式涉及多个变量时——这一工具都极具价值。

注意：数据表不同于常规 Excel 表格。
  • 数据表允许您在公式中测试不同输入值，并实时观察这些变化对输出结果的影响。特别适用于敏感性分析、情景规划和财务建模，助您轻松掌握关键变量间的关联！
  • Excel 表格用于高效管理和分析相关数据。它是一种结构化的数据区域，支持轻松排序、筛选及其他操作。与侧重于基于不同输入探索多种结果的数据表不同，Excel 表格专为高效管理与分析数据集而设计。

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

单变量数据表：此类型可帮您分析一个变量的不同取值对公式结果的影响。您可以根据需要将单变量数据表设为横向或纵向格式，具体取决于希望输入值在行中还是列中变化。
单变量数据表

双变量数据表：该类型可让您直观查看两个不同变量的变化对公式结果的影响。在双变量数据表中，您可同时沿行和列调整数值。
双变量数据表

创建单变量数据表

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

列导向型数据表

当您希望测试单个变量的不同取值对公式结果的影响，且这些变量值按列排列时，纵向数据表就非常实用。让我们来看一个简单的财务示例：

假设您正在考虑一笔 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. 转到功能区，单击数据选项卡，然后单击模拟分析> 数据表，参见截图：
    “点击Data Table"/>
  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 for excel AI 的截图

借助 KUTOOLS AI 解锁 Excel 魔力

  • 智能执行：只需输入简单命令，即可执行单元格操作、分析数据并创建图表。
  • 自定义公式：生成量身定制的公式，助您优化工作流程！
  • VBA 编程：轻松编写并运行 VBA 代码。
  • 公式解析：轻松掌握复杂公式，一目了然！
  • 文本翻译：轻松打破电子表格中的语言障碍！
借助 AI 驱动的工具提升您的 Excel 能力。立即下载体验前所未有的高效！

创建双变量数据表

Excel 中的双变量数据表能够直观展示两组变量值的不同组合对公式结果的影响，清晰呈现当公式中的两个输入值同时变化时所产生的综合效果。

此处我绘制了一个简单示意图，帮助您更好地理解双变量数据表的外观和结构。
双变量数据表示意图

在前述单变量数据表示例的基础上，我们现在继续学习如何在 Excel 中创建双变量数据表。

在下方数据集中，我们已知利率、贷款期限和贷款金额，并使用以下公式计算出月供：=-PMT($B$1/12, $B$2*12, $B$3)。接下来，我们将聚焦于数据中的两个关键变量——利率与贷款金额，观察二者同时变化时如何影响月供。
示例数据

步骤 1：设置两个变动变量

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

步骤 2：创建数据表

  1. 将您的公式置于所列变量值所在行与列的交叉处。在本例中，我将在单元格 E2 中输入以下公式：=B4. 参见截图：
    输入公式以链接到付款单元格
  2. 选择包含您的贷款金额、利率、公式单元格以及将显示结果的单元格的区域。
    选择区域
  3. 然后，单击数据 > 模拟分析 > 数据表。在“数据表”对话框中：
    • 在行可变单元格框中，选择变量值所在行对应的单元格引用（本例中为 B3，包含贷款金额）。
    • 在列输入单元格框中，选择变量值所在列对应的单元格引用（例如 B1 包含利率）。
    • 然后，单击确定按钮。
      在对话框中设置选项
  4. 现在，Excel 将自动为每种贷款金额与利率的组合填充数据表中的结果，直观呈现不同组合对月供的影响，是财务规划与分析的得力助手。
    已创建双变量数据表
  5. 最后，您应根据需要为结果应用相应的数字格式（如货币）。
    对结果应用所需的数字格式

使用数据表的关键要点

  • 新创建的数据表必须与原始数据位于同一工作表中。
  • 数据表的输出取决于源数据中设置的公式单元格，对该公式单元格的任何更改都会自动更新输出。
  • 在数据表中计算出数值后，无法通过 Ctrl + Z 撤销操作，但您可以手动全选这些数值并删除。
  • 数据表会生成数组公式，因此无法修改或删除表中的单个单元格。

使用数据表的其他操作

创建数据表后，您可能需要执行一些额外操作来高效管理它，例如删除数据表、修改其结果，以及手动触发重新计算。

删除数据表

由于数据表结果是通过数组公式计算得出的，因此无法单独删除数据表中的某个值，只能整体删除整个数据表。

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

编辑数据表结果

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

如需修改，通常需先删除现有数据表，再根据所需调整重新创建一个新数据表——包括调整基础公式或输入值，并重新设置数据表以准确反映这些更改。

手动重新计算数据表

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

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

转到公式选项卡，然后单击计算选项> 除数据表外自动重算，参见截图：
点击“除数据表外自动计算”以禁用自动计算

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

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

通过遵循本指南中的步骤并牢记关键要点，您可高效地将数据表应用于数据分析需求。如需探索更多 Excel 技巧，欢迎访问我们的网站，数千篇实用教程等您发现！请点击此处访问。感谢阅读，期待未来为您带来更多实用信息！