【刷卡考勤】利用Excel快速处理刷卡考勤数据.docx

利用Excel快速处理汇总刷卡考勤数据
马上又要月底了，又要面临着把从刷卡机里导出的12月的考勤数据进行统计汇总。我们总不能还是花几 天时间来手工处理这些考勤数据吧？前天晚上给HR们上课，正好介绍到考勤数据统计分析的一个例子，现在 拿出来跟大家分享。
实际数据如下列图所示，从打卡机导出的数据是一个流水，要求：
、1找出每个人最早打卡时间和最晚打卡时间；
、2判断每个人每天的迟到、早退情况、加班情况。
说明：出勤时间标准是8:-17:30，18点以后为加班时间。

A

C
D
E
1
部门名称
考勤号砖

.时间

2
财务部
"1146
Bl~1 张三
2014-08-01 07:13:38

3
财务部
勺1D046
2014-08-01 07:35:52

4
财务部
彳14€
张三
2014-08-01 07:53:16

5
财务部
彳146
张三
2014-08-01 10:16:34

6
财务部
彳146
张三
2014-08-01 11:37:11

7
财务部
勺146
张三
2014-08-01 11:52:31

8
财务部
彳14€
张三
2014-08-01 11:53:28

9
财务部
\ 14€
张三
2014-08-011-0:32

10
财务部
彳1D046
张三
2014-08-01 1438:22

11
财务部
F
1146
张三
2014-08-01 14:47:25

12
财务部
彳 WtMOOfi
张三
2014--01 14:48:25

13
财务部
\ 14C
张三
2014-08-01 17:29:45

14
财务部
% 14C
张三
2014-38-04 06:58:

15
财务部
114€
张三
2014-08-04 07:31:31

16
财务部
% 14€
张三
2014-[)8-04 07:53:38

17
财务部
‘1146
张三
2D14-Q8-04 07:54:31

18
财务部
1146
张三
2014-08-04 11:33:03

19
财务部
彳146
张三
2014-08-04 11:49:35

咨F1+度血
1 4 nnyinm?
膏二
/I ng n』4 。•匚门-三旦

TF

这个表格数据的特点是：每个人的刷卡数据有好几个，分成了几行保存，而且刷卡日期和时间是保存在一 个单元格，因此我们首先要把刷卡日期和时间分开，这个工作可使用“分列 工具栏完成，处理结果如下：

A

G
D

1
部门名祢
考勤号码
姓名
日期
时间

2
财务部
214C
张三
2014-8-lj
I 7/33138

3
财务部
ri 146
张三
20^4-8-1
'7:35:52

4
财务部
1146

201^-8-1
7:53:16

5
财务部
'1oo^ooe
张三
2014-8-1
10 16:34

财务部
F114。牌
虹
2D14-8-1
11:37:11

7
财务部
F110tMOOC
张三
20$ 4-8-1
11:52:31

8
财务部
F1146
张三
2014-8-1
1153128

g
财务部
ri 146
张三
2D14-8-1
14:10:32

W
财务部
r! 146
y
2014-8-1
14:38:22

11
财务部
ri 146
张三
2琳 4-8-1
14:47:25

12
财务部
ri 14C
张三
2014-8-1
14:48:25

13
财务部
ri 146
张三
2014-8-1
17:29145

14
财务部
?146
张三
2014-8^
6:58:

15
财务部
‘110EM0D6
张三
2014-8H
7:31:31

16
财务部
146

2Q14-84
7:53138

17
财务部
ri 14fi
张三
2014-8-4
7:54:31

18
财务部
r! 146
张三
2014-8^
11:33:03

19
财务部
1146
张三
2014-8-4
11:49135

