Excel表格函数学习,SUMPRODUCT函数的进阶操作技巧播

之前给大家介绍了SUMPRODUCT函数的基础用法,今天再给大家介绍下它的进阶用法,让你看看什么是万能的公式

首先再回顾下SUMPRODUCT函数基本函数语法结构

SUMPRODUCT(array1, [array2], [array3], …)

array1:必需。其相应元素需要进行相乘并求和的第一个数组参数。

array2, array3,…:可选。2 到 255 个数组参数,其相应元素需要进行相乘并求 和。

函数定义为在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。

下面开始介绍其相关的使用技巧

01

单条件计数

当我们对数据进行计数的时候,我们首先想到的肯定是Countif函数,但你知道SUMPRODUCT函数也是可以实现计数的

比如如下,我想统计每个部门的人数,我们可以在G2单元格输入公式

=COUNTIF(B:B,F2)

图片[1]-Excel表格函数学习,SUMPRODUCT函数的进阶操作技巧播-职比牛办公资源网

使用SUMPRODUCT函数怎么实现呢,同样我们可以在G2输入公式

=SUMPRODUCT(($B$2:$B$11=F2)*1)

图片[2]-Excel表格函数学习,SUMPRODUCT函数的进阶操作技巧播-职比牛办公资源网

公式解释:

1.第一步首先是输出B2:B11的值

图片[3]-Excel表格函数学习,SUMPRODUCT函数的进阶操作技巧播-职比牛办公资源网

2.然后计算B2:B11=F2判定得出的结果是True还是False,True即为1,False即为0

图片[4]-Excel表格函数学习,SUMPRODUCT函数的进阶操作技巧播-职比牛办公资源网

3.然后判定的结果和1相乘,即得到如下结果

图片[5]-Excel表格函数学习,SUMPRODUCT函数的进阶操作技巧播-职比牛办公资源网

4.然后对数组内的数据相加,即得到最终结果

02

多条件求和

如果我想求不同部门的男生数量,要怎么写公式呢?

我们可以使用Countifs函数进行多条件计数,公式如下:

=COUNTIFS(B:B,F2,D:D,”男”)

当我们使用SUMPRODUCT函数,可以在G2单元格输入如下公式:

=SUMPRODUCT(($B$2:$B$11=I2)*(($D$2:$D$11)=”男”))

公式讲解参考技巧01,简单来讲就是满足条件的是True即为1相乘后再加总

图片[6]-Excel表格函数学习,SUMPRODUCT函数的进阶操作技巧播-职比牛办公资源网

总结来讲,和SUM函数计数类似,同样的如果你有多少条件就乘以多少次

=SUMPRODUCT(条件1*条件2*条件3*…)

03

单条件求和

在讲SUM函数求和的时候,我们讲到可以使用SUM函数进行条件求和,其实SUMPRODUCT函数也是可以实现的

比如如下我们要统计销售额>=1000的和,我们可以在G2输入公式:

=SUMPRODUCT((D2:D11>=10000)*D2:D11)

图片[7]-Excel表格函数学习,SUMPRODUCT函数的进阶操作技巧播-职比牛办公资源网

公式运行的方法同01,先判断然后相乘再加总

图片[8]-Excel表格函数学习,SUMPRODUCT函数的进阶操作技巧播-职比牛办公资源网

05

多条件求和

同样根据上面的数据,如果我统计销售1部,销售额>=1000的和,要怎么计算呢

我们可以在G2输入公式:

=SUMPRODUCT((B2:B11=”销售1部”)*(D2:D11>=10000)*D2:D11)

运行结果如下:

图片[9]-Excel表格函数学习,SUMPRODUCT函数的进阶操作技巧播-职比牛办公资源网

总结来讲,条件求和就是,条件和数据列乘积

=SUMPRODUCT((条件1)*(条件2)*(数据1)*…)

06

计算不重复数

当我们计算一列人员数据非重复人员的数量的,我们可以使用【数据】-【删除重复值】,然后再统计数据,现在我们使用SUMPRODUCT函数,要怎么实现?

首先在D2输入公式:

=SUMPRODUCT(1/COUNTIF(B2:B11,B2:B11))

图片[10]-Excel表格函数学习,SUMPRODUCT函数的进阶操作技巧播-职比牛办公资源网

当人员名字变更的时候,人员数量也会根据实际进行变动

总结

SUMPRODUCT函数除了以上的功能,还可以实现区间求和,隔列求和,还可以实现有条件的数组求乘积

公式填写的时候满足此格式

=SUMPRODUCT((条件1)*(数据1)*(数据2)…)

从上面我们计算可以看出,其实通过其他函数比如Countif,Countifs,或者Sum函数Sumif函数也可以实现,但这一个函数可以实现其他函数的所有功能~

另外上面提到的几种SUMPRODUCT实现的功能,大家可以手动练习下哦~

© 版权声明
THE END
喜欢就支持一下吧
点赞0分享
评论 抢沙发
头像
欢迎您留下宝贵的见解!
提交
头像

昵称

取消
昵称表情代码图片

    暂无评论内容