SUM、SUMIF、COUNTIF函数的使用.xls
《SUM、SUMIF、COUNTIF函数的使用.xls》由会员分享,可在线阅读,更多相关《SUM、SUMIF、COUNTIF函数的使用.xls(28页珍藏版)》请在咨信网上搜索。
1、SUM函函数数的的使使用用by chenjun语法:ESUM(参参数数1,参参数数2,.,参参数数30)结果:返回所有参数中的数字之和。说明:参数最多只能有30个,并且可以省略(即,间没有参数或最后有一个,);参数可以为引用、返回数值和文本及逻辑值的计算表达式、数组;参数如为引用,可以是区域联合、区域交叉、三维区域引用,只要引用不再参与数组运算就可以;如果参数为错误值或为不能转换成数字的文本,将会导致错误。下面作一些详细的分析:A.对对文文本本、逻逻辑辑值值及及错错误误值值的的计计算算对引用中的文本、数字型的文本、逻辑值忽略不计算。姓名3500a1公式=SUM(H11:J14),只计单元格中的
2、数值,不计文本、逻辑值a2和I12格中的文本1000a3对数组中的文本、数字型的文本、逻辑值忽略不计。#N/A3500 数组公式,不带、号输入,按ctrl+shift+enter三键结束。公式=SUM(姓名,a1,1000,TRUE,2000,FALSE,1500,H11:H14=a2)错误值,不管是在引用、参数、还是在数组中均返回错误,此处excel的帮助中有错误。#N/A引用中有错误值#DIV/0!作为参数的计算表达式的结果为错误值#VALUE!数组中有错误值参数或作为参数的计算表达式为不是数字型的文本,返回错误。要不返回错误用F中提出的方法。#VALUE!直接用不是数字的文本作参数#VA
3、LUE!以返回不是数字的文本表达式作为参数参数或作为参数的计算表达式为数字型的文本,转为数值后计算;参数或作为参数的计算表达式为逻辑值时,TRUE算1,FALSE算0。34 公式为=SUM(10,21,12,TRUE,FALSE,2,2&0)其中的21为逻辑值TRUE算1,直接参数TRUE算1,其他的FALSE算0,文本2和表达式文本20转换后计算。B.以以引引用用的的运运算算作作参参数数区域联合86 请注意区域联合运算外的一对括号,那是不可少的,A此运算在SUM函数中算1个参数,当SUM中的参数1多于30个时可用此法来减少参数。2区域交叉70 注意括号及2个引用间的空格,交叉引用3在SUM函
4、数中也只算1个参数,此处实际运算返回4的是H31:K32和I29:J34相交的B31:C32区域。5联合区域不能在数组公式中继续进行计算。6交叉引用在数组公式中可以可以继续进行计算。42 公式为=SUM(H29:K34 I:I)12)*(H29:K34 I:I)实际计算的是I29:I34区域大于12的值的和C.以以三三维维引引用用作作参参数数63 公式为=SUM(SUM:SUMIF!H29:H34),其中SUM:SUMIF!H29:H34为对从SUM工作表开始至SUMIF工作表结束的H29:H34的区域引用。象这样的直接三维引用不可继续用于数组计算中。象下面这样的数组公式为什么是可以正确运算的
5、?191 公式为=SUM(H28:H34,I34,J29:K29),(H29:K34 I:I)12)*(H29:K34 I:I),SUM:SUMIF!H29:H34)请注意上面的数组公式中SUM函数有3个参数,第1个是联合区域引用,第2个是交叉区域引用计算的数组,第3个是三维区域引用。由于联合区域和三维引用区域均没有继续进行数组运算,所以在SUM函数的数组公式中作为单独的参数是可以的。提示:我们在使用SUM函数的数组公式时,经常只考虑有1个参数,其实是忘了SUM函数最多可有30个参数。D.以以没没有有打打开开的的工工作作薄薄的的指指定定表表的的指指定定区区域域引引用用作作参参数数600 公式为
6、=SUM(C:excelhomefunctionINDIRECT函数的使用.xlsSheet2!$B:$B)引用了C:excelhomefunctionINDIRECT函数的使用.xls 工作薄Sheet2表的整个B列。只要路径所指定的文件存在就不需要打开文件,如不存在就返回错误。E.以以(由由一一个个元元素素的的数数组组参参数数产产生生的的)单单元元格格区区域域引引用用作作SUM函函数数的的参参数数SUM函数不作为其他函数的参数可以使用,见G54格,1020公式为=SUM(INDIRECT(H&ROW()/2&:J&ROW()/2+4)实际相当于SUM(INDIRECT(H27:J31),即
7、对H27:J31区域求和。H54格是将这样的SUM函数放在IF函数中作为参数,就错误了,因为ROW()返回的是一个数组并不是一个数值,这样在数组公式中INDIRECT函数返回的并不是一个二维的区域引用,而是三维的区域引用(第3维的尺寸是1),所以SUM的计算出错。可以将公式改为I54格的样子(本论坛的会员提出的方法,先用SUM函数将ROW函数的数组变为数值),或用SUMIF代替(见J54格)。E.以以(由由多多个个元元素素的的数数组组参参数数产产生生的的)单单元元格格区区域域引引用用作作SUM函函数数的的参参数数一般是INDIRECT函数和OFFSET函数才能以多个数组元素,产生一系列对多个单
8、元格区域的引用,返回的是三维的区域引用,SUM函数只能对第1个元素指定的区域求和,如H64格的公式。61用内嵌SUMIF函数代替就正确了,见H65格。130F.以以非非数数字字型型文文本本作作参参数数的的方方法法A中指出参数或作为参数的计算表达式为不是数字型的文本,返回错误。要解决直接参数为非数字的问题,可按图设置。#VALUE!按图设置后,就按Lotus1-2-3的方式忽略文本。见H69格。不利因素是,所有的公式均按Loutus1-2-3的方式处理,很多excel的表达式就会出错。G.SUM函函数数在在数数组组公公式式中中的的一一些些应应用用多多条条件件计计数数A部门的男性员工有几人?3姓名
9、部门性别工资(B92:B105=A)*(C92:C105=男)返回2个逻辑数组的乘积,基于A1A男1000 TRUE*TRUE=1;TRUE*FALSE=0;FALSE*FALSE=0A2B女1500 所以此处是逻辑与的关系,在excel的IF函数的条件中,0表示FALSE,非0的数值表示TRUE。A3C女1000 去除IF函数可以简化公式为3A4D女800 A、B两部门的男性员工有几人?4A5B女2000 基于TRUE+FALSE=1;FALSE+FALSE=0;TRUE+TRUE=2A6C男2500 而(B92:B105=A)和(B92:B105=B)不可能同时满足,所以此处是条件或的关系
10、,A7D男1500 再乘以(C92:C105=男)作为并列条件。A8A男1000 A部门所有女性员工和A部门工资1500以上的男性员工总数是多少?A9C女10003A10D男2000 因为(C92:C105=女)和(D92:D105=1500)可能同时满足,所以再用NOT(NOT()转换,基于A11A男3000 NOT(TRUE)=FALSE;NOT(FALSE)=TRUE;NOT(0)=TRUE;NOT(非0数值)=FALSEA12B男900A13A女1800A14A女2500多多条条件件求求和和A部门女性员工的工资总额是多少?4300基于:FALSE*任何数=0;TRUE*任何数=原来的数
11、(B92:B105=A)*(C92:C105=女)为并列条件,*D92:D105后就是满足条件的工资。所有女性员工的工资和男性员工工资1500以上的工资总额是多少?19600如加IF函数就可以不用NOT(NOT()19600提示:以(C92:C105=女)+(D92:D105=1500)这样的形式表示条件或的关系,在条件可能同时满足时要用NOT(NOT()转换 或用IF函数判别。否则会多计数量的。统计偶数行的工资总和是多少?1130011300其中的(MOD(ROW(D92:D105),2)=0)就是判别是否偶数行。特特别别提提示示:SUM函函数数在在绝绝大大多多数数的的情情况况下下用用于于数
12、数组组公公式式中中只只能能返返回回一一个个值值,以以后后会会讲讲到到在在某某些些特特殊殊的的函函数数组组合合中中 在在多多单单元元格格数数组组公公式式中中,可可在在不不同同的的单单元元格格返返回回不不同同的的值值,好好象象是是返返回回了了一一个个数数组组,但但那那只只能能在在单单元元格格 中中表表现现,而而不不能能继继续续进进行行数数组组运运算算的的。E中中有有很很多多的的相相关关帖帖子子,请请大大家家多多看看看看。工资婚姻状况1000TRUE2000FALSE1500#DIV/0!BCD102030112131122232132333142434152535by chenjunE 参数如为引
13、用,可以是区域联合、区域交叉、三维区域引用,只要引用不再参与数组运算就可以;错误值,不管是在引用、参数、还是在数组中均返回错误,此处excel的帮助中有错误。参数或作为参数的计算表达式为不是数字型的文本,返回错误。要不返回错误用F中提出的方法。以返回不是数字的文本表达式作为参数其中的21为逻辑值TRUE算1,直接参数TRUE算1,其他的FALSE算0,文本2和表达式文本20转换后计算。公式为=SUM(SUM:SUMIF!H29:H34),其中SUM:SUMIF!H29:H34为对从SUM工作表开始至SUMIF工作表结束公式为=SUM(H28:H34,I34,J29:K29),(H29:K34
14、I:I)12)*(H29:K34 I:I),SUM:SUMIF!H29:H34)请注意上面的数组公式中SUM函数有3个参数,第1个是联合区域引用,第2个是交叉区域引用计算的数组,第3个是三维区域引用。由于联合区域和三维引用区域均没有继续进行数组运算,所以在SUM函数的数组公式中作为单独的参数是可以的。102102提示:我们在使用SUM函数的数组公式时,经常只考虑有1个参数,其实是忘了SUM函数最多可有30个参数。公式为=SUM(C:excelhomefunctionINDIRECT函数的使用.xlsSheet2!$B:$B)引用了C:excelhomefunctionINDIRECT函数的使用
15、.xls 工作薄Sheet2表的整个B列。因为ROW()返回的是一个数组并不是一个数值,这样在数组公式中INDIRECT函数返回的并不是一个二维的区域引用,可以将公式改为I54格的样子(本论坛的会员提出的方法,先用SUM函数将ROW函数的数组变为数值),或用SUMIF代替(见J54格)。一般是INDIRECT函数和OFFSET函数才能以多个数组元素,产生一系列对多个单元格区域的引用,返回的是三维的区域引用,按图设置后,就按Lotus1-2-3的方式忽略文本。见H69格。不利因素是,所有的公式均按Loutus1-2-3的方式处理,很多excel的表达式就会出错。如有2个以上并列条件,可将几个条件
16、式相乘。(B92:B105=A)*(C92:C105=男)返回2个逻辑数组的乘积,基于TRUE*TRUE=1;TRUE*FALSE=0;FALSE*FALSE=0所以此处是逻辑与的关系,在excel的IF函数的条件中,0表示FALSE,非0的数值表示TRUE。基于TRUE+FALSE=1;FALSE+FALSE=0;TRUE+TRUE=2而(B92:B105=A)和(B92:B105=B)不可能同时满足,所以此处是条件或的关系,A部门所有女性员工和A部门工资1500以上的男性员工总数是多少?因为(C92:C105=女)和(D92:D105=1500)可能同时满足,所以再用NOT(NOT()转换
17、,基于NOT(TRUE)=FALSE;NOT(FALSE)=TRUE;NOT(0)=TRUE;NOT(非0数值)=FALSE提示:以(C92:C105=女)+(D92:D105=1500)这样的形式表示条件或的关系,在条件可能同时满足时要用NOT(NOT()转换特特别别提提示示:SUM函函数数在在绝绝大大多多数数的的情情况况下下用用于于数数组组公公式式中中只只能能返返回回一一个个值值,以以后后会会讲讲到到在在某某些些特特殊殊的的函函数数组组合合中中 在在多多单单元元格格数数组组公公式式中中,可可在在不不同同的的单单元元格格返返回回不不同同的的值值,好好象象是是返返回回了了一一个个数数组组,但但
18、那那只只能能在在单单元元格格COUNTIF函函数数的的使使用用语法:COUNTIF(引引用用,条条件件)结果:计算引用所指定的区域内满足条件的单元格的数目。说明:一般情况下引用只能是对一个工作表的一个区域的引用,但实际应用中可以用以数组指定的多个区域(也就是数组返回的三维引用);条件为确定哪些单元格将被计算在内的条件,其形式可以为数字、表达式、文本或数组,只能用单条件不能是复合条件,当条件为文本时可以用统配符*(表示任意多的字符)和?(表示单个字符),如果要查找不是统配符的*和?字符,可用*和?表示。提示:引用必须是指区域不能是数组,COUNTIF函数一般是返回一个数值,但如果引用是数组指定的
19、多个区域,可以返回一个数组;当条件为数组时也返回一个同尺寸的数组。A.对对一一个个区区域域引引用用的的计计算算11 公式为=COUNTIF(H28:K34,20),求H28:K34区域中20的数值单元格数量。上例中的条件也可以是引用别的单元格的,如:条件2011 公式改为=COUNTIF(H28:K34,&G13)不能直接用三维引用,如:#VALUE!这样的公式=COUNTIF(SUM:SUMIF!H28:K34,20)返回错误!怎么计算下面会讲到。可以用交叉区域引用,如:11 公式=COUNTIF(28:34 H:K),20)的引用区域同H28:K34不能用联合区域引用,如:#VALUE!公
20、式=COUNTIF(J29:K30,J33:K34),20)是错误的当用2个条件时,必须其中的1个条件为TRUE时包括了另一个条件为FALSE的范围,或2个条件为TRUE的范围不重复。如求区域H28:K34中满足20并且20包含了=30的区域,可按下面的公式5 公式=COUNTIF(H28:K34,20)-COUNTIF(H28:K34,=30)5求区域H28:K34中满足30的单元格数,30不会同时满足,可用下面的公式17 公式=COUNTIF(H28:K34,30)17以数组作条件,也可写成这样17公式=SUM(COUNTIF(H28:K34,30)中为常量数组,可以不按数组公式输入。此时
21、COUNTIF函数按数组条件返回了2个元素的数组,再用SUM求和。B.在在条条件件中中使使用用统统配配符符A5B5C5D5AA6ABA6C6AD6*1A*7BA17C7AD7*2BA8A8C8AD8*3A9B9C9D94求区域A28:D32中以A开头的单元格数56 公式=COUNTIF(A28:D32,A*),*表示任意长度的字符。6求区域A28:D32中以A开头以6结束的单元格数2 公式=COUNTIF(A28:D32,A*6)求区域A28:D32中包含6的单元格数4 公式=COUNTIF(A28:D32,*6*)求区域A28:D32中第3位为A的单元格数4 公式=COUNTIF(A28:D
22、32,?A*),?表示一个任意字符。求区域A28:D32中包含*的单元格数4 公式=COUNTIF(A28:D32,*),第1个*为统配符,*表示*字符,最后1个*为统配符,要表示字符*用*转换。求区域A28:D32中第3位为*的单元格数2 公式=COUNTIF(A28:D32,?*),*表示字符*,其他的是统配符。求区域A28:D32中包含的单元格数3 公式=COUNTIF(A28:D32,*),其中的表示以免把*当作*字符。求区域A28:D32中包含的单元格数1 公式=COUNTIF(A28:D32,*),其中的表示2个字符。求区域A28:D32中包含*的单元格数2 公式=COUNTIF(
23、A28:D32,*),其中的*表示2个*字符。提提示示:统统配配符符只只能能对对文文本本有有效效,对对数数值值无无效效,如如求求H29:K34区区域域中中含含1的的单单元元格格数数0 因引用区域中是数值,公式=COUNTIF(H29:K34,*1*)无效,可用SUM的数组公式解决。C.比比较较条条件件对对数数字字型型文文本本和和数数值值的的区区别别求右面区域中等于12的单元格数2 公式=COUNTIF(K55:K64,12),用等于条件计数时,对文本型数字和数值一样对待。如求大于12的单元格数会怎样?553 公式=COUNTIF(K55:K64,12)的结果是错的,只在数值单元格中计数。怎样改
24、呐?一个供参考的方案5公式=COUNTIF(K55:K64,12)+COUNTIF(K55:K64,12A)为2段相加,其中12A实际是强制对文本格与文本12A比较。如用作条件会怎样了?如求不等于12的格数89 公式=COUNTIF(K55:K64,12)实际统计了不等于数值12的格数,文本12被当作不等的。如非得这样算,建议的方案8公式=COUNTA(K55:K64)-COUNTIF(K55:K64,12)返回了正确的结果。提提示示:实实在在没没有有必必要要将将文文本本型型数数字字和和数数值值混混在在一一起起,那那简简直直是是自自找找麻麻烦烦,设设计计表表格格时时就就应应该该做做到到类类型型
25、一一致致。D.将将数数字字型型数数据据类类型型统统一一成成文文本本格格式式就就不不会会出出错错吗吗?看右面的18位身份证号码区域,单元格中均是文本。(只是为了说明问题,并不是真正的号码)求区域中320101197001012011号码出现了几次?16 公式=COUNTIF(K69:K74,=320101197001012011),为什么结果是错的?原因是:比较条件为等于时,=320101197001012011实际是比较数值,文本型数字是先转为数值 再同条件中的数比较的,而关键是excel对数值的有效位最多为15位,超出的3位在条件 和文本型数字转换时均被忽略了,想想看,忽略了后3位在本例中那
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SUM SUMIF COUNTIF 函数 使用
1、咨信平台为文档C2C交易模式,即用户上传的文档直接被用户下载,收益归上传人(含作者)所有;本站仅是提供信息存储空间和展示预览,仅对用户上传内容的表现方式做保护处理,对上载内容不做任何修改或编辑。所展示的作品文档包括内容和图片全部来源于网络用户和作者上传投稿,我们不确定上传用户享有完全著作权,根据《信息网络传播权保护条例》,如果侵犯了您的版权、权益或隐私,请联系我们,核实后会尽快下架及时删除,并可随时和客服了解处理情况,尊重保护知识产权我们共同努力。
2、文档的总页数、文档格式和文档大小以系统显示为准(内容中显示的页数不一定正确),网站客服只以系统显示的页数、文件格式、文档大小作为仲裁依据,平台无法对文档的真实性、完整性、权威性、准确性、专业性及其观点立场做任何保证或承诺,下载前须认真查看,确认无误后再购买,务必慎重购买;若有违法违纪将进行移交司法处理,若涉侵权平台将进行基本处罚并下架。
3、本站所有内容均由用户上传,付费前请自行鉴别,如您付费,意味着您已接受本站规则且自行承担风险,本站不进行额外附加服务,虚拟产品一经售出概不退款(未进行购买下载可退充值款),文档一经付费(服务费)、不意味着购买了该文档的版权,仅供个人/单位学习、研究之用,不得用于商业用途,未经授权,严禁复制、发行、汇编、翻译或者网络传播等,侵权必究。
4、如你看到网页展示的文档有www.zixin.com.cn水印,是因预览和防盗链等技术需要对页面进行转换压缩成图而已,我们并不对上传的文档进行任何编辑或修改,文档下载后都不会有水印标识(原文档上传前个别存留的除外),下载后原文更清晰;试题试卷类文档,如果标题没有明确说明有答案则都视为没有答案,请知晓;PPT和DOC文档可被视为“模板”,允许上传人保留章节、目录结构的情况下删减部份的内容;PDF文档不管是原文档转换或图片扫描而得,本站不作要求视为允许,下载前自行私信或留言给上传者【fq****56】。
5、本文档所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用;网站提供的党政主题相关内容(国旗、国徽、党徽--等)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。
6、文档遇到问题,请及时私信或留言给本站上传会员【fq****56】,需本站解决可联系【 微信客服】、【 QQ客服】,若有其他问题请点击或扫码反馈【 服务填表】;文档侵犯商业秘密、侵犯著作权、侵犯人身权等,请点击“【 版权申诉】”(推荐),意见反馈和侵权处理邮箱:1219186828@qq.com;也可以拔打客服电话:4008-655-100;投诉/维权电话:4009-655-100。