45周 考勤整理专题 - 2

古哥计划
古哥计划

优秀创作者

45-3 按日期重复对应次数 - 题目

📌 知识点总结：生成与人员一一对应的日期序列（重复日期列）

在将二维打卡数据转换为一维长表后，下一步通常是为每位员工的每一行记录匹配对应的具体日期。这一步的关键是：

根据统计周期，生成一个与“序号-姓名”重复结构完全一致的日期序列。

以下公式展示了多种实现方式，并提炼出核心思路。

✅ 1. 核心思想：日期也需按天数重复展开

与“姓名+序号”的重复逻辑相同，日期列也需要按照统计天数进行重复。例如：

  • 统计周期为 10 月 1 日 至 31 日 → 共 31 天

  • 每位员工有 31 条记录 → 每个日期需重复 31 次（但顺序不同）

因此，“重复次数” = COLUMNS(C4:.ZZ4) 或 COUNTA(打卡时间记录!C4:AG4)，即日期列的数量。

✅ 2. 方法一：使用 REPTARRAY + SEQUENCE 构造日期序列

excel编辑

C2 = REPTARRAY(SEQUENCE(31,,--MID(打卡时间记录!B2,6,6)), MAX(A2:.A500))
🔍 解析：
MID(打卡时间记录!B2,6,6) 提取 “10-01” 到 “10-31” 的文本部分（如“10-01”）
-- 强制转为数值（减负）→ 得到数字 45931 （10月1日对应的数字）
SEQUENCE(31,,45931) 生成从31天，从10月1日开始（表示“10月1日”到“10月31日”）
REPTARRAY(..., MAX(A2:.A500))：将该日期序列重复 MAX(A2:.A500) 次（即总人数）

⚠️ 注意：

  • 此方法依赖于“10-01”等格式可被解析为数值（如 1001），存在局限性

  • 若格式不统一，可能失败

✅ 优点：

  • 代码简洁，适合简单场景

  • 利用 REPTARRAY 实现高效重复

✅ 3. 方法二：使用 DATE + TOCOL 标准化日期构造

C2 = TOCOL(REPTARRAY(
    SEQUENCE(COLUMNS(打卡时间记录!C4:.ZZ4),,
        DATE(2025, @REGEXP(打卡时间记录!B2,"\d+"),1)
    ),
    , MAX(A:.A)
),,1)
🔍 解析：
@REGEXP(打卡时间记录!B2,"\d+") 提取年份（如“2025”）和月份（如“10”）
DATE(2025, 10, 1) 构造起始日期：2025/10/1
SEQUENCE(...) 生成连续日期序列（从 10月1日 到 10月31日）
REPTARRAY(..., MAX(A:.A))：将日期序列重复 总人数 次
TOCOL(...,,1) 转换为单列数组（保留顺序）

✅ 优点：

  • 使用标准 DATE 函数，结果为真实日期类型

  • 支持任意年月组合，通用性强

  • 避免了文本拼接带来的歧义

❌ 缺点：

  • 公式较长，理解门槛略高

  • 对 REGEXP 函数兼容性要求较高（WPS/Excel 365 支持）

✅ 4. 方法三：结合 GROUPBY 与条件判断（高级聚合思路）

excel编辑

C2 = LET(
    A, GROUPBY(打卡时间记录!A5:.B10, 打卡时间记录!C5:.AH10, COUNTA, 0, 0),
    TOCOL(IF(DROP(A,,2)<>"", 
        SEQUENCE(, COLUMNS(打卡时间记录!C4:.ZZ4), 
            DATE(MID(打卡时间记录!B2,29,4), @REGEXP(打卡时间记录!B2,"\d+"),1)
        ), "")
    )
)
🔍 解析：
GROUPBY(...) 对每行员工的打卡记录进行分组统计
DROP(A,,2) 获取分组后的值（即是否有效）
IF(...) 判断是否存在有效记录
SEQUENCE(...) 生成起始日期并重复
最终通过 TOCOL 合并为一列

