Excel Filter 函数的用法

函数FILTER当前在Excel2021,Excl365，最新WPS版本中可用。

=FILTER(查找区域，条件，备用返回值)

它可以很灵活地实现各种条件下的数据查找，在数据查找领域称王称霸30年之久的VLOOKUP在FILTER面前简直就是弱鸡。

一对一查找

=FILTER(D:D,A:A=F2)

在D列查找指定姓名对应的薪资。

一对多

查找销售部的姓名清单，一个查找条件返回多个查找结果，以数组的形式溢出显示：

=FILTER(A:A,C:C=F1)

一对整

查找销售部所有人的姓名及所有信息，只需把整体数据区域作为查找区域：

=FILTER(A:D,C:C="销售部")

横向查找

FILTER也可以支持横向数据的查找：

=FILTER($4:$4,$1:$1=B7)

多条件（与）

实际工作中查找条件很可能不止一个，如果是多个条件同时满足，统统放进FILTER的第二参数，用星号（*）将其连接即可：

=FILTER(A:A,(B:B="女")*(D:D>8000))

注意第二参数包含的2个条件：性别为女；薪资大于8000

多条件（或）

有多个条件，但只需满足其中之一或几个，同样把所有条件设置到第二参数，用加号（+）连接即可：

=FILTER(A:A,(B:B="女")+(D:D>8000))

备用返回值

FILTER第三参数用于指定查找失败时的返回值，例如找不到安全部时返回“没有这个部门”：

=FILTER(A:D,C:C="安全部","没有这个部门")

第三参数也可以再次套用函数来实现更为复杂的功能。

转置

搭配TRANSPOSE可以将FILTER的结果进行行列转置：

=TRANSPOSE(FILTER(A:A,C:C=E2))

二维查询

二维数据的查找是Excel中经久不衰的话题，主流的方法是VLOOKUP+MATCH,INDEX+MACH.

FILTER+FILTER嵌套也能实现同样的效果：

=FILTER(FILTER($A$2:$D$9,$A$1:$D$1=G$1),$A$2:$A$9=$F2)

返回不连续的列

在Excel365中搭配函数CHOOSECOLS可以让二维查找变得更加简单，即便是查找项目的数量和顺序与原数据都不一致的情况：

=CHOOSECOLS(FILTER(A:F,B:B=H2),5,3,1,6)

其逻辑是FILTER查找返回整体数据后，用CHOOSECOLS提取所需的列。

相对于FILTER+FILTER嵌套，这种方式更容易理解，也避免了繁杂的相对引用和绝对引用设置，大大降低公式难度。

搭配XMATCH返回不连续的列

上个公式中CHOOSCOLS的参数3，5，1，6表示从FILTER返回的数据中提取第3，5，1，6列，如果要返回的列数更多，手动输入难免有出错的风险，于是再嵌套XMATCH来获取：

=CHOOSECOLS(FILTER(A:F,B:B=H2),XMATCH($I$1:$L$1,$A$1:$F$1,0))

XMATCH也是Excel365函数，其作用是返回I1:L1在A1:F1中的相对位置。

建立Excel数组思维，学会数组公式，刻不容缓