精通数组公式17：基于条件提取数据（续） / 开普饭

excelperfect

使用数组公式来提取数据

创建数据提取数组公式的技巧是在公式内部创建一个“匹配记录”相对位置的数组。如下图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社群下载这本电子书的完整中文版。

精通数组公式17：基于条件提取数据（续）

相关推荐