在弹出的“宏”对话框中选择“遍历工作表”—期货入门与技巧奈何速捷将考勤打卡机导出的打卡记实外摒挡汇总成考勤统计外，让每位员工的出勤天数、迟到、早退、加班情状一目清晰？你须要计划建造一个考勤汇总模板文档——有了它就能够一键天生须要的汇总统计外。本篇先容奈何绘造汇总外、奈何摒挡打卡数据，为主动统计打下根柢。

　　好比每个月底最让小青头疼的便是公司的考勤外汇总。她是一家小公司的HR，每个月都要建造考勤汇总外。

　　公司利用的是指纹考勤机，月底固然能够从呆板里导出员工打卡记实文献（xlsx方式，每天一张职业外），然而呆板导出的数据惟有员工编号和对应的打卡韶华。

　　小青须要将逐日打卡记实职业外中的原始数据，摒挡并汇总天生如下考勤汇总外，直接反应出员工的迟到早退、加班金额：

　　2）依照打卡韶华并连合公司考勤端正鉴定考勤状况：平常、迟到/早退、加班；将打卡韶华与考勤状况组合成一个归纳考勤值，如“08:56:39（平常）”；

　　3）鉴定打卡韶华的归属时段：9点段（动手上班）、18点段（动手放工）、19点段（动手加班）；

　　4）将每小我每天的归纳考勤值，如08:56:39（平常），按打卡韶华分离归属到对应的时段（9:00、18:00、19:00）下；

　　5）算计当日的出勤奖罚合计金额：迟到/早退罚款+加班夸奖。（若是您的公司正在汇总阶段分歧计两者，能够分离列出迟到/早退罚款、加班夸奖。）

　　由于每个月导出打卡记实数据尽头众，又没有模板可用，因此月底时小青总会彻夜加班干这事！

　　新修一个Excel文献，将sheet 1职业外重定名为“汇总外”。保管文献，定名为“汇总外模板”，方式为xlsm（Excel启用宏的职业簿）。

　　正在A1单位格输入“2022年11月考勤汇总外”字样，选中A1:G1，创立对齐办法为“跨列居中”，云云能够正在视觉上竣工归并单位格的结果。预防：“2022年11月考勤汇总外”中的年必需是四位数，月必需是两位数，亏欠位数的正在前面加0占位，如2月，就写成02月。

　　正在D2单位格输入公式“=MID($A$1,1,4)&MID($A$1,6,2)&01”天生与打卡记实外名称相同的日期。将公式向右填充到G2单位格，已毕第一天日期数据的填充。

　　用MID函数分离提取A1中前四位数字“2022”，第6、7位数字“11”，并利用“&”符号将字符“01”和它们连正在一同构成“20221101”，透露第一天。

　　汇总外中的日期与打卡记实外名称尽或许（非必需）维持相似！或许的话，应依照导出的打卡记实外名称样式来写公式天生日期。

　　定位到H2单位格输入公式“=D2+1”，向右拖至K2单位格，已毕“20221102”的填充。选中H2:K2区域，向右拖直到天生四个“20221131”为止。天生“20221131”是为了满意一切月的须要。

　　通过此刻公式创立，若是A1中的年份和月份窜改了，则第二行的日期会主动更改。

　　用MID函数分离从D2单位格中提取3组数字，然后用DATE函数将其转化为尺度的年月日形态的日期，接着正在外层套用TEXT函数得回日期对应的礼拜数。

　　第四行实质包含姓名、编号、当月扣款/加班、当日扣款/加班，以及三个时段9:00、18:00、19:00。

　　依照花名册录入员工姓名和编号。（后续各月能够依照员工离入职实质，增删、窜改姓名和编号。）

　　保管文献。到此曾经绘造好汇总外。正在已毕数据摒挡后，咱们还会返回其余中进一步创立公式竣工主动汇总。

　　新修一个名为“摒挡模板外”的职业外。接着掀开打卡记实文献“202211.xlsx”，将“20221101”外的实质全选复造，粘贴到“摒挡模板外”中。封闭“202211.xlsx”文献。

　　定位到I2单位格输入公式“=IFERROR(汇总外!A5,)”并下拉填充（下拉填充行数应当逾越此刻最大员工数20%及以上，以应对往后或许的员工人数拉长，巩固模板的实用性），将“汇总外”中的一切员工姓名获取过来。

　　公式下拉填充行数应当比凡是单日考勤最大行数众20%及以上，巩固模板的实用性。切记！切记！切记！譬如，平素单日最大考勤量是150行，这里就能够下拉填充到C200。

