在Excel或Power Query中按班次起止时间生成小时时段分组

需求是根据「开始时间」和「结束时间」列，把工作时长拆分到指定的小时时段桶，计算每个时段内的工作分钟数，重点解决跨夜班、跨多时段班次的计算问题，替代容易出错的嵌套IF语句。

示例数据 #

Power Query解决方案 #

1. 创建通用计算重叠分钟数的自定义函数

在Power Query中新建空白查询，重命名为CalculateOverlapMins，将以下M代码粘贴到高级编辑器：

(startTime as time, endTime as time, shiftStart as time, shiftEnd as time) as number =>
let
    // 处理跨天：结束时间早于开始时间时，视为结束时间+24小时
    adjustedEndTime = if endTime < startTime then #time(endTime[Hour]+24, endTime[Minute], endTime[Second]) else endTime,
    adjustedStartTime = #time(startTime[Hour], startTime[Minute], startTime[Second]),
    // 处理跨天时段（如23点-0点）
    adjustedShiftStart = if shiftEnd < shiftStart then #time(shiftStart[Hour], shiftStart[Minute], shiftStart[Second]) else #time(shiftStart[Hour], shiftStart[Minute], shiftStart[Second]),
    adjustedShiftEnd = if shiftEnd < shiftStart then #time(shiftEnd[Hour]+24, shiftEnd[Minute], shiftEnd[Second]) else #time(shiftEnd[Hour], shiftEnd[Minute], shiftEnd[Second]),
    // 计算重叠的时间范围
    overlapStart = List.Max({adjustedStartTime, adjustedShiftStart}),
    overlapEnd = List.Min({adjustedEndTime, adjustedShiftEnd}),
    // 输出重叠分钟数，无重叠则返回0
    overlapMins = if overlapStart < overlapEnd then Duration.TotalMinutes(overlapEnd - overlapStart) else 0
in
    overlapMins

这个函数通过时间转换统一处理跨天场景，再计算班次与目标时段的重叠时长，逻辑通用所有时段。

2. 为每个时段创建自定义列

回到你的数据查询，依次添加对应时段的自定义列：

• 6点-7点分钟数：

  = CalculateOverlapMins([开始时间], [结束时间], #time(6,0,0), #time(7,0,0))
  

• 7点-11点分钟数：

  = CalculateOverlapMins([开始时间], [结束时间], #time(7,0,0), #time(11,0,0))
  

• 11点-15点分钟数：

  = CalculateOverlapMins([开始时间], [结束时间], #time(11,0,0), #time(15,0,0))
  

• 15点-19点分钟数：

  = CalculateOverlapMins([开始时间], [结束时间], #time(15,0,0), #time(19,0,0))
  

• 19点-23点分钟数：

  = CalculateOverlapMins([开始时间], [结束时间], #time(19,0,0), #time(23,0,0))
  

• 23点-0点分钟数：

  = CalculateOverlapMins([开始时间], [结束时间], #time(23,0,0), #time(0,0,0))
  

• 0点-6点分钟数：

  = CalculateOverlapMins([开始时间], [结束时间], #time(0,0,0), #time(6,0,0))
  

3. 处理NA值

如果需要将无重叠的0显示为NA，可通过「替换值」功能把0替换为null（Power Query中null对应Excel的NA）。

优势对比 #

相比嵌套IF语句，该方案：

• 逻辑统一，所有时段复用同一函数，避免重复编写复杂判断
• 完美兼容跨天夜班、跨多时段的班次计算
• 代码易读易维护，修改时段范围仅需调整函数参数

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