精通Excel数组公式024：模拟运算表

excelperfect

本文介绍模拟运算表功能，其使用TABLE函数创建一个结果数组。使用模拟运算表是一种对使用公式输入的公式进行假设分析的快速而简单的方法。该功能允许修改一个或两个公式输入，显示多个假设分析结果。

使用单变量模拟运算表进行单公式假设分析

下图1展示单元格B6中使用公式输入的PMT函数。如果修改单元格B2（年利率）中的输入，PMT函数将更新。然而，这里的目标是修改输入为5个不同的利率并显示所有5个PMT结果。虽然创建自已的公式可以很容易完成，但是使用模拟运算表功能更有优势，主要原因为：

1.模拟运算表比公式计算更快。

2.使用模拟运算表替代许多公式时，公式创建时间会更快。

对于图1示例，需要执行下列步骤来获得创建解决方案的模拟运算表：

1.选择单元格区域A10:B15。原因是单元格B10包含一个公式，该公式指向要进行假设分析的公式。此外，单元格区域A11:A15包含PMT函数的新的假设分析公式输入，这是想要“替换”到PMT函数中生成5个新值的5个值。

2.按Alt D，T打开模拟运算表对话框。（或者单击功能区“数据”选项卡“预测”组中的“模拟分析——模拟运算表”）

3.因为“替换”值在列中，单击“输入引用列的单元格”文本框，选择单元格B6中PMT函数指向的原始公式输入，即单元格B2（注意PMT间接指向B2）。这里告诉模拟运算表从单元格B2中删除PMT计算过程中的值，将单元格区域A11:A15中的值替换成公式。

4.单击确定。

图1

如下图2所示，如果选择单元格区域B11:B15，将会在公式栏中看到TABLE函数。TABLE函数不是一个可以手动输入的函数，它在使用模拟运算表对话框时自动创建和输入。

图2

使用单变量模拟运算表用一个公式代替多个公式

如下图3所示，单元格区域E3:I3中的每个单元格都包含一个不同的公式，直接或间接引用单元格B3中单位销售量的公式输入。通过使用模拟运算表，可以对这5个公式基于单元格区域D4:D12中的单位进行假设分析。

图3

使用双变量模拟运算表

本示例的目标是创建一个交叉表，显示基于两个条件的最小值。可以使用双变量模拟运算表，如下图4所示。注意到，必须输入一个公式到该表的左上角单元格中。可以通过设置自定义格式（;;;）隐藏这个值。

图4

这个示例中，DMIN公式和模拟运算表代替了数组公式：

=MIN(IF($B$2:$B$19=$F9,IF($C$2:$C$19=G$8,$D$2:$D$19)))

下图5展示了另一个示例，使用双变量模拟运算表代替了数组公式：

=INDEX($C$2:$C$15,MATCH($E9&F$8,$A$2:$A$15&$B$2:$B$15,0))

这个示例在本系列前面也出现过。

图5

注意，在Excel中有一个选项：除模拟运算表外，自动重算。如果你选取了该选项，模拟运算表不会自动更新。