25-46周 考勤分析专题 -2
Lv.2潜力创作者
46-3 有效打卡记录转一维 - 题目
在考勤分析中，原始数据常以“员工 × 日期 × 多次打卡”的二维形式存在。为了便于后续统计（如按时间排序、计算工时、识别异常等），需将这些数据从横向布局转换为纵向的一维长表，即：
本题通过五种不同方法实现该目标，并对比其优缺点，提炼核心思路与适用场景。
🔧 公式解析与对比
✅ 方法一：使用 IF + TOCOL + HSTACK 实现条件拼接（推荐）
excel编辑
A2 = LET(C,有打卡记录!B2#, S,有打卡记录!C2#, J,有打卡记录!F2:.K5000,
F, LAMBDA(F, TOCOL(IFS(J<>"", F), 3)),
HSTACK(F(C), F(S), F(J)))定义变量：
C: 姓名列
S: 日期列
J: 考勤时间列（F:K）
F = LAMBDA(F, TOCOL(IFS(J<>"", F), 3))：
对每列 F，用 IFS(J<>"" , F) 判断是否非空
若非空 → 返回对应值；否则返回 FALSE
TOCOL(..., 3)：将结果转为一维列，参数 3 表示忽略错误/逻辑值
HSTACK(...)：将三列合并为一个三列表格
✅ 优点：
代码简洁，逻辑清晰
使用 LAMBDA 提升复用性，避免重复写法
性能良好，适合中等规模数据
❌ 缺点：
需要对每一列单独处理
不支持动态列宽扩展
📌 适用场景：
✅ 方法二：逐列判断 + TOCOL + HSTACK（基础通用法）
excel编辑
A2 = LET(A,有打卡记录!F2:.M5000, B,有打卡记录!B2:.B5000,
HSTACK(
TOCOL(IF(A<>"", B,\), 3),
TOCOL(IF(A<>"", 有打卡记录!C2:.C5000,\), 3),
TOCOL(IF(A<>"", A,\), 3)
))IF(A<>"", B, \)：若考勤时间不为空，则返回姓名；否则返回错误值 \
TOCOL(..., 3)：将结果转为一维列，自动过滤错误值
HSTACK(...)：合并三列
✅ 优点：
不依赖 LAMBDA，兼容性强
逻辑直观，易于理解
可灵活调整字段顺序
❌ 缺点：
每列需手动编写 TOCOL，重复代码较多
扩展性差（增加列需修改多个部分）
📌 适用场景：
✅ 方法三：使用 REDUCE + VSTACK + OFFSET 实现堆叠（迭代法）
excel编辑
A2 = DROP(REDUCE(0,有打卡记录!E2:.E5000,
LAMBDA(X,Y,
VSTACK(X,
LET(A, REPTARRAY(OFFSET(Y,,-3,,2), Y),
HSTACK(A,
TOCOL(TAKE(OFFSET(Y,,1,,5),,Y))
)
)
)
)),
1)REDUCE(0, E2:.E5000, ...)：对每一行的“打卡次数”进行迭代
OFFSET(Y,,-3,,2)：获取当前行的“姓名+日期”
REPTARRAY(..., Y)：根据打卡次数重复姓名和日期
TAKE(OFFSET(Y,,1,,5),,Y)：提取考勤时间列中前 Y 个值
TOCOL(...)：将时间数组转为一维
HSTACK(...)：合并姓名、日期、时间
VSTACK(...)：垂直堆叠所有结果
DROP(...,1)：去除首行空值
✅ 优点：
无需预设列数，可动态适应不同打卡次数
使用 OFFSET 确保引用一致性
支持复杂逻辑扩展
❌ 缺点：
公式较长，调试困难
性能较低（尤其大数据集）
对初学者不友好
📌 适用场景：
✅ 方法四：使用 GROUPBY + CHOOSECOLS + FILTER（聚合函数法）
excel编辑
A2 = LET(E, CHOOSECOLS(GROUPBY(有打卡记录!A2:.C5000, 有打卡记录!F2:.Z5000,
CHOOSE({1,2}, SINGLE, TOCOL(有打卡记录!F1:.Z1)), ,0),
2,3,5),
FILTER(E, TAKE(E,,-1)<>""))GROUPBY(...)：
按考勤时间列分组
使用 CHOOSE({1,2}, SINGLE, TOCOL(...)) 构造输出结构
第1列：单个值（姓名）
第2列：考勤时间（转为一维）
CHOOSECOLS(...,2,3,5)：提取所需列
FILTER(..., TAKE(E,,-1)<>"")：过滤掉最后一列为空的行
✅ 优点：
展示了 GROUPBY 的高级用法
可实现复杂的分组聚合逻辑
结构化程度高
❌ 缺点：
过度复杂化问题，增加了理解成本
不符合“简单有效”的原则
易出错且难调试
📌 适用场景：
✅ 方法五：使用 REDUCE + VSTACK + OFFSET + FILTER（终极堆叠法）
excel编辑
A2 = LET(E, DROP(REDUCE("",有打卡记录!A2:.A5000,
LAMBDA(X,Y,
VSTACK(X,
HSTACK(
REPTARRAY(OFFSET(Y,,,,3),5),
TOCOL(OFFSET(Y,,5,,5))
)
)
)),
1),
DROP(FILTER(E, TAKE(E,,-1)<>""),,1)REDUCE(...)：对每一行员工数据进行迭代
OFFSET(Y,,,,3)：获取姓名、日期、判定
REPTARRAY(...,5)：重复5次（假设最多5次打卡）
OFFSET(Y,,5,,5)：获取考勤时间列
TOCOL(...)：转为一维
HSTACK(...)：合并姓名、日期、时间
VSTACK(...)：堆叠所有结果
FILTER(...)：剔除空行
✅ 优点：
支持任意数量打卡记录
可扩展性强
❌ 缺点：
性能较差
逻辑复杂，易出错
📌 适用场景：
📌 核心知识点总结
编号 | 知识点 | 说明 |
1 | 二维转一维的核心是“重复”与“筛选” | 每个打卡时间独立成行，需重复姓名、日期并匹配时间 |
2 | IF 或 IFS + TOCOL 是经典组合 | 通过判断是否为空，配合 TOCOL 的错误屏蔽功能实现数据清洗 |
3 | TOCOL 的第3参数用于过滤错误/逻辑值 | 参数 3 自动忽略 FALSE、#N/A 等，保留有效值 |
4 | LAMBDA 可定义复用函数 | 减少重复代码，提升可读性和维护性 |
5 | OFFSET 保证引用一致性 | 在堆叠过程中确保 Y 值始终指向当前行 |
6 | REDUCE + VSTACK 是迭代利器 | 适用于无法使用 REPTARRAY 的环境 |
7 | FILTER 可清理空值行 | 最终输出应无空白行，提高数据质量 |
8 | GROUPBY 虽强大但易过度设计 | 适合复杂聚合，不适合简单转换任务 |
🎯 实际应用建议
优先推荐使用方法一（LAMBDA + TOCOL + HSTACK）
✅ 代码简洁，性能良好
✅ 易于理解和维护
✅ 符合现代 Excel 开发规范
若数据结构简单，可用方法二（逐列判断）
✅ 兼容性强，适合旧版软件
✅ 逻辑清晰，适合初学者
若打卡次数不规则，可用方法三（REDUCE + VSTACK）
✅ 动态适应不同次数
✅ 支持复杂逻辑
方法四和五仅作拓展学习参考
✅ 了解高级函数能力
✅ 不推荐用于生产环境
注意约束条件：
✅ 输出为三列：姓名、打卡日期、打卡记录
✅ 每条打卡时间独立成行
✅ 排除“没上班”记录
✅ 动态数组预留 30000 行 扩展空间
🧩 示例效果说明
成功将二维考勤表转换为一维长表，为后续分析提供标准化输入。
📚 小结口诀
重复是关键；
IF加TOCOL，
错误自动删；
LAMBDA来封装，
代码更简洁；
REDUCE堆叠行，
动态全搞定。”
此操作是数据标准化流程中的重要一步。掌握这些技巧，不仅能高效完成考勤数据转换，还能迁移到其他业务场景（如订单明细拆分、日志解析、销售记录展开等），大幅提升数据分析效率。
