我前不久曾教过大家如何统计区域内符合多个条件的单元格个数,详情请参阅 Excel – 统计同时满足多个条件的单元格数,你要用几个函数?
但是有些同学反馈自己的 Excel 版本比较低,无法使用 countifs 函数,咨询有没有低版本函数能替代的公式?
那我今天就教大家几个 countif 的使用案例。
案例 1:按区间统计
下图 1 的 A 至 C 列是各销售人员每个月的单笔提成记录,可能每月有多笔提成,也可能没有。
需要在右侧按不同的金额区间统计总提成笔数。
效果如下图 2 所示。
![2b80e3955a13536d4af53c7721d22ecb.png](https://i-blog.csdnimg.cn/blog_migrate/0210b624405379fbc7723c0f9f66128e.jpeg)
![527614798d50e9782ffe3f91f1ecdfa4.png](https://i-blog.csdnimg.cn/blog_migrate/d30f210b86d8623775e8a62665c2c7b2.jpeg)
解决方案 1:
1. 在 F2 单元格中输入以下公式:
=SUM(COUNTIF($C$2:$C$24,{">=1000",">=1500"})*{1,-1})
公式释义:
- COUNTIF($C$2:$C$24,{">=1000",">=1500"}):分别统计出提成大于等于1000 和大于等于 1500 的个数,返回一组数组
- ...*{1,-1}:用上述数组乘以另一个数组 {1,-1},再次等到一个数组,其中大于等于 1000 的个数为正数,而大于等于 1500 的个数为负数
- 最后用 SUM 函数将数组中的每个数相加,相当于用大于等于1000 的个数减去大于等于 1500 的个数,从而实现区间统计
![01b4cfdc696764e7c5de5cbddf3f9e92.png](https://i-blog.csdnimg.cn/blog_migrate/4f8dcac8f5139f5dc7a437ac1a3c25b2.jpeg)
![82501df9c5f30f0cfca4a98369afa0c3.png](https://i-blog.csdnimg.cn/blog_migrate/0c2812e80ac4cc44576fe7f48e96960e.jpeg)
2. F3 单元格中的公式只要将区间数据稍加变通即可:
=SUM(COUNTIF($C$2:$C$24,{">=1500",">=2000"})*{1,-1})
![7a4c46a0ddbfd669a9fe07ed4e9fae31.png](https://i-blog.csdnimg.cn/blog_migrate/5f207a064cbf2da664a58e67d69d989b.jpeg)
![7b1b25714f11277d8636157ad7df55b9.png](https://i-blog.csdnimg.cn/blog_migrate/b4d2e169c8bee7f7159c8d514721070a.jpeg)
案例 2:统计不重复人数
在 E7 单元格中计算整个数据表中的非重复姓名数。
![da389e4cd0d58aeb1ad7398f4b4eb2cd.png](https://i-blog.csdnimg.cn/blog_migrate/edb7f91029a5c5583ce0e43f36ddb9eb.jpeg)
解决方案 2:
1. 在 E7 单元格中输入以下公式 --> 按 Ctrl+Shift+Enter 结束:
=SUM(1/COUNTIF(B2:B24,B2:B24))
公式释义:
- COUNTIF(B2:B24,B2:B24):统计 B2:B24 区域中,每个名字出现的次数;本例的结果为 {3;4;2;4;4;2;1;3;3;4;2;4;4;4;4;3;4;3;4;4;2;4;3}
- 1/COUNTIF(B2:B24,B2:B24):用 1 除以上述数组,得到一组分数
- SUM(1/COUNTIF(B2:B24,B2:B24)):将以上分数相加,就是每个名字出现的唯一次数。比如,“赵铁锤”出现过 3 次,数组中就会有 3 个 1/3,sum 求和的结果正好为 1
- 数组公式,需要按三键结束。
![21d97ca4c99ca540c8bbc82ac1083e54.png](https://i-blog.csdnimg.cn/blog_migrate/41966edbf0004b2dcb59e9dcb2421fc6.jpeg)
![6753e62eb46402227af5823ae8f3a1ab.png](https://i-blog.csdnimg.cn/blog_migrate/697bb863ad17d29aadf7693488b0f910.jpeg)
案例 3:统计非空提成数
统计数据表区域内提成数不为空的单元格数。
![5f074ddd88516bfbdf4fe6aa2424010b.png](https://i-blog.csdnimg.cn/blog_migrate/7cf934279f7d7bd762d77e6020a32811.jpeg)
解决方案 3:
统计非空单元格数可以使用函数 counta。
1. 在 E11 单元格中输入以下公式:
=COUNTA(C2:C24)
* counta 函数用于计算范围中不为空的单元格的个数。由于 C2:C24 的非空单元格都是数值,所以本例也可以将 counta 替换为 count 函数。
![633b54c569dd6ced718d38bb585be933.png](https://i-blog.csdnimg.cn/blog_migrate/bfe7e209ae21ba2309902bb95b6f75ca.jpeg)
![9543069c5ba61d01fdb6cdbcfe77d7e1.png](https://i-blog.csdnimg.cn/blog_migrate/160357561114d6b71a0db525352b8d17.jpeg)
如果用 countif 函数也能实现同样的效果。
2. 在 E12 单元格中输入以下公式:
=COUNTIF(C2:C24,"<>")
公式释义:
- "<>":表示非空
- COUNTIF(C2:C24,"<>"):统计参数区域内非空单元格的个数
![d73347f388cb1fd9f595be0485d62e88.png](https://i-blog.csdnimg.cn/blog_migrate/f3d3570f9fe020bcf309c2b68a1c26ae.jpeg)
![9dd6a392c3d1ef8b628a28ce921c782d.png](https://i-blog.csdnimg.cn/blog_migrate/ac72dce75ad53fe2e54db179b1c1baef.jpeg)
案例 4:统计空提成数
统计数据表区域内提成为空的单元格数。
解决方案 4:
统计空单元格数同样有专门的函数 countblank。
1. 在 F11 单元格中输入以下公式:
=COUNTBLANK(C2:C24)
![729b1c39471949d11d5f04d39621eae7.png](https://i-blog.csdnimg.cn/blog_migrate/ee01c507ba93ab919fe6123a5fcc86b7.jpeg)
![b5b084818f1a7c205ee73c3740cef406.png](https://i-blog.csdnimg.cn/blog_migrate/f883a4539e1d4fca1768fb1a2bcaab27.jpeg)
这个案例一样也可以用 countif 函数来实现。
2. 在 F12 单元格中输入以下公式:
=COUNTIF(C2:C24,"")
公式释义:
- "":表示空
- COUNTIF(C2:C24,""):统计参数区域内空单元格的个数
![c275c76f4de1ef3e8d3a96ecc1fb4c19.png](https://i-blog.csdnimg.cn/blog_migrate/865b042913e716c19413fa4be130d6b6.jpeg)
![9e9402dc2d73767dcbe56381b4e0160a.png](https://i-blog.csdnimg.cn/blog_migrate/0be8b02b9ad50b2f30d4f4b9bb0aa495.jpeg)
很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。
现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。