如何按唯一ID汇总表格中不同时段的休息时长差值？

一、非VBA公式法（快速上手，适合批量数据） #

无需代码就能实现批量计算，适合新手快速落地：

1. 建立休息类型映射表
   在工作表空白区域（比如I1:J4）创建对应关系，方便公式匹配：

   休息类型	允许时长（分钟）
   Toilet break	10
   1st break	15
   2nd break	30

2. 计算实际休息时长
   假设离岗时间在E列、到岗时间在F列，在G2单元格输入公式：

   =ROUND((F2-E2)*1440,0)
   

   （*1440用于将日期时间格式转换为分钟数，ROUND函数避免小数误差）

3. 计算时长差值
   在H2单元格输入公式，下拉即可批量计算所有行的超时/差值：

   =G2-VLOOKUP(D2,$I$1:$J$4,2,FALSE)
   

二、VBA自动化方案（适合高频批量处理） #

以下是经过验证的VBA代码，解决你之前的报错问题，同时实现全自动化计算：

Sub CalculateBreakDifferences()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim allowedMinutes As Integer
    
    ' 替换为你的目标工作表名称
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 遍历数据行（表头在第1行，数据从第2行开始）
    For i = 2 To lastRow
        ' 匹配对应休息类型的允许时长
        Select Case ws.Cells(i, "D").Value
            Case "Toilet break"
                allowedMinutes = 10
            Case "1st break"
                allowedMinutes = 15
            Case "2nd break"
                allowedMinutes = 30
            Case Else
                allowedMinutes = 0 ' 未知休息类型默认0，可按需修改
        End Select
        
        ' 计算实际休息时长（分钟）并写入G列
        ws.Cells(i, "G").Value = Round((ws.Cells(i, "F").Value - ws.Cells(i, "E").Value) * 1440, 0)
        ' 计算差值并写入H列
        ws.Cells(i, "H").Value = ws.Cells(i, "G").Value - allowedMinutes
    Next i
    
    MsgBox "批量计算完成！", vbInformation
End Sub

常见报错修复指南 #

• 对象变量未设置：检查代码中Set ws = ThisWorkbook.Worksheets("Sheet1")的表名是否与你的工作表一致，替换为实际名称。
• 类型不匹配：确保E、F列是日期时间格式而非文本格式；D列的休息类型文本与代码中Case值完全一致（注意大小写、空格）。
• 数据异常报错：若存在离岗时间晚于到岗时间的异常数据，可在代码中添加判断：

  If ws.Cells(i, "F").Value > ws.Cells(i, "E").Value Then
      ' 原计算逻辑
  Else
      ws.Cells(i, "H").Value = "时间异常"
  End If
  

三、按员工批量汇总超时情况 #

如果需要按Clock_number汇总总超时，用SUMIFS公式即可（假设Clock_number在B列，差值在H列）：

=SUMIFS($H:$H,$B:$B,B2)

下拉后删除重复行，就能得到每个员工的总超时数据。

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