Excel常用函数公式及操作技巧之六汇总计算与统计二.docx
《Excel常用函数公式及操作技巧之六汇总计算与统计二.docx》由会员分享，可在线阅读，更多相关《Excel常用函数公式及操作技巧之六汇总计算与统计二.docx（15页珍藏版）》请在冰豆网上搜索。
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个数如何统计着五个数中有几个是偶数
ABCDEF
15015811153
在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单元格中）
结果：
791851771
十列数据合计成一列
=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
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
男=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列统计。
=COUNTIF（$B$2:
$B$446,E2）
在几百几千个数据中发现重复项
我的意思不是查找功能,那个我会用,比如有几百个人的名字输入单元格中,但我面对那么多名字真无法短时间内看出谁重复了,该如何办?
假设判断区域为A1:
D10,格式/条件格式，选公式（不是数值），输入：
=COUNTIF（$A$1:
$D$10,A1）>1
然后在格式中设置一个字体或图案颜色,确定,这样重复数据就变成了有色单元格。
统计互不相同的数据个数
例如,在3*3的区域中统计互不相同的数据个数,
123
321
120
结果应为4（4个互不相同的数据）
数组公式=sum（1/countif（a1:
c3,a1:
c3））
还可以公式:
=COUNT（IF（FREQUENCY（A1:
C3,A1:
C3）,1））
多个工作表的单元格合并计算
=Sheet1!
D4+Sheet2!
D4+Sheet3!
D4，更好的=SUM（Sheet1:
Sheet3!
D4）
单个单元格中字符统计
假设A1单元格中有数据"sdfsfjksfhweofiefondsfljsdfisdofjei"
如何用公式统计出A1单元格中有多个不重复的字符?
=SUMPRODUCT（--（LEN（A1）-LEN（SUBSTITUTE（A1,CHAR（ROW（97:
122））,""））=1））
数组公式=SUM（IF（ISERROR（FIND（CHAR（ROW（97:
122））,A1））,,1））
这个公式只适用单元中的字符为小写字母,给个通用点的
=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））
数据区包含某一字符的项的总和,该用什么公式
=sumif（a:
a,"*"&"某一字符"&"*",数据区）
函数如何实现分组编码
对数值进行分组编码
=A2&TEXT（COUNTIF（$A$2:
A2,A2）,"00"）
©2011EricHu
原创作品，转贴请注明作者和出处，留此信息。
------------------------------------------------
cnBlobs:
CSDN：
作者：
EricHu（DB、C\S、B\S、WebService、WCF、PM等）
出处：
QQ：
80368704E-Mail:
80368704@
本博文欢迎大家浏览和转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，在『参考』的文章中，我会表明参考的文章来源，尊重他人版权。
若您发现我侵犯了您的版权，请及时与我联系。
更多文章请看[置顶]索引贴——（不断更新中）
升级会员 