尧图网页设计 
Excel函数•补短工具箱②官方不给自己手搓一个 XFILTER为 FILTER 插上新翅膀新增条件列 多值清单查询WPS 通用办公技巧你是否遇到过这样的场景用 FILTER 函数做条件筛选明明结果能出来可一旦遇到“多个城市任意命中”“筛完还想把命中原因一起展示”这类需求却又得绕回辅助列和 VLOOKUPFILTER 确实是 Excel 365 和 WPS 新版本里的王牌动态数组函数但把它放到真实业务中你会发现官方函数并不“全能”。比如筛选条件不能直接用中间计算列、多个值清单查询时要拼接逻辑、老版本和部分 WPS 环境根本不支持 FILTER……这些短板不是靠“等官方更新”就能解决的。这篇文章我会带着你亲手做一个“加强版筛选方案”——XFILTER。它不依赖某个高版本专属神秘 API而是用现有的 INDEX、SMALL、IF、MATCH、ISNUMBER、LET、TEXTJOIN 等函数组合而成既能在旧版 Excel 和常见 WPS 环境中运行又能在新版本中发挥更强大的能力。全文会覆盖兼容旧版的基础筛选、多条件筛选、新增条件列输出、多值清单查询四个应用层次并给出可直接复制套用的公式模板。如果你经常处理“同一张表反复按不同条件抽取数据”这类工作这篇文章会非常实用。阅读时建议打开 Excel 或 WPS 跟着操作效果远比只看不练好得多。1. FILTER 函数的概念、优势与使用现状1.1 FILTER 是什么FILTER 是 Excel 365 推出的一类动态数组函数它的核心作用是用一个条件数组去筛选另一个数据区域。和传统 VLOOKUP 只能返回单列匹配结果不同FILTER 可以一次性返回多行多列结果还会自动溢出到相邻单元格无需提前拖拽公式。FILTER 的基本语法是FILTER(array, include, [if_empty])参数含义是否必填array要筛选的数据区域必填include逻辑数组返回 TRUE 对应的行必填if_empty无结果时返回的值选填比如有一份员工数据A 列是姓名B 列是部门要筛出“销售部”的所有员工FILTER(A2:A10, B2:B10销售部, 无匹配人员)结果会自动从公式所在单元格向下填充把销售部所有姓名列出来。1.2 FILTER 解决了哪些历史问题在 FILTER 出现前我们要实现“条件筛选后返回多列结果”通常依靠 INDEX SMALL IF 数组公式或者数据透视表再或者添加一堆辅助列。FILTER 的出现大幅简化了这类需求尤其是配合 LET、SORT、UNIQUE 等函数可以让整张报表的数据清洗逻辑变得非常清晰。举一个常用场景销售明细表里希望把“华东大区且订单金额大于 5000”的订单一次抽出来。FILTER(A2:E100, (C2:C100华东大区)*(D2:D1005000), 暂无数据)这里用乘法*表示“并且”关系两个条件同时成立时结果为 1再转化为 TRUE筛选就生效了。1.3 FILTER 的三个局限性虽然 FILTER 很好用但在日常办公和复杂业务场景中它存在几个明显的短板第一低版本兼容问题。Excel 2019 及更早版本、部分旧版 WPS 并不支持 FILTER。即使你的同事用的是新版 WPS也可能因为公司统一安装的是精简版而无法使用。这个问题很现实尤其是跨部门协作时公式要“大家都能跑”才有意义。第二条件列无法动态生成。FILTER 的 include 参数只能引用已有列官方没有提供一个入口让你把“中间判断结果列”直接输出到结果区域。比如你想在筛选结果旁边显示“该行匹配了哪个城市”单靠 FILTER 是做不到的必须额外再来一段公式。第三多值清单查询需要组合其他函数。FILTER 本身接受的是 TRUE/FALSE 数组所以“多值任意匹配”需要手动扩展条件。比如“城市属于北京、上海、广州中的一个”你可能会写成FILTER(A2:E10, (D2:D10北京)(D2:D10上海)(D2:D10广州), 无数据)如果清单有 10 个值条件就要写 10 遍既繁琐又容易出错。正是因为这三个短板我决定把 FILTER 拆开手搓一个更适合实际业务使用的 XFILTER 方案。2. 环境准备与版本说明2.1 环境要求本文所有公式均适用于 Excel 和 WPS。但因为不同版本支持函数范围不同我先说明版本差异环境支持 FILTER支持 LET支持 LAMBDA数组公式输入方式Excel 365支持支持支持直接回车Excel 2021支持支持不支持直接回车Excel 2019不支持不支持不支持CtrlShiftEnterWPS 最新版视版本而定部分支持不支持大多自动溢出WPS 旧版不支持不支持不支持CtrlShiftEnter版本需要根据你的实际环境调整。如果你不确定自己的 Excel 或 WPS 是否支持 FILTER可以直接在单元格里输入FILTER(如果出现函数参数提示就表示可用如果提示“函数无效”那就表示当前环境不支持。2.2 示例数据准备为了统一演示效果下面所有案例都使用同一张“员工表”。A 列到 E 列的数据如下ABCDE姓名部门岗位城市薪资张三销售部销售专员北京8500李四销售部销售经理上海12000王五市场部市场专员广州7000赵六销售部销售专员深圳7800钱七市场部市场经理北京9800孙八销售部大客户经理上海15000周九行政部行政助理广州5200吴十销售部销售专员成都6900郑十一市场部市场专员武汉6500数据范围是A2:E10即从“张三”那一行到“郑十一”那一行表头在第 1 行。下面会围绕这张表演示四类不同复杂度的筛选公式。3. 不依赖 FILTER 的 XFILTER 基础版3.1 核心思路INDEX SMALL IF在 FILTER 不可用的环境中最经典的筛选提取公式是INDEX(返回区域, SMALL(IF(条件, ROW(数据范围)-起始行1, 4^8), ROW(A1)))这个公式的套路可以拆解为四个步骤ROW(数据范围)-起始行1把数据相对行号算出来。比如数据从第 2 行开始第二行的相对行号就是 1。IF(条件, 相对行号, 4^8)满足条件的行返回它的相对行号不满足的返回一个很大的数字 65536。SMALL(..., ROW(A1))依次取出第 1 小、第 2 小的行号。配合下拉公式就能逐条提取符合条件的行号。INDEX(返回区域, 行号)根据行号从目标列取回内容。4^8的含义是 4 的 8 次方等于 65536。这是旧版 Excel 的最大行数作为“找不到”时的占位值后面配合 IFERROR 隐藏错误。3.2 单条件 XFILTER按部门筛选姓名现在要筛选 B 列部门等于“销售部”的所有姓名公式如下IFERROR(INDEX(A$2:A$10, SMALL(IF($B$2:$B$10销售部, ROW($A$2:$A$10)-1, 4^8), ROW(A1))), )操作步骤在 G2 单元格输入上面的公式。旧版 Excel 按CtrlShiftEnter结束新版 Excel 或 WPS 如果支持动态数组直接回车。向下拖动填充公式到 G2:G10。预期结果张三 李四 赵六 孙八 吴十因为销售部有 5 人多余单元格会显示为空字符串。3.3 多条件 XFILTER部门加薪资同时满足需求变成同时满足“部门销售部”且“薪资8000”。公式修改为IFERROR(INDEX(A$2:A$10, SMALL(IF(($B$2:$B$10销售部)*($E$2:$E$108000), ROW($A$2:$A$10)-1, 4^8), ROW(A1))), )这里用*连接两个条件表示“并且”。预期结果为张三 李四 孙八赵六薪资 7800吴十薪资 6900虽然部门是销售部但薪资条件不满足所以不会被提取出来。3.4 多列返回一个公式提取整行数据上面的公式每次只返回一列如果希望把符合条件的整行都提取出来需要把公式向右填充并改变 INDEX 的第一参数区域。在 G2 单元格输入姓名提取公式然后在 H2 到 K2 分别输入部门、岗位、城市、薪资的提取公式IFERROR(INDEX(B$2:B$10, SMALL(IF(($B$2:$B$10销售部)*($E$2:$E$108000), ROW($A$2:$A$10)-1, 4^8), ROW(A1))), )注意这里 INDEX 的第一参数改成了B$2:B$10而 SMALL 中的 ROW 范围仍然对应原始数据的行号范围$A$2:$A$10。列方向使用相对引用B$2向下填充时列固定向右填充时列自动变化。如果你希望结果直接以动态数组形式输出并且确认自己的 Excel 支持 LET可以把公式写成更具可读性的 LET 版本LET( 数据范围, $A$2:$E$10, 条件行号, IF(($B$2:$B$10销售部)*($E$2:$E$108000), ROW($A$2:$A$10)-1, 4^8), 提取第N个, ROW(A1), IFERROR(INDEX(数据范围, SMALL(条件行号, 提取第N个), COLUMN(A1)), ) )这个版本的思路和前面一致只是用 LET 把中间结果命名公式更容易维护。如果你的环境不支持 LET使用基础版本即可。4. XFILTER 增强版新增条件列让筛选结果自带“命中原因”4.1 什么是新增条件列在日常业务中有时候我们不只希望看到“哪些行被选中”还希望知道“这一行是因为什么被选中的”。举例来说用城市清单筛选员工后我们希望结果旁边多出一列“命中城市”。当某个员工同时属于多个筛选清单值时这一列能直观地显示到底命中了哪个值。这种“把中间判断结果变成结果列”的操作就是新增条件列。4.2 方法一辅助列方式推荐通用性最强在数据表旁添加一列 F 列“命中城市”F2 单元格输入IFERROR(INDEX($H$2:$H$4, MATCH(1, ($D2$H$2:$H$4)*1, 0)), )假设 H2:H4 是筛选城市清单内容为H北京上海广州这个公式会判断 D2 单元格的城市是否在 H2:H4 中返回第一个匹配的城市。然后在 G2 输入筛选结果FILTER(A2:F10, F2:F10, 无匹配数据)这样结果区域会包含原始数据列 A 到 E再加上 F 列的命中城市。辅助列这种方式逻辑简单对新手友好而且 FILTER 的 include 参数可以直接引用辅助列不需要写复杂条件。缺点是会修改原表结构如果不想改动原始数据可以看下面的内存数组法。4.3 方法二内存数组方式不改原表如果不想在原表加辅助列可以借助 CHOOSE 函数把命中城市“拼”进结果区域。FILTER( CHOOSE({1,2,3,4,5,6}, A2:A10, B2:B10, C2:C10, D2:D10, E2:E10, IFERROR(INDEX($H$2:$H$4, MATCH(1, ($D2:$D10$H$2:$H$4)*1, 0)), ) ), ISNUMBER(MATCH(D2:D10, $H$2:$H$4, 0)), 无匹配数据 )这里 CHOOSE 把 A 列、B 列、C 列、D 列、E 列以及动态计算的“命中城市”列组合成一个 9 行 6 列的新数组再交给 FILTER 筛选。这个公式的关键点在于MATCH(1, ($D2:$D10$H$2:$H$4)*1, 0)会返回每个员工城市在清单中的位置。INDEX($H$2:$H$4, 匹配位置)取回具体的城市名称。IFERROR把不匹配的行显示为空。需要注意这种方式对公式理解要求较高而且如果行列方向写错容易返回乱结果。对于大多数办公场景我更推荐辅助列方式。4.4 多值命中合并展示TEXTJOIN 进阶如果筛选清单有多个值一个员工可能同时命中多个值比如“城市列”同时包含北京和上海或者希望把所有命中的筛选词用逗号合并显示可以使用 TEXTJOIN 与 IF 组合。在辅助列 F2 输入TEXTJOIN(,, TRUE, IF(ISNUMBER(MATCH($H$2:$H$4, D2, 0)), $H$2:$H$4, ))这个公式的思路是依次检查 H2:H4 中的每一个城市是否等于 D2命中的城市用 TEXTJOIN 拼接起来。仍然用辅助列配合 FILTERFILTER(A2:F10, F2:F10, 无匹配数据)这样结果中的“命中城市”列会显示出类似“北京,上海”的效果。TEXTJOIN 是目前文本合并最灵活的函数如果你的 WPS 版本不支持 TEXTJOIN可以用 SUBSTITUTE 和 TRIM 做替代但公式会变长这里不再展开。5. XFILTER 进阶版多值清单查询筛选条件不再是一个值5.1 多值清单查询的典型业务场景“多值清单查询”是指筛选条件不是单个固定值而是一组可变化的值。典型场景包括从员工表中筛选“北京、上海、广州”三座城市的员工。从订单表中筛选“A类、B类、C类”商品。从流水表中筛选“今天、昨天、前天”三天产生的记录。如果只用 FILTER条件会写成FILTER(A2:E10, (D2:D10北京)(D2:D10上海)(D2:D10广州), 无数据)当清单只有 3 项时还能接受但如果清单有 20 项公式就会变得难以维护。5.2 用 COUNTIF 实现多值匹配COUNTIF 的经典写法是COUNTIF(清单区域, 数据列)0筛选员工表中“城市属于 H2:H4 清单”的员工FILTER(A2:E10, COUNTIF($H$2:$H$4, D2:D10)0, 无匹配数据)COUNTIF 第一个参数是清单区域第二个参数是数据列 D2:D10。当某行的城市存在于清单中COUNTIF 返回 1条件结果为 TRUE。这个写法比用拼接条件简洁得多并且清单区域可以随时增删公式不需要改动。5.3 用 MATCH ISNUMBER 实现多值匹配MATCH ISNUMBER 是另一种主流写法FILTER(A2:E10, ISNUMBER(MATCH(D2:D10, $H$2:$H$4, 0)), 无匹配数据)MATCH 会返回每个城市在清单中的位置存在则返回数字不存在返回 #N/A。ISNUMBER 把数字转成 TRUE把错误转成 FALSE。两种写法的区别函数组合适用场景注意点COUNTIF(清单, 数据)0数据列是单元格区域时最直观当清单出现重复值时结果也容易核对ISNUMBER(MATCH(数据, 清单, 0))数据列和清单都是数组时更规范查询值必须是精确匹配实际项目中两者都常见关键看团队习惯。我更推荐 MATCH ISNUMBER因为 MATCH 本身是精确匹配工具性能更好逻辑也更清晰。5.4 多值清单查询的结果自动扩展对于支持动态数组的 Excel 365 和 WPS 新版上面的公式会动态扩展FILTER(A2:E10, ISNUMBER(MATCH(D2:D10, $H$2:$H$4, 0)), 无匹配数据)输入后结果会自动显示为多行。只要把 H 列的清单换了结果立即刷新。如果不支持动态数组可以回退到第 3 节的 INDEX SMALL IF 版本并把条件改成多值判断IFERROR(INDEX(A$2:A$10, SMALL(IF(ISNUMBER(MATCH($D$2:$D$10, $H$2:$H$4, 0)), ROW($A$2:$A$10)-1, 4^8), ROW(A1))), )这个公式把 FILTER 的 include 逻辑“翻译”成数组公式适合老环境。5.5 多条件与多值清单组合查询更复杂的业务场景通常是“多值清单”和“其他条件”混合使用。例如筛选“城市属于 H2:H4 清单且薪资大于 8000”的员工。FILTER 版本FILTER(A2:E10, ISNUMBER(MATCH(D2:D10, $H$2:$H$4, 0))*(E2:E108000), 无匹配数据)INDEX SMALL 版本IFERROR(INDEX(A$2:A$10, SMALL(IF(ISNUMBER(MATCH($D$2:$D$10, $H$2:$H$4, 0))*($E$2:$E$108000), ROW($A$2:$A$10)-1, 4^8), ROW(A1))), )预期结果根据示例数据姓名部门岗位城市薪资张三销售部销售专员北京8500李四销售部销售经理上海12000钱七市场部市场经理北京9800孙八销售部大客户经理上海15000王五和周九的城市都是广州虽然城市在清单中但薪资不足 8000因此不会被选中。6. 完整实战把 XFILTER 做成一个可复用的“筛选模板”6.1 制作数据透视式筛选面板这一节我们把前几节的内容整合成一个实战模板。目标是在表格右侧搭建一个“筛选条件区”通过修改条件自动得到筛选结果。操作步骤在 G1 单元格输入“城市清单”。在 G2:G4 输入北京、上海、广州。在 I1 单元格输入“最低薪资”。在 I2 单元格输入 8000。在 A12 单元格输入“筛选结果”然后从 A13 开始写公式。FILTER 版本FILTER(A2:E10, ISNUMBER(MATCH(D2:D10, $G$2:$G$4, 0))*(E2:E10$I$2), 无匹配数据)旧版兼容版本先把结果区域的列头写好然后在 A13 输入IFERROR(INDEX(A$2:A$10, SMALL(IF(ISNUMBER(MATCH($D$2:$D$10, $G$2:$G$4, 0))*($E$2:$E$10$I$2), ROW($A$2:$A$10)-1, 4^8), ROW(A1))), )向右填充到 E 列向下填充 10 行。这就是一个简单的动态筛选模板。修改 G2:G4 的城市清单或 I2 的最低薪资阈值结果会自动更新。6.2 增加命中城市辅助列继续在模板中加入“命中城市”列。在 F13 单元格输入IFERROR(INDEX($G$2:$G$4, MATCH(1, ($D13$G$2:$G$4)*1, 0)), )如果你的 Excel 支持 FILTER可以把筛选区域扩展到 A:FFILTER(A2:F10, ISNUMBER(MATCH(D2:D10, $G$2:$G$4, 0))*(E2:E10$I$2), 无匹配数据)这里 F 列的内容需要提前在 F2:F10 中写好。或者在内存数组中动态生成第 6 列。6.3 运行与验证验证思路如下当城市清单为“北京、上海、广州”最低薪资为 8000结果应为 4 行。把最低薪资改为 0结果会新增“王五、周九”两行。把城市清单清空或删除结果会返回“无匹配数据”。我的建议是每次修改筛选条件后先用肉眼核对 1 到 2 行数据再确认总行数是否合理避免公式逻辑错误导致漏选或错选。7. 常见问题与排查思路7.1 公式显示 #VALUE! 或 #NAME?出现这种错误通常有三个原因问题现象常见原因解决思路公式返回 #NAME?当前 Excel/WPS 版本不支持 FILTER 或 LET改用 INDEXSMALLIF 基础版本公式返回 #VALUE!include 参数的行数与 array 不一致检查条件区域和数据区域是否同范围动态数组结果只有一个值没有按 CtrlShiftEnter或版本不支持溢出新版直接回车老版手动按三键7.2 结果出现 #N/A 或 #NUM!#N/A通常出现在 MATCH 找不到匹配值时。检查多值清单中是否有空格、换行符或数据本省前后有不可见字符。#NUM!通常出现在 INDEX SMALL 公式中是因为 SMALL 试图取出比实际匹配数更大的第 N 小值。外层加上 IFERROR 即可隐藏。7.3 多值清单查询时漏数据排查顺序检查清单区域是否包含隐藏空格。使用 TRIM 清理数据列TRIM(D2)。检查数据类型是否一致。数字与文本型数字可能导致匹配不上。把 COUNTIF 的写法临时改成COUNTIF($G$2:$G$4, D2)看单行是否能匹配。7.4 结果顺序不对INDEX SMALL 版本默认按原表顺序返回。如果希望按某一列排序需要再套用 SORT 函数或者用 SMALL 内部对行号排序。老版本没有 SORT 时只能先按目标列排序原表再筛选。7.5 WPS 不识别动态数组如果 WPS 旧版不支持动态数组输入 FILTER 公式后只会显示一个结果不会自动扩展。解决办法是先选中足够大的空白区域输入公式后按 CtrlShiftEnter。或者改用第 3 节的 INDEX SMALL IF 公式下拉填充。8. 最佳实践与工程建议8.1 控制计算范围FILTER 和数组公式都会对整个引用区域进行计算。如果把条件写成整列引用比如D:D虽然方便但会拖慢表格计算速度。建议把数据区域精确写成D2:D1000这类范围既覆盖数据量又避免无意义的大范围计算。8.2 用命名区域提升可读性在多表联动场景中可以把清单区域定义为名称例如城市清单 Sheet1!$G$2:$G$4然后公式写成FILTER(A2:E10, ISNUMBER(MATCH(D2:D10, 城市清单, 0)), 无匹配数据)这样公式语义更清晰别人接手时一眼就能看出筛选条件是什么。8.3 辅助列并非坏实践很多人追求“一个公式完成所有事”非要用内存数组代替辅助列。但在多人协作的 Excel/WPS 表格中辅助列反而更利于核对、过滤和排查。我的建议是如果是个人临时分析可以用内存数组。如果是长期维护的业务报表优先使用辅助列 FILTER 方案。辅助列可以隐藏在视图之外不影响数据阅读。8.4 数据备份与验证优先凡是涉及筛选、汇总、更新的公式都建议先在副本中测试。特别是在生产报表中修改公式时先复制一份原表验证筛选结果的行数和明细无误后再覆盖正式区域。筛选逻辑出错不会直接破坏原数据但会误导管理决策严重的会导致后续统计错误。8.5 配合其他函数构建完整方案XFILTER 只是条件筛选的一环。实际办公中它通常与以下函数配合使用函数用途UNIQUE提取清单中的唯一值SORT / SORTBY对筛选结果排序TEXTJOIN合并多个命中值MAXIFS / MINIFS在筛选结果中求最大最小值SUMIFS / COUNTIFS对筛选结果做条件汇总IFERROR隐藏错误值提升输出美观度例如统计“销售部且薪资大于 8000”的最高薪资MAX(IF(($B$2:$B$10销售部)*($E$2:$E$108000), $E$2:$E$10, ))老版本按下 CtrlShiftEnter。这里仍然是一个数组公式和 XFILTER 的原理一致。8.6 注意版本差异团队统一标准在公司或团队共享表格时优先使用大家都能运行的公式版本。如果团队中有同事使用旧版 WPS就不要在核心报表里用 FILTER、LET、TEXTJOIN 等新函数。可以制定一份“团队公式兼容性清单”把允许使用的函数范围固定下来。9. 总结与学习路线本文围绕 FILTER 的不足一步一步搭建了一个 XFILTER 方案。你至少掌握了四种能力在旧版 Excel/WPS 中用 INDEX SMALL IF 手写基础筛选公式用 FILTER COUNTIF/MATCH ISNUMBER 实现多值清单查询用辅助列或 CHOOSE 内存数组在筛选结果中新增“命中条件列”把以上逻辑组合成一个可复用、可调整的动态筛选模板。一个更重要的收获是官方函数不足时不要急着等待新版本组合现有函数往往也能解决问题。数组公式、动态数组、文本合并这些基础函数组合起来能力远超大部分人的想象。下一步可以继续学习的方向用 LET 重构长公式提升可读性用 LAMBDA 把 XFILTER 真正封装成一个“自定义函数”用 SORT、UNIQUE 完善筛选结果结合 SUMIFS、COUNTIFS 构建“筛选 统计”一体的报表。如果你做的是销售报表、人事花名册或库存台账这类条件筛选和条件汇总能力几乎每天都会用到。建议你拿一份自己平时处理最多的表格试着用本文介绍的四种方式做一遍遇到报错时对照常见问题表逐项排查。公式只有自己动手写过一遍才真正属于自己。如果本文对你有帮助欢迎收藏备用也欢迎在评论区分享你遇到的 FILTER 使用难题一起交流更高效的表格解决方案。 Excel函数补短：手搓XFILTER，为FILTER添加条件列与多值查询
Excel函数•补短工具箱②官方不给自己手搓一个 XFILTER为 FILTER 插上新翅膀新增条件列 多值清单查询WPS 通用办公技巧你是否遇到过这样的场景用 FILTER 函数做条件筛选明明结果能出来可一旦遇到“多个城市任意命中”“筛完还想把命中原因一起展示”这类需求却又得绕回辅助列和 VLOOKUPFILTER 确实是 Excel 365 和 WPS 新版本里的王牌动态数组函数但把它放到真实业务中你会发现官方函数并不“全能”。比如筛选条件不能直接用中间计算列、多个值清单查询时要拼接逻辑、老版本和部分 WPS 环境根本不支持 FILTER……这些短板不是靠“等官方更新”就能解决的。这篇文章我会带着你亲手做一个“加强版筛选方案”——XFILTER。它不依赖某个高版本专属神秘 API而是用现有的 INDEX、SMALL、IF、MATCH、ISNUMBER、LET、TEXTJOIN 等函数组合而成既能在旧版 Excel 和常见 WPS 环境中运行又能在新版本中发挥更强大的能力。全文会覆盖兼容旧版的基础筛选、多条件筛选、新增条件列输出、多值清单查询四个应用层次并给出可直接复制套用的公式模板。如果你经常处理“同一张表反复按不同条件抽取数据”这类工作这篇文章会非常实用。阅读时建议打开 Excel 或 WPS 跟着操作效果远比只看不练好得多。1. FILTER 函数的概念、优势与使用现状1.1 FILTER 是什么FILTER 是 Excel 365 推出的一类动态数组函数它的核心作用是用一个条件数组去筛选另一个数据区域。和传统 VLOOKUP 只能返回单列匹配结果不同FILTER 可以一次性返回多行多列结果还会自动溢出到相邻单元格无需提前拖拽公式。FILTER 的基本语法是FILTER(array, include, [if_empty])参数含义是否必填array要筛选的数据区域必填include逻辑数组返回 TRUE 对应的行必填if_empty无结果时返回的值选填比如有一份员工数据A 列是姓名B 列是部门要筛出“销售部”的所有员工FILTER(A2:A10, B2:B10销售部, 无匹配人员)结果会自动从公式所在单元格向下填充把销售部所有姓名列出来。1.2 FILTER 解决了哪些历史问题在 FILTER 出现前我们要实现“条件筛选后返回多列结果”通常依靠 INDEX SMALL IF 数组公式或者数据透视表再或者添加一堆辅助列。FILTER 的出现大幅简化了这类需求尤其是配合 LET、SORT、UNIQUE 等函数可以让整张报表的数据清洗逻辑变得非常清晰。举一个常用场景销售明细表里希望把“华东大区且订单金额大于 5000”的订单一次抽出来。FILTER(A2:E100, (C2:C100华东大区)*(D2:D1005000), 暂无数据)这里用乘法*表示“并且”关系两个条件同时成立时结果为 1再转化为 TRUE筛选就生效了。1.3 FILTER 的三个局限性虽然 FILTER 很好用但在日常办公和复杂业务场景中它存在几个明显的短板第一低版本兼容问题。Excel 2019 及更早版本、部分旧版 WPS 并不支持 FILTER。即使你的同事用的是新版 WPS也可能因为公司统一安装的是精简版而无法使用。这个问题很现实尤其是跨部门协作时公式要“大家都能跑”才有意义。第二条件列无法动态生成。FILTER 的 include 参数只能引用已有列官方没有提供一个入口让你把“中间判断结果列”直接输出到结果区域。比如你想在筛选结果旁边显示“该行匹配了哪个城市”单靠 FILTER 是做不到的必须额外再来一段公式。第三多值清单查询需要组合其他函数。FILTER 本身接受的是 TRUE/FALSE 数组所以“多值任意匹配”需要手动扩展条件。比如“城市属于北京、上海、广州中的一个”你可能会写成FILTER(A2:E10, (D2:D10北京)(D2:D10上海)(D2:D10广州), 无数据)如果清单有 10 个值条件就要写 10 遍既繁琐又容易出错。正是因为这三个短板我决定把 FILTER 拆开手搓一个更适合实际业务使用的 XFILTER 方案。2. 环境准备与版本说明2.1 环境要求本文所有公式均适用于 Excel 和 WPS。但因为不同版本支持函数范围不同我先说明版本差异环境支持 FILTER支持 LET支持 LAMBDA数组公式输入方式Excel 365支持支持支持直接回车Excel 2021支持支持不支持直接回车Excel 2019不支持不支持不支持CtrlShiftEnterWPS 最新版视版本而定部分支持不支持大多自动溢出WPS 旧版不支持不支持不支持CtrlShiftEnter版本需要根据你的实际环境调整。如果你不确定自己的 Excel 或 WPS 是否支持 FILTER可以直接在单元格里输入FILTER(如果出现函数参数提示就表示可用如果提示“函数无效”那就表示当前环境不支持。2.2 示例数据准备为了统一演示效果下面所有案例都使用同一张“员工表”。A 列到 E 列的数据如下ABCDE姓名部门岗位城市薪资张三销售部销售专员北京8500李四销售部销售经理上海12000王五市场部市场专员广州7000赵六销售部销售专员深圳7800钱七市场部市场经理北京9800孙八销售部大客户经理上海15000周九行政部行政助理广州5200吴十销售部销售专员成都6900郑十一市场部市场专员武汉6500数据范围是A2:E10即从“张三”那一行到“郑十一”那一行表头在第 1 行。下面会围绕这张表演示四类不同复杂度的筛选公式。3. 不依赖 FILTER 的 XFILTER 基础版3.1 核心思路INDEX SMALL IF在 FILTER 不可用的环境中最经典的筛选提取公式是INDEX(返回区域, SMALL(IF(条件, ROW(数据范围)-起始行1, 4^8), ROW(A1)))这个公式的套路可以拆解为四个步骤ROW(数据范围)-起始行1把数据相对行号算出来。比如数据从第 2 行开始第二行的相对行号就是 1。IF(条件, 相对行号, 4^8)满足条件的行返回它的相对行号不满足的返回一个很大的数字 65536。SMALL(..., ROW(A1))依次取出第 1 小、第 2 小的行号。配合下拉公式就能逐条提取符合条件的行号。INDEX(返回区域, 行号)根据行号从目标列取回内容。4^8的含义是 4 的 8 次方等于 65536。这是旧版 Excel 的最大行数作为“找不到”时的占位值后面配合 IFERROR 隐藏错误。3.2 单条件 XFILTER按部门筛选姓名现在要筛选 B 列部门等于“销售部”的所有姓名公式如下IFERROR(INDEX(A$2:A$10, SMALL(IF($B$2:$B$10销售部, ROW($A$2:$A$10)-1, 4^8), ROW(A1))), )操作步骤在 G2 单元格输入上面的公式。旧版 Excel 按CtrlShiftEnter结束新版 Excel 或 WPS 如果支持动态数组直接回车。向下拖动填充公式到 G2:G10。预期结果张三 李四 赵六 孙八 吴十因为销售部有 5 人多余单元格会显示为空字符串。3.3 多条件 XFILTER部门加薪资同时满足需求变成同时满足“部门销售部”且“薪资8000”。公式修改为IFERROR(INDEX(A$2:A$10, SMALL(IF(($B$2:$B$10销售部)*($E$2:$E$108000), ROW($A$2:$A$10)-1, 4^8), ROW(A1))), )这里用*连接两个条件表示“并且”。预期结果为张三 李四 孙八赵六薪资 7800吴十薪资 6900虽然部门是销售部但薪资条件不满足所以不会被提取出来。3.4 多列返回一个公式提取整行数据上面的公式每次只返回一列如果希望把符合条件的整行都提取出来需要把公式向右填充并改变 INDEX 的第一参数区域。在 G2 单元格输入姓名提取公式然后在 H2 到 K2 分别输入部门、岗位、城市、薪资的提取公式IFERROR(INDEX(B$2:B$10, SMALL(IF(($B$2:$B$10销售部)*($E$2:$E$108000), ROW($A$2:$A$10)-1, 4^8), ROW(A1))), )注意这里 INDEX 的第一参数改成了B$2:B$10而 SMALL 中的 ROW 范围仍然对应原始数据的行号范围$A$2:$A$10。列方向使用相对引用B$2向下填充时列固定向右填充时列自动变化。如果你希望结果直接以动态数组形式输出并且确认自己的 Excel 支持 LET可以把公式写成更具可读性的 LET 版本LET( 数据范围, $A$2:$E$10, 条件行号, IF(($B$2:$B$10销售部)*($E$2:$E$108000), ROW($A$2:$A$10)-1, 4^8), 提取第N个, ROW(A1), IFERROR(INDEX(数据范围, SMALL(条件行号, 提取第N个), COLUMN(A1)), ) )这个版本的思路和前面一致只是用 LET 把中间结果命名公式更容易维护。如果你的环境不支持 LET使用基础版本即可。4. XFILTER 增强版新增条件列让筛选结果自带“命中原因”4.1 什么是新增条件列在日常业务中有时候我们不只希望看到“哪些行被选中”还希望知道“这一行是因为什么被选中的”。举例来说用城市清单筛选员工后我们希望结果旁边多出一列“命中城市”。当某个员工同时属于多个筛选清单值时这一列能直观地显示到底命中了哪个值。这种“把中间判断结果变成结果列”的操作就是新增条件列。4.2 方法一辅助列方式推荐通用性最强在数据表旁添加一列 F 列“命中城市”F2 单元格输入IFERROR(INDEX($H$2:$H$4, MATCH(1, ($D2$H$2:$H$4)*1, 0)), )假设 H2:H4 是筛选城市清单内容为H北京上海广州这个公式会判断 D2 单元格的城市是否在 H2:H4 中返回第一个匹配的城市。然后在 G2 输入筛选结果FILTER(A2:F10, F2:F10, 无匹配数据)这样结果区域会包含原始数据列 A 到 E再加上 F 列的命中城市。辅助列这种方式逻辑简单对新手友好而且 FILTER 的 include 参数可以直接引用辅助列不需要写复杂条件。缺点是会修改原表结构如果不想改动原始数据可以看下面的内存数组法。4.3 方法二内存数组方式不改原表如果不想在原表加辅助列可以借助 CHOOSE 函数把命中城市“拼”进结果区域。FILTER( CHOOSE({1,2,3,4,5,6}, A2:A10, B2:B10, C2:C10, D2:D10, E2:E10, IFERROR(INDEX($H$2:$H$4, MATCH(1, ($D2:$D10$H$2:$H$4)*1, 0)), ) ), ISNUMBER(MATCH(D2:D10, $H$2:$H$4, 0)), 无匹配数据 )这里 CHOOSE 把 A 列、B 列、C 列、D 列、E 列以及动态计算的“命中城市”列组合成一个 9 行 6 列的新数组再交给 FILTER 筛选。这个公式的关键点在于MATCH(1, ($D2:$D10$H$2:$H$4)*1, 0)会返回每个员工城市在清单中的位置。INDEX($H$2:$H$4, 匹配位置)取回具体的城市名称。IFERROR把不匹配的行显示为空。需要注意这种方式对公式理解要求较高而且如果行列方向写错容易返回乱结果。对于大多数办公场景我更推荐辅助列方式。4.4 多值命中合并展示TEXTJOIN 进阶如果筛选清单有多个值一个员工可能同时命中多个值比如“城市列”同时包含北京和上海或者希望把所有命中的筛选词用逗号合并显示可以使用 TEXTJOIN 与 IF 组合。在辅助列 F2 输入TEXTJOIN(,, TRUE, IF(ISNUMBER(MATCH($H$2:$H$4, D2, 0)), $H$2:$H$4, ))这个公式的思路是依次检查 H2:H4 中的每一个城市是否等于 D2命中的城市用 TEXTJOIN 拼接起来。仍然用辅助列配合 FILTERFILTER(A2:F10, F2:F10, 无匹配数据)这样结果中的“命中城市”列会显示出类似“北京,上海”的效果。TEXTJOIN 是目前文本合并最灵活的函数如果你的 WPS 版本不支持 TEXTJOIN可以用 SUBSTITUTE 和 TRIM 做替代但公式会变长这里不再展开。5. XFILTER 进阶版多值清单查询筛选条件不再是一个值5.1 多值清单查询的典型业务场景“多值清单查询”是指筛选条件不是单个固定值而是一组可变化的值。典型场景包括从员工表中筛选“北京、上海、广州”三座城市的员工。从订单表中筛选“A类、B类、C类”商品。从流水表中筛选“今天、昨天、前天”三天产生的记录。如果只用 FILTER条件会写成FILTER(A2:E10, (D2:D10北京)(D2:D10上海)(D2:D10广州), 无数据)当清单只有 3 项时还能接受但如果清单有 20 项公式就会变得难以维护。5.2 用 COUNTIF 实现多值匹配COUNTIF 的经典写法是COUNTIF(清单区域, 数据列)0筛选员工表中“城市属于 H2:H4 清单”的员工FILTER(A2:E10, COUNTIF($H$2:$H$4, D2:D10)0, 无匹配数据)COUNTIF 第一个参数是清单区域第二个参数是数据列 D2:D10。当某行的城市存在于清单中COUNTIF 返回 1条件结果为 TRUE。这个写法比用拼接条件简洁得多并且清单区域可以随时增删公式不需要改动。5.3 用 MATCH ISNUMBER 实现多值匹配MATCH ISNUMBER 是另一种主流写法FILTER(A2:E10, ISNUMBER(MATCH(D2:D10, $H$2:$H$4, 0)), 无匹配数据)MATCH 会返回每个城市在清单中的位置存在则返回数字不存在返回 #N/A。ISNUMBER 把数字转成 TRUE把错误转成 FALSE。两种写法的区别函数组合适用场景注意点COUNTIF(清单, 数据)0数据列是单元格区域时最直观当清单出现重复值时结果也容易核对ISNUMBER(MATCH(数据, 清单, 0))数据列和清单都是数组时更规范查询值必须是精确匹配实际项目中两者都常见关键看团队习惯。我更推荐 MATCH ISNUMBER因为 MATCH 本身是精确匹配工具性能更好逻辑也更清晰。5.4 多值清单查询的结果自动扩展对于支持动态数组的 Excel 365 和 WPS 新版上面的公式会动态扩展FILTER(A2:E10, ISNUMBER(MATCH(D2:D10, $H$2:$H$4, 0)), 无匹配数据)输入后结果会自动显示为多行。只要把 H 列的清单换了结果立即刷新。如果不支持动态数组可以回退到第 3 节的 INDEX SMALL IF 版本并把条件改成多值判断IFERROR(INDEX(A$2:A$10, SMALL(IF(ISNUMBER(MATCH($D$2:$D$10, $H$2:$H$4, 0)), ROW($A$2:$A$10)-1, 4^8), ROW(A1))), )这个公式把 FILTER 的 include 逻辑“翻译”成数组公式适合老环境。5.5 多条件与多值清单组合查询更复杂的业务场景通常是“多值清单”和“其他条件”混合使用。例如筛选“城市属于 H2:H4 清单且薪资大于 8000”的员工。FILTER 版本FILTER(A2:E10, ISNUMBER(MATCH(D2:D10, $H$2:$H$4, 0))*(E2:E108000), 无匹配数据)INDEX SMALL 版本IFERROR(INDEX(A$2:A$10, SMALL(IF(ISNUMBER(MATCH($D$2:$D$10, $H$2:$H$4, 0))*($E$2:$E$108000), ROW($A$2:$A$10)-1, 4^8), ROW(A1))), )预期结果根据示例数据姓名部门岗位城市薪资张三销售部销售专员北京8500李四销售部销售经理上海12000钱七市场部市场经理北京9800孙八销售部大客户经理上海15000王五和周九的城市都是广州虽然城市在清单中但薪资不足 8000因此不会被选中。6. 完整实战把 XFILTER 做成一个可复用的“筛选模板”6.1 制作数据透视式筛选面板这一节我们把前几节的内容整合成一个实战模板。目标是在表格右侧搭建一个“筛选条件区”通过修改条件自动得到筛选结果。操作步骤在 G1 单元格输入“城市清单”。在 G2:G4 输入北京、上海、广州。在 I1 单元格输入“最低薪资”。在 I2 单元格输入 8000。在 A12 单元格输入“筛选结果”然后从 A13 开始写公式。FILTER 版本FILTER(A2:E10, ISNUMBER(MATCH(D2:D10, $G$2:$G$4, 0))*(E2:E10$I$2), 无匹配数据)旧版兼容版本先把结果区域的列头写好然后在 A13 输入IFERROR(INDEX(A$2:A$10, SMALL(IF(ISNUMBER(MATCH($D$2:$D$10, $G$2:$G$4, 0))*($E$2:$E$10$I$2), ROW($A$2:$A$10)-1, 4^8), ROW(A1))), )向右填充到 E 列向下填充 10 行。这就是一个简单的动态筛选模板。修改 G2:G4 的城市清单或 I2 的最低薪资阈值结果会自动更新。6.2 增加命中城市辅助列继续在模板中加入“命中城市”列。在 F13 单元格输入IFERROR(INDEX($G$2:$G$4, MATCH(1, ($D13$G$2:$G$4)*1, 0)), )如果你的 Excel 支持 FILTER可以把筛选区域扩展到 A:FFILTER(A2:F10, ISNUMBER(MATCH(D2:D10, $G$2:$G$4, 0))*(E2:E10$I$2), 无匹配数据)这里 F 列的内容需要提前在 F2:F10 中写好。或者在内存数组中动态生成第 6 列。6.3 运行与验证验证思路如下当城市清单为“北京、上海、广州”最低薪资为 8000结果应为 4 行。把最低薪资改为 0结果会新增“王五、周九”两行。把城市清单清空或删除结果会返回“无匹配数据”。我的建议是每次修改筛选条件后先用肉眼核对 1 到 2 行数据再确认总行数是否合理避免公式逻辑错误导致漏选或错选。7. 常见问题与排查思路7.1 公式显示 #VALUE! 或 #NAME?出现这种错误通常有三个原因问题现象常见原因解决思路公式返回 #NAME?当前 Excel/WPS 版本不支持 FILTER 或 LET改用 INDEXSMALLIF 基础版本公式返回 #VALUE!include 参数的行数与 array 不一致检查条件区域和数据区域是否同范围动态数组结果只有一个值没有按 CtrlShiftEnter或版本不支持溢出新版直接回车老版手动按三键7.2 结果出现 #N/A 或 #NUM!#N/A通常出现在 MATCH 找不到匹配值时。检查多值清单中是否有空格、换行符或数据本省前后有不可见字符。#NUM!通常出现在 INDEX SMALL 公式中是因为 SMALL 试图取出比实际匹配数更大的第 N 小值。外层加上 IFERROR 即可隐藏。7.3 多值清单查询时漏数据排查顺序检查清单区域是否包含隐藏空格。使用 TRIM 清理数据列TRIM(D2)。检查数据类型是否一致。数字与文本型数字可能导致匹配不上。把 COUNTIF 的写法临时改成COUNTIF($G$2:$G$4, D2)看单行是否能匹配。7.4 结果顺序不对INDEX SMALL 版本默认按原表顺序返回。如果希望按某一列排序需要再套用 SORT 函数或者用 SMALL 内部对行号排序。老版本没有 SORT 时只能先按目标列排序原表再筛选。7.5 WPS 不识别动态数组如果 WPS 旧版不支持动态数组输入 FILTER 公式后只会显示一个结果不会自动扩展。解决办法是先选中足够大的空白区域输入公式后按 CtrlShiftEnter。或者改用第 3 节的 INDEX SMALL IF 公式下拉填充。8. 最佳实践与工程建议8.1 控制计算范围FILTER 和数组公式都会对整个引用区域进行计算。如果把条件写成整列引用比如D:D虽然方便但会拖慢表格计算速度。建议把数据区域精确写成D2:D1000这类范围既覆盖数据量又避免无意义的大范围计算。8.2 用命名区域提升可读性在多表联动场景中可以把清单区域定义为名称例如城市清单 Sheet1!$G$2:$G$4然后公式写成FILTER(A2:E10, ISNUMBER(MATCH(D2:D10, 城市清单, 0)), 无匹配数据)这样公式语义更清晰别人接手时一眼就能看出筛选条件是什么。8.3 辅助列并非坏实践很多人追求“一个公式完成所有事”非要用内存数组代替辅助列。但在多人协作的 Excel/WPS 表格中辅助列反而更利于核对、过滤和排查。我的建议是如果是个人临时分析可以用内存数组。如果是长期维护的业务报表优先使用辅助列 FILTER 方案。辅助列可以隐藏在视图之外不影响数据阅读。8.4 数据备份与验证优先凡是涉及筛选、汇总、更新的公式都建议先在副本中测试。特别是在生产报表中修改公式时先复制一份原表验证筛选结果的行数和明细无误后再覆盖正式区域。筛选逻辑出错不会直接破坏原数据但会误导管理决策严重的会导致后续统计错误。8.5 配合其他函数构建完整方案XFILTER 只是条件筛选的一环。实际办公中它通常与以下函数配合使用函数用途UNIQUE提取清单中的唯一值SORT / SORTBY对筛选结果排序TEXTJOIN合并多个命中值MAXIFS / MINIFS在筛选结果中求最大最小值SUMIFS / COUNTIFS对筛选结果做条件汇总IFERROR隐藏错误值提升输出美观度例如统计“销售部且薪资大于 8000”的最高薪资MAX(IF(($B$2:$B$10销售部)*($E$2:$E$108000), $E$2:$E$10, ))老版本按下 CtrlShiftEnter。这里仍然是一个数组公式和 XFILTER 的原理一致。8.6 注意版本差异团队统一标准在公司或团队共享表格时优先使用大家都能运行的公式版本。如果团队中有同事使用旧版 WPS就不要在核心报表里用 FILTER、LET、TEXTJOIN 等新函数。可以制定一份“团队公式兼容性清单”把允许使用的函数范围固定下来。9. 总结与学习路线本文围绕 FILTER 的不足一步一步搭建了一个 XFILTER 方案。你至少掌握了四种能力在旧版 Excel/WPS 中用 INDEX SMALL IF 手写基础筛选公式用 FILTER COUNTIF/MATCH ISNUMBER 实现多值清单查询用辅助列或 CHOOSE 内存数组在筛选结果中新增“命中条件列”把以上逻辑组合成一个可复用、可调整的动态筛选模板。一个更重要的收获是官方函数不足时不要急着等待新版本组合现有函数往往也能解决问题。数组公式、动态数组、文本合并这些基础函数组合起来能力远超大部分人的想象。下一步可以继续学习的方向用 LET 重构长公式提升可读性用 LAMBDA 把 XFILTER 真正封装成一个“自定义函数”用 SORT、UNIQUE 完善筛选结果结合 SUMIFS、COUNTIFS 构建“筛选 统计”一体的报表。如果你做的是销售报表、人事花名册或库存台账这类条件筛选和条件汇总能力几乎每天都会用到。建议你拿一份自己平时处理最多的表格试着用本文介绍的四种方式做一遍遇到报错时对照常见问题表逐项排查。公式只有自己动手写过一遍才真正属于自己。如果本文对你有帮助欢迎收藏备用也欢迎在评论区分享你遇到的 FILTER 使用难题一起交流更高效的表格解决方案。