首页 > 其他分享 >万能函数SUMPRODUCT超实用的10种经典用法

万能函数SUMPRODUCT超实用的10种经典用法

时间:2023-06-10 19:01:38浏览次数:47  
标签:10 FALSE 函数 TRUE 万能 E4 D4 SUMPRODUCT

Excel函数100问

Excel员工管理信息+进销存+应收账款+工资管理等系统课程

Excel中有不少万能函数,一个函数能顶多个函数,例如VLOOKUP、OFFSET、SUBTOTAL、AGGREGATE、SUMPRODUCT等。它们各有专长,功能都非常强大,且受人追捧,今天Excel办公小课堂来给大家介绍其中的SUMPRODUCT函数,朴实低调,不为大多数人所知,但却同样拥有超强的能力!一共整理了10种经典案例,非常全面,建议先收藏,不然到时候需要就找不到了哦~

SUMPRODUCT函数:返回相应的数据或区域乘积之和

功能:SUMPRODUCT(数组1,数组2,...)

一、SUMPRODUCT函数第1种用法:基础用法,乘积求和

目的:根据每个商品的数量和单价,计算所有商品的总计

公式:=SUMPRODUCT(C4:C12,D4:D12)

万能函数SUMPRODUCT超实用的10种经典用法_数据

说明:SUMPRODUCT函数(C4:C12,D4:D12)表示两组数据相乘,然后求和汇总。

二、SUMPRODUCT函数第2种用法:单条件求和

目的:根据每个商品的数量和单价,计算行政部使用各商品总计

公式:=SUMPRODUCT((C4:C12="行政部")*(E4:E12)*(F4:F12))

万能函数SUMPRODUCT超实用的10种经典用法_Excel函数_02

说明:SUMPRODUCT函数(C4:C12="行政部")表示在C4:C12区域查询“行政部”,如果此区域有包含行政部,则通过行政部中数量列和单价列的数据进行相乘,也就是(E4:E12)*(F4:F12),最后进行求和汇总。

三、SUMPRODUCT函数第3种用法:多条件求和

目的:根据每个商品的数量和单价,计算行政部6月份使用各商品总计

公式:=SUMPRODUCT((C4:C12="行政部")*(D4:D12="6月")*(E4:E12)*(F4:F12))

万能函数SUMPRODUCT超实用的10种经典用法_Excel函数_03

说明:SUMPRODUCT函数(C4:C12="行政部")*(D4:D12="6月")表示在C4:C12区域查询“行政部”,D4:D12区域查询“6月”如果两个区域同时包含行政部和6月,则通过行政部和6月的数量列和单价列数据进行相乘,也就是(E4:E12)*(F4:F12),最后进行求和汇总。

四、SUMPRODUCT函数第4种用法:单条件计数

目的:根据两个班级总人数,统计男同学人数

公式:=SUMPRODUCT(N(D4:D13="男"))

万能函数SUMPRODUCT超实用的10种经典用法_SUMPRODUCT函数_04

说明:SUMPRODUCT函数N(D4:D13="男")表示在D4:D13区域计算性别为“男”的个数,其中“性别”列是文本形式,则需嵌套N函数,表示返回转化为数值后的值,从而统计男同学的人数。

五、SUMPRODUCT函数第5种用法:多条件计数

目的:根据两个班级总人数,统计80分以上女同学人数

公式:=SUMPRODUCT((D4:D13="女")*(E4:E13>80))

万能函数SUMPRODUCT超实用的10种经典用法_数字转换_05

说明:SUMPRODUCT函数(D4:D13="女")表示在D4:D13区域查询性别为女的数据,(E4:E13>80)表示在E4:E13查询成绩大于80的数据,然后将两则数据进行相乘,从而统计80分以上女同学的人数。

六、SUMPRODUCT函数第6种用法:不间断排名

目的:根据两个班级学生成绩进行排名

公式:=SUMPRODUCT(($E$4:$E$13>E4)/COUNTIF($E$4:$E$13,$E$4:$E$13))+1

万能函数SUMPRODUCT超实用的10种经典用法_SUMPRODUCT函数_06

