SUMIFS和LET函数——让 Excel 自己动起来

上周五快下班，群里突然弹出一条消息。老板要统计一些产品的销售额，还要按达成率排序， “下班前给我”。
我盯着屏幕上那个拖了三屏的公式，深吸一口气。
真他妈想关机走人。
好几次都这样了，I 服了 U …



顺手把这个 SUMIFS​ 函数的使用记录一下，也扩展一下 LET 函数。

一、SUMIFS：它不是计算器，是带筛子的漏斗
很多人以为 SUMIFS 就是"多条件加一下"。错了。
它的真实工作流程是：逐行扫描 → 全部条件都满足 → 把对应数字拎出来 → 加总。只要有一个条件不匹配，这行直接跳过，连看都不看。
像超市收银员，只扫贴有"特价"标签且是"饮料区"的商品。


SUMIFS漏斗示意图

1.1 语法就一句话，但坑巨多
=SUMIFS(求和区域, 条件区域1, 条件1, 条件区域2, 条件2...)
最容易翻车的：范围没对齐。
你的求和区域是 A2:A100​，条件区域就必须也是 B2:B100​，不能是 B2:B99​。少一行，直接报 #VALUE!。
我见过有人复制公式时手滑，多选或少选了一行，结果查半天查不到原因。很蠢，但很容易犯。
另一个隐形坑：数据类型。
你眼睛看着是"2024-06-01"，但单元格里存的是文本 "2024-06-01"​，SUMIFS 就当没看见。怎么查？选中条件单元格，用 TYPE() 函数——返回 2 就是文本，返回 1 才是数字。转成标准日期再算，否则结果永远是 0。

1.2 通配符：* 和 ? 是雷区
如果你要匹配的产品编号里本身就有 *​，比如 A*B*C123，直接写：
=SUMIFS(销售额, 产品编号, "A*B")
Excel 会理解为"A开头、B结尾的任意字符串"，比如 A123B​、A测试B​ 都会被算进去。结果莫名其妙大了一圈，你还不知道错在哪。
解法：用波浪线 ​~​ ​ 转义。
=SUMIFS(销售额, 产品编号, "A~*B") // 只匹配字面意义的 "A*B"
记住这套规则：


​~*​ 匹配字面 *


​~?​ 匹配字面 ?


​~~​ 匹配字面 ~


如果你条件写在单元格里（比如 D2），可以套一层 SUBSTITUTE 自动转义，省得每次手动改：
=SUMIFS(销售额, 产品编号, SUBSTITUTE(D2, "*", "~*"))

1.3 动态日期：别手写，让公式自己算
我见过太多报表里硬编码这种写法：
=SUMIFS(销售额, 日期, ">=2024-06-01", 日期, "=" & 本月第一天, 日期, "=" & EOMONTH(TODAY(), -1) + 1
),

年度目标, VLOOKUP(产品代码, 目标表!A:C, 2, FALSE),
月度目标, 年度目标 / 12,
达成率, 本月销售 / 月度目标,

SWITCH(TRUE,
达成率 >= 1.2, "超额完成",
达成率 >= 1, "完成",
达成率 >= 0.8, "基本完成",
TRUE, "未完成"
)
)
SUMIFS 只算一次，VLOOKUP 只查一次。 逻辑像读流水账，从上到下，清清楚楚。




2.3 命名心法：别叫 x，叫人话
我见过有人写成这样：
=LET(x, SUMIFS(...), y, VLOOKUP(...), x/y)
三天后回来，x 是什么？y 是什么？完全失忆。
命名建议直接上业务语义：
=LET(
实际销售额, SUMIFS(...),
目标销售额, VLOOKUP(...),
达成率, 实际销售额 / 目标销售额,

IF(达成率 >= 1, "达标", "未达标")
)
如果团队用 WPS 或者想对中文更友好，直接用中文变量名完全没问题。公式是写给人看的，顺便让 Excel 算一下。

2.4 调试：把公式掰开看
LET 最大的隐藏福利是调试方便。如果你结果不对，不用从头拆括号，直接在最后输出中间变量：
=LET(
销售额, SUMIFS(...),
目标额, VLOOKUP(...),
达成率, 销售额 / 目标额,

// 调试时把下面这行打开，看看中间值
// "销售额:" & 销售额 & " 目标:" & 目标额 & " 比率:" & 达成率

IF(达成率 > 1, "达标", "未达标")
)
小技巧：选中公式里的某一段（比如 ">="&EOMONTH(TODAY(),-1)+1​），按 F9，Excel 会直接显示这段的计算结果。看完按 Esc 别按回车，不然就把值写死了。

2.5 兼容性：旧版 Excel 和 WPS 怎么办？
LET 是 Excel 2021 / 365 和 WPS 新版才支持的。如果你要发文件给用 Office 2016 的同事，打开直接报 ​#NAME?​ 。
临时方案：用 IFERROR 做降级：
=IFERROR(
LET(测试, 1, 测试), // 能跑 LET 就是新版

// 跑不了就降级为传统写法
IF(SUMIFS(...)/VLOOKUP(...)>1, "达标", "未达标")
)
长期方案：如果公司还在用老版本，复杂计算建议拆成多列辅助列，或者上 Power Query。LET 再香，也得看环境吃饭。

三、实战：一个能直接交差的销售看板
把 SUMIFS 和 LET 串起来，做一个带动态筛选的销售看板。假设 B2:B6 是用户输入的查询条件：
=LET(
开始日期, B2,
结束日期, B3,
产品筛选, B4,
地区筛选, B5,
最小金额, B6,

总销售额, SUMIFS(
销售表!销售额,
销售表!日期, IF(开始日期"", ">="&开始日期, ">="&DATE(1900,1,1)),
销售表!日期, IF(结束日期"", ""&最小金额, ">="&0)
),

总订单数, SUMPRODUCT(
(销售表!日期 >= IF(开始日期="", 0, 开始日期)) *
(销售表!日期 = IF(最小金额=0, 0, 最小金额))
),

平均单价, IF(总订单数>0, 总销售额/总订单数, 0),

"销售额：" & TEXT(总销售额, "#,##0") & "元" & CHAR(10) &
"订单数：" & TEXT(总订单数, "#,##0") & "笔" & CHAR(10) &
"平均单价：" & TEXT(平均单价, "#,##0.00") & "元"
)
这段实现了什么？


用户任何条件留空，就自动忽略该条件（全量统计）


同时算出销售额、订单数、平均单价


结果带换行，直接贴到邮件或 PPT 里像人话



四、速查表：建议贴在显示器边上










场景


公式片段


注意




本月至今


​">="&EOMONTH(TODAY(),-1)+1


别手写日期




最近 30 天


​">="&TODAY()-30


包含今天




匹配空值


​""


不是 0




匹配非空


​""


排除真空




通配符转义


​SUBSTITUTE(A1,"*","~*")


​*​ ?​ ~ 都要转




或逻辑（2 个条件）


​SUMIFS(...)+SUMIFS(...)


最稳




或逻辑（多个条件）


​SUMPRODUCT((区域="A")+(区域="B"))*销售额


数组运算




防除零


​IF(分母=0, "N/A", 分子/分母)


或 +0.0001




整列引用替代


​A2:A1000


留 20% 余量






写在最后
SUMIFS 解决的是"怎么算对"，LET 解决的是"怎么算得又对有快还看得懂"。这两个函数搭在一起，基本上能覆盖你 80% 的数据统计需求。

记得下班前早些关电脑，手机消息回到家再看。
‍