oracle判断奇数偶数_考勤表制作——多表查询判断及快速填充的高级玩法

大多数职场人都需要上下班打卡，而每个月的考勤记录都会跟我们的工资息息相关，如：全勤奖、请假、调休、加班、迟到、早退……

如何利用EXCEL公式，将考勤机导出的原始数据转换成我们需要的结果呢？每个企业的考勤机不同，导出的数据格式也不同，我们今天围绕下边两张表来分析。

表一 考勤机导出的原始数据

表二 考勤统计表（本文要实现的结果）

仅看这两张表密密麻麻的是不是就有些头疼，这只是截取了一部分，一般单位人数都比这多的多，工作量自然也不容小觑。

1.问题分析

这是一个HR朋友公司6月份的考勤部分记录，这次分享也是来自朋友的一个小问题，我给出了自己的答案，也许其他朋友有更好的处理方法，欢迎一起交流。

目前数据录入方式

根据原始记录表数据，一一对应判断，在考勤报表中手动录入结果。

提出的问题

如何让正常出勤（上班未迟到、下班未早退）的自动生成“√”，以减少考勤统计工作量。

（生成“√”是为了进一步核算考勤结果）

解决思路

将原始记录表中的空格，即未打卡项返回空，正常上下班情况直接返回“√”，剩余返回"异常"。异常的情况，一般是迟到、外出、请假、忘打卡等原因，由于篇幅有限，这些本文暂时不做处理。

解决方法

