企业官网建设流程全解析

FILTER 函数上线之后，很多人终于不用再靠辅助列、自动筛选和 VLOOKUP 组合来“假装”做动态查询了。一个=FILTER(数据区, 条件, "无匹配")就能把整表结果直接吐出来。但真正做报表时很快会撞到 FILTER 的硬伤：条件必须写死在公式里。想从一张人员清单里任选几个销售员，再叠加一个区域清单，公式马上就变成一串=、+、*的叠加，写的人费劲，看的人更费劲。

这篇文章要说的是怎么基于 LAMBDA 函数在名称管理器里“手搓”一个自定义函数 XFILTER，给 FILTER 补上两个能力：一是支持“多值清单查询”，把条件放进单元格区域，查询结果跟着清单自动变；二是支持“新增条件列”，把第二列、第三列的条件清单以 AND 逻辑叠加上去。最终你会得到类似=XFILTER(A2:E9, B2:B9, F2:F4, C2:C9, H2:H3)的用法。整个过程在 WPS 表格和 Excel 365 中基本一致，适合经常做动态筛选报表、对账、清单匹配的办公场景。

1. 先想清楚 FILTER 到底缺什么

1.1 FILTER 的基本能力和标准写法

FILTER 的语法是FILTER(数组, 包括, [为空时])。三个参数的含义分别是：要过滤的数据区域、与数据区域行数一致的条件判断、没有匹配结果时返回的内容。

最基础的场景是单值等于判断。例如有一张销售明细表放在 A1:E9，表头是序号、销售员、区域、产品、销售额，要把“张三”的全部记录提取出来：

=FILTER(A2:E9, B2:B9="张三", "无匹配数据")

这个公式能正常工作，但它的 include 参数是一个写死的布尔数组。一旦查询条件变成“张三、李四、王五三个人”，公式就变成了这样：

=FILTER(A2:E9, (B2:B9="张三")+(B2:B9="李四")+(B2:B9="王五"), "无匹配数据")

如果再加一个区域条件“华东、华南”，条件和条件之间还要继续用括号、乘号、加号拼下去。这种写法有几个现实问题：公式不可读，条件不可变，非技术同事根本不敢碰。

1.2 FILTER 的三个短板

第一个短板是条件写死。查询条件如果每天都变，就得每天改公式，而不是改一个单元格里的值。第二个短板是多值清单没有原生支持。FILTER 的 include 参数里没法直接写“这个列的值要命中某一清单区域里的任意一个值”。第三个短板是多条件列组合时公式失控。两个条件列乘起来还能看，三个、四个条件列再各自带清单，公式长度会迅速膨胀，排查一个括号错误都可能花半小时。

这三个短板实际上指向同一个需求：把 FILTER 的查询条件“外置”到单元格区域，让条件变成用户可修改的参数。这就是 XFILTER 要解决的核心问题。

2. 核心思路：用 MATCH + ISNUMBER 把“命中清单”变成条件列

2.1 MATCH 的数组化用法

在使用 XFILTER 之前，先理解一个关键组合：ISNUMBER(MATCH(条件列, 清单, 0))。

MATCH 的作用是查找某个值在另一个区域中的位置。当把“条件列”的整列区域传给 MATCH 的第一个参数时，它在动态数组环境中会逐行计算，对每个单元格返回一个位置数字；如果没找到，就返回#N/A。用 ISNUMBER 包一层后，#N/A变成 FALSE，命中清单的值变成 TRUE。

通俗地说，这个组合的意思就是：判断条件列里的每一行，是否出现在清单区域中。这正好实现了 FILTER 原本没有的“多值清单查询”。

2.2 一个条件列的查询公式

假设销售明细在 A2:E9，销售员在 B2:B9，清单区域是 F2:F4，里面放着张三、李四、王五。最小查询公式可以这样写：

=FILTER(A2:E9, ISNUMBER(MATCH(B2:B9, F2:F4, 0)), "无匹配数据")

运行结果会把 B 列命中清单的所有行整体返回。这里有两个使用前提需要特别注意：

• 条件列 B2:B9 必须和过滤数据区域 A2:E9 行数一致。
• 清单区域 F2:F4 必须是单行或单列，MATCH 的第二个参数不接受多行多列区域。

2.3 多个条件列：用乘号实现 AND 逻辑

当条件从一个变成两个时，需要同时满足“销售员在清单中”和“区域在另一个清单中”。布尔值在公式运算里等价于 0 和 1，因此两个布尔数组相乘就是 AND 逻辑，结果只有两个条件都为 TRUE 时才返回 1。

=FILTER(A2:E9, ISNUMBER(MATCH(B2:B9, F2:F4, 0)) * ISNUMBER(MATCH(C2:C9, H2:H3, 0)), "无匹配数据")

其中 F2:F4 是销售员清单，H2:H3 是区域清单。这个公式已经能完成“多值清单 + 多条件列”的核心查询。缺点也很明显：它是一次性公式，换一个数据范围就要重新编辑，无法像函数一样复用。

2.4 为什么不直接用 COUNTIF

有经验的人可能知道COUNTIF(清单, 条件列)>0也能实现同样的判断。实际使用中两种写法都能出结果，但 MATCH + ISNUMBER 更稳定。COUNTIF 在进行通配符匹配时，会把条件列中的*、?当作通配符处理，如果数据里碰巧有星号或问号，结果会出乎意料。MATCH 的第三个参数写成 0，做的是精确匹配，没有这个隐患。从可读性看，ISNUMBER(MATCH(...))也更直接地表达了“是否命中”的语义。

3. 在名称管理器中手搓 XFILTER

3.1 LAMBDA 如何把公式变成函数

LAMBDA 允许你在公式里定义参数和计算逻辑，例如：

