Excel FILTER函数一对多及多对多查询案例拆解

FILTER 函数的核心理念其实非常直观：要实现单条件返回多条记录，只需将源数据区域传递给 array 参数，而判断逻辑则交给 include 参数；若要进行多对多查询，则需先将多个条件转化为行数完全一致的 TRUE/FALSE 数组，再通过乘号、加号或 XMATCH 进行组合。以下所有操作均基于 Microsoft 365 版本的 Excel，如果您使用的是 WPS 或旧版 Excel，请先确认软件支持 FILTER 函数后再进行后续操作。

我们以最常见的订单、地区、客户表为例进行拆解，不会将 FILTER 当作普通筛选按钮泛泛而谈，而是直接拆分为四个实操步骤：先理清源数据和条件、完成一对多查询、实现多对多查询、提前预留空结果及排序的处理入口。

第一步：明确区分源数据和条件区域

源数据区域应保持连续，字段名统一置于第一行；条件区域最好避免放在源数据中间，而是放置在表格右侧或上方，以便公式直接引用。本示例中，左侧为待筛选的原始列表，右侧为 FILTER 的返回区域，公式栏中可以看到 =FILTER(...) 已将数据源和判断条件分开书写。

自行建表时，请先将源数据整理成类似 A2:D20 这样连续的订单区域，再将“地区”“客户”“商品”等条件放在 F2:H2 区域。除非数据量极小，否则不建议将整列直接填入公式，因为全列引用会拖慢动态数组的计算速度。

第二步：使用 FILTER 实现一对多查询

一对多查询即通过单个条件匹配出所有符合条件的行，例如查询所有“地区=华东”的订单。公式可直接写为 =FILTER(A2:D20,C2:C20=G2,"无符合记录")。其中 A2:D20 是您要返回的整块数据，C2:C20=G2 会逐行判断地区是否等于条件单元格，符合条件的行将一次性溢出到右侧的结果区域。

此类公式无需向下拖拽复制，只需在结果区域左上角单元格输入一次，Excel 便会自动展开多行结果。若结果区域下方仍存在旧内容，Excel 会直接报出 #SPILL! 错误，此时只需清除溢出范围内的多余内容后重新计算即可。

第三步：组合多个条件完成多对多查询

多对多查询有两种常用写法。当多个单值条件需同时满足时，使用乘号代表 AND 逻辑；当单个字段允许多个候选值时，可用 XMATCH 或 COUNTIF 生成对应的匹配数组。例如，要查询“地区在 H2:H4 列表内，同时客户在 I2:I3 列表内”的所有订单，公式可写为 =FILTER(A2:D20,ISNUMBER(XMATCH(C2:C20,H2:H4))*ISNUMBER(XMATCH(B2:B20,I2:I3)),"无符合记录")。

乘号的作用是要求每一行必须同时满足两个判断逻辑。若需实现“地区是华东或华南”这种同字段多选需求，可直接写 =FILTER(A2:D20,ISNUMBER(XMATCH(C2:C20,H2:H4)),"无符合记录")。如果只是两个固定值的或逻辑，也可用加号组合：(C2:C20="华东")+(C2:C20="华南")，只要最终生成的数组行数与源数据完全对应即可。

第四步：为空结果、排序和去重预留处理接口

if_empty 并非无用的装饰参数，在实际制作查询表时，一旦没有符合条件的记录，直接让公式返回空白或提示文字，远比页面凭空出现 #CALC! 错误更加稳定。如果需要按金额从高到低排序，只需将 FILTER 套入 SORT 函数：=SORT(FILTER(A2:D20,ISNUMBER(XMATCH(C2:C20,H2:H4)),"无符合记录"),4,-1)。

若返回结果中可能出现重复的客户，可再在外层套用 UNIQUE 函数：=UNIQUE(FILTER(B2:B20,ISNUMBER(XMATCH(C2:C20,H2:H4)),"无符合记录"))。编写此类嵌套公式时，建议先单独跑通 FILTER，再逐层包裹 SORT、UNIQUE，避免一开始就堆砌所有函数，导致排查困难。

FILTER 函数语法与参数说明

FILTER 的完整语法为：

=FILTER(array, include, [if_empty])

常见错误排查

出现 #CALC! 错误，多因未匹配到结果且未写 if_empty 参数；出现 #SPILL! 错误，通常是结果溢出区域被已有内容阻挡；出现 #VALUE! 错误，常见原因是 array 和 include 两个区域的行数不一致。若公式返回了不应出现的行，可选中 include 那一段参数按 F9，临时查看生成的 TRUE/FALSE 数组，核对每一行的判断逻辑是否对齐。

进行多对多查询时，最容易写错的是条件列表的方向。XMATCH(C2:C20,H2:H4) 是将每一行的地区拿到条件列表中匹配，若写成 XMATCH(H2:H4,C2:C20)，生成的数组行数就会与订单表不匹配，FILTER 无法逐行筛选。

扩展示例：打造可改条件的查询模板

如果 H2:H4 是可自由选择的地区列表，I2:I3 是可自由选择的客户列表，订单表固定在 A2:D20，可直接将公式写死为：

=FILTER(A2:D20,ISNUMBER(XMATCH(C2:C20,H2:H4))*ISNUMBER(XMATCH(B2:B20,I2:I3)),"无符合记录")

之后只需修改条件区域的内容，完全无需改动公式。如需按金额降序，则在外层添加 SORT；如果只需返回客户名，将 array 参数改为 B2:B20；若想去除重复客户，再套一层 UNIQUE。这正是 FILTER 处理一对多、多对多查询最便捷的地方：条件区域一改，溢出的结果会自动同步刷新。