Excel FILTER函数一对多/多对多查询案例详解

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

以最常用的订单、地区、客户表为例，本文不会将FILTER简单等同于普通筛选按钮，而是拆解为4个实操步骤：先理清源数据和条件，再做一对多查询、多对多查询，最后预留空结果与排序的处理方式。

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

源数据区域应保持连续，字段名统一放在第一行；条件区最好与源数据分离，置于表格右侧或上方，便于公式直接引用。示例中左侧为待筛选的原始列表，右侧为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进行一对多、多对多查询的最大优势：条件区变化后，溢出结果自动刷新。