将一维考勤表向二维透视! wps函数pivotby是一个超级透视表函数


    网友求助SOS：如何将表①布局的考勤记录转换成表②布局的考勤记录。
    对于财务部门或人力资源部门进行考勤统计工作，大多数情况下都是考勤机导出表②表格形式的记录，然后想方设法变成表①那样，他这个却是反过来的。为统计工作造成了一定的难度。
    
    由于原表格数据量太大，我们简化数据源，用几组简单的数据还原真实的职场办公场景即可：
    左侧为简单的一维格式的考勤数据，一列姓名，一列时间点，每行为一组一一对应的数据。我们要转换为右下侧二维格式的考勤数据，列标题为时间点，行标题为姓名，中间值区域为对应的个数不等的时间点记录。
    
  • A列（A2:A8）：员工姓名（张三、李四）
  • B列（B2:B8）：Excel日期时间序列号，格式如45778.32847。将B列设置单元格格式为数值后可查看。

    日期时间序列号由整数部分（日期）和小数部分（时间）组成，例如：
    
  • 45778→日期：2025-5-1
  • 0.32847→时间：7:53

    
    我们今天讲的这个方法，不添加任何辅助列，只在一个单元格输入一组嵌套函数公式。这个公式通过Excel的日期时间序列号的特性和数组运算实现了考勤数据的二维透视。
    PIVOTBY函数类似于数据透视表功能。
    函数总体结构：
    
    =PIVOTBY(
    A2:A8, // 行分类字段（员工姓名）
    INT(B2:B8), // 列分类字段（日期部分）
    MOD(B2:B8,1),// 待聚合值（时间部分）
    LAMBDA(x, TEXTJOIN(CHAR(10),,TEXT(x,"h:mm"))), // 聚合函数
    ,0,,0// 参数占位（不显示总计行/列）
    )
    我们一步一步输入公式，理解作用过程。
    输入公式：
    =PIVOTBY(A2:A8,INT(B2:B8),,)
    行分组
    根据A2:A8的姓名进行纵向分组，形成张三、李四两行。
    列分组
    通过INT(B2:B8)提取日期(整数部分)，将不同日期的考勤横向分组。
    例如：45778对应2025-5-1，45779对应2025-5-2
    实际效果中的日期，需要设置单元格格式为“m"月"d"日";@”才能正常显示。
    
    完善公式参数：
    =PIVOTBY(A2:A8,INT(B2:B8),MOD(B2:B8,1),)
    待聚合值处理
    MOD(B2:B8,1)提取时间(小数部分)。
    由于目前还没有设置第4参数，第3参数的待聚合值要靠4参数的聚合函数实现，所以暂时显示错误值。
    
    完善公式参数：
    =PIVOTBY(A2:A8,INT(B2:B8),MOD(B2:B8,1),LAMBDA(x,TEXTJOIN(CHAR(10),,TEXT(x,"h:mm"))))
    用LAMBDA函数设置第4参数，聚合函数。
    LAMBDA函数定义第3参数的待聚合值为x
    使用TEXT(x,"h:mm")：
    将小数转换为标准时间格式（如0.328472→7:54）
    使用TEXTJOIN(CHAR(10),...)：
    用换行符合并同一单元格内的多个时间。
    动态数组工作原理：
    公式自动检测行/列维度组合，例如：
    张三在2025-5-1日有两次打卡（7:53和17:15）
    公式会自动创建二维矩阵，交叉位置合并对应时间。
    实际效果中的换行符（CHAR(10））需要单元格启用自动换行功能后才能正常显示。
    实际效果中的时间，需要设置单元格格式为“h:mm;@”才能正常显示。
    
    继续完善公式：
    =PIVOTBY(A2:A8,INT(B2:B8),MOD(B2:B8,1),LAMBDA(x,TEXTJOIN(CHAR(10),,TEXT(x,"h:mm"))),,0)
    设置第6参数为“0”，代表不显示“总计行”。
    
    继续完善公式：
    =PIVOTBY(A2:A8,INT(B2:B8),MOD(B2:B8,1),LAMBDA(x,TEXTJOIN(CHAR(10),,TEXT(x,"h:mm"))),,0,,0)
    设置第8参数为“0”，代表不显示“总计列”。
    
    
    推荐阅读：
    
  • 不要再用透视表! wps的超级汇总函数PivotBy更好用
  • wps函数PIVOTBY参数居然有10个! 高效数据透视分析指南
相关文章！