FILTER：按条件把行整条捞出来

筛选这件事，Excel 老用户第一反应一定是点表头上的小箭头，或者按 Ctrl+Shift+L 打开自动筛选。这套操作用着顺手，但有三个绕不过去的问题：一是筛选只是把不符合的行藏起来，你没法把结果直接拿去继续算，除非再复制粘贴一遍；二是源数据变了，筛选状态不会自动跟着变，每次都要重新点；三是想同时满足“华东区且金额大于一万”这种两个以上的条件，还得在自定义筛选里一层层填，做完之后别人看不出你到底筛了什么。FILTER 就是把这三件事一次性解决的：它返回的是一个真正的数组，可以直接当作任何函数的参数，条件写在公式里一目了然，源数据变动时结果即时刷新。

更关键的是 FILTER 会把整条记录都捞出来。传统做法里你想把“张伟的全部订单”单独列一张表，得先筛、再复制、再粘贴，列宽格式还得重调；现在只需要在目标单元格写一条公式，选中区域的哪一列要、哪一列不要，都由参数决定，而且列的顺序还能重排。过去要做一张“按条件动态变动的报表”，要么用透视表，要么用 VBA，现在一条公式就够。本篇所有区域同样按“第 1 行表头、数据第 2 行到第 500 行”写，你替换成自己的区域即可。

一、语法：三个参数与条件怎么写

FILTER 的语法比 UNIQUE 还简单，只有三个参数，写在下面的代码块里。

FILTER(array, include, [if_empty])

第一个参数 array 是你想返回的区域，可以是一列，也可以是 A2:F500 这样的多列矩形，返回的列数和列顺序完全由它决定。第二个参数 include 是判断条件，必须是一个“高度或宽度与 array 一致”的布尔数组，也就是一堆 TRUE 和 FALSE 组成的数组，TRUE 对应的行会被留下。第三个参数 if_empty 是找不到符合条件的行时显示什么，省略的话会返回 #CALC!，建议永远写上，比如写成“无匹配数据”或者 0，报表会好看很多。

第二个参数是新手最容易卡住的地方，因为它必须和 array 的行数一致。假设要筛 A2:F500 这个六列区域，条件是“E 列销售员等于张伟”，那么 include 就要写成 E2:E500="张伟"，长度刚好是 499 行，和 array 的 499 行对上。如果你不小心写成 E:E="张伟"，长度是一百多万行，Excel 会直接报错。反过来说，如果你只返回一列，比如 =FILTER(B2:B500, E2:E500="张伟")，那就要求两个区域行数相同，都是 499 行，这一点务必检查。

多条件用乘号和加号连接，这里的规则要记牢：乘号表示“并且”，加号表示“或者”。写成 =FILTER(A2:F500, (C2:C500="华东")*(D2:D500>10000)) 就是华东区且金额大于一万；写成 =FILTER(A2:F500, (C2:C500="华东")+(C2:C500="华南")) 就是华东或华南。原理是 TRUE 在运算时等于 1、FALSE 等于 0，相乘只有两个都是 1 才得 1，相加只要有一个是 1 就非零。如果想表达“非”，用 1-条件 或者 <>，比如 =FILTER(A2:F500, (C2:C500<>"华东"))。

理解布尔数组这件事，是跨过动态数组门槛的关键一步。传统公式里我们也写 =IF(C2="华东",1,0)，但那是对单个单元格的判断；在 FILTER 里，条件是对一整列同时做的判断，一次产出几百个 TRUE 和 FALSE，然后由 FILTER 拿着这串结果去“挑行”。想验证自己写的条件对不对，可以选中公式里的条件部分按 F9，Excel 会把它临时算出来显示成一串 TRUE 和 FALSE，检查无误后按 Esc 退出。这个调试技巧在写复杂条件时能省下大量猜测时间，强烈建议掌握。

二、五个能直接抄的写法

下面这张表把最常见的五种筛选需求列了出来，公式可以直接复制，改区域和条件值就能用。建议把第二行和第三行背下来，它们出现的频率最高。

