自定义XFILTER：用FILTER+MATCH+LAMBDA实现多值筛选与标记列

很多用 FILTER 的同学都遇到过这样的场景同事甩来一张一万行的销售明细让你把“北京、上海、广州”三个城市的订单全部筛出来还要在结果后面打上“是否大额”的标记。你打开公式栏第一反应是写一个官方 FILTERFILTER(A2:E10000, (C2:C10000北京) (C2:C10000上海) (C2:C10000广州), 无匹配)写完之后自己都不想回头再看第二眼。条件一多等号要拼一长串下次换几个城市又得从头改公式更麻烦的是筛选结果里不会自动告诉你“这条为什么被选中”。FILTER 本身确实很强但“多值清单批量查询”和“筛选结果附加状态列”这两个能力官方一直都没有直接给。这篇文章要干一件事在 Excel 和 WPS 里自己封装一个 XFILTER把这两个短板一次性补上。先给结论XFILTER 不是什么神秘函数它的核心组合是FILTER MATCH LAMBDA。它和官方 FILTER 最大的区别是条件值可以直接传一个“清单区域”想筛几个值就框几个值同时可以把判断结果作为一列挂在筛选结果右侧不用再写一堆 IF 去手动拼接。读完这篇文章你会得到一个开箱即用的自定义函数以及一套可复用的“函数封装”思路。1. FILTER 很香但这两个短板必须补1.1 FILTER 官方能做什么FILTER 是 Excel 365 / Excel 2021 以及新版 WPS 表格里最重要的动态数组函数之一。它的核心能力是“按条件筛选”并且结果会自动扩展不用按 CtrlShiftEnter也不用下拉填充。基本语法是这样的FILTER(数据区域, 条件数组, [无匹配时的返回值])第二个参数是很多人理解不透彻的地方。它必须是一个“布尔数组”也就是一组 TRUE 或 FALSE而且这个数组的长度必须和数据区域的行数完全一致。比如FILTER(A2:E100, C2:C100北京, 无匹配)这行公式的意思是把 C 列每一行都和“北京”比较形成{TRUE; FALSE; TRUE; ...}这样的布尔数组然后 FILTER 会把所有 TRUE 对应的行取出来。这个设计本身很优秀它把“筛选”和“条件计算”解耦了。问题也出在这里官方 FILTER 只负责“按布尔数组取数”不负责帮你构造这个布尔数组。一旦条件变复杂构造布尔数组的工作就落到用户身上。1.2 真正让人难受的三个瞬间第一个瞬间是多值筛选。你要筛北京、上海、广州三座城市使用官方 FILTER 就只能在 include 参数里用加号连接等号判断FILTER(A2:E100, (C2:C100北京) (C2:C100上海) (C2:C100广州), 无匹配)城市一旦增加到 8 个、10 个公式长度会爆炸而且很容易漏写一个括号。第二个瞬间是“筛选结果要带状态列”。你想把“北京”筛出来并且希望旁边一起显示“一线城市”这个标记官方 FILTER 不会给你输出这个标记你只能在原表旁边做一列辅助判断再把这个辅助判断列一起选进数据区域。第三个瞬间是条件值放在单元格里按引用区域批量筛选。官方 FILTER 的 include 参数天然不支持整块条件值区域你很难写一个公式完整体现“条件清单有一堆值去数据里做模式匹配”。这三个瞬间共同指向同一个结论FILTER 是一个很底层的函数它把“筛选动作”做得很干净但把“构造条件”的责任全部留给了用户。1.3 这篇文章要给你的判断我写这个系列的核心观点一直是现代 Excel/WPS 公式真正值钱的不是某个冷门函数而是“把常用公式模板化、函数化”的能力。官方不给的筛选能力完全可以通过 LAMBDA MATCH HSTACK 这类零件自己拼出来。与其每次写十几行又臭又长的公式不如花十分钟定义一个 XFILTER然后把它当成普通函数反复使用。2. 核心原理先看懂 FILTER 的 include 参数2.1 FILTER 的语法细节FILTER 的完整语法是FILTER(array, include, [if_empty])array要返回的数据区域。include布尔数组控制每一行是否被保留。if_empty可选参数当没有匹配数据时返回的占位文本。include 的行数必须和 array 的行数一致否则会报#VALUE!或返回意外结果。这是新手最常犯的错误之一。比如数据区域是A2:E100那 include 就必须是C2:C100北京这样长度相同的判断不能写C2:C10。2.2 为什么 include 必须是布尔数组简单来说FILTER 的底层逻辑是“逐行扫描”。它给每一行做一个真/假判断真的留下假的丢掉。所以 include 参数的本质不是“一个条件”而是一整列布尔值。理解了这一点你就知道如何扩展 FILTER只要能生成和原数据行数一致的布尔数组就能做出花式筛选。所以很多 FILTER 的高级用法本质上都是在“生成布尔数组”这个环节做文章。多值清单查询的核心是让“某一行的城市是否属于清单中的任意一个”变成 TRUE/FALSE。2.3 LET 和 LAMBDA把公式变成函数LET 是 Excel 365 里一个关键函数它的作用是在公式内部给中间结果命名避免重复计算也让公式可读性大幅提升。语法是LET(变量名, 变量值, 计算结果)后面要写的 XFILTER 中我们会用 LET 把匹配结果先存起来再交给 FILTER 使用。LAMBDA 是真正实现“自定义函数”的核心。它可以把一段公式封装成一个可复用的函数。我们可以先在“名称管理器”里定义好一段 LAMBDA然后在单元格里直接用新的函数名调用。这段公式不在 VBA 里也不需要启用宏普通 xlsx/xlsx 文件就能保存。2.4 MATCH 是 XFILTER 的核心零件MATCH 在 Excel 里的作用是查找某个值在区域中的位置。基本语法是MATCH(查找值, 查找区域, [匹配方式])第三个参数写 0表示精确匹配。如果查找不到返回#N/A。把 MATCH 的第一个参数从“单个值”换成“一整列”它就会逐行查找返回一个位置数组。再包一层ISNUMBER就把所有位置数字都转成 TRUE所有#N/A都转成 FALSE。这正好就是 FILTER 需要的 include 布尔数组。这段逻辑就是 XFILTER 的发动机ISNUMBER(MATCH(条件区域, 清单区域, 0))3. 环境准备哪些版本能跑通3.1 支持矩阵说明先说一个现实问题Excel/WPS 版本之间函数支持差异很大。FILTER 和动态数组在 Excel 365、Excel 2021、新版 WPS 表格中已经比较普及但 LAMBDA、HSTACK、TEXTSPLIT 这类更晚出现的函数在不同版本里的支持度参差不齐。不能笼统说“新版本 WPS 全部支持”因为实际部署时很多人用的还是旧版 WPS 或绿色精简版。更稳妥的做法是在练习前先用一个最小公式自测确认环境支持哪些函数。3.2 快速检查环境在任意单元格输入以下公式能返回TRUE就说明支持相应的动态数组能力ISNUMBER(MATCH(测试, {测试;示例}, 0))这个公式不依赖 LAMBDA只验证 MATCH 和数组计算是否正常。再测试 LAMBDA 是否可用直接在单元格输入LAMBDA(x, x 1)(1)如果得到2说明当前环境能使用 LAMBDA后面的自定义函数方案就可以落地。如果提示#NAME?说明 LAMBDA 不可用需要升级版本或者使用本文第 6.3 节的“辅助列方案”作为替代。3.3 没有 LAMBDA 时的替代思路如果你的 WPS 版本没有 LAMBDA也不用灰心。辅助列方案永远可用先在原表旁边写一个判断列列出“是否命中”再用官方 FILTER 根据这个辅助列筛选。这个方案唯一缺点是会多一列但在兼容性上是无敌的。后面会专门写清楚。4. 手搓第一个 XFILTER名称管理器 LAMBDA4.1 准备数据与清单区域先准备一张简单的订单表作为后续所有示例的数据源。打开 WPS 表格或 Excel新建一个工作表命名为“订单表”输入以下内容A1: 编号 B1: 日期 C1: 城市 D1: 金额 E1: 负责人 A2: 1001 2024-01-05 北京 8200 张三 A3: 1002 2024-01-06 上海 12600 李四 A4: 1003 2024-01-07 广州 4300 王五 A5: 1004 2024-01-08 深圳 15800 赵六 A6: 1005 2024-01-09 北京 9900 张三 A7: 1006 2024-01-10 上海 6700 李四 A8: 1007 2024-01-11 广州 12100 王五 A9: 1008 2024-01-12 深圳 3100 赵六然后在 F2:F4 单元格分别输入“北京”“上海”“广州”作为多值清单区域。4.2 在“名称管理器”里定义 XFLT这一步是核心操作。点击“公式”选项卡打开“名称管理器”新建一个名称名称填XFLT引用位置粘贴下面的 LAMBDA 公式LAMBDA(数据区域, 条件区域, 条件值, LET( 匹配数组, ISNUMBER(MATCH(条件区域, 条件值, 0)), FILTER(数据区域, 匹配数组, 无匹配数据) ) )名称管理器在 Excel 和 WPS 里的路径基本相同。注意粘贴时不要带“ LAMBDA”之外的多余字符不要把名称和公式写在同一格。定义完成之后关闭名称管理器。4.3 调用 XFLT 完成单值筛选回到工作表在任意空白单元格输入XFLT(A2:E9, C2:C9, 北京)按回车公式会自动扩展出两行结果编号 1001 和 1005 对应的订单。这只是第一步先验证自定义函数能正常工作。接下来继续输入多值清单版本XFLT(A2:E9, C2:C9, F2:F4)结果会把北京、上海、广州三种城市的订单全部筛选出来。这里最关键的变化是第三个参数从“一个文本”变成了“一个区域”而 XFLT 内部通过 MATCH 自动完成了逐行匹配。4.4 这段公式拆解把这个 LAMBDA 拆开看它做了三件事第一ISNUMBER(MATCH(条件区域, 条件值, 0))生成布尔数组。MATCH 会把条件区域的每一行去和条件值区域里的所有值比较匹配成功返回数字匹配失败返回#N/A包一层 ISNUMBER 后得到一组 TRUE/FALSE。第二把布尔数组交给 FILTER 的 include 参数让 FILTER 只保留匹配成功的行。第三用LET把中间结果命名为“匹配数组”避免重复计算也让公式更易读。这个过程的核心在于FILTER 的 include 参数不一定是“直接写死的等号”它可以是任何能输出布尔数组的表达式。这是从“会用 FILTER”到“会扩展 FILTER”的分水岭。5. 实现多值清单查询5.1 传统写法 vs XFLT 写法如果不用自定义函数多值条件通常写成这样FILTER(A2:E9, (C2:C9北京) (C2:C9上海) (C2:C9广州), 无匹配)这种写法有三个痛点一是公式会随着条件数量线性膨胀。条件从 3 个变 8 个公式长度直接翻倍一旦少写一个等号排查成本很高。二是条件值写死在公式里不直观。换一批城市要打开公式逐字修改容易改错。三是它没有把“条件清单”本身变成一个可变项无法快速实现动态下拉切换。用 XFLT 写同样效果XFLT(A2:E9, C2:C9, F2:F4)条件清单直接放在 F2:F4想筛哪些城市改单元格内容即可公式一行都不用动。这才是“多值清单查询”的完整含义把条件从公式中抽离放到工作表的单元格区域里像参数一样供人修改。5.2 逗号分隔清单的升级版有些场景下用户不想特意准备一个清单区域而是希望直接在公式里写“北京,上海,广州”这种文本由函数自己拆开。这就需要用 TEXTSPLIT 函数做文本拆分。再定义一个名称XFLT_TEXT引用位置填写LAMBDA(数据区域, 条件区域, 条件文本, LET( 清单, TEXTSPLIT(条件文本, ,), 匹配数组, ISNUMBER(MATCH(条件区域, 清单, 0)), FILTER(数据区域, 匹配数组, 无匹配数据) ) )用法XFLT_TEXT(A2:E9, C2:C9, 北京,上海,广州)注意这里的分隔符是英文逗号如果你的数据里存在空格比如“北京, 上海”拆分后会出现“ 上海”这种带空格的值导致匹配不上。稳妥的做法是统一不加空格或者在写清单时用TRIM函数预先清理。TEXTSPLIT 在新版 Excel 和部分新版 WPS 中可用。如果不支持就不用这版直接用清单区域方案。5.3 字段组合筛选实际业务里筛选条件往往不止一个。比如既要城市命中清单又要金额大于 8000。官方 FILTER 的 include 参数可以用乘号组合多个布尔数组FILTER(A2:E9, (ISNUMBER(MATCH(C2:C9, F2:F4, 0))) * (D2:D9 8000), 无匹配)在 XFLT 里我们可以把这种组合做成更清晰的几个参数。重新定义一个XFLT2LAMBDA(数据区域, 条件区域1, 条件值1, 条件区域2, 条件值2, LET( 匹配1, ISNUMBER(MATCH(条件区域1, 条件值1, 0)), 匹配2, ISNUMBER(MATCH(条件区域2, 条件值2, 0)), FILTER(数据区域, 匹配1 * 匹配2, 无匹配数据) ) )使用示例XFLT2(A2:E9, C2:C9, F2:F4, D2:D9, 8000)这里的第二个条件值写的是 8000最终效果是城市属于清单且金额大于等于 8000 记录才会被留下。虽然 MATCH 对数值条件也能工作但更直观的是把这个参数设计成“比较式”。你可以根据自己业务需求继续扩展公式封装的价值就在这里把复杂逻辑收敛成几个好理解的参数。6. 新增条件列筛选结果自动打标记6.1 需求拆解“新增条件列”是 XFILTER 的第二个卖点。举个真实场景你筛出了北京、上海、广州的订单但希望结果末尾多一列“标签”显示“一线城市”同时如果金额大于 12000希望在标签里直接标注“大额”。这里的难点是FILTER 只负责筛选数据区域它不会自动生成新的列。想给筛选结果加一列常见做法是先在原表做辅助列再一起进 FILTER 的数据区域。但这个做法会污染原表结构而且每次条件变化都要重新设计辅助列。其实还有一条路先用 FILTER 筛出结果再用 HSTACK 把“结果区域”和“标记列”横向拼接起来。HSTACK 是动态数组函数作用是把多个区域按列拼在一起。比如HSTACK(A2:E9, G2:G9)会把 G 列拼到 A:E 的右边。6.2 定义 XFLT_TAG在名称管理器里再新建一个XFLT_TAG引用位置粘贴LAMBDA(数据区域, 条件区域, 条件值, 标记文字, LET( 匹配数组, ISNUMBER(MATCH(条件区域, 条件值, 0)), 基础结果, FILTER(数据区域, 匹配数组, 无匹配数据), 状态列, FILTER(IF(匹配数组, 标记文字, ), 匹配数组), IF(SUM(--匹配数组) 0, 无匹配数据, HSTACK(基础结果, 状态列)) ) )使用示例XFLT_TAG(A2:E9, C2:C9, F2:F4, 一线城市)运行后每一条北京/上海/广州的订单右侧都会多出一列“一线城市”。如果条件值区域 F2:F4 中没有任何城市命中则整个公式返回“无匹配数据”。这段公式里最关键的是状态列这行FILTER(IF(匹配数组, 标记文字, ), 匹配数组)它先根据匹配数组生成一列标记文字匹配的行填“一线城市”不匹配的行填空字符串然后再用 FILTER 把不匹配的行剔除。这样状态列的行数就和基础结果完全一致拼接之后不会错位。如果环境不支持 HSTACK可以在数据区域后面手动加辅助列用“先判断再筛选”的思路达到同样效果。针对 WPS 旧版本更通用的做法是第 6.3 节的辅助列方案。6.3 没有 HSTACK用辅助列方案如果你的 Excel/WPS 版本不支持 HSTACK或者你不想在自定义函数里处理拼接问题最稳妥的方案是在原始数据右侧专门加一列辅助判断列然后用官方 FILTER 按辅助列筛选。这样操作起来也很直观。假设原始数据在 A2:E9 区域把 G1 单元格写上“是否命中”G2 写公式IF(COUNTIF($F$2:$F$4, C2) 0, 命中, )向下填充到 G9。然后用官方 FILTER 筛选FILTER(A2:E9, G2:G9命中, 无匹配)这里 COUNTIF 的作用是统计当前行的城市在清单区域里出现了几次大于 0 说明命中。辅助列方案完全不依赖 LAMBDA、HSTACK、TEXTSPLIT 这些新函数在所有主流 Excel/WPS 版本里都能运行。它最大的好处是你能看见每一行的判断过程排查问题非常方便。缺点是原表会多出一列辅助内容。6.4 把“辅助判断列”也做成 XFLT 参数辅助列方案虽然通用但多出来的列有时会影响报表美观。如果你想保留 XFLT_TAG 的“自带标记”能力又不想依赖 HSTACK可以把“标记文字”和“标记条件”都拆成参数在数据区域之外并列输出。不过这种方法实际落地时会受版本函数限制我这里更推荐的做法是新版本用 XFLT_TAG老版本根据辅助列方案手动实现。两者并不冲突甚至可以并存。7. 完整案例一张订单表解决 4 种筛选需求7.1 模拟数据与函数清单我们继续使用 4.1 节准备的订单表。为了便于理解整理一下本系列目前定义的三个自定义函数函数名作用适用版本XFLT基础筛选支持清单区域支持 LAMBDA 的版本XFLT_TEXT多值清单查询支持逗号分隔文本支持 LAMBDA TEXTSPLITXFLT_TAG筛选结果自动新增标记列支持 LAMBDA HSTACK下面四个需求会把这几个函数串起来。7.2 需求 1筛选北京订单最简单的情况用基础筛选函数XFLT(A2:E9, C2:C9, 北京)结果只返回城市为北京的两行数据编号 1001 和 1005。7.3 需求 2筛选北京、上海、广州把三个城市写在 F2:F4然后用区域作为条件值XFLT(A2:E9, C2:C9, F2:F4)结果返回三座城市的全部订单深圳的订单不会出现。这个需求最直观地体现了“多值清单查询”的威力。7.4 需求 3筛选并新增“一线城市”标记需要标记列时使用 XFL