哈喽,大家好~这里是略懂一点Excel的小六子~
今天来盘点一下Excel里 所有的统计公式,涉及函数有COUNTIF、 SUMPRODUCT、FREQUENC等,包括以下这些:
- 数字个数统计
- 文本个数统计
- 空单元格个数统计
- 非空单元格个数统计
- 按条件统计个数
- 使用通配符统计个数(包含类统计)
- 分区间段统计个数(如按分数段统计人数)
- 去重复后统计个数
- ……
温馨提示:本文共1800字,阅读时间5分钟,建议先收藏/转发,慢慢看。
第一类 常规个数统计
数字个数、非空单元格个数、空单元格个数
这类函数有三个,因为都是COUNT开头的,也被叫做COUNT函数“三兄弟”。
- COUNT:计算包含数字的单元格个数以及参数列表中数字的个数。
- COUNTA:计算包含任何类型的信息(包括错误值和空文本 (""))的单元格。
- COUNTBLANK:计算单元格区域中的空单元格的个数。
这三个函数非常简单,通过下面的示例有助于大家理解各自的功能。
扩展:针对文本和错误值的特殊统计
(1)只统计文本个数
=SUM(ISTEXT(B2:B12)*1)
(2)只统计错误值(不分错误类型)个数
=SUM(ISERROR(B2:B12)*1)
注:以上两个公式为数组公式,需要三键结束(CTRL+SHIFT+ENTER)。
第二类 按条件统计个数
使用通配符进行包含类模糊统计
常用的条件统计函数有COUNTIF、COUNTIFS以及SUMPRODUCT。它们可以统计符合条件的个数。
- COUNTIF函数可以按一组指定的条件进行统计
- 语法结构为:=COUNTIF(查找区域,查找条件)。
- COUNTIFS函数可以按多组指定的条件进行统计
- 语法结构为:=COUNTIFS(条件区域1,条件1,[条件区域2,条件2],…)。
下面用一组示例来说明两个函数的功能。
示例1:统计名单中的女性人数,公式为=COUNTIF(B:B,"女")
示例2:统计名单中男性且学历为本科的人数,公式为=COUNTIFS(B:B,"男",C:C,"本科")
示例3:统计名单中30岁以上的人数,公式为=COUNTIF(D:D,">30")
示例4:统计名单中30-40(含40)岁的人数公式为=COUNTIF(D:D,">30")-COUNTIF(D:D,">40")或=COUNTIFS(D:D,">30",D:D,"<=40")
重点:
函数中的条件可以使用通配符,即问号(?) 和星号(*)进行模糊统计。
- ?问号表示任何单个字符
- *星号表示任意字符串
- 查 找实际的问号或星号,则在字符前键入波浪符(~)
譬如统计有多少个姓王的,公式=COUNTIF(A2:A20,"王"&"*");
统计包含“彩”字的人数,公式=COUNTIF(A2:A20, "*"&"彩"&"*")。
除了这两个函数之外, SUMPRODUCT也可以实现条件统计。
第三类 分区间段统计个数
譬如统计不同分数段、年龄段的人数,统计不同价位段、销量段的产品种类数等等。
语法结构为:=FREQUENCY(数据源区域,区间分割值)
例如要统计人员名单中指定的年龄段的人数,就可以使用公式
=FREQUENCY(D2:D20,{25,35,40})完成统计。
函数要点:
1.非365版本需要选择填充公式的单元格区域G2:G5,然后输入公式后按Ctrl+Shift+Enter才能得到正确结果。
2.函数中的区间分割值以常量数组的形式,也就是在一对大括号里填写分界点对应的数字。
如果对此函数有疑惑可以看文章 《价格带统计就用FREQUENCY》。
第四类 统计不重复的个数
去掉数据中的重复值然后统计个数,分如下4种。
1.单条件统计不重复个数
譬如下表,统计姓名不重复的人数。判断重复的条件就一个——姓名不重复。
在F2单元格输入公式=SUMPRODUCT(1/COUNTIF(B2:B22,B2:B22))即可。
公式解析:
运用了单条件去重统计个数的套路公式
= SUMPRODUCT (1/COUNTIF(统计区域,条件区域))
重点:统计区域与条件区域通常是相同的。譬如,统计区域是B2:B22,条件区域也是B2:B22。
公式解析:
相对于单条件去重统计,用分类判断表达式取代了常数1。
公式结构:
= SUMPRODUCT (([判断区域]比较符号[分组条件])/COUNTIF(统计区域,条件区域))
3.多条件统计不重复个数
还是上表,统计不重复的总人数。判断重复的条件有两个——组别和姓名都相同的才算重复。
在F4中输入公式:
=SUMPRODUCT(1/COUNTIFS(A2:A22,A2:A22,B2:B22,B2:B22))即可。
最后再来提一个问题:如果只统计重复的个数怎么统计呢?
好的,以上。
原创不易,如果今天内容对你有帮助的话,记得帮我们点赞、在看、转发哟~~
小伙伴们
扫码进入微信群交流~
Excel基础不扎实的同学,欢迎学习下面这套工作用得上的Excel训练营返回搜狐,查看更多
责任编辑: