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

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

分享到:

收藏收藏人气：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，得到的结果如下图所示。

E9和F9单元格显示列号，其中的内容分别是5、6。B9:H9就是我们要构造的FREQUENCY函数的第二参数，即分段点。

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

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

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

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

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

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

对于案例来说：分段点是B9:H9，其中的空格被忽略不计（注：这是FREQUENCY的一个特点——自动忽略任意一个参数中非数值型的数据），有效的分段点是5和6；数据源是B8:H8，实际上有效的数据源是2、3、4、7、8。对于FREQUENCY函数的统计区间，有一点大家要注意——区间的数量总是比第2参数（即分段点）的数量多一个。

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

现在我们来看一下数据源落在各区间内的情况，数据源中落在第一个区间内（即小于等于5）的数据是2、3、4，一共是3个数据，所以，区间1内的数据个数是3；数据源中没有任何数据落在第二个区间内（即大于5，且小于等于6），所以，区间2内的数据个数是0；数据源中落在第三个区间内（即大于6）的数据是7和8，一共是2个数据，所以，区间3中的数据个数是2。如下图所示。

根据分析，落在3个区间内数据的数量分别是3、0、2，这3个数，构成一个数组来作为FREQUENCY函数的返回结果，这也就是刚才我们通过F9键查看到的B10中的数据内容。所以呢，3、0、2这三个数据就表示一周内张三的加班情况：连续加了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}”，如下图所示。

大家看，拿王五来说，实际的分段点是2、4、6（B12:H12为第二参数，该区域中的空格被忽略），数据源是3、5、7、8（B11:H11中为第一参数，该区域中的空格被忽略）。所以，经过对区间分段并对落在各区间内的数据数量进行统计之后，可以得到0、1、1、2这样一组数据，具体的对应关系，如下图所示。

好了，至此，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，得到的结果如下图所示。

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

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