【诚展考勤干货】如何用Excel软件处理近10万条考勤记录？10分钟的时间-考勤易

每到新的一个月的月初，HR都要将上个月的考勤刷卡记录从考勤机上导出，然后在那里披星戴月地核对考勤记录，赶紧算出来再去算工资，毕竟员工的工资是不能拖延的！据以往经验，每个月初，小诚每天都会收到各种求助信息：

小诚，每个月的考勤数据导出后成千上万条，有些人一天打N次卡，我要怎么统计出所有员工的出勤情况啊？我们公司的班次还特别复杂，早班、夜班、正常班、周末班，我真是被逼疯了！有什么EXCEL方法能一次性处理好所有员工的考勤信息么？

看到大家都这么辛苦，小诚不得不再次请出咱们的“大表姐”Coco老师，拜托Coco老师结合她平时工作中的实际案例，

给大家详细介绍一下怎么用EXCEL处理打卡记录，轻松搞定每月考勤！

做考勤的HR都知道，每个考勤机都能导出【打卡原始记录】。在这个原始记录里，谁在什么时候打的卡，打了多少次卡，全都被记录下来了，而【报表】因为要设置迟到、早退、旷工以及加班的规则，所以要设置有效打卡的时间段。由于公司班次相对复杂，总会有人超出有效时间打卡，所以导致了报表出来的时候，就显示有人没打卡而旷工。

我们可以看到，考勤机的【打卡原始记录】长这样。里面包含【部门】、【姓名】、【考勤号码】，还有打卡的【日期时间】。

【迟到】——我们可以通过找到员工一天中第一次打卡时间，对比上班时间，判断员工是否迟到；【早退】——同理，通过找到该员工当天最后一次打卡时间，对比下班时间，判断员工是否早退；【加班】——通过计算最后一次打卡和第一次打卡之间的时间差，对比一天的应上班时长，判断员工的加班时间；【没打卡】——则对照请假单、出差申请单等，自行判断员工到底是旷工了还是忘打卡了。

以上的思路捋清楚之后，我们就可以在Excel表里开始出报表了。这里主要用到了以下几个函数：

【if】函数：根据判断条件的真伪，返回相应的值；【countifs】函数：多条件汇总，汇总满足多个条件的值；【index】函数：给出特定范围，根据相应的坐标，找到正确的值。

下面正式开始。

准备工作

将打卡【原始记录】中的【日期时间】分两列，分别显示【日期】和【时间】

我们可以看到，原始记录中的日期时间是合并显示在一列里面的，这对于我们后面的计算是不利的，所以我们第一步要让原始记录中的日期和时间分开显示，这里用到的功能是【数据分列】。选中原始记录中的【日期时间】这一列，点击菜单栏中的【数据】—【分列】，在弹出的对话框中，我们选择【固定宽度】：

然后点下一步，在日期和时间中间用鼠标点一下，建立一根分割线，或者在空白处点一下不放，然后拖动到日期和时间中间，建立一根分割线，就像这样：
诚展考勤记录EXCEL处理

建立好分割线之后，点击下一步，我们就可以对已经分割成两列的的数据，分别设置单元格格式了。这里我们知道左边一列应该是日期格式，右边一列是时间，我们设置好左边日期列的格式后，点一下右边时间列，设置右边列的格式，这里选择文本或者常规就好：

设置好格式之后，点击完成，对话框关闭，我们会看到Excel工作表中的之前日期、时间合并一列显示的数据，变成了

两列，就像这样：

然后，我们需要把这两列分别重新命名为【日期】和【时间】，完成这一步之后，我们可以从表中清楚的看见某个人在某一天一共打了多少次卡，有了这样一张表之后，我们就可以放飞自我啦！哦，不是，是可以计算每天的上下班打卡时间啦！

上班时间

第一步，找到每个人每天的第一次打卡时间

1、先判断【时间】列的打卡时间，分别是某个员工当天第几次打卡。

在E列【时间】列的右边新建一列（即F列），命名为【第几次打卡】，并在F2单元格中输入如下公式：

=COUNTIFS($B$2:B2,B2,$D$2:D2,D2)输好以后按回车，则F2单元格中显示了数字1，然后选中F2单元格，向下填充，则F列每个单元格就被1、2、3……这样的数字填满了，表示了所对应的E列的时间，分别是所对应的员工在当天的第几次打卡。就像这样的：

我们在F2单元格里输入这个公式的时候，用通俗的语言表达就是：在$B$2:B2这个区域里，符合B2（小薪）这个值，同时在$D$2:D2这个区域里，符合D2（2017/6/1）这个值的有多少个。通过看表，我们可以数出来这个区域符合这两个条件的只有1行。

