FILTER函数是Excel 2021版本中的新函数之一,作用是筛选符合条件的单元格。
语法:
=FILTER(要返回内容的数据区域,指定的条件,[没有记录时返回的内容])
借助Excel 2021的数组溢出功能,这个函数让数据查询变得非常便捷。今天咱们就一起来分享一下这个函数的一些典型应用。
1. 一对多查询
如下图所示,希望根据F2单元格中指定的部门,提取出左侧列表中“梁山部”的所有人员姓名。
G2单元格输入以下公式,按回车,公式结果会自动溢出到其他单元格。
=FILTER(B2:B10,A2:A10=F2)
公式中的B2:B10部分,是需要返回内容的单元格区域,A2:A10=F2部分是指定的条件。当指定条件返回逻辑值TRUE时,FILTER函数就返回B2:B16区域中,与之对应位置的记录。
2. 多对多查询
如下图所示,希望提取出部门为“梁山部”,并且星级为“五星”的所有人员姓名。H2单元格输入以下公式:
=FILTER(B2:B10,(A2:A10=F2)*(C2:C10=G2))
同样的道理,如有需要查找符合三个条件的话,那就是三个条件公式相乘。
如下图所示,需要提取不重复的名单。F2单元格输入以下公式:
=UNIQUE(FILTER(B2:B13,A2:A13>0))
简析:
注:UNIQUE函数的作用是从指定范围返回唯一值。
如下图所示,需要提取“梁山部”中的不重复的名单。G2单元格输入以下公式:
=UNIQUE(FILTER(B2:B13,A2:A13=F2))
简析:
5.提取包含关键字的记录
如下图所示,希望提取有星级的所有姓名。不论是多少星,都符合要求。G2单元格输入以下公式:
=FILTER(B2:B12,ISNUMBER(FIND(F2,C2:C12)))
简析:
首先,使用FIND函数,找出包含F2单元格内容所处的位置。如果某个单元格里包含F2中的内容,FIND函数返回表示位置的数字,否则返回错误值。最终得到一组由数字和错误值构成的数组。
然后,再使用ISNUMBER函数,判断FIND函数的结果是不是数字。如果某个单元格中包含了F2中的关键字,ISNUMBER函数返回逻辑值TRUE,否则返回FALSE。
最后,FILTER函数返回A2:A16单元格区域中与TRUE对应的整行记录。
联系客服