本文介绍了如何利用FastReport将多条考勤记录整合到一个单元格，快速核对8000条记录，轻松识别迟到和早退情况，提升工作效率。
作者：Coco
来源：薪人薪事企小薪(ID：xrxs_qixiaoxin)
不知不觉又到了月底，HR 们想必超级抓狂…… 因为八月份的考勤记录已经出来啦！ 据以往经验，每到月底，就会有好多同学发来求助信息：每个月的考勤数据导出后成千上万条，有些人一天打 N 次卡，我要怎么统计出所有员工的出勤情况啊？ 我们公司的班次还特别复杂，早班、夜班、正常班、周末班，我真是被逼疯了！有什么方法，能一次性处理好所有员工的考勤信息么？
01
做考勤的 HR 都知道，每个考勤机都能导出【打卡原始记录】。 在这个原始记录里，谁在什么时候打的卡，打了多少次卡，全都被记录下来了。 而【报表】因为要设置迟到、早退、旷工以及加班的规则，所以要设置有效打卡的时间段。 由于公司班次复杂，总会有人超出有效时间打卡，所以报表出来的时候，显示有人没打卡而旷工。 我们可以看到，考勤机的【打卡原始记录】长这样：
我们先来分析一下，打卡机给出的报表，比原始打卡记录多出来的信息有：迟到、早退、加班、以及没打卡的记录。
这里的没打卡的记录，有的可能是因为员工请假了，有的可能是出差外出了，还有的可能是忘打卡了，最严重的是旷工了。
然后我们捋一下思路：
【迟到】——我们可以通过找到员工一天中第一次打卡时间，对比上班时间，判断员工是否迟到；
【早退】——同理，通过找到该员工当天最后一次打卡时间，对比下班时间，判断员工是否早退；
【加班】——通过计算最后一次打卡和第一次打卡之间的时间差，对比一天的应上班时长，判断员工的加班时间；
【没打卡】——则对照请假单、出差申请单等，自行判断员工到底是旷工了还是忘打卡了。
以上思路捋清之后，我们就可以在 Excel 表里开始操作了。
这里主要用到了以下几个函数：
if 函数：根据判断条件的真伪，返回相应的值；
countifs 函数：多条件汇总，汇总满足多个条件的值；
index 函数：给出特定范围，根据相应的坐标，找到正确的值。
02
下面正式开始~ 我们可以看到，原始记录中日期时间合并显示在一列里，这对后面的计算不利。 所以首先要将日期、时间分两列。
然后把这两列分别重命名为「日期」和「时间」，这样我们可以从表中清楚地看见某个人在某一天共打了多少次卡。
有了这样一张表之后，我们就可以放飞自我啦！哦，不是，是
可以计算每天的上下班打卡时间啦！
◆ 上班时间 ◆
第一步，找到每个人每天的第一次打卡时间。 ❶ 先判断「时间」列的打卡时间，分别是某个员工当天第几次打卡。 在 E 列「时间」列的右边新建一列(即 F 列)，命名为【第几次打卡】，并在 F2 单元格中输入如下公式： =COUNTIFS($B$2:B2,B2,$D$2:D2,D2) 输好以后按回车，则 F2 单元格中显示了数字 1，然后向下填充。 则 F 列每个单元格就被1、2、3、4……这样的数字填满了，表示了所对应的 E 列的时间，分别是员工在当天的第几次打卡：
那这是怎么实现的呢？我们来剖析一下 Countifs 这个函数。
Countif 函数，是用来统计指定(单个)区域符合特定(单个)条件单元格计数。
Countifs 函数则是统计指定(多个)区域符合(多个)条件的单元格个数。
COUNTIFS($B$2:B2,B2,$D$2:D2,D2)这个公式里面有两个区域——[$B$2:B2]和[$D$2:D2]，还有两个特定条件——[B2]和[D2]。
我们在 F2 单元格里输入这个公式的时候，用通俗的语言表达就是：
在 $B$2:B2 这个区域里，符合 B2(小薪)这个值，同时在 $D$2:D2 这个区域里，符合 D2(2017/6/1)这个值的有多少个。
我们可以直接看出，这个区域符合这两个条件的只有 1 行。
当我们把 F2 单元格向下填充的时候，公式里面的区域以及条件值也会跟着变化。
但是带有 $ 符号的属于绝对引用，不会跟随目标单元格的变化而变化。
所以我们可以看到 F3 单元格里的公式变成了：
=COUNTIFS($B$2:B3,B3,$D$2:D3,D3)
说明在 $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 列的每一个打卡时间分别是某一天的第几次打卡。
❷ 新建一列，记录员工「上班打卡时间」。不管员工一天打多少次卡，
这里我们把每天第一次打卡定义为上班打卡。
有了上一步的基础，找出上班打卡时间就容易得多了。上一步中，我们通过 Countifs 函数知道了 E 列的每一个打卡时间分别是当天的第几次打卡。通过分析我们知道：
只要 F 列的数值为 1，则对应的 E 列中的时间就是上班打卡时间。为了后面统计方便，我们在 F 列右边新建一列 G 列，命名为「上班打卡时间」。在 G2 单元格输入如下公式：
=if(F2=1,E2,””)意思是，如果 F2=1，则在 G2 中返回 E2 的值，否则返回空值：
然后向下填充，通过这个操作，我们就把 E 列中每个人每天的第一次打卡时间填充到了 G 列，就像这样：
◆ 下班时间 ◆
找到每个人每天最后一次打卡的时间。 我们转换一下思路，最后一次打卡时间，可以先算当天一共打了多次卡。比如某个人一天一共打了 4 次卡，那第 4 次打卡，就是最后一次打卡了。❶ 计算每人每天一共打多少次卡。这里我们依然用 Countifs 函数，在最右边新建一列 H 列，命名「共计打卡次数/天」。在 H2 输入如下公式： =countifs(B:B,B2,D:D,D2)
细心的同学可能发现了，H 列和 F 列用的是同一个函数，只是参数中引用的方式、范围不一样，导致了输出结果不一样。通俗地来讲，在 F 列中 Countifs 函数每次都计算的从 B2/D2 单元格到当前位置满足条件的单元格数量，从而算出的就是第几次打卡；而在 H 列中，Countifs 函数每次都是计算整个 B 列和 D 列中，满足相应条件的一共有多少个单元格，从而输出每天每人的打卡次数。❷
输出显示每人每天最后一次打卡时间。新建一列，让这一列只显示某一天下班打卡时间。这里我们用 if 函数嵌套 index 函数来实现。在 I2 中输入如下公式：
=IF(G2<>"",INDEX(E2:$E$466,H2),"")跟前面一样，if 函数有三个参数：第一个是判断条件，【G2<>""】判断「G2 单元格不是空值」这个命题是真命题还是假命题，其中“<>”是「不等于」的意思；如果是真命题，则返回第二个参数；如果是假命题则返回第三个参数，即空值。这里的第二个参数，也就是当命题为真时，返回 index 函数的输出值——在 E2:$E$466 这列中第「H2」个单元格里的值：
其实这里我们用 if 函数，是对下班打卡时间显示的位置进行了固定——只能显示在有上班打卡时间的单元格所对应的 H 列中。这样就保证一个人在一天中，上下班打卡时间显示在同一行，更加直观。当然我们也可以不用 if 来固定，直接用 index 函数，但是这样会让 H 列中每个单元格都被填充上时间，而且会有重复值出现。
◆ 最后修改 ◆
调整表格，去「糟粕」留精华。到这里，我们前期的处理步骤已经完成了一大半！(撒花花~)但是为了美观好看，方便后面计算加班迟到，还要继续调整。现在的表格中，有很多列是为了输出上下班打卡时间而建立的，现在它们的使命已经完成，就该功成身退了~比如 F 列、H 列。但是不能直接暴力删除，因为 G 列 I 列对它们有引用，直接删除会引起报错。 正确的处理方法是：先将 G 列 I 列保存为数值格式——选中 G 列 I 列复制，然后粘贴为值。
操作完这一步，就可以删除原来的 F 列和 H 列，仅保留上下班打卡时间啦：
删除了两列后，表格清爽了很多，但是还有一个问题，现在的 F 列和 G 列中那么些空格也挺遭人嫌弃的。这里用筛选命令，选出空白单元格删除就可以。选中 F 列，点击表格右上角「筛选」，这时「上班打卡」单元格右下角会显示一个小三角；我们点击小三角，选择「空白」：
然后我们的表格变成了这样：
这时我们把显示的这些行全部删除：
不要哭，数据没有消失~刚才对上班打卡这一列进行了筛选，现在我们让其他非空单元格显示出来就好了，点击【上班打卡】旁边的小箭头，选择【全选】——【确定】：
然后，我们的表格就变成了这个样子：
这里可以把 E 列「时间」也删掉，得到这样的结构，表格会更清爽：
部门—姓名—日期—上班打卡时间—下班打卡时间，一行全部搞定啦！
◆ 查找迟到人员 ◆
8:30 上班，只要 E 列上班打卡时间大于 8:30:00，则对应的员工在当天视为迟到(这里假设员工没有请假没有外出)。选中 E 列，在菜单栏中依次点击【条件格式】-【突出显示单元格规则】-【大于】：
然后在弹出的对话框里这样设置：
这时我们已经看到，E 列中打卡时间晚于 8:30 的被标红突出显示。我们可以很清楚的看到某个员工哪天迟到了。emm 小薪这个月迟到次数有点多，一会我得找她谈谈。
除了迟到，早退、是否加班，我们也可以通过这个表判断~
03
以上就是如何手动处理打卡记录的方法啦！如果操作熟练，也可以用上面提到的函数进行嵌套实现~
最后，给坚持看到这里的同学点个赞！
扫一扫
374
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
