精通数组公式16：基于条件提取数据 / 开普饭

excelperfect

在Excel中，基于AND或OR条件从数据集中提取数据是经常要做的事。可以使用下列方法来实现：

1.筛选

2.高级筛选

3.使用辅助列的非数组公式

4.使用SMALL函数和INDEX函数的数组公式

5.使用AGGREGATE函数和INDEX函数的数组公式

关于上述5种方法的要点如下：

1.筛选和高级筛选通常比公式更容易使用，但是不像公式能即时更新。

2.当单元格中的条件改变时，公式能够即时更新。

3.使用辅助列的非数组公式解决方案比数组公式计算速度更快。

4.数组公式可能使用许多单元格引用、包含许多计算，因此可能要更长的计算时间。要减小计算时间，考虑使用辅助列、布尔逻辑构造和有效的函数。

5.这里没有考虑使用VBA解决方案，有时使用它们是自动执行数据提取的好方法。

为何提取数据的公式如此复杂？

当从表中提取数据时，实际上是在执行查找。在Excel中，标准的查找函数例如INDEX、MATCH、VLOOKUP等都非常好，但当存在重复值时就比较困难了。如下图1所示，提取满足3个条件的数据记录，可以看出有2条记录满足条件。对于垂直表，从多列中提取数据的查找公式不会很难；查找公式难于在多行中使用。如果需要使用公式提取记录，那么有两个基本的方法：

1.基于辅助列使用标准的查找函数。辅助列包含提供顺序号的公式，只要公式找到了满足条件的记录。这些顺序号解决了重复值问题，因为对于每条匹配的记录都有唯一的标识号。辅助列作为查找列，供查找函数查找并提取数据。

2.基于全数据集的数组公式。这些公式是独立的，不需要额外的列辅助。对于这些公式，必须在公式内为与条件相匹配的记录创建一个相对位置数组。

图1：需要提取两条记录，标准的查找函数对于重复值有些困难。

使用辅助列来提取数据

假设有3个AND条件来决定要提取的记录，如下图2所示，可以在辅助列中使用AND函数。辅助列作为INDEX函数的查找列。单独使用AND函数的问题是获得了两个TRUE值，这意味着又回到了查找列中有重复项的问题。真正想要的是查找列包含数字，其中单元格E14中第一个TRUE是数字1，而E17中第二个TRUE是数字2。