Excel 中的资料表：建立一变量和2变量资料表-英雄云拓展知识分享

当您有一个依赖多个变数的复杂公式并且想要了解更改这些输入如何行之有效的地作用结果时，Excel 中的假定分析资料表是一个强盛的工具。它使您可以快捷阅读所有可能的结果。以下是有关如何在 Excel 中建立资料表的逐渐指南。另外，我们将讨论行之有效的使用资料表的一些关键点，并示範其他操作，例如删除、编辑和重新计算资料表。

Excel中的资料表是什幺？

建立单变量资料表

• 面向列的资料表
• 面向行的资料表
• 一变量资料表的多个公式

建立2变量资料表

使用数据表的要点

使用资料表的其他操作

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

Excel中的资料表是什幺？

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

Excel中有两类类型的资料表：

一变量资料表：这使您可以分析一个变数的不同值将如何作用公式。您可以採用面向行或面向列的格式设订单变量资料表，这取决于您是要在行中还是在列中变更输入。

2变量资料表：此类型可以让您查看更改两个不同变数对公式结果的作用。在双变量资料表中，您可以改变行和列上的值。

建立单变量资料表

在 Excel 中建立单变量资料表是一项宝贵的技能，可用于分析单一输入的变化如何作用各种结果。本节将引导您完成建立面向列、面向行和多公式一变量资料表的进程。

面向列的资料表

当您想要测试单一变数的不同值如何作用公式的输出（变数值在一列中列出）时，面向列的资料表非常有用。让我们斟酌一个简单的财务範例：

假定您正在斟酌一笔 50,000 美元的贷款，您计划在 3 年（相当于 36 个月）内偿还。为了针对您的薪资评估每个月还款额是可以承受的，您有兴趣探索不同的利率将如何作用您每个月需要支付的金额。

第 1 步：设定基本公式

为了计算还款额，这里我将利息设定为5%。在贮存格 B4 中，输入 PMT 公式，它针对利率、期数和贷款金额计算每个月还款。看截图：

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

步骤 2：在一栏列出利率

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

第3步：建立资料表

1. 在贮存格 E2 中，输入以下公式： = B4.
   
   备注: B4 是主公式所在的贮存格，您希望看到该公式的结果随着利率的变化而变化。

2. 选择包括公式、利率清单和结果相邻贮存格的范围（举例来看，选择 D2 到 E10）。看截图：
   
3. 转到功能区，按一下 数据 标签，然后单击 假定分析 > 数据表，请参见屏幕截图：
   
4. 在 数据表 对话框，点击 列 输入贮存格 方块（由于我们使用一列作为输入值），然后选择公式中援用的变数贮存格。在此範例中，我们选择包括利率的 B1。最后，点击 OK 按钮，请参见屏幕截图：
   
5. Excel 会自动以对应的结果填入每一个变数值（不同利率）旁边的空白贮存格。看截图：
   
6. 针对您的需求将所需的数位格式利用于结果（货币）。现在，列式资料表已成功创建，您可以快捷查看结果来评估当利率变化时您可以承受哪些每个月还款金额。看截图：
   

面向行的资料表

在 Excel 中建立面向行的资料表需要对资料进行排列，以便变数值跨行列出，而不是沿着列列出。以上面的範例为参考，我们继续依照步骤完成Excel中行式资料表的建立。

步骤 1：连续列出利率

将变数值（利率）放在一行中，确保左边最少有一个空白列用于公式，下方最少有一个空白行用于结果，请参阅萤幕截图：

第3步：建立资料表

1. 在贮存格 A9 中，输入以下公式： = B4.
   
2. 选择包括公式、利率清单和结果相邻贮存格的范围（举例来看，选择 A8 到 I9）。然后，点击 数据 > 假定分析 > 数据表.
3. 在 数据表 对话框，点击 行输入单元格 框（由于我们使用一行作为输入值），然后选择公式中援用的变数贮存格。在此範例中，我们选择包括利率的 B1。最后，点击 OK 按钮，请参见屏幕截图：
   
