Excel中隐藏的明星函数 – Filter

一、 使用场景

经常使用EXCEL的人对它的“筛选”功能和“数据透视图”功能一定不陌生，实际工作中，我们经常需要将符合一定条件的记录从原始数据中查找出来。比如一个销售记录表，它包含销售地区、销售人员、销售产品及销售量这些信息，我们可能需要筛选某个销售人员或某个产品的销售量，再或者我们需要看哪些地区的销售量是在给定值以上的。

等等这些需求一般都是用“筛选”功能和“数据透视图”功能来实现的，但如果我们的查询条件很复杂，需要返回的信息也很多，那就不是一两步操作能解决的了，比如要查销售总量大于20000的销售地区的所有销售记录，需要先用数据透视图计算一下各地区的总销售量，得到符合条件的地区为“北部”，“西部”和“东部”，然后再用EXCEL的筛选功能筛出这几个地区的销售记录，在筛选的时候我们是要手动勾选这三个地区的，如果地区多了还挺废手的。
现在我们有了FILTER函数，一切复杂的操作都可以省略了，它可以基于各种条件生成一个动态数组，注意动态数组这个东西很奇妙，没有动态数组这功能，你要么需要借助一个数据透视表存储返回结果，要么需要在筛选数据时各种勾勾选选，这些操作的时间会远高于你熟练掌握FILTER函数后所要用的时间。而对于返回的动态数组我们还可以嵌套函数来对数组进行整合（见案例“筛选结果聚合”），这么好用的函数如果你还不知道，那真是太遗憾了。

二、 OFFICE版本

目前只有 Excel 365 和 Excel 2021 等较新版本支持这个函数的使用。

三、 FILTER函数语法

FILTER(array, include, [if_empty])

array 是你要从中过滤值的范围或数组。
include 是一个逻辑表达式，用于指定哪些值应被包含在结果中。
if_empty（可选）是当 array 为空时返回的值。如果省略，则返回一个空数组。

四、 使用举例-基础案例

1、 单条件筛选

筛选“苹果”的销售记录，筛选公式：

=FILTER(A5:D20,C5:C20=H3)

2、 多条件筛选-且关系
筛选地区为"东部"地区且产品为“苹果”的销售记录，注意这里用乘号来表示“且”，筛选公式：

=FILTER(B6:E21,(D6:D21=I4) * (B6:B21=I3))

3、 多条件筛选-或关系
筛选地区为"东部"地区或产品为“苹果”的销售记录，注意这里用加号来表示“或”，筛选公式：

=FILTER(B5:E20,(D5:D20=I3) + (B5:B20=I2))

五、 使用举例-高阶应用

1、 关键字筛选
筛选有销售人员“Tom”的销售记录，注意这里我们使用了函数FIND来定位符合条件的行，由于FIND函数的返回结果有异常值，又用ISNUMBER函数做了一层数据转换，最终的筛选公式：

=FILTER(B5:E20,ISNUMBER(FIND("Tom",C5:C20)))

2、 分类加和结果筛选
筛选销售总量大于20000的地区的所有销售记录，这里我们嵌套了SUMIF函数计算不同地区的销售量总和，然后用一个小表达式判断哪些销量是在20000以上的，最后使用FILTER返回符合条件的所有销售记录。如果是数据透视图来做这件事，需要先用数据透视计算一下各地区的总销售量，得到符合条件的地区为“北部”，“西部”和“东部”，然后再用EXCEL的筛选功能筛出这几个地区的销售记录。以下是FILTER的解决方法：

=FILTER(B6:E21,SUMIF(B6:B21,B6:B21,E6:E21)>20000)

3、 分类计数结果筛选
筛选只有1条销售记录的水果的所有销售记录，这里我们嵌套了COUNTIF函数计算不同产品的销售记录条数，以下是公式：

=FILTER(B6:E21,COUNTIF(D6:D21,D6:D21)=1)

4、 分类最大值项筛选
筛选每个地区中销售量最大的一条销售记录，这个案例类似于我们通过数据透视图对地区进行分类汇总，并计算每个地区的最大销售量是多少。使用FILTER函数不仅省去了数据透视的操作，还可以直接得到销售人员、产品等其他信息，可以说是非常方便好用了。

=FILTER(B6:E21,E6:E21=MAXIFS(E6:E21,B6:B21,B6:B21))

5、 筛选结果聚合
筛选有过苹果销售记录的销售人员，并把所有销售人员的姓名拼接成一个数组返回至一个单元格中。这个案例里我们使用了TEXTJOIN函数对返回结果做拼接，实际上厉害的不是TEXTJOIN，而是FILTER函数，由于它返回的是一个动态数组，且可以进行各种条件筛选，那么我们只需要外层套一些简单的聚合函数，比如加和或者字符串拼接，就可以得到意想不到的结果。

=TEXTJOIN(",",TRUE,FILTER(C6:C21,D6:D21="苹果"))