发文章
发文工具
撰写
网文摘手
文档
视频
思维导图
随笔
相册
原创同步助手
其他工具
图片转文字
文件清理
AI助手
留言交流
第一部分,SUMPRODUCT函数在计数中的应用
SUMPRODUCT函数用于多条件计数,计算符合2个及以上条件的数据个数。有一个经典公式计数:SUMPRODUCT((条件1)*(条件2)*(条件3)*...)
第一,统计C列性别列中女性有几个人。
此题为单条件求和。首先要知道条件是什么,(C4:C33='女')区域中等于女的,这部分就是条件。
如果直接输入=SUMPRODUCT(C4:C33='女'),得到结果为0。第一部分用法介绍里介绍:函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理,C4:C33='女',按F9键得到执行结果是true、false形式的逻辑值,所以等于0。
那如何把逻辑值转换为数值呢,就要让逻辑值参加运算,可以用--,*1,+0等等。比如,=TRUE*1,结果为1。=FALSE*1,结果为0。因此在(C4:C33='女')外面加上--就可以了。
公式为:=SUMPRODUCT(--(C4:C33='女')),结果为9人。
第二,求E列潜水天数大于15天的男性有几人。
此题有两个条件:第一,大于15天,用E4:E33>15表示。第二,男性,用C4:C33='男'表示。
套用SUMPRODUCT((条件1)*(条件2)*(条件3)*...),得到公式:=SUMPRODUCT((E4:E33>15)*(C4:C33='男')),结果为8人。
第三,统计2月份发言的男性有几人。
D列最后发言时间有1月和2月的份。统计2月份,需要用到month函数来求月份。比如A1单元格:2011-2-25,A2单元格输入公式:=MONTH(A1),返回2。
还是套用SUMPRODUCT((条件1)*(条件2)*(条件3)*...),得到公式:=SUMPRODUCT((MONTH(D4:D33)=2)*(C4:C33='男')),结果为16人。
第四,统计不包括笑看今朝的男性有多少人。
不包括,就是不等于,是<>表示。
公式为:=SUMPRODUCT((A4:A33<>'笑看今朝')*(C4:C33='男')),结果为20人。
第五,统计有几个人的潜水天数是不一样的。
比如,E列潜水天数为6的有6次,为5的有2次。
如何让每个数字只出现一次呢?计数用countif函数。如何让每个数只计算一次呢,可以使用1/countif。比如5出现两次,就是两个1/2,最后汇总就得到1。最后再套用公式sumproduct(1/countif(区域,区域))。
公式为:=SUMPRODUCT(1/COUNTIF(E4:E33,E4:E33)) ,结果为14人。
第二部分,SUMPRODUCT函数在求和中的应用
用函数SUMPRODUCT求和,函数需要的参数一个是进行判断的条件,另一个是用来求和的数据区域。
SUMPRODUCT函数求和应用有一个经典的套用格式:SUMPRODUCT((条件1)*(条件2)*(条件3)*…*求和区域)
第一,女性潜水总天数计算。
套用格式,得到公式:=SUMPRODUCT((C4:C33='女')*E4:E33),结果为134。
第二,潜水时间大于15天的男性的潜水天数计算。
套用格式,得到公式:=SUMPRODUCT((E4:E33>15)*(C4:C33='男')*E4:E33),结果为242。
第三,2月份发言的男性的潜水天数计算。
套用格式,得到公式:=SUMPRODUCT((MONTH(D4:D33)=2)*(C4:C33='男')*E4:E33),结果为159。
第四,QQ号首位是8的人的潜水天数计算。
首先用left函数提取首位是8的,因为left提取的是文本,加''就成了文本。然后套用格式,得到公式:=SUMPRODUCT((LEFT(B4:B33)='8')*E4:E33),结果为77。
第五,姓名字符数为2,不包括“月亮”的人的潜水天数计算。
公式为:=SUMPRODUCT((LEN(A4:A33)=2)*(A4:A33<>'月亮')*E4:E33),得到结果:92。
第六,“笑看今朝”和 “冷逸”的潜水天数计算。
公式为:=SUMPRODUCT((A4:A33={'笑看今朝','冷逸'})*E4:E33),得到结果13。
公式中两个条件,可以这样写:{'笑看今朝','冷逸'}。
第三部分,SUMPRODUCT函数在查找及排名中的应用
如下图所示,需要求出姓名列的潜水天数已经排名情况。
在以前的讲座详细介绍了vlookup函数实现查找的方法,在本讲座中用sumproduct函数取代。利用单条件求和的特点来查找。根据前面的介绍,套用格式=SUMPRODUCT((姓名=I26)*天数),得到公式:=SUMPRODUCT(($A$4:$A$33=I26)*$E$4:$E$33),然后下拉即可完成。
在以往排名我们使用rank函数,不过我们也可使用SUMPRODUCT函数来完成。在K26输入公式:=SUMPRODUCT(--($J$26:$J$31>J26))+1,下拉。思路就是如果区域中有几个大于本身,目标值就是大于的所有数+1。
来自: 金石为开hajyr8 > 《Excel》
0条评论
发表
请遵守用户 评论公约
SUMPRODUCT函数的应用
SUMPRODUCT函数的应用2011年第11期技术讲座:SUMPRODUCT函数的应用[日期:2011-02-25] 来源:IT部落窝 作者:IT部落窝 阅读:14420次...
不重复数值的计算(sumproduct函数的运用)
不重复数值的计算(sumproduct函数的运用)=SUMPRODUCT(1/COUNTIF($D$3:$D$22,$D$3:$D$22))利用这一结论再结合sumproduct函数的功能就能...
工作中最常用的excel函数公式大全,帮你整理齐了,拿来即用
工作中最常用的excel函数公式大全,帮你整理齐了,拿来即用。二、判断公式。1、把公式产生的错误值显示为空。公式:详见下图。说明:在...
Excel不会数据区间统计就out了,四个函数让你玩转区间个数计算
Excel不会数据区间统计就out了,四个函数让你玩转区间个数计算。1、Frequency函数在区间统计函数中是最简单方便的一个函数,可以直接根据自身参数特点,快速进行区间数据计算;函数二:Sumproduct函数...
你的工作中必须要学会的这27个Excel函数公式,速速收藏
你的工作中必须要学会的这27个Excel函数公式,速速收藏。4、多条件查找5、指定区域最后一个非空数据的查找6、按数字区域间取对应的值六...
工作中最常用Excel函数公式大全
工作中最常用Excel函数公式大全数字处理取绝对值=ABS(数字)取整=INT(数字)四舍五入=ROUND(数字,小数位数)判断公式把公式产生的错误值显示为空公式:C2=IFERROR(A2/B2,"")IF多条件判断返回值...
22个常用Excel函数大全,直接套用,提升工作效率!
言归正传,今天和大家分享一组常用函数公式的使用方法:职场人士必须掌握的12个Excel函数,用心掌握这些函数,工作效率就会有质的提升。...
UC头条:工地常用Excel函数公式大全, 算量算得飞起
UC头条:工地常用Excel函数公式大全, 算量算得飞起。公式:详见下图 打开UC浏览器 查看更多精彩图片。1、单条件查找公式。说明:利...
好运万年历
今天和大家分享一组常用函数公式的使用方法。公式:=SUMPRODUCT(($D$4:$D$9>=D4)*(1/COUNTIF(D$4:D$9,D$4:D$9)))二、数字处理。说明...
微信扫码,在手机上查看选中内容