作者:Coco 来源:薪人薪事企小薪(ID:xrxs_qixiaoxin) 据以往经验,每到月底,就会有好多同学发来求助信息:每个月的考勤数据导出后成千上万条,有些人一天打 N 次卡,我要怎么统计出所有员工的出勤情况啊?我们公司的班次还特别复杂,早班、夜班、正常班、周末班,我真是被逼疯了! 做考勤的 HR 都知道,每个考勤机都能导出【打卡原始记录】。在这个原始记录里,谁在什么时候打的卡,打了多少次卡,全都被记录下来了。而【报表】因为要设置迟到、早退、旷工以及加班的规则,所以要设置有效打卡的时间段。由于公司班次复杂,总会有人超出有效时间打卡,所以报表出来的时候,显示有人没打卡而旷工。我们先来分析一下,打卡机给出的报表,比原始打卡记录多出来的信息有:迟到、早退、加班、以及没打卡的记录。这里的没打卡的记录,有的可能是因为员工请假了,有的可能是出差外出了,还有的可能是忘打卡了,最严重的是旷工了。 【迟到】——我们可以通过找到员工一天中第一次打卡时间,对比上班时间,判断员工是否迟到;【早退】——同理,通过找到该员工当天最后一次打卡时间,对比下班时间,判断员工是否早退;【加班】——通过计算最后一次打卡和第一次打卡之间的时间差,对比一天的应上班时长,判断员工的加班时间;【没打卡】——则对照请假单、出差申请单等,自行判断员工到底是旷工了还是忘打卡了。以上思路捋清之后,我们就可以在 Excel 表里开始操作了。countifs 函数:多条件汇总,汇总满足多个条件的值;index 函数:给出特定范围,根据相应的坐标,找到正确的值。我们可以看到,原始记录中日期时间合并显示在一列里,这对后面的计算不利。然后把这两列分别重命名为「日期」和「时间」,这样我们可以从表中清楚地看见某个人在某一天共打了多少次卡。有了这样一张表之后,我们就可以放飞自我啦!哦,不是,是可以计算每天的上下班打卡时间啦!❶ 先判断「时间」列的打卡时间,分别是某个员工当天第几次打卡。在 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 单元格向下填充的时候,公式里面的区域以及条件值也会跟着变化。但是带有 $ 符号的属于绝对引用,不会跟随目标单元格的变化而变化。=COUNTIFS($B$2:B3,B3,$D$2:D3,D3)说明在 $B$2:B3 这个区域里,符合 B3(小薪)这个值,同时在 $D$2:D3 这个区域里,符合 D3(2017/6/1)这个值的有 2 个。(我们可以看到,当我们的目标单元格从 F2 变成 F3 时,公式里面带 $ 符号的引用没有跟随变化,但是不带 $ 符号的引用都跟随变化了。) =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 列,命名为「上班打卡时间」。意思是,如果 F2=1,则在 G2 中返回 E2 的值,否则返回空值:然后向下填充,通过这个操作,我们就把 E 列中每个人每天的第一次打卡时间填充到了 G 列,就像这样:我们转换一下思路,最后一次打卡时间,可以先算当天一共打了多次卡。比如某个人一天一共打了 4 次卡,那第 4 次打卡,就是最后一次打卡了。这里我们依然用 Countifs 函数,在最右边新建一列 H 列,命名「共计打卡次数/天」。细心的同学可能发现了,H 列和 F 列用的是同一个函数,只是参数中引用的方式、范围不一样,导致了输出结果不一样。通俗地来讲,在 F 列中 Countifs 函数每次都计算的从 B2/D2 单元格到当前位置满足条件的单元格数量,从而算出的就是第几次打卡;而在 H 列中,Countifs 函数每次都是计算整个 B 列和 D 列中,满足相应条件的一共有多少个单元格,从而输出每天每人的打卡次数。这里我们用 if 函数嵌套 index 函数来实现。=IF(G2<>'',INDEX(E2:$E$466,H2),'')第一个是判断条件,【G2<>''】判断「G2 单元格不是空值」这个命题是真命题还是假命题,其中“<>”是「不等于」的意思;这里的第二个参数,也就是当命题为真时,返回 index 函数的输出值——在 E2:$E$466 这列中第「H2」个单元格里的值:其实这里我们用 if 函数,是对下班打卡时间显示的位置进行了固定——只能显示在有上班打卡时间的单元格所对应的 H 列中。这样就保证一个人在一天中,上下班打卡时间显示在同一行,更加直观。当然我们也可以不用 if 来固定,直接用 index 函数,但是这样会让 H 列中每个单元格都被填充上时间,而且会有重复值出现。到这里,我们前期的处理步骤已经完成了一大半!(撒花花~)但是为了美观好看,方便后面计算加班迟到,还要继续调整。现在的表格中,有很多列是为了输出上下班打卡时间而建立的,现在它们的使命已经完成,就该功成身退了~但是不能直接暴力删除,因为 G 列 I 列对它们有引用,直接删除会引起报错。先将 G 列 I 列保存为数值格式——选中 G 列 I 列复制,然后粘贴为值。操作完这一步,就可以删除原来的 F 列和 H 列,仅保留上下班打卡时间啦:删除了两列后,表格清爽了很多,但是还有一个问题,现在的 F 列和 G 列中那么些空格也挺遭人嫌弃的。
|