　　这是一个规范的LOOKUP切确查找套道公式。不领略的小伙伴能够查看著作《LOOKUP函数用法全解（上）——LOOKUP函数的5种用法》。

　　打卡韶华须要归类正在三个韶华段中。（若是一天四次打卡，加上加班时段，则要归属为五个韶华段。）

　　采用IFS函数实行众前提鉴定。函数公式=IFS(前提1, 值1, [前提2, 值2 ], [前提3, 值3]...)。适宜某个前提，就显示某个值。

　　正在E列设置“考勤状况”辅帮列。正在G列和H列设置韶华段与状况对应列，便当查找。

　　TEXT函数将B2单位格的数值显示为“时分秒”样式的文本。利用VLOOKUP函数实行分区段隐约查找取得状况值。VLOOKUP分区段隐约查找能够看《众前提鉴定，劝你用VLOOKUP函数隐约查找庖代IF函数的一长串公式！》。最终利用“&”将韶华和状况以及括号接连起来。

　　打卡韶华正在19:00:01及此后的为加班；每小时加班协帮10元，逾越1小时亏欠2小时的，四舍五入到整数实行补贴。

　　迟到/早退则每次扣10元。（这里简化了，实质或许依照迟到或早退的时长分歧，扣款金额分歧。）

　　ROUND((B22-TIME(19,0,1))*24,0)，用来得回四舍五入后的加班小时数。为何要乘以24？由于韶华，如“18:00:02”，正在Excel中的实质是一个以天数为单元的小数“0.7500231”，惟有乘以24后才取得小时数。

　　若是公司的考勤规章迟到早退的时长分歧扣款金额分歧，则须要正在公式中算计迟到或早退时长，然后套用Vlookup函数实行分区查找得回分歧的扣款金额。

　　这里利用SUMIF函数，以$C$2:$C$200为前提区域，以I2的姓名举动前提，合计F列的金额。预防公式中的数据区域巨细，若是公司有500人，每天打卡两次，很较着起码$C$2:$C$1001才合意。

　　若是您的公司正在汇总阶段分歧计两者，能够分离列出当日迟到/早退罚款、当日加班夸奖。当日迟到/早退罚款公式：

　　定位到K2单位格输入数组公式按Ctrl+Shift+Enter 收场，然后向右向下填充：

　　这是一个规范的INDEX嵌套MATCH函数援用数据的公式，不睬解的能够看著作《INDEX:函数中的切确造导导弹，最强健的瘸子》。

　　至此，摒挡模板外曾经已毕。只须将外中C~M列实质粘贴到其他打卡记实外中，即可主动已毕摒挡。

　　掀开打卡记实文献“202211.xlsx”，接着切换到掀开的“汇总外模板xlsm”,按住Ctrl键次第选中“汇总外”和“摒挡模板外”，右击采选“搬动或复造职业外”。

　　正在弹出的“搬动或复造职业外”对线.xlsx”；场所选“20221101”，透露将上述须要搬动的两个职业外安置正在其余之前；勾选下方“设置副本”。

　　单击“确定”，封闭对话框后，“汇总外”和“摒挡模板外”附加到此刻的“202211.xlsx”文档中。接着另存文献，将文档保管为启用宏的“11月汇总外.xlsm”。

　　按Alt+F11掀开VBA编辑器，单击“插入→模块”敕令，粘贴下列代码，天生一个名为“遍历职业外”的宏。

　　代码中利用“FOR EACH”语句来遍历职业簿，并拂拭名称中包罗“外”字的职业外（即汇总外和摒挡模板外）。创立变量“i”等于3，透露从排位第3的职业外（即每月第1天的职业外）动手粘贴；粘贴的实质复造自排位第2的职业外（Sheets（2），即摒挡模板外）的“C1:M200”区域。这里的数据区域巨细须要依照前面公式实质填充来窜改，譬如公式填充到1000行，则窜改为“C1:M1000”。

　　然后单击“开荒东西”菜单下“宏”按钮，正在弹出的“宏”对话框中采选“遍历职业外”，单击“推行”，即可已毕一切打卡外的主动摒挡。

　　接下来须要做的是返回到“汇总外”职业外中实行公式创立，完玉成体模板的搭修。因为篇幅源由，咱们下篇再来给专家讲授。

　　做Excel能手，速捷提拔职业恶果，部落窝教授《一周Excel直通车》视频和《Excel极速理解班》直播课全心为你！

　　非凡员工组别查找？INDEX、OFFSET、LOOKUP……我有100个函数能够管理这个题目

转载请注明出处。