我的个人技术笔记

xlookup和filter

14 min read

xlookup和filter

在 Excel 中，XLOOKUP 和 FILTER 是两个极为强大的查找与筛选函数，它们完全颠覆了传统 VLOOKUP 的局限，让数据提取变得灵活高效。以下是对这两个函数的详细总结，包含语法、参数、经典实例和组合应用。

一、XLOOKUP 函数详解

XLOOKUP 用于在某个范围或数组中 查找匹配项，并返回对应位置的值，可替代 VLOOKUP、HLOOKUP、INDEX+MATCH 等传统组合。

1. 语法与参数

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
参数必需/可选说明
lookup_value必需要查找的值，可以是单个值、单元格引用或数组。可以利用&拼接实现多条件匹配，比如a2&b2
lookup_array必需要搜索的数组或区域，必须是单行或单列。可以利用&拼接实现多条件匹配，比如a:a&b:b
return_array必需要返回的数组或区域，不能返回单个值，可以返回多列，利用hstack或choosecols或choose
=XLOOKUP(G2, A:A, HSTACK(B:B, D:D, F:F))
=XLOOKUP(G2, A:A, CHOOSECOLS(A:F, 2,4,6)),A:F可以是{A:A,C:C}等数组形式
XLOOKUP(W6,表4[房号],CHOOSE({1,2,3,4},表4[签约日期],表4[租赁期限开始日期],表4[租赁期限结束日期],表4[列1]),"不存在")
if_not_found可选找不到匹配项时返回的值，省略则返回 #N/A。
match_mode可选匹配模式：
0 - 精确匹配（默认）
-1 - 精确匹配或下一个较小项
1 - 精确匹配或下一个较大项
2 - 通配符匹配（* ? ~）
search_mode可选搜索方式：
1 - 从上到下（默认）
-1 - 从下到上（倒序）
2 - 二分搜索升序（数据需升序）
-2 - 二分搜索降序（数据需降序）

2. 经典使用实例

假设有员工表 A1:D9：

员工ID姓名部门工资
1001张三销售6000
1002李四技术8000
1003王五销售5500
1004赵六财务7000
…………

① 基础正向查找
根据姓名查工资：
=XLOOKUP("张三", B2:B9, D2:D9, "无此人")
→ 返回 6000。

② 反向查找（传统VLOOKUP需要辅助列）
根据姓名查员工ID，查找列在返回列的右侧：
=XLOOKUP("李四", B2:B9, A2:A9)
→ 返回 1002，轻松实现从右向左查找。

③ 多条件查找
查找“销售部”且“姓名”为“王五”的工资，利用布尔数组相乘构建联合条件：
=XLOOKUP(1, (B2:B9="王五")*(C2:C9="销售部"), D2:D9)
(B2:B9="王五") 和 (C2:C9="销售部") 分别生成 TRUE/FALSE 数组，相乘后转为 1/0，XLOOKUP 查找 1 即匹配行。

④ 返回整行或多列结果
查找到“赵六”后返回其所在行的全部信息（ID、姓名、部门、工资）：
=XLOOKUP("赵六", B2:B9, A2:D9)
公式会溢出到相邻单元格，一次性输出多列数据。

⑤ 模糊匹配（近似查找）
假设有成绩表，需要根据分数返回等级，匹配规则为“精确或下一个较小项”：
=XLOOKUP(85, {0,60,80,90}, {"F","D","C","B"}, , -1)
当分数为85时，80对应的“C”是较小项，返回 "C"。

⑥ 查找最后一次出现（倒序搜索）
若存在多个“张三”的记录，要返回最后一次出现的工资：
=XLOOKUP("张三", B2:B9, D2:D9, , 0, -1)
search_mode 设为 -1 从下向上搜。

⑦ 错误处理
找不到时显示自定义提示：
=XLOOKUP("周七", B2:B9, D2:D9, "员工不存在")