on
(il+/
jj a a j i\r\c

1 q a门门了
—
H
Uli

e - Sh已己tl」Sh己己
剩下的问题，就是要解决本文前面提出的问题了。这些问题的解决，有很多方法，其中一个最容易掌握、 也最简单的方法，是利用简单的几个函数IF、COUNTIFS 、INDEX函数进行处理。下面进行具体说明。
在右侧插入一个辅助列，标题为“第几次刷卡，在单元格F2输入公
式=COUNTIFS($B$2:B2,B2,$D$2:D2,D2)，往下复制，就可以统计E列的某个刷卡时间是某人某天的第几次 刷卡了。
再插入两个辅助列，标题分别为“最早刷卡和“最晚刷卡。
在单元格G2输入公式=IF(F2=1,E2,"")，往下复制，即可得到某个人在某天的最早刷卡时间。
在单元格 H2 输入公式=IF(G2<>"",INDEX(E2:E875,COUNTIFS(B:B,B2,D:D,D2)),"")，往下复制，即可得 到某个人在某天的最晚刷卡时间。
这三个公式的思路、逻辑和原理，这里就不再细说了。
这样，就得到了如下的处理结果。

将F列至H列的公式，采用选择性粘贴的方法转换为数值，然后筛选出所有空值单元格，予以删除，并 删除E列和F列，就得到下面的每个人的刷卡考勤数据，以此数据来做考勤计算：
1部门名称 考勤号码 n财务部 3财务部 4财务部 5财务部 6财务部 7财务部 8财务部
J146
110D46
1146
1146
1146
姓名
张三
张三
张三
张三
张三
1146 张三
110D4D06
10财务部
11财务部
1146
1146
1146
12
13
14
15
财务部
财务部
财务部
财务部
1146
1146
D
日期
2014-8-1
2014-8-4
2014-S-5
2014-8-6
2014-8-7
2014-8-8
最早刷卡最晚刷卡
7 13：38| 1729:45
16财务部 节］财务部 彳可财务部 19财务部 西财务部
110D4D06
1146
1146
1146
1146
1146
1146
张三
张三
张三
张三
张三
张三
张三
张三
张三
张三
张三 张三 张三
2014-8-H
2014-8-12
2014-8-13
2014-8-14
20U-8-15
2014-8-18 2014-8-m 2014-8-20 2014-8-21
2014-8-22
6:58: 7:01:38 7:21:04 7:19:15 7:18:45 6:55:55
7:21:41 6:53:05 7:17.21 647:20 7:06:02 8:14:22
7:15: 7:16:14 7:11:34
14:04:53
14::05
17:32:37
13:16:31
12:07:26
疣
吧岛己己坦官吊h己咨矿I血*己己屹"•七
2014-8-25
2014-8-26
2014-8-27
17:31:42
17:38:46
17:29:44
1732101
17:29:48
11:41:35
17:37:26
17:43:25
17:35:41
13:36:33
17:31:16
1735:58
17:38:47
47:51:03
Jill

在此表格的右侧进行迟到、早退、加班的计算，数字1表示迟到或早退，具体的时间表示加班时间，如下 表：
其中，各单元格的公式分别如下：
单元格 G2 : =IF(E2>8/24,1,"")
单元格 H2 : =IF(F2<17.5/24,1,"")
单元格 I2： =IF(F2>18/24,F2-18/24,"")

最后再对这个数据表进行透视汇总分析，就得到每个人这个月的迟到、早退、加班汇总数据，如下:

部门名称W
姓名V
退到次数
早退沃数
怠加班肘间
办公室
-刘一
5
12
3:03土2

王二
6
12
14:08:57
财务部
张三
1
9
0::
人力资源部
•季四
6
8
4:46:20
研发部
陈五
1
1
6:06:57
营销部
陈七
12
1
1&442
忌计
31
43
44:50:27

大家看到了吧，考勤数据的处理并不复杂，也不难，只要掌握了 Excel的几个实用技能〔分列工具、常用 函数、透视表〕，就可以在几分钟把看起来繁琐无比的考勤数据进行高效快速处理和统计汇总。有关Excel数 据处理和分析的问题，欢送大家参加我创立的千人Excel交流学习群〔群号228678049〕，随时跟大家交流实 际工作中的问题，分享最新案例模版，了解最新学习动态，学习最新Excel技能。
上面介绍的仅仅是一种考勤机数据格式，实际中，每家企业的考勤机都不一样，数据格式也缤纷异彩，这就 需要根据具体的情况进行具体分析，找出最正确的解决方案。后面的总结文章中，我将再陆续介绍几个常见的 考勤数据例子及其解决方案。
【作者简介】
韩小良，国内著名资深实战型Excel培训讲师和应用解决方案专家。Excel应用方面的著作第一人， 出版了近40部关于Excel企业管理应用方面的实用书籍。从事Excel研究及应用多年，对Excel及VBA在企业 管理中的应用有着较深的研究和独特的认识，对Excel及VBA在企业管理中的高级应用培训有着丰富的实战经 验，已经为数千家大中型企业进行了 Excel培训和应用方案解决咨询效劳，在业界享有很高的评价，深得客户 认可。主要著作： ＜构建高效数据分析模版一一Excel函数和动态图表高级应用〉，＜Excel高效财务管理和分析 模版应用大全〉，＜Excel会计与财务高效应用从入门到精通〉等等。