使用模拟运算表计算多个结果

在 Microsoft Excel 中，数据表是称为 What-If 分析工具的命令套件的一部分。 构造和分析数据表时，你正在执行 What-if 分析。

What-if 分析是更改单元格中的值的过程，以查看这些更改将如何影响工作表上公式的结果。 例如，可以使用数据表来改变贷款的利率和期限长度，以评估可能的每月付款金额。

What-if 分析的类型    

Excel 中有三种类型的 What-if 分析工具： 方案、数据表和 目标查找。 方案和数据表使用输入值集来计算可能的结果。 目标查找明显不同，它使用单个结果并计算产生该结果的可能输入值。

与方案一样，数据表可帮助你探索一组可能的结果。 与方案不同，数据表在一个工作表的一个表中显示所有结果。 使用数据表可以轻松一目了然地检查各种可能性。 因为仅关注一个或两个变量，便可轻松阅读并以表格形式共享所得结果。

一个数据表不能容纳两个以上的变量。 如果要分析两个以上的变量，应改用方案。 尽管它仅限于一个或两个变量， (一个用于行输入单元格，一个用于列输入单元格) ，但数据表可以包含任意多个不同的变量值。 一个方案最多可以有 32 个不同的值，但你可以根据需要创建任意数量的方案。

有关详细信息，请参阅 What-If 分析简介一文。

创建单变量或双变量数据表，具体取决于需要测试的变量和公式的数量。

单变量数据表    

如果要查看一个或多个公式中一个变量的不同值将如何更改这些公式的结果，请使用单变量数据表。 例如，可以使用单变量数据表，通过 PMT 函数查看不同的利率如何影响每月抵押贷款付款。 在一列或一行中输入变量值，结果显示在相邻的列或行中。

在下图中，单元格 D2 包含付款公式 =PMT (B3/12，B4，-B5) ，它指的是输入单元格 B3。

双变量数据表    

使用双变量数据表可查看一个公式中两个变量的不同值将如何更改该公式的结果。 例如，可以使用双变量数据表来了解利率和贷款条款的不同组合对每月抵押贷款付款的影响。

在下图中，单元格 C2 包含付款公式 =PMT (B3/12，B4，-B5) ，它使用两个输入单元格 B3 和 B4。

 

数据表计算    

每当工作表重新计算时，任何数据表也会重新计算，即使数据没有更改也是如此。 若要加快包含数据表的工作表的计算速度，可以更改 “计算 ”选项以自动重新计算工作表，而不是重新计算数据表。 若要了解详细信息，请参阅 包含数据表的工作表中的加速计算部分。

单变量数据表包含其输入值 (列面向列) 的单个列，或跨一行 (面向行的) 。 单变量数据表中的任何公式只能引用一 可变单元格 。

请按以下步骤操作：

1. 在输入单元格中键入要替换的值列表（向下一列或跨一行）。 在值的任一侧保留几个空行和列。

2. 执行下列操作之一：

   • 如果数据表 面向列 (变量值位于列) 中，请在上方一行的单元格中键入公式，在值列右侧键入一个单元格。 此单变量数据表面向列，公式包含在单元格 D2 中。
     
     
     
     如果要检查各种值对其他公式的影响，请在第一个公式右侧的单元格中输入其他公式。

   • 如果数据表 面向行 (变量值位于) 行中，请在单元格中键入公式（位于第一个值左侧的一列）和值行下方的一个单元格。
     
     如果要检查各种值对其他公式的影响，请在第一个公式 下面的 单元格中输入其他公式。

3. 选择包含要替换的公式和值的单元格区域。 在上图中，此范围是 C2：D5。

