玩转Excel：实用函数与技巧系列六 - 汇总计算与统计数据详解(第二部分)
(Excel)常用函数公式及操作技巧之六：
汇总计算与统计（二）
——通过知识共享树立个人品牌。
统计数值大于等于80的单元格数目
在C17单元格中输入公式：
=COUNTIF(B1:B13,">=80")
确认后，即可统计出B1至B13单元格区域中，数值大于等于80的单元格数目。
计算出A1里有几个abc
A1: abc-ded-abc-def-abc-ded-ded-abc , 如何计算出A1里有几个abc
公式
=(LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc")
有条件统计
如何统计当A1<=15时，统计B列中<=8.5的累加值和个数，而>15时不进行统计？
个数：
=IF(A1>15,"",COUNTIF(B2:B10,"<=8.5"))
累加值（求和）：
=IF(A1>15,"",SUMIF(B2:B10,"<=8.5"))
如何统计各年龄段的数量
需分别统计20岁以下、21-30岁、31-40岁、41-50岁、50岁以上年龄段的数量。
根据“出生日期”用以下公式，得到“自动显示年龄”。
先将F列的出生日期设置为“1976年5月”格式，在G列公式为：
=DATEDIF(F2,TODAY(),"Y") （周岁，自动显示年龄） =YEAR(TODAY())-YEAR(F2)
再根据年龄段：20岁以下、21-30岁、31-40岁、41-50岁、50岁以上，用以下公式，求出不同年龄段人数。
在J2公式为：
=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)}
或数组公式：
{=SUM(($G$2:$G$34<=VALUE(MID(I3,4,2)))*1)-SUM($J$2:J2)}
如何计算20-50岁的人数?
=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岁的人的个数
＝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岁的女生人数
=COUNTIF(D2:D12,D2) =SUMPRODUCT((B2:B12="女")*(D2:D12=7))
统计人数
=COUNTA(A:A) =COUNTIF(A:A,"> ")
如何统计A1:A10，D1:D10中的人数？
=COUNTA(A1:A10,D1:D10)
如何让EXCEL自动从头统计到当前单元格
情况如下： C列要根据A列的内容来统计B列的数据，范围从A1：An，即当A列中An有数据时，Cn自动根据An的值，统计B1：Bn的数据。
｛=SUM(INDIRECT("B1:B" & LARGE((A1:A65535<>"")*(ROW(A1:A65535)),1)))｝
统计人数
|
建议 |
提建议人员姓名 |
提建议人数 |
|
建议1 |
王、李、赵、孙、钱、胡 |
6 |
|
建议2 |
张、王、李、赵、孙、钱、胡 |
7 |
|
建议3 |
张、王、李、孙、钱、胡 |
6 |
=LEN(B2)-LEN(SUBSTITUTE(B2,"、",""))+1 =LEN(SUBSTITUTE(B2,"、",""))
统计人数
见表：
|
性别 |
年龄 |
|
男 |
6 |
|
女 |
35 |
|
男 |
3 |
|
男 |
55 |
|
男 |
21 |
|
男 |
53.5 |
|
女 |
55 |
|
女 |
56 |
|
男 |
65 |
|
女 |
45 |
|
女 |
53 |
|
男 |
51 |
如何计算20-50岁的人数?
=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岁的人数?
=SUMPRODUCT((B3:B17="男")*(C3:C17>=20)*(C3:C17<=50))
求各分数段人数
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")
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要用什么公式能统计出来
统计偶数的个数
｛=COUNT(1/MOD(A1:E1-1,2))｝ ｛=Sum(Mod(a1:e1+1,2))｝
将偶数转化成奇数，再求奇数的个数。
请在编辑栏中选择部分公式按F9观察每一步的计算过程。
{=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"))
工资统计中的问题
问题：表一和表二中的职工姓名相同,但不在同一个位置上。怎样用公式求出表一中职工在表二中对应的工资、奖金和值班费的总额。要求，不能用表二中先加入一列，然后求和，再用公式导入表一的方法。我想知道能否在表一中用一个公式就可实现，而表二不动。
=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
十列数据合计成一列
=SUM(OFFSET($1,(ROW()-2)*10+1,,10,1))
统计汉字字符个数
中国 A1中"中国",A2中"人民",A3中是空白,A4中是"幸福",A5,A6中是空白
人民258
258
幸福
247大家好
中国147
函数 结果 说明
=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号。
｛=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)))
统计单元格内不为空的格数
如下图，怎么自动统计单元格内的“√”，而空白的单元格则不计入内？
=counta(a2:a31),下拉 =countif(a2:a31,"√") =COUNTIF(a2:a31,"<>")
自动将销量前十名的产品代号及销量填入表中
如：产品代号在“B”列，销量在“C”列
=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连在一起, 最大的连续次数, 公式为 :
{=LARGE(FREQUENCY(IF(A1:A10=4,ROW(A1:A10),""),IF(A1:A10<>4,ROW(A1:A10),"")),1)}
[2] 次大的连续长次数, 公式为 :
{=LARGE(FREQUENCY(IF(A1:A10=4,ROW(A1:A10),""),IF(A1:A10<>4,ROW(A1:A10),"")),2)}
3个“不重复”个数统计
=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处输入：
=IF(COUNTIF($A$1:A1,A1)>1,"",A1)
向下复制即可
用数组公式也可以解决呀:假设你要统计A1到A100可以这样:
=sum(1/countif(a1:a100,a1:a100),然后按住crtl,shift,和回车就可以了。
计数的问题
这个例子主要是计数的问题：共有三列数据，分别统计每列字母的个数、每列有几个不同的字母，最后把它们分别列出来。对每列字母个数统计，字符用COUNTA()，数字可以用COUNT()和COUNTA()。公式分别为：
=COUNT(A2:A12) =COUNTA(B2:B12) =COUNTA(C2:C12)
每列不相同的字母，公式分别为：
｛=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))｝
分别列出来，公式分别为：
｛=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 |
|