01金额的合计
如下清单在没有汇总行时
我们用
=SUM(D2:D6)
表示金额的合计。
金额=单价*数量
在没有金额列时,我们用
=SUMPRODUCT(B2:B6,C2:C6)
表示金额的合计。
如果用前面讲的表项-结构化引用,公式会更直观
=SUM(清单[金额])
=SUMPRODUCT(清单[单价],清单[数量])
在SUMPRODUCT看来,金额是个辅助列,每行PRODUCT之后的金额,以及每个金额SUM之后的金额的合计,他一步完成了,辅助列只会影响他“出剑”的速度。
02对齐
SUMPRODUCT不限于两个参数的【单价*数量】,三个参数的【长*宽*高】,或者更多,只要不超过255个,都可以。
SUMPRODUCT(array1,[array2], [array3],[array4],...)
参数array1为单列时,后续参数也应为单列,并且行数相同,以便行对齐相乘,乘积相加。
参数为单行时,原理相同,只是相乘时列对齐。
参数为多行多列,我们在主题5介绍。
03当SUMIFS用
SUMIF可拆解为SUM IF,通过对齐替换,不符合条件的值被替换为空。
类似的,SUMPRODUCT对齐相乘,如果TRUE和FALSE分别对应1和0,利用【1*N=N】和【0*N=0】两个特性,“辅助列”不符合的项以0参与计算,不影响累计结果。
用SUMPRODUCT函数
=SUMPRODUCT(条件求和[生产总值],(条件求和[城市]="深圳")*1)
替换SUMIFS函数
=SUMIFS(条件求和[生产总值],条件求和[城市],"深圳")
在SUMPRODUCT中,TRUE不能自动按【1】计算,需【*1】或【 0】转为【1】,多条件可以在一个array参数中以【(…)*(…)】的方式实现,也可以一个array判断一个条件。
相比SUMIFS,SUMPRODUCT允许“条件区域”和“汇总区域”为数组,从这个角度讲,他是SUMIFS的另一个SUM IF.
04一参SUMPRODUCT
公式
=SUM(B2:B6*5)
对区域直接乘积,在支持数组扩展的2021版和365,能得到正确结果。
在未支持数组扩展的版本,未使用数组三键录入,会显示为错误值#VALUE.
而高版本用户录入的公式,低版本用户打开时,则自动转化为数组公式,但低版本用户编辑后,仍需使用数组三键。
使用SUMPRODUC录入则不会有该问题
=SUMPRODUCT(B2:B6*5)
公式兼容性更好。
05多行多列
假设array1为r行c列的区域
记array1的第1行第2列为array1_1_2,……,依此类推,第r行第c列的单元格记为array1_r_c
后续的array2,array3,……arrayn也是此规则编号。
记arrayPro_r_c=array1_r_c*array2_r_c*……*arrayn_r_c
记arrayPro=array1*array2*……*arrayn
SUMPRODUCT(array1,[array2], [array3],[array4],...)
=SUM(arrayPro)
SUMPRODCT除了只有array1的情况外,同一个array内的项不会相加,也不会相乘,他们与其他array的项对齐相乘,再将乘积相加。
SUMPRODUCT通常无法返回一个数组结果,那件事得由MMULT来干。
示例文件:https://pan.baidu.com/s/1PyDj6Hw2QdslVzg9Pcff4Q?pwd=ez15
欢迎点赞-转发-关注,我们下次见。