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

注意: 我们希望能够尽快以你的语言为你提供最新的帮助内容。 本页面是自动翻译的,可能包含语法错误或不准确之处。 我们的目的是使此内容能对你有所帮助。 可以在本页面底部告诉我们此信息是否对你有帮助吗? 请在此处查看本文的英文版本以便参考。

数据表是一个单元格区域, 可在其中更改某些单元格中的值, 并提出有关问题的不同答案。 数据表的一个很好的示例使用具有不同贷款金额和利率的PMT函数, 以在家庭抵押贷款上计算价格低廉的金额。 通过试验不同的值来观察结果中的相应变体是数据分析中的常见任务。

在 Microsoft Excel 中, 数据表是一组称为 "模拟分析工具" 的命令的一部分。 构造和分析数据表时, 你正在执行模拟分析。

模拟分析是更改单元格中的值以查看这些更改对工作表上的公式结果有何影响的过程。 例如, 您可以使用数据表改变贷款的利率和术语长度, 以评估潜在的每月付款金额。

注意: 你可以通过数据表和 Visual Basic for Applications (VBA) 执行更快的计算。 有关详细信息, 请参阅Excel 模拟运算表: 用 VBA 更快地进行计算

模拟分析的类型    

Excel 中有三种类型的假设分析工具:方案、数据表目标寻道。 方案和数据表使用输入值集来计算可能的结果。 目标-寻道的差别各不相同, 它使用单个结果并计算可能产生该结果的输入值。

与方案一样, 数据表可帮助您探索一组可能的结果。 与方案不同, 数据表在一个工作表上显示一个表中的所有结果。 通过使用数据表, 可轻松查看一系列可能的功能。 因为仅关注一个或两个变量,便可轻松阅读并以表格形式共享所得结果。

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

有关详细信息, 请在本文中了解有关分析分析的简介

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

单变量模拟运算表    

如果要查看一个或多个公式中一个变量的不同值如何更改这些公式的结果, 请使用单变量模拟运算表。 例如, 您可以使用一个单变量的数据表来查看不同利率使用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 的 "数据工具" 组或 "预测组" 中)。 

  5. 执行下列操作之一:

    • 如果数据表是列方向的, 请在 "列输入单元格" 字段中输入输入单元格的单元格引用 。 在上图中, 输入单元格为 B3。

    • 如果数据表是行方向, 请在 "输入引用行的单元格" 字段中输入输入单元格的单元格引用。

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

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

请按以下步骤操作

  1. 执行以下任一操作:

    • 如果模拟运算表为列方向, 请在数据表首行的现有公式右侧的空白单元格中输入新公式。

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

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

  3. 在 "数据" 选项卡上, 单击 "模拟分析>" 数据表 (在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 中) 中, 单击 "模拟分析 _GT_数据表" (在Excel 2016 的 "数据工具" 组或 "预测组" 中)。 

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

  7. 在 "输入引用列的单元格" 字段中, 输入对列中输入值的输入单元格的引用。
    在 "输入到列的单元格" 框中键入 " B3 "。

  8. 单击“确定”。

双变量模拟运算表的示例

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

双变量模拟运算表

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

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

  1. 执行下列任一操作:

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

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

  2. 在 "计算选项" 部分的 "计算" 下, 单击 "除模拟运算表外自动"。

    提示: (可选) 在 "公式" 选项卡上, 单击 "计算选项" 上的箭头, 然后单击 "计算" 组中的 "自动" (数据表格除外)。

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

单变量求解

如果您知道公式中的结果是预期的, 但不知道公式需要什么输入值来获得该结果, 请使用 "目标寻道" 功能。 请参阅使用 "目标查找" 通过调整输入值来查找所需结果的文章。

Excel 规划求解

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

通过在单元格中插入不同的数字, 你可以快速获得问题的不同答案。 一个很好的示例是将PMT函数与不同的利率和贷款期 (以月为单位) 结合使用, 以确定您可以为家庭或汽车支付多少贷款。 将数字输入到一个称为数据表的单元格区域中。

此处, 数据表是单元格 B2: D8 区域。 您可以在 "D" 栏中更改 "数字"、"贷款金额" 和 "D 月付款" 的值。 使用 3.75% 的利率, D2 将使用以下公式返回 $1042.01 的每月付款: = PMT (C2/12, $B $3, $B $4)。

此单元格区域 B2:D8 为数据表

你可以使用一个或两个变量, 具体取决于要测试的变量和公式的数量。

使用单变量测试查看公式中一个变量的不同值将如何更改结果。 例如, 您可以使用 PMT 函数更改每月抵押支付的利率。 在一列或一行中输入变量值 (利率), 结果将显示在附近的列或行中。

在此实时工作簿中, 单元格 D2 包含付款公式=PMT (C2/12, $B $3, $B $4)。 单元格 B3 是可变单元格, 您可以在其中插入不同术语长度 (每月付款期数)。 在单元格 D2 中, PMT 函数将其插入利率 3.75 = 12、360月和 $225000 贷款, 并计算 $1042.01 每月付款。

使用双变量测试查看公式中两个变量的不同值将如何更改结果。 例如, 您可以测试利率和每月付款期数的不同组合, 以计算抵押支付。

在此实时工作簿中, 单元格 C3 包含付款公式, =PMT ($B $ 3/12, $B $ 2, B4), 它使用两个可变单元格 B2 和 B3。 在单元格 C2 中, PMT 函数会将利率 3.875/12、360月和 $225000 贷款一起插入, 并计算 $1058.03 每月付款。

需要更多帮助吗?

可随时在 Excel 技术社区中咨询专家,在解答社区获得支持,或在 Excel User Voice 上建议新功能或功能改进。

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

此信息是否有帮助?

谢谢您的反馈!

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

×