求Excel条件求和公式：计算设备指定时段内的开机总时长

场景说明 #

现有Excel表格包含两列数据：

• A列：带日期与时间的时间戳
• B列：设备状态标识（1代表开机，0代表关机）

需要编写公式，统计**指定起始时间（存于D1）到结束时间（存于E1）**内的设备总开机时长。

示例数据 #

按全时段统计的话，总开机时长为11:10:00（对应示例计算结果）。

计算公式 #

假设数据范围为A2:A7（时间戳）、B2:B7（状态），起始时间D1，结束时间E1，使用以下公式：

=SUMPRODUCT(
    (B2:B7=1)*
    (A2:A7>=D1)*
    (OFFSET(A2:A7,1,0)<=E1)*
    (OFFSET(A2:A7,1,0)-A2:A7)
) +
IF(AND(B7=1,A7<=E1),E1-A7,0) +
IF(AND(B2=1,A2>=D1),A2-D1,0)

公式解释 #

1. SUMPRODUCT部分：统计完全落在指定区间内的完整开机时段时长。当当前行状态为开机、当前时间戳不早于起始时间、下一行时间戳不晚于结束时间时，计算两个时间戳的差值并累加。
2. 第一个IF部分：处理表格最后一条记录为开机状态且时间戳在结束时间之前的情况，计算从该时间戳到结束时间的时长。
3. 第二个IF部分：处理表格第一条记录为开机状态且时间戳在起始时间之后的情况，计算从起始时间到该时间戳的时长。

注意事项 #

• 计算结果的单元格需设置为自定义格式[h]:mm:ss，确保超过24小时的时长能正确显示（默认格式会自动取模24，导致时长统计错误）。
• 若使用旧版Excel（2019及以前），输入公式后需按Ctrl+Shift+Enter触发数组计算；新版Excel直接回车即可。

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