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>$H$6,'',INDEX(A$8:A$17,MATCH($G12,$E$8:$E$17,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,$B$3:$B$4,0)),A9)
或：
=SUM(OR(D10=$B$3,D10=$B$4),N9)
示例：使用辅助列，AND和OR条件，使用INDEX和MATCH函数仅提取部分列的数据
如下图7所示，使用AND和OR条件的辅助列，只从日期和商品数列中提取数据。
图7：AND和OR条件，双向查找从日期和商品数列中获取数据
未完待续>>>
注：本文为电子书《精通Excel数组公式（学习笔记版）》中的一部分内容节选。你可以到知识星球App的完美Excel社群下载这本电子书的完整中文版。
欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。
联系客服