第四行里的 CHOOSECOLS 是个很实用的搭档，它可以在不改动原表的前提下重新排列列顺序。比如 =FILTER(CHOOSECOLS(A2:F500,2,4,6), E2:E500="张伟") 只返回客户、金额、日期三列，而且顺序变成客户在前、金额居中、日期最后。如果只想挑列不筛选，直接用 =CHOOSECOLS(A2:F500,2,4,6) 即可，不必再套 FILTER。老版本没有 CHOOSECOLS 时，可以改用 =FILTER(INDEX(A2:F500,0,2), ...) 这种按列号取的方式，效果相同但一次只能取一列。

第五行的思路值得展开：FILTER 负责“选出哪些行”，SORT 负责“这些行怎么排”，两者职责分明、可以任意组合。同理，FILTER 外面套 UNIQUE 就是“筛选后去重”，里面套 COUNTIFS 就是“按出现次数筛选”，这类由内向外的嵌套是动态数组最核心的写法。想把这个组合用得更熟，可以配合 SORT 和 SORTBY：排序交给公式 一起看。

三、真实场景：动态明细表与条件汇总

第一个场景是做一张“随时可切换的销售员明细表”。在 H1 放一个下拉菜单，选项是全部销售员的名字（这个名单可以用 =UNIQUE(E2:E500) 生成），然后在 A4 写 =FILTER(A2:F500, E2:E500=H1, "该销售员暂无订单")。之后只要切换 H1 的下拉选项，下面的明细表就整片换掉，列数、行数全部自动，不需要任何手工操作。这比透视表更灵活，因为结果区域旁边还能继续写自己的公式，格式也完全自由。

第二个场景是异常数据排查。财务月结时要找出所有“金额大于五万但没有审批单号”的记录，写成 =FILTER(A2:F500, (D2:D500>50000)*(G2:G500=""), "本月无异常")，结果会列出全部嫌疑行。把这条公式放在一张单独的“异常清单”表里，每个月打开文件就能看到最新情况，比每次手工筛选靠谱得多。如果还要按金额从大到小看，就在外面再套一层排序函数，一条公式解决排查和排序两件事。

第三个场景是给其他函数喂数据。比如要算“华东区金额大于一万的订单的平均金额”，不必先筛出来再算，可以直接写 =AVERAGE(FILTER(D2:D500, (C2:C500="华东")*(D2:D500>10000)))。这种写法的好处是中间结果不落地，工作表上看不到任何辅助列，也不用担心忘了刷新。过去这类需求只能用 AVERAGEIFS 解决，而 FILTER 的写法胜在条件可以任意复杂，甚至可以引用别的公式结果作为条件。

第四个场景是按关键字模糊筛选。FILTER 本身不支持通配符，但可以配合 ISNUMBER 和 SEARCH 实现：=FILTER(A2:F500, ISNUMBER(SEARCH("加急", B2:B500)), "无") 会找出 B 列包含“加急”两个字的所有行。SEARCH 不区分大小写，找到就返回位置数字、找不到就报错，所以外面套一层 ISNUMBER 把结果转成 TRUE 和 FALSE。想区分大小写就把 SEARCH 换成 FIND。这个套路在做备注、摘要类的模糊查询时用得非常多。

第五个场景是做交互式看板的底层数据。很多看板需要“选一个大区、下面自动列出该大区所有门店的关键指标”，用 FILTER 配上切片器式的下拉菜单就能实现，而且不需要透视表。做法是把 H1 做成大区下拉，然后写 =SORT(FILTER(CHOOSECOLS(A2:F500,2,4,6), C2:C500=H1, "无数据"), 2, -1)，一次完成筛选、挑列、排序三件事。这类看板过去要靠 VBA 或者复杂的辅助表，现在一条公式加一个数据验证就够，维护成本下降一个数量级。

第六个场景是跨表汇总时先过滤再合并。如果各分公司的明细表结构相同，可以用 =VSTACK(FILTER(北京!A2:F500, 北京!D2:D500>0), FILTER(上海!A2:F500, 上海!D2:D500>0)) 把两张表里有效的行纵向堆在一起，再对合并后的结果做透视或者统计。过去这类需求要在 Power Query 里做追加查询，现在公式里就能完成，适合分公司数量不多、结构稳定的情况。

