转自EXCEL不加班

没想到3-4年过去了，还是有学员喜欢用这样的考勤表。

这是2016年卢子帮HR做的一个考勤模板。

这是学员现在做的模板，是不是挺像的？

这是从考勤机导出的记录，上下班可能存在多次打卡这种情况，怎么转变成上面的模板呢？

勤涉及到的知识非常多，卢子一个个慢慢讲。

1.将日期时间转变成9/1\n日这种格式

9/1就是月/日，\n是固定字符，日代表星期几。

这里借助TEXT函数转换，m就是月，d就是日，!\!n就是强制显示\n的意思，aaaa就是星期几。

=TEXT(D2,"m/d!\!naaa")

2.将日期时间中的时间提取出来

TEXT函数非常强大，可以转换很多种格式，提取时间也是可以的。h就是小时，m就是分钟。

=TEXT(D2,"hh:mm")

3.根据姓名和日期，判断上下班

上班就是每个人员每天第一次打卡时间。

下班就是每个人每天最后一次打卡时间。当然，这个下班的判断不需要太精确，把不是上班的全部归为下班就可以。因为后面借助LOOKUP函数，是查找最后一个满足条件的值，不管中间有多少次打卡，都会查找到最后一次。

这样判断起来就简单一些。

借助COUNTIFS函数判断是不是第一次打卡，如果是就返回上班，否则返回下班。

=IF(COUNTIFS(B$2:B2,B2,G$2:G2,G2)=1,"上班","下班")

准备工作做完，剩下的就是将这些数据引用到模板中。

4.引用每个人每天的打卡时间

需要同时借助3个条件查找，姓名、日期、上下班。

=IFERROR(LOOKUP(1,0/((LOOKUP("座",$A$3:$A3)=全体人员!$B$2:$B$115)*(全体人员!$G$2:$G$115=G$2)*(全体人员!$I$2:$I$115=$F3)),全体人员!$H$2:$H$115),"")

LOOKUP("座",$A$3:$A3)的意思就是让合并单元格都填充上姓名。

LOOKUP函数有很多种语法，剩下的就是经典的查找模式。

=LOOKUP(1,0/((查找值1=查找区域1)*(查找值2=查找区域2)*(查找值n=查找区域n)),返回区域)

最后，嵌套IFERROR函数是让没有对应值的显示空白，这样比较美观。

考勤表真的是Excel中一个非常难的问题，涉及到的知识超级多，每个公司用的考勤表基本不一样，增加学习的难度。

陪你学Excel，一生够不够？

,