天天都在用的SUMIF函数,这个小技巧,你敢说一定会吗?

 首页     |      2021-05-11 16:37
本文作者:小爽本文编辑:竺兰、小胖大家好,我是继续在研究各种函数用法的小爽~我们知道,完成一道工序,需要多个步骤。比如下面这个小伙伴是加工行业的 HR,他记录了每个操作员,在每道工序上,对应的工时情况的表格数据。现在他需要求出,每个人对应的总工时是多少,以方便结算工钱。

图片

我将数据模拟了一下,如下图所示,现在我们需要求对应的总工时数。

图片

既然要求总工时数,总数我们可以使用 SUM 函数。

图片

直接粗暴的做法:就是用数组直接判断进行求和。公式如下:
=SUM(($C$2:$C$12=I2)*($B$2:$B$12),($E$2:$E$12=I2)*($D$2:$D$12),($G$2:$G$12=I2)*($F$2:$F$12))
▲ 左右滑动查看

图片

咋一看,公式好长,好复杂的样子!其实仔细一看,就是一组组数据的判断。($C$2:$C$12=I2)*($B$2:$B$12)C 列中的操作员与小爽进行判断,形成 TRUR 和 FALSE 的数组,最后与工序 1 的 B 列相乘得到工序 1 的工时结果。($E$2:$E$12=I2)*($D$2:$D$12)E 列中的与小爽进行判断,形成 True 和 False 的数组,最后与工序 2 的 D 列相乘得到工序 2 的工时结果。($G$2:$G$12=I2)*($F$2:$F$12)G 列中的与小爽进行判断,形成 True 和 False 的数组,最后与工序 3 的 F 列相乘得到工序 3 工时结果。最后用 SUM 函数对三个工序进行求和,即可得到小爽的总工时了。利用 SUM 函数的做法,优点就是简单粗暴直接,但是很明显的缺点就是运算量大,数据容易卡顿。对于这个问题,我们还可以用 SUMIF 函数直接搞定!

图片

图片

SUMIF 函数错位求和公式如下:
=SUMIF($C$2:$H$12,I2,$B$2:$G$12)

图片

SUMIF 函数的用法很简单,按照指定条件求和。=SUMIF(条件区域,条件,求和区域)❶ 参数 1:条件的判断区域,如 $C$2:$H$12;❷ 参数 2:要符合的条件,如 I2;❸ 参数 3:要求和的区域,如 $B$2:$G$12。由于 SUMIF 函数允许条件区域和求和区域设置多行多列,所以案例中,我们可以用 SUMIF 直接进行判断求和。当条件区域和求和区域为多行多列时,SUMIF 函数会依次判断条件区域是否满足条件。如果满足条件,则将求和区域对应的位置的数据相加,得到最后的结果。如下图,我们将公式中的条件区域和求和区域,进行比对,条件为小爽,从条件区域中的第一列开始判断。如果符合条件,则返回对应的求和区域(下表)的位置。

图片

条件区域中所有列比对完毕,最后将求和区域中符合条件的对应位置进行相加,可以看到 13 13 20=46,也就是最后的结果。

图片

理解了 SUMIF 函数公式的思路,其实我们也不难发现:用 SUMIF 函数的解决思路,与我们前面所讲的数组判断返回对应值,最后用 SUM 函数求和的思路,本质是一样的。SUMIF 函数错位的用法,还有一个典型的应用,就是求最后一次出现的数值(要求数值连续)。

图片

SUMIF 函数错位延伸应用比如下面这两个例子:

图片

我们来看看公式,公式如下:
=SUMIF(B2:F2,'',A2:E2)

图片

=SUMIF(B12:B16,'',B11:B15)

图片

思路解析:
=SUMIF(B2:F2,'',A2:E2)
我们将公式对应的条件区域和求和区域,复制粘贴到一个空白区域中,以便来观察它们的规律。

图片

上述公式中条件为空(''),对应的求和区域就是 24。我们可以发现,条件区域和求和区域错位后,我们要找的最后一个数据,在求和区域上,对应条件区域上第一个空单元格。所以我们将条件设置为「空」,查找「条件区域为空对应的求和区域位置上的数值」,并求和。

图片

图片

总结一下❶ 我们可以利用数组依次判断返回对应数值进行求和,本质跟 SUMIF 函数用法思路一致。❷ SUMIF 函数允许我们设置多行多列,在计算的时候,会一一对应符合条件的求和区域的位置的数据,从而实现多行多列的按条件查找。❸ 利用 SUMIF 函数错位,还可以求最后一次出现的数字。

图片

图片