二维码 购物车
部落窝在线教育欢迎您!

强大的FREQUENCY函数,计算连续加班天数的利器

 

作者:阿硕来源:部落窝教育发布时间:2021-11-26 09:54:29点击:3362

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

今天给大家讲一讲FREQUENCY函数,在解决连续计数的问题上有着得天独厚的优势,比如计算连续晴天的天数、连续比赛胜利的场数、连续取得某成绩(如大于80分)的次数等。下面,就让我们一起来学习吧!

 

哈喽,大家好,欢迎来到部落窝教育!我是阿硕。

最近,上进青年小张摊上事儿了。公司领导 “作妖”,发布新规定说:哪位员工在一周之内连续加班三天或三天以上,将额外发一笔连续加班奖励金。这个令人头疼的统计问题,就落在了小张的头上。

先看数据,如下图所示,A列是员工姓名,B列至H列是日期以及相应日期中员工的加班情况,共分为“加班“和”未加班“两种。

 

 

解决这种连续计数的问题,可以用一个特殊的计数函数——FREQUENCY函数。下面,就让我们一起来学习吧!

FREQUENCY函数一共有两个参数,它的函数表达式是=FREQUENCY(数据源,分段点)

我们首先对张三的加班和未加班情况分别写一条IF函数。在B8中,我们输入“=IF(B2="加班",COLUMN(B:B),"")”,然后向右复制填充单元格至H8,得到的结果如下图所示。

 

 

这个IF函数的作用就是进行一个判断:如果某一天张三加班,则会在第8行相应的单元格中显示该列的列号,否则显示空格。B8:H8就是我们要构造的FREQUENCY函数的第一参数,即数据源。

 

接下来,我们在B9中输入“=IF(B2="未加班",COLUMN(),"")”,然后向右复制填充公式至H9,得到的结果如下图所示。

 

 

E9F9单元格显示列号,其中的内容分别是56B9:H9就是我们要构造的FREQUENCY函数的第二参数,即分段点。

 

好了,写完上述两条IF函数之后,我们今天的主角FREQUENCY函数就要出场了。我们在B10中输入“=FREQUENCY(B8:H8,B9:H9)”,得到的结果如下图所示。

 

 

乍看上去,FREQUENCY函数得到的结果是3,实则不然。

在函数编辑栏选中公式,按下F9键,查看计算结果。B10中的实际数据是“{3;0;2}”,如下图。(可按Esc键退出查看)

 

 

FREQUENCY函数实际是返回了一个数组,数组中共有3个数据,分别是302。这3个数据是怎么得来的呢?这就要详细讲一讲FREQUENCY函数的原理了。

 

FREQUENCY函数的功能是统计数据源被分段点分割之后,所形成的各个区间内的数据的数量,也就是统计数据源中的数据落在各个区间内的数量,所以,它本质上是一个计数函数。

 

在统计的时候,FREQUENCY函数统计的是大于前1个分段点,且小于或等于当前分段点的数值的个数。这么说起来,可能有点抽象,我们用刚才的数据来套一下,就比较容易理解了。

 

对于案例来说:分段点是B9:H9,其中的空格被忽略不计(注:这是FREQUENCY的一个特点——自动忽略任意一个参数中非数值型的数据),有效的分段点是56;数据源是B8:H8,实际上有效的数据源是23478。对于FREQUENCY函数的统计区间,有一点大家要注意——区间的数量总是比第2参数(即分段点)的数量多一个。

 

打一个比方,假设我们面前有一条绳子,如果剪1刀,则会产生2段绳子,如果剪2刀,则会产生3段绳子,如果剪3刀,则会产生4段绳子,其余以此类推。对于张三来说,分段点为56,一共是2个分段点,所以最终会构造出3个统计区间,这3个区间分别是“小于等于5”,“大于5,且小于等于6”,“大于6”。

 