✅ 优点：

  • 展示了 GROUPBY 在非聚合场景下的灵活应用

  • 可用于复杂条件筛选（如仅对有打卡的员工生成日期）

❌ 缺点：

  • 过度复杂化问题，增加了计算负担

  • 不推荐作为常规方案，仅作拓展学习参考

🔧 核心知识点提炼

编号

知识点

说明

1

重复日期的本质是“按天数重复”

与姓名重复逻辑一致，只是内容变为日期

2

开始日期提取可用 MID + -- 实现“减负”

将文本“10-01”转为数值，再构造序列

3

推荐使用 DATE + SEQUENCE 构造标准日期

更准确、更通用，避免文本误判

4

REPTARRAY 是高效重复工具

适用于所有需要“复制某列多次”的场景

5

TOCOL 可将多维数组扁平化为一维

特别适合处理 REPTARRAY 输出的结果

6

GROUPBY 可用于替代传统循环逻辑

但在此场景下属于“过度设计”，建议慎用

🎯 实际应用建议

  1. 优先推荐使用方法二（DATE + SEQUENCE + REPTARRAY）

✅ 结果为标准日期类型

✅ 通用性强，适配各种年月

✅ 易于维护和调试

  1. 避免使用方法三中的 GROUPBY

虽然可行，但增加了不必要的复杂度，不符合“简单有效”的原则

  1. 确保日期范围与人员数量匹配

  • 日期列数 = 31（10月）

  • 人员总数 ≤ 1000（预留裁剪空间）

  • 最终输出应为：人数 × 天数 行数据

  1. 约束条件明确：

  • ✅ 日期必须来自打卡记录中的统计时间段（如 10-01 ~ 10-31）

  • ✅ 日期序列需与“序号-姓名”一一对应（每条记录都有唯一日期）

📚 小结口诀

“日期也要重复展，
起始日从B2取；
TEXT变DATE才标准，
REPTARRAY最省力；
GROUPBY虽能解，
复杂不如简。”

此步骤是构建标准化考勤数据库的关键环节。掌握这些技巧，不仅能提升数据处理效率，还能为后续的迟到早退识别、缺勤预警、工时统计等高级功能打下坚实基础。

45-4 打卡时间转换 - 题目

在考勤数据处理中，原始打卡记录常以“多个时间段用顿号分隔”的形式存储在一个单元格内（如：“07:44、12:11、13:24、20:01”）。为了便于后续分析（如计算上下班时间、识别迟到早退等），需要将这些合并的打卡时间拆分成独立的单元格。

本节介绍三种实现方式，并提炼核心思路与适用场景。

🔧 公式解析与对比

✅ 方法一：向量化处理 —— 先合并后拆分

IFNA(TEXTSPLIT(TEXTJOIN("#",,TOCOL(打卡时间记录!C5:.ZZ500)),"、","#"),"")
🔍 解析步骤：
TOCOL(打卡时间记录!C5:.ZZ500)：将整个区域转为一维列数组（忽略空值）
TEXTJOIN("#",, ...)：用 # 将所有打卡时间拼接成一行文本（如：“07:44#12:11#...”）
TEXTSPLIT(...,"、","#")：按“顿号”和“#”双重分隔符拆分
IFNA(...,"")：处理无数据情况，避免错误显示

✅ 优点：

  • 使用纯向量化函数，运算速度快

  • 代码简洁，适合大数据量场景

❌ 缺点：

  • TEXTJOIN 的最大字符限制为 32,767 字符，若某员工打卡记录过长可能截断

📌 适用场景：

数据总量较小、每行打卡记录不超过 30 条的情况

✅ 方法二：迭代堆叠 + 分列 —— 逐行处理

