Excel函数之——SUMPRODUCT函数太强大了



Excel学习与提高 2017-12-30 16:29:31

Excel中的SUMPRODUCT()函数是个乘法累加函数,其实这个函数还有查询,统计,条件统计等各种用法,今天就跟大家分享下这些用法。

Excel函数之——SUMPRODUCT函数太强大了

1,SUMPRODUCT函数基本用法

先介绍下SUMPRODUCT函数的基本用法

格式:SUMPRODUCT(array1,[array2], [array3], ...)

该函数可以有多个参数,但只要第一个参数是必须的,其余的参数都可以省略。

每个参数都必须是有相同维度的数组。

返回的结果是,将各数组中相应位置的数字相乘,再将这些结果累加后返回

具体操作如下:

Excel函数之——SUMPRODUCT函数太强大了

2,单个条件计数

由于该函数有一个特点,就是只有第一个参数是必须的,所以,利用这个特点,可以实现条件计数

以下是单条件计数

在E2单元格,使用的公式为:

“=SUMPRODUCT(N(B2:B7=D2))”

其中“B2:B7=D2”,返回的是一个数组,数组中的元素是“TRUE”或“FALSE”, 满足条件的是“TRUE”, 不满足条件的是“FALSE”

在使用N()函数,将“TRUE”转换成“1”,“FALSE”转换成“0”,

最后,将N()返回的数组中的所有元素,即所有的“1”和“0”,累加后,返回,即得到了满足条件的个数

具体操作如下:

Excel函数之——SUMPRODUCT函数太强大了

3,多条件计数

多条件计数和单条件计数的思路是一样的。

如下面这个例子

在F2单元格使用的公式为:

“=SUMPRODUCT((B2:B7=E2)*(C2:C7>80))”

表示统计三班,分数大于80的人数

两个条件分别是“B2:B7=E2”和“C2:C7>80”,中间使用乘法“*”运算

由于使用了乘法运算,结果自动转换成数字,所以就不在需要使用N()函数了

具体操作如下:

Excel函数之——SUMPRODUCT函数太强大了

4,多条件统计-1

在F2单元格使用公式:

“=SUMPRODUCT((B2:B7=E2)*(C2:C7>80),C2:C7)”

表示,计算三班中分数大于80分的同学,他们的总分,并返回

公式中“(B2:B7=E2)*(C2:C7>80)” 返回的是满足条件的一个数组,这个数组是有“1”和“0”组成,其中“1”表示满足条件,“0”表示不满足条件,再将这个数组与C2:C7(即分数数组)相乘累加后返回

具体操作如下:

Excel函数之——SUMPRODUCT函数太强大了

5,多条件统计-2

下面这个例子也是多条件统计,与上面不同的是,需要相乘的数组多了一个。

C12单元格使用的公式为:

“=SUMPRODUCT((A2:A9=A12)*(B2:B9=B12),C2:C9,D2:D9)”

SUMPRODUCT函数的参数可以有多个,最多是255个。

具体操作如下:

Excel函数之——SUMPRODUCT函数太强大了

6,跨列求和

SUMPRODUCT函数也可以用来进行跨列求和

H3单元格使用的函数为:

“=SUMPRODUCT(($B$2:$G$2=H$2)*($A3=$A$3:$A$7)*$B$3:$G$7)”

其中这也是多条件统计,公式中的两个条件分别是

“$B$2:$G$2=H$2”,和 “$A3=$A$3:$A$7”

公式中的用到了两个“*”乘号,其中最后一个“*”改用逗号“,”,也是一样的。

具体操作如下:

Excel函数之——SUMPRODUCT函数太强大了

7,生成二维汇总数据表

这个例子与上面的例子类似,也是多条件统计

F2单元格中,使用的公式为:

“=SUMPRODUCT(($A$2:$A$9=$E2)*($B$2:$B$9=F$1),$C$2:$C$9)”

具体操作如下:

Excel函数之——SUMPRODUCT函数太强大了

8,自动生成排名

D2单元格使用的公式为:

“=SUMPRODUCT((C2<$C$2:$C$7)*1)+1”

其中“SUMPRODUCT((C2<$C$2:$C$7)*1)” 返回的是,分数大于自己的人数,最后在+1,就是排名了。

具体操作如下:

Excel函数之——SUMPRODUCT函数太强大了

以上就是Excel中SUMPRODUCT()函数的用法了,如果有不明白的就留言吧。别为了点赞啊。


0