《利用Excel快速处理汇总刷卡考勤数据》由会员分享，可在线阅读，更多相关《利用Excel快速处理汇总刷卡考勤数据（6页珍藏版）》请在金锄头文库上搜索。
1、利用Excel快速处理汇总刷卡考勤数据（之一）马上又要月底了，又要面临着把从刷卡机里导出的12月的考勤数据进行统计汇总。我们总不能还是花几 天时间来手工处理这些考勤数据吧？前天晚上给HR们上课，正好介绍到考勤数据统计分析的一个例子，现在 拿出来跟大家分享。实际数据如下图所示，从打卡机导出的数据是一个流水，要求:1、找出每个人最早打卡时间和最晚打卡时间;2、判断每个人每天的迟到、早退情况、加班情况。说明：出勤时间标准是8:00-17:30，18点以后为加班时间。这个表格数据的特点是：每个人的刷卡数据有好几个，分成了几行保存，而且刷卡日期和时间是保存在一 个单元格，因此我们首先要把刷卡日期和时间分
2、开，这个工作可使用分列”工具栏完成，处理结果如下：ABGD1部门名称考勤号码姓名日期时间2财务部100400张三2Dl4-8-1iI 7/331383财务部ri 1004006形三204-8-17:35:524财务部11004006张三2014-8-17:53:165财务部100400张三2014-8-110 16:34财务部F1100400C2D14-8-111:37:117财务部11DD400C张三2014-8-111:52:318财务部1100400(5张三2014-8-111:53128g财务部1100400C张三2014-8-114:10:3210财务部110040062014-8-
3、114:38:2211财务部11Q0400C张三2QH-8-114:47:2512财务部1100400C张三20g归14:48:2513财务部11004006张三2014-8-117:2914514财务部11004006张三2014-846:58:0015财务部110040朋张三2014-8-47：31：3116财务部11。4。贫张三2014-87:5313817财务部11004硕张三2D14-8-47:54:3118财务部11004006张三2D14-8411:33:0319财务部11004006张三2014-8-411:49:35ond 1HAC二Tfld A Q An-rn-0714 4
4、卜卜ISheie 11Sh己己 12项剩下的问题，就是要解决本文前面提出的问题了。这些问题的解决，有很多方法，其中一个最容易掌握、 也最简单的方法，是利用简单的几个函数 IF. COUNTIFS、INDEX函数进行处理。下面进行具体说明。在右侧插入一个辅助列，标题为“第几次刷卡”，在单元格F2输入公式=COUNTIFS($B$2:B2,B2,$D$2:D2,D2)，往下复制，就可以统计E列的某个刷卡时间是某人某天的第几次 刷卡了。再插入两个辅助列，标题分别为最早刷卡”和“最晚刷卡”。在单元格G2输入公式=IF(F2 = 1,E2,)，往下复制，即可得到某个人在某天的最早刷卡时间。在单元格 H2
5、 输入公式=IF(G2,INDEX(E2:E875,COUNTIFS(B:B,B2,D:D,D2)广)，往下复制，即可得 到某个人在某天的最晚刷卡时间。这三个公式的思路、逻辑和原理，这里就不再细说了。这样，就得到了如下的处理结果。将F列至H列的公式，采用选择性粘贴的方法转换为数值，然后筛选出所有空值单元格，予以删除，并 删除E列和F列，就得到下面的每个人的刷卡考勤数据，以此数据来做考勤计算：ABcDEFGH1部门名称考勤号码姓名日期最早刷卡.最晚刷卡2财务部11004006张三2014-8-1大 13:3817:29:453财务部11004006张三2014-8-46:58:0014:04:5
6、34财务部rT10D4006张三2014857:01 3814:00:055财务部F11004006张三2014-8-67:21:0417:32:37财务部11004Q06张三2014-8-77:19:1513:16:317财务部F11004006张三2014-8-87:18:4512:07:268财务部F11004006张三2014-8-116:53:5517:31:429财务部11004006张三2014-8-127:21:41扑 38:4610财务部11004006张三2014-8-侣6:53:051729:4411财务部110D4006张三2014-8-147：17：.2117:321
7、0112财务部rT10D4006张三2014-8-156:47:2017:29:4813财务部11004006张三2014-8-187:06:0211:41:3514财务部11004006张三2014-8-198:14:2217:37:2615财务部F11004006张三2014-8-2&7:15:0017:43:2516财务部F11004006张三2014-8-217:16:1417:35:4117财务部11004006张三2014-8-Z27:11:3413:36:3318财务部11004006张三2014-8-25F 32 5317:31:1619财务部11004006张三2014-8-
8、267:14:3117:35:5820财务部11004006张三2014-8-276:58 4517:38:47财学-riino4nna丸14-日二朋7:15:2517:51:03M 4* h Sheet 1 Sheet4Sheet 2.IHL_川在此表格的右侧进行迟到、早退、加班的计算，数字1表示迟到或早退，具体的时间表示加班时间，如下 表：其中，各单元格的公式分别如下：单元格 G2 ：=IF(E28/24,1,)单元格 H2 ：=IF(F218/24,F2-18/24广)最后再对这个数据表进行透视汇总分析，就得到每个人这个月的迟到、早退、加班汇总数据，如下:值部门名称-姓名*迟到族数早退损
9、数怠加班时间办公室-刘一5 123:03土 26 1214:03:57财务郃张三190:00.00人力资源部684：4：20研发部陈五116:06:57营销部陈士12116:44:21忌计314344:50:27大家看到了吧，考勤数据的处理并不复杂，也不难，只要掌握了 Excel的几个实用技能（分列工具、常用 函数、透视表），就可以在几分钟把看起来繁琐无比的考勤数据进行高效快速处理和统计汇总。有关Excel数 据处理和分析的问题，欢迎大家加入我创建的千人Excel交流学习群（群号228678049），随时跟大家交流实 际工作中的问题，分享最新案例模版，了解最新学习动态，学习最新Excel技能。
10、上面介绍的仅仅是一种考勤机数据格式，实际中，每家企业的考勤机都不一样，数据格式也缤纷异彩，这就 需要根据具体的情况进行具体分析，找出最佳的解决方案。后面的总结文章中，我将再陆续介绍几个常见的考 勤数据例子及其解决方案。韩小良，国内著名资深实战型Excel培训讲师和应用解决方案专家Excel应用方面的著作第一人， 出版了近40部关于Excel企业管理应用方面的实用书籍。从事Excel研究及应用多年，对Excel及VBA在企业 管理中的应用有着较深的研究和独特的认识，对Excel及VBA在企业管理中的高级应用培训有着丰富的实战经 验，已经为数千家大中型企业进行了 Excel培训和应用方案解决咨询服务，在业界享有很高的评价，深得客户 认可。主要著作：构建高效数据分析模版一一Excel函数和动态图表高级应用，Excel高效财务管理和分 析模版应用大全，Excel会计与财务高效应用从入门到精通等等。
