SUMPRODUCT函数是Excel当中最为高级的一个函数之一,它拥有多项技能,无论是数据的求和、计数、还是权重计算以及排名,用这个函数都能一次性帮你搞掂!Sumproudct函数,从名字上来看,它就分为两个部分,一个是求和,另外一个是乘积。它的具体运算规则是:在给定的几组数组中,将数组间对应的元素先相乘(PRODUCT),后相加(SUM)。它的语法表达式是:SUMPRODUCT(array1, [array2], [array3], ...)其中,array1是必需的参数,代表相应元素需要进行相乘并求和的第一个数组参数。在下面这个销售表当中,我们要计算这些商品的总收益,计算的方式就是先用销售乘以单价,计算出每个产品的总价。计算出总结后,再对每个产品的收益进行相加,最后得到的才是这批商品的总收益。但利用Sumproduct函数就能非常轻易地解决这个问题。我们在E43单元格录入函数公式:=SUMPRODUCT(E35:E40,F35:F40)该函数公式的运算逻辑,其实跟前面我们分开做的步骤是一样的:先用每个产品的销量乘以单价,得到每个产品的收益,之后再把每个产品的收益加起来,就得到总收益。我们要统计工龄大于4年,年龄大于30岁的员工有多少个,一般来说我们就需要用到多条件计数函数COUNTIFS了。但其实我们用SUMPRODUCT函数也能够实现多条件计数。我们这里一共有两个条件需要进行判断,一个是工龄,一个是年龄。 =SUMPRODUCT(($D$53:$D$61>4)*($E$53:$E$61>30))$D$53:$D$61>4代表,D53到D61区间的数值大于4,如果正确则返回“TRUE”(相当于1),不正确则返回'FLASE'(相当于0)$E$53:$E$61>30代表,E53到E61区间的数值大于30,如果正确则返回“TRUE”(相当于1),不正确则返回'FLASE'(相当于0)然后两个数组分别相乘,再相加,那么就得到下面这个结果。 如上图所示,我们需要计算每个季度计划和实际的总额,但这里面的计划和实际是分开两列来显示的。我们以往在进行计算的时候,可能需要用到SUMIF,条件求和函数来进行统计,但其实利用SUMPRODUCT函数同样有效。=SUMPRODUCT(($C$3:$J$3=K$3)*$C4:$J4)其中,($C$3:$J$3=K$3)表示判断C3到J3这一行的数值,那些是等于K3单元格计划的。如上图所示,我们把这部分公式单独进行运算,那么在12行就能得到判定的结果。如果$C$3:$J$3这一行的数据是等于K3,那么就返回TRUE,否则返回FALSE。接着($C$3:$J$3=K$3)*$C4:$J4,后面的乘以C4:J4表示对办公桌的数据进行求和运算。在求和运算时,依然时保持先计算乘法,再计算加法的原则。 也就是分别用前面判断出来的结果(第13行的数值),分别乘以办公桌一行的数值(第15行),得到第17行的结果,接着再对第17行的结果进行求和运算。统计出一个结果之后,我们往下拖拉公式,往右拖拉公式,那么就可以得到其他结果出来。 如下图所示,某公司在招聘的时候,需要对面试者的情况做一个统计,考核分为三个环节,笔试、面试、健康,其中笔试占比30%,面试占比50%,健康占比20%。在以往统计的时候,我们需要用笔试得分乘以笔试所占的比重,再加上面试的得分乘以面试所占的比重,再加上健康的得分乘以健康的比重,得到最终的得分。现在利用SUMPRODUCT函数,在F5单元格录入函数公式:=SUMPRODUCT($C$4:$E$4,C5:E5)就能帮你完成先计算乘法,再计算加法的运算,一步到位,简单快捷又高效!以上就是SUMPRDUCT函数在实际过程当中的运用,下面我们来看看使用SUMPRODUCT函数过程中的常见错误。如下图所示,如果E5单元格的格式为文本格式,那么在实际计算的时候,是不会把文本型的数值计算到的,所以导致最后F5的结果错误了。在实际计算过程中,需要先排除文本型的数字格式,将其转换为数字格式再进行运算。
|