别再VLOOKUP了!Excel新函数FILTER,一个公式搞定所有筛选

"领导让我把销售部绩效A级的人员名单拉出来，我捣鼓了半小时……"

这是上周一个学员的吐槽。她的表格有2000多行，要按部门+绩效双重筛选，还要提取特定列，以前只能用高级筛选或者VLOOKUP+IFERROR嵌套，写得自己都看不懂。

其实，Excel 365早就有了一个神器级别的筛选函数——FILTER。今天一次给你讲透。

一、FILTER是谁？

FILTER 是 Excel 365/2021 推出的动态数组函数，意思就是：你写好公式，结果会自动"溢出"到旁边的单元格，不用拖拽、不用Ctrl+Shift+Enter。

=FILTER(要筛选的区域, 筛选条件, [没找到时显示什么])

就这么简单。

二、先从最简单的开始

场景：把"销售部"所有人的信息找出来

假设你的数据长这样：

姓名      部门      岗位      销售额

张三     销售部    经理      85万

李四     技术部    工程师   48万

王五     销售部    主管      92万

赵六     销售部    专员      63万

公式只需要一行：

=FILTER(A1:D100, B1:B100="销售部")

结果自动显示所有销售部人员的信息，源数据变了，结果也跟着变，完全动态。

这还只是开胃菜。

三、多条件筛选：AND 和 OR

场景：销售部且销售额≥80万（AND）

=FILTER(A1:D100, (B1:B100="销售部")*(D1:D100>=800000))

规律：多个条件用 * 连接，表示"并且"。

场景：销售部或技术部（OR）

=FILTER(A1:D100, (B1:B100="销售部")+(B1:B100="技术部"))

规律：多个条件用 + 连接，表示"或者"。

场景：模糊查找 — 姓"王"的员工

=FILTER(A1:D100,LEFT(A1:A100,1)="王")

或者用通配符配合 FIND：

=FILTER(A1:D100,ISNUMBER(FIND("王", A1:A100)))

四、进阶：只返回我想要的列

很多时候我们不需要整张表，比如只要 姓名 和 销售额 两列。

方法1：CHOOSECOLS（推荐，版本够新的话）

=CHOOSECOLS(FILTER(A1:D100, B1:B100="销售部"), 1, 4)

意思：先筛选销售部，再从结果里取第1列和第4列。

方法2：INDEX（兼容Excel 2021）

=FILTER(INDEX(A1:D100, SEQUENCE(ROWS(A1:D100)),{1,4}),B1:B100="销售部")

建议直接记方法1，最直观。

五、没人告诉你但巨好用的技巧

技巧1：做一个搜索框

在 H1 单元格输入部门名称，公式自动跟着变：

=FILTER(A1:D100, B1:B100=H1)

搭配数据验证下拉菜单，就是一个动态查询面板，领导看了都说好。

技巧2：处理"找不到"的情况

不加处理时，没找到数据会显示 #CALC!，很丑。

加个友好的提示：

=FILTER(A1:D100, B1:B100="市场部", "暂无匹配数据")

瞬间优雅了。

技巧3：筛选后自动排序

FILTER 的结果还可以直接喂给 SORT 函数：

=SORT(FILTER(A1:D100, B1:B100="销售部"), 4, -1)

筛选出销售部并按销售额降序排列，一步到位。

六、避坑手册

你可能会遇到

#SPILL!    原因：结果被挡住了。

               怎么办：清空公式下面和右边的单元格。

#CALC!    原因：没匹配到数据。

                怎么办：加第三个参数 "无数据"。

#VALUE!原因：条件区域行数不对。

               怎么办：检查条件区域的行数是否和数据区域一致。

#NAME? 原因：你的Excel版本不支持。

              怎么办：检查是不是Excel 365或2021+。

写在最后

FILTER 函数最大的魅力在于：一个公式 = 过去半小时的手动操作。而且它和 SORT、UNIQUE、CHOOSECOLS 这些新函数组合起来，基本可以告别 VLOOKUP 和高级筛选了。

——————

今日作业：打开你的表格，试试用 FILTER 替代你手头最常用的一个筛选操作，评论区告诉我效果如何。

——————

觉得有用？ 点个「在看」分享给同事，一起告别加班 ✌️

——————

本文由 AI 整理，内容仅供学习参考，如有错误欢迎指正。