SUM、SUMIF、COUNTIF函数的使用.xls

SUM函函数数的的使使用用by chenjun语法：ESUM(参参数数1,参参数数2,.,参参数数30)结果：返回所有参数中的数字之和。说明：参数最多只能有30个，并且可以省略(即,间没有参数或最后有一个,)；参数可以为引用、返回数值和文本及逻辑值的计算表达式、数组；参数如为引用，可以是区域联合、区域交叉、三维区域引用，只要引用不再参与数组运算就可以；如果参数为错误值或为不能转换成数字的文本，将会导致错误。下面作一些详细的分析：A.对对文文本本、逻逻辑辑值值及及错错误误值值的的计计算算对引用中的文本、数字型的文本、逻辑值忽略不计算。姓名3500a1公式=SUM(H11:J14)，只计单元格中的数值，不计文本、逻辑值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!直接用不是数字的文本作参数#VALUE!以返回不是数字的文本表达式作为参数参数或作为参数的计算表达式为数字型的文本，转为数值后计算；参数或作为参数的计算表达式为逻辑值时，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函数中也只算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的区域引用。象这样的直接三维引用不可继续用于数组计算中。象下面这样的数组公式为什么是可以正确运算的？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 公式为=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)，即对H27:J31区域求和。H54格是将这样的SUM函数放在IF函数中作为参数，就错误了，因为ROW()返回的是一个数组并不是一个数值，这样在数组公式中INDIRECT函数返回的并不是一个二维的区域引用，而是三维的区域引用(第3维的尺寸是1)，所以SUM的计算出错。可以将公式改为I54格的样子(本论坛的会员提出的方法，先用SUM函数将ROW函数的数组变为数值)，或用SUMIF代替(见J54格)。E.以以(由由多多个个元元素素的的数数组组参参数数产产生生的的)单单元元格格区区域域引引用用作作SUM函函数数的的参参数数一般是INDIRECT函数和OFFSET函数才能以多个数组元素，产生一系列对多个单元格区域的引用，返回的是三维的区域引用，SUM函数只能对第1个元素指定的区域求和，如H64格的公式。61用内嵌SUMIF函数代替就正确了，见H65格。130F.以以非非数数字字型型文文本本作作参参数数的的方方法法A中指出参数或作为参数的计算表达式为不是数字型的文本，返回错误。要解决直接参数为非数字的问题，可按图设置。#VALUE!按图设置后，就按Lotus1-2-3的方式忽略文本。见H69格。不利因素是，所有的公式均按Loutus1-2-3的方式处理，很多excel的表达式就会出错。G.SUM函函数数在在数数组组公公式式中中的的一一些些应应用用多多条条件件计计数数A部门的男性员工有几人？3姓名部门性别工资(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)不可能同时满足，所以此处是条件或的关系，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*任何数=原来的数(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函函数数在在绝绝大大多多数数的的情情况况下下用用于于数数组组公公式式中中只只能能返返回回一一个个值值，以以后后会会讲讲到到在在某某些些特特殊殊的的函函数数组组合合中中 在在多多单单元元格格数数组组公公式式中中，可可在在不不同同的的单单元元格格返返回回不不同同的的值值，好好象象是是返返回回了了一一个个数数组组，但但那那只只能能在在单单元元格格 中中表表现现，而而不不能能继继续续进进行行数数组组运运算算的的。E中中有有很很多多的的相相关关帖帖子子，请请大大家家多多看看看看。工资婚姻状况1000TRUE2000FALSE1500#DIV/0!BCD102030112131122232132333142434152535by chenjunE 参数如为引用，可以是区域联合、区域交叉、三维区域引用，只要引用不再参与数组运算就可以；错误值，不管是在引用、参数、还是在数组中均返回错误，此处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 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函数的使用.xls 工作薄Sheet2表的整个B列。因为ROW()返回的是一个数组并不是一个数值，这样在数组公式中INDIRECT函数返回的并不是一个二维的区域引用，可以将公式改为I54格的样子(本论坛的会员提出的方法，先用SUM函数将ROW函数的数组变为数值)，或用SUMIF代替(见J54格)。一般是INDIRECT函数和OFFSET函数才能以多个数组元素，产生一系列对多个单元格区域的引用，返回的是三维的区域引用，按图设置后，就按Lotus1-2-3的方式忽略文本。见H69格。不利因素是，所有的公式均按Loutus1-2-3的方式处理，很多excel的表达式就会出错。如有2个以上并列条件，可将几个条件式相乘。(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()转换，基于NOT(TRUE)=FALSE；NOT(FALSE)=TRUE；NOT(0)=TRUE；NOT(非0数值)=FALSE提示：以(C92:C105=女)+(D92:D105=1500)这样的形式表示条件或的关系，在条件可能同时满足时要用NOT(NOT()转换特特别别提提示示：SUM函函数数在在绝绝大大多多数数的的情情况况下下用用于于数数组组公公式式中中只只能能返返回回一一个个值值，以以后后会会讲讲到到在在某某些些特特殊殊的的函函数数组组合合中中 在在多多单单元元格格数数组组公公式式中中，可可在在不不同同的的单单元元格格返返回回不不同同的的值值，好好象象是是返返回回了了一一个个数数组组，但但那那只只能能在在单单元元格格COUNTIF函函数数的的使使用用语法：COUNTIF(引引用用,条条件件)结果：计算引用所指定的区域内满足条件的单元格的数目。说明：一般情况下引用只能是对一个工作表的一个区域的引用，但实际应用中可以用以数组指定的多个区域(也就是数组返回的三维引用)；条件为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、文本或数组，只能用单条件不能是复合条件，当条件为文本时可以用统配符*(表示任意多的字符)和?(表示单个字符)，如果要查找不是统配符的*和?字符，可用*和?表示。提示：引用必须是指区域不能是数组，COUNTIF函数一般是返回一个数值，但如果引用是数组指定的多个区域，可以返回一个数组；当条件为数组时也返回一个同尺寸的数组。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!公式=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)中为常量数组，可以不按数组公式输入。此时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:D32,?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(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)的结果是错的，只在数值单元格中计数。怎样改呐？一个供参考的方案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)返回了正确的结果。提提示示：实实在在没没有有必必要要将将文文本本型型数数字字和和数数值值混混在在一一起起，那那简简直直是是自自找找麻麻烦烦，设设计计表表格格时时就就应应该该做做到到类类型型一一致致。D.将将数数字字型型数数据据类类型型统统一一成成文文本本格格式式就就不不会会出出错错吗吗？看右面的18位身份证号码区域，单元格中均是文本。(只是为了说明问题，并不是真正的号码)求区域中320101197001012011号码出现了几次？16 公式=COUNTIF(K69:K74,=320101197001012011)，为什么结果是错的？原因是：比较条件为等于时，=320101197001012011实际是比较数值，文本型数字是先转为数值 再同条件中的数比较的，而关键是excel对数值的有效位最多为15位，超出的3位在条件 和文本型数字转换时均被忽略了，想想看，忽略了后3位在本例中那不就全部相同了！在全部为18位的数字型文本，怎么才能正确比较呐？关键就是要强制让excel按文本来比较，我建议的方案如下：1 公式=COUNTIF(K69:K74,=3201011*97001012011)在在作作为为条条件件的的号号码码数数字字中中间间任任一一位位置置插插入入一一个个统统配配符符，那那样样excel就就只只能能以以文文本本来来比比较较了了！E.以以对对别别的的工工作作薄薄的的区区域域引引用用作作参参数数统计C:excelhomefunctionINDIRECT函数的使用.xlsSheet2!$B$2:$B$4中大于150的格数#VALUE!提提示示：必必须须打打开开被被引引用用的的工工作作薄薄，否否则则COUNTIF函函数数返返回回错错误误！F.统统计计空空格格3 公式=COUNTIF(J83:J89,)，注意条件用，如果用=，那么结果是错的，1 公式=COUNTIF(J83:J89,=)只统计没有内容的单元格数。以以下下内内容容涉涉及及用用数数组组产产生生对对区区域域的的三三维维引引用用G.以以由由数数组组指指定定的的区区域域引引用用作作参参数数A中讲到直接用三维区域引用=COUNTIF(SUM:SUMIF!H28:K34,20)是错的，可用INDIRECT函数的数组参数产生一个三维引用33 数组公式=SUM(COUNTIF(INDIRECT(SUM,COUNTIF,SUMIF&!H28:K34),20)其中INDIRECT函数用常量数组产生对3个工作表H28:K34区域的引用，COUNTIF函数也返回3个元素的数组，SUM函数再对数组求和。参考INDIRECT函数的使用一文中“关于工作表名的几个宏表函数名称定义”，就可以在INDIRECT函数中用名称代替数组常量统计各表中指定区域满足条件的单元格数。详见G97格的链接关于工作表名的几个宏表函数名称定义再举一个例子：求下面区域中间隔列(蓝色列)中满足20的单元格数12910292029152510252025592110212021913171017201713171310132013172191092092125525520525291291201298 公式=SUM(COUNTIF(INDIRECT(R99C&ROW(1:4)*2-1&:R106C&ROW(1:4)*2-1,0),20)INDIRECT返回4个区域引用，并作为COUNTIF函数的引用参数，COUNTIF函数返回4个值的数组，选中公式中COUNTIF(.)部分按F9就可看到这一点。SUM函数求总和。特特别别提提示示：因因为为COUNTIF函函数数的的第第1个个参参数数必必须须用用区区域域引引用用，所所以以就就可可以以正正确确计计算算由由数数组组参参数数指指定定的的多多个个区区域域引引用用。由由数数组组参参数数指指定定的的区区域域引引用用我我们们暂暂且且称称之之为为“三三维维引引用用”，大大部部分分的的函函数数是是不不支支持持这这样样的的引引用用的的。上例也可以用SUM函数的多条件数组方法求得，但如果数据列所包含的行数很多，上面的公式计算要快得多。H.以以由由数数组组指指定定的的区区域域引引用用作作参参数数求A99:G106区域中出现数值的个数(重复出现的只算1次)？10 公式=SUM(1/COUNTIF(A99:G106,A99:G106)因为COUNTIF函数的条件参数是引用了一个单元格区域，实际计算时先将引用转换为数组，然后按每个数组作为条件，返回8行7列的结果数组，每个元素为针对每个条件数组元素的结果，1/COUNTIF的结果如下：0.20.166670.166670.1666670.1250.166670.20.20.166670.166670.1666670.1250.166670.20.20.20.166670.20.1250.20.20.20.20.166670.20.1250.20.20.20.20.166670.20.1250.20.20.20.20.166670.20.1250.20.20.1666670.20.166670.20.1250.20.166670.1666670.20.166670.20.1250.20.16667可以看出返回的每个元素是相应单元格的值在区域中出现次数的倒数，再用SUM函数求和就是区域中不同元素的个数。提提示示：我我还还是是要要提提醒醒excel对对实实数数的的计计算算是是有有误误差差的的，用用SUM函函数数求求和和后后可可能能出出现现不不是是整整数数，建建议议再再用用ROUND函函数数圆圆整整。10 公式=ROUND(SUM(1/COUNTIF(A99:G106,A99:G106),0)这这样样就就可可以以避避免免实实数数的的计计算算误误差差了了，一一定定是是返返回回唯唯一一出出现现的的个个数数。COUNTIF函数的第2个条件参数可以为表达式计算出的数组，更多的用法请看E论坛中的相关帖子，如果COUNTIF函数的第1个参数是数组产生的引用、第2个参数是数组或多单元格区域引用，那么COUNTIF函数返回的也是一个数组，其运算规则见A134格中链接帖子的1楼“数组的特殊用途”中关于“数组运算规则”的论述。数组的一些特殊用途BCD102030112131122232132333142434152535说明：一般情况下引用只能是对一个工作表的一个区域的引用，但实际应用中可以用以数组指定的多个区域(也就是数组返回的三维引用)；条件为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、文本或数组，只能用单条件不能是复合条件，当条件为文本时可以用统配符*(表示任意多的字符)和?(表示单个字符)，如果要查找不是统配符的*和?字符，可用*和?表示。提示：引用必须是指区域不能是数组，COUNTIF函数一般是返回一个数值，但如果引用是数组指定的多个区域，可以返回一个数组；公式为=COUNTIF(H28:K34,20)，求H28:K34区域中20的数值单元格数量。这样的公式=COUNTIF(SUM:SUMIF!H28:K34,20)返回错误！怎么计算下面会讲到。公式=COUNTIF(28:34 H:K),20)的引用区域同H28:K34公式=COUNTIF(J29:K30,J33:K34),20)是错误的当用2个条件时，必须其中的1个条件为TRUE时包括了另一个条件为FALSE的范围，或2个条件为TRUE的范围不重复。如求区域H28:K34中满足20并且20包含了=30的区域，可按下面的公式求区域H28:K34中满足30的单元格数，30不会同时满足，可用下面的公式公式=SUM(COUNTIF(H28:K34,30)中为常量数组，可以不按数组公式输入。公式=COUNTIF(A28:D32,*)，第1个*为统配符，*表示*字符，最后1个*为统配符，要表示字符*用*转换。910文本型11文本型12文本型13文本型14文本型11 数值型12 数值型13 数值型14 数值型15 数值型320101197001012011320101197001012012320101197001012013320101197001012014320101197001012015320101197001012016由=产生的空白格由=产生的空白格0 数值0没有内容的格a1文本0文本023 数值因引用区域中是数值，公式=COUNTIF(H29:K34,*1*)无效，可用SUM的数组公式解决。公式=COUNTIF(K55:K64,12)，用等于条件计数时，对文本型数字和数值一样对待。公式=COUNTIF(K55:K64,12)+COUNTIF(K55:K64,12A)为2段相加，公式=COUNTIF(K55:K64,12)实际统计了不等于数值12的格数，文本12被当作不等的。提提示示：实实在在没没有有必必要要将将文文本本型型数数字字和和数数值值混混在在一一起起，那那简简直直是是自自找找麻麻烦烦，设设计计表表格格时时就就应应该该做做到到类类型型一一致致。看右面的18位身份证号码区域，单元格中均是文本。(只是为了说明问题，并不是真正的号码)公式=COUNTIF(K69:K74,=320101197001012011)，为什么结果是错的？原因是：比较条件为等于时，=320101197001012011实际是比较数值，文本型数字是先转为数值 再同条件中的数比较的，而关键是excel对数值的有效位最多为15位，超出的3位在条件 和文本型数字转换时均被忽略了，想想看，忽略了后3位在本例中那不就全部相同了！在全部为18位的数字型文本，怎么才能正确比较呐？关键就是要强制让excel按文本来比较，我建议的方案如下：在在作作为为条条件件的的号号码码数数字字中中间间任任一一位位置置插插入入一一个个统统配配符符，那那样样excel就就只只能能以以文文本本来来比比较较了了！统计C:excelhomefunctionINDIRECT函数的使用.xlsSheet2!$B$2:$B$4中大于150的格数公式=COUNTIF(J83:J89,)，注意条件用，如果用=，那么结果是错的，A中讲到直接用三维区域引用=COUNTIF(SUM:SUMIF!H28:K34,20)是错的，可用INDIRECT函数数组公式=SUM(COUNTIF(INDIRECT(SUM,COUNTIF,SUMIF&!H28:K34),20)其中INDIRECT函数用常量数组产生对3个工作表H28:K34区域的引用，COUNTIF函数也返回3个元素的数组，参考INDIRECT函数的使用一文中“关于工作表名的几个宏表函数名称定义”，就可以在INDIRECT函数中关于工作表名的几个宏表函数名称定义公式=SUM(COUNTIF(INDIRECT(R99C&ROW(1:4)*2-1&:R106C&ROW(1:4)*2-1,0),20)INDIRECT返回4个区域引用，并作为COUNTIF函数的引用参数，COUNTIF函数返回4个值的数组，特特别别提提示示：因因为为COUNTIF函函数数的的第第1个个参参数数必必须须用用区区域域引引用用，所所以以就就可可以以正正确确计计算算由由数数组组参参数数指指定定的的多多个个区区域域引引用用。由由数数组组参参数数指指定定的的区区域域引引用用我我们们暂暂且且称称之之为为“三三维维引引用用”，大大部部分分的的函函数数是是不不支支持持这这样样的的引引用用的的。上例也可以用SUM函数的多条件数组方法求得，但如果数据列所包含的行数很多，上面的公式计算要快得多。因为COUNTIF函数的条件参数是引用了一个单元格区域，实际计算时先将引用转换为数组，然后按每个数组作为条件，返回8行7列的结果数组，每个元素为针对每个条件数组元素的结果，1/COUNTIF的结果如下：可以看出返回的每个元素是相应单元格的值在区域中出现次数的倒数，再用SUM函数求和就是区域中不同元素的个数。提提示示：我我还还是是要要提提醒醒excel对对实实数数的的计计算算是是有有误误差差的的，用用SUM函函数数求求和和后后可可能能出出现现不不是是整整数数，建建议议再再用用ROUND函函数数圆圆整整。COUNTIF函数的第2个条件参数可以为表达式计算出的数组，更多的用法请看E论坛中的相关帖子，如果COUNTIF函数的第1个参数是数组产生的引用、第2个参数是数组或多单元格区域引用，那么COUNTIF函数返回的也是一个数组，其运算规则见A134格中链接帖子的1楼“数组的特殊用途”中关于“数组运算规则”的论述。SUMIF函函数数的的使使用用http:/ “数数组组的的一一些些特特殊殊用用途途”一一贴贴。数组的一些特殊用途因因为为很很多多的的用用法法与与SUM和和COUNTIF中中的的相相同同，下下面面就就说说得得简简单单一一点点A.省省略略引引用用2参参数数310 公式=SUMIF(H28:K34,20)，求出区域H28:K34中20的数值总和，省略第3个参数就在第1个参数指定的区域中求和。B.不不省省略略参参数数165 公式=SUMIF(H28:I34,10,J28:K34)，注意2个引用区域的行列数要相同，在H28:I34中按10条件返回J28:K34中对应格中值的和。C.简简化化B中中第第3个个参参数数写写法法的的方方法法这是E中一位网友提出的用法。165 公式=SUMIF(H28:I34,10,J28)，其第3个参数只写左上单元格引用SUMIF函数会自动按第1个参数的尺寸大小扩展第3个参数的区域引用。这样写法的麻烦是如果J28:K34区域中变化的不是J28格，公式不会自动重算。大家可以改变一个格中的值试试！ABD.2个个引引用用参参数数均均可可以以引引用用整整列列110165 公式=SUMIF(H:I,10,J:K)211312413E.以以“由由数数组组产产生生的的三三维维引引用用”作作参参数数514求出12个月的分表汇总的各地区的销售总额615地区销售总额东24000 公式=SUM(SUMIF(INDIRECT(ROW($1:$12)&月!A:A),$A36&*,INDIRECT(ROW($1:$12)&月!B:B)，南24000 然后向下拖动。西24000 INDIRECT(ROW($1:$12)&月!A:A)就是由数组产生的对12个月表的A列的引用，北36000$A36&*是使用带统配符的条件。求出H29:K34区域中各行之和最大2个值的平均值79 在这里见证了mmult函数的强大，呵呵卢子79 公式=AVERAGE(LARGE(SUMIF(INDIRECT(H&ROW($H$29:$K$34)&:K&ROW($H$29:$K$34),0),ROW($1:$2)INDIRECT(H&ROW($H$29:$K$34)&:K&ROW($H$29:$K$34)就是数组产生的对每一行的引用。求出H29:K34区域中行的和70的有几行？333 公式=SUM(1*(SUMIF(OFFSET($H$28:$K$28,ROW($H$29:$K$34)-ROW($H$28:$K$28),),0)70)OFFSET($H$28:$K$28,ROW($H$29:$K$34)-ROW($H$28:$K$28),)也是另一种由数组产生的对每一行的引用，SUMIF函数再对这个三维引用求和并返回一个6行元素组成的数组，70比较运算再产生一个逻辑数组，SUM函数再求逻辑数组中为TRUE的个数。曾经在Excelhome中出现过的问题，求下面区域中有50出现的行的最大间隔行数？545351504753474948534748475047505152485153484550465152534747515249545449505052494947454749494049464246544651474949545149514651495054525053493 公式太长了，而且比我在原贴处的复杂，这儿的用法只是为了说明SUMIF函数对三维引用的计算法。F.如如果果引引用用是是对对其其他他工工作作薄薄区区域域的的引引用用，那那个个工工作作薄薄必必须须打打开开，否否则则就就返返回回错错误误G.按按非非空空白白格格的的条条件件计计算算计算右面区域中K列为非空白格对应的L列数值的和111101 公式=SUMIF(K63:K68,L63:L68)是错的，条件将输入=的空白格计算在内了我建议的公式如下110101 公式=SUM(L63:L68)-SUMIF(K63:K68,L63:L68)分2部分计算再相减，在COUNIF函数中讲过条件包含了没有输入的空白格及输入=产生的空白格。为为了了写写这这三三个个函函数数用用了了我我一一个个月月的的时时间间，有有很很多多会会员员多多次次问问我我好好了了吗吗？现现在在终终于于可可以以交交卷卷了了！希希望望多多提提意意见见，可可在在原原贴贴处处跟跟贴贴。Echenjun2004.2.28CD203021312232233324342535http:/ A11没有输入的空白格10文本0 0100输入=产生的空白格1000非0数值123410000数值00100000SUMIF函数再对这个三维引用求和并返回一个6行元素组成的数组，70比较运算再产生一个逻辑数组，公式太长了，而且比我在原贴处的复杂，这儿的用法只是为了说明SUMIF函数对三维引用的计算法。地区月销售额东11000南11000西11000北11000西21000北21000东21000南21000北31000地区月销售额东11000东21000南11000西11000北11000西21000北21000北31000南21000地区月销售额南11000东11000东21000西11000西21000北11000北21000北31000南21000地区月销售额东11000东21000南11000西11000北11000西21000北21000南21000北31000地区月销售额东11000南11000南21000西11000北11000西21000北21000北31000东21000地区月销售额南11000南21000西11000北11000西21000北21000东11000东21000北31000地区月销售额东11000南11000北11000北21000西11000西21000东21000南21000北31000地区月销售额南11000西11000北11000西21000北21000东11000东21000南21000北31000地区月销售额东11000西11000北11000西21000北21000北31000东21000南11000南21000地区月销售额南11000西11000北11000北21000西21000北31000东11000东21000南21000地区月销售额东11000东21000北11000西11000西21000北21000南11000南21000北31000地区月销售额北11000南1