教你几分钟搞定的考勤数据!

Excel函数 2021-12-27 21:30:07

面对新的统计需求，很多人会一下变懵，不知如何办。如果涉及的统计有一千多行数据，哭的心思都有了：什么时候才能下班哟!今天老菜鸟通过考勤统计分析表实例分享自己面对新统计需求的解决方法：简化数据、找数据规律、做辅助列。任何复杂的统计，只要采取这样的方法，大多几分钟就会搞定。

考勤管理，是企业劳动纪律管理的最基本工作，公司领导要求人力资源每周按部门汇总一份考勤打卡情况通报，格式如下：

练习课件到QQ群：264539405下载

通报表可以更为宏观的看到考勤纪律的执行情况，是非常好的一个管理工具，但是负责制作报表的伙伴却是异常烦恼。不为别的，就是不知道该如何下手，无奈之下，找到老菜鸟求助。

我们先来看看从考勤软件导出的数据源吧：

整个表有二十多列，一千多行，如果仅靠手工统计，难度确实不小。如果是你接到这个任务时，会如何着手开始统计呢?

其实问题并没有看上去那么复杂，关键是找到思路。思路决定出路，最终完成这个统计表并没有用什么高大上的函数，也不是像有些伙伴猜的那样，用VBA去解决，只是用了几个常用的函数IF、COUNTIF、SUM和SUMIF就搞定了。是不是很想知道方法呢?

拿到数据，切忌盲目动手，而是首先整理数据。就当前通报表来说，数据源中对我们有用处的列并不多，只需要保留部门、工号、上下班的打卡结果这四列即可：

看上去清爽了很多吧?删去了无用数据之后，就需要分析具体的统计思路，同时在数据源中寻找可以利用的规律。很容易看到，每个工号对应的打卡结果都是6行2列(12个单元格)的一个数据区域：

我们需要根据这12个单元格中的信息进行分类统计，按照要求分为正常和异常两类。其中异常包括了：缺卡、请假、迟到和早退四种情况，只要没有异常即可视为正常。

根据统计要求添加辅助列。辅助列的作用就是简化问题的难度系数，在这个问题中我们可以这样设置：

在继续往下前，需要理清楚这里统计的6个数据的关系。通报表是按人数而非次数统计的，因此可以得出这些结论：

◎同一工号一周内不管缺卡多少次，都算缺卡1人;

◎同一工号一周内不管请假多少次，都算请假1人;

◎迟到、早退规则同上;

◎同一工号，一周内不管缺卡+请假+迟到+早退有多少人，只要大于0，都算异常1人;

◎同一工号，一周内正常人数等于1-异常。

很多时候我们是不建议使用合并单元格的，因为合并单元格会带来很多不方便。但是这个例子中，每6行数据(一个工号的数据)统计出一行结果，所以使用合并单元格会更方便。分别把E2:E7、F2:F7、G2:G7、H2:H7、I2:I7、J2:J7合并，然后在G2中输入公式：

=IF(COUNTIF($C2:$D7,G$1)>0,1,0)

右拉填充公式，4项异常都进行了统计。

从结果来看，这四项异常数据的统计结果是正确的。

这个公式的核心是COUNTIF，当区域中符合条件的个数大于0时，得到1，否则得到0。注意区域和条件的写法，$C2:$D7锁定了列，G$1锁定了行，这样公式在右拉和下拉的时候就不用再去修改了。

异常的统计，只要后面四项之和大于0就算异常1人，公式为：=IF(SUM(G2:J7)>0,1,0)。这个公式很简单了，相信大家都能明白。

到这一步，正常的统计更没什么难度：

选中统计好的6个单元格，双击J2单元格的右下角，公式会自动向下填充至数据的最后一行，可以浏览一下结果：

咦!早退和迟到的统计结果出现了错误，怎么解决?

有些朋友可能想到了用通配符，没错，就是通配符，不过不用修改公式，只需要将表头修改一下就好了：

看明白了吧，在表头“迟到”和“早退”的两边都加上*，统计结果就正确了。

数据源经过以上处理以后，要做出最终的统计表，已经完全不是问题了：

在职人数公式：=COUNTIF(数据源!A:A,汇总!B4)/6。至于为什么要除以6，不难理解。

正常打卡人数：=SUMIF(数据源!A:A,B4,数据源!E:E)，SUMIF最基本的用法哦;

占比：=D4/C4

后面的几列都是用SUMIF统计人数，用除法计算占比。

最后总结一下：

今天的通报表乍一看很棘手，平时工作可能也会遇到类似的情况。解决的办法就是：首先排除无用的数据，然后是寻找数据规律，根据统计需要使用辅助列，最后通过一些基本的操作，例如累计、排序、合并，还有填充等，完成任务。

今天的分享重在体会问题的解决过程，涉及到的公式都非常简单，相信大家在动手练习的过程中一定会有很多收获的。