继前几天教大家统计健康打卡表以后,很多同学询问如何制作考勤表。

其实考勤表不需要自己做,Excel 本身就有很多自带的优秀模板,比如 2016 版的考勤表就挺好。

大家可以考虑以这个考勤表为模板,根据自己需要适当调整或增加功能,比如,增加一个全年请假数汇总表。

案例:

使用下图 1 的模板记录考勤数据,将 12 个工作表中的考勤记录汇总一个全年总表,统计每个人的请假总数。

效果如下图 2 所示。

excel考勤表怎么计算总小时(考勤表模板虽好)(1)

excel考勤表怎么计算总小时(考勤表模板虽好)(2)

解决方案:

1. 打开 Excel --> 选择“开始”-->“新建”,就能看到很多默认的模板 --> 选择“员工考勤时间表”模板

excel考勤表怎么计算总小时(考勤表模板虽好)(3)

excel考勤表怎么计算总小时(考勤表模板虽好)(4)

这个模板已经做得挺好了,你也可以根据需要适当修改,比如,修改“缺勤类型键”。目前首先要将姓名修改一下。

2. 选中“员工姓名”工作表,输入每个人的姓名

excel考勤表怎么计算总小时(考勤表模板虽好)(5)

3. 回到“1月”工作表,从更新后的下拉菜单中依次选择每个员工姓名

excel考勤表怎么计算总小时(考勤表模板虽好)(6)

excel考勤表怎么计算总小时(考勤表模板虽好)(7)

excel考勤表怎么计算总小时(考勤表模板虽好)(8)

4. 将“1月”工作表中的姓名依次复制粘贴到其他月的工作表中

excel考勤表怎么计算总小时(考勤表模板虽好)(9)

excel考勤表怎么计算总小时(考勤表模板虽好)(10)

现在考勤表模板已经微调好了,可以根据实际情况填写公司员工的考勤记录。

美中不足的是,这个考勤表每个月各有一个单独的工作表,却没有全年记录汇总表,统计每个人的总出勤记录非常不便。

接下来我们就增加一个全年汇总表。

5. 选择菜单栏的“数据”-->“新建查询”-->“从文件”-->“从工作簿”

excel考勤表怎么计算总小时(考勤表模板虽好)(11)

6. 在弹出的对话框中选择考勤表文件 --> 点击“导入”

excel考勤表怎么计算总小时(考勤表模板虽好)(12)

7. 在弹出的“导航器”对话框中勾选“选择多项”--> 勾选 1 至 12 月工作表 --> 点击“转换数据”

excel考勤表怎么计算总小时(考勤表模板虽好)(13)

12 个月的工作表就上传至 Power Query 中了。

excel考勤表怎么计算总小时(考勤表模板虽好)(14)

8. 选中“1月”工作表 --> 选择菜单栏的“主页”-->“追加查询”

excel考勤表怎么计算总小时(考勤表模板虽好)(15)

9. 在弹出的对话框中选择“三个或更多”

excel考勤表怎么计算总小时(考勤表模板虽好)(16)

10. 选中 2 至 12 月工作表 --> 点击“添加”

excel考勤表怎么计算总小时(考勤表模板虽好)(17)

11. 点击“确定”

excel考勤表怎么计算总小时(考勤表模板虽好)(18)

12 个月的记录就合并到了同一个查询中。

excel考勤表怎么计算总小时(考勤表模板虽好)(19)

第一列全是空值,不需要,可以将其删除。

12. 选中第一列 --> 选择菜单栏的“主页”-->“删除列”

excel考勤表怎么计算总小时(考勤表模板虽好)(20)

13. 选中“员工考勤时间表”列 --> 选择菜单栏的“转换”-->“逆透视列”-->“逆透视其他列”

excel考勤表怎么计算总小时(考勤表模板虽好)(21)

excel考勤表怎么计算总小时(考勤表模板虽好)(22)

14. 删除“属性”列

excel考勤表怎么计算总小时(考勤表模板虽好)(23)

15. 点击“值”列旁边的下拉框选项 --> 勾选 P、S、V --> 点击“确定”

因为只有这三个选项是考勤记录,其他都不是,就不需要显示了。

excel考勤表怎么计算总小时(考勤表模板虽好)(24)

excel考勤表怎么计算总小时(考勤表模板虽好)(25)

16. 点击“员工考勤时间表”旁边的下拉框选项 --> 取消勾选非姓名选项 --> 点击“确定”

excel考勤表怎么计算总小时(考勤表模板虽好)(26)

excel考勤表怎么计算总小时(考勤表模板虽好)(27)

17. 选择菜单栏的“主页”-->“分组依据”

excel考勤表怎么计算总小时(考勤表模板虽好)(28)

18. 在弹出的对话框中选择“高级”--> 在下拉菜单中选择“员工考勤时间表”--> 点击“添加分组”

excel考勤表怎么计算总小时(考勤表模板虽好)(29)

19. 在下拉菜单中选择“值”

excel考勤表怎么计算总小时(考勤表模板虽好)(30)

20. 在“新列名”中输入“天数”,“操作”中选择“对行进行计数”--> 点击“确定”

excel考勤表怎么计算总小时(考勤表模板虽好)(31)

excel考勤表怎么计算总小时(考勤表模板虽好)(32)

21. 选中“值”列 --> 选择菜单栏的“转换”-->“透视列”

excel考勤表怎么计算总小时(考勤表模板虽好)(33)

22. 在弹出的对话框的“值列”选择“天数”--> 点击“确定”

excel考勤表怎么计算总小时(考勤表模板虽好)(34)

excel考勤表怎么计算总小时(考勤表模板虽好)(35)

23. 选择菜单栏的“主页”-->“关闭并上载”-->“关闭并上载至...”

excel考勤表怎么计算总小时(考勤表模板虽好)(36)

24. 在弹出的对话框中选择“仅创建连接”--> 点击“加载”

excel考勤表怎么计算总小时(考勤表模板虽好)(37)

现在回到了 Excel,右侧会看到“工作簿查询”设置区域。

excel考勤表怎么计算总小时(考勤表模板虽好)(38)

25. 在右侧的“工作簿查询”区域选中“1月”--> 右键单击 --> 在弹出的菜单中选择“加载到...”

excel考勤表怎么计算总小时(考勤表模板虽好)(39)

26. 在弹出的对话框中选择“表”-->“新建工作表”--> 点击“加载”

excel考勤表怎么计算总小时(考勤表模板虽好)(40)

27. 此刻工作簿中就出现了一个新的工作表,统计出了每个员工全年各类休假总数。

excel考勤表怎么计算总小时(考勤表模板虽好)(41)

28. 选择这个表格的任意区域 --> 选择菜单栏的“设计”--> 从中选择一种所需的样式

excel考勤表怎么计算总小时(考勤表模板虽好)(42)

今后任何月份的考勤表若有变化,只要选中下表,右键单击,选择“刷新”,就能实时更新汇总结果。

excel考勤表怎么计算总小时(考勤表模板虽好)(43)

很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。

现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。

,