数据标准化处理方法_Excel教程：考勤数据标准化处理

助力春季就业，越努力越幸运

特推超级会员限时疯狂抢购

点击了解

支持微信公众号+小程序+APP+PC网站多平台学习

经常做考勤的同学有没有遇到过这样的考勤数据呢？

没错，从考勤机里导出来的，要统计那叫一个麻烦啊(有同学说可以导出txt文件，然后再处理会省事很多，有条件的小伙伴可以自己研究研究)

看这表格数据其实挺规范的，每个人占2行，第一行是个人信息(个人信息的C列是工号，K列是姓名(后面还有部门这里用不上就不截图出来了))，第二号是打卡记录，打卡记录每天占用一列，时间统一5位数

知道规则以后，我们就来写个vba 代码秒杀它(对于小白来说今天的知识量有点大，请选个好的椅子来坐，小心摔倒)

代码有点长，就不放代码图了，直接贴出来吧，

这是主过程，代码量其实并不多，复制粘贴就好

Option Explicit Sub 处理考勤数据() Dim kqStr   As String, yfStr    As String Dim ghStr   As String, xmStr    As String Dim xRow    As Integer, iRow    As Integer Dim xCol    As Integer, xLen    As Integer Dim oArr(1 To 10000, 1 To 6)    With Worksheets(1)  '考勤数据表        yfStr = Split(.Range("C3").Value, "-", , vbTextCompare)(1)        For xRow = 6 To .UsedRange.Rows.Count Step 2            ghStr = .Range("C" & xRow - 1).Value    '工号赋值给变量            xmStr = .Range("K" & xRow - 1).Value    '姓名赋值给变量            For xCol = 1 To 31                kqStr = .Cells(xRow, xCol).Value    '考勤记录赋值给变量                If Len(kqStr) > 3 Then                    For xLen = 1 To Len(kqStr) Step 5                        iRow = iRow + 1                        oArr(iRow, 1) = yfStr   '考勤月分                        oArr(iRow, 2) = xCol    '考勤日期                        oArr(iRow, 3) = ghStr   '工号                        oArr(iRow, 4) = xmStr   '姓名                        oArr(iRow, 5) = CDate(Mid(kqStr, xLen, 5)) '分离时间                        oArr(iRow, 6) = 状态(oArr(iRow, 5))     '判断结果                    Next                End If            Next        Next    End With    With Worksheets("处理结果")        .Range("A:D").ClearContents        .Range("A1").Resize(, 6).Value = Array("考勤月分", "日期", "工号", "姓名", "打卡时间", "状态")        .Range("A2").Resize(iRow, 6).Value = oArr    End With    MsgBox "考勤数据标准化处理完毕，共计处理出 " & iRow & " 条记录！", , "处理完成" End Sub

这里还有个自定义函数，取出时间后，简单判断一下刷卡结果，因打卡记录不规范，此结果仅供参考分析之用。(该函数也可以用到单元格中)

Function 状态(ByVal dkTime As Date) '判断刷卡结果    Select Case dkTime        Case Is <= #8:00:00 AM#            状态 = "正常"        Case Is < #12:00:00 PM#            状态 = "异常"        Case Is <= #2:00:00 PM#            状态 = "正常"        Case Is < #6:00:00 PM#            状态 = "异常"        Case Else            状态 = "正常"    End Select End Function

自定义函数中的异常其实就是迟到或早退，为了给新人降低难度，统一做异常处理了。

因为这个非常不好判断(有打上班卡，提前打下班卡叫早退，也有可能是忘了有没有打上班卡，补打一次(宁愿迟到也不愿意旷工半天)，下班正常打卡，(中余外出打2次卡)等等，不同公司制度不同，情况太复杂了)

代码写好以后，要方便操作，还是插入一个形状，然后右键它，指定宏，选择 处理考勤数据 这个宏，指定好以后直接点按钮就可以啦，来看一下效果图吧！图02

处理成这样的数据以后，要用函数，或者透视表来分析都很方便了！

接下来就用透视表来分析一下吧，为了方便后期刷新(每个月都要做，总不能每个月都手工插入透视表吧？)，把这结果表做成超级表，然后再来创建透视表，以后超级表中数据有增减，在透视表中只需要刷新就可以更新结果了

鼠标选中数据区域中任意单元格，点开始，套用表格样式，点选一个你喜欢的样式，弹出的窗口中一定要勾上 数据源包含标题

来看一下步骤图吧，图03

表格样式套用好了，我们再来修改它的名字，同样的选中超级表中任意单元格，点 功能区最右边的 表设计 把名称修为 考勤数据 ，来看一下设置步骤图04

为什么要修改名字呢？因为后面要让它自动更新，统一名称好操作一点。设置好以后，就插入透视表，同样的选中超级表中任意单元格，点 插入，透视表，来看步骤图05

