比Vlookup好用10倍，它才是Excel查找函数No.1
职场
职场
2024-11-12 11:31
湖北
点击蓝字【秋叶 Excel】👆
![]()
本文作者：明镜在心
本文编辑：卫星酱
在 Office 365 和 Excel 2021 及以上版本中，有一个非常好用的函数——Filter。
它基本上可以代替之前在函数界称霸武林十几年之久的万金油函数组合（Index+Small+If+Row）。
我们在之前的文章中（👉点这里），也介绍过该函数的基本用法和它的厉害之处。
但是，今天要介绍的这两个神秘用法，一般人还真不一定知道！
有点好奇？那就赶紧上车，跟我一起来看看吧！
在之前我们介绍该函数的用法中，一般都是在一维方向实现单条件或者多条件查找引用。如下图，是一张销售明细表，需要查找客户名称为：AA 的商品名称有多少种。在 B 列的客户名称中，如果有与客户名称 AA 相同的，就返回显示 C 列中对应的商品名称。如果需要查看下单日期，那我们就必须把公式的第一参数引用区域更改成 A 列。那么再想查看规格型号，就要再次更改第一参数的引用区域。
那么如何才能在一个二维的区域中，快速查找并显示我们想要的数据，而不需要每次更改公式的引用区域呢？公式如下：=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 的就显示所在的那一列全部内容。此时【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】变成了可以变动的单元格，然后向右拖动就可以显示相对应的数据了。公式如下：=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 天集训营》吧，这门课由我教学，还配有助教老师答疑！
只需 3 天，你就能获取：
✅ Excel 高效办公技巧
✅ 视频+直播+图文笔记+课后练习
✅ 35 个常用函数说明手册
✅ 超多 Excel 学习资源……
原价 99 元
今天免费领取！
还等什么⚠️
快扫描下图中的二维码抢课吧！
👇
只需下班花 30 分钟，就能学更多 Excel 干货！![]()