现在我们来看一下数据源落在各区间内的情况,数据源中落在第一个区间内(即小于等于5)的数据是234,一共是3个数据,所以,区间1内的数据个数是3;数据源中没有任何数据落在第二个区间内(即大于5,且小于等于6),所以,区间2内的数据个数是0;数据源中落在第三个区间内(即大于6)的数据是78,一共是2个数据,所以,区间3中的数据个数是2。如下图所示。

 

 

根据分析,落在3个区间内数据的数量分别是302,这3个数,构成一个数组来作为FREQUENCY函数的返回结果,这也就是刚才我们通过F9键查看到的B10中的数据内容。所以呢,302这三个数据就表示一周内张三的加班情况:连续加了3天班,然后连续2天没加班(即加连续加了0天班),之后又连续加了2天班。同时,显而易见,张三的最长的连续加班天数是3天。

 

为了让大家充分理解FREQUENCY函数,我们再以王五为例,看一下他的加班情况。我们在B11中输入“=IF(B3="加班",COLUMN(B:B),""),然后向右复制填充公式至H11,在B12中输入“=IF(B3="未加班",COLUMN(B:B),"")”,然后向右填充公式到H12,如下图所示。

 

 

B13中,我们输入“=FREQUENCY(B11:H11,B12:H12)”,如下图所示。

 

 

通过F9键,我们可以查看B13中的内容。可以看到,B13中的内容也是一个数组,数组中的数据是“{0;1;1;2}”,如下图所示。

 

 

大家看,拿王五来说,实际的分段点是246B12:H12为第二参数,该区域中的空格被忽略),数据源是3578B11:H11中为第一参数,该区域中的空格被忽略)。所以,经过对区间分段并对落在各区间内的数据数量进行统计之后,可以得到0112这样一组数据,具体的对应关系,如下图所示。

 

 

好了,至此,FREQUENCY函数的逻辑,就讲明白了。在FREQUENCY函数计算出加班的具体分布情况后,我们在其外面再嵌套一个MAX函数,就可以得到员工最长的连续加班天数了。如果员工的最长加班天数大于等于3,那么这名员工在一周内肯定有过连续三天加班的情况;否则,他就没有过连续三天加班的情况,也就拿不到连续加班奖励金啦!还是以王五为例,我们把B13中的函数修改为“=MAX(FREQUENCY(B11:H11,B12:H12))”,则可以看到,王五的最长连续加班天数为2,这样看来,王五是拿不到连续加班奖励金的!

 

 

现在,我们在I2中输入“=FREQUENCY(IF(B2:H2="加班",COLUMN(B2:H2),""),IF(B2:H2="未加班",COLUMN(B2:H2),""))”,输完后按Ctrl+Shift+Enter构造数组,然后向下复制填充公式至I6,得到的结果如下图所示。

 

 

要注意,I2I6中的数据都是数组,小伙伴们可以通过F9键查看其中的内容。为了便于理解 ,阿硕为大家做了整理,如下图所示。

 

 

接下来,我们在FREQUENCY函数外面嵌套一个MAX函数,就可以计算出每个员工的最长连续加班天数啦!我们将I2中的公式修改为“=MAX(FREQUENCY(IF(B2:H2="加班",COLUMN(B2:H2),""),IF(B2:H2="未加班",COLUMN(B2:H2),"")))”,输入后按Ctrl+Shift+Enter构造数组,然后向下复制填充公式至I6,得到的结果如下图所示。

 

 

可以看到,张三、王五、朱七、程九、李四的最长连续加班天数分别为32410天。统计到这里,大家应该能看出来哪位员工可以得到连续加班奖励金了吧!

 

好了,今天的内容就是这些,你学会了吗?

 

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

将excel中的数据按照指定数量进行重复,非得用VBA才能实现吗?

如何在特定位置批量插入空行等12种实用办公技巧

4种删除excel重复值的小妙招,速收藏

Power Query的数据替换技巧比Excel函数更万能!

版权申明:

本文作者阿硕;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。