大家好,SUMPRODUCT函数是一个在计算方面非常强大的函数.
这一章我们来盘点盘点SUMPRODUCT函数的一些用法,
从一定程度而言,他可以取代:
SUMIFS,COUNTIFS,AVERAGEIFS这些函数的全部用法.
其中应该有你不知道的一些搭配用法.
我都给你总结好了,可以直接套用这些方法.
目录:
1.SUMPRODUCT通用方法
2.SUMPRODUCT条件求和
3.SUMPRODUCT搭配SIGN函数
4.SUMPRODUCT中的日期判断
5.加权均值算法
6.权重算法情境
我们从简单的开始:
1.SUMPRODUCT普通用法
如下面有一份数量单价的表格,通过数量*单价算出总计.
最后对每个产品的总计求和:
同时你也可以使用SUMPRODUCT函数:
=SUMPRODUCT(B2:B7,C2:C7)
其内置算法拆开如下:
=B2*C2+B3*C3+B4*C4+B5*C5+B6*C6+B7*C7
这个函数你也可以这么去理解:
SUM是求和的意思.
PRODUCT是乘积的意思.
先PRODUCT然后再求和.
2.替代SUMIFS条件求和
如同样是下面这个表格,如果我要满足两个条件:
1.单价>15
2.数量>100
来进行条件求和,用SUMIFS函数如下:
注意:SUMIFS函数必须是要算出总计列的情况下,用总计列作为求和区域进行计算.
如果使用SUMPRODUCT,可以不用考虑总计列,直接进行计算如下:
=SUMPRODUCT((B2:B7>100)*(C2:C7>15),B2:B7,C2:C7)
这个公式的运行逻辑如下:
首先:判断结果是TRUE或者FALSE,TRUE=1,FALSE=0
依据上述的线条相互乘积的结果:
{0;0;1;1;1;0}
然后再融入SUMPRODUCT函数,再次进行交错相乘之后的相加:
如上方图片的最后一步:
0*80*20+0*90*15+.......
最后的结果就是满足条件的求和结果.
不需要借助总计列.
3.SUMPRODUCT或的关系表达
这个中间我相信有人不清楚SIGN函数的用法.
如下图,我如果想表达两个条件:
1.单价>20
或者
2.数量>100
满足条件的求和:
这个函数就已经超出了SUMIFS函数的使用范围了.
步骤解析:
SIGN函数的意义就在于:把大于1的数值变成1.
4.日期判断
在SUMPRODUCT函数使用过程中,特别是日期相关的判断求和,最容易犯错的就是这个位置:
如下方需要对1月5号之后进行条件求和.
你应该套DATE函数,而不应该在条件判断中写:2023/1/5.
5.加权均值
如果你想通过数量和单价算出加权均值,可以操作公式如下:
6.权重求和计算
这个也是一个非常常见的案例,
例如N个学员,考试5个科目,每一个在总分的占比都不一样.
这个权重如果是纵向表格,你就应该使用公式如下所示:
=SUMPRODUCT(B2:F2,TRANSPOSE($J$6:$J$10))