excel 如何按指定长度统计加班的个数

Excel函数 2021-11-25 12:44:08

工作中遇到用excel求指定长度，某条件的个数时，如何解决？还按照最原始的笨办法一个个数吗？那当你遇到900条数据甚至更多数据时，该怎么破？我大概估算了下，一个个改完后，需要大半天时间，而且准确率无法保证，最要命的是，眼睛会非常非常的疼，而且特别浪费你宝贵的时间哦！这到底是一个什么样的问题呢？

下图是我实际工作中遇到的问题，写成案例供大家参考。以D列的数据作为长度，求该数据所在行中条件=“上班”的个数，下图红色边框部分，结果见R列。为什么这样统计呢？实际上D列是这名员工未转正之前的应该上班天数，需要需要统计出转正前的上班打卡天数。所以就用到此技巧。

比如要统计第一行中从1号开始6个范围内的上班的个数。第二行就是统计12个范围内的上班的个数，以此类推。大家都应该知道用countif函数是解决“上班”统计的核心函数。本例的难点在于每一行的判断范围都是不一样的。这应该如何实现呢？

今天就来教大家掌握如何解决这种变动的（动态）判断范围的方法。

思路是这样：需要利用D列的数值参数，来决定E到Q列的每行选取范围。Excel专门有个函数解决此问题，就是传说中动态范围生成函数offset。

Offset函数是高级函数，共有五个参数，看着很吓人，实际掌握了并不难。函数的参数解析如下图：

Offset的第一个参数是基点，也就是起始单元格。

第二个参数是从基点单元格开始（不包含基点），向下偏移的单元格个数，上图是2，则说明基地从A5变成A7.

第三个参数是从A7单元开始，向右便宜的单元格个数，上图也是2，则说明从A7变成C7。

第四个参数是上图写的是5，是指从C7新基点单元格开始（包含C7），向下生成一个范围：C7：C11。

第五个参数上图写的是3，是指从C7：C11向右移动3列，产生一个C7：E11的一个单元格区域。

有没有晕,简单的理解是offset的是基于一个单元格开始，利用向下向后参数产生新的单元格区域的函数。

那我们来看本例的小伙伴的案例，要得到之前的列数的范围，只需要Offset的最后一次参数引用D2即可。

解决了offset这个函数的难点之后，后续就简单了，只需要在offset外面在套一个countif函数即可。完整的公式如下，由于是对每一行进行这样的判断，所以无需添加美元符号锁定。

=COUNTIF(OFFSET(E2,0,0,1,D2),"上班")

具体看动图的操作：

公式也不长，也算轻松搞定。

总结：offset+统计类函数（SUM，countif等）组合也是黄金搭档，专门解决动态的范围的判断引用。是Excel职场进阶函数。也希望职场小伙伴能够彪悍掌握。