excel数据处理技巧:打卡日期和时间同格,如何进行考勤统计?

编按:根据考勤机数据统计考勤表是每个考勤人员的磨难之一。有的考勤机导出的数据打卡时间都在一个单元格里,有的考勤机导出的数据日期和打卡时间在一个单元格里。如何由这些数据快速生成考勤统计表呢?


考勤是每个公司主抓制度之一,每个公司都会安排1~2个人,甚至更多的人来负责考勤。学习更多技巧,请收藏关注部落窝教育excel图文教程

考勤也和每一个打工者息息相关,准确的说是和打工者的工资有着密切关系。早上总会看到为了不迟到而奔跑的人,晚上快下班的时候,大家做得最多的一件事情,就是看时间。

无论是迟到还是早退,其实它的衡量标准很简单,就是“打卡时间”,如果打卡时间晚于上班时间就是“迟到”,早于下班时间就是“早退”。在EXCEL中,我们用函数或者VBA按这条标准进行判断,可以智能地、快速地依据考勤机数据得到考勤统计表。

笔者E图表述接触过多种考勤机数据,发现考勤机导出的数据依据打卡时间的记录方式主要分为三类:打卡时间独立型日期和打卡时间混合型同一单元格多个打卡时间型。我们分别来说明一下。

说明:以下数据统一按照上班时间8:00,下班时间17:00来计算迟到、早退情况。

一、打卡时间独立型

特点:每一行就是一次打卡记录,而且打卡日期和打卡时间是分开的两列字段。

这种考勤应该是最常见,也是处理最简单的一类数据源。

笔者解法:

首先增加上午/下午辅助列,并用公式自动判定填写上午或下午。

E2单元格输入函数:=IF(D2<=VALUE("12:00:00"),"上午","下午")

函数解析:VALUE("12:00:00")将12:00:00转化成时间格式,然后与D列的时间进行对比,得到记录条中打卡时间是“上午”还是“下午”,向下填充函数到E2:E12单元格区域。

然后增加迟到/早退辅助列,并用公式自动判定填写。

F2单元格输入函数:=IF(E2="上午",IF(D2>VALUE("8:00:00"),"迟到","正常"),IF(D2<value("17:00:00"),"< span="" style="margin-top: 0px; margin-bottom: 0px; padding: 0px;">早退","正常"))

函数解析:通过IF函数,判断打卡是迟到、早退,还是正常。如果打卡记录是“上午”,那么判断是否大于8:00:00,是就是“迟到”,不是就是“正常”;“下午”,则判断是否小于17:00:00,是就是“早退”,不是就是“正常”。

最后这样处理源数据后,我们就可以使用“索引函数”将这些内容匹配到《考勤统计表》中,如下所示:

在K2单元格输入函数:=LOOKUP(1,0/(($B$2:$B$12=$I2)*($C$2:$C$12=K$1)*($E$2:$E$12=$J2)),($F$2:$F$12))

函数解析:多条件索引的方式有很多、很多,笔者在这里采用的是LOOKUP。LOOKUP(1,0/…)是一个很常用的多条件索引方法,之前的教程中也有涉及,这里就不再赘述。给大家一个公式以后可以套用。

=LOOKUP(1,0/((条件1)*(条件2)*(条件3)*…*(条件n)),(被查询目标列))

通过上图我们可以看到很多错误码#N/A,那些就需要其他的判断了。例如L2单元格应该是“上午漏打卡”,O2和P2单元格则是“公休”。这不是本文的重点,也不算难点,无外乎用IF函数判断条件再返回需要的说明即可,我们就不介绍了。学习更多技巧,请收藏关注部落窝教育excel图文教程

二、日期和打卡时间混合型

特点:日期和时间在一个单元格中,导出的数据基本都是文本格式。

日期有两种常见格式:一种带分隔符,如2020/09/01;一种不带分隔符,如20200901。

日期都是双位数显示,如1月——01,12月——12;1日——01,8日——08。

处理这样的内容,完全可以先将打卡记录处理成常规的【独立时间型】之后,然后再来处理考勤。以“打卡记录2”为例,如下:

首先获得日期列。