四、翻车提醒

第一，条件区域和返回区域行数不一致是最常见的报错。Excel 会直接提示“数组大小不匹配”或者返回 #VALUE!。检查办法很简单：分别看两个区域的首尾行号是否一致，比如 A2:F500 是 499 行，条件就必须也是从第 2 行到第 500 行。最省心的预防办法是把源数据转成超级表，然后用结构化引用，行数自动对齐。

第二，空值判断要小心。判断“审批单号为空”时写 G2:G500="" 只能抓到真正的空单元格和公式返回的空字符串，抓不到只有一个空格的单元格。如果数据是从系统导出的，稳妥写法是 TRIM(G2:G500)=""，先去掉首尾空格再判断。反过来，判断非空时写 <>"" 也要留意那些看起来空、其实是空格的单元格。

第三，if_empty 参数省略会返回 #CALC!。很多人在做模板时忘了写第三个参数，结果某个月没有符合条件的数据，整张报表就变成一片错误值，看着像文件坏了。养成习惯：永远写第三个参数，内容可以是“无匹配数据”、0 或者空字符串，具体看下游公式能不能处理。

第四，不要在条件里直接写整列。和上一节同理，E:E="张伟" 会生成一百多万行的布尔数组，既慢又容易触发大小不匹配的报错。要么写死成 E2:E500，要么用超级表的结构化引用。

第五，结果会覆盖下方单元格吗？不会。FILTER 的结果需要多少行就溢出多少行，如果下方已经有内容，公式会返回 #SPILL! 而不是覆盖它。这个设计是安全的，但也意味着你不能把 FILTER 放在一张已经排满的表中间。解决办法是把结果单独放一张工作表，或者在下方预留足够空白。

第六，日期条件要用 DATE 函数或者引用单元格，不要直接写文本。写成 =FILTER(A2:F500, F2:F500>=DATE(2026,1,1), "无") 是安全的，写成 =FILTER(A2:F500, F2:F500>="2026-01-01") 在不同区域设置下可能解析失败。最稳妥的做法是把起止日期放在两个单元格里，公式引用它们，这样改日期也不用改公式。

五、版本要求与老版本替代

FILTER 和 UNIQUE 一样，需要 Microsoft 365 订阅版或者 Excel 2021 及以后版本。Excel 2019 和更早的版本没有这个函数，公式会显示为 #NAME?，曾经有预览版把它放在 Office 预览体验计划里，但正式发布只面向 365 订阅用户，后来才并入 Excel 2021。共享文件前最好确认一下协作方的版本，尤其是发给外部客户时。

老版本要实现“按条件把行整条捞出来”，有三条路。第一条是自动筛选加复制粘贴，最简单但完全不自动，适合一次性工作。第二条是高级筛选，在“数据”选项卡里可以设置条件区域，还能勾选“将结果复制到其他位置”，支持多条件并且、或者，缺点是每次都要重新执行，而且条件区域要单独维护。第三条是数组公式，用 INDEX 加 SMALL 加 IF 的经典组合，写成 =IFERROR(INDEX(B$2:B$500, SMALL(IF($E$2:$E$500="张伟", ROW($E$2:$E$500)-ROW($E$2)+1), ROW(A1))), "")，按 Ctrl+Shift+Enter 输入后向下填充。这条公式能用，但每列都要写一遍、还要预先拖够行数，维护成本极高，属于没有新函数时的权宜之计。

还有一条折中路线是 Power Query。它的筛选操作会被记录成步骤，刷新即可重跑，而且能处理几十万行数据，比工作表公式更稳。缺点是结果要加载回工作表或者数据模型，不是所见即所得的公式，改动逻辑需要打开查询编辑器。对于每月固定跑一次的报表，Power Query 往往比 FILTER 更合适；对于需要随时改条件、随时看到结果的分析场景，FILTER 更快。

如果你的筛选结果还要继续做去重，建议接着读 UNIQUE：去重不用再点删除重复项；如果需要把筛选出来的某几列再按关键字拆分，可以看 TEXTSPLIT 和 TEXTJOIN：拆得开也合得上。这两个组合几乎是日常报表里最常出现的搭配。

