利用Excel快速处理汇总刷卡考勤数据

利用Excel快速处理汇总刷卡考勤数据（之一）马上又要月底了，又要面临着把从刷卡机里导出的12月的考勤数据进行统计汇总。我们总不能还是花几天时间来手工处理这些考勤数据吧？前天晚上给HR们上课，正好介绍到考勤数据统计分析的一个例子，现在拿出来跟大家分享。实际数据如下图所示，从打卡机导出的数据是一个流水，要求:1、找出每个人最早打卡时间和最晚打卡时间;2、判断每个人每天的迟到、早退情况、加班情况。说明：出勤时间标准是8:00-17:30，18点以后为加班时间。这个表格数据的特点是：每个人的刷卡数据有好几个，分成了几行保存，而且刷卡日期和时间是保存在一个单元格，因此我们首先要把刷卡日期和时间分开，这个工作可使用〃分列”工具栏完成，处理结果如下：

ABGD1部门名称考勤号码姓名日期时间2财务部100400€张三2Dl4-8-1iI7/331383财务部ri1004006形三20^4-8-1'7:35:524财务部11004006张三2014-8-17:53:165财务部'100400€张三2014-8-11016: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-114:38:2211财务部11Q0400C张三2QH-8-114:47:2512财务部1100400C张三20g归14:48:2513财务部11004006张三2014-8-117:2914514财务部11004006张三2014-8^46:58:0015财务部110040朋张三2014-8-47：31：：3116财务部11。。4。贫张三2014-8^7:5313817财务部11004硕张三2D14-8-47:54:3118财务部11004006张三2D14-8^411:33:0319财务部11004006张三2014-8-411:49:35ond1 HAC二TfldAQAn-rn-07144卜卜ISheie11」Sh己己12 项剩下的问题，就是要解决本文前面提出的问题了。这些问题的解决，有很多方法，其中一个最容易掌握、也最简单的方法，是利用简单的几个函数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列，就得到下面的每个人的刷卡考勤数据，以此数据来做考勤计算：

ABcDEFGH1部门名称考勤号码姓名日期最早刷卡.最晚刷卡2财务部11004006张三2014-8-1大13:38]17:29:453财务部「11004006张三2014-8-46:58:00'14:04:534财务部rT10D4006张三2014857:013814: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:3210112财务部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-25F325317:31:1619财务部‘11004006张三2014-8-267:14:3117:35:5820财务部「11004006张三2014-8-276:584517:38:47财学-riino4nna丸14-日二朋7:15:2517:51:03M4*hSheet1£Sheet4Sheet2.IHL_川在此表格的右侧进行迟到、早退、加班的计算，数字1表示迟到或早退，具体的时间表示加班时间，如下表：其中，各单元格的公式分别如下：单元格G2：=IF(E2>8/24,1,"")单元格H2：=IF(F2<17.5/24,1广)单元格I2：=IF(F2>18/24,F2-18/24广)

最后再对这个数据表进行透视汇总分析，就得到每个人这个月的迟到、早退、加班汇总数据，如下:值部门名称 -姓名*迟到族数早退损数怠加班时间办公室-刘一12 3:03土212 14:03:57财务郃张三1 9 0:00.00人力资源部6 8 4：4€：20研发部陈五1 1 6:06:57营销部陈士12 1 16:44:21忌计31 43 44:50:27大家看到了吧，考勤数据的处理并不复杂，也不难，只要掌握了Excel的几个实用技能（分列工具、常用函数、透视表），就可以在几分钟把看起来繁琐无比的考勤数据进行高效快速处理和统计汇总。有关Excel数据处理和分析的问题，欢迎大家加入我创建的千人Excel交流学习群（群号228678049），随时跟大家交流实际工作中的问题，分享最新案例模版，了解最新学习动态，学习最新Excel技能。上面介绍的仅仅是一种考勤机数据格式，实际中，每家企业的考勤机都不一样，数据格式也缤纷异彩，这就需要根据具体的情况进行具体分析，找出最佳的解决方案。后面的总结文章中，我将再陆续介绍几个常见的考勤数据例子及其解决方案。韩小良，国内著名资深实战型Excel培训讲师和应用解决方案专家°Excel应用方面的著作第一人，出版了近40部关于Excel企业管理应用方面的实用书籍。从事Excel研究及应用多年，对Excel及VBA在企业管理中的应用有着较深的研究和独特的认识，对Excel及VBA在企业