office365 自定义函数（Office365函数新世界FILTER函数）

我们今天分享的是FILTER函数，她完美展现了365函数世界动态数组的理念；运算高效、公式简洁、还可以作为中转表的性质用于其它函数二次计算，下面我们就来聊聊关于office365 自定义函数?接下来我们就一起去了解一下吧!

office365 自定义函数

我们今天分享的是FILTER函数，她完美展现了365函数世界动态数组的理念；运算高效、公式简洁、还可以作为中转表的性质用于其它函数二次计算。

基础语法

在日常数据处理中，我们经常会遇到一对多式的数据查询问题。

所谓一对多查询，简单而言，就是符合查询条件的结果有多个。

我举个例子。

以上图所示的成绩表为例，现在需要按G2单元格的指定班级名，查询相关信息，这样的信息可能是一条，也可能是多条，本例中查询结果如下图所示。

如果你使用的Excel版本是365，可以使用一个函数干净利落的解决同类问题▼

G5单元格输入公式▼

=FILTER(A2:E15,A2:A15=G2,"")

FILTER是过滤器的意思，顾名思义，该函数的主要功能是数据查询与筛选。

FILTER函数的语法▼

=FILTER(数据源,筛选条件,容错值)

它只有三个参数，第1个是数据源，第3个参数是容错值，可以在查无结果的情况下，屏蔽错误值#CALC!，返回一个指定值。

重点是第2个参数，它用一组逻辑值指定了筛选的规则，如果为True则保留相应数据源记录，为False则删除相应记录。

以公式=FILTER(A2:E15,A2:A15=G2,"")而言，数据源是A2:E15，筛选条件是A列班级是否等于G2单元格所指定的班级，如果相等，则保留相关记录，否则删除；而如果整条公式查无结果，则返回一个假空值。

……下面给大家举几个实例，说明一下FILTER函数如何轻松解决各种数据查询问题的，这包含了并且关系的多条件查询、或关系的多条件查询、单条件模糊查询、多条件模糊查询等。

1）并且关系的多条件查询

查询班级为'一班'、性别为'男'的数据明细。

并且关系▼

=FILTER(A2:E15,(A2:A15="一班")*(C2:C15="男"),"")

(A2:A15="一班")*(C2:C15="男")是筛选条件，用乘法来表示并且关系。

2）或关系的多条件查询

查询成绩大于90或者小于60的人员名单——学校需要搭建帮帮队。

或关系▼

=FILTER(A2:E15,(E2:E15<60) (E2:E15>90),"")

(E2:E15<60) (E2:E15>90)是筛选条件，用加法来表示'或'关系。

3）模糊条件查询

查询人名中包含关键字'看见'的数据明细。

模糊筛选▼

=FILTER(A2:E15,ISNUMBER(FIND("看见",B2:B15)),"")

ISNUMBER(FIND("看见",B2:B15))是筛选条件，用FIND来查找B2:15是否存在关键字'看见'，如果存在则返回位置序号，否则返回错误值；再用ISNUMBER判断是否为数值，返回由逻辑值TRUE和FALSE组成的内存数组。

扩展用法

相比于普通版本的数组公式，比如INDEX SMALL IF套路，FILTER函数更强大的地方在于，它的返回结果是一个纯净的数组，可以嵌套在其它函数内部继续运算。

结合我们前面两期分享的SORT和UNIQUE函数，我举几个例子。

1）对查询结果排序

如下图所示，需要查询班级为一班的数据明细，并根据成绩列，对该查询结果降序排列。

公式如下：

=SORT(FILTER(A2:E15,A2:A15="一班",""),5,-1)

FILTER函数筛选出班级为一班的数据明细，再使用SORT函数按第5列的成绩列降序排列。

2）对查询结果去重复

如下图所示，需要查询人名中包含关键字'看见'的明细，并删除重复项，返回人名唯一值。

实现公式如下：

=UNIQUE(FILTER(B2:B15,ISNUMBER(FIND("看见",B2:B15))))

FILTER函数筛选出人名中包含关键字'看见'的数据明细，再使用UNIQUE函数删除重复项。

3）分类中式排名

如下图所示，A:C是数据明细，需要在D列实现班级内部中式排名。

实现公式如下：

=MATCH(C2,SORT(UNIQUE(FILTER(C$2:C$12,A$2:A$12=A2)),1,-1),0)

FILTER函数筛选出相同班级的成绩数据，UNIQUE函数对该结果去重复，SORT函数再对去重复后的成绩降序排列，最后使用MATCH函数查询当前成绩在去重并排序后成绩中的序号位置，也就是了中式排名结果了。

没了，挥挥手，下期再见。

图文作者：看见星光

原载：Excel星球