=LAMBDA(数量, 单价, 数量*单价)(10, 5)

前面的 LAMBDA 部分声明了两个参数，后面的(10, 5)是实际传入参数，整个公式返回 50。LAMBDA 单独写在单元格里意义不大，它真正的价值是放进名称管理器，变成一个可以在任意单元格调用的自定义函数。

名称管理器支持中文参数名，比如“数据”“条件列”“清单”。如果某些版本不接受中文参数名，改成 data、col、list 之类的英文名即可，不影响功能。

3.2 定义最小版 XFILTER

打开公式 -> 名称管理器 -> 新建，按下表填写：

引用位置填入：

=LAMBDA(数据,条件列,清单, IFERROR( FILTER(数据, ISNUMBER(MATCH(条件列,清单,0)), "无匹配"), "无匹配" ) )

确定后，在工作表任意单元格里就可以使用：

=XFILTER(A2:E9, B2:B9, F2:F4)

这个最小版 XFILTER 做了三件事：第一，把数据区域当作第一个参数，返回时保持原行列结构；第二，把条件列和清单区域作为参数，内部用 MATCH + ISNUMBER 生成布尔数组；第三，用 IFERROR 和 FILTER 的“为空时”参数兜底，避免没有结果时显示难看的错误值。

3.3 参数速查表

这三个参数是最小版 XFILTER 的固定接口。缺一个函数就无法工作，顺序写反也会直接报错。

4. 升级 XFILTER：支持新增条件列

4.1 双条件列版本

把上一节的 LAMBDA 升级成 5 个参数。新增条件列 2 和条件清单 2，两个条件之间用乘号连接：

=LAMBDA(数据,条件列1,清单1,条件列2,清单2, IFERROR( FILTER(数据, ISNUMBER(MATCH(条件列1,清单1,0)) * ISNUMBER(MATCH(条件列2,清单2,0)), "无匹配"), "无匹配" ) )

调用方式：

=XFILTER(A2:E9, B2:B9, F2:F4, C2:C9, H2:H3)

参数顺序是：数据区、第一个条件列、第一个清单、第二个条件列、第二个清单。建议把每对“条件列 + 清单”写在一起，减少调用时漏参数的几率。

4.2 三层条件列版本

当查询需要三个条件时，继续按同样模式扩展：

=LAMBDA(数据,条件列1,清单1,条件列2,清单2,条件列3,清单3, IFERROR( FILTER(数据, ISNUMBER(MATCH(条件列1,清单1,0)) * ISNUMBER(MATCH(条件列2,清单2,0)) * ISNUMBER(MATCH(条件列3,清单3,0)), "无匹配"), "无匹配" ) )

需要说明的是，LAMBDA 的参数个数是固定的，没法像某些编程语言那样写一个“可变参数”版本。条件列数量固定为多少个，就需要定义对应参数的函数。建议把一到三层分别定义成 XFILTER、XFILTER2、XFILTER3，按查询复杂度选用。

如果想让条件数量更灵活，还可以把“条件列和清单”预先用辅助列合并成一个布尔判断列，再交给最小版 XFILTER 处理。不过那样就把复杂度转移到了表格结构上，适合项目固定、速度优先的场景。

4.3 同一列也可以用“或”清单查询

多值清单本身已经实现了“同一列内满足任意一个值”的 OR 逻辑。如果要把“两个不同条件列”的命中结果合并，查询语义就变成“销售员在清单 1 中，或者区域在清单 2 中”。这时把乘号换成加号：

=LET(数据, A2:E9, 条件列1, B2:B9, 清单1, F2:F4, 条件列2, C2:C9, 清单2, H2:H3, FILTER(数据, ISNUMBER(MATCH(条件列1,清单1,0)) + ISNUMBER(MATCH(条件列2,清单2,0)), "无匹配"))

在实际报表里，AND 和 OR 的混用很常见。建议先用乘号搭建基础查询，再单独扩展 OR 分支，不要在同一个公式里塞进去超过两种逻辑组合。

4.4 只返回需要的列

XFILTER 返回的是整块数据区。如果只想看“销售员、区域、销售额”三列，可以在外面套 CHOOSECOLS：

=LET(结果, XFILTER(A2:E9, B2:B9, F2:F4, C2:C9, H2:H3), CHOOSECOLS(结果, 2, 3, 5) )

CHOOSECOLS 的第二个参数是列序号，这里表示从结果里取第 2、3、5 列。这个组合在制作报表摘要时非常有用，把查询和列裁剪的职责分开。

5. 让查询结果“活”起来：清单联动与下拉选择

5.1 把清单区域变成可维护的控制区

XFILTER 的最大改进不是公式本身，而是条件外置。F2:F4 和 H2:H3 这类清单区域可以放在工作表顶部，命名为“控制区”，其他人只需要修改这些单元格里的值，不用碰公式。

推荐布局是：控制区放在左侧或顶部，数据区放在中间，结果区放在右侧。控制区每个清单上方写上用途，例如“销售员清单”“区域清单”。这样一张表就能当成一个小型查询面板使用。

5.2 用数据验证制作单值下拉

选中清单单元格，例如 F2:F4，点击数据 -> 下拉列表或数据验证，把来源设置为可选人员所在的整列。这样每个清单单元格都有下拉菜单，用户从下拉框里选择一名销售员，多个单元格合起来就形成了一组多值清单。

这种“多单元格各选一个值”的做法比一次选择多个值更稳定，也更容易被 WPS 和 Excel 同时支持。

5.3 用 UNIQUE 自动生成清单

如果业务数据经常变化，清单区域不希望手动维护，可以直接用 UNIQUE 从数据列生成可选值：

=UNIQUE(B2:B100)

假设这个动态数组结果落在