Ecel技巧-快速统计公司员工考勤记录

1、对于公司的人力资源或行政人员来说，员工的每月考勤统计一直是很头痛的一个问题。从各类考勤机、网上考勤与移动考勤系统导出电子考勤数据并借助Excel进行处理是最基本的统计方法。如果对Excel了解不够深入的话，也许十人以下规模的公司考勤还能靠眼睛人工应付，几百人的就变得不太现实。本篇技巧就将向大家介绍如何借助Excel的自带高级功能快速统计考勤数据、减少人工工作量提高工作效率。让我们先来看下借助Excel可以实现的考勤统计最终效果对比：初始考勤导出表：9L CQlOt9" h:1,白三94如1心/二 此.派沁1屋 弱I &LAS 的Was10 W16iIO； i>316-

2、：L. W631 - V ib.W2-in-19 ?朝齐1卜的201E-10-D9wma 加2-1 A】口 2015rl0-ll 2O12-1&-11 己町l萨im 2612-10-12 261E-irH15：丁 ： iV 1 051boi BE 1 OB *1X163 10 1XIL63 1正次：r.=- h= 1。 11 P0016E11 U-LMi200 2-10-16 201Z-10-1T-2013-10-n? 2OI2-1D-1.F- 2012-10-19 加1并1叱.上13： . 'J；CKlT；：32；W iniSM T:：32：i：O ITiLTiOOT133C

