Excel中按姓氏统计员工操作时长的技术求助

方法一：Excel公式实现（适合小数据量） #

步骤1：计算同员工操作的时间间隔 #

假设员工审计表中，D列为姓氏、F列为操作创建时间（需确保是日期时间格式），插入辅助列（如G列）计算当前操作与上一条同姓氏操作的时间差：

• 在G2单元格输入公式：

  =IF(D2=D1,F2-F1,"")
  

  逻辑：若当前行姓氏与上一行一致，计算两次操作的时间差，否则留空。

步骤2：按姓氏汇总总时长 #

用SUMIF函数对每个姓氏的时间差求和：

• 在空白区域（如I列输入姓氏，J列计算结果），J2单元格输入：

  =SUMIF(D:D,I2,G:G)*24  // 转换为小时，*1440可转换为分钟
  

如果需要筛选特定操作类型（如仅统计“点击”操作），改用SUMIFS：

=SUMIFS(G:G,D:D,I2,B:B,"点击")*24

方法二：Power Query实现（适合大数据量） #

1. 选中数据区域，点击「数据」→「从表格/区域」导入Power Query编辑器。
2. 点击「转换」→「分组依据」，设置参数：
   • 分组依据：姓氏
   • 新列名：总操作时长
   • 操作：自定义
   • 自定义公式：

     List.Sum(List.Transform(List.Positions([操作创建时间]), (i)=> if i>0 then [操作创建时间]{i}-[操作创建时间]{i-1} else 0))
     

3. 点击「确定」后，系统自动计算每个姓氏的总时长，最后点击「关闭并上载」导出结果到Excel。

关键注意事项 #

• 必须确保操作创建时间列是日期时间格式，否则时间差计算会失效，可通过「开始」→「数字格式」调整。
• 如果操作时长定义为“员工从第一条操作到最后一条操作的总时段”，可直接用MAX(时间列)-MIN(时间列)替代间隔求和逻辑。

内容的提问来源于stack exchange，提问作者Miles