不需要将数据添加到数据模型，所以这里不勾它(版本不同，可能没有此选项，请忽略)

(加入数据模型后，透视表会有一些隐藏功能，我们用不上，所以不用勾选)

然后在透视表里拖拖拖，做简单的汇总分析，看图片06

把状态 拉到列，再拉一个状态到值(计数),把姓名和日期拉到行

左边的透视表中可以看到汇总情况，大致看一下，刷卡很不规范有刷超过4次和不足4次的，这样的数据要统计还是很麻烦的。

这就是为什么会有人说考勤是史上最难处理的原因了，(如何准确处理考勤结果就不在讨论范围内了，因为各公司制度不同，处理起来差异很大，数据标准化以后可以自己想办法处理了！)

到这一步，次月考勤数据更新以后，还无法实现刷新效果，因为我还没有写对超级表中的数据做更改的代码。

接下来把之前的过程复制一份出来，对名称做一个修改，再添加一些代码进去，就可以实现了

Sub 处理考勤数据超级表() Dim kqStr   As String, yfStr    As String Dim ghStr   As String, xmStr    As String Dim xRow    As Integer, iRow    As Integer Dim xCol    As Integer, xLen    As Integer Dim oArr(1 To 10000, 1 To 6) Dim lst As ListObject    With Worksheets("考勤记录")        yfStr = Split(.Range("C3").Value, "-", , vbTextCompare)(1)        For xRow = 6 To .UsedRange.Rows.Count Step 2            ghStr = .Range("C" & xRow - 1).Value    '工号赋值给变量            xmStr = .Range("K" & xRow - 1).Value    '姓名赋值给变量            For xCol = 1 To 31                kqStr = .Cells(xRow, xCol).Value    '考勤记录赋值给变量                If Len(kqStr) > 3 Then                    For xLen = 1 To Len(kqStr) Step 5                        iRow = iRow + 1                        oArr(iRow, 1) = yfStr   '考勤月分                        oArr(iRow, 2) = xCol    '考勤日期                        oArr(iRow, 3) = ghStr   '工号                        oArr(iRow, 4) = xmStr   '姓名                        oArr(iRow, 5) = CDate(Mid(kqStr, xLen, 5)) '分离时间                        oArr(iRow, 6) = 状态(oArr(iRow, 5))     '判断结果                    Next                End If            Next        Next    End With    With Worksheets("处理结果")       For Each lst In .ListObjects            If lst.Name = "考勤数据" Then                lst.Range.Delete                Exit For            End If        Next        .Range("A1").Resize(, 6).Value = Array("考勤月分", "日期", "工号", "姓名", "打卡时间", "状态")        .Range("A2").Resize(iRow, 6).Value = oArr        .ListObjects.Add(xlSrcRange, .Range("$A$1").CurrentRegion, , xlYes).Name = "考勤数据"    End With    ActiveWorkbook.RefreshAll    MsgBox "考勤数据标准化处理完毕，共计处理出 " & iRow & " 条记录！", , "处理完成" End Sub

红色部分代码是新添加的，同时里面删除了一行代码！复制出来对比一下就能看到区别了！

为了方便看处理出来的区别，先把透视表 行 中的日期拉出去，看一下结果

可以看到 姓名最后有空白，那是因为考勤数据源里有的姓名被删除掉了(为了保密)，接下来要做的是去数据源里，把没姓名的记录都删除掉，然后再使用新的宏，重新分析。

可以多删除一些，方便看效果，在透视表里插入一个形状，指定 处理考勤数据超级表 这个宏 忘了怎么指定了？翻一下上面的记录，有过！

都设置好以后，就是点按钮啦！！看吧，很香的哟！！图08

点一下，结果就出来了，用VBA是不是很香啊，可能你每个月要花几小时来做的事情，用它只需要点一下，不到1秒就处理完了。数据标准化处理以后，再做其它分析就简单多啦！！

PS：因软件不同、软件版本不同等原因，某些功能会有差异，可多在群里问哦！

我用的软件是office365 版本2004(内部版本12725.20006)

有兴趣的同学可以把这个做成加载宏，以后只需要打开导出来的考勤数据，在功能区点一下就给你分析好了！

↗点击优秀讲师   原创投稿 点击↖

推荐学习★★★★★

Excel教程：吐血整理，70个精选实用Excel技巧(↶点击学习)

Excel教程:100篇精华原创教程汇集！收藏慢慢学(↶点击学习)

推荐Office学习关注

(PPT WORD EXCEL)

今天的分享就到这，如果教程对大家有用，希望大家多多分享点赞支持小编哦！你的每一次点赞和转发都是支持小篇坚持原创的动力。

Excel学习交流群Q群：562059974 欢迎加入

(群共享，配套练习课件，提供答疑)