之前写了一篇关于员工考勤统计的文章(考勤表制作——多表查询及快速填充的高级玩法),得到了很多朋友的认可与支持,结合最近朋友们的反馈意见,今天跟大家分享一下考勤表制作的“自动化模式”,我们先看一下效果:
月初导出打卡记录后,只需要简单复制、粘贴,修改月份,剩下的工作自动化完成。
当月天数、日期与星期的自动变更
周末休息日单元格颜色自动填充
P2单元格对应年份,S2单元格对应月份,日期更改后,相应的“当月天数”会自动计算。
DAY函数:返回以序列号表示的某日期的天数,用整数 1 到 31 表示。
EOMONTH函数:返回指定日期之前或者之后的月份的最后一天的序列号。
start_date参数必须为日期格式,字符串形式的日期将计算错误。
months为日期之前或之后的月份数。
返回结果默认为日期对应的序列号,设置单元格格式为“日期”,可以更直观理解函数。
Date(年,月,0):返回上一个月的最后一天日期。例如:
02 日期、星期自动化
按31天(即31列日期)做表,如遇到小于31天的月份,可以把多余的列手动隐藏,不影响考勤结果的核算,切勿删除,以免影响下个月份表格的自动生成。
03周末休息日单元格颜色自动填充
- 选中需要填充颜色的区域。
- 【开始】---【条件格式】---【新建规则】。
- 选中【使用公式确定要设置格式的单元格】公式为:
OR函数:在其参数组中,任何一个参数逻辑值为 TRUE,即返回 TRUE。
- 点【格式】,选择需要填充的颜色,点【确定】即可。
注:法定节假日休息不在自动填充范围,需要根据当月实际休假情况手动处理。
上篇考勤表制作文章原始数据是这个样式:
如果考勤机导出的原始数据是下面这个样式,又该如何利用公式统计考勤结果呢?
01 查询当天的打卡记录
区别于样式一,样式二表格中的数据查询我们需要用到HLOOKUP函数。
HLOOKUP函数是Excel中的横向查找函数,它与LOOKUP函数和VLOOKUP函数属于一类函数,HLOOKUP是按行查找的,VLOOKUP是按列查找的。
=HLOOKUP(1号,数据查询区域,张丽丽所在行+1,精确匹配)
MATCH函数可在单元格区域中搜索指定项,然后返回该项在单元格区域中的相对位置。
02 提取当天最早、最晚打卡时间
LEFT(截取的单元格文本,从最左侧数截取字节个数)
RIGHT(截取的单元格文本,从最右侧数截取字节个数)
张丽丽1号最晚打卡时间即D6单元格公式:
最早打卡时间<=8:00,正常出勤;
最晚打卡时间>=18:00,正常出勤;
通用公式:=IF(判断是奇数行,上午公式,下午公式)
考勤统计结果显示规则:正常出勤返回空值,休息返回"×",迟到返回"※",早退返回"早退",异常返回"异常"。
本文仅列举了考勤统计公式应用的思路,每个企业考勤规则都有所不同,可以根据实际情况稍作调整后使用。比如:请假情况需要根据判断结果手动录入。
了解EXCEL的使用方法,善用EXCEL函数,会大大提升办公效率,事半功倍,反之,一知半解错用公式,会带来一连串的错误结果,事倍功半。没必要刻意为了用公式而找公式,活学活用,适合自己的才是最好的。
公众号窗口回复关键字:“考勤制作”,可以直接获取本文案例表格文件,查看详细公式。
本文首发于公众号:萌懂微暖,查看原文
关注公众号,可直接领取海量HR学习资料。