精通数组公式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。

图2：辅助列公式的第1部分涉及到AND函数

如下图3所示，将AND函数作为SUM函数的第1个参数，使用相对引用将公式所在单元格的上方单元格作为SUM函数的第2个参数。注意，SUM函数将逻辑值转换成1或0，并且忽略文本值。

图3：最终的辅助列公式使用SUM函数将AND函数的逻辑值与上方单元格中的值相加

单元格H6是一个辅助单元格。在该单元格中，输入公式：

=MAX(E8:E17)

得到匹配的记录的条数。

使用辅助单元格，可以帮助减小公式的计算时间。

如下图4所示，在单元格H12中输入公式：

=IF(G12>6,"",INDEX(A17,MATCH(G12,E8:E17,0)))

向左向下拖动复制。

图4：在单元格H12中输入的最终公式

当条件改变或者数据增加时，提取区域的数据会自动更新，如下图5所示。

图5：数据变化时，公式结果会自动更新

示例：使用辅助列，OR条件，VLOOKUP作为查找函数

如下图6所示，使用了OR条件的辅助列并且放置在第1列，因此可以使用VLOOKUP函数。注意，VLOOKUP公式中的参数col_index_num使用了COLUMNS函数，当公式向下复制时，其数值将依次递增为2、3、4、5，等等。

图6：使用辅助列，OR条件和VLOOKUP

注：当所有OR逻辑测试都指向同一列时，可以使用下列两种公式构造之一：ISNUMBER/MATCH函数，或者OR函数。而ISNUMBER/MATCH函数组合运算更快且对于多个条件更容易构造。

对于本示例，构造辅助列的公式：

=SUM(ISNUMBER(MATCH(D10,B3:B4,0)),A9)

或：

=SUM(OR(D10=B3,D10=B4),N9)

示例：使用辅助列，AND和OR条件，使用INDEX和MATCH函数仅提取部分列的数据

如下图7所示，使用AND和OR条件的辅助列，只从日期和商品数列中提取数据。

图7：AND和OR条件，双向查找从日期和商品数列中获取数据

未完待续>>>

注：本文为电子书《精通Excel数组公式（学习笔记版）》中的一部分内容节选。你可以到知识星球App的完美Excel社群下载这本电子书的完整中文版。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。