(Excel)常用函数公式及操作技巧之六：汇总计算与统计（二）

 (Excel)常用函数公式及操作技巧之六：

汇总计算与统计（二）

——通过知识共享树立个人品牌。

 统计数值大于等于80的单元格数目

在C17单元格中输入公式：

?

1	=COUNTIF(B1:B13,">=80")

确认后，即可统计出B1至B13单元格区域中，数值大于等于80的单元格数目。

计算出A1里有几个abc

A1: abc-ded-abc-def-abc-ded-ded-abc , 如何计算出A1里有几个abc

公式

?

1	=(LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc")

有条件统计

如何统计当A1<=15时，统计B列中<=8.5的累加值和个数，而>15时不进行统计？

个数：

?

1	=IF(A1>15,"",COUNTIF(B2:B10,"<=8.5"))

    累加值（求和）：

?

1	=IF(A1>15,"",SUMIF(B2:B10,"<=8.5"))

如何统计各年龄段的数量

需分别统计20岁以下、21-30岁、31-40岁、41-50岁、50岁以上年龄段的数量。

根据“出生日期”用以下公式，得到“自动显示年龄”。

先将F列的出生日期设置为“1976年5月”格式，在G列公式为：

?

1 2	=DATEDIF(F2,TODAY(),"Y")     （周岁，自动显示年龄） =YEAR(TODAY())-YEAR(F2)

再根据年龄段：20岁以下、21-30岁、31-40岁、41-50岁、50岁以上，用以下公式，求出不同年龄段人数。

在J2公式为：

?

1 2	=SUMPRODUCT(($G$2:$G$34>$H1)*($G$2:$G$34<=$H2)*($C$2:$C$34=J$1)) {=SUM(($G$2:$G$34<=VALUE(MID(I2,1,2)))*1)}

或数组公式：

?

1	{=SUM(($G$2:$G$34<=VALUE(MID(I3,4,2)))*1)-SUM($J$2:J2)}

如何计算20-50岁的人数?

?

1 2 3 4 5 6 7

=COUNTIF(C3:C17,">=20")-COUNTIF(C3:C17,">50")   =SUMPRODUCT((C3:C17>=20)*(C3:C17<=50))   =FREQUENCY(C3:C17,50)-FREQUENCY(C3:C17,19)   ｛=SUM(COUNTIF(C3:C17,">="&{20,51})*{1,-1})｝

如何统计40-50岁的人的个数

?

1 2 3 4 5 6 7

＝countif(a:a,">40")-countif(a:a,">50")   ＝SUM(COUNTIF(a:a,">"&{40,50})*{1,-1})   数组公式{=sum((a1:a7>40)*(a1:a7<50))}   ＝SUMPRODUCT((A1:A7>40)*(A1:A7<50))

要统计出7岁的女生人数

?

1 2	=COUNTIF(D2:D12,D2) =SUMPRODUCT((B2:B12="女")*(D2:D12=7))

统计人数

?

1 2	=COUNTA(A:A) =COUNTIF(A:A,"> ")

如何统计A1:A10，D1:D10中的人数？

?

1	=COUNTA(A1:A10,D1:D10)

如何让EXCEL自动从头统计到当前单元格

情况如下： C列要根据A列的内容来统计B列的数据，范围从A1：An，即当A列中An有数据时，Cn自动根据An的值，统计B1：Bn的数据。

?

1	｛=SUM(INDIRECT("B1:B" & LARGE((A1:A65535<>"")*(ROW(A1:A65535)),1)))｝

统计人数

建议	提建议人员姓名	提建议人数
建议1	王、李、赵、孙、钱、胡	6
建议2	张、王、李、赵、孙、钱、胡	7
建议3	张、王、李、孙、钱、胡	6

?

1 2	=LEN(B2)-LEN(SUBSTITUTE(B2,"、",""))+1 =LEN(SUBSTITUTE(B2,"、",""))

统计人数

见表：

性别	年龄
男	6
女	35
男	3
男	55
男	21
男	53.5
女	55
女	56
男	65
女	45
女	53
男	51

如何计算20-50岁的人数?

?

1 2 3 4

=COUNTIF(C3:C17,">=20")-COUNTIF(C3:C17,">50") =SUMPRODUCT((C3:C17>=20)*(C3:C17<=50)) =FREQUENCY(C3:C17,50)-FREQUENCY(C3:C17,19) ｛=SUM(COUNTIF(C3:C17,">="&{20,51})*{1,-1})｝

如何计算男20-50岁的人数?

?

1	=SUMPRODUCT((B3:B17="男")*(C3:C17>=20)*(C3:C17<=50))

求各分数段人数

?

1 2 3 4

90—100   =COUNTIF(B2:B43,">=90") 80—89    =COUNTIF(B2:B43,">=80")-COUNTIF(B2:B43,">=90") 70—79    =COUNTIF(B2:B43,">=70")-COUNTIF(B2:B43,">=80")<br>60—69    =COUNTIF(B2:B43,">=60")-COUNTIF(B2:B43,">=70") 50—59    =COUNTIF(B2:B43,">=50")-COUNTIF(B2:B43,">=60")

有什么方法统计偶数

例如：A1到E1有5个数如何统计着五个数中有几个是偶数

A B C D E F

1 50 15 8 11 15 3

在F1中的3要用什么公式能统计出来

统计偶数的个数

?

1 2	｛=COUNT(1/MOD(A1:E1-1,2))｝ ｛=Sum(Mod(a1:e1+1,2))｝

将偶数转化成奇数，再求奇数的个数。

请在编辑栏中选择部分公式按F9观察每一步的计算过程。

?

1 2 3

{=SUM(--((A1:F1)/2=INT((A1:F1)/2)))}   =SUMPRODUCT((MOD(A1:E1,2)=0)*1) =SUMPRODUCT(1-MOD(A1:E1,2))

如何显示

如果D2>20那E2就显示$200、如果D2>30那E2就显示$300依此类推

解答：=INT(D2/10)*100，当然，你的单元格格式设置成$格式就可以了。否则用，="$"&INT(D2/10)*100

则该单元格成字符型 。当然，你也可以用IF函数，但它有7层的限制。= IF (D2>30, "300",IF(D2>20,"200"))

工资统计中的问题

问题：表一和表二中的职工姓名相同,但不在同一个位置上。怎样用公式求出表一中职工在表二中对应的工资、奖金和值班费的总额。要求，不能用表二中先加入一列，然后求和，再用公式导入表一的方法。我想知道能否在表一中用一个公式就可实现，而表二不动。

?

1 2 3 4 5 6 7 8 9

=SUMPRODUCT((表二!$B$3:$B$42=A3)*(表二!$C$3:$E$42)+(表二!$G$3:$G$42=A3)*(表二!$H$3:$J$42))   =IF(COUNTIF(表二!$B$3:$B$42,A3),SUMPRODUCT(VLOOKUP(A3,表二!$B$3:$E$42,{2;3;4},)),SUMPRODUCT(VLOOKUP(A3,表二!$G$3:$J$42,{2;3;4},)))   =IF(ISERROR(MATCH(A3,表二!$B$3:$B$42,0)),SUM(OFFSET(表二!$G$2,MATCH(A3,表二!$G$3:$G$42,0),1,,3)),SUM(OFFSET(表二!$B$2,MATCH(A3,表二!$B$3:$B$42,0),1,,3)))   =IF(ISERROR(VLOOKUP(A3,表二!$B$3:$F$42,4,0)),SUM(INDIRECT("表二!H"&MATCH(A3,表二!$G$3:$G$42,0)+2&":J"&MATCH(A3,表二!$G$3:$G$42,0)+2)),SUM(INDIRECT("表二!C"&MATCH(A3,表二!$B$3:$B$42,0)+2&":J"&MATCH(A3,表二!$B$3:$B$42,0)+2)))   =IF(ISERROR(VLOOKUP(A3,表二!$B$3:$F$42,4,0)),VLOOKUP(A3,表二!$G$3:$J$42,4,0),VLOOKUP(A3,表二!$B$3:$F$42,4,0))

统计数据问题一例

如果我想统计50个数据中大于某个值的数据个数，（这个值是在使用时才输入某个单元格的），请问用什么函数。 如数据单元格为A1：E10，值的单元格为A11。

1、使用下面的数组公式： {=SUM(IF($A$1:$E$10>$A$11,1))}

2、输入以下函数： =COUNTIF(A1：E10,">"&A11)

根据给定的条件，对数据进行合计

实例：      姓名      件数      （姓名在B307-B313中；件数在C307-C313中）

                 李六       12  

                 王武       50  

                 李六       18  

                 陈丰       187

                 李六       49  

                 王武       135

                 陈丰       1584     

目的：      对上面三个人的件数分别进行统计分析        

步骤：      李六的：     =SUMIF(B307:B313,B323,C307:C313)      

                  王武的：    =SUMIF(B307:B313,C323,C307:C313)      

                  陈丰的：    =SUMIF(B307:B313,D323,C307:C313)      

姓名： 李六      王武      陈丰（分别在B323、C323、D323单元格中）

结果：  79       185       1771

十列数据合计成一列

?

1	=SUM(OFFSET($1,(ROW()-2)*10+1,,10,1))

统计汉字字符个数

中国              A1中"中国",A2中"人民",A3中是空白,A4中是"幸福",A5,A6中是空白

人民258           

258            

幸福           

247大家好       

中国147           

函数                               结果      说明

?

1 2 3

=SUMPRODUCT(LENB(ASC(A1:A6))-LEN(A1:A6))   11        仅统计汉字字符个数   =SUMPRODUCT(LEN(A1:A6))                    23       如果还混杂有其它字符

关于取数

购进日期	付款期
7月5日	2007-8-25
6月5日	2007-7-25
7月18日	2007-9-15
7月26日	2007-9-15

注：我想在B列的付款期中得到这样的结果：

付款期=（购进日期+45天），但我们的付款期只有每月15和25号，如果按购进日期加上45天后不正好是付款日，那就得再往后延到最近的一个付款日，也就是15或25号。

?

1 2 3 4 5 6 7 8 9

｛=MIN(IF(DAY(A2+ROW($45:$70))={15,25},A2+ROW($45:$70)))｝   ｛=MIN(IF(DAY(A2+ROW($45:$70))={15,25},A2+ROW($45:$70),999999))｝   ｛=MIN(IF((DAY(A2+ROW($45:$67))=15)+(DAY(A2+ROW($45:$67))=25),A2+ROW($45:$67)))｝   =IF(DAY(A2+45)<15,TEXT(A2+60-DAY(A2+45),"mm月dd日"),TEXT(A2+70-DAY(A2+45),"mm月dd日"))   =DATE(YEAR(A2+45),IF(DAY(A2+45)>25,MONTH(A2+45)+1,MONTH(A2+45)),IF(DAY(A2+45)<=15,15,IF(DAY(A2+45)<=25,25,15)))

统计单元格内不为空的格数

如下图，怎么自动统计单元格内的“√”，而空白的单元格则不计入内？

?

1 2 3 4 5

=counta(a2:a31),下拉   =countif(a2:a31,"√")   =COUNTIF(a2:a31,"<>")

自动将销量前十名的产品代号及销量填入表中

如：产品代号在“B”列，销量在“C”列

?

1 2	=INDIRECT("b"&MATCH(ROW(A1),$D$2:$D$20,0)+1) =INDIRECT("c"&MATCH(ROW(A1),$D$2:$D$20,0)+1)

统计最大的连续次数

如图，请问如何编写公式求出A1到A10单元格中数字4连在一起的次数，本例中答案应为3（A1到A3）和2（A9到A10）。

[1] A1到A10单元格中, 数字4连在一起, 最大的连续次数, 公式为 :

?

1	{=LARGE(FREQUENCY(IF(A1:A10=4,ROW(A1:A10),""),IF(A1:A10<>4,ROW(A1:A10),"")),1)}

[2] 次大的连续长次数, 公式为 :

?

1	{=LARGE(FREQUENCY(IF(A1:A10=4,ROW(A1:A10),""),IF(A1:A10<>4,ROW(A1:A10),"")),2)}

3个“不重复”个数统计

?

1 2 3 4 5

=SUM(--IF(MATCH(B$2:B$21,B$2:B$21,0)=ROW(B$2:B$21)-1,B$2:B$21>B2))+1   =SUM(--(IF(FREQUENCY(B$2:B$21,B$2:B$21),B$2:B$21>B2)))+1   =SUM(--(FREQUENCY(IF(B$2:B$21>B2,B$2:B$21),B$2:B$21)>0))+1

在一列有重复的姓名中，如何统计出具体有几人

如果第一个张三在A1单元格，在B1处输入：

?

1	=IF(COUNTIF($A$1:A1,A1)>1,"",A1)

向下复制即可

用数组公式也可以解决呀:假设你要统计A1到A100可以这样:

?

1	=sum(1/countif(a1:a100,a1:a100),然后按住crtl,shift,和回车就可以了。

计数的问题

　　这个例子主要是计数的问题：共有三列数据，分别统计每列字母的个数、每列有几个不同的字母，最后把它们分别列出来。对每列字母个数统计，字符用COUNTA()，数字可以用COUNT()和COUNTA()。公式分别为：

?

1 2 3 4 5

=COUNT(A2:A12)   =COUNTA(B2:B12)   =COUNTA(C2:C12)

每列不相同的字母，公式分别为：

?

1 2 3 4 5

｛=SUM(1/COUNTIF(A$2:A$12,A$2:A$12))｝   ｛=SUM(1/COUNTIF(B$2:B$12,B$2:B$12))｝   ｛=SUM(1/COUNTIF(C$2:C$12,C$2:C$12))｝

分别列出来，公式分别为：

?

1 2 3 4 5

｛=IF(SUM(1/COUNTIF(A$2:A$12,A$2:A$12))>=ROW(A1),INDEX(A$2:A$12,SMALL(IF(ROW(A$2:A$12)-1=MATCH(A$2:A$12,A$2:A$12,0),ROW(A$2:A$12)-1,"0"),ROW(A1))),"END")｝   ｛=IF(SUM(1/COUNTIF(B$2:B$12,B$2:B$12))>=ROW(B1),INDEX(B$2:B$12,SMALL(IF(ROW(B$2:B$12)-1=MATCH(B$2:B$12,B$2:B$12,0),ROW(B$2:B$12)-1,"0"),ROW(B1))),"END")｝   ｛=IF(SUM(1/COUNTIF(C$2:C$12,C$2:C$12))>=ROW(C1),INDEX(C$2:C$12,SMALL(IF(ROW(C$2:C$12)-1=MATCH(C$2:C$12,C$2:C$12,0),ROW(C$2:C$12)-1,"0"),ROW(C1))),"END")｝

列1	列2	列3
1	m	B
2	n	B
3	m	C
1	n	D
1	m	A
2	m	B
3	n	C
2	n	D
1	m	A
2	n	A
1	m	B
对每列字母个数统计:
11	11	11
每列不相同的字母有:
3	2	4
它们分别是:
1	m	B
2	n	C
3	END	D
END		A
		END

如何分班统计男女人数

姓名	班别	性别
高健丽	1	女
蔡美燕	2	女
张玉玫	3	女
蔡文文	4	女
陈娇娇	5	女
吴振宇	1	男
周婷婷	6	女
肖欣	6	女
梁丽宝	5	女
邱晓雯	4	女
李春梅	3	女
龙玉桦	2	女
阮梅英	1	女
梁光昕	2	男
…	…	…

班别	男	女	总人数
1	29	45	74
2	30	44	74
3	30	44	74
4	31	43	74
5	30	44	74
6	30	45	75

?

1 2 3 4 5 6 7 8 9 10 11

男=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1))   女=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=G$1))   男｛=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$F$1))｝   女｛=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$G$1))｝   男｛=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$G$1)*$D$2:$D$446)｝   女｛=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$H$1)*$D$2:$D$446)｝

