供应链实战之EXCEL技能——SUMIFS:多条件求和——计划人员必须掌握的效率提升器(下篇)

场景1：同时统计多个产品的求和，使用通配符"*"

业务问题：以下为某一家电公司的销售报表部分数据。现在需求计划人员需要快速统计不同要求的销量，比如分地区，产品。表格数据如下：

问题如下：

    1)中国东北、华北和西北三个北方地区电冰箱销售额？

    2)中国大陆地区型号“电冰箱_5000”销售量？

    3)所有出口产品的销售额？

问题一的解决方法：我们当然可以根据上篇所说的最直接简单最笨的方法，通过三个SUMIFS求和函数把中国华北地区，东北地区和西北地区加起来。

但是如果我们知道如何使用通配符“*”，一个SUMIFS函数就够了。公式为：

=SUMIFS($G$2:$G$21,$B$2:$B$21,"中国*北",$A$2:$A$21,"电冰箱*")。

如果需要日期范围，可以加上日期范围。

同样的方法简单解决第二个问题。公式为：

=SUMIFS($G$2:$G$21,$B$2:$B$21,"中国*",$A$2:$A$21,"电冰箱_5000")。

第三个问题看似复杂一些，似乎要把几个国外的地区销售额加起来，但是也是一个SUMIFS可以搞定。不信你看公式：

=SUM($G$2:$G$21)-SUMIFS($G$2:$G$21,$B$2:$B$21,"中国*")。

通配符除了"*"，还有"? "，"*" 不限定字符个数，一个"*"代表所有字符，也可以不包含字符，而一个“? "有且只能代表一个字符，所以如果需要查找某些数据，不知道具体描述但是知道具体的字符个数，使用"? "就是最好的方法。比如下面有一组描述，我们需要只选有S090~S290_MF?_，也就是"90"前面必须有一个数字，"MF"后面只有一个字母的型号的2024年的数量。

公式：

=SUMIFS(C2:C12,B2:B12,"S?90_MF?_*",D2:D12,2024)

对于时间，我们可以使用原来的日期格式，">="&date(2024,1,1), 和 "<"date(2025,1,1)，也可以增加一列辅助列“年”,通过year(date)得出年份，D列，这样更简单。

注意"?"与"*"的区别，"?"限定了90前面必须有且只有一个字符，如果使用"*"，则不限定字符，可以没有字符，两个公式的结果完全不同。

业务问题：有十个型号，关于螺丝的各种标准件，从A到K，而我们只要求其中的B，D，F，I四个螺丝产品的6月份用量。产品描述是乱的，不能使用通配符“*”，使用一个一个加起来显然也不是最佳的方法。

这个时候，我们就需要使用到数组公式。

数组公式是可以对数组中的一个或多个项执行多个计算的公式，可以将数组视为值的行或列，也可以将数组视为值行和列的组合，数组公式可以返回多个结果。

数组公式的标志：在Excel中数组公式的显示是用大括号 “{}”来区分普通Excel公式。

数组公式需要同时按“Ctrl+Shift+Enter”三个键，当按下这三个键后，Excel会自动给公式加上“{}”，通俗点说，数组就是集合，数组公式就是对集合进行计算。

需要注意的是，数组公式不能单独编辑、清除或移动数组公式所涉及的单元格区域中的某一个单元格。(Office365已经对此做了改进，也可以删除或者修改其中的某个单元格)。

公式如下：

={SUM(SUMIFS($D$2:$D$16, $B$2:$B$16, {"镀锌螺丝-B","不锈钢螺丝-D ","内六角螺丝-F","外六角长螺丝-I"}, $A$2:$A$16,">=2026/6/1", $A$2:$A$16,"<=2026/6/30"))}。

