PMC47周:考勤计算专题 - 1

古哥计划

Lv.2潜力创作者

47-1 上午下班打卡记录 —— 提取每日12:00至13:00之间的最早打卡时间

在考勤分析中，上午下班打卡通常指员工在中午休息前完成的最后一次打卡。由于可能存在多次打卡（如补卡、反复进出），需从 12:00 至 13:00 时间段内提取出当天最早的那次打卡时间，作为正式的“上午下班”记录。

🔧 公式解析与对比

✅ 方法一：使用 FILTER + XLOOKUP + CONCAT 实现匹配（推荐）

excel编辑

J2 = LET(M, FILTER(A2:D627, (D2:D627 < 1/24*13) * (D2# > 1/24*9)),
         XLOOKUP(BYROW(F2:H162, CONCAT),
                 BYROW(TAKE(M,,3), CONCAT),
                 TAKE(M,,-1),
                 "未打卡",
                 ,-1))

🔍 解析步骤：
M = FILTER(A2:D627, (D2:D627 < 1/24*13) * (D2# > 1/24*9))：
(D2:D627 < 1/24*13)：筛选早于 13:00 的打卡
(D2# > 1/24*9)：筛选晚于 09:00 的打卡（避免误选早上打卡）
* 表示逻辑“且”
BYROW(F2:H162, CONCAT)：将姓名+日期组合为唯一键（如 "刘备2025/10/2"）
BYROW(TAKE(M,,3), CONCAT)：对过滤后的数据也做相同拼接
TAKE(M,,-1)：提取打卡时间列
XLOOKUP(..., ..., "未打卡", ,-1)：
查找每个员工每天的打卡时间
使用 -1 参数表示：从最后一项开始反向搜索
这样可确保优先匹配最晚的打卡时间（即接近13:00）

✅ 优点：

代码简洁，逻辑清晰

利用 CONCAT 构造复合键，实现精准匹配

支持动态数组，性能良好

❌ 缺点：

依赖 XLOOKUP 和 CONCAT 函数（WPS/Excel 365 支持）

不适合复杂条件组合

📌 适用场景：

数据结构规范、需要快速提取中午打卡时间的场景

✅ 方法二：使用 FILTER + UNIQUE + XLOOKUP 实现去重与匹配

excel编辑

J2 = LET(D, D2:D627,
         A, FILTER(A2:D627, (D >= --"12:00") * (D <= --"13:00")),
         XLOOKUP(BYROW(UNIQUE(A2:C627), CONCAT),
                 BYROW(DROP(A,,-1), CONCAT),
                 TAKE(A,,-1),
                 "未打卡",
                 ,-1))

🔍 解析步骤：
D = D2:D627：定义时间列
A = FILTER(A2:D627, (D >= --"12:00") * (D <= --"13:00"))：
使用 --"12:00" 将文本时间转为数值（更直观）
筛选出 12:00 至 13:00 之间的打卡记录
UNIQUE(A2:C627)：对姓名+日期去重，得到每个员工每天的唯一记录
BYROW(..., CONCAT)：构造复合键
DROP(A,,-1)：提取除时间外的前三列
XLOOKUP(..., ..., "未打卡", ,-1)：查找对应时间，反向搜索

✅ 优点：

使用 --"12:00" 更易读，避免小数偏差

UNIQUE 确保每条记录唯一

思路清晰，易于理解

❌ 缺点：

公式较长，调试困难

性能较低（尤其大数据集）

📌 适用场景：

需要精确控制时间范围、且数据未排序的场景

✅ 方法三：使用 GROUPBY + MAX + XLOOKUP 实现聚合与匹配

J2 = LET(X, A2:C627,
         D, D2:D627,
         A, GROUPBY(X, D, MAX,,0,, (D < 1/24*13) * (D > 1/24*9)),
         XLOOKUP(BYROW(UNIQUE(X), CONCAT),
                 BYROW(DROP(A,,-1), CONCAT),
                 TAKE(A,,-1),
                 "未打卡",
                 ,-1))

🔍 解析步骤：
X = A2:C627：定义主数据区域
D = D2:D627：定义时间列
GROUPBY(X, D, MAX,,0,, ...)：
按时间分组
使用 MAX 聚合函数返回每组中最大值（即最晚打卡）
筛选条件：D < 1/24*13 且 D > 1/24*9
UNIQUE(X)：获取所有员工每天的唯一组合
XLOOKUP(...)：反向查找匹配结果

✅ 优点：

展示了 GROUPBY 在复杂条件下的应用

可处理多维度聚合需求

❌ 缺点：

过度复杂化问题，增加了理解成本

不符合“简单有效”的原则

📌 适用场景：

仅作拓展学习参考，实际项目中不推荐使用

📌 核心知识点总结

编号	知识点	说明
1	上午下班打卡是多条件筛选问题	时间范围：12:00 ~ 13:00；需排除早班打卡干扰
2	时间比较建议使用 --"12:00" 而非 0.5	避免浮点误差，提高准确性
3	XLOOKUP 的参数 -1 表示反向搜索	从最后一项开始查找，确保优先匹配最晚打卡
4	多条件筛选用 * 表示逻辑“且”	如 (D>=12:00)*(D<=13:00)
5	GROUPBY 支持筛选参数	可直接在聚合时过滤数据，本质与 FILTER 类似
6	CONCAT 是构建复合键的有效手段	将姓名+日期合并为唯一标识，便于匹配
7	UNIQUE 可用于去重	确保每条记录唯一，避免重复

🎯 实际应用建议

优先推荐使用方法一（FILTER + XLOOKUP + CONCAT）

✅ 代码最短，逻辑最清晰

✅ 性能最优，适合大规模数据

✅ 符合现代 Excel 开发规范

若环境不支持 XLOOKUP，可用方法二（FILTER + UNIQUE）

✅ 兼容性强，适用于旧版软件

✅ 逻辑直观，适合初学者

方法三仅作拓展学习参考

✅ 展示了高级函数的组合能力

✅ 不推荐用于生产环境

注意约束条件：

✅ 时间范围：12:00 ≤ 打卡时间 ≤ 13:00

✅ 多次打卡 → 取最早一次（即最接近12:00的）

✅ 若无打卡 → 返回“未打卡”

✅ 输出应包含：序号、姓名、打卡日期、上午下班打卡

✅ 数据需按日期排序（方法二要求）

🧩 示例效果说明

在 J2 单元格输入上述任一公式后，结果如下：

成功提取出每位员工每天中午12:00至13:00之间的最早打卡时间，并对无打卡的日期标记为“未打卡”，为后续分析提供完整依据。

📚 小结口诀

“中午打卡有约束，
12到13才算准；
多次打卡取最早，
反向搜索防误判；
时间用--更稳妥，
CONCAT连键快又稳。”

此操作是考勤数据分析流程中的关键一步。掌握这些技巧，不仅能高效提取上午下班打卡时间，还能迁移到其他业务场景（如下午上班、晚班下班等），大幅提升数据分析能力。

47-2 上午打卡分析- —— 根据上下班打卡时间判断考勤状态

在考勤系统中，仅记录打卡时间是不够的，还需根据业务规则对员工的出勤行为进行自动分类。本题目标是：

基于已提取的上午上班打卡时间（I列）和上午下班打卡时间（J列），判断每位员工每日的上午考勤状态。

🎯 判定规则如下：

字段	正常范围	异常情况
上午上班打卡	≤ 08:00	> 08:00 → 迟到；值为“未打卡” → 上午上班未打卡
上午下班打卡	≥ 12:00	< 12:00 → 早退；值为“未打卡” → 上午下班未打卡

最终输出两列结果：

第一列：上午上班状态

第二列：上午下班状态

本题通过两种不同方法实现该多条件判断，并对比其优缺点，提炼核心思路与适用场景。

🔧 公式解析与对比

✅ 方法一：使用 IFS + HSTACK + IFNA 实现分步判断（推荐）

excel编辑

K2 = LET(I, I2:.I2000, J, J2:.J2000,
         IFNA(HSTACK(
             IFS(I="未打卡", "上午上班未打卡",
                 I > 1/24*8, "上午迟到"),
             IFS(J="未打卡", "上午下班未打卡",
                 J < 1/24*12, "上午早退")
         ), "打卡正常"))

🔍 解析步骤：
I = I2:.I2000, J = J2:.J2000：定义上午上班和下班打卡时间列
IFS(I="未打卡", "上午上班未打卡", I > 1/24*8, "上午迟到")：
若上班打卡为“未打卡” → 返回“上午上班未打卡”
否则若时间 > 08:00 → 返回“上午迟到”
否则默认为“打卡正常”（由外层 IFNA 处理）
类似地处理下班打卡：
若为“未打卡” → “上午下班未打卡”
若时间 < 12:00 → “上午早退”
HSTACK(...)：将两个结果横向合并
IFNA(..., "打卡正常")：若两个字段都无异常，则返回“打卡正常”

✅ 优点：

使用 IFS 函数清晰表达多条件逻辑

结构化程度高，可读性强

支持动态数组，性能良好

❌ 缺点：

依赖 IFS 和 HSTACK 函数（WPS/Excel 365 支持）

对初学者有一定门槛

📌 适用场景：

需要明确区分多种考勤状态、且数据质量较高的情况

✅ 方法二：使用 IFERROR + IF 实现嵌套判断（兼容性更强）

excel编辑

K2 = LET(A, I2:.I2000, B, J2:.J2000,
         HSTACK(
             IFERROR(IF(--A <= 1/24*8, "打卡正常", "上午迟到"), "上午上班未打卡"),
             IFERROR(IF(--B >= 1/24*12, "打卡正常", "上午早退"), "上午下班未打卡")
         ))

🔍 解析步骤：
A = I2:.I2000, B = J2:.J2000：定义上下班打卡时间
--A <= 1/24*8：将文本时间转为数值，判断是否 ≤ 08:00
是 → “打卡正常”
否 → “上午迟到”
IFERROR(..., "上午上班未打卡")：若发生错误（如“未打卡”无法转换）→ 返回“上午上班未打卡”
类似处理下班打卡：
--B >= 1/24*12：判断是否 ≥ 12:00
否 → “上午早退”
错误 → “上午下班未打卡”
HSTACK(...)：合并结果

✅ 优点：

不依赖 IFS，兼容性更强

利用 IFERROR 自动处理文本值，避免错误

逻辑清晰，易于调试

❌ 缺点：

公式稍长，重复代码较多

性能略低于 IFS

📌 适用场景：

数据包含文本值（如“未打卡”）、环境不支持 IFS 的情况

📌 核心知识点总结

编号	知识点	说明
1	考勤判定是多条件组合问题	需同时考虑上班、下班、时间、状态等多重因素
2	IFS 是现代条件判断的最佳选择	替代嵌套 IF，语法更简洁，逻辑更清晰
3	HSTACK 可横向合并多个结果	实现“并行判断”的输出格式
4	IFNA 或 IFERROR 用于处理空值/错误	确保输出不会出现 #N/A 或 #VALUE!
5	时间比较建议使用 --"08:00" 而非 0.333	避免浮点误差，提高准确性
6	LET 提升公式可读性和性能	定义变量避免重复引用，尤其适用于大范围计算

🎯 实际应用建议

优先推荐使用方法一（IFS + HSTACK + IFNA）

✅ 代码最短，逻辑最清晰

✅ 性能最优，适合大规模数据

✅ 符合现代 Excel 开发规范

若环境不支持 IFS，可用方法二（IFERROR + IF）

✅ 兼容性强，适用于旧版软件

✅ 逻辑直观，适合初学者

注意约束条件：

✅ 上午上班打卡 > 08:00 → “上午迟到”

✅ 上午下班打卡 < 12:00 → “上午早退”

✅ 任一打卡为“未打卡” → 显示对应状态

✅ 其他情况 → “打卡正常”

✅ 输出应为两列：上午上班判断、上午下班判断

WPS表格

2025-11-23 04:06:15 广东省

4 +1