excelperfect
导语：本文为《精通Excel数组公式16：基于条件提取数据》的后半部分。
使用数组公式来提取数据
创建数据提取数组公式的技巧是在公式内部创建一个“匹配记录”相对位置的数组。如下图8所示，可以看到与条件相匹配的记录的相对位置是7和10，它们将作为INDEX的row_num参数的值。
图8：匹配的数据在数据集中的第7行和第10行
在单元格F12中输入下面的数组公式：
=IF(ROWS(F$12:F12)>$A$7,'',INDEX(A$11:A$20,SMALL(IF($A$11:$A$20>=$B$3,IF($A$11:$A$20<=$C$3,IF($B$11:$B$20=$D$3,ROW($A$11:$A$20)-ROW($A$11)+1))),ROWS(F$12:F12))))
向右向下拖动复制。结果如下图9所示。
图9：使用数组公式提取满足条件的记录
对于Excel2010及以后的版本来说，还可以使用AGGREGATE函数的公式：
=IF(ROWS(F$12:F12)>$A$7,'',INDEX(A$11:A$20,AGGREGATE(15,6,(ROW($A$11:$A$20)-ROW($A$11)+1)/(($A$11:$A$20>=$B$3)*($A$11:$A$20<=$C$3)*($B$11:$B$20=$D$3)),ROWS(F$12:F12))))
向右向下拖动复制。结果如下图10所示，注意，无需按Ctrl+Shift+Enter键。
图10：使用AGGREGATE函数的公式提取满足条件的记录
示例：从一个查找值返回多个值
在Excel中，诸如VLOOKUP、MATCH、INDEX等标准的查找函数不能够从一个查找值中返回多个值，除非使用数组公式。下面是一个示例，如下图11所示，在单元格D3中是查找值，需要从列B中找到相应的值并返回列A中对应的值。
图11：可以在INDEX函数的参数row_num中使用SMALL或AGGREGATE
下面的两个公式都可以实现。在单元格D6中输入公式：
=IF(ROWS(D$6:D6)>E$3,'',INDEX($A$3:$A$52,AGGREGATE(15,6,(ROW($A$3:$A$52)-ROW(A$3)+1)/($B$3:$B$52=D$3),ROWS(D$6:D6))))
或者输入数组公式：
=IF(ROWS(D$6:D6)>E$3,'',INDEX($A$3:$A$52,SMALL(IF($B$3:$B$52=D$3,ROW($A$3:$A$52)-ROW(D$3)+1),ROWS(D$6:D6))))
下拉复制至出现空单元格为止。
也可以使用辅助列来完成，如下图12所示。
图12：使用辅助列使公式更简单易懂
示例：提取满足OR条件和AND条件的数据
如下图13所示，需要提取West区域或者客户K商品数在400至1300之间的数据，使用的数组公式如图。
图13：提取满足OR条件和AND条件的数据
示例：提取满足OR条件和AND条件且能被5整除的数据
如下图14所示，需要提取West区域或者客户K且商品数能被5整除的数据，使用的公式如图。
图14：MOD函数使用来提取仅能被5整除的数据
示例：提取列表2中有而列表1中没有的数据项——列表比较
如下图15所示，对两个列表进行比较并提取数据。
1.获取在列表2中但不在列表1中的姓名。在单元格E9中输入数组公式：
=IF(ROWS(E$9:E9)>$E$5,'',INDEX($C$5:$C$9,SMALL(IF(ISNA(MATCH($C$5:$C$9,$A$5:$A$8,0)),ROW($C$5:$C$9)-ROW($C$5)+1),ROWS(E$9:E9))))
下拉复制至出现空单元格。
2.获取两个列表中都有的姓名。在单元格E22中输入数组公式：
=IF(ROWS(E$22:E22)>$E$18,'',INDEX($C$18:$C$22,SMALL(IF(ISNUMBER(MATCH($C$18:$C$22,$A$18:$A$21,0)),ROW($C$18:$C$22)-ROW($C$18)+1),ROWS(E$22:E22))))
下拉复制至出现空单元格。
图15：列表比较
示例：在数据提取区域使用辅助列
如下图16所示，要求提取区域在West或East的数据记录。此时，不允许在数据集区域使用辅助列，但为了节省计算时间，在提取区域使用辅助列。在单元格L10中的公式为：
=IF(F10>$G$3,'',AGGREGATE(15,6,(ROW($A$9:$A$18)-ROW($A$9)+1)/ISNUMBER(MATCH($B$9:$B$18,$B$3:$B$4,0)),F10))
在单元格G10中的公式为：
=IF($L10='','',INDEX(A$9:A$18,$L10))
向右向下复制到提取区域。
图16：计算相对行位置的公式元素移至辅助列
有时，可以为创建定义名称的动态单元格区域，以简化公式。
小结
1.使用IF函数代替IFERROR函数，因为IFERROR函数在每个单元格中计算，这将增加公式计算时间。
2.AND条件能够使用IF函数或者布尔算术运算创建。
3.OR条件能够使用IF函数或者布尔算术运算创建。在使用OR条件时要注意：对于单个列上的OR条件操作，ISNUMBER/MATCH组合比布尔OR加计算更容易创建且运算更快；对于多列上的OR条件操作，记住要考虑大于1的计数。
4.有两种有用的方法来考虑数据提取公式：提取匹配一组条件的记录或数据；从单个查找值返回多个数据值。
注：本文为电子书《精通Excel数组公式（学习笔记版）》中的一部分内容节选。你可以到知识星球App的完美Excel社群下载这本电子书的完整中文版。
欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。
联系客服
