多工单重叠场景下系统停机时长计算公式优化求助

需求说明 #

需要编写Excel公式，基于工单台账计算同一系统的实际停机时长：当系统存在多个并行工单时，不能累加各工单的停机时长，而是取该系统所有工单中最早的开始日期到最晚的结束日期的差值作为实际停机时长。

示例场景 #

• 系统1工单1：2025/1/16开启，2025/1/20关闭
• 系统1工单2：2025/1/17开启，2025/1/20关闭
• 实际停机时长应为4天，但现有公式累加两个工单时长（4天+3天）得出7天的错误结果

现有错误公式 #

=IF(AU22="","",IF(COUNTIFS('Main Log'!D2:D2000, Dashboard!AU22, 'Main Log'!B2:B2000, "Open")>1,SUMIFS('Main Log'!K2:K2000, 'Main Log'!D2:D2000, Dashboard!AU22,'Main Log'!C2:C2000, MIN('Main Log'!C2:C2000),'Main Log'!C2:C2000, MAX('Main Log'!C2:C2000)),SUMIFS('Main Log'!K2:K2000,'Main Log'!D2:D2000, Dashboard!AU22))

样本数据 #

样本错误说明 #

针对系统1，实际停机时长应为2025/1/23到2025/2/17的差值（25天），但现有公式累加两个工单的解决时长（20+20）得出40天的错误结果。

解决方案公式 #

方案1：Excel 365/2021及以上版本（支持MAXIFS/MINIFS） #

=IF(AU22="","",MAXIFS('Main Log'!E:E,'Main Log'!C:C,AU22)-MINIFS('Main Log'!D:D,'Main Log'!C:C,AU22))

• 逻辑：MAXIFS提取当前系统所有工单的最晚解决日期，MINIFS提取当前系统所有工单的最早开始日期，两者差值即为合并重叠时间后的实际停机时长。

方案2：旧版Excel（无MAXIFS/MINIFS，需数组公式） #

输入公式后按Ctrl+Shift+Enter完成数组公式录入：

=IF(AU22="","",MAX(IF('Main Log'!C:C=AU22,'Main Log'!E:E))-MIN(IF('Main Log'!C:C=AU22,'Main Log'!D:D)))

• 逻辑：通过数组条件筛选出当前系统的所有工单日期，分别取最大结束日期和最小开始日期，计算差值。

错误原因分析 #

原有公式的核心逻辑错误：试图通过累加工单时长计算停机时间，未考虑并行工单的时间重叠；同时SUMIFS的条件设置（MIN('Main Log'!C2:C2000)和MAX('Main Log'!C2:C2000)）无法正确筛选目标工单，最终导致错误的累加结果。

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