4. 在“数据”选项卡上，单击“数据工具”组或“ Excel 2016 ) 预测”组中的“What-If Analysis >Data Table (”。 

5. 执行下列操作之一：

   • 如果数据表面向列，请在“列输入单元格”字段中输入输入 单元格 的 单元格引用 。 在上图中，输入单元格为 B3。

   • 如果数据表面向行，请在“行输入单元格”字段中输入输入 单元格的单元格 引用。

     注意: 创建数据表后，可能需要更改结果单元格的格式。 在图中，结果单元格的格式为货币。

单变量数据表中使用的公式必须引用同一个输入单元格。

请按以下步骤操作

1. 执行以下任一操作：

   • 如果数据表面向列，请在数据表顶部行现有公式右侧的空白单元格中输入新公式。

   • 如果数据表面向行，请在数据表第一列中现有公式下方的空单元格中输入新公式。

2. 选择包含数据表和新公式的单元格区域。

3. 在“数据”选项卡上，单击“Data Tools”组或“预测”组中的“What-If Analysis > Data Table (”， Excel 2016 ) 。

4. 执行以下任一操作：

   • 如果数据表面向列，请在“列输入单元格”框中输入输入 单元格的单元格 引用。

   • 如果数据表面向行，请在“行输入单元格”框中输入输入 单元格的单元格 引用。

双变量数据表使用包含两个输入值列表的公式。 公式必须引用两个不同的输入单元格。

请按以下步骤操作：

1. 在工作表上的单元格中，输入引用两个输入单元格的公式。

   在以下示例中，在单元格 B3、B4 和 B5 中输入公式起始值时，在单元格 C2 中键入公式 =PMT (B3/12，B4，-B5) 。

2. 在公式下方的同一列中键入一个输入值列表。

   在这种情况下，请在单元格 C3、C4 和 C5 中键入不同的利率。

3. 在公式右侧的同一行中输入第二个列表。

   在单元格 D2 和 E2 中键入贷款条款 (（以月为单位) ）。

4. 选择包含公式 (C2) 的单元格区域， (C3：C5 和 D2：E2) 的值行和列，以及需要计算值的单元格 (D3：E5) 。

   在这种情况下，请选择范围 C2：E5。

5. 在“数据”选项卡上的“数据工具”组或“预测”组中 ( Excel 2016 ) ，单击“数据工具”组或“预测”组中的“What-If Analysis >Data Table (”或“ Excel 2016 ) 的预测组”。 

6. 在 “行输入单元格 ”字段中，输入对行中输入值的输入单元格的引用。
   在“行输入单元格”框中键入单元格 B4。

7. 在 “列输入单元格 ”字段中，输入对列中输入值的输入单元格的引用。
   在“列输入单元格”框中键入 B3。

8. 单击“确定”。

双变量数据表示例

双变量数据表可以显示利率和贷款条件的不同组合将如何影响每月抵押贷款付款。 在此图中，单元格 C2 包含付款公式 =PMT (B3/12，B4，-B5) ，它使用两个输入单元格 B3 和 B4。

设置此计算选项时，在整个工作簿上执行重新计算时，不会发生数据表计算。 若要手动重新计算数据表，请选择其公式，然后按 F9。

按照以下步骤提高计算性能：

1. 单击“ 文件 > 选项 ”> 公式。

2. 在 “计算选项” 部分的“ 计算”下，单击“自动”（ 数据表除外）。

   提示: （可选）在“公式”选项卡上，单击“计算选项”上的箭头，然后在“计算”组中单击“自动除数据表 (”) 。

如果你有特定目标或较大的变量数据集，则可以使用一些其他 Excel 工具执行 What-if 分析。

单变量求解

如果知道公式预期的结果，但不知道公式获取该结果所需的输入值，请使用 Goal-Seek 功能。 请参阅文章 使用目标查找通过调整输入值来查找所需的结果。

Excel 规划求解

可以使用 Excel 规划求解加载项查找一组输入变量的最佳值。 规划求解器适用于一组 (称为决策变量的单元格，或者只是用于计算目标和约束单元格中的公式的变量单元格) 。 “规划求解”调整决策变量单元格中的值以满足约束单元格上的限制，并产生您对目标单元格期望的结果。 有关详细信息，请参阅本文： 使用规划求解定义和解决问题。