怎么在excel编辑表格（Excel教程：excel员工考勤表模板，3小时制作完，免费下载！）Excel教程 / Excel表格制作与编辑...

点击“预约”按钮，Excel直播课免费学习每到月初，让人抓狂的事情就很多……比如总有一些同事跑过来问：昨天发的工资怎么不对？考勤表是不是算错了？员工的考勤汇总，是HR小青每个月都要做的事情，也是工资发放的基础工作。

做好考勤汇总随时备查，看似简单，里面却隐藏着许多的Excel知识今天就手把手地来教大家制作一个考勤汇总表！温馨提示：本篇文章长达3000+字，涉及常用函数、技巧、VBA等，建议收藏起来慢慢看！数据来源：。

公司使用的是指纹考勤机，月底可以从机器里导出员工打卡记录文件（xlsx格式，每天一张工作表），但是机器导出的数据只有员工编号和对应的打卡时间。

原始数据：打卡记录小青需要将每日打卡记录工作表中的原始数据，整理并汇总生成如下考勤汇总表：

汇总表效果需要这个模板的同学扫这个二维码获取

如果群满，添加微信：blwjymx发送对比打卡记录表和汇总表，小青需要做的事包括：1.绘制每月的考勤汇总表2.整理每张打卡记录，使其满足汇总表的需要1）将员工编号与姓名对应，便于按姓名汇总；2）根据打卡时间并结合公司考勤规则判断考勤状态：正常、迟到/早退、加班；将打卡时间与考勤状态组合成一个综合考勤值，如“08:56:39（正常）”；

3）判断打卡时间的归属时段：9点段（开始上班）、18点段（开始下班）、19点段（开始加班）；4）将每个人每天的综合考勤值，如08:56:39（正常），按打卡时间分别归属到对应的时段（9:00、18:00、19:00）下；

5）计算当日的出勤奖罚合计金额：迟到/早退罚款+加班奖励。（如果您的公司在汇总阶段不合计两者，可以分别列出迟到/早退罚款、加班奖励。）

3.将整理后的30张打卡记录数据汇总到汇总表中因为每个月导出打卡记录数据非常多，又没有模板可用，所以月底时小青总会通宵加班干这事！下面我们来设计汇总模板实现一键汇总：一、汇总表绘制：Step1：新建并保存为xlsm格式

新建一个Excel文件，将sheet 1工作表重命名为“汇总表”保存文件，命名为“汇总表模板”，格式为xlsm（Excel启用宏的工作簿）之所以选择启用宏的格式，是因为后续我们要使用VBA代码Step2：自动填充日期数据。

在A1单元格输入“2022年11月考勤汇总表”字样，选中A1:G1，设置对齐方式为“跨列居中”，这样可以在视觉上实现合并单元格的效果在D2单元格输入公式“=MID($A$1,1,4)&MID($A$1,6,2)&"01"”生成与打卡记录表名称一样的日期。

将公式向右填充到G2单元格，完成第一天日期数据的填充

公式解析：用MID函数分别提取A1中前四位数字“2022”，第6、7位数字“11”，并使用“&”符号将字符“01”和它们连在一起组成“20221101”，表示第一天重要提醒：汇总表中的日期与打卡记录表名称尽可能（非必须）保持一致！可能的话，应根据导出的打卡记录表名称样式来写公式生成日期。

譬如，打卡记录表名称是“2021-11-01”，则D2单元格公式=MID($A$1,1,4)&"-"&MID($A$1,6,2)&"-"&"01"定位到H2单元格输入公式“=D2+1”，向右拖至K2单元格，完成“20221102”的填充。

选中H2:K2区域，向右拖直到生成四个“20221131”为止生成“20221131”是为了满足所有月的需要

通过当前公式设置，如果A1中的年份和月份修改了，则第二行的日期会自动更改Step3：填充星期数据下面根据第二行的日期，在第三行加上对应的星期数据在D3输入公式“=TEXT(DATE(MID(D2,1,4),MID(D2,5,2),MID(D2,7,2)),"aaaa")”，向右填充。

公式解析：用MID函数分别从D2单元格中提取3组数字，然后用DATE函数将其转化为标准的年月日形式的日期，接着在外层套用TEXT函数获得日期对应的星期数Step4：填充第四行第四行内容包括姓名、编号、当月扣款/加班、当日扣款/加班，以及三个时段9:00、18:00、19:00。

