Excel拆分跨时段总时长为符合07时起算规则的每日累计（Index Match+SUM试错无效）

核心统计逻辑 #

自定义核算区间从当日07:00开始、次日07:00结束，计算时直接利用Excel日期时间的数值特性（1天=1，1小时=1/24天）做换算：

• 核算区间起始点：统计日期 + 7/24
• 核算区间终止点：统计日期 + 1 + 7/24
• 单条原始时段落在核算区间内的时长：取两个区间的重叠部分计算，公式为MAX(0, MIN(原始时段结束时间, 核算区间终止点) - MAX(原始时段开始时间, 核算区间起始点)) * 24，结果为十进制小时

数据结构约定 #

请根据你的实际表格列位置调整公式中的范围引用：

• 原始数据工作表：名为「原始数据」，有效范围为A2:D1000
  • A列：人员姓名
  • B列：时段开始时间（标准日期时间格式）
  • C列：时段结束时间（标准日期时间格式）
• 每日统计工作表：名为「每日统计」，有效范围为A2:C1000
  • A列：待统计人员姓名
  • B列：待统计核算日期（仅日期格式，如2021/1/1）
  • C列：输出的当日累计时长（自动限制上限24小时）

公式实现方案 #

Excel 365/2021 版本（动态数组简化版） #

直接在「每日统计」C2单元格输入以下公式，自动批量计算所有行的结果，无需下拉填充：

=BYROW(A2:B1000,LAMBDA(x,
LET(
name,INDEX(x,1),
stat_date,INDEX(x,2),
range_start,stat_date+7/24,
range_end,stat_date+1+7/24,
cross_arr,MAX(0,
MIN(原始数据!$C$2:$C$1000,range_end)-MAX(原始数据!$B$2:$B$1000,range_start)
)*24,
valid_cross,FILTER(cross_arr,原始数据!$A$2:$A$1000=name,0),
MIN(24,SUM(valid_cross))
)))

公式特性：

• 自动计算跨区间的时段拆分时长，无需手动拆分原始数据
• 无匹配时段时自动返回0，无报错
• 内置MIN(24,...)逻辑，单日累计不会超过24小时上限

Excel 2019及更早版本（兼容版） #

在「每日统计」C2单元格输入以下公式，选中C2下拉填充到所有统计行：

=MIN(24,SUMPRODUCT(
(原始数据!$A$2:$A$1000=$A2)*
((MIN(原始数据!$C$2:$C$1000,$B2+1+7/24)-MAX(原始数据!$B$2:$B$1000,$B2+7/24))*24>0)*
(MIN(原始数据!$C$2:$C$1000,$B2+1+7/24)-MAX(原始数据!$B$2:$B$1000,$B2+7/24))*24
))

兼容版注意事项：

• 原始数据范围不要整列引用（如不要写原始数据!A:A），限定实际数据行数，避免运算卡顿
• 交叉时长为负的记录（即无时间重叠的原始时段）会被自动过滤，不会被计入总和

之前Index Match+SUM方案的常见踩坑点 #

• 仅匹配「起止时间完全落在核算区间内」的记录，漏掉了跨区间的部分时长
• 未写入07:00的偏移逻辑，默认按自然日（00:00-24:00）计算统计区间
• 未做交叉时长的正负判断，无重叠的时段会产生负时长，拉低总和结果

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