在E2单元格输入函数:=--LEFT(D2,10)

函数解析:用文本函数Left直接提取日期部分,再用两个符号【--】转换为数值,最后调整单元格格式为日期就可以了。

然后获得时间列。

在F2单元格输入函数:=--RIGHT(D2,LEN(D2)-11)

函数解析:用文本函数Right提取文本右侧的时间。LEN(D2)得到文本的总长度,11是日期长度10加一个空格长度,差值就是时间部分的长度。

G列、H列的处理同上例,索引到《考勤统计表》中也和上例一样,是不是融会贯通了?

三、同一单元格多个打卡时间型

特点:同一人当天的多次打卡时间都在一个单元格中

对于考勤统计来说,这种数据是最难处理的。而且我们上图是模拟数据,实际情况中,一个单元格中可能不止一两条记录。

处理这样的数据,笔者的总体思路还是先将其转化成第一种数据类型那样的表,然后再来统计。

这里用函数处理,难度比较大,运行效率也比较低(数据量大了后),所以推荐给大家一个VBA的做法吧。(不要谈VBA就色变,VBA很实用而且不难学,有兴趣的同学可以通过我们的QQ群找到辅导老师。)

简单的几行代码就把数据A2:D7处理到F2:I13单元格区域了,那么接下的操作就回到了我们第一例中了。代码如下:


说明:每行代码蓝色引号'''后的灰色部分是笔者对代码的注解,方便大家了解代码的含义。蓝色引号在代码中表示注释,其后内容不参与运行。

Sub 数据清理()

Dim arr, brr '''定义两个变量,作为数组使用

With Sheets("多个时间型") '''指定被操作的工作表《多个时间型》

a = .[A1].End(4).Row '''找到数据末行行号,赋值给a

arr = .Range("A2:D" & a) '''将数据区域赋值到数组arr中

ReDim brr(1 To 500, 1 To 4) '''重新定义brr为二维数组,数组大小为500行,4列

For i = 1 To UBound(arr) '''循环数组arr

s = Split(arr(i, 4), Chr(10)) '''拆分arr数组的第4列,打卡时间列,并赋值给一维数组s

For j = 0 To UBound(s) '''循环一维数组s

k = k + 1 '''计数器效果,得到每次操作brr数组的行号

brr(k, 1) = arr(i, 1) '''将数组arr的第1列的值,赋值给数组brr的第1列

brr(k, 2) = arr(i, 2) '''将数组arr的第2列的值,赋值给数组brr的第2列

brr(k, 3) = arr(i, 3) '''将数组arr的第3列的值,赋值给数组brr的第3列

brr(k, 4) = s(j) '''将一维数组s的值逐个赋值到数组brr的第4列

Next j '''结束循环变量j

Next i '''结束循环变量i

.[F2].Resize(UBound(brr), UBound(brr, 2)) = brr '''将数组brr的值赋值到单元格区域

End With '''结束with语句

Erase arr '''清空数组arr

Erase brr '''清空数组brr

Erase s '''清空数组s

End Sub


考勤表是日常EXCEL工作中比较典型的套表系统之一,对于这类数据的统计,我们可以讲的还有很多。例如【每天四次打卡】的处理,例如【婚丧病事】请假的工资核算,例如【个税抵扣】。如果这些都会了,同学们就可以给自己的公司制作一套DIY的考勤及工资核算模板,这样既可以提高工作的效率,还可以在领导心中树立一个好的形象。

如果你考勤统计的其他需要,请留言——不留言,笔者可猜不出你的需求。学习更多技巧,请收藏关注部落窝教育excel图文教程


****部落窝教育-excel不同时间格式的考勤统计****

原创:E图表述/部落窝教育(未经同意,请勿转载)

更多教程:部落窝教育


做Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

相关推荐:

做一张智能的人工考勤表:智能考勤表,MM你再也不用加班了!

一天打四次卡的考勤机数据整理:学会这2个公式,整理考勤数据只要一分钟

如何整理考勤机导出的数据:别怕:只要会简化拆分,几分钟搞定考勤统计分析表!

看懂VBA的循环语句:VBA实战入门教程(三):循环语句

(0)

相关推荐