Step5：输入员工姓名和编号根据花名册录入员工姓名和编号。（后续各月可以根据员工离入职实际，增删、修改姓名和编号。）

保存文件到此已经绘制好汇总表在完成数据整理后，我们还会返回此表中进一步设置公式实现自动汇总二、建立打卡数据整理模板Step1：新建整理模板表继续在当前文件中工作新建一个名为“整理模板表”的工作表接着打开打卡记录文件“202211.xlsx”，将“20221101”表的内容全选复制，粘贴到“整理模板表”中。

关闭“202211.xlsx”文件Step2：建立整理后表格样式在I:M处建立整理后的表格样式C:H列暂时空着便于后续建立辅助列定位到I2单元格输入公式“=IFERROR(汇总表!A5,"")”并下拉填充（下拉填充行数应该超过当前最大员工数20%及以上，以应对今后可能的员工人数增长，增强模板的适用性），将“汇总表”中的所有员工姓名获取过来。

Step3：将编号和员工姓名对应在C列创建“姓名”辅助列定位到C2单元格输入公式“=LOOKUP(1,0/(汇总表!B$5:B$200=A2),汇总表!$A$5:$A$200)&""”，下拉公式完成编号和人名的对应。

重要提醒：公式下拉填充行数应该比平常单日考勤最大行数多20%及以上，增强模板的适用性切记！切记！切记！譬如，日常单日最大考勤量是150行，这里就可以下拉填充到C200文章后续提到的公式下拉填充都如此办理。

公式解析：这是一个典型的LOOKUP精确查找套路公式不理解的小伙伴可以查看文章《LOOKUP函数用法全解（上）——LOOKUP函数的5种用法》Step4：打卡时段归类打卡时间需要归类在三个时间段中（如果一天四次打卡，加上加班时段，则要归属为五个时间段。

）<18点,归于9:00段≥18点≤19点，归于18:00段其他，归于19:00段在D列新建“归属时段”辅助列在D2单元格输入公式并下拉填充：=IFS(B2=TIME(18,0,0),B2TIME(19,0,0),"19:00")。

公式解析：采用IFS函数进行多条件判断函数公式=IFS(条件1, 值1, [条件2, 值2 ], [条件3, 值3]...)符合某个条件，就显示某个值如果读者的版本低不支持IFS函数，也可以使用IF函数，公式：。

