大家好,SUMPRODUCT函数是一个在计算方面非常强大的函数.
这一章我们来盘点盘点SUMPRODUCT函数的一些用法,
从一定程度而言,他可以取代:
SUMIFS,COUNTIFS,AVERAGEIFS这些函数的全部用法.
其中应该有你不知道的一些搭配用法.
我都给你总结好了,可以直接套用这些方法.
目录:
1.SUMPRODUCT通用方法
2.SUMPRODUCT条件求和
3.SUMPRODUCT搭配SIGN函数
4.SUMPRODUCT中的日期判断
5.加权均值算法
6.权重算法情境
7.矩阵多条件条件求和
我们从简单的开始:
或者你也可以直接观看下方的视频解析.
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))
7.矩阵多条件核算
还有一个非常适用的情景,如下图,
我想求和:部门2,月份1,标记的颜色位置交错的结果是135和117
所以这个公式可以这么写:
=SUMPRODUCT((C1:F1=H3)*(A2:A13=H4)*C2:F13)
在同时满足表头为部门和月份列为1月交错相乘C2:F13这个范围,最后进行SUMPRODUCT函数的求和.
同样,如果是条件为部门3,产品条件为产品2
那么交错出来的结果应该是
147+179+136+155+198+149
结果为964
公式如下所示:
=SUMPRODUCT((C1:F1=H3)*(B2:B13=H4)*C2:F13)
以上.
素材下载:
复制下方文字,发送公众号信息获取课件:
SUMPRODUCT函数6种用法合集
小贴士:
之前一些课件由于时间太久失效,所以现在的下载素材方式,回复公众号信息获得,如果有一天素材失效,记得给老徐留言,我再补上.
如果想要系统学习Excel,可以看看这个链接:
更新不易!
如果喜欢老徐的内容,记得给老徐点赞关注哦!!
本章视频:
