Excel 高级函数和组合
827 字约 3 分钟
2026-05-20
本页定位
这一章解决复杂分析中的公式建模问题。高级函数的重点不是炫技，而是让公式更清晰、更稳定、更容易复用。
1. 动态数组
动态数组可以让一个公式返回多行多列结果，是新版 Excel 的核心能力。
FILTER
按条件筛选数据：
=FILTER(订单表, (订单表[月份]="2026-05")*(订单表[渠道]="自然流量"), "无数据")多个条件之间：
*表示同时满足，相当于 AND。+表示满足任一条件，相当于 OR。
UNIQUE
提取唯一值：
=UNIQUE(订单表[用户ID])SORT 和 SORTBY
按销售额排序：
=SORTBY(商品汇总表, 商品汇总表[销售额], -1)TAKE 和 DROP
取 Top N：
=TAKE(SORTBY(商品汇总表, 商品汇总表[销售额], -1), 10)2. LET
LET 用来给公式中的中间结果命名，减少重复计算并提高可读性。
=LET(
amount, 订单表[实付金额],
channel, 订单表[渠道],
month, 订单表[月份],
SUM(FILTER(amount, (channel="自然流量")*(month="2026-05")))
)适合使用 LET 的场景：
- 同一条件重复出现多次。
- 公式太长，不容易维护。
- 需要拆解中间计算过程。
- 看板指标需要保持公式一致。
3. LAMBDA
LAMBDA 可以把公式封装成自定义函数。
示例：计算增长率。
=LAMBDA(current, previous, IFERROR((current-previous)/previous, 0))在名称管理器中命名为 GROWTH_RATE 后，可以这样使用：
=GROWTH_RATE(B2, C2)适合封装的函数：
- 增长率。
- 毛利率。
- 目标完成率。
- 日期区间判断。
- 复杂文本清洗规则。
4. 多表关联公式
XLOOKUP 多字段匹配
当单一字段不能唯一匹配时，可以拼接多个条件。
=XLOOKUP(A2&B2, 客户表[城市]&客户表[等级], 客户表[折扣])更推荐在数据表中提前创建稳定的辅助键：
城市等级键 = 城市 & "|" & 等级INDEX + XMATCH
二维查找：
=INDEX(价格表, XMATCH(A2, 价格表[商品ID]), XMATCH(B2, 价格表[#Headers]))5. 组合分析公式
分组汇总
用 UNIQUE 生成维度，再用 SUMIFS 计算指标。
=UNIQUE(订单表[渠道])=SUMIFS(订单表[实付金额], 订单表[渠道], A2)动态 Top N 看板
=TAKE(SORTBY(商品汇总表, 商品汇总表[销售额], -1), 控制区!B2)其中 控制区!B2 可以是用户输入的 Top N 数字。
动态时间窗口
统计最近 30 天销售额：
=SUMIFS(订单表[实付金额], 订单表[订单日期], ">="&TODAY()-30, 订单表[订单日期], "<="&TODAY())6. 错误控制
常用错误处理：
=IFERROR(A2/B2, 0)=IFNA(XLOOKUP(A2, 商品表[商品ID], 商品表[商品名称]), "未匹配")错误控制原则：
- 不要盲目把所有错误都变成 0。
- 查找失败用“未匹配”比空白更安全。
- 除数为 0 时要确认业务含义。
- 看板展示可以隐藏错误，计算过程最好保留检查列。
7. 公式设计原则
- 能用结构化引用就不要用整列引用。
- 公式过长时用
LET拆解。 - 复杂清洗优先考虑 Power Query。
- 重复逻辑考虑
LAMBDA或 VBA。 - 重要指标公式要写清口径说明。