增加d列，输入公式：=B2&C2，合并数据后再利用countif公式对D列统计。

?

1	=COUNTIF($B$2:$B$446,E2)

在几百几千个数据中发现重复项

我的意思不是查找功能,那个我会用,比如有几百个人的名字输入单元格中,但我面对那么多名字真无法短时间内看出谁重复了,该如何办?

假设判断区域为A1:D10,格式/条件格式，选公式(不是数值)，输入：

?

1	=COUNTIF($A$1:$D$10,A1)>1

然后在格式中设置一个字体或图案颜色,确定,这样重复数据就变成了有色单元格。

统计互不相同的数据个数

例如,在 3 * 3 的区域中统计互不相同的数据个数,

1 2 3 

3 2 1

1 2 0

结果应为 4 (4 个互不相同的数据)

　　数组公式

?

1	=sum(1/countif(a1:c3,a1:c3))

还可以公式:

?

1	=COUNT(IF(FREQUENCY(A1:C3,A1:C3),1))

多个工作表的单元格合并计算

?

1 2 3

=Sheet1!D4+Sheet2!D4+Sheet3!D4 更好的 =SUM(Sheet1:Sheet3!D4)

单个单元格中字符统计

假设 A1单元格中有数据"sdfsfjksfhweofiefondsfljsdfisdofjei"

　　如何用公式统计出A1单元格中有多个不重复的字符?

?

1	=SUMPRODUCT(--(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(97:122)),""))=1))

数组公式

?

1	=SUM(IF(ISERROR(FIND(CHAR(ROW(97:122)),A1)),,1))

这个公式只适用单元中的字符为小写字母,给个通用点的

?

1 2 3

=SUM(--(MATCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),)=ROW(INDIRECT("1:"&LEN(A2)))))   =SUM(IF(ISERROR(FIND(CHAR(ROW(97:122)),LOWER(A1))),,1))

数据区包含某一字符的项的总和,该用什么公式

?

1	=sumif(a:a,"*"&"某一字符"&"*",数据区)

函数如何实现分组编码

对数值进行分组编码

?

1	=A2&TEXT(COUNTIF($A$2:A2,A2),"00")