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

上周五快下班，群里突然弹出一条消息。老板要统计一些产品的销售额，还要按达成率排序， “下班前给我”。

我盯着屏幕上那个拖了三屏的公式，深吸一口气。

真他妈想关机走人。

好几次都这样了，I 服了 U …

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

一、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", 日期, "<=2024-06-30")

下个月怎么办？手动改？报表的价值就是自动化，手动改日期的报表不叫报表，叫草稿。

用函数算日期边界：

=LET(
    本月第一天, EOMONTH(TODAY(), -1) + 1,
    今日, TODAY(),
    
    SUMIFS(销售额, 日期, ">=" & 本月第一天, 日期, "<=" & 今日)
)

• ​EOMONTH(TODAY(), -1)：上个月最后一天

• ​+1：本月第一天

• ​EOMONTH(TODAY(), 0)：本月最后一天

• ​TODAY()-30：30 天前

把 TODAY() 换成报表里的日期参数，打开表格自动刷新，这才是报表该干的事。

1.4 空白 vs 零：这俩真不是一回事

做数据清洗时，这俩经常搞混：

坑在哪？ 有人输入 0 后又删掉，单元格看起来是空的，但可能残留了 0 或者 ""，肉眼根本分不清。如果结果不对，先用 COUNTIF(数量列, "") 和 COUNTIF(数量列, 0) 分别看看有多少行，定位问题。

1.5 "或"逻辑：SUMIFS 天生不会，得绕路

SUMIFS 所有条件之间是且（AND） 关系。你想统计 " 华东 或 华南 " 的销售额，不能直接塞数组，这是很多人误传的做法。

正确姿势有三种，看场景选：

方法一：简单粗暴相加（最推荐）

=SUMIFS(销售额, 地区, "华东") + SUMIFS(销售额, 地区, "华南")

好理解、好维护、兼容所有版本。条件多了公式长？

长就长，能跑比好看重要。

方法二：SUMPRODUCT 数组（条件多时用）

=SUMPRODUCT(
    ((地区列="华东") + (地区列="华南")) * 销售额列
)

这里 + 就是 "或" 的意思。TRUE 当 1，FALSE 当 0，满足任一条件就保留，最后乘销售额求和。

数据量超万行时慎用，比如我有一个表快 25 万行了…

方法三：辅助列（数据量大时最快）

如果地区特别多，比如要统计 "华东、华南、华西、华北、华中"，不如在数据源旁边加一列辅助列，用 IF(OR(...), 1, 0) 标记，然后 SUMIFS 直接筛辅助列。性能最好，公式最清爽。

1.6 性能：别让公式拖垮表格

第一，别动不动就引用整列。

// 差：扫描 100 万行
=SUMIFS(A:A, B:B, "条件")

// 好：只扫实际数据范围
=SUMIFS(A2:A5000, B2:B5000, "条件")

Excel 365 对整列引用优化了不少，但 WPS 和老版本 Excel 真的会卡。养成习惯，数据范围留 20% 余量就行，比如现在 800 行，写到 A2:A1000。

第二，别重复计算。

如果你一个 SUMIFS 结果要拿来比大小、算百分比、再判断等级，千万别在 IF 里嵌套三遍 SUMIFS。算一次存起来，后面直接用。这就要用到下面要说的 LET 函数。

二、LET 函数：给公式起个名字，告别"复制粘贴地狱"

2.1 那个让我崩溃的公式

去年有人做了个季度考核表，写了这么个东西：

=IF(
    SUMIFS(销售!G:G, 销售!A:A, A2, 销售!B:B, B2) / 
    VLOOKUP(A2, 目标!A:B, 2, FALSE) > 1.2,
    "超额完成",
    IF(
        SUMIFS(销售!G:G, 销售!A:A, A2, 销售!B:B, B2) / 
        VLOOKUP(A2, 目标!A:B, 2, FALSE) > 1,
        "完成",
        IF(
            SUMIFS(销售!G:G, 销售!A:A, A2, 销售!B:B, B2) / 
            VLOOKUP(A2, 目标!A:B, 2, FALSE) > 0.8,
            "基本完成",
            "未完成"
        )
    )
)

SUMIFS 算了 3 遍，VLOOKUP 查了 3 遍。 公式长得看不到头，一个月后她自己都看不懂，改个条件跟拆炸弹似的。

还有一旦这种很长的公式因为什么原因报错了…你就知道检查起来有多麻烦了。

直到我用 LET 重构，才发现原来公式可以像写文章一样清晰。

2.2 LET 到底是什么？

一句话：在公式内部定义变量，后面随便用。

=LET(
    变量名1, 值1,
    变量名2, 值2,
    ...,
    最终计算
)

上面的崩溃公式，用 LET 重写后：

=LET(
    产品代码, A2,
    地区代码, B2,
    
    本月销售, SUMIFS(
        销售表!销售额,
        销售表!产品, 产品代码,
        销售表!地区, 地区代码,
        销售表!日期, ">=" & 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(结束日期<>"", "<="&结束日期, "<="&DATE(9999,12,31)),
        销售表!产品, IF(产品筛选<>"", 产品筛选, "<>"),
        销售表!地区, IF(地区筛选<>"", 地区筛选, "<>"),
        销售表!销售额, IF(最小金额>0, ">"&最小金额, ">="&0)
    ),
    
    总订单数, SUMPRODUCT(
        (销售表!日期 >= IF(开始日期="", 0, 开始日期)) *
        (销售表!日期 <= IF(结束日期="", 99999, 结束日期)) *
        (IF(产品筛选="", 1, 销售表!产品=产品筛选)) *
        (IF(地区筛选="", 1, 销售表!地区=地区筛选)) *
        (销售表!销售额 >= IF(最小金额=0, 0, 最小金额))
    ),
    
    平均单价, IF(总订单数>0, 总销售额/总订单数, 0),
    
    "销售额：" & TEXT(总销售额, "#,##0") & "元" & CHAR(10) &
    "订单数：" & TEXT(总订单数, "#,##0") & "笔" & CHAR(10) &
    "平均单价：" & TEXT(平均单价, "#,##0.00") & "元"
)

这段实现了什么？

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

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

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

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

写在最后

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

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

‍