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

excelperfect

导语：本文为《精通Excel数组公式16：基于条件提取数据》的后半部分。

使用数组公式来提取数据

创建数据提取数组公式的技巧是在公式内部创建一个“匹配记录”相对位置的数组。如下图8所示，可以看到与条件相匹配的记录的相对位置是7和10，它们将作为INDEX的row_num参数的值。

图8：匹配的数据在数据集中的第7行和第10行

在单元格F12中输入下面的数组公式：

=IF(ROWS(F12:F12)>7,"",INDEX(A20,SMALL(IF(A11:A20>=3,IF(A11:A20<=3,IF(B11:B20=D3,ROW(A11:A20)-ROW(A11)+1))),ROWS(F12:F12))))

向右向下拖动复制。结果如下图9所示。

图9：使用数组公式提取满足条件的记录

对于Excel2010及以后的版本来说，还可以使用AGGREGATE函数的公式：

=IF(ROWS(F12:F12)>7,"",INDEX(A20,AGGREGATE(15,6,(ROW(A11:A20)-ROW(A11)+1)/((A11:A20>=3)*(A11:A20<=3)*(B11:B20=D3)),ROWS(F12:F12))))

向右向下拖动复制。结果如下图10所示，注意，无需按Ctrl+Shift+Enter键。

图10：使用AGGREGATE函数的公式提取满足条件的记录

示例：从一个查找值返回多个值

在Excel中，诸如VLOOKUP、MATCH、INDEX等标准的查找函数不能够从一个查找值中返回多个值，除非使用数组公式。下面是一个示例，如下图11所示，在单元格D3中是查找值，需要从列B中找到相应的值并返回列A中对应的值。

图11：可以在INDEX函数的参数row_num中使用SMALL或AGGREGATE

下面的两个公式都可以实现。在单元格D6中输入公式：

=IF(ROWS(D6:D6)>EA3:A52,AGGREGATE(15,6,(ROW(A3:A52)-ROW(A3)+1)/(B3:B52=D3),ROWS(D

或者输入数组公式：

=IF(ROWS(D6:D6)>EA3:A52,SMALL(IF(B3:B52=D3,ROW(A3:A52)-ROW(D3)+1),ROWS(D

下拉复制至出现空单元格为止。

也可以使用辅助列来完成，如下图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(E9:E9)>5,"",INDEX(5:C9,SMALL(IF(ISNA(MATCH(C5:C9,A5:A8,0)),ROW(C5:C9)-ROW(C5)+1),ROWS(E9:E9))))

下拉复制至出现空单元格。

2.获取两个列表中都有的姓名。在单元格E22中输入数组公式：

=IF(ROWS(E22:E22)>18,"",INDEX(18:C22,SMALL(IF(ISNUMBER(MATCH(C18:C22,A18:A21,0)),ROW(C18:C22)-ROW(C18)+1),ROWS(E22:E22))))

下拉复制至出现空单元格。

图15：列表比较

示例：在数据提取区域使用辅助列

如下图16所示，要求提取区域在West或East的数据记录。此时，不允许在数据集区域使用辅助列，但为了节省计算时间，在提取区域使用辅助列。在单元格L10中的公式为：

=IF(F10>A9:A18)-ROW(A9)+1)/ISNUMBER(MATCH(B9:B18,B3:B4,0)),F10))

在单元格G10中的公式为：

=IF(L10="","",INDEX(A18,L10))

向右向下复制到提取区域。

图16：计算相对行位置的公式元素移至辅助列

有时，可以为创建定义名称的动态单元格区域，以简化公式。

小结

1.使用IF函数代替IFERROR函数，因为IFERROR函数在每个单元格中计算，这将增加公式计算时间。

2.AND条件能够使用IF函数或者布尔算术运算创建。

3.OR条件能够使用IF函数或者布尔算术运算创建。在使用OR条件时要注意：对于单个列上的OR条件操作，ISNUMBER/MATCH组合比布尔OR加计算更容易创建且运算更快；对于多列上的OR条件操作，记住要考虑大于1的计数。

4.有两种有用的方法来考虑数据提取公式：提取匹配一组条件的记录或数据；从单个查找值返回多个数据值。

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

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