企业官网建设流程全解析

很多用 FILTER 的同学都遇到过这样的场景：同事甩来一张一万行的销售明细，让你把“北京、上海、广州”三个城市的订单全部筛出来，还要在结果后面打上“是否大额”的标记。你打开公式栏，第一反应是写一个官方 FILTER：

=FILTER(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 表格里最重要的动态数组函数之一。它的核心能力是“按条件筛选”，并且结果会自动扩展，不用按 Ctrl+Shift+Enter，也不用下拉填充。基本语法是这样的：

=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：名称管理器 + LAMBDA

4.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 里，我们可以把这种组合做成更清晰的几个参数。重新定义一个XFLT2：

=LAMBDA(数据区域, 条件区域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 节准备的订单表。为了便于理解，整理一下本系列目前定义的三个自定义函数：

下面四个需求会把这几个函数串起来。

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