原文标题:《这个函数比COUNTIF好用3倍,助你高效工作,到点下班!》
许久不见的同学老王突然找到我。
他是一名人民教师,每次测验都想快速了解班上学生的成绩状况:
低于60分的有多少人,60-80分的有多少人,80-90分的有多少人,90分以上又有多少人?
如下图,根据C列成绩,统计各个等级的人数。
如果是你,会怎么做?
有一定基础的同学,第一反应可能是用COUNTIF函数。
那我们先来看看COUNTIF的做法。
COUNTIF函数法
使用COUNTIF函数,我们需要在G列输入以下公式:
❶G3单元格输入公式,求成绩低于60的人数。
=COUNTIF(C:C,"<60")
❷G4单元格输入公式,求成绩大于等于60,小于80分的人数。
=COUNTIFS(C:C,">59",C:C,"<=79")
❸G5单元格输入公式,求成绩大于等于80,小于90分的人数。
=COUNTIFS(C:C,">79",C:C,"<=89")
❹G6单元格输入公式,求成绩大于等于90分的人数。
=COUNTIF(C:C,">89")
很明显,用COUNTIF函数,我们需要一个个编写公式,好麻烦啊!
下面必须拿出我的「绝招」了——FREQUENCY函数。
只需一个公式,就能完成分段统计人数。
接下来我们来看看它的做法吧。
FREQUENCY函数法
选中G3:G6单元格,在公式栏中输入公式:
=FREQUENCY(C3:C18,{59,79,89})
按下[CTRL+SHIFT+ENTER]三键进行数组运算,结果就全都出来啦。
是不是超级简单!
现在,我们知道FREQUENCY函数的做法了,但是它究竟是啥?
看起来好复杂啊!别担心!接下来我们就来讲解它的语法规则。
基本语法如下:
=FREQUENCY(Data_array,Bins_array)
=FREQUENCY(统计的区域,分组的界点值)
FREQUENCY函数有两个参数,它是用来计算数值在某个区域内的出现频率,然后返回一个垂直的数组。
我们来看看前面案例的公式:
=FREQUENCY(C3:C18,{59,79,89})
公式我们可以理解为:
需要统计的数值在单元格区域C3:C18,它作为第1参数:数值分布在一个数值轴上;
再根据数组中{59,79,89}这3个数值作为分段点,为第2参数:它们将数值轴上的数据分隔成4个区间。
做成图示如下:
最后,FREQUENCY函数会帮我们数一数各个区间范围的数值个数,返回一个垂直的数组即:{2;11;1;2}
是不是很简单!
当我们需要对数值分段统计个数时,用FREQUENCY函数解决更高效、更简洁。
你以为FREQUENCY函数只能用在统计个数?那你就错了!
由于FREQUENCY函数还忽略逻辑值;它的「分组的界点值」可以是乱序,允许重复。
当该分段点首次出现时返回其统计的个数,其后重复出现的分段点返回的是0,最后1个返回大于分段点最大值的个数。
下面做一个简单示例来验证这一特性:
所以,它还可以:
❶计算不重复值的个数
❷统计最大连续次数
❸统计文本分布频率
❹查找最接近目标值的数值
……
举个简单的例子,它可以做中国式排名。
即无论有几个并列第1名,之后的排名仍然是第2名,也就是并列排名不占用名次。
在D3单元格输入如下公式,然后按[CTRL+SHIFT+ENTER]三键,下拉填充。
=SUM(--(FREQUENCY(C$3:C$11,IF(C$3:C$11>=C3,C$3:C$11))>0))
简单解析一下:
IF(C$3:C$11>=C3,C$3:C$11),如果C$3:C$11区域的值大于等于C3,则返回对应的数值,否则返回逻辑值False;
利用FREQUENCY忽略逻辑值的特性,将大于等于C3单元格的值作为分段点;
因为重复出现的分段点返回的是0,所以返回的结果的非零数就是大于等于C3的不重复个数;
最后用SUM函数求非零的个数,就是C3在C$3:C$11的排名。
小结
下面对FREQUENCY函数做个小结:
❶当我们需要对数值分段统计个数时,可以使用FREQUENCY函数;
❷FREQUENCY返回的结果为数组,需要用数组公式的形式输入。
按[CTRL+SHIFT+ENTER]三键;
❸「分组的界点值」中的每一分段点,是以左开右闭的规则来分组。
如公式:
=FREQUENCY(C3:C18,{59,79,89})
分别统计的范围为:数值≤59,59<数值≤79,79<数值≤89,数值>89。
❹FREQUENCY函数忽略空白、文本、逻辑值,它的「分组的界点值」可以是乱序,允许分段点重复。
❺对于「分组的界点值」中重复出现的分段点数据,只在该分段点首次出现时返回其统计的个数;
其后重复出现的分段点返回的是0,最后1个返回大于分段最大值的个数。
由于文章的篇幅有限,今天我们暂且学习下Frequency函数的基本用法,有关它的更多用法,我们后续继续探讨。