说明:对于排名可能大家都会想到使用RANK函数来进行计算排名,但是RANK是国际的一种排名方式,当遇到相同名次时,下一个名次数字顺序就会间断,例如案例中有三位学生都是第5名,顺延下一位学生的名次就变成了第8名了,此时就间断了6和7名的名次,解决不间断名次(中国式排名方式)需要使用SUMPRODUCT函数。

SUMPRODUCT函数($E$4:$E$13>E4)表示在E4:E13成绩区域列是否大于E4成绩,按F9则返回一组数组{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE},这里的TRUE表示的是1,FALSE表示的是0。而COUNTIF($E$4:$E$13,$E$4:$E$13)表示数组中的元素就是对应成绩重复出现的次数,按F9可以看出{1;1;1;3;3;3;1;1;1;1},这里的3表示的是成绩78分出现3次,1表示没有重复的分数,也就是各分数出现1次。然后将两组数组进行相除,再通过SUMPRODUCT函数进行求和得到数字5,后面再加上1表示的是当前数字的本身排名情况。

如果对以上说明不理解,可以通过这种公式套路进行理解

套路=SUMPRODUCT((条件区域>条件)/COUNTIF(要进行排名的区域))+1

七、SUMPRODUCT函数第7种用法:按条件排名

目的:根据两个班级学生成绩,分别进行按班级排名

公式:=SUMPRODUCT(($B$4:$B$13=B4)*($E$4:$E$13>E4))+1

万能函数SUMPRODUCT超实用的10种经典用法_Excel函数_07

说明:SUMPRODUCT函数($B$4:$B$13=B4)表示在B4:B13班级区域列判断是否等于B4,按F9则返回一组数组{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE},($E$4:$E$13>E4)表示在E4:E13成绩区域列是否大于E4,按F9则返回一组数组{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE}。这里的TRUE表示的是1,FALSE表示的是0,通过SUMPRODUCT函数将两组数组乘积求和汇总,再加上1本身成绩排名,从而计算出各班级学生排名情况。

如果对以上说明不理解,可以通过这种公式套路进行理解

套路=SUMPRODUCT((条件区域1=条件1)*(要进行排名的区域))+1

八、SUMPRODUCT函数第8种用法:模糊条件求和

目的:根据每个商品的数量和单价,计算笔销量总和

公式:=SUMPRODUCT(ISNUMBER(FIND("笔",B4:B12))*C4:C12*D4:D12)

万能函数SUMPRODUCT超实用的10种经典用法_数据_08

说明:由于SUMPRODUCT函数的参数中不支持使用通配符,所以在计算带有关键字的问题,在这里需要截取ISNUMBER解决关键字的问题,首先使用FIND函数在B4:B12数据区域中查找关键字“笔”,如果包含关键字“笔”,就返回表示关键字位置的数值,否则就返回错误值。然后使用ISNUMBER函数判断FIND函数的数组结果是不是数值,如果是数值,说明是包含关键字的,如果不是数值,那就是不包含关键字了。ISNUMBER最终得到由逻辑值TRUE或FALSE构成的内存数组,然后再与后面的区域C4:C12和区域D4:D12依次相乘,最后由SUMPRODUCT返回乘积之和

九、SUMPRODUCT函数第9种用法:忽略文本求和

目的:统计所有商品的总数量

公式:=SUMPRODUCT(C4:C12)

万能函数SUMPRODUCT超实用的10种经典用法_SUMPRODUCT函数_09

说明:公式SUMPRODUCT(C4:C12)直接对数量列中包含数字进行求和,忽略文本。当然在这里也可以直接用SUM函数进行汇总。

十、SUMPRODUCT函数第10种用法:数值和文本混合求和

目的:根据每个商品的数量和单价,计算笔销量总和

公式:=SUMPRODUCT(--SUBSTITUTE(D4:D12,"元","")*C4:C12)

万能函数SUMPRODUCT超实用的10种经典用法_数组_10

