Excel如何自动计算每行成对列的工时总和？

原公式逻辑提炼 #

你当前每组工时的计算逻辑可拆解为：

1. 计算跨天原始时长：MOD(结束时间-开始时间,1)*24（将跨天时长转为小时）
2. 跨天扣除：若结束早于开始（跨天），扣除22-6=16小时
3. 工作时段修正：通过MEDIAN函数仅统计6:00-22:00区间内的有效工时

自动化解决方案 #

方案1：兼容旧版Excel（SUMPRODUCT数组公式）

无需手动枚举每组，公式自动识别该行所有两列一组的时间对并累加结果：

=SUMPRODUCT(
    MOD(OFFSET(C4,0,1,1,INT((COLUMNS(C4:XFD4))/2))-OFFSET(C4,0,0,1,INT((COLUMNS(C4:XFD4))/2)),1)*24
    - (OFFSET(C4,0,1,1,INT((COLUMNS(C4:XFD4))/2))<OFFSET(C4,0,0,1,INT((COLUMNS(C4:XFD4))/2)))*(22-6)
    - MEDIAN(OFFSET(C4,0,1,1,INT((COLUMNS(C4:XFD4))/2))*24,6,22)
    + MEDIAN(OFFSET(C4,0,0,1,INT((COLUMNS(C4:XFD4))/2))*24,6,22)
)

• OFFSET(C4,0,0,...)：提取所有开始时间列（C、E、G...）
• OFFSET(C4,0,1,...)：提取所有结束时间列（D、F、H...）
• INT((COLUMNS(C4:XFD4))/2)：自动计算该行的时间组数（XFD4为Excel最后一列，确保覆盖所有可能的组）

方案2：适用于Excel 365/2021（BYROW+LAMBDA）

用自定义逻辑批量处理每组，可读性更强：

=SUM(
    BYROW(
        CHOOSECOLS(C4:XFD4,SEQUENCE(INT(COLUMNS(C4:XFD4)/2)*2,,1,2)),
        LAMBDA(x,
            MOD(INDEX(x,2)-INDEX(x,1),1)*24
            - (INDEX(x,2)<INDEX(x,1))*(22-6)
            - MEDIAN(INDEX(x,2)*24,6,22)
            + MEDIAN(INDEX(x,1)*24,6,22)
        )
    )
)

• CHOOSECOLS(...)：按顺序提取每两列一组的时间区域
• BYROW+LAMBDA：对每组时间应用原计算逻辑，最后用SUM累加结果

优化版公式（简化逻辑）

原公式的逻辑可简化为直接统计6:00-22:00区间的有效工时，包括跨天情况，结果与原公式一致但更直观：

=SUM(
    BYROW(
        CHOOSECOLS(C4:XFD4,SEQUENCE(INT(COLUMNS(C4:XFD4)/2)*2,,1,2)),
        LAMBDA(x,
            MAX(0,MIN(INDEX(x,2)*24,22)-MAX(INDEX(x,1)*24,6))
            + IF(INDEX(x,2)<INDEX(x,1),MAX(0,MIN(22,INDEX(x,2)*24+24)-MAX(6,INDEX(x,1)*24+24)),0)
        )
    )
)

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