Excel模拟运算表应用深入了解

模拟运算表是一个单元格区域，用于显示公式 （公式:单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号 (=) 开始。）中一个或两个变量的更改对公式结果的影响。模拟运算表提供了一种快捷手段，它可以通过一步操作计算多个结果；同时，它还是一种有效的方法，可以查看和比较由工作表中不同变化所引起的各种结果。

概述

模拟运算表是一组命令的组成部分，这些命令也被称作模拟分析工具。使用模拟运算表即意味着执行模拟分析。

模拟分析是指通过更改单元格中的值来查看这些更改对工作表中公式结果的影响的过程。例如，可以使用模拟运算表更改贷款利率和期限以确定可能的月还款额。

模拟分析工具的种类    

Excel 中包含三种模拟分析工具：方案、模拟运算表和单变量求解。方案和模拟运算表根据各组输入值来确定可能的结果。单变量求解与方案和模拟运算表的工作方式不同，它获取结果并确定生成该结果的可能的输入值。

与方案类似的是，模拟运算表有助于寻找一组可能的结果。不同于方案的是，模拟运算表会在工作表中的一个表中显示所有结果。使用模拟运算表可以轻松查看一系列可能性。由于只关注一个或两个变量，表格形式的结果易于阅读和共享。

模拟运算表无法容纳两个以上的变量。如果要分析两个以上的变量，则应改用方案。尽管只能使用一个或两个变量（一个用于行输入单元格，另一个用于列输入单元格），但模拟运算表可以包括任意数量的不同变量值。方案可拥有最多 32 个不同的值，但可以创建任意数量的方案。

模拟运算表基础

创建单变量模拟运算表还是双变量模拟运算表，取决于需要测试的变量和公式数。

单变量模拟运算表    

若要了解一个或多个公式中一个变量的不同值如何改变这些公式的结果，请使用单变量模拟运算表。例如，可以使用单变量模拟运算表来查看不同的利率水平对使用 PMT 函数计算的月按揭付款的影响。在单列或单行中输入变量值后，结果便会在相邻的列或行中显示。

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

---

双变量模拟运算表    

使用双变量模拟运算表可以查看一个公式中两个变量的不同值对该公式结果的影响。例如，可以使用双变量模拟运算表来查看利率和贷款期限的不同组合对月还款额的影响。

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

---

模拟运算表计算    

每当重新计算工作表时，也会同时重新计算模拟运算表，即使模拟运算表未曾发生更改。若要加快包含模拟运算表的工作表的计算速度，可以更改“计算”选项，使其自动重新计算工作表而不重新计算模拟运算表。

创建单变量模拟运算表

单变量模拟运算表的输入值被排列在一列（列方向）或一行（行方向）中。单变量模拟运算表中使用的公式必须仅引用一个输入单元格 （输入单元格:在该单元格中，源于模拟运算表的输入值将被替换。工作表中的任何单元格都可用作输入单元格。尽管输入单元格不必是模拟运算表的一部分，但是模拟运算表中的公式必须引用输入单元格。）。

在一列或一行中的单元格中，键入要替换的值列表。将值任一侧的几行和几列单元格保留为空白。 请执行下列操作之一： 如果模拟运算表为列方向的（变量值位于一列中），请在紧接变量值列右上角的单元格中键入公式。“概述”部分中所示的单变量模拟运算表插图是列方向的，公式包含在单元格 D2 中。

若要检查各个值在其他公式中的效果，请在第一个公式右侧的单元格中键入其他公式。
  如果模拟运算表为行方向的（变量值位于一行中），请在紧接变量值行左下角的单元格中键入公式。

若要检查各个值在其他公式中的效果，请在第一个公式下方的单元格中键入其他公式。
  选定包含需要替换的数值和公式的单元格区域。根据前述“概述”部分中的第一个插图，此区域为 C2:D5。

 

在“数据”选项卡上的“数据工具”组中，单击“模拟分析”，然后单击“模拟运算表”。

请执行下列操作之一： 如果模拟运算表为列方向，请在“输入引用列的单元格”框中，为输入单元格键入单元格引用 （单元格引用:用于表示单元格在工作表上所处位置的坐标集。例如，显示在第 B 列和第 3 行交叉处的单元格，其引用形式为“B3”。）。根据第一个插图中所示的示例，输入单元格为 B3。 如果模拟运算表是行方向的，请在“输入引用行的单元格”框中，为输入单元格键入单元格引用。

 

 注释   创建模拟运算表后，可能需要更改结果单元格的格式。在插图中，结果单元格使用了货币格式。

为单变量模拟运算表添加公式

在单变量模拟运算表中使用的公式必须引用相同的输入单元格。

请执行下列操作之一： 如果模拟运算表为列方向（变量值位于列中），请在模拟运算表首行现有公式右面的空白单元格中键入新公式。 如果模拟运算表为行方向（变量值位于行中），请在模拟运算表首列现有公式下面的空白单元格中键入新公式。 选定含有模拟运算表和新公式的单元格区域。

 

在“数据”选项卡上的“数据工具”组中，单击“模拟分析”，然后单击“模拟运算表”。

请执行下列操作之一： 如果模拟运算表为列方向，请在“输入引用列的单元格”框中，为输入单元格键入单元格引用。 如果模拟运算表是行方向的，请在“输入引用行的单元格”框中，为输入单元格键入单元格引用。

 

创建双变量模拟运算表

双变量模拟运算表使用含有两个输入值列表的公式。该公式必须引用两个不同的输入单元格。

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

在下例中，在单元格 B3、B4 和 B5 中输入公式的初始值，您可在单元格 C2 中键入公式 =PMT(B3/12,B4,-B5)。
在同一列中，在公式下方键入一个输入值列表。

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

在单元格 D2 和 E2 中键入贷款期限（月数）。
选择单元格区域，其中包含公式 (C2) 、数值行和列（C3:C5 和 D2:E2），以及要在其中放入计算值的单元格 (D3:E5)。

在这种情况下，选择区域 C2:E5。
在“数据”选项卡上的“数据工具”组中，单击“模拟分析”，然后单击“模拟运算表”。 在“输入引用行的单元格”框中，请输入由行数值替换的输入单元格的引用。

在“输入引用行的单元格”中键入 B4。
在“输入引用列的单元格”框中，请输入由列数值替换的输入单元格的引用。
在“输入引用列的单元格”中键入 B3。
单击“确定”。 

示例    双变量模拟运算表可显示不同利率和贷款期限的组合对月还款额的影响。在下图中，单元格 C2 中包含还款公式 =PMT(B3/12,B4,-B5)，它使用了两个输入单元格 B3 和 B4。

 

加快包含模拟运算表的工作表中的计算速度

依次单击“文件”选项卡、“选项”和“公式”类别。 在“计算方式选项”部分的“计算”下，单击“除模拟运算表外，自动计算”。

 提示   或者，在“公式”选项卡上“计算”组中，单击“计算选项”上的箭头，然后单击“除模拟运算表外，自动计算”。

 注释   选择该计算选项后，在重新计算工作簿的其他部分时，将跳过模拟运算表。若要手动重新计算模拟运算表，请选择模拟运算表公式，然后按 F9。