4. 针对您的需求将所需的数位格式利用于结果（货币）。现在，面向行的资料表已创建，请参见萤幕截图：
   

一变量资料表的多个公式

在 Excel 中建立包括多个公式的单变量资料表可以让您了解变更单一输入如何同时作用多个不同的公式。在上面的範例中，如果您想查看还款利率和总利息的利率变化，该怎幺办？设定方法以下：

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

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

=B4*B2*12-B3

步骤2：整理资料表的来源数据

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

第3步：建立资料表：

1. 在贮存格 E2 中，输入以下公式： = B4 建立原始资料中还款计算的参考。
2. 在贮存格 F2 中，输入以下公式： = B5 建立对原始资料的总兴趣的参考。
   
3. 选择包括公式、利率清单和结果相邻贮存格的范围（举例来看，选择 D2 到 F10）。然后，点击 数据 > 假定分析 > 数据表.
4. 在 数据表 对话框，点击 列输入单元格 方块（由于我们使用一列作为输入值），然后选择公式中援用的变数贮存格。在此範例中，我们选择包括利率的 B1。最后，点击 OK 按钮，请参见屏幕截图：
   
5. 针对您的需求将所需的数位格式利用于结果（货币）。您可以针对不同的变数值查看每一个公式的结果。
   

建立2变量资料表

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

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

在建立一变量资料表的範例的基本上，现在让我们继续学习如何在 Excel 中建立2变量资料表。

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

第 1 步：设定两个变化变数

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

第3步：建立资料表

1. 将公式放在列出变数值的行和列的交叉点。在这类情况下，我将输入这个公式： = B4 进入单元格 E2。看截图：
   
2. 选择包括贷款金额、利率、公式贮存格和将显示结果的贮存格的范围。
   
3. 然后，单击 数据 > 假定分析 > 数据表。在资料表对话框中：
   • 在 行输入单元格 在方块中，选择对行中变数值的输入贮存格的贮存格参考（在此範例中，B3 包括贷款金额）。
   • 在 列输入贮存格l 方块中，选择对列中变数值的输入贮存格的贮存格参考（B1 包括利率）。
   • 然后，单击 OK 按钮。
     

4. 现在，Excel 将使用每种贷款金额和利率组合的结果填入资料表。它可以直接了解贷款金额和利率的不同组合如何作用每个月还款额，使其成为财务计划和分析的宝贵工具。
   
5. 最后，您应当针对需要将所需的数位格式利用于结果（货币）。
   

使用数据表的要点

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

使用资料表的其他操作

建立资料表后，您可能需要履行其他操作来行之有效的管理它，包括删除资料表、修改其结果和履行手动重新计算。

删除资料表

由于资料表结果是使用阵列公式计算的，因此您没法从资料表中删除单一值。但是，您只能删除全部资料表。

选取所有资料表贮存格或仅选取包括结果的贮存格，然后按 删除 键盘上的键。

编辑资料表结果

事实上，没法直接编辑资料表中的各个贮存格，由于它们包括 Excel 自动产生的阵列公式。

若要进行更改，您通常需要删除现有资料表，然后建立一个包括所需修改的新资料表。这触及调剂基本公式或输入值，然后再次设定资料表以反应这些变更。

手动重新计算数据表

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

为了不每次工作簿中产生变更时 Excel 会自动对所有资料表履行计算，您可以将计算模式从自动切换为手动。请依照以下步骤操作：

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

修改此设定后，当您重新计算全部工作簿时，Excel 将不再自动更新资料表中的计算。

如果您需要手动更新资料表，只需选择显示结果的贮存格（包括 TABLE() 公式的贮存格），然后按 F9 键。

透过遵守本指南中概述的步骤并记住要点，您可以行之有效的地利用资料表来满足您的资料分析需求。如果您有兴趣探索更多 Excel 提示和技能，我们的网站提供了数千个教程， 请点击此处造访它们。 感谢您的浏览，我们期待在不久的将来为您提供更多有用的信息！