前言
上周五下午，财务部的小王跑来找我，说有个数据匹配的问题弄了一下午没搞定。我过去一看，他在一个2000行的表格里用VLOOKUP做数据关联，公式拉得老长，中间还夹了好几个IFERROR。
我问他：”你为什么不用XLOOKUP？”他愣了一下：”XLOOKUP是什么？”
我帮他改完只用了两分钟。从2000行数据匹配，到加上错误处理，前前后后一个公式搞定。小王当时的表情很有意思，有点像刚学会骑自行车的小孩——”就这？”
这件事让我意识到，很多人对Excel新函数的了解还停留在五年前。XLOOKUP、FILTER、LET这些函数，其实早就出来了，而且真的能大幅提升效率。
今天这篇文章，我想把Excel 2026里那些实用的新函数都讲清楚。不是功能列表的堆砌，而是结合实际工作场景，告诉你什么场景用什么函数、怎么用。
如果你还在用VLOOKUP做数据匹配，或者用公式里嵌套一堆IF的写法，这篇文章可能会让你有种”早知道就好了”的感觉。
第一章：为什么还要学新函数
1.1 Excel版本和函数支持
在开始之前，先说明一下版本问题。
Excel函数是逐步推出的，不是所有函数在所有版本都可用：
- Excel 365 / Excel 2021：所有新函数都支持
- Excel 2019 及更早版本：部分函数不可用
如果你的Excel是365订阅版的，那你应该能用上所有的函数。打开Excel，按Alt + Q，输入函数名，如果能搜到就说明支持。
1.2 新函数带来的改变
说一个我的观察：很多职场人做Excel表格，80%的时间其实花在了”找数据”和”整理数据”上，而不是真正的分析。
VLOOKUP、INDEX+MATCH这些经典函数当然能用，但用起来确实麻烦。新函数把这部分工作简化了，让你有更多时间去做真正有价值的事情。
以前做数据匹配：写公式 → 调参数 → 报错 → 改公式 → 再报错 → 终于对了
现在做数据匹配：XLOOKUP搞定，可能一行就够
第二章：XLOOKUP——VLOOKUP的完美替代
2.1 VLOOKUP的痛点
先说说VLOOKUP为什么让人又爱又恨。
VLOOKUP的优点：
- 大多数人会用
- 语法相对简单
VLOOKUP的缺点：
- 只能从左往右查找，不能反向
- 列索引是固定数字，删列会出错
- 默认模糊匹配，容易出错
- 性能在大数据量时较差
这些缺点你可能都遇到过。比如你写了个VLOOKUP，引用了第3列，结果不小心在前面插入了一列，公式就全乱了。
2.2 XLOOKUP的基本用法
XLOOKUP的出现就是为了解决这些问题。
语法：
plaintext
=XLOOKUP(查找值, 查找数组, 返回数组, [未找到时返回的值], [匹配模式], [搜索模式])
最简单的用法（三个必需参数）：
plaintext
=XLOOKUP(A2, B:B, C:C)
解释一下：
- A2：你要查找的值
- B:B：你在哪一列里找
- C:C：你要返回哪一列的值
和VLOOKUP不一样，XLOOKUP不需要指定返回第几列，直接选择返回的列就行。
2.3 实战案例：员工信息匹配
场景：有两张表
- 表A：员工工号、姓名、部门
- 表B：工号、基本工资、绩效工资
现在要在表A里匹配出每个员工的基本工资。
用VLOOKUP写：
plaintext
=VLOOKUP(A2, 表B!A:C, 2, FALSE)
用XLOOKUP写：
plaintext
=XLOOKUP(A2, 表B!A:A, 表B!B:B)
XLOOKUP的优势：
- 不需要数第几列
- 默认精确匹配，不会出错
- 即使插入列也不会乱
2.4 XLOOKUP的进阶用法
1. 反向查找
VLOOKUP只能从左往右找，但有时候你想从右往左。比如你知道员工姓名，想找工号：
plaintext
=XLOOKUP("张三", B:B, A:A)
直接写，查找列和返回列都可以随便选。
2. 未找到时的默认值
plaintext
=XLOOKUP(A2, B:B, C:C, "未找到")
当找不到匹配时，返回”未找到”，而不是让人头疼的#N/A。
3. 匹配模式
plaintext
=XLOOKUP(A2, B:B, C:C, , -1) // 向下查找最接近的
=XLOOKUP(A2, B:B, C:C, , 1) // 向上查找最接近的
=XLOOKUP(A2, B:B, C:C, , 2) // 通配符匹配
4. 搜索模式
plaintext
=XLOOKUP(A2, B:B, C:C, , , -1) // 从最后往前找
=XLOOKUP(A2, B:B, C:C, , , 1) // 从前往后找
=XLOOKUP(A2, B:B, C:C, , , 2) // 二分法搜索（大数据量时更快）
2.5 我的使用建议
XLOOKUP几乎可以替代90%的VLOOKUP场景。建议：
- 所有新写的公式：用XLOOKUP
- 旧的VLOOKUP公式：如果能用XLOOKUP替换，建议逐步替换
- 特别复杂的嵌套公式：考虑是否可以用XLOOKUP简化
第三章：FILTER——智能筛选神器
3.1 什么是FILTER函数
FILTER是Excel里我最喜欢的函数之一。它能根据条件筛选数据，并返回筛选结果。
语法：
plaintext
=FILTER(数组, 包含, [如果为空时返回])
最简单的例子：
plaintext
=FILTER(A:C, C:C="销售部")
这行公式的意思是：从A到C列中筛选出C列等于”销售部”的所有行。
3.2 和高级筛选的区别
你可能会说，高级筛选不是也能做到吗？是的，但FILTER更好用：
- 高级筛选是”一次性”操作，改条件要重新筛选
- FILTER是公式，条件改变时自动更新
- FILTER返回的是数组，可以直接用于其他计算
3.3 实战案例：多条件筛选
场景：从销售记录表中，筛选出”华东区域”、”销售额大于10000″的记录
公式：
plaintext
=FILTER(A:D, (B:B="华东")*(D:D>10000))
注意：多条件用乘号*连接，表示”且”的关系。
3.4 筛选结果为空时
如果没有任何记录匹配条件，FILTER默认会返回错误。可以用第三个参数自定义返回内容：
plaintext
=FILTER(A:C, (B:B="华东")*(D:D>10000), "无匹配记录")
3.5 动态筛选表
FILTER的一个高级用法是创建”动态筛选表”：
场景：在另一个sheet里，根据选择的部门动态显示该部门的所有员工
步骤一：在某个单元格（比如A1）输入部门名称，比如”销售部”
步骤二：在另一个区域写公式：
plaintext
=FILTER(员工表!A:D, 员工表!C:C=A1)
现在，当你改变A1的值时，下面的表格会自动更新显示对应部门的员工。
3.6 结合其他函数使用
FILTER返回的是一个数组，可以和其他函数配合：
统计筛选结果的数量：
plaintext
=ROWS(FILTER(A:A, 条件))
=COUNTA(FILTER(A:A, 条件))
对筛选结果求和：
plaintext
=SUM(FILTER(C:C, 条件))
FILTER + SORT：
plaintext
=SORT(FILTER(A:D, 条件), 4, -1) // 按第4列降序排列
第四章：LET函数——让公式更清晰
4.1 为什么需要LET
你有没有见过这种公式：
plaintext
=(A1+B1)*(A1-B1)/((A1+B1)*(A1-B1)+100)
这个公式里(A1+B1)*(A1-B1)重复出现了3次，每次Excel都要重新计算一遍。
对于简单的公式这没什么，但如果你写一个很长的公式，里面有很多重复的计算，Excel就会卡顿。
LET函数就是来解决这个问题的。
4.2 LET的基本用法
语法：
plaintext
=LET(名称1, 值1, 名称2, 值2, ..., 计算)
重写上面的公式：
plaintext
=LET(差值, (A1+B1)*(A1-B1), 差值/差值+100)
现在：
- “差值”只计算一次
- 后面引用”差值”时直接用保存的值
- 公式更清晰易读
4.3 实战案例：复杂计算优化
场景：计算销售提成，公式比较复杂：
- 基础提成 = 销售额 × 5%
- 如果销售额 > 10000，超出部分再加3%
- 加上部门系数
不用LET写：
plaintext
=(A2*0.05 + MAX(0, A2-10000)*0.03) * VLOOKUP(B2, 部门系数表!A:B, 2, 0)
用LET写：
plaintext
=LET(
基础提成, A2*0.05,
超出部分, MAX(0, A2-10000)*0.03,
部门系数, VLOOKUP(B2, 部门系数表!A:B, 2, 0),
(基础提成 + 超出部分) * 部门系数
)
用LET写的公式：
- 更容易理解，每个步骤都有名称
- VLOOKUP只计算一次，性能更好
- 后续修改也更方便
4.4 性能提升
LET在以下场景对性能提升明显：
- 公式中有重复的复杂计算
- VLOOKUP/XLOOKUP在公式中多次使用
- 数组公式中的重复运算
根据微软的测试，某些场景下性能提升可达30%-50%。
第五章：UNIQUE——去重利器
5.1 基本用法
UNIQUE函数返回列表中的唯一值。
语法：
plaintext
=UNIQUE(数组, [按列还是按行], [是否只返回出现一次的值])
最简单的例子：
plaintext
=UNIQUE(A:A) // 返回A列中的唯一值
5.2 实战案例：提取不重复的部门列表
场景：有一张员工表，C列是部门，有很多重复的部门名称
提取不重复的部门列表：
plaintext
=UNIQUE(C:C)
结果会自动展开，显示所有不重复的部门。
5.3 结合COUNTIF统计出现次数
提取出唯一值后，可以结合COUNTIF统计每个值出现的次数：
公式：
plaintext
=LET(
部门列表, UNIQUE(C:C),
部门列表 & " (" & COUNTIF(C:C, 部门列表) & "人)"
)
这会返回一个部门名称和人数的列表。
5.4 提取只出现一次的值
如果想知道哪些部门只有一个人：
plaintext
=UNIQUE(C:C, , TRUE)
第三个参数设为TRUE，只返回出现恰好一次的值。
第六章：GROUPBY——数据汇总新方案
6.1 什么是GROUPBY
GROUPBY是Excel 365新增的函数，用于对数据进行分组汇总，类似数据透视表的功能，但用公式实现。
语法：
plaintext
=GROUPBY(行字段, 值字段, 汇总方式, [标题行数], [总计行])
最简单的例子：
plaintext
=GROUPBY(B:B, D:D, SUM)
这会按B列分组，对D列求和。
6.2 和透视表的对比
透视表的优点：
- 拖拽操作更直观
- 功能更丰富
GROUPBY的优点：
- 公式可以自动更新
- 更适合动态报表
- 可以和其他函数组合使用
6.3 实战案例：按月汇总销售额
场景：有一张销售记录表，A列是日期，B列是月份，C列是客户，D列是销售额
按月汇总销售额：
plaintext
=GROUPBY(TEXT(A:A, "yyyy-mm"), D:D, SUM, 0, -1)
解释：
- 第一个参数：
TEXT(A:A, "yyyy-mm")，把日期转换成”yyyy-mm”格式作为分组依据 - 第二个参数：D列的值
- 第三个参数：SUM，求和
- 第四个参数：0，不显示标题行
- 第五个参数：-1，在最后显示总计行
6.4 多字段分组
GROUPBY支持多字段分组，但语法稍有不同：
plaintext
=HSTACK(
GROUPBY(行字段1, 行字段2, LAMBDA(x, COUNT(x)), 0, 0),
GROUPBY(行字段1, 行字段2, 值字段, 汇总方式, 0, 0)
)
这个用起来比较复杂，如果需要多字段分组，可能还是透视表更方便。
第七章：综合实战案例
7.1 案例一：动态销售报表
需求：创建一个销售报表，可以按部门筛选，自动显示该部门的销售汇总
步骤一：准备数据表
- A列：日期
- B列：部门
- C列：销售员
- D列：销售额
步骤二：创建筛选控件
在A1单元格旁边放一个数据验证下拉框，引用UNIQUE提取的部门列表
步骤三：写汇总公式
plaintext
=LET(
筛选部门, A1,
总销售额, SUMIF(B:B, 筛选部门, D:D),
总订单数, COUNTIF(B:B, 筛选部门),
平均订单额, 总销售额/总订单数,
"销售额: " & 总销售额 & CHAR(10) &
"订单数: " & 总订单数 & CHAR(10) &
"平均订单额: " & ROUND(平均订单额, 2)
)
步骤四：写明细公式
plaintext
=FILTER(A:D, B:B=A1)
当你在A1选择不同部门时，下面的明细表会自动显示对应部门的数据。
7.2 案例二：库存预警表
需求：从入库记录中找出库存低于安全库存的商品
假设：
- 商品表：A列商品编码，B列商品名称，C列当前库存，D列安全库存
- 库存记录：另外一张表记录每次的出入库
方法一：用FILTER
plaintext
=FILTER(A:B, C:C < D:D, "库存充足")
方法二：用IF+FILTER显示更多信息
plaintext
=LET(
预警列表, FILTER(A:B, C:C < D:D),
预警行数, ROWS(预警列表),
IF(预警行数=0, "库存充足", 预警列表)
)
7.3 案例三：个人所得税计算
需求：根据个税税率表，计算员工应缴个税
税率表（简化版）：
- 0-5000：0%
- 5000-15000：10%
- 15000-35000：20%
- 35000以上：25%
公式：
plaintext
=LET(
应纳税所得额, MAX(0, 税前工资 - 5000 - 社保公积金 - 专项附加扣除),
税级1, MIN(应纳税所得额, 5000) * 0,
税级2, MAX(0, MIN(应纳税所得额, 15000) - 5000) * 0.1,
税级3, MAX(0, MIN(应纳税所得额, 35000) - 15000) * 0.2,
税级4, MAX(0, 应纳税所得额 - 35000) * 0.25,
总税额, 税级1 + 税级2 + 税级3 + 税级4,
总税额
)
第八章：函数组合技巧
8.1 常见组合
UNIQUE + SORT：生成排序后的唯一值列表
plaintext
=SORT(UNIQUE(A:A))
FILTER + SORT：对筛选结果排序
plaintext
=SORT(FILTER(A:D, 条件), 3, -1) // 按第3列降序
FILTER + XLOOKUP：在筛选结果中查找
plaintext
=XLOOKUP(查找值, FILTER(B:B, 条件1), FILTER(C:C, 条件1))
LET + 复杂公式：优化性能
plaintext
=LET(
结果, FILTER(A:D, 条件),
IF(ROWS(结果)=0, "无数据", 结果)
)
8.2 性能优化建议
避免在公式中重复计算
把重复的部分用LET包起来，只计算一次。
用FILTER替代手动筛选
如果你经常需要筛选数据来做分析，用FILTER+辅助表格会更高效。
注意数组大小
FILTER返回的数组如果很大，可能会影响性能。如果只需要部分结果，用INDEX+FILTER限制返回行数。
结语
写到最后，总结一下我的想法：
Excel的新函数确实让很多以前复杂的操作变得简单了。XLOOKUP替代VLOOKUP、FILTER替代手动筛选、LET优化复杂公式——这些变化让数据分析的门槛降低了不少。
但我想说的是：工具再强大，也只是工具。真正重要的是你知道自己要做什么，知道怎么处理数据。
建议大家有空的时候，可以把工作中的一个常用操作拿出来，看看有没有可以用新函数优化的地方。一开始可能会觉得不习惯，但用多了就会发现，这些函数确实能帮你省不少时间。
如果有任何问题，欢迎在评论区交流。祝大家都能成为Excel高手！
发表回复