最后一个值得掌握的组合是“先查后筛”。有时候条件值本身也要从别的表里查出来，比如先根据工号查出这个人的所属大区，再用这个大区去筛明细，可以写成 =FILTER(A2:F500, C2:C500=XLOOKUP(H1, 员工表!A:A, 员工表!C:C, ""), "无")。由内向外读，XLOOKUP 先算出大区名称，FILTER 再拿它当条件。这种把两个函数串成一条流水线的写法，是动态数组时代最典型的公式风格，也是本书第 2 章 XLOOKUP：一次写对的全能查找 要讲的核心能力。

常见问题

FILTER 和高级筛选、透视表该怎么选？

三者的定位不一样。高级筛选适合一次性的、条件写在单元格区域里的操作，结果不自动更新；透视表适合要分组汇总、要看合计和小计的场景，交互性好但格式受限；FILTER 适合“结果要继续参与计算、条件要写在公式里、源数据会持续变化”的场景。简单判断：要汇总数字用透视表，要拿到原始明细行继续处理就用 FILTER。如果既要明细又要汇总，通常是 FILTER 出明细，外面再用 SUM 或 COUNT 给一个合计。

FILTER 返回 #CALC! 是怎么回事？

绝大多数情况是没有写第三个参数，也就是 if_empty。当没有任何一行满足你的条件时，FILTER 不知道该显示什么，就会返回 #CALC!。解决办法是补上第三个参数，比如 =FILTER(A2:F500, E2:E500="张伟", "无匹配数据")。还有一种可能是条件区域里全是错误值，比如源数据的某一列里有 #N/A，此时连布尔判断都会跟着出错，需要先用 IFERROR 把源数据清洗一遍。

条件里能不能用通配符，比如找出所有以“上海”开头的客户？

FILTER 的条件参数不支持通配符，星号和问号会被当成普通字符。要实现模糊匹配，用 =FILTER(A2:F500, LEFT(B2:B500,2)="上海", "无") 表示以什么开头，或者用 =FILTER(A2:F500, ISNUMBER(SEARCH("上海", B2:B500)), "无") 表示包含。SEARCH 不区分大小写，需要区分大小写时换成 FIND。如果要排除某个关键词，在前面加 NOT 或者写成 ISNUMBER(SEARCH("上海",B2:B500))=FALSE。

我想按“最近 7 天”筛选，公式该怎么写？

把条件写成日期比较即可，最稳妥的做法是用 TODAY 动态计算：=FILTER(A2:F500, (F2:F500>=TODAY()-7)*(F2:F500<=TODAY()), "最近七天无数据")。不要直接把日期写成文本，因为不同区域设置下文本日期可能被解析成别的日期甚至解析失败。如果只想按固定区间筛选，把起止日期放在两个单元格（比如 H1 和 H2），条件写成 (F2:F500>=$H$1)*(F2:F500<=$H$2)，改日期时不用改公式。

能不能只返回满足条件的前 5 行？

可以，在 FILTER 外面套一层取行函数。=TAKE(FILTER(A2:F500, D2:D500>10000, "无"), 5) 会返回符合条件的前五行；如果想返回金额最大的五行，先排序再取：=TAKE(SORT(FILTER(A2:F500, D2:D500>10000), 4, -1), 5)。TAKE 的第二个参数写负数表示从末尾取，比如 -5 就是最后五行。这个组合在做“排行榜”“前十大客户”这类报表时极其好用。

多条件里同时有并且和或者，括号怎么加？

按运算优先级，乘号和加号混合时先算乘号后算加号，所以“华东区且（金额大于一万或者大于五万的加急单）”要写成 (C2:C500="华东")*((D2:D500>10000)+(G2:G500="加急"))，里层的或者用括号包起来。判断写对了没有，可以先把条件部分单独放进一个空区域测试，看返回的 TRUE 和 FALSE 分布是否符合预期，确认无误后再塞回 FILTER。宁可多加括号也不要省，这是写复杂条件时最省时间的习惯。