首页 > Excel专区 > Excel函数 >

excel Sumproduct函数高级用法 一个Excel函数搞定多条件计数、求和

Excel函数 2021-06-06 17:40:20

很多朋友知道,Excel里的Sumproduct函数是一个乘积求和函数,即两列数值相应乘积之和。

其基本用法前面我们已经讲解过了,本期给大家讲解一下sumproduct函数那些令人意想不到、功能却令人惊叹的的高级用法。

一、基本用法(详情请看Excel常用函数—sumproduct函数

1. 普通求和

当sumproduct函数的参数只有一个区域(数组)时,那就没有了相应数值的乘积,得到的是该区域之和。

Sumproduct高级用法:一个Excel函数搞定多条件计数、求和

如上图,当只有一个区域时,使用sumproduct和sum函数得到的值是一样的。

2.乘积之和

Sum(和)+product(乘积)=sumproduct(乘积之和)

各产品的总销售额=sumproduct(B2:B8,C2:C8)

Sumproduct高级用法:一个Excel函数搞定多条件计数、求和

其实,sumproduct除了上面这种用法,还可以使用"*",如上题各产品的总销售额=sumproduct((B2:B8)*(C2:C8)),所求得的值一样的,大家可以试一下。

同理,当参数是三个或多个区域(数组)时,得到的是相对应数值的乘积之和。

二、高级用法

1.单条件计数

如下图,要求总分大于等于180的人数,可以使用countif和sumproduct函数实现。

=COUNTIF(E2:E9,">=180")

=SUMPRODUCT((E2:E9>=180)*1) "*1"将其转换为数组,才可以参与计算。

Sumproduct高级用法:一个Excel函数搞定多条件计数、求和

2.单条件求和

如下图,要求总分大于等于180的分数之和,可以使用sumif和sumproduct函数实现。

=SUMIF(E2:E9,">=180",E2:E9)

=SUMPRODUCT((E2:E9>=180)*E2:E9)。

Sumproduct高级用法:一个Excel函数搞定多条件计数、求和

3.多条件计数

提到"多条件计数",大家第一反应就是"countifs"函数了,没错。不过sumproduct函数也是可以实现该功能的,具体如下:

Sumproduct高级用法:一个Excel函数搞定多条件计数、求和

上图是个人语文、数学、英语及总分成绩,如果我要求语文、数学、英语三门都及格(>=60)的人有多少。

Countifs函数:=COUNTIFS(B2:B9,">=60",C2:C9,">=60",D2:D9,">=60"),得到3。wps下一页如何增加_wps下一页增加的方法

sumproduct函数:=SUMPRODUCT((B2:B9>=60)*(C2:C9>=60)*(D2:D9>=60))

"*"在这里起到"and"的作用,表示三门成绩都大于等于60的个数。

Sumproduct高级用法:一个Excel函数搞定多条件计数、求和

从这个例子可以看到,sumproduct还有这种作用,对多个条件计数时,如果一个条件成立则为1,不成立则为0,最后将所有值相加即为所求值:

=sumproduct((区域1=条件1)*(区域2=条件2)*(区域3=条件3)...),其中等号"="还可以是">""<""<>""<="">="等。

4.多条件求和

如下图,要求语文、数学、英语及格的分数之和(有一门及格就行)

=SUMPRODUCT((B2:B9>=60)*(B2:B9)+(C2:C9>=60)*(C2:C9)+(D2:D9>=60)*(D2:D9))

以上公式的含义:(B2:B9>=60)*(B2:B9),条件乘以分数,即语文及格的分数,三者相加得到三门科目及格的分数之和。

Sumproduct高级用法:一个Excel函数搞定多条件计数、求和

好了,以上就是sumproduct函数的高级使用方法,可以实现单条件计数、单条件求和、多条件计数、多条件求和,你学会了么?


标签: excel函数高级用法一个搞定

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.