说明:由于单价列包含有单位“元”,此时先使用SUBSTITUTE函数将单位元进行替换掉,也就是--SUBSTITUTE(D4:D12,"元",""),这里两个减号--,表示的是负负得正,也就是强制地把文本型的数字转换成数值型的,最后通过SUMPRODUCT函数将数量列和单价列的数据进行乘积求和。

作者:Excel办公小课堂


今天分享的内容,建议收藏起来以备不时之需!

关注Excel办公小课堂

解锁办公技能,告别无效加班

万能函数SUMPRODUCT超实用的10种经典用法_Excel函数_11

万能函数SUMPRODUCT超实用的10种经典用法_Excel函数_12

看完点赞,月入过万~

标签:10,FALSE,函数,TRUE,万能,E4,D4,SUMPRODUCT
From: https://blog.51cto.com/u_12801775/6455138

相关文章

  • 6.10 闲话
    今天和昨天都被dp真实了,得好好补补dp了。一个式子能推我一天,我真服辣。下面说正事昨天打入门赛了,感觉出题人对"她"是........当时我读题就感觉看到了自己,但人家比我好啊......我还真应了我的名字Jokest(但其实我是因为这事才取的Jokest的名字)我真的有很多话想说,但是总说不......
  • 1000道Python题库系列分享三(30道)
    上一期题目链接:1000道Python题库系列分享二(48道)上一题题目参考答案:2.1 312.2 'F'2.3 Python采用的是基于值得内存管理方式,在Python中可以为不同变量赋值为相同值,这个值在内存中只有一份,多个变量指向同一个内存地址;Python具有自动内存管理功能,会自动跟踪内存中所有的值,对于没......
  • 1000道Python题库系列分享四(40道)
    热烈庆祝2018年2月董付国老师《Python程序设计(第2版)》出版18个月第5次印刷,《Python可以这样学》出版12个月第5次印刷,系列教材《Python程序设计基础》、《Python程序设计开发宝典》、《中学生可以这样学Python》等出版两年半以来已累计印刷25次。上一期题目链接:1000道Python题库系列......
  • 2023.6.10集训总结
    2023.6.10集训总结在5月中旬到现在,我们经历了几周的停课集训,期间我还前往NJU参加学科营活动,感受到自己与全国大佬的差距时,也学到了一些大赛策略和经验。现对停课期间的收获与反思进行总结。讲课这几天之内,Meatherm、yny和tqx分别来讲了2、2、4天的课。讲课主要以做例题为主,图论......
  • 1000道Python题库系列分享一(17道)
    本系列题目共约1000道,下一期题库分享时发布本期题目参考答案,可以在微信公众号菜单查看系列题目。1.1 到Python官方网站下载并安装Python解释器环境。1.2 到Anaconda官方网站下载并安装最新的Anaconda3开发环境。1.3 Python程序的__name__的作用是什么?1.4 Python安装扩展库......
  • 算法刷题记录:P4924 [1007]魔法少女小Scarlet
    题目链接https://www.luogu.com.cn/problem/P4924题目分析题意为将以[x,y]为中心某个矩阵,逆时针/顺时针旋转。所以其本质就是矩阵的旋转,所以找出通项公式即可。通项公式:顺时针:x后=x+y-y原,y后=y-x+x原逆时针:x后=x-y+y原,y后=x+y-x原AC代码//Problem:P4924[1007]魔法少......
  • 1000道Python题库系列分享21(11道编程题:内置函数专项练习)
    本期题目,答案在文末:参考代码:......
  • 1000道Python题库系列分享25(40道Pandas客观题)
    本期题目:公众号“Python小屋”......
  • 1000道Python题库系列分享26(12道Pandas编程题)
    题目要求:根据给定的文件“超市营业额2.xlsx”,数据格式如下:编写程序完成下面的题目,所有代码保存在一个文件中,该文件使用自己姓名进行命名,例如“张三.py”,其中每个题目使用注释“#第1题”类似的形式进行分隔。1.读取文件中的数据,删除重复数据,把缺失的交易额使用每个员工自己所有交易......
  • 1000道Python题库系列分享23(61个填空题)
    封面图片:《玩转Python轻松过二级》,董付国,清华大学出版社=================本期题目,参考答案在文末:公众号“Python小屋”参考答案:......