|
大家好,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))
素材下载: 复制下方文字,发送公众号信息获取课件: SUMPRODUCT函数6种用法合集
|
|
|
来自： hercules028 > 《excel》