输入SUM(SUMIFS(---)公式后，按住“Ctrl+Shift”加“Enter”回车，就得到公式外面的两个大括号{}，而不需要手工输入。

这个数组公式原理是：

- SUMIFS的产品条件参数{"镀锌螺丝-B","不锈钢螺丝-D ","内六角螺丝-F","外六角长螺丝-I"} 是一个数组常量，在非数组公式中，只相当于一个产品，比如“产品A”。

-日期条件不变($A$2:$A$16,">=2026/6/1",$A$2:$A$16,"<=2026/6/30")，四个产品使用同一个日期条件。

- SUMIFS 会对数组中的每个元素分别计算求和，返回一个数组，例如{500,0,1200,0}。

-外层的SUM将这个数组求和，得到最终结果 1700。

应用价值：当需要统计的产品/产线/供应商/仓库/SKU等较多时而又不规则不能使用通配符时，使用数组公式一次性完成，无需多个公式相加。

*注意 Excel 2021/365 支持动态数组，可直接回车；早期版本需按三键。

 03

SUMIFS的条件本身是表达式

我们继续以家电公司的销售报表为例。

要求统计大于所有地区平均销售额的国内各地区的销售金额总和。

所有地区平均销售额我们可以使用average这个函数，所有地区包括了国外地区，所以是G列的所有销售额，average(G2:G21)，我们不必将这个平均值写出来，而是可以直接将这个函数写入表达式中，用法与日期的函数的表达式致，需要用">"和"&"连起来。

公式：

=SUMIFS(G2:G21,B2:B21,"中国*",G2:G21,">"&AVERAGE(G2:G21))

所有地区大于平均销售额的有7个记录，但是国内大于平均销售额的记录只有4个，通过地区条件"中国*"将四个求和可得到相应的正确结果。

假设今天是 2026年7月10日（系统日期），我们需要自动统计 2026年6月1日 至 2026年6月30日洗衣机的销量。灵活使用eomonth，today等函数。

=SUM(SUMIFS(C2:C8,B2:B8, "洗衣机", A2:A8,">" &EOMONTH(TODAY(),-2) +1,A2:A8,"<="&EOMONTH(TODAY(),-1)))

today()今天日期函数，返回系统当日的日期，今天为2026年7月10日，EOMONTH(A5,-2)，-2减去2个月即为2026年5月份，返回2026年5月31，"+1" 加一天，变成2026年6月1，EOMONTH(today(),-1)，-1减去1个月即为2026年6月份，返回2026年6月30。

SUMIFS可以结合的函数还有其他很多，我们无法一一举例，重要的是如何灵活应用。

很多读者可能EXCEL不太熟练，特别恐惧写超长公式和多个公式组合起来的复杂超长公式，所以当 SUMIFS 遇到无法解决的复杂逻辑时，可以不用硬写超长公式及数组公式等。可以考虑增加辅助列，或者切换到更高级的函数SUMPRODUCT等，往往能让问题变得清晰高效。增加辅助列并不丢人，我经常为客户设计一些实用的表格公式，除了以上的SUMIFS公式和函数的组合，也经常使用辅助列，我尽量不使用宏、VBA这类复杂高级的功能，因为大部分供应链从业者没有时间来学习和研究VBA这类复杂的语言，也因为表格和条件经常是会变的，一旦变动，这些宏什么的可能就无法使用了，所以表格函数的公式是越简单越好。

SUMIFS 可以简单、快速地实现多条件求和，不规则条件求和，公式易读易懂，快速上手。

但当公式开始出现 SUM(SUMIFS(...))嵌套、或需要按三键的组合公式时，或者更复杂的条件需要更多函数组合，而单独的SUMIFS难以满足时，我们可能需要停下来想一想：是不是该用辅助列了？是不是有更高级的函数或者方法？

没错，对于这种情况，我们可能需要用升级版函数——SUMPRODUCT来求解。

SUMPRODUCT = SUM + PRODUCT，即，SUM求和+PRODUCT乘积(此处Product的意思是乘积而非产品)。

SUMPRODUCT是比SUMIFS更强大的函数，用途更加广泛，当然它们的用法所针对的场景也有所不同。

想知道SUMPRODUCT 的用法，请继续关注公众号的后续文章。