根据条件查数据,FILTER函数最简单

摘要:FILTER函数的作用是筛选符合条件的单元格,目前可以在Excel 2021以及最新版的WPS表格中使用。

FILTER函数的作用是筛选符合条件的单元格,目前可以在Excel 2021以及最新版的WPS表格中使用。

常用写法为:

=FILTER(要返回内容的数据区域,指定的条件,[没有记录时返回的内容])

今天咱们就一起来分享一下这个函数的一些典型应用。

1、一对多查询

如下图所示,希望根据E2单元格中指定的品牌,提取出左侧列表中该品牌的所有商品。

E5单元格输入以下公式,按回车,公式结果会自动溢出到其他单元格。

=FILTER(A2:A13,B2:B13=E2)

公式中的A2:A13部分,是需要返回内容的单元格区域。

B2:B13=E2部分是指定的条件。

当指定条件返回逻辑值TRUE时,FILTER函数就返回A2:A13单元格区域中与之对应的整行记录。

2、多对多查询

如下图所示,希望提取出品牌为“松下”,并且库存数大于20的所有记录。

E5单元格输入以下公式,按回车。

=FILTER(A2:A13,(B2:B13=E2)*(C2:C13>20))

本例中要返回内容的单元格区域是A2:A13,指定的条件为(B2:B13=E2)*(C2:C13>20)。

两个条件之间使用乘号,表示同时符合。

(B2:B13=E2)和(C2:C13>20)分别返回一组由TRUE和FALSE构成的逻辑值的内存数组。

当两组逻辑值的对应位置都是TRUE时,说明两个条件同时符合,相乘后的结果为1,否则返回0。

如果相乘后的某项结果不等于0,FILTER函数就返回A2:A13单元格区域中对应位置的整行记录。

3、提取包含关键字的记录

如下图所示,希望提取包含关键字“音响”的所有商品名。

E5单元格输入以下公式,按回车。

=FILTER(A2:A13,ISNUMBER(FIND(E2,A2:A13)))

本例中指定的条件为ISNUMBER(FIND(E2,A2:A13))。

先使用FIND函数,获取E2单元格中的关键字在A2:A13每个单元格所处的位置。如果某个单元格里包含E2中的内容,FIND函数返回表示位置的数字,否则返回错误值。最终得到一组由数字和错误值构成的内存数组。

然后再使用ISNUMBER函数,判断FIND函数的结果是不是数字。如果某个单元格中包含了E2中的关键字,ISNUMBER函数返回逻辑值TRUE,否则返回FALSE。

最终FILTER函数返回A2:A13单元格区域中与TRUE对应的整行记录。

4、提取未售出商品

如下图所示,希望根据A列的商品列表和C列的已售商品列表,提取全部未售出的商品。

E2单元格输入以下公式,按回车。

=FILTER(A2:A13,COUNTIF(C2:C5,A2:A13)=0)

本例中指定的条件为COUNTIF(C2:C5,A2:A13)=0

先使用COUNTIF函数统计A2:A13单元格中的每个元素在C2:C5中出现的次数,得到一组由1和0构成的内存数组。

结果为1的,表示在C2:C5中出现过(已售出)。结果为0的,则表示A列的姓名在C2:C5中没有出现过(未售出)。

接下来判断内存数组中的各个元素是否等于0,返回由TRUE或FALSE构成的内存数组。

FILTER函数最终返回A2:A13单元格区域中与TRUE对应的整行记录。

好了,今天咱们的分享就是这些,祝各位一天好心情~~

图文制作:祝洪忠

来源:Excel之家

相关推荐