精通Excel数组公式005：比较数组运算及使用一个或多个条件的聚合计算

下面是Excel的比较运算符：

= 等于

<> 不等于

> 大于

>= 大于等于

< 小于

<= 小于等于

在诸如基于条件查找最小值或最大值、计算标准偏差等情形时，Excel没有提供相应的内置函数，必须编写数组公式，其中往往涉及到在数组中使用比较运算符。

如下图1所示，在单元格区域A3:B8中记录了城市名和对应的时间，想要知道每个城市对应的最小时间。我们知道，可以使用MIN函数来求一组数值的最小值，但是如何分离出每个城市并分别求出它们对应的时间最小值呢？

图1

使用数组公式

Excel中没有一个MINIF函数来根据条件求相应的最小值，可以使用MIN/IF函数组合来实现。在单元格E3中的数组公式如下：

=MIN(IF(A3:A8=D3,B3:B8))

向下拉至单元格E5。

在公式中：

A3:A8=D3

将单元格区域A3:A8中的城市名与单元格D3中的城市名相比较，生成数组：

{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}

接着，IF函数根据比较的结果，从单元格区域B3:B8中获取单元格D3城市的时间：

{FALSE;FALSE;9;FALSE;FALSE;6}

IF函数忽略了参数value_if_false，默认值为FALSE。

传递给MIN函数：

=MIN({FALSE;FALSE;9;FALSE;FALSE;6})

得到单元格D3城市的最小时间。注意，MIN函数忽略其参数中的逻辑值。

使用数据库函数

在Excel中，有一组基于判断条件执行计算的数据库函数，共12个，也称之为D-函数，例如DMIN、DMAX和DSUM函数。当执行单独的计算且数据集具有字段名称（列标签）时，这些函数非常强大。

如下图2所示，使用DMIN函数来计算指定城市的最小时间。

图2

如果仅要知道某个城市的最小时间，使用DMIN函数比使用数组公式更简单且对于大数据集来说速度更快。然而，使用DMIN函数需要在某单元格中输入字段名并在该字段名下方输入判断条件，这意味着对于每次计算都需要在上下两个单元格中输入相应的内容。如下图3所示，显然，对于多个值不能像以前那样简单地下拉公式，这是其不利之处。

图3

有时候，对于非常大的数据来说公式计算时间过长是个问题，下图4展示了一个解决方案，充分利用D-函数优于数组公式计算的优势。

图4

下面是创建上述解决方案的步骤：

1. 在单元格E3中创建公式。

2. 在单元格区域D6:D8中输入所有的城市名称。

3. 在单元格E5中，创建指向单元格E3的公式。

4. 选择单元格区域D5:E8，按Alt、D、T键，（或者单击功能区“数据”选项卡“预测”组中的“模拟分析——模拟运算表”命令）打开“模拟运算表”对话框，如下图5所示。

图5

6. 在“输入引用列的单元格”中输入D3，单击“确定”按钮。

使用数据透视表

可以使用数据透视表来获得上文示例中的结果，如下图6所示。

图6

创建数据透视表的步骤如下：

1. 在要创建数据透视表的数据集中任选一单元格，单击“插入”选项卡“表格”组中的“数据透视表”命令。

2. 在“创建数据透视表”对话框的“选择放置数据透视表的位置”中选取“现有工作表”，输入：D1，单击“确定”。

3. 将“城市”字段拖至行区域，将“时间(h)”字段拖至值区域。

4. 在数据透视表的任意值单元格中，单击右键，选择“值字段设置”命令。在“值字段设置”对话框的“值字段汇总方式”列表框中，选择“最小值”。

5. 在数据透视表中单击右键，选择“数据透视表选项”命令。在“数据透视表选项”对话框的“汇总和筛选”选项卡中，取消“显示行总计”和“显示列总计”复选框。

6. 将数据透视表顶部字段修改为相应内容并调整布局。

可以看出，数据透视表对于带有一个或多个判断条件的聚合计算非常方便，但是与公式相比，当源数据变化时，它不能立即更新，需要刷新才能更新其内容。此外，数据透视表仅有11个函数可用，而公式有近400个可用函数。

两个条件的求值示例

下面再看一个多条件的例子。如下图7所示，在指定区域中分别计算每位销售代表的最大销售量。

图7

想要编写一个公式能够直接向下复制，且当源数据更新时结果能自动更新。有了上文的基础后，我们知道可以使用MAX函数配合两个嵌套的IF函数来实现。正如上图7中所示，在单元格F5中的数组公式为：

=MAX(IF(A3:A12=F2,IF(B3:B12=E5,C3:C12)))

其含义为，如果单元格区域A2:A12中的值等于单元格F2中的值，且单元格区域B3:B12中的值等于单元格E5中的值，则返回单元格区域C3:C12中相应的值，否则返回假。即生成数组：

{914;FALSE;FALSE;610;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

注意，嵌套的IF函数创建了一个AND条件判断。此示例也可以使用上文介绍的DMAX函数或数据透视表来实现，有兴趣的朋友可以试试。

再看一个示例。如下图8所示，分别为：跑道（不同城市中的不同跑道）、BMX赛车手（赛车手名字）、时间(s)（沿跑道行驶的时间，以秒计）。现在，想要计算每个赛车手的最快时间。然而，因为PI跑道非常短而其他跑道非常长，需要从计算中排除PI跑道的时间。

图8

我们在单元格F5中输入数组公式：

=MIN(IF(A3:A13<>F2,IF(B3:B13=E5,C3:C13)))

其原理与前一个示例相同，只是条件判断中使用了“<>”号，表示NOT运算。

我们看到，前面使用的数组公式必须以按Ctrl+Shift+回车键结束。自Excel 2010起，可以使用一个新函数：AGGREGATE函数，而无需按Ctrl+Shift+回车键。对于上例，使用AGGREGATE函数的解决方案如下图9所示。（注意，如果在公式里的IF函数中有数组操作，那么该公式必须按Ctrl+Shift+回车键结束，即便作为AGGREGATE函数的数组参数也是如此。）

图9

《Ctrl+Shift+Enter：MasteringExcel Array Formulas》学习笔记

完美Excel