=IF(B2

Step5：生成综合考勤值在E列建立“考勤状态”辅助列。在G列和H列建立时间段与状态对应列，方便查找。

时间段和状态对应列是根据考勤规则建立的：0:00:00到9:00:00之间打卡，正常上班；9:00:01到17:59:59之间打卡， 迟到或者早退；18:00:00到19:00:00之间打卡，正常下班；

19:00:01及以后打卡，加班定位到E2单元格输入公式“=TEXT(B2,"hh:mm:ss")&"("&VLOOKUP(B2,$G$2:$H$5,2,1)&")"”，下拉公式生成打卡时间+考勤状态的综合考勤值。

公式解析：TEXT函数将B2单元格的数值显示为“时分秒”样式的文本使用VLOOKUP函数进行分区段模糊查找得到状态值VLOOKUP分区段模糊查找可以看《多条件判断，劝你用VLOOKUP函数模糊查找取代IF函数的一长串公式！》。

最后使用“&”将时间和状态以及括号连接起来Step6：计算当次打卡的扣款/加班补助公司考勤规定：打卡时间在19:00:01及以后的为加班；每小时加班补助10元，超过1小时不足2小时的，四舍五入到整数进行补贴。

迟到/早退则每次扣10元（这里简化了，实际可能根据迟到或早退的时长不同，扣款金额不同）定位到F2单元格输入公式并下拉填充：=IFS(VLOOKUP(B2,$G$2:$H$5,2,1)=$H$2,"",VLOOKUP(B2,$G$2:$H$5,2,1)=$H$3,-10,VLOOKUP(B2,$G$2:$H$5,2,1)=$H$5,ROUND((B2-TIME(19,0,1))*24,0)*10)。

公式解析：ROUND((B22-TIME(19,0,1))*24,0)，用来获得四舍五入后的加班小时数为何要乘以24？因为时间，如“18:00:02”，在Excel中的本质是一个以天数为单位的小数“0.7500231”，只有乘以24后才得到小时数。

Excel低版本读者也可以使用如下公式：=IF(VLOOKUP(B2,$G$2:$H$5,2,1)=$H$3,-10,IF(VLOOKUP(B2,$G$2:$H$5,2,1)=$H$5,ROUND((B2-TIME(19,0,1))*24,0)*10,""))

如果公司的考勤规定迟到早退的时长不同扣款金额不同，则需要在公式中计算迟到或早退时长，然后套用Vlookup函数进行分区查找获得不同的扣款金额Step7：合计员工当天扣款/加班补助金额定位到J2单元格输入公式并下拉填充：。

=IF(SUMIF($C$2:$C$200,I2,$F$2:$F$200)<>0,SUMIF($C$2:$C$200,I2,$F$2:$F$200),"")

公式解析：这里使用SUMIF函数，以$C$2:$C$200为条件区域，以I2的姓名作为条件，合计F列的金额注意公式中的数据区域大小，如果公司有500人，每天打卡两次，很显然至少$C$2:$C$1001才合适。

外层嵌套IF函数的目的是让没有扣款/加班金额的显示为空如果不需要显示为空，则公式更简单，=SUMIF($C$2:$C$200,I2,$F$2:$F$200)Step8:提取综合考勤值下面将综合考勤值归属到三个时段下。

定位到K2单元格输入数组公式按Ctrl+Shift+Enter 结束，然后向右向下填充：=IFERROR(INDEX($E$1:$E$200,MATCH($I2&K$1,$C$1:$C$200&$D$1:$D$200,0)),"")

公式解析：这是一个典型的INDEX嵌套MATCH函数引用数据的公式，不明白的可以看文章《INDEX:函数中的精确制导导弹，最强大的瘸子》保存文件至此，整理模板表已经完成只要将表中C~M列内容粘贴到其他打卡记录表中，即可自动完成整理。

为了省事，我们用VBA代码来做这种批量复制粘贴操作Step9：用VBA代码批量处理其他打卡记录表打开打卡记录文件“202211.xlsx”，接着切换到打开的“汇总表模板xlsm”,按住Ctrl键依次选中“汇总表”和“整理模板表”，右击选择“移动或复制工作表”。

在弹出的“移动或复制工作表”对话框中，工作簿选择“202211.xlsx”；位置选“20221101”，表示将上述需要移动的两个工作表放置在此表之前；勾选下方“建立副本”单击“确定”，关闭对话框后，“汇总表”和“整理模板表”附加到当前的“202211.xlsx”文档中。

接着另存文件，将文档保存为启用宏的“11月汇总表.xlsm”关闭“汇总表模板.xlsm”文件按Alt+F11打开VBA编辑器，单击“插入→模块”命令，粘贴下列代码，生成一个名为“遍历工作表”的宏Sub 遍历工作表()。

  如果工作表名称不包含“表”For Each sh In Workbooks("11月汇总表.xlsm").WorksheetsIf Not sh.Name Like "*" & "表" & "*" Then  如果工作表名称不包含“表”

sh.SelectFor i = 3 To ActiveWorkbook.Worksheets.CountSheets(2).Range("C1:M200").Copy ActiveWorkbook.Worksheets(i).Range("C1:M200")

NextEnd IfNextEnd Sub

代码解释代码中使用“FOR EACH”语句来遍历工作簿，并排除名称中包含“表”字的工作表（即汇总表和整理模板表）设置变量“i”等于3，表示从排位第3的工作表（即每月第1天的工作表）开始粘贴；粘贴的内容复制自排位第2的工作表（Sheets（2），即整理模板表）的“C1:M200”区域。

这里的数据区域大小需要根据前面公式实际填充来修改，譬如公式填充到1000行，则修改为“C1:M1000”关闭VBA编辑窗口并保存文件然后单击“开发工具”菜单下“宏”按钮，在弹出的“宏”对话框中选择“遍历工作表”，单击“执行”，即可完成所有打卡表的自动整理。

宏对话框

自动整理后的“20221101”工作表保存文件接下来需要做的是返回到“汇总表”工作表中进行公式设置，完成整个模板的搭建由于篇幅原因，本篇内容将讲到这里，我们下篇继续来给大家介绍需要课件练习的同学，扫码在群里下载。

不懂的知识点，在评论区交流呀。

另外，Excel基础不扎实的同学，欢迎学习下面这套工作用得上的Excel训练营