通用公式：=IF(判断是奇数行，上午公式，下午公式）

上午公式：=IF(打卡记录为空，返回空，IF(最早打卡时间<=上班时间，"√","异常"))

下午公式：=IF(打卡记录为空，返回空，IF(最晚打卡时间>=下班时间，"√","异常"))

2.查询当天打卡记录

需要用到的函数

VLOOKUP(查找值,数据区域,返回值对应列序数,匹配条件)

详细介绍，可以看之前一篇文章：

萌懂微暖：职场干货：快速掌握VLOOKUP函数，查询不发愁​zhuanlan.zhihu.com

目标：查询“张丽丽”3号的出勤数据

注：6月份1号、2号正好是休息日，我直接在E5单元格录入公式，主要方便查验公式正确性。

E5公式：

=VLOOKUP($A5,[人资6月.xlsx]打卡时间!$A$3:$AE$22,E$4+1,0)

结果如下：

解释

1.查找值：“张丽丽”所对应单元格A5，用$锁定A列，方便后拉填充。
2.数据区域：两个数据表在一个文件夹内，输入文件名称——对应的表名称——需要查询的数据区域，依然需要用$锁定。

数据区域第一列必须是“姓名”列，公式录入完成后，系统会自动调整为以下格式：

=VLOOKUP($A5,'C:UsersAdministratorDesktop6月考勤表[人资6月.xlsx]打卡时间'!$A$3:$AE$22,E$4+1,0)

3.查找序列数恰好是日期+1的结果，故巧妙运用日期行数据，为后续公式填充做铺垫。

4.如果原始数据有修改，重新打开文件，会有“更新提示”，记得点【选项】——【确定】，更新结果。

3.提取当天最早、最晚打卡时间

需要用到的函数

LEFT(截取的单元格文本，从最左侧数截取字节个数）

RIGHT(截取的单元格文本，从最右侧数截取字节个数）

目标：查询“张丽丽”3号最早打卡时间即上班打卡时间、最晚打卡时间即下班打卡时间

E5公式：

=LEFT(VLOOKUP($A5,[人资6月.xlsx]打卡时间!$A$3:$AE$22,E$4+1,0),5)E6公式：

=RIGHT(VLOOKUP($A5,[人资6月.xlsx]打卡时间!$A$3:$AE$22,E$4+1,0),5)

最终效果如下：

提醒

原始表格不能有空格，录入公式前，可以用【替换】去除所有空格，以防影响结果。

4.条件判断，返回结果

需要用到的函数

TIMEVALUE(表示时间的文本串)

把时间转化为0到0.999999999的小数数值

TIME(hour, minute, second)

返回的小数值为 0（零）到 0.99999999 之间的数值，代表从 0:00:00 (12:00:00 AM) 到 23:59:59 (11:59:59 P.M.) 之间的时间。

IF(条件判断, 结果为真返回值, 结果为假返回值)

目标：判断“张丽丽”3号打卡结果，如果未打卡，返回空，如果正常出勤，返回“√”否则，返回“异常”。

E5（上午）公式：

=IF(VLOOKUP($A5,[人资6月.xlsx]打卡时间!$A$3:$AE$22,E$4+1,0)="","",IF(TIMEVALUE(LEFT(VLOOKUP($A5,[人资6月.xlsx]打卡时间!$A$3:$AE$22,E$4+1,0),5))<=TIME(8,30,0),"√","异常"))

=IF(打卡记录为空，返回空，IF(最早打卡时间<=上班时间，"√","异常"))

E6（下午）公式：

=IF(VLOOKUP($A5,[人资6月.xlsx]打卡时间!$A$3:$AE$22,E$4+1,0)="","",IF(TIMEVALUE(RIGHT(VLOOKUP($A4,[人资6月.xlsx]打卡时间!$A$3:$AE$22,E$4+1,0),5))>=TIME(18,0,0),"√","异常"))

=IF(打卡记录为空，返回空，IF(最晚打卡时间>=下班时间，"√","异常"))

录入公式后拉填充，即可得出“张丽丽”6月份打卡的判断结果，如下图

思考

还有一个小问题，奇数行与偶数行的公式不一样，如何快速填充所有员工的考勤结果呢？重复之前的步骤，继续录入下一个员工3号的考勤公式，再后拉填充，依次类推？

还有一个思路，我们把奇偶行的公式统一为一个公式来实现奇偶行公式交替，具体请看下一节。

5.通用公式

需要用到的函数

ROW（需要得到其行号的单元格或单元格区域）

MOD（被除数，除数）

返回余数

目标：利用IF函数，奇数行返回上午判断公式，偶数行返回下午判断公式。

E5公式：

=IF(MOD(ROW(E5),2)=1,IF(VLOOKUP($A5,[人资6月.xlsx]打卡时间!$A$3:$AE$22,E$4+1,0)="","",IF(TIMEVALUE(LEFT(VLOOKUP($A5,[人资6月.xlsx]打卡时间!$A$3:$AE$22,E$4+1,0),5))<=TIME(8,30,0),"√","异常")),IF(VLOOKUP($A4,[人资6月.xlsx]打卡时间!$A$3:$AE$22,E$4+1,0)="","",IF(TIMEVALUE(RIGHT(VLOOKUP($A4,[人资6月.xlsx]打卡时间!$A$3:$AE$22,E$4+1,0),5))>=TIME(18,0,0),"√","异常")))

最后一步

多数据快速填充

1.往右拉，填充一行。

2.选中需要填充的数据区域，【Ctrl】+【D】快速填充，即可得出文章开头表二结果。

excel快速填充，了解更多可查看往期文章：

萌懂微暖：Excel快速填充，还可以这样用！​zhuanlan.zhihu.com

结语

只有理解公式、了解思路，才有公式纠错能力，才可以活学活用，因此，文章介绍尽量详细了一些。文章主要分享的是跨表格查询记录并判断结果的思路，不只是适用于考勤统计工作，你的工作中有哪些场景也可以用到呢？

备注：

本文公式可以直接复制使用，套用公式时记得根据实际情况修改对应单元格信息。

本文分享操作方法全部基本Microsoft Excel 2007版本。

本文表格资料领取方式

微信公众号（萌懂微暖）窗口回复关键字：“考勤”，可以直接获取百度网盘下载地址。

链接有效期为7天，如果没有百度网盘，可以在对话窗口发送邮箱账号，我将统一处理。

END

本文首发于微信公众号：萌懂微暖，欢迎关注！如需转载，请注明出处。