= IFNA(DROP(REDUCE(0, 打卡时间记录!C5:.AG100, LAMBDA(X,Y,VSTACK(X,TEXTSPLIT(Y,"、")))),1),"")
🔍 解析步骤：
REDUCE(0, 区域, LAMBDA(X,Y,...))：对每一行打卡记录进行迭代处理
TEXTSPLIT(Y,"、")：将当前单元格中的时间按“顿号”拆分为数组
VSTACK(X, ...)：将结果垂直堆叠到前序结果中
DROP(...,1)：去除首行空值
IFNA(...,"")：处理空值

✅ 优点：

  • 不受字符长度限制（逐行处理）

  • 支持任意数量的打卡记录

  • 结构清晰，易于调试

❌ 缺点：

  • 性能略低于向量化方法（尤其是大表时）

  • 需要较多嵌套函数

📌 适用场景：

数据量较大、存在超长打卡记录、要求高兼容性的场景

✅ 方法三：正则表达式分词 —— 更精准的文本提取

IFNA(DROP(REDUCE(0, 打卡时间记录!C5:.AG100, LAMBDA(X,Y,VSTACK(X,REGEXP(Y,"[^、]+")))),1),"")
🔍 解析步骤：
REGEXP(Y,"[^、]+")：使用正则表达式匹配非顿号的连续字符
[^、]：表示不是“顿号”的任何字符
+：表示一个或多个连续字符
示例输入：“07:44、12:11” → 输出：{"07:44", "12:11"}
VSTACK(...)：堆叠所有结果
DROP(...,1)：移除首行空值
IFNA(...,"")：处理空值

✅ 优点：

  • 正则表达式更灵活，可应对复杂格式（如包含空格、换行等）

  • 可自定义规则，适应不同导出格式

  • 比 TEXTSPLIT 更强大，适合非标准分隔符

❌ 缺点：

  • 对 REGEXP 函数兼容性要求高（WPS/Excel 365 支持）

  • 学习成本稍高

📌 适用场景：

打卡记录格式不统一、存在异常字符或混合分隔符的情况

📌 核心知识点总结

编号

知识点

说明

1

向量化思维：先合并再拆分

利用 TEXTJOIN 和 TEXTSPLIT 实现高效批量处理，速度最快，但受限于字符上限（32K）

2

迭代思维：逐行堆叠处理

使用 REDUCE + LAMBDA + VSTACK 构建动态数组，不受字符限制，稳定性强

3

正则表达式：精准文本分割

REGEXP("[^、]+") 能准确提取以顿号分隔的时间段，灵活性高，适合复杂场景

4

TEXTSPLIT 是基础工具

支持多种分隔符，是日常拆分任务首选

5

REDUCE 是迭代利器

在无法使用 REPTARRAY 或 MAKEARRAY 时，是构建动态数组的核心函数

6

IFNA 保证输出整洁

避免出现 #N/A 错误，提升用户体验

🎯 实际应用建议

  1. 优先推荐使用方法二（REDUCE + VSTACK + TEXTSPLIT）

✅ 平衡性能与稳定性

✅ 通用性强，适配大多数场景

  1. 若数据量小且格式规范，可用方法一

✅ 速度快，代码短

✅ 适合快速原型开发

  1. 若存在异常格式或特殊需求，使用方法三

✅ 正则表达式提供更强控制力

✅ 可扩展至其他分隔符（如逗号、竖线、空格等）

  1. 注意约束条件：

  • ✅ 每个打卡时间应被正确拆分（如“07:44”、“12:11”等）

  • ✅ “没上班”等文本也应保留并作为单独项

  • ✅ 输出列数应足够（建议预留 ≥10 列）

📚 小结口诀

“合并再拆快又灵，
字符超限有风险；
逐行堆叠稳如山，
正则分词最精细；
三种方法各有道，
按需选用才称心。”

此操作是考勤数据标准化流程中的关键一步。掌握这三种方法，不仅能解决当前问题，还能迁移到其他类似场景（如订单明细拆分、日志解析、多标签处理等），大幅提升数据处理能力。

WPS表格 WPS表格
广东省
浏览 18
收藏
点赞
分享
+1
+1
全部评论