我前不久曾教过大家如何统计区域内符合多个条件的单元格个数,详情请参阅 Excel – 统计同时满足多个条件的单元格数,你要用几个函数?
但是有些同学反馈自己的 Excel 版本比较低,无法使用 countifs 函数,咨询有没有低版本函数能替代的公式?
那我今天就教大家几个 countif 的使用案例。
案例 1:按区间统计
下图 1 的 A 至 C 列是各销售人员每个月的单笔提成记录,可能每月有多笔提成,也可能没有。
需要在右侧按不同的金额区间统计总提成笔数。
效果如下图 2 所示。
![b6c30ac7af8d603abab0f344afa170c5.png](https://i-blog.csdnimg.cn/blog_migrate/f29c1a5e3292f7a725cb8e3fedbb1972.jpeg)
![3a9498f9eef11caf0b64648d54ce6c66.png](https://i-blog.csdnimg.cn/blog_migrate/4a8f8c16f1713fb4dbe0e028384d8619.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 的个数,从而实现区间统计
![bbdaeee9462970dac4ad44cea82be438.png](https://i-blog.csdnimg.cn/blog_migrate/6d4349ecca2cf0e84910cb93ca73c193.jpeg)
![1a3ac9030c0718f8daaab54e80e2b477.png](https://i-blog.csdnimg.cn/blog_migrate/e9e6e6ae631e7d853712142ac7eba4c2.jpeg)
2. F3 单元格中的公式只要将区间数据稍加变通即可:
=SUM(COUNTIF($C$2:$C$24,{">=1500",">=2000"})*{1,-1})
![aeb87fef9fdc985bab04f62175b41e7d.png](https://i-blog.csdnimg.cn/blog_migrate/c2902529231bb7c79e5d8d799f8dfedc.jpeg)
![a25c01a241517001c591ecf484b7bb8a.png](https://i-blog.csdnimg.cn/blog_migrate/8ba0b4bce355b527cc4b6e4975438757.jpeg)
案例 2:统计不重复人数
在 E7 单元格中计算整个数据表中的非重复姓名数。
![37ecabdc604b6bc0178109a25cc40b53.png](https://i-blog.csdnimg.cn/blog_migrate/9be260b414b16f799ebddd60cf37781d.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
- 数组公式,需要按三键结束。
![6281160a610dd9ac4b8b9c3f0eac19be.png](https://i-blog.csdnimg.cn/blog_migrate/529af645dc474cc91505ca5a91913860.jpeg)
![aa042597d9039cd3df46746ced61136a.png](https://i-blog.csdnimg.cn/blog_migrate/1a451acbfd731306c572a32f51d1c85f.jpeg)
案例 3:统计非空提成数
统计数据表区域内提成数不为空的单元格数。
![88c9a8603568e5d37198e25b88c858b0.png](https://i-blog.csdnimg.cn/blog_migrate/713c41eaa4a4cec6e5a06bc513a7164b.jpeg)
解决方案 3:
统计非空单元格数可以使用函数 counta。
1. 在 E11 单元格中输入以下公式:
=COUNTA(C2:C24)
* counta 函数用于计算范围中不为空的单元格的个数。由于 C2:C24 的非空单元格都是数值,所以本例也可以将 counta 替换为 count 函数。
![d6a73ea796a3baada4db4e98ec9a132b.png](https://i-blog.csdnimg.cn/blog_migrate/ed919d073a2308058f6d77b939e54bb5.jpeg)
![db3d5da7beb63951228684cf40b6fa2c.png](https://i-blog.csdnimg.cn/blog_migrate/e42b5b77cdddbb23b291e1b199ec1352.jpeg)
如果用 countif 函数也能实现同样的效果。
2. 在 E12 单元格中输入以下公式:
=COUNTIF(C2:C24,"<>")
公式释义:
- "<>":表示非空
- COUNTIF(C2:C24,"<>"):统计参数区域内非空单元格的个数
![b4d077b20f365e2a6a57d290604089a2.png](https://i-blog.csdnimg.cn/blog_migrate/5c9d2de9be44986ca4e8cfc9576492ff.jpeg)
![0228875fd6e1a9d5a2d7a54e6b32ed2f.png](https://i-blog.csdnimg.cn/blog_migrate/b2d3ee533dd2e4f663ad6b9caeed3b86.jpeg)
案例 4:统计空提成数
统计数据表区域内提成为空的单元格数。
解决方案 4:
统计空单元格数同样有专门的函数 countblank。
1. 在 F11 单元格中输入以下公式:
=COUNTBLANK(C2:C24)
![ad1b2090e3e25d664e2c3bcf7a49b5a3.png](https://i-blog.csdnimg.cn/blog_migrate/8e11b5c7db59fbec304fd916bcfbbb70.jpeg)
![6bd58cc37130b4e5ceadad56e8839b98.png](https://i-blog.csdnimg.cn/blog_migrate/427157da63952c6e9aa6bf5c4a02830d.jpeg)
这个案例一样也可以用 countif 函数来实现。
2. 在 F12 单元格中输入以下公式:
=COUNTIF(C2:C24,"")
公式释义:
- "":表示空
- COUNTIF(C2:C24,""):统计参数区域内空单元格的个数
![3859170b25ae82f7889e86f07d665662.png](https://i-blog.csdnimg.cn/blog_migrate/49f1a685694532864716c537a5b34011.jpeg)
![58d8eeb7c9a6f7821e3bdc88bfab20fc.png](https://i-blog.csdnimg.cn/blog_migrate/db2008933c3d9fafc11d99255c233a60.jpeg)
很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。
现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。