我们今天分享的是FILTER函数,她完美展现了365函数世界动态数组的理念;运算高效、公式简洁、还可以作为中转表的性质用于其它函数二次计算。在日常数据处理中,我们经常会遇到一对多式的数据查询问题。所谓一对多查询,简单而言,就是符合查询条件的结果有多个。以上图所示的成绩表为例,现在需要按G2单元格的指定班级名,查询相关信息,这样的信息可能是一条,也可能是多条,本例中查询结果如下图所示。如果你使用的Excel版本是365,可以使用一个函数干净利落的解决同类问题▼=FILTER(A2:E15,A2:A15=G2,””)
FILTER是过滤器的意思,顾名思义,该函数的主要功能是数据查询与筛选。它只有三个参数,第1个是数据源,第3个参数是容错值,可以在查无结果的情况下,屏蔽错误值#CALC!,返回一个指定值。重点是第2个参数,它用一组逻辑值指定了筛选的规则,如果为True则保留相应数据源记录,为False则删除相应记录。以公式=FILTER(A2:E15,A2:A15=G2,””)而言,数据源是A2:E15,筛选条件是A列班级是否等于G2单元格所指定的班级,如果相等,则保留相关记录,否则删除;而如果整条公式查无结果,则返回一个假空值。下面给大家举几个实例,说明一下FILTER函数如何轻松解决各种数据查询问题的,这包含了并且关系的多条件查询、或关系的多条件查询、单条件模糊查询、多条件模糊查询等。=FILTER(A2:E15,(A2:A15=”一班”)*(C2:C15=”男”),””)
(A2:A15=”一班”)*(C2:C15=”男”)是筛选条件,用乘法来表示并且关系。查询成绩大于90或者小于60的人员名单——学校需要搭建帮帮队。=FILTER(A2:E15,(E2:E15<60)+(E2:E15>90),””)
(E2:E15<60)+(E2:E15>90)是筛选条件,用加法来表示’或’关系。=FILTER(A2:E15,ISNUMBER(FIND(“看见”,B2:B15)),””)
ISNUMBER(FIND(“看见”,B2:B15))是筛选条件,用FIND来查找B2:15是否存在关键字’看见’,如果存在则返回位置序号,否则返回错误值;再用ISNUMBER判断是否为数值,返回由逻辑值TRUE和FALSE组成的内存数组。相比于普通版本的数组公式,比如INDEX+SMALL+IF套路,FILTER函数更强大的地方在于,它的返回结果是一个纯净的数组,可以嵌套在其它函数内部继续运算。结合我们前面两期分享的SORT和UNIQUE函数,我举几个例子。如下图所示,需要查询班级为一班的数据明细,并根据成绩列,对该查询结果降序排列。=SORT(FILTER(A2:E15,A2:A15=”一班”,””),5,-1)
FILTER函数筛选出班级为一班的数据明细,再使用SORT函数按第5列的成绩列降序排列。
如下图所示,需要查询人名中包含关键字’看见’的明细,并删除重复项,返回人名唯一值。=UNIQUE(FILTER(B2:B15,ISNUMBER(FIND(“看见”,B2:B15))))
FILTER函数筛选出人名中包含关键字’看见’的数据明细,再使用UNIQUE函数删除重复项。如下图所示,A:C是数据明细,需要在D列实现班级内部中式排名。=MATCH(C2,SORT(UNIQUE(FILTER(C$2:C$12,A$2:A$12=A2)),1,-1),0)
FILTER函数筛选出相同班级的成绩数据,UNIQUE函数对该结果去重复,SORT函数再对去重复后的成绩降序排列,最后使用MATCH函数查询当前成绩在去重并排序后成绩中的序号位置,也就是了中式排名结果了。https://pan.baidu.com/s/1hb0G-9nKPhqBed8NXBCurw 图片部分来自网络,如有侵权请联系QQ897219494,或者发布邮箱897219494@qq.com
技能大全,高效工具 »
Office 365函数新世界:动态数组