基础语法 =SUMPRODUCT(array1,array2,array3, ...) ——SUM是求和的意思,PRODUCT是相乘的意思,参数之间相乘之后再求和,你看,SUMPRODUCT确实人如其名了。 看我手,歪、图、斯瑞……总结起来,SUMPRODUCT函数具有以下三个特点: 1> 它本身默认执行数组运算。 2> 它会将参数中非数值型的数组元素作为0处理。 3> 参数必须有相同的尺寸,否则返回错误值。 特点解析 看完了SUMPRODUCT的简历,想必很多朋友是雾里看花,仅仅对它有个模糊的认知,它的这些特点是啥意思?它到底能够胜任什么样子的工作?其实并不了然。 打个响指,我举几个例子。 如上图所示的数据表,C列是商品单价,D列是销售数量,现在需要在C9单元格计算销售总额。 C9输入以下公式,即可得出结果11620.60
这便是一个简单的SUMPRODUCT函数了。它的运算过程是:C3:C7和D3:D7两个区域数组内的元素分别相乘,也就是C3*D3,C4*D4,C5*D5……直至C7*D7 等于先将每个商品的销售金额计算出来,最后汇总求和。 由于SUMPRODUCT函数第一个特点,本身是支持数组间运算的,所以虽然该公式执行了多项运算,但并不需要按数组三键<Ctrl+Shift+Enter>结束公式输入。 有的朋友说啦,公式也可以写成这样: =SUMPRODUCT(C3:C7*D3:D7) 或者使用以下数组公式,也是可以的。
那么这三个公式之间有什么区别呢? 首先,大部分情况下,SUMPRODUCT函数都不需要数组三键结束公式输入即可执行数组运算,而SUM函数是需要的。 其次,就要说到SUMPRODUCT函数另一个非常重要的特点了。 …… 我们将上面的表稍做改动,将“钢笔”的销售数量更改为:暂未统计。同样需要在C9单元格计算销售总额。 这时候,如果使用公式: =SUMPRODUCT(C3:C7*D3:D7) 或者数组公式:
都将返回错误值#VALUE! 返回错误值的原因在于D4单元格“暂未统计”为文本值,文本值是无法直接参与数学运算的,于是C4*D4返回错误值#VALUE!,进而造成整个公式的结果返回错误值。 而使用以下公式就没有这方面的困扰,会直接返回正确结果: =SUMPRODUCT(C3:C7,D3:D7) 这便是SUMPRODUCT函数的第二个特点:将非数值型的数组元素作为0处理。 以该示例来说,D4单元格的值“暂未统计”为文本,并非数值,SUMPRODUCT将其主动视为零,于是C4*D4,结果亦为零,其余数组元素照常计算,得出11385.60的结果。 需要特别说明的是,SUMPRODUCT将非数值型的数组元素作为0处理,所谓的非数值型数组元素,包含逻辑值、文本,但并不包含错误值,如果数组元素中包含错误值,该公式亦返回错误值,比如该示例的第一条公式。 …… 说完了SUMPRODUCT函数的两个特点,我们就再来聊聊它的第三个特点:数组参数必须有相同的尺寸,否则返回错误值。 我们依然用上述图片的例题为例,继续计算商品的销售总额。如果我们在C9输入公式:
结果会是怎么样的呢? 错误值:#VALUE! 为什么? 细心的您肯定已经注意到了,两个区域数组,C3:C7明显显比D3:D6多了一个元素,C3和D3结对子,C4和D4结对子……那么C7和谁结对子呢?女人们都嫁了,结果剩下一个光棍,这日子没法过了!一个萝卜一个坑,只有萝卜没有坑,这不是要萝卜死吗? ——于是SUMPRODUCT就不高兴了,它给你一个错误值#VALUE!,明确告诉你,和谐时代幸福岁月,日子不能这么过。 这就是SUMPRODUCT函数的第三个特点:数组参数必须有相同的尺寸,否则返回错误值。 下面是一道练习题,你看看,能用SUMPRODUCT函数做出来吗? 案例拓展 ——那么,问题和广告都来了: 1 员工西门庆领取了几次工资? =SUMPRODUCT((C2:C13='西门庆')*1) 先判断C2:C13的值是否等于”西门庆”,相等则返回TRUE,不等则返回FALSE,由此建立一个有逻辑值构成的内存数组。 上文已经说过,SUMPRODUCT有一个特性,它会将非数值型的数组元素作为0处理,逻辑值自然是属于非数值型的数组元素,为了避免SUMPRODUCT函数把逻辑值视为0,造成统计错误,我们使用*1的方式,把逻辑值转化为数值,TRUE转化为1,FALSE转化为0,最后统计求和。 2 员工西门庆领取了多少工资?
依然首先判断C2:C13的值是否等于”西门庆”,得到逻辑值FALSE或TRUE,再和D2:D13的值对应相乘。TRUE乘以数值,得到数值本身。FALSE乘以数值返回0。最后统计求和得出结果。 看完了上面两个问题,有些朋友可能会在心里想,貌似SUMPRODUCT能干的事,SUMIF和COUNTIF也能做到,而且做的更好,那么还要SUMPRODUCT干啥嘞? 乡亲们呐,话不能这么说,SUMPRODUCT可是上得厅堂下得厨房,对工作环境不挑不拣,它对参数类型没有啥特别要求,COUNTIF和SUMIF就不同了,他俩要求个别参数,必须是区域(Range型),不支持数组,比如下面这两个问题,COUNTIF和SUMIF就要绕了。 3 二月份外交部发放了几次工资?总额是多少? 这是一个多条件计数的问题。 第一个条件,发放工资的时间必须是二月份;第二个条件,发放工资的部门必须是外交部。 如果使用多条件计数函数COUNTIFS,判断发放工资的时间是否属于六月份,会简单问题复杂化。而使用SUMPRODUCT函数,咱们可以把公式写成这样: =SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13='外交部')) 第2个问题,统计二月份外交部发放了多少工资? 这是一个常见的多条件求和问题。 如果使用SUMIFS函数,判断发放工资的时间是否属于六月份,也会简单问题复杂化。 SUMPRODUCT跃然而至:
或者: =SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13='财务部')*D2:D13) 打个响指,关于这两个形式的SUMPRODUCT函数的区别,咱们上文已有详细说明——你还记得吗? 上面这个公式可以说是SUMPRODUCT多条件求和的典型用法啦,可以归纳为:
4 二月份外交部和步兵部合计发放了多少工资? 我们经常见有些性格朴素的表亲们把公式写成这样: 代码如看不全,可以左右拖动..▼ =SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13='外交部')*D2:D13)+SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13='步兵部')*D2:D13) 这些表亲们估计心想,不就是计算两个部门吗?甭说两个,二十个咱也能算,一个加一个,一直加到二十个,世上无难事,只怕有心人嘛,一砖加一砖,长城咱也能垒成喽…… 公式写的那么长,先不谈计算速度,首先它累手啊,万一写错了,又要修改,那也是麻烦他妈哭麻烦——麻烦死了。 其实我们可以写成这样:
5 排名应用 如上图所示,某个月某个公司某些人领了某些工资,然后呢,他们想看看自己的工资,在部门内的排名情况,比如说步兵部的鲁智深都是老员工了,非常想知道自个工资在各自部门排几号。 当然啦,不排不知道,一排就傻掉。 SUMPRODUCT是这么解决这个问题的,D2输入公式向下复制: =SUMPRODUCT(($A$2:$A$9=A2)*(C2<$C$2:$C$9))+1 (思考,为什么公式的最后+1,而不是直接写成如下:)
结束语 唠唠叨叨说了这么多,眼睛都说酸麻了,是到了该结束的时候啦。 最后,请思考两个小问题: 第一个问题:下面SUMPRODUCT函数有几个参数? =SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13='财务部')*D2:D13) 下面这个SUMPRODUCT函数又有几个参数?
第二个问题: SUMPRODUCT为什么有时候比SUMIF/COUNTIF计算速度慢? The End
|
|
来自: hercules028 > 《excel》