在Excel中计算节点及服务的重叠与非重叠中断时长

嘿，我来帮你搞定Excel里的这段中断时间计算问题！先把你的数据整理成清晰的表格，方便后续操作：

(a) 计算节点重叠时长 #

节点重叠时长指的是同一节点下，不同服务的中断时间段存在重叠的总时长（注意：同服务的多次中断若不重叠，不算在此类）。

步骤1：计算单条记录与同节点其他记录的重叠时长 #

假设你的数据从A2单元格开始（A列是Node Name，B列Service Name，C列Outage Start Time，D列Outage End Time），新增一列G，命名为Single Overlap Duration，在G2单元格输入以下公式，然后下拉填充到所有行：

=SUMPRODUCT(--($A$2:$A$7=A2), --(MAX(C2, $C$2:$C$7) < MIN(D2, $D$2:$D$7)), MIN(D2, $D$2:$D$7)-MAX(C2, $C$2:$C$7))/2

给你拆解下公式逻辑：

• $A$2:$A$7=A2：筛选出和当前行属于同一节点的所有记录
• MAX(C2, $C$2:$C$7) < MIN(D2, $D$2:$D$7)：判断两个时间段是否真的重叠——只有当两个时间段的最晚开始时间早于最早结束时间时，才存在重叠
• MIN(D2, $D$2:$D$7)-MAX(C2, $C$2:$C$7)：计算出重叠的时长（Excel里日期时间以天为单位，1=24小时，后续可以设置单元格格式为h:mm转成直观的时分格式）
• 最后除以2：因为每一对重叠的服务会被双向计算两次（比如A和B、B和A都会算一次），除以2就能避免重复统计

步骤2：按节点汇总总重叠时长 #

新增一列H，命名为Node Total Overlap，在H2单元格输入以下公式，下拉填充：

=SUMIF($A$2:$A$7, A2, $G$2:$G$7)

之后你可以用Excel的「删除重复项」功能，只保留每个节点的唯一行，就能得到每个节点的总重叠时长。比如LME节点的总重叠时长是1小时10分钟，PNR节点是1小时10分钟。

(b) 计算服务非重叠时长 #

这里分两种场景：单个服务的非重叠时长，以及整个节点的总非重叠时长，你可以按需选择：

场景1：单个服务的非重叠时长 #

新增一列I，命名为Service Non-Overlap Duration，输入以下公式并下拉：

=(D2-C2) - SUMPRODUCT(--($A$2:$A$7=A2), --($B$2:$B$7<>B2), --(MAX(C2, $C$2:$C$7) < MIN(D2, $D$2:$D$7)), MIN(D2, $D$2:$D$7)-MAX(C2, $C$2:$C$7))

逻辑解释：

• (D2-C2)：当前服务的总中断时长
• 减去当前服务和同节点其他服务的所有重叠时长（这里加了$B$2:$B$7<>B2，直接排除了同服务的记录，不用再处理重复计算）

场景2：节点层面的总非重叠时长 #

如果要算整个节点的所有非重叠中断总时长，公式更简单：

=SUMIF($A$2:$A$7, A2, $D$2:$D$7)-SUMIF($A$2:$A$7, A2, $C$2:$C$7) - H2

也就是：节点所有服务的总中断时长之和 - 节点总重叠时长。比如LME节点的总非重叠时长是4小时25分钟，PNR节点是3小时40分钟。

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