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社群,进行技术交流和提问,获取更多电子资料。
|
|
来自: hercules028 > 《excel》