⑧ 通配符匹配
查找姓名以“张”开头的员工的工资（仅返回第一个）：
=XLOOKUP("张*", B2:B9, D2:D9, , 2)

9 返回多列 XLOOKUP(W6,表4[房号],CHOOSE({1,2,3,4},表4[签约日期],表4[租赁期限开始日期],表4[租赁期限结束日期],表4[列1]),"不存在")

3. XLOOKUP 核心优势

  • 不需要指定列号，直接用 return_array 指定返回区域。
  • 默认精确匹配，无需第四参数 FALSE。
  • 可自由进行反向、多条件查找。
  • 返回数组可以溢出多列，灵活性强。
  • 内置错误处理和搜索方向控制。

二、FILTER 函数详解

FILTER 根据指定条件 筛选区域或数组，返回所有满足条件的记录，是一个动态数组函数，结果会自动溢出。

1. 语法与参数

FILTER(array, include, [if_empty])
参数必需/可选说明
array必需要筛选的区域或数组，可以多行多列。
include必需布尔数组，长度必须与 array 的行数（或列数）一致。TRUE 对应行被保留。
if_empty可选当没有满足条件的数据时返回的值，省略则返回 #CALC!。

2. 经典使用实例

（沿用上文员工表数据）

① 单条件筛选
提取所有“销售部”员工的全部信息：
=FILTER(A2:D9, C2:C9="销售部", "暂无销售部人员")
结果会动态溢出显示 1001 张三 销售 6000 和 1003 王五 销售 5500 两行。

② 多条件“与”筛选
筛选“销售部”且工资大于 5500 的员工：
=FILTER(A2:D9, (C2:C9="销售部")*(D2:D9>5500), "无符合条件记录")
用乘号 * 连接多个条件，相当于 AND。

③ 多条件“或”筛选
筛选“销售部”或“技术部”的员工：
=FILTER(A2:D9, (C2:C9="销售部")+(C2:C9="技术部"))
用加号 + 连接，相当于 OR。注意加法会使 TRUE 变为 1，仍然等效 TRUE。

④ 筛选后只返回指定列
如果只想显示姓名和工资，可将 array 设置为所需列的区域，同时保持 include 基于完整的行条件（行数必须匹配）：
=FILTER(B2:D9, C2:C9="销售部")
这样做会返回 B、C、D 三列，如果想返回不连续的列（如只取姓名和工资），可以结合 CHOOSECOLS：
=CHOOSECOLS(FILTER(A2:D9, C2:C9="销售部"), 2, 4)
在 Excel 365 中可用，返回姓名和工资两列。

⑤ 包含特定文本的筛选
提取姓名中包含“张”的员工：
=FILTER(A2:D9, ISNUMBER(SEARCH("张", B2:B9)))
SEARCH 找到位置返回数字，未找到报错；ISNUMBER 将结果转为 TRUE/FALSE。

⑥ 日期区间筛选
假设 E 列为入职日期，筛选 2023 年入职的员工：
=FILTER(A2:D9, YEAR(E2:E9)=2023)
或利用 (E2:E9>=DATE(2023,1,1))*(E2:E9<=DATE(2023,12,31))。

⑦ 无匹配结果处理
=FILTER(A2:D9, D2:D9>10000, "没有工资超过1万的员工")
当不存在符合条件记录时，显示自定义文本。

⑧ 与其它动态数组函数组合

  • 对筛选结果排序：=SORT(FILTER(A2:D9, C2:C9="销售部"), 4, -1) 按工资降序排列。
  • 返回唯一值：=UNIQUE(FILTER(C2:C9, D2:D9>6000)) 获取高工资员工的不重复部门。

3. FILTER 核心优势

  • 一次返回所有符合条件的记录，无需下拉或三键输入。
  • 条件设置极其灵活，支持与/或逻辑、通配、自定义函数组合。
  • 结果自动溢出，便于构建动态报表。
  • 与 SORT、UNIQUE、SUM 等函数无缝衔接。

三、XLOOKUP 与 FILTER 的核心区别

