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

模拟运算表是一个单元格区域,您可以更改其中某些单元格的值,得到问题的不同的答案。一个很好的示例是,使用 PMT 函数,用不同的贷款金额和利率计算您能够负担多少房屋或车的贷款。尝试以可变值产生不同的结果,是数据分析这一学科的组成部分。

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

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

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

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

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

有关模拟分析的信息,请参阅模拟分析简介

模拟运算表基础

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

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

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

单变量模拟运算表

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

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

双变量模拟运算表

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

创建单变量模拟运算表

单变量模拟运算表的输入值纵排成一列(列方向)或横排成一行(行方向)。单变量模拟运算表中使用的公式必须仅引用一个可变单元格。

  1. 在一列或一行中的单元格中,键入要替换的值列表。将值任一侧的几行和几列单元格保留为空白。

  2. 然后执行下列操作之一:

    • 如果模拟运算表为列方向的(变量值位于一列中),请在紧接变量值列右上角的单元格中键入公式。“概述”部分中所示的单变量模拟运算表插图是列方向的,公式包含在单元格 D2 中。
      若要检查各个值在其他公式中的效果,请在第一个公式右侧的单元格中键入其他公式。

    • 如果模拟运算表为行方向的(变量值位于一行中),请在紧接变量值行左下角的单元格中键入公式。
      若要检查各个值对其他公式的影响,请在第一个公式下方的单元格中键入其他公式。

  3. 选定包含需要替换的数值和公式的单元格区域。根据前述“概述”部分中的第一个插图,此区域为 C2:D5。

  4. 在“数据”选项卡上的“数据工具”组或“趋势预测”组(Excel 2016 中)中,单击“模拟分析”,然后单击“模拟运算表”。

  5. 然后执行下列操作之一:

    • 如果模拟运算表为列方向,请在“输入引用列的单元格”框中,为输入单元格键入 单元格引用。根据第一个插图中所示的示例,输入单元格为 B3。

    • 如果模拟运算表是行方向的,请在“输入引用行的单元格”框中,为输入单元格键入单元格引用。

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

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

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

  1. 然后执行下列操作之一:

    • 如果模拟运算表为列方向(变量值位于列中),请在模拟运算表首行现有公式右面的空白单元格中键入新公式。

    • 如果模拟运算表为行方向(变量值位于行中),请在模拟运算表首列现有公式下面的空白单元格中键入新公式。

  2. 选定含有模拟运算表和新公式的单元格区域。

  3. 在“数据”选项卡上的“数据工具”组或“趋势预测”组(Excel 2016 中)中,单击“模拟分析”,然后单击“模拟运算表”。

  4. 然后执行下列操作之一:

    • 如果模拟运算表为列方向,请在“输入引用列的单元格”框中,为输入单元格键入单元格引用。

    • 如果模拟运算表是行方向的,请在“输入引用行的单元格”框中,为输入单元格键入单元格引用。

创建双变量模拟运算表

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

  1. 在工作表的某个单元格内,输入引用了两个输入单元格的公式。

    在下例中,公式的初始值位于单元格 B3、B4 和 B5 中,可将公式 =PMT(B3/12,B4,-B5) 键入单元格 C2 中。

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

    在本例中,在单元格 C3、C4 和 C5 中键入不同的利率。

  3. 在公式右边的同一行中输入第二列输入值。

    在单元格 D2 和 E2 中,键入贷款期限(以月为单位)。

  4. 选择单元格区域,其中包含公式 (C2)、数值行和列(C3:C5 和 D2:E2),以及要在其中放入计算值的单元格 (D3:E5)。

    在本例中,选择区域 C2:E5。

  5. 在“数据”选项卡上的“数据工具”组或“趋势预测”组(Excel 2016 中)中,单击“模拟分析”,然后单击“模拟运算表”。

  6. 在“输入引用行的单元格”框中,请输入由行数值替换的输入单元格的引用。
    在“输入引用行的单元格”中键入单元格 B4

  7. “输入引用列的单元格”框中,请输入由列数值替换的输入单元格的引用。
    在“输入引用列的单元格”中键入 B3

  8. 单击“确定”。

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

双变量模拟运算表

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

  1. 请执行下列操作之一:

    • 在 Excel 2007 中,单击“Microsoft Office 按钮Office 按钮图像 ,单击“Excel 选项”,然后单击“公式”类别。

    • 在所有其他版本中,单击“文件”>“选项”>“公式”。

  2. “计算方式选项”部分的“计算”下,单击“除模拟运算表外,自动计算”

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

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

下一步是什么?

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

单变量求解

如果您知道从公式获得的结果,但不确定为获得该结果所需的公式输入值,则请使用“单变量求解”功能。请参阅使用单变量求解通过调整输入值来查找所需的结果一文。

Excel 规划求解

您可以使用 Excel 规划求解加载项,查找基于多个变量的最佳值。规划求解使用一组单元格(被调用的决策变量或简单变量单元格),这些单元格用于计算目标和限制单元格中的公式。规划求解调整决策变量单元格中的值,以满足限制单元格的限制条件,并为目标单元格生成所需的结果。请参阅使用规划求解定义和解决问题一文。

返回页首

扩展你的技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×