如果在考勤表上那一个个细小的方格内打勾、画圈、描星,不要说上班轻松,就连数这些符号都能累的你够呛。
因此,学会用excel制作适合自己的全自动考勤模板,那真的是很方便了。
该模板可实现以下几种功能:月份和日期的自动跳转、双休日颜色提示、当前工作日提醒、考勤项列表选择、迟到早退、中途脱岗折算为出勤天数、月度考勤自动汇总、单元格保护。
文末有模板领取方式
做出表格框架
做出如下图样式的表格,表格右侧一部分是需要记录的考核项目,如迟到次数、病假天数等等这些信息。
每天的考勤记录分上午和下午,避免出现无法考勤“半天上班、半天请假”的情况。
做出第一个员工考勤的两行(一行上午、一行下午)后,鼠标放到第4、第5行的行标上选中这两行,点击格式刷,从第6行行标开始向下拖动,有多少员工就拖动多少。
月份和日期的自动跳转
1、年月下拉选择框
选中需要显示年的单元格,不够显示可以适当合并几个单元格。
执行【数据有效性】→【允许】→【序列】,【来源】中输入
2020,2021,2022,2023,2024,2025五年。
用同样的方法做出月份,【来源】中输入1,2,3,4,5,6,7,8,9,10,11,12
需要注意的是来源中各项用英文逗号分开。
2、选择月份自动显示日期
在D5单元格输入下面公式:
=IF(MONTH(DATE($D$2,$H$2,COLUMN(A2)))=$H$2,DATE($D$2,$H$2,COLUMN(A2)),"")
这时,你会惊奇发现,该单元格里面显示数字43862,其实这不是错误,而是日期的格式不对。
按ctrl+1,打开格式设置对话框,自定义输入“d”即可显示正常。
最后向右拖动,生成一个月的天数。
3、显示日期对应的星期
每个日期都对应一个相应的星期。
所以,星期=日期,然后改变一下显示格式即可完成,D4单元格中输入公式:=D5,向右拖动。
按ctrl+1,打开格式设置对话框,设置为日期,类型为“三”看下图。
双休日颜色提示
选中D4单元格,【条件格式】→【新建规则】→【使用公式确定要设置格式的单元格】
输入公式=WEEKDAY(D4,2)>5,
点击下面的格式,设置字体加粗,颜色红色,再向右拖动即可。
公式返回D4单元格中星期的值,后面的数字有3种:
数字1 或省略则1 至7 代表星期天到星期六,
数字2 则1 至7 代表星期一到星期天(本例中使用),
数字3则0至6代表星期一到星期日。
当前工作日提醒
公式:
="今天是:"&TEXT(TODAY(),"yyyy年m月d日")&"【"&TEXT(TODAY(),"[$-804]aaaa;@")&"】"
考勤单项下拉列表
考勤单项可根据实际情况自行设定,需要考勤哪几项就填写那几项。
选中D6单元格,【数据有效性】→【允许】→【序列】,【来源】中输入出勤,出差,休假,事假,病假,旷工,迟到,早退,中途脱岗。
然后拖动鼠标,使其考勤的全部单元格都有考勤单项下拉列表。
用条件格式,把特别需要指出的考核项用不同颜色显示出来,使得一目了然。
选中月内需要考勤的单元格,【条件格式】→【突出显示单元格规则】→【其它规则】,弹出新建规则对话框,如下图红色框圈起来所示填写,再点击下方【设置】,将字体颜色设为自己喜欢的颜色即可。
全勤的毕竟占绝大多数,可以设置“出勤”为不显示,这样整个界面就会显得清爽一些。
这一步的规则可以根据自己的需要来设置。
迟到早退、中途脱岗折算为出勤天数
迟到次数:=COUNTIF(D6:AH6,"迟到")+COUNTIF(D7:AH7,"迟到")
早退、中途脱岗则把上面公式中的“迟到”替换掉即可。
迟到早退折算天数:
=IF(AN6+AO6>=5,"1","0")
其中AN6+AO6>=5为迟到和早退次数,“1”为条件符合算一天,“0”不够5次显示0。
本例中,迟到和早退次数超过5次(包含)算一天旷工,中途脱岗超过2次(包含)算1天旷工。
显示“0”的意思是后边还要折算出总的出勤天数,需要参加运算,在正常出勤天数里面减去迟到早退次数够5的折算天数,如果留空则没法运算。
月度考勤自动汇总
每月结束,要对是否全勤、休假天数、迟到早退次数等做一汇总。
函数公式如下:
休假天数:=(COUNTIF(D6:AH6,"休假")+COUNTIF(D7:AH7,"休假"))*0.5
事假、病假、旷工则把上面公式中的“休假”替换掉即可。
单元保护
除了选择考勤单项的单元格外,其它单元格均设置锁定,保护。
先设置单元格保护,再对工作表加密。
这样记录员工考勤,方便管理,易于存档,便于统计,节约纸张~~~