基于班次起止时间分配考勤表工时的Excel方案问询

核心思路 #

放弃繁琐的嵌套IF，采用日期分段拆解+辅助列的方式，结合PowerQuery/PowerPivot实现可维护、易刷新的统计流程，比纯函数方案更清晰。

步骤实现 #

1. 基础辅助列搭建 #

先给原始班次表（含开始时间、结束时间列）添加以下辅助列：

• 日期序列：生成班次覆盖的所有日期，公式：=SEQUENCE(INT(B2-A2)+1,1,A2)（A2=开始时间，B2=结束时间），下拉填充后展开成每行对应一个日期
• 当日有效开始时间：取班次开始时间与当日0点的较大值：=MAX(A2, DATEVALUE(C2))（C2=辅助列日期）
• 当日有效结束时间：取班次结束时间与当日23:59:59的较小值：=MIN(B2, DATEVALUE(C2)+TIME(23,59,59))
• 是否公假日：匹配自定义公假日列表判断：=NOT(ISNUMBER(MATCH(C2, $P$2:$P$20, 0)))（$P$2:$P$20是公假日单元格范围）
• 星期几：=WEEKDAY(C2,2)（1=周一，5=周五，6=周六，7=周日）

2. 各时段时长计算（分列实现） #

工作日白班（07:30-16:00，非公假日、周一至周五）

=IF(AND(D2=FALSE, E2<=5), MAX(0, MIN(F2, DATEVALUE(C2)+TIME(16,0,0)) - MAX(G2, DATEVALUE(C2)+TIME(7,30,0))), 0)

• 参数说明：D2=是否公假日，E2=星期几，F2=当日有效结束时间，G2=当日有效开始时间

工作日夜班（16:00-次日07:30，非公假日、周一至周四；周五仅到24:00）

=IF(AND(D2=FALSE, E2<=4), 
    MAX(0, MIN(F2, DATEVALUE(C2)+TIME(23,59,59)) - MAX(G2, DATEVALUE(C2)+TIME(16,0,0))) + 
    MAX(0, MIN(DATEVALUE(C2)+TIME(7,30,0), B2) - DATEVALUE(C2)+TIME(0,0,0)),
    IF(AND(D2=FALSE, E2=5), 
        MAX(0, MIN(F2, DATEVALUE(C2)+TIME(23,59,59)) - MAX(G2, DATEVALUE(C2)+TIME(16,0,0))), 
        0
    )
)

• 周一至周四的夜班跨次日，分当日16:00-24:00和次日0:00-7:30两部分计算；周五仅统计当日16:00-24:00

周六时段（当日00:00-24:00）

=IF(E2=6, F2-G2, 0)

周日及公假日时段（当日00:00-次日07:30）

=IF(OR(D2=TRUE, E2=7), 
    MAX(0, MIN(F2, DATEVALUE(C2)+TIME(23,59,59)) - G2) + 
    MAX(0, MIN(DATEVALUE(C2)+TIME(7,30,0), B2) - DATEVALUE(C2)+TIME(0,0,0)), 
    0
)

3. 总工时校验 #

添加校验列确保各时段求和与实际总工时一致：

=ROUND(SUM(H2:K2) - (B2-A2), 6)

• H2-K2为四个时段的时长列，结果应为0（秒级误差可忽略）

4. PowerQuery批量处理优化 #

如果班次数据量大，用PowerQuery自动完成日期拆分与计算：

1. 导入原始班次表到PowerQuery
2. 添加自定义列生成日期序列：List.Dates([开始时间], Duration.Days([结束时间]-[开始时间])+1, #duration(1,0,0,0))
3. 展开日期列表，生成每日数据行
4. 添加自定义列计算当日有效开始/结束时间，再按上述规则编写M语言计算各时段时长
5. 加载回Excel，插入数据透视表，按日期、时段快速汇总

5. PowerPivot实现专项校验 #

针对加班专项校验需求，将数据导入PowerPivot后创建度量值：

• 月度夜班总时长：

  月度夜班总时长 = CALCULATE(SUM('班次表'[工作日夜班时长]), DATESBETWEEN('班次表'[日期], STARTOFMONTH('班次表'[日期]), ENDOFMONTH('班次表'[日期])))
  

• 公假日加班班次数量：

  公假日加班班次 = CALCULATE(DISTINCTCOUNT('班次表'[班次ID]), '班次表'[周日及公假日时长] > 0)
  

推荐表格结构 #

更新起止时间后，刷新PowerQuery或拖动公式，再刷新数据透视表即可获取最新统计结果。

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