最新

Excel 怎么用 FILTER 函数做动态筛选

把筛选条件写进公式,源数据一变结果自动刷新,比反复点筛选按钮省事得多。

Excel 的筛选按钮每换一次条件就要重按一遍,条件多了还容易漏勾。FILTER 函数把条件写进公式,源数据一变结果立刻跟着变,适合做长期使用的查询小看板。

入口在哪

  • 需要支持动态数组的版本,Microsoft 365 与 Excel 2021 及以后才有;更早的版本输入后只会得到 #NAME? 错误。
  • 「公式」选项卡的函数库里,「查找与引用」分类下能找到 FILTER。
  • 记不住菜单位置时,直接在编辑栏敲等号和函数名,参数提示会自动弹出,比翻菜单快。

具体步骤

  1. 先确定要输出哪几列,把源数据整理成连续的一块,中间不要留空列,否则返回的列会错位。
  2. 选一个右侧和下方都空着的单元格作为结果左上角,输入等号、FILTER、左括号,然后框选源数据区域。
  3. 第二个参数写条件,例如某一列等于某个单元格;多个条件同时满足用乘号连接,满足任意一个用加号连接,每组条件都用括号包住。
  4. 第三个参数填一句提示文字,没有匹配时显示它;不填则直接返回错误值,看起来像公式写错了。
  5. 回车后结果会自动溢出到相邻单元格,蓝色虚线框住的范围就是结果区,不要再手动往下拉公式。
  6. 把源数据转成超级表,公式改用结构化引用,之后新增的行会自动纳入范围。

几个坑

  • 结果区右侧或下方有内容时会报 #SPILL!,需要把整个溢出范围清空,哪怕只留一个空格也算被占用。合并单元格同样会造成这个报错。
  • 条件区域的行数必须和源数据一致。整列引用虽然能写,但会把末尾大量空白行也当成匹配项,结果里会多出一串空行。
  • 把结果复制到别处粘贴,粘出来的是静态值,源数据更新后不再联动。需要保留联动就别复制,要看历史结果则相反。
  • 文件发给还在用旧版 Excel 的同事,公式会整体变成错误值,最好同时留一份粘贴为值的版本备用。

怎么验证

在源数据最下面加一行符合条件的记录,回到结果区看是否自动多出一行;再改一个条件单元格的值,结果应当立刻重算;最后把条件改成一个不可能成立的值,应显示第三参数那句提示而不是错误码。三个动作都正常,公式才算写对。

常见问题

FILTER 和直接点筛选按钮有什么区别

筛选按钮只是临时隐藏行,换个条件要重新点,也没法同时摆出多组结果。FILTER 是公式,条件放在单元格里,改条件值结果就换,还能把几组条件的结果并排放在一张表上对照。

结果区能不能直接改内容

不能。溢出区域是算出来的,手动改会提示不能更改数组的某一部分。确实要微调,先把结果复制并选择性粘贴为值,再改那份副本。

为什么一直显示 #SPILL!

说明结果要占的位置被占用了。选中提示的单元格,表格会用虚线标出它想占的范围,把那个范围内的内容和格式清掉就好。跨表引用的结果区如果压到别的表也不行。

一句话总结:把筛选条件写成 FILTER 公式,源数据一动结果就变,前提是给结果区留足空白。