|
与 30万 读者一起学Excel 这是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，一生够不够？ 上篇：哈哈哈，再也不用Ctrl+V到手酸，又挖掘出邮件合并的妙用 作者：卢子，清华畅销书作者，《Excel效率手册 早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban） |
|
|
