利用Ecel快速处理汇总刷卡考勤数据(之一)

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

AGD1部门名称考勤号码姓名日期时间2财务部7100400€张三2Dl4-8-1iI7:13:383财势部ri10D4006胀三20^4-8-1'7:35:524财务韶'1004006张三2014-8-17:53:165财务部r11(XMOOe张三2014-8-110J6:34财务部F1100400C2D14-8-111:37:117财务部11DD400C张三2014-8-111:52:318财势剖1100400(5张三2014-8-111:53128g财务邹11oo^ooe张三2014-8-114:10:3210财务部110040062014-8-114:38:2211财务部11Q0400C张三2QH-8-114:47:2512财努部1100400C张三20屮J14:48:2513财势部11004006张三2014-8-117:2914514财务部11004006张三2014-8^46:58:0015财务部11DD400fi张三2014-8-47：31：：311611Q0400t张三2014-8』7:5313817财务部11(XMOOfi张三2D14-8-47:54:3118财务韶11DD4006张三2D14-8^411:33:0319财务部11OD4006张三2014-8-411:49:35on闰扌夕-±11JJ化八#fineTfldHOWd-if；n-97oHnJillSheet1Sheet2剩下的问题，就是要解决本文前面提出的问题了。这些问题的解决，有很多方法，其中一个最容易掌握、也最简单的方法，是利用简单的几个函数IF、COUNTIFS、INDEX函数进行处理。下面进行具体说明。在右侧插入一个辅助列，标题为“第几次刷卡”，在单元格F2输入公式二COUNTIFS($B$2:B2,B2,$D$2:D2,D2)，往下复制，就可以统计E列的某个刷卡时间是某人某天的第几次刷卡了。再插入两个辅助列，标题分别为“最早刷卡”和“最晚刷卡”。在单元格G2输入公式=IF(F2=1,E2,"")，往下复制，即可得到某个人在某天的最早刷卡时间。在单元格H2输入公式=IF(G2v>"",INDEX(E2:E875,COUNTIFS(B:B,B2,D:D,D2)),"")，往下复制，即可得到某个人在某天的最晚刷卡时间。这三个公式的思路、逻辑和原理，这里就不再细说了。这样，就得到了如下的处理结果。将F列至H列的公式，采用选择性粘贴的方法转换为数值，然后筛选出所有空值单元格，予以删除，并删除E列和F列，就得到下面的每个人的刷卡考勤数据，以此数据来做考勤计算:ABcDEFGH1部门名称考勤号码姓名日期最早刷卡最晚刷卡二2财务部11QQ4Q06张三2014-8-17:13:38|17:29:453财务部ri10D4006张三2014-8-46:58:00'14:04:534财务部rT10D4006张三2014-8-&7:013814:00:055财务部F11004006张三2014-8-67:21:0417:32:37财务部'11004006张三2014-8-77:19:1513:16:317财务部F11004006张三2014-8-87:18:4512:07:268财势部F11004006张三2014-8-H6:55:5517:31:429财务部11004006张三2014-8-127:21:4117.:38:4610财箸部F110D4006张三2014-8-1^6:53:0517:29:4411财务部110D4006张三2D14-8-147:17:2117:3210112财务部rT10D4006张三2014-8-156:47:2017:29:4813财务部11004006张三2014-8-187:06:0211:41:3514财务部ri10D4Q06张三2014-8-198:14:2217:37:2615财务部F11004006张三2014-8-2&7:15:0017:43:2516财势部F11004006张三2014-8-217:16:1417:35:4117财务却110D4006张三2014-8-Z27:11:3413:36:3318财务部'11004006张三2014-8-257325317:31:1619财务部'11004006张三2014-8-267:14:3117:35:5820财务■部rT10D4006张三2D14-8-276:584517:38:47的M才务哪riino^nna张二5?山4_良朋7:15:2517:51:03▼11对卜NSheet1上Sheet4Sheet2°^heet3z£IHL川在此表格的右侧进行迟到、早退、加班的计算，数字1表示迟到或早退，具体的时间表示加班时间，如下表：其中，各单元格的公式分别如下：单元格G2：=IF(E2>8/24,1,"")单元格H2：=IF(F2<17.5/24,1,"")单元格I2：=IF(F2>18/24,F2-18/24,"")

ABcDEF GHIJK1部门容称考勤弓码姓名日期最早刷卡最晚刷卡达到早退加班时问2财夯部"11004006抵三2014-8-17:13.3817:29:4513财务部F11004006张三2Q14-8^6:58:0014:04:5314财务部'110040()6张三2Q14-8-5II7D13814:00:0515财务剤‘11004006张三2&14-8^6'7:210417:32:376财务却^11004006张匚2014-0-77:19:1513:16:3117财务制F|1004006张旨2014-8-87:16:4512:07:2613财努部F11004006张二2014-8-116：55：5517：31：429财务邹P110040Dfi张三2014-8-127:21:4117:38:4610财务郢'11004006张二2014-8-136:53:0517:29:44142研瘵證r11007101陈五2014-8-117441217:534243研君部^11007101陈五2014-8-127:12:3118:15:420:15:42U4ri1Q07101匪五2014-8-1311:37:0517:56:411研騒F11007101陈五2014-8-147:42:2814:30:301牝研发部F11007W1陈五2014-8-157:41:3218:19:390:19:3947"11007101陈五2014-8-187：53：0318：29：440：29：4448研发部'11007101陈五2014-8-197:58:4617:51:0049研发部F11007101陈五^014-8-207:27:4218:10:250：1G：2550研岌郢F11007101偉五2014-8-217:030519:02:®102:2151硏发部1007101［东五2014-8-227:44:2318:26:160:26:16='：；T"mrn~lo'-jr\rlJr1”谊血已乜/縞时吐弧珀z■灼最后再对这个数据表进行透视汇总分析，就得到每个人这个月的迟到、早退、加班汇总数据，如下：部门名称-姓名3迟到次数早退揀数总加班时间办公室刘一5123:0^52壬二61214：0&：57财务郃张三190:00:00人力资源部684:4€:20研发部陈五116106157营销部陈士1211€:44:21总■计314344:50:27大家看到了吧，考勤数据的处理并不复杂，也不难，只要掌握了Excel的几个实用技能（分列工具、常用函数、透视表），就可以在几分钟把看起来繁琐无比的考勤数据进行高效快速处理和统计汇总。有关Excel数据处理和分析的问题，欢迎大家加入我创建的千人Excel交流学习群（群号228678049），随时跟大家交流实际工作中的问题，分享最新案例模版，了解最新学习动态，学习最新Excel技能。上面介绍的仅仅是一种考勤机数据格式，实际中，每家企业的考勤机都不一样，数据格式也缤纷异彩，这就需要根据具体的情况进行具体分析，找出最佳的解决方案。后面的总结文章中，我将再陆续介绍几个常见的考勤数据例子及其解决方案。韩小良，国内著名资深实战型Excel培训讲师和应用解决方案专家。Excel应用方面的著作第一人，出版了近40部关于Excel企业管理应用方面的实用书籍。从事Excel研究及应用多年，对E