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

数据表是单元格的一个范围，在其中你可以更改某些单元格中的值，从而针对一个问题得出不同的答案。数据表的一个很好的示例是使用 PMT 函数，通过不同的贷款金额和利率来计算房屋抵押贷款可负担的金额。试验不同的值以观察结果的相应变化是数据分析中的常见任务。

在 Microsoft Excel 中，数据表是“模拟分析”工具套件的一部分。当你构建并分析数据表时，就是在执行模拟分析。

模拟分析即通过更改单元格中的值并进而了解这些更改会如何影响工作表上公式的结果的过程。例如，你可以使用数据表来改变贷款的利率和期限，以评估潜在的每月还款金额。

模拟分析的类型

Excel 中有三种模拟分析工具：方案、数据表和单变量求解。方案和数据表使用输入值集来计算可能的结果。单变量求解则截然不同，它使用单个结果并计算可能产生该结果的输入值。

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

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

有关详细信息，请参阅模拟分析简介一文。

根据需要测试的变量和公式数量，创建单变量或双变量数据表。

单变量数据表

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

在下图中，单元格 D2 包含付款公式 =PMT(B3/12,B4,-B5)，该公式引用输入单元格 B3。

单变量模拟运算表

双变量数据表

使用双变量数据表可以查看一个公式中两个变量的不同值将如何更改该公式的结果。例如，你可以使用双变量数据表来查看利率和贷款期限的不同组合将如何影响每月的房屋抵押贷款还款额。

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

双变量模拟运算表

数据表计算

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

单变量数据表中的输入值既可以位于单列（面向列）中，也可以位于一行（面向行）中。单变量数据表中的任何公式都必须仅引用一个可变单元格。

请按以下步骤操作：

在输入单元格中键入要替换的值列表（可沿某一列向下输入，也可沿某一行横向输入）。在值的两侧留出几个空行和空列。
执行下列操作之一：
- 如果数据表是面向列（变量值位于一列中），请在该值列的上方一行且右侧一个单元格的位置键入公式。此单变量数据表是面向列的，公式包含在单元格 D2 中。如果要检查各种值对其他公式的影响，请在第一个公式右侧的单元格中输入其他公式。
- 如果数据表是面向行（变量值在某一行中），请在该值行中第一个值的左侧一列且下方一个单元格的位置输入公式。如果要检查各种值对其他公式的影响，请在第一个公式下方的单元格中输入其他公式。
选择包含要替换的公式和值的单元格范围。在上图中，此范围是 C2:D5。
在“数据”选项卡上，选择“模拟分析”>“数据表”（位于“数据工具”组或“预测”组中的 Excel 2016）。
执行下列操作之一：
- 如果数据表是面向列的，请在“列输入单元格”字段中输入输入单元格的单元格引用。在上图中，输入单元格是 B3。
- 如果数据表是面向行的，请在“行输入单元格”字段中输入输入单元格的单元格引用。
  
  注意: 创建数据表后，你可能需要更改结果单元格的格式。在图中，结果单元格的格式设置为货币。

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

请按以下步骤操作

执行以下任一操作：
- 如果数据表是面向列的，请在数据表顶行中现有公式右侧的空白单元格中输入新公式。
- 如果数据表是面向行的，请在数据表第一列中现有公式下方的空单元格中输入新公式。
选择包含数据表和新公式的单元格范围。
在“数据”选项卡上，选择“模拟分析”>“数据表”（位于“数据工具”组或“预测”组中的 Excel 2016）。
执行以下任一操作：
- 如果数据表是面向列的，请在“列输入单元格”框中输入输入单元格的单元格引用。
- 如果数据表是面向行的，请在“行输入单元格”框中输入输入单元格的单元格引用。

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

请按以下步骤操作：

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

在以下示例中（其中公式起始值输入到单元格 B3、B4 和 B5 中），你在单元格 C2 中键入公式 =PMT(B3/12,B4,-B5)。
在公式下方的同一列中键入一个输入值列表。

在这种情况下，在单元格 C3、C4 和 C5 中键入不同的利率。
在与公式相同的行中（在其右侧）输入第二个列表。

在单元格 D2 和 E2 中键入贷款期限（以月为单位）。
选择包含公式 (C2)、值的行和列 (C3:C5 和 D2:E2) 以及要在其中显示计算值 (D3:E5) 的单元格的单元格范围。

在这种情况下，选择范围 C2:E5。
在“数据”选项卡上，在“数据工具”组或“预测”组（位于 Excel 2016）中，选择“模拟分析”>“数据表”（位于“数据工具”组或“预测”组中的 Excel 2016）。
在“行输入单元格”字段中，输入行中输入值的输入单元格的引用。在“行输入单元格”框中键入单元格 B4。
在“列输入单元格”字段中，输入列中输入值的输入单元格的引用。在“列输入单元格”框中键入 B3。
选择“确定”。

双变量数据表示例

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

双变量模拟运算表

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

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

选择“文件”>“选项”>“公式”。
在“计算选项”部分中，选择“自动”。

提示: （可选）在“公式”选项卡上，选择“计算选项”上的箭头，然后选择“自动”。

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

单变量求解

如果知道公式的预期结果，但不确定公式需要什么输入值才能获得该结果，请使用“单变量求解”功能。请参阅使用单变量求解通过调整输入值来查找你想要的结果一文。

Excel 规划求解

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