OFFICE 365的FILTER函数，颠覆了我对Excel的认知②

最近推送的五篇文章：

• OFFICE 365的这些功能，颠覆了我对Excel的认知①
  

• 你真的理解了相对引用？这三点95%的人都不会，你呢？
  

• SUBTOTAL函数：统计筛选出的数据，就用它

• 【一本不正经系列】我把Excel玩坏了！
  

• 一本不正经地用Excel画了个。。那个，还是自己看吧
  

• 保护工作表时能否在指定单元格输入数据？能否使用切片器？
  

在上一篇介绍OFFICE 365的文章中，链接：

OFFICE 365的这些功能，颠覆了我对Excel的认知①

我们介绍了OFFICE 365体验版中强大的动态数组功能，它可以根据公式结果智能地在相应单元格区域显示。从本篇文章开始，我们将介绍几个新增的函数。这些函数可以让以前要用数组公式才能实现的功能，现在用一个函数就可轻松搞定。

先从筛选函数FILTER开始。

一、需求

在工作中，我们需要根据指定的条件，将符合条件的所有记录从数据源表格式查找过来。比如所有客户的交易明细存在同一张工作表，现需要将指定客户的交易明细查询出来：

要实现这个需求，如果不用Power Query的话，一是可以用高级筛选。但高级筛选在查询下一个单位时 ，需要重新设置一次，很麻烦。二是用函数公式查询。典型的套路有：INDEX+SMALL+ROW、或者LOOKUP+COUNTIF+OFFSET。如果我们不会数组公式，都是用VLOOKUP、COUNTIF结合辅助列，来实现查询符合条件的多个记录。

由于每个单位的往来明细条数不一样，没有动态数组这功能，用函数公式查询明细，每次都要改变修改填充公式或删除公式、以及设置单元格格式（可用条件格式智能设置格式）。

在OFFICE 365中，应用动态数组和FILTER函数，可完美的满足我们的需求。

二、FILTER函数语法

单词释义

filter

英 [ˈfɪltə(r)]   美 [ˈfɪltɚ]

n.滤波器;滤光器;滤色镜;[化] 过滤器

vi.过滤;透过;渗透

vt.过滤;滤除

函数语法

=FILTER（数据区域，筛选条件，[无满足条件的记录时返回的值]）

第二个参数“筛选条件”的计算结果要是TRUE或FALSE，这一点和高级筛选一样。

三、基本应用的案例

1、单条件筛选

筛选出办事处为“重庆”的记录。筛选公式：

=FILTER(A3:E22,C3:C22="重庆")

筛选结果貌似不能指定返回哪些列，如果能改为可根据指定的列标题，返回相应的列就更好了。

技巧：如何指定返回的列？

方法1：在参数1只选定要返回的列

方法2：用IF函数拼结两个不相邻的列

这个技巧我们常用于VLOOKUP的反向查找，详见《“偷懒”的技术：打造财务Excel达人》中的示例：

在这里我们也可用这个技巧来拼接，如下图：

貌似只能拼接二列，如果要拼接多列，可以用SWITCH函数：

2、多条件筛选（或）

在FILTER函数中，用加号来表示“或”。

要筛选出“四川”、“贵州”办事处的记录，其公式为：

=FILTER(A3:E22,(C3:C22="四川")+(C3:C22="贵州"))

如果改用OR，公式会出错：

=FILTER(A3:E22,OR((C3:C22="四川"),(C3:C22="贵州")))

3、多条件筛选（且）

在FILTER函数中，用星号（乘）表示“和、且”。

要筛选出“西部大区”，并且满足“2≤合同数量≤5”条件的记录，筛选公式为：

=FILTER(A3:E22,(B3:B22="西部大区")*(D3:D22>=2)*(D3:D22<=5))

四、拓展应用的案例

我们还可结合其他函数，来满足高级查询需求：

1、筛选“合同数量合计数大于5的办事处的所有记录”

由于要根据办事处的合计来筛选，普通的筛选很难实现，但用FILTER函数可轻松搞定。

筛选公式：

=FILTER(A3:E22,SUMIF(C3:C22,C3:C22,D3:D22)>5)

2、筛选只有一条记录的办事处

筛选公式：

=FILTER(A3:E22,COUNTIF(C3:C22,C3:C22)=1)

3、筛选出各办事处最大金额的记录

筛选公式：

=FILTER(A3:E22,E3:E22=MAXIFS(E3:E22,C3:C22,C3:C22))

4、筛选表1有，表2也有的记录（不考虑金额是否相同）

筛选公式：

=FILTER(表1,ISNUMBER(MATCH(表1[省份],表2[省份],0)),"")

5、筛选表1有，表2无的记录

筛选公式：

=FILTER(表1,ISNA(MATCH(表1[省份],表2[省份],0)),"")