特性XLOOKUPFILTER
返回结果数量返回第一个匹配项（或最后一个）返回所有匹配项
主要用途基于键值提取单个对应值基于条件提取整个记录集
匹配方式查找值在数组中定位，支持近似、通配符条件为布尔数组，强调逻辑判断
输出形式单个值或连续多列（行）溢出多行多列筛选结果溢出
典型场景员工 ID 查姓名、价格查产品、反向查找按部门列出所有员工、按日期筛选订单
如果无匹配返回 #N/A 或自定义值 (if_not_found)返回 #CALC! 或自定义值 (if_empty)

选择原则：

  • 需要精准返回单一结果（如通过唯一标识查找属性）→ 用 XLOOKUP。
  • 需要列出一组符合条件的数据（如某月的销售明细）→ 用 FILTER。
  • 即使查找值可能对应多条记录但只需取最新/最旧一条，也可以使用 XLOOKUP 配合 search_mode；若要全部查看则用 FILTER。

四、XLOOKUP 与 FILTER 组合应用

两者经常结合使用，构建更灵活的分析模型。

示例1：用 FILTER 获取列表，再用 XLOOKUP 对列表项单独取值
先筛选出所有“销售部”员工姓名：
=FILTER(B2:B9, C2:C9="销售部")
然后利用 XLOOKUP 根据这些姓名查询其他关联表的提成数据：
=XLOOKUP(FILTER(B2:B9, C2:C9="销售部"), 提成表[姓名], 提成表[提成比例])
（注意：XLOOKUP 的 lookup_value 接受数组，会返回对应的数组结果。）

示例2：用 XLOOKUP 获取条件阈值，再放入 FILTER 进行筛选
假设某单元格 H1 存有“张三”的工资，想筛选出工资高于张三的所有员工：
=FILTER(A2:D9, D2:D9 > XLOOKUP("张三", B2:B9, D2:D9))

示例3：构建动态仪表板
通过下拉菜单选择部门（数据验证），XLOOKUP 返回部门经理等单一信息；同时 FILTER 返回该部门全部员工清单，实现一键刷新报告。

五、注意事项与版本兼容性

  1. 版本要求：

    • XLOOKUP 和 FILTER 均为 Excel 2021 及 Microsoft 365 中的新函数，Excel 2019 及更早版本不可用。
    • 如果需要在旧版中打开，公式将显示 #NAME?，可使用 IFERROR + INDEX/MATCH 或数组公式替代。

  2. 动态数组溢出：
    这两个函数均支持动态数组，结果会自动“溢出”到相邻单元格。需确保溢出区域有足够的空白单元格，否则会出现 #SPILL! 错误。

  3. 数组尺寸一致性：

    • XLOOKUP 的 lookup_array 与 return_array 必须具有相同行数（或列数）。
    • FILTER 的 include 布尔数组必须与 array 的行数匹配，通常使用与 array 行数相同的列区域。

  4. 性能建议：
    避免引用整列（如 B:B），尽量限定实际数据范围（如 B2:B1000），以提升计算速度并防止多余空单元格导致异常。

  5. 通配符使用：
    XLOOKUP 的 match_mode 设为 2 时，支持 *（任意字符序列）和 ?（单个字符），若要查找实际的星号或问号，需在前面加波浪号 ~。FILTER 本身不支持通配符，但可通过 SEARCH 或 FIND 函数配合实现。

六、总结

  • XLOOKUP 是“精准打击”的利器，解决单点查找、近似匹配、倒序搜索和多条件单值提取，完全取代 VLOOKUP 和 INDEX+MATCH。
  • FILTER 是“动态筛选”的神器，轻松实现多条件、批量数据提取，与排序、去重函数组合后能构建自动化数据看板。
  • 熟练掌握二者的语法、条件构建方式和组合应用，将大幅提升 Excel 数据处理效率和模型灵活性。

如果需要，可以针对特定场景提供更详细的公式拆解和实例演示。

Graph View