EXCEL COUNTIF()的一些奇特的用法

xiao_chuai

于 2022-07-28 16:22:34 发布

阅读量2k

点赞数

分类专栏： EXCEL 文章标签： excel

原文链接：https://baijiahao.baidu.com/s?id=1731792430973409324&wfr=spider&for=pc

版权

EXCEL 专栏收录该内容

4 篇文章 3 订阅

订阅专栏

文章目录

前言
一、统计第几次重复
二、统计不重复的数量
三、通配符模糊统计
四、防止重复录入
五、忽略错误值或空值统计
六、重复值填充背景色
总结

前言

日常工作中需要度娘很多知识点或者方法，但每次用了就忘，下次遇到就需要继续度娘，故在此记录能解决相关问题的文章并做简单的个人理解，以此达到加深印象，分享知识的目的。

#excel函数#COUNTIF函数是Excel中的单条件计数函数，语法是：=COUNTIF(区域，条件)，第二个参数条件可以是数字、表达式或文本形式。COUNTIF函数有不少高级用法，一般教学过程中很少会全部提及到，今天小包来给小伙伴们做个详细的归纳讲解。

一、统计第几次重复

COUNTIF函数合理利用混合引用可以对目标数据内容进行“第几次重复”统计，不理解绝对引用、相对引用和混合引用的小伙伴可以点击观看学习：小包Excel速成课程第七节：相对、绝对及混合引用区别，实例讲解！如图1所示，要对部门进行排序，在A2单元格输入公式为：

=COUNTIF($B$2:B2,B2)

并向下填充，第一个参数区域要进行混合引用，在A2单元格时，区域为$B$2:B2，在A3单元格时，区域为$B$2:B3，在A4单元格时，区域为$B$2:B4，以此类推，这样就会形成一个动态的条件区域。
在这里插入图片描述

二、统计不重复的数量

如图2所示，要统计不重复的部门数量，其公式为：

=SUM(1/COUNTIF(B2:B10,B2:B10))，然后同时按下数组三键Ctrl+Shift+Enter，
或者
=SUMPRODUCT(1/COUNTIF(B2:B10,B2:B10))，无需按数组三键。

COUNTIF(B2:B10,B2:B10)第二个参数B2:B10为连续的单元格条件区域，会形成条件数组{“人事部”;“人事部”;“行政部”;“推广部”;“推广部”;“推广部”;“客服部”;“客服部”;“售后部”}，COUNTIF(B2:B10,B2:B10)会对每一个部门进行计数，结果为：{2;2;1;3;3;3;2;2;1}，然后用1除以这个数组，结果为：{0.5;0.5;1;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;1}，例如说“人事部”重复两次，用1除后，每个人事部就会统计为0.5次，再用SUM函数求和，那么两个人事部就是1次，其他部门以此类推，这样就能统计到不重复次数。

个人理解：
1.可以加上条件，例如

=SUM((B2:B10<>“推广部”)*(1/COUNTIF(B2:B10,B2:B10)))，数组三键

那么这个公式的意思就变为“统计B2:B10这个区域中不等于“推广部”的不重复的部门数量”，此时结果就会变成4，也可以继续加多个条件进去，相当于“对目标区域做各种条件筛选，筛选后剩下的数据再进行去重，去重完毕后，统计剩下的单元格个数”这样的手动操作，自己手动操作一遍，就很好理解这个公式的应用了，那么条件如何添加也会更加清晰。

三、通配符模糊统计

如图3所示，要统计姓“张”员工的数量，其公式为：

=COUNTIF(A2:A11,“张*”)

，*表示任意匹配多个通配符，"张*"就能匹配到区域中所有以张为开头的字符串。

要统计非“张”性员工人数，其公式为：=COUNTIF(A2:A10,“<>张*”)，
如果要引用单元格，其公式为：=COUNTIF(A2:A10,“<>”&D5)。

在这里插入图片描述

四、防止重复录入

Excel数据验证是盛久不衰的话题，如何在录入数据时防止重复录入？Excel职场加分项：6个常见案例让你了解数据验证！赶快收下！譬如说统计员工姓名，如何防止姓名重复录入（假设没有重复姓名）？如图5所示，当我重复录入“张三”时，会提示出错并禁止输入成功。

选中A列，点击数据——有效性——“允许”中选择自定义，如图6所示，在公式里面输入:=COUNTIF(A:A,A1)<2,点击确定即可。

在这里插入图片描述

五、忽略错误值或空值统计

如图所示，要忽略各种错误值Excel速成第十节：错误值类型有哪些？怎么出现的？如何规避？，统计销量显示正常的人数，其公式为：=COUNTIF(C2:C10,“<9e307”)，9e307为Excel中最大的数值，<9e307表示满足小于这个数值的条件并且忽略错误值，也能忽略空值，将错误值删除，结果不变。
在这里插入图片描述