粉丝从某音上面刷到一个做考勤表的方法,并用在自己的表格上。于是就出现了标题说到的这一幕,写了62个VLOOKUP函数,简直疯了。
每一天的考勤存放在一个表,12月份共31天就31个表,然后根据姓名查找对应的出勤在明细表20列,加班在明细表21列。
操作方法就是先筛选出勤,输入公式下拉。
=VLOOKUP(C4,'1201'!A:U,20,0)
再筛选加班,输入公式下拉。
=VLOOKUP(C4,'1201'!A:U,21,0)
再将以上步骤重复31遍。每一列操作一遍。
如果有看卢子前几天写的文章,就会想到用VLOOKUP+INDIRECT实现一次搞定。
VLOOKUP查找多个表格,这次全讲明白了(内含57套PPT年终报告模板)
别吓自己,INDIRECT函数跨表引用真的好简单(内含25个函数视频)
单元格的日期跟工作表的显示不一样,可以用手工都改成1201,也可以借助TEXT转换。
=TEXT(E3,"mmdd")
出勤的31个公式都可以统一成这样。
=VLOOKUP($C4,INDIRECT(TEXT(E$3,"mmdd")&"!A:U"),20,0)
同理,将VLOOKUP的第三参数20改成21就得到加班的。
=VLOOKUP($C4,INDIRECT(TEXT(E$3,"mmdd")&"!A:U"),21,0)
能否用一条公式解决呢?
可以,不过要解决2个问题。
01 姓名是合并单元格,如果直接下拉的话,$C4是有内容,$C5就没内容,需要解决,让没内容的等于上一个单元格。
=IF($C4="",$C3,$C4)
02 如果是出勤的就返回第20列,加班就返回21列。
=IF($D4="出勤",20,21)
将这些公式合并,就可以下拉和右拉。
=VLOOKUP(IF($C4="",$C3,$C4),INDIRECT(TEXT(E$3,"mmdd")&"!A:U"),IF($D4="出勤",20,21),0)
这个公式还可以进一步完善,让错误值显示空白,也就是嵌套IFERROR。
=IFERROR(VLOOKUP(IF($C4="",$C3,$C4),INDIRECT(TEXT(E$3,"mmdd")&"!A:U"),IF($D4="出勤",20,21),0),"")
最后,还有一个小细节,里面的时间是文本格式,因为后期要求和,这里加--转换。
=IFERROR(--VLOOKUP(IF($C4="",$C3,$C4),INDIRECT(TEXT(E$3,"mmdd")&"!A:U"),IF($D4="出勤",20,21),0),"")
如果让你一次性写完这么长的公式,你应该写不出。不过,没关系,卢子也写不出,但你要学会写公式的方法。先分几列依次在单元格写上单个函数,然后组合成完整的公式。
推荐:想要Excel水平超过90%的同事,只需学会这20个公式就够了!(内含836个表格模板)
上篇:突破限制,实现多个内容、多表同时筛选(内含电脑入门电子书)
作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)
联系客服