Excel实战：考勤表（公式）

表格公式问题，可公众号留言。

QQ交流群2：860692128（新开）

QQ群，可下载表格源文件。

需求：用公式自动生成【出勤记录表】。

图-1丨考勤机导出 图-2丨出勤记录表

关键词：考勤表  公式

---

01 思路分析

手动操作？

“只需三步：筛选→复制粘贴→循环操作，就这样子。”

群主：“666哇，你也蛮辛苦的，眼睛要瞎啊，早点问我好了。”

公式思路

根据经验，考勤机导出数据，一般是文本格式，不能计算，第一步需要转换格式。

可能还存在，一天签到/签退数次的情况，需确定，当日最早签到及最迟签退时间。

综上，公式思路分三步走：

①统一格式，便于计算；

②确定一天中的最早签到及最迟签退时间；

③将上一步结果，匹配到【出勤记录表】。

---

02 具体操作

如图-2，提取日期，E2处公式：

=TEXT(D2,"yyyy/mm/dd")，向下填充。

图-2丨提取日期

如图-3，将打卡时间转换为数值格式，F2处公式：

=--TEXT(D2,"yyyy/mm/dd HH:MM:ss")，向下填充。

图-3丨文本转数值格式

如图-4，确定最早签到时间，G2处公式：

=TEXT(MINIFS(F:F,E:E,E2),"hh:mm:ss")，向下填充。

公式解析：

巧妙的MINIFS函数，可根据条件，先筛选出某一天的打卡记录，然后再找出它们中的最小值=当日最早签到时间。

图-4丨确定最早签到时间

同理图-5，H2处公式：

=TEXT(MAXIFS(F:F,E:E,E2),"hh:mm:ss")，向下填充。

图-5丨确定最迟签退时间

图-6，这个简单，用连接符&，将姓名和日期连接，作为匹配依据。

图-6丨连接符&

接下来，重点！敬请期待......

开个玩笑，我们继续。

如图-7，E2处公式：

=INDEX(原始数据!G:G,MATCH(C3&TEXT(D3,"yyyy/mm/dd"),原始数据!I:I,0))，向下填充。

图-7丨匹配最早签到时间

同理图-8，F2处公式：

=INDEX(原始数据!H:H,MATCH(C3&TEXT(D3,"yyyy/mm/dd"),原始数据!I:I,0))，向下填充。

图-8丨匹配最迟签退时间

公式解析：

看起来很长，其实就是个简单的匹配公式。index+match组合=vlookup函数，该组合以前有过分享，感兴趣的可到公众号查阅。

若实在读不懂，也可用vlookup，不过需要调整一下列顺序，动图演示如下：

图-9丨动图演示vlookup

最后图-10，K2处公式：

=IFERROR(IF(E3=F3,"上下班时间一样，忘签？",""),"休息？")，向下填充。

作用：简单初步判断，异常打卡情况。

图-10丨初步判断异常打卡

具体情况，尚需人工判定。