当我们把F2单元格向下填充的时候，公式里面的区域以及条件值也会跟着变化，但是带有$符号的属于绝对引用，不会跟随目标单元格的变化而变化，所以我们可以看到F3单元格里的公式变成了：

=COUNTIFS($B$2:B3,B3,$D$2:D3,D3) 诚展EXCEL考勤处理

说明在$B$2:B3这个区域里，符合B3（小薪）这个值，同时在$D$2:D3这个区域里，符合D3（2017/6/1）这个值的有2个。（我们可以看到当我们的目标单元格从F2变成F3时，公式里面带$符号的引用没有跟随变化，但是不带$符号的引用都跟随变化了。）

同理，F4单元格的公式变成了：=COUNTIFS($B$2:B4,B4,$D$2:D4,D4)，表示符合在$B$2:B4和$D$2:D4这两个区域里，同时符合【B4（小薪）——D4（2017/6/1）】有3个。

通过这种方式，我们最终得出了E列的每一个打卡时间分别是某一天的第几次打卡。

2、新建一列，记录员工【上班打卡时间】不管员工一天打多少次卡，这里我们把每天第一次打卡定义为上班打卡。有了上一步的基础，找出上班打卡时间就容易得多了。上一步中，我们通过countifs函数知道了E列的每一个打卡时间分别是当天的第几次打卡，那么通过分析我们知道：只要F列的数值为1，则对应的E列中的时间就是上班打卡时间。

为了后面统计方便，我们在F列右边新建一列G列，命名为【上班打卡时间】，在G2单元格输入如下公式：=if（F2=1,E2,””），意思是“如果F2=1，则在G2中返回E2的值，否则返回空值”：

然后在选中G2，向下填充，通过这个操作，我们就把E列中每个人每天的第一次打卡时间按照人名和日期填充到了G列，就像这样：

下班时间

找到每个人每天最后一次打卡的时间

我们转换一下思路，最后一次打卡时间，可以先算当天一共打了多次卡。比如某个人一天一共打了4次卡，那第四次打卡，就是最后一次打卡了。

1、计算每人每天一共打多少次卡。这里我们依然用countifs函数，在最右边新建一列H列，命名【共计打卡次数/天】，在H2输入如下公式：=countifs(B:B,B2,D:D,D2)，输出如下：

诚展EXCEL考勤处理

小薪可能有强迫症吧，每天都打4次卡才安心。细心的同学可能发现了，H列和F列用的是同一个函数，只是参数中引用的方式、范围不一样，导致了输出结果不一样。

通俗地来讲，在F列中countifs函数每次都计算的是从B2/D2单元格到当前位置满足条件的单元格数量，从而算出的就是第几次打卡；而在H列中，countifs函数每次都是计算在整个B列和D列中，满足相应条件的一共有多少个单元格，从而输出每天每人的打卡次数。

2、输出显示每人每天最后一次打卡时间新建一列，让这一列只显示某一天下班打卡时间。这里我们用if函数嵌套index函数来实现。在I2中输入如下公式：=IF(G2<>””,INDEX(E2:$E$466,H2),””)

跟前面一样，if函数有三个参数：第一个是判断条件，【G2<>””】判断“G2单元格不是空值”这个命题是真命题还是假命题，”<>”是“不等于”的意思；如果是真命题，则返回第二个参数；如果是假命题则返回第三个参数【””】(空值)。

这里的第二个参数，也就是当命题为真时，则返回index函数的输出值——在E2:$E$466这列中第“H2”个单元格里的值。

如下：

诚展EXCEL考勤处理

其实这里我们用if函数其实是对下班打卡时间显示的位置进行了固定——只能显示在有上班打卡时间的单元格所对应的H列中，其他地方就空白显示。

这样就保证了一个人在一天中上下班打卡时间是显示在同一行中的，更加直观。当然我们也可以不用if来固定，直接用index函数，但是这样会让H列中每个单元格都被填充上时间值而且会有重复值出现。

最后修改

调整表格，去“糟粕”留精华

到这里，我们前期的处理步骤已经完成了一大半，但是为了美观好看，方便后面计算加班迟到，还是要继续调整。现在的表格当中有很多列当初是为了后面输出上下班打卡时间而被建立的，现在它们的使命已经完成，就该功成身退了，不然后面被嫌弃，比如F列、H列。但是不能直接暴力删除，因为G列I列还有对它们的引用，直接删除会引起G列I列报错。正确的处理方法是先将G列I列去除公式保存为数值格式（以前的教程中讲过）——选中G/I列复制，然后粘贴为值。

诚展EXCEL考勤处理