点击关注【秋叶 Excel】

发送【6】

领取秋叶 Excel 6 年精选文章合集

作者：明镜在心

编辑：卫星酱

在 Office 365 和 Excel 2021 及以上版本中，有一个非常好用的函数——Filter。

它基本上可以代替之前在函数界称霸武林十几年之久的万金油函数组合（Index+Small+If+Row）。

我们在之前的文章中（），也介绍过该函数的基本用法和它的厉害之处。

但是，今天要介绍的这两个神秘用法，一般人还真不一定知道！

有点好奇？那就赶紧上车，跟我一起来看看吧！

实现二维方向查找

在之前我们介绍该函数的用法中，一般都是在一维方向实现单条件或者多条件查找引用。

如下图，是一张销售明细表，需要查找客户名称为：AA 的商品名称有多少种。

我们可以在【I4】单元格输入如下公式：

=FILTER(C3:C9,B3:B9=J2)

公式的意思是：

在 B 列的客户名称中，如果有与客户名称 AA 相同的，就返回显示 C 列中对应的商品名称。

如果需要查看下单日期，那我们就必须把公式的第一参数引用区域更改成 A 列。

公式如下：

=FILTER(A3:A9,B3:B9=J2)

那么再想查看规格型号，就要再次更改第一参数的引用区域。

这样操作是不是很麻烦？

那么如何才能在一个二维的区域中，快速查找并显示我们想要的数据，而不需要每次更改公式的引用区域呢？

这时我想起了那样一句经典的话：

没有什么问题是用一个函数解决不了的。

如果有，那就再来一个。

好吧，那我们就再来一个 Fliter 函数吧，

把上面的公式稍微改动下，如下图：

公式如下：

=FILTER(FILTER(A3:G9,B3:B9=J2),A2:G2=K2)

公式解析：

❶ 先用第一个 Filter 函数 （FILTER(A3:G9,B3:B9=J2)） 筛选出【A3:G9】单元格的符合条件的全部数据区域。

如下图：

❷ 然后在这个结果的基础上，再进一步筛选： =FILTER(❶,A2:G2=K2)

第二参数的条件设置为：【A2:G2】单元格的内容如果等于【K2】单元格的内容，就返回结果 TRUE，其他返回 FALSE。

如果结果为 TRUE 的就显示所在的那一列全部内容。

其他返回 FALSE 的，将不显示。

此时【K2】单元格内容是下单日期，所以只显示下单日期：

如果，我们再想查看商品名称，就只需要把【K2】单元格的内容更改成商品名称即可。

想看哪个就显示哪个，再也不用一个一个更改引用区域了。

是不是方便的不要不要的呢！

显示指定列内容

在某些工作场景下，需要根据销售明细表，来制作指定格式的客户对账单。

如下图：需要根据上面的销售明细表中的数据，提取其中的【下单日期】、【商品名称】、【规格型号】、【金额】等这四列，其他列则不需要显示出来。

你可能会像下面这样写出公式。

公式如下：

=FILTER(明细!$A$3:$A$9,明细!$B$3:$B$9=B2)

这个就是最基本的用法。

但是每换一列，就需要更改单元格区域。这里有四列，那就需要写四个公式。无法自动化。

如何解决这个问题呢？这里有两种方法。

▋方法一

还是用和上面类似的方法，使用两个 Filter 函数来实现。

公式如下：

=FILTER(FILTER(明细!$A$3:$G$9,明细!$B$3:$B$9=$B$2),明细!$A$2:$G$2=A3)

公式解析：

❶ 第一个 FILTER(明细!$A$3:$G$9,明细!$B$3:$B$9=$B$2)；

显示全部符合条件的数据。

❷ 第二个 FILTER(❶,明细!$A$2:$G$2=A3)；

在第一步的基础上，将明细表中的 A2:G2 单元格中的等于 A3 的内容显示出来。

这里的与上面的原理相同。只是把【A3】变成了可以变动的单元格，然后向右拖动就可以显示相对应的数据了。

一直拖动到【D4】单元格。

这样所有列就全部显示出来了！

▋方法二

也可以用下面这个公式解决，如下图：

公式如下：

=FILTER(FILTER(明细!$A$3:$G$9,明细!$B$3:$B$9=$B$2),{1,0,1,1,0,0,1})

公式解析：

这里是利用 1 和 0 就可以解决多列显示的问题。

因为在 Excel 中，1 代表 TRUE，0 代表 FALSE。

明细表中，【$A$3:$G$9】一共有 7 列，那么第二个 Filter 的第二参数，也由 7 个 1 和 0 组成。

如果需要显示的列就写 1，不需要显示的列就写 0。

最后就完美解决上面的问题啦！

PS. 一定要记住的是： 第二参数中 1 和 0 之间的分隔符号要用逗号，而不能用分号噢。

如果用分号将返回错误值。

今天，我们介绍了 Filter 函数的两个神秘用法。

❶用两个 Filter 函数实现二维方向查找，突破之前仅仅用于一维方向的查找引用。

❷ 显示指定列内容。利用数字 1 和 0 组成的常量数组，轻松实现想要显示的列内容。这种方法无论是指定列在数据源中的排列顺序是什么，都可以轻松实现。

对了，如果你想更系统地学习 Excel 技巧，提升办公效率！

那就加入《秋叶 Excel 3 天集训营》吧，这门课由拉登Dony老师教学，还配有助教老师答疑！

只需 3 天，你就能获取：

✅ Excel 高效办公技巧

✅ 视频+直播+图文笔记+课后练习

✅ 35 个常用函数说明手册

✅ 超多 Excel 学习资源……

原价 99 元

今天免费领取！

还等什么⚠️

快扫描下图中的二维码抢课吧！

只需下班花 30 分钟，就能学更多 Excel 干货！

关注【秋叶 Excel】 ，发送【6】

领取秋叶 Excel 6 年精选文章合集