3、M1T：siXiT：CjOJB:L4:CUTMEIT；羽；0Q ?:：35eiXi .u最终完成处理的考勤统计表:*£iEL101钟明甘上午下午J若iF1一程早飞TrrF：中工作E工作叼长5ttfi723:009n11li0fl.2012-1-Oiimasoo10101昉他Taia-ioroiITslFs&O1e,luH时*Ji轩件g团12-1CT7:22=04iTzl9:u301cQi09.西iz-m打33OH1B:14no1o0i0in-l时的十忡LQB12-10-1Z1c01。10-H由调113312-1(15?；叼二岫IT口目加IcDi(n9小时髀分部12如2-161

4、:-:G5-i：'17:16:001c0i091口钟15B91M0-1T打3XMLTmsOO10010*-卜|八1力冲14R12-SBI7:23;»017:190Q01IuI讨E箱。152012-lCly11Qi0-ad可片上W16V：tS:OaIT；12；00是否有考勤数据一目了然豁然的感觉？借助Excel达到最终统计效果仅仅只需要几个步骤几分钟而已！还等什么？赶快来看下面的教程一起学习吧！步骤1:导出原始考勤电子表格首先，我们通过考勤机所连接服务器将考勤数据导入到Excel中，通常我们都会导出以下几项考勤数据内容：员工工号，员工姓名，刷卡日期，刷卡时间。有时每天每个员工的

5、刷卡时间与次数都是不同的，但不用怕，这些不同的数据会在后续的处理中被过滤掉，不会为最终的统计造成影响。步骤2:添加上下午标签现在我们需要为打卡时间设置上下午标签，以便为后续统计提供帮助。在数据表格中插入“上下午”歹U,并撰写函数:IF（D2<-"12:00:00","上午","下午"）然后拖拽整列即可。ArPC.D.：<="12100JA1人员期号2口咽a%帅婶4bjooeUMq晒7h叫隔gT jOMgOOOlDSujouta116qq值12阿啕1314把MHIBEoosu17toore16bOMflISboob

6、力叫咽01009 i仙的?iOOOT i叫明口比* I：工口哂 OODW “口必很3名乱发土工犷*土制7 £*凯凯工ir.Har.HJ,爵/蕤融特稿糖凝疑胃场森段 的-3蕈方哥李=*方W:左胃¥svlsflttw许许有许许音在讦许许许许讦L：EI+H1T12011A的201E-10-OT20(1.>112P1E-1D-102012-1D-H201>jD-il2012-1U-122012-1D-122(112-10-152012-1D-L520110-1CZO12-10-16W1A1QT72O11D-1S血n201M0=1?2012-10-02aCQE-10-02

7、小10一心2O12-D-ra双十】.(K42012-1D-H剑2-1n-f4wom4201MD=(«201HII曲201E-lD-073-仃I'：同市时叵7It127：MZI.SW27:WITll；«7久必n713;W3Q;M11lZ：iKi1111B:00TITT3B：O0LE:“Q9MIT00T1711T ir41M744:007沾;92 tM7LjlW，l肌M1眈MgITg烟七犯帆IT5：-MIS7riMi7 ieMML1：M7J I -午午午午午午午午年午午午早午午午午午午午午午午中午隼午耳午午午午 上一上一一、上F上TTr-上,上F上一 _上上上-上上Fr

8、r一卜一上步骤3:考勤表格数据分类汇总接下来我们就要做最关的一步，对原始考勤数据表格进行分类汇总。此时我们要用到的Excel功能就是“数据透视表”。依次选择菜单：“插入”>“数据透视表”>“数据透视表”，在弹出的选择窗口中，点选“选择一个表或区域”选项，并如示例图中所示，拖拽选择以下数据列：“姓名”“刷卡日期”“刷卡时间”“上下午”。口亚君*奥笃典F匕工兰AcrobatE-r1AC步号理工瓶起取沮工修同一工一1«r000M:诏妻2C1M0-0S7:27:00二平 !kwoos:语H2C12 10-0517:21100*kow:访利2C12-1Q-O57:27:00 -午

9、：boo®:宜手.3C13-10-051T：K：OO 下午：.占箕。朗:金丁，2012-10-10?!34:00 上午MOM:诺丸2012-10-1511:13:00 不另IFarM0M;仃丸2C1J-10-117:3100 1T：:koos：®5f2C12-10-li17:12:00 下平 ：10的他8:酒却3C12-10-1；T斗第依上午1:12讪口同 pmom:色术.2C12-10-1517:1?:00 F4:山丸2012-10-157:36:00：13rM0M酒丸2C1S-10-1517:12:00：14rooow:语2C12-10-167:38:00171LI-0

10、00M boOM:语,3C1M0-1&1T:13:OQ Tr遹知.201M0-1TT：35：aJ 上午小。脚，的MHS-iO-1717:19:00 T2F ；1£ !：kM0M:母丸2C12-1O-1&T:el:加上不rooow:语,SC12 10-1917:12100加rWQM:语,EClMO-l?T:3EQ 上午 ：221莫:"豪3013-10-2T;39;Q0 上午：讪。同:许安2H 12-125:30:03 上午2：;计典2C12-10-031:50:00 上工rM0W抑就2C1S-10 0319:09:00 lT ：至浒麻2C12-1O-O17:5

11、5:00 上车 ：A据当香号上iTMJd爆在身京住E期厘汪>0；FO01上班/下事MC 上底T避PEC 上强一下避F* 上班/下班做 4. - JiF：'7帝踊隶第愀惭*徒祥一下卡我泞堆日去；写g2CL2 10 1： 19:53:2( 2012 10 12 1E 5：:2( 2012-10-L： 15:53;2c 2CH2-10-12 15:53:20 2012-10-12 1E:53j2CrileZiaeD1&33Cnift 耙|!白IT巨001.世；IFF式2012-10-12. 15:53:30飞虱上旺下JfilKC2012-10-1： 15;5：;20其上酊下魂F

12、IC2012-1C 12 15:5：:20石5 .J灯一下班窗2012-IQ-12 10;?3;30恚争百： 当岸中三也向也阳情缶 U flip'll 我有工卞苒R 行三。，点击“确定”按钮，在新工作数据表中，右边侧栏会出现“数据透视表字段列表”栏，根据示例中所示，将对应字段拖拽至对应区域内。然后我们需要对“刷卡时间”数值进行设置，我们将“刷卡时间”设置为“最小值”表格会自动根据“刷卡日期”过滤多余非最小“刷卡时间”的数据，多次重复的打卡时间会被自动过滤。同时将数字格式设置为“时间”格式。A123H门酒而E4ws-5.阵俵62012-10-0812012-10-0982012-10-1

13、092012-1CHI1102012-1CT12112012-1Al5122012-10-16_13.2M2-1O-1T142012-10-18152012-10-1916fft?172012-10-0813.2012-10-09192012-1010202012-10-15212012-10-1622.2012-1CF1T,2012-10-18crrraa429a计数吊；刷长时间BD1，也名veFs.i7勖冏同7上下午Efiift计热JM在以不三忖丽芹依胃iF布F5R钎11Fxlel21033L6330?Sheetl9t7:32:007:32:007:33:007:33:007:37:007

14、:35:007:36:007:35:007:23:007:33:007:45:007:47:007:52:007:4S;007:51:007:46:0(|7:45:007:45:007；3；007:29:00ABDEFG1最小情项士刷卡对同列标拉1行港整二IJ午下午总il5队传7:23:0017:15:00T：Z3：0062012-1D-08T:33:Q017;L5:007i32：0：72012-10-097:32:00ILL:度i7:32:0a2012-1WD7:33:0017:19:007:33:0.32012-10-117:33:0018:14:007:33:0j102012-10127

15、:37:0017:37:007:37:00I1L2012-10-15T:3tl:QC17;LS:007:33:0:122012-10-16T:36:001715:007:35:0jIE2012-1W77:3S:QD17:19:007:35:0;14201>10-187:23:0017:19:007:33:0D152012-10-197:33:007:33:00116-程宜7:45:0017:10:007:45:00172012-10-087：47：no1712:007:47:0'IE2012-1CHQ?7:B2jOO17:12:007i52:0j19201>10-10TiU

16、:0017:12:007:48:40202012-10'157:51:0。l?：l!007i51:0D212012-10-167:laJ0017:11:007:<6:0:222012-10-177:45：noi?tn：(w7:45:0'j2012-1CHL37:18:0017:11:007i<a：Q?步骤4:在刚插入的表格旁边再次插入数据透视表插入位置如图中所示，唯一不同之处就在于“刷新时间”数值选择最大值。下班时间以最后一次打卡时间为准，其余时间均被被过滤。间列标签/己1上午下午（空白）总计7:23:0017:15:007:23:0017:11:007:46:00

17、17:10:007:45:0017:11:007:43:007:48:0017:15:007:29:00器软称：刷卡对司口定提名力仁MlFDrfll西方武|市辰示FE |恒亨展忆总方式嚼选库.用于宜管班老宇我救施的汁尊赳独字精乩11步骤5:隐藏数据透视表多余字段现在的表格已经有我们所需要的全部统计数据了，此时我们还隐藏多余字段列表。UEs»_2J3耒工-11-A*AJ'/*，下TB，三二，-虢出史AB-sFa-7tl下午t空口;篇计£J午wici7321T00ITilfiiOC7s23t00降偌TjSTsL2-LO-QB7:320ITilSiQOFi32iQQ3QL

18、2-1Q-Q07：32Tmg7:32!i00?：sam3012-10-09v：sa祐皿.12-l?>io7:53:Cial7;19:0D7:23idO3012-10-1C7:33L2-m7;33;U?18:14；aD7=33sOQ3012-10-11Y;S312TAi27:37:0017-37sM7:STiOtl212-10-157:S7Aa(L12-10157:35:001007:35:00M-E7:2-5»Ci13-10167:3fi：017:18:006:002013-10-167：3&1210171T319EOO7r:褥:oaXI2-10177：35丰工必风12

19、-icie?：E3：QCMhsOO7:2300ZQL3-1Q-137；Z312HAi9?：33：Oli7:33iOO2012-101?7:33Jirtr-fi7:45:0017:10:00T:45iOO理建T;52!L3-IO06H;12sMmoo3012-10-08ni12-1001210-107；4-i1T:12:OOITslZsOU7B2iOO7-13；Qu2012-A3黑mo-iQ7sB27-4R41i11夕llb7：51：0i'17:1：；O07:511002012-10-1817:11;C&L2TA67:4：E:Qi：'1T;11：QD7:46:002013

20、-10-16T:4&:0017:11:01)12-1十IP?;45i;0l'17;10；007:45002012-10-17f二45汨017;10;00L2-IO1S7:4E0ITilUOtt7-48：!OOani2-ii>ia7:40:0012-1015TsUiOD-4肌era山uohv：25iM1/：16t0Oy：2*31ao黄志史Y；4书；0017:21:00隐藏后的表格就是我们下图中所见,二JT-«|T1二二jZ上1FF年qnaT:必g1址“：O068】2-1AB7S32：QOLT：1B：»r2ai210-t&n:32iUi.LTsiT

21、stnHsai2=i(rio!OuLT:19:CN119aimLBiHiCNIP海一：r二T：371DOLT-3T：C»11的1新1510IT;fl：q412如31OWfWS;昧4.32A12-10-177:35:Ui.LT:19:dQ14Mil1：z中心:Mt?；iu；Qa1&国】2-10-19及旧旧16丘£T：QQQJT：IZ：CWITTHfjfiOLI1-JI-CM1ST201>10-0&?sE2iOOLT：】打CM3312-10-17:tS:(lLLT二12二加加酬方16蜴T：S10L7sii：et21KIMO-16打问口黜H1HTA1-7：M

22、；QQLT:J(i：qo器团1Am装LTzllzdC制】2-1T19r<a：H25立寺郭£T:21;M2&3n宗Igoe,：睁腺IT型&IT*r汾】L】A停LT：Z:«2B3ai2-10-lGemac2&纪土n。211I5!O4步骤6:上下班打卡统计接下来我们要对上班时间和下班时间来进行处理。假定公司规定早上上班时间不能晚于7点30分，下午下班时间不能早与17点。那么我们新建两列，分别为：上午迟到情况和下午早退情况，分别编写两个统计函数：=IF(B6<-"07:30:01",0,1)和=IF(B6<-"

23、07:30:01",0,1)然后拖拽对应姓名所属范围列即可。为何迟到是1,不知道是0?这是为了后续总和统计方便所设数值。就如示例图中所示，使用sum函数累加后即可得出当月迟到或早退总数。于AKG*5/j=17CE/一"加：加Ml知。ABL一R考势拄计-JITFT上也正到下到月退5修拈7:28:01314:00016ZEaa-LCHSB7:32rQQLTil&iQV110T2032-1009T;32：001T:17:0ij0BT:33rOQLT：19:0010201.2-10117;弱;QQ13;l4;0fl1102ftm27=27=0017:27:0£i1

24、1b112Oli-1>157:38:00ITslBsOO11a122032-1O1ST:36:00LT:16:tX111132012-10-177:35:00ir：i?：oc10L420(12-10-13T;331T；1?；Wq口152012-10-197:33；00ii由程馔L7:12:Ca7F=L7!MZ-10-097:1?：00LF：1£：M133M2-1CHD0Ts52?00ITHJfOOIS2012-1trio7:40:0017:12:0020E012-10-13T：51：0QITtlUOd212012-1CF16THEEQLT：11:M292012-10-171T;10;0(i二E2CU3-1卜旧7:4=9-00ITiiljtn242Q12-1(M9T:4B:002j黄土比lT:Kl：ca263U3-10-0B?：25：oa11:19100步骤7:缺勤统计如果上下班都未能打卡，记为缺勤。因为我们已经有上下班迟到的标识数值，那么利用这个数值即可很方便的统计当天是否有缺勤及当月缺勤总数。缺勤函数：=IF(K7=0,IF(L7=0,1,0),0)IT氏tC7=C.I?a7-a,i,H,口，AH.4雪朝先计-上午卜午上置迟胤下或早也苛*K,kJ-*ST:2S:0»ta：i+iOOg1162012-10-CS7*32200i00知