Excel按假期类型统计对应前置3单元格时长需求（400行数据）

需求说明 #

我有一份包含约400行员工数据的假期统计工作表，需要按Holiday（假期）、Lieu（调休）、**Unpaid（无薪假）**三种类型分别汇总总时长。每行对应一位员工，需遍历每行，统计每个假期类型对应左侧前3个单元格（E/L/N列）的小时数，最终得到各类型的总时长。

示例数据 #

预期输出 #

实现方案 #

方法1：Excel函数直接计算 #

使用SUMIFS函数匹配假期类型与对应列数值，自动忽略空值/非数值（需将示例中的-替换为0）：

• Holiday总时长：=SUMIFS(A:A,D:D,"Holiday") + SUMIFS(F:F,I:I,"Holiday")
• Lieu总时长：=SUMIFS(B:B,D:D,"Lieu") + SUMIFS(G:G,I:I,"Lieu")
• Unpaid总时长：=SUMIFS(C:C,D:D,"Unpaid") + SUMIFS(H:H,I:I,"Unpaid")

方法2：VBA自动化遍历 #

如果需要适配复杂格式或批量处理，用VBA脚本遍历所有行累加时长：

Sub CalculateHolidayTotals()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim totalHoliday As Double, totalLieu As Double, totalUnpaid As Double
    
    Set ws = ThisWorkbook.ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row '以D列为准获取最后一行数据
    
    '从第2行开始遍历（跳过表头行）
    For i = 2 To lastRow
        '处理左侧分组（D列为Type）
        Select Case ws.Cells(i, "D").Value
            Case "Holiday": totalHoliday = totalHoliday + Val(ws.Cells(i, "A").Value)
            Case "Lieu": totalLieu = totalLieu + Val(ws.Cells(i, "B").Value)
            Case "Unpaid": totalUnpaid = totalUnpaid + Val(ws.Cells(i, "C").Value)
        End Select
        
        '处理右侧分组（I列为Type）
        Select Case ws.Cells(i, "I").Value
            Case "Holiday": totalHoliday = totalHoliday + Val(ws.Cells(i, "F").Value)
            Case "Lieu": totalLieu = totalLieu + Val(ws.Cells(i, "G").Value)
            Case "Unpaid": totalUnpaid = totalUnpaid + Val(ws.Cells(i, "H").Value)
        End Select
    Next i
    
    '将结果输出到K2:M2单元格
    ws.Range("K2").Value = totalHoliday
    ws.Range("L2").Value = totalLieu
    ws.Range("M2").Value = totalUnpaid
End Sub

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