在 DAX 中创建的 Excel 版本，它使用将二维时间数据展平到一维线上的想法：

=LET(
   start_times,A1:A3,
   end_times,B1:B3,
    timeline, SEQUENCE((MAX(end_times)-MIN(start_times))*1440, 1, MIN(start_times), 1/1440),
    overlaps, MMULT((timeline>=TRANSPOSE(start_times))*(timeline<=TRANSPOSE(end_times)), SEQUENCE(ROWS(start_times), 1, 1, 0)),
    unique_overlaps, SUM(--(overlaps > 0)),
    unique_overlaps &  " minutes"
)

建议的解决方案以一分钟的粒度工作（公式 24*60 中的 1440）。如果输入数据的粒度更高或更低，则可以进行调整。

首先，我假设工作表/数据按开始时间（升序）排序，然后按结束时间（升序）排序。这样工作就容易多了，因为一切都组织得井井有条。一旦得到结果，你总是可以按另一种方式排序。

我在这里使用的逻辑是假设我遇到的第一行是“完整的”……然后将后面的行与前面的行进行比较，将开始时间“重新定位”为上一行的结束时间……如果有重叠。

我在 D2 中为“实际开始”添加了此列： =MAX(A2,OFFSET(B2,-1,0,1,1),OFFSET(D2,-1,0,1,1))

这将选出可能的最晚开始日期…从列的原始开始日期，与前一行的结束日期进行比较…以及前一行的实际开始日期（如果出现多行重叠…需要考虑它 – 因为它将是集合第一行的结束日期..开始向前拉）

然后计算持续时间：在 E2 中：=IF(D2<B2,B2-D2,0)

IF 有助于排除出现的负持续时间，例如来自重叠日期范围的负持续时间：

可能有办法提高其效率——目前它对N 个日期对执行超过 2 N ² 次计算

=LET(startDates, A2:A4,
     endDates, B2:B4,

     linearS, TOCOL(startDates),
     linearE, TOCOL(endDates),
     seq, SEQUENCE(ROWS(linearS)),

     datesTable, HSTACK(linearS, linearE),
     descDates, SORTBY(datesTable, linearS, -1),
     ascDates, SORTBY(datesTable, linearE, 1),

     early, BYROW(linearS,
                  LAMBDA(_d, REDUCE(_d, seq, LAMBDA(_a,_v,
                                                    LET(_s, INDEX(descDates, _v, 1),
                                                        _e, INDEX(descDates, _v, 2),
                                                        IF(_a=MEDIAN(_s, _a, _e), _s, _a)
                                                    )
                                             )
                             )
                  )
            ),
     late, BYROW(linearE,
                 LAMBDA(_d, REDUCE(_d, seq, LAMBDA(_a,_v,
                                                   LET(_s, INDEX(ascDates, _v, 1),
                                                       _e, INDEX(ascDates, _v, 2),
                                                       IF(_a=MEDIAN(_s, _a, _e), _e, _a)
                                                   )
                                            )
                            )
                 )
            ),

     timeBlocks, UNIQUE(HSTACK(early, late)),
     durations, INDEX(timeBlocks, , 2)-INDEX(timeBlocks, , 1),
     SUM(durations)
)

我们要做的第一件事是强制startDates 和endDates 分别位于单个列数组（linearS和linearE）中 — 这意味着我们不再需要担心输入的方向。我们还创建了一个SEQUENCE数字数组，从 1 到我们要处理的日期数。稍后将使用它来控制循环。

datesTable然后，我们创建一个包含线性日期的两列，以及两个排序的副本：按开始日期降序排列，按结束日期升序排列。

接下来是两个大计算：

• 对于每个开始日期，我们将累加器 ( _a) 设置为开始日期，然后循环遍历从“最后开始日期”到“第一个开始日期” ( ) 的日期。（此循环使用我们之前创建的影响descDates来控制）如果介于当前开始日期和结束日期之间，则我们在检查下一个开始/结束对之前更改为当前开始日期。seq_a_a
• 对于每个结束日期，我们将累加器 ( _a) 设置为结束日期，然后循环遍历从“第一个结束日期”到“最后一个结束日期” ( ) 的日期。（此循环使用我们之前创建的影响ascDates进行控制）如果介于当前开始日期和结束日期之间，则我们在检查下一个开始/结束对之前更改为当前结束日期。seq_a_a

这将使每个开始日期尽可能向前移动重叠的时间段early；并将每个结束日期尽可能向后移动重叠的时间段late。我们将early和late组合成一个两列数组，然后取唯一的 timeBlocks。（例如，“12:00–14:00”和“13:00–15:00”将变为“12:00–15:00”和“12:00–15:00”……然后我们只需要这两个记录中的一个！）

最后，我们从结束日期中减去所有的开始日期以timeBlocks得到它们的值durations，然后将它们全部加起来以得到总经过时间。

2024-10-17 根据 Michal 和 rotabor 的评论尝试改进

请参阅编辑历史中的先前答案

---

对于每个条目

• 获取重叠条目

overlap_dur, LAMBDA(i, j,
    MAX(MIN(end(dates, i), end(dates, j)) 
  - MAX(start(dates, i), start(dates, j)), 0)
    ),
has_overlap, LAMBDA(i, MAP(idx, LAMBDA(j, overlap_dur(i, j) > 0))),

• 如果该条目之前没有重叠的条目，
  
  • 根据这些条目计算唯一小时数
    24 * (MAX(end(overlapping, 0)) - MIN(start(overlapping, 0)))
  • 否则为零

=LET(
    last_entry, INDEX(B:B, XMATCH(TRUE, B:B <> "", , -1)),
    dates, A2:last_entry,
    start, LAMBDA(dates, i, INDEX(dates, i, 1)),
    end, LAMBDA(dates, i, INDEX(dates, i, 2)),
    idx, SEQUENCE(ROWS(dates)),
    overlap_dur, LAMBDA(i, j,
        MAX(MIN(end(dates, i), end(dates, j)) - MAX(start(dates, i), start(dates, j)), 0)
    ),
    has_overlap, LAMBDA(i, MAP(idx, LAMBDA(j, overlap_dur(i, j) > 0))),
    unique_hours, REDUCE(
        {"Start", "End", "Duration"},
        idx,
        LAMBDA(acc, i,
            LET(
                overlapping, FILTER(dates, has_overlap(i)),
                dur, IF(
                    AND(i > 1, SUM(--TAKE(has_overlap(i), IF(i > 1, i - 1, 1)))),
                    0,
                    24 * (MAX(end(overlapping, 0)) - MIN(start(overlapping, 0)))
                ),
                VSTACK(acc, HSTACK(start(dates, i), end(dates, i), dur))
            )
        )
    ),
    VSTACK(
        HSTACK("Total Unique Hours", "", SUM(INDEX(unique_hours, , 3))),
        HSTACK("Calculation", "", ""),
        unique_hours
    )
)

任务是合并（或求和）重叠的时间间隔。

有两个选项：工作表公式或用户定义函数。

解决方案 1

该解决方案基于快速算法，内存消耗低，包括一个循环来收集数据、一个排序和一个循环来获取结果。它适用于无限数量的间隔（在公式版本中，受 Excel 动态数组限制，<2^19 个间隔）和无限的时间戳值。

间隔的结束时间应大于或等于开始时间。无需进行初步排序。

公式

输入：

1. StartTimes – 开始时间范围，图片上为A2:A4
2. EndTimes – 结束时间范围，图片上B2:B4

=LET(et,REDUCE("",EndTimes,LAMBDA(a,i,VSTACK(a,HSTACK(i,1)))),
  tl,SORT(DROP(REDUCE(et,StartTimes,LAMBDA(a,i,VSTACK(a,HSTACK(i,-1)))),1)),
  INDEX(REDUCE({0,0,0},SEQUENCE(ROWS(tl)),LAMBDA(a,i,
    LET(t,INDEX(tl,i,1),a1a,INDEX(a,1),a2a,INDEX(a,2),stst,a2a+INDEX(tl,i,2),
      VSTACK(IF(a2a=0,t,a1a),stst,INDEX(a,3)+IF(stst=0,t-a1a,0))
    ))),3)*24)

自定义函数

SumTimes函数需要开始/结束时间范围作为输入。

=SumTimes(A2:B4)

Option Explicit

Function SumTimes(r As Range) As Double
  Dim a, v, i As Long, j As Long, ub As Long, m As Long
  Dim St As Double, Ss As Long, Tot As Double
  a = r: ub = UBound(a, 1): m = 2 * ub
  ReDim v(1 To m, 1 To 2)
  For i = 1 To ub
    v(2 * i - 1, 1) = a(i, 1): v(2 * i - 1, 2) = -1
    v(2 * i, 1) = a(i, 2): v(2 * i, 2) = 1
  Next
  a = Application.Sort(v)
  For i = 1 To m
    j = Ss + a(i, 2)
    If j = 0 Then Tot = Tot + a(i, 1) - St
    If Ss = 0 And j = -1 Then St = a(i, 1)
    Ss = j
  Next
  SumTimes = Tot * 24
End Function

算法

算法将开始和结束时间戳放入一个数组中，然后对其进行排序。然后从最小时间戳开始迭代，计数结束和开始之间的匹配。一旦计数达到零，时间间隔就会添加到总时间中。

解决方案 2

使用 Tom Sharpe 提示的变体（看起来最好）(@TomSharpe)

一般来说，它由一个排序和一个循环组成（由于某些限制，公式版本中有 2 个循环；这可以被侵入，但会稍微复杂一些）。

公式

输入：时间间隔。

=LET(ti,SORT(TimeIntervals),st,TAKE(ti,,1),en,TAKE(ti,,-1),cs,SEQUENCE(ROWS(st)),
  me,SCAN(0,cs,LAMBDA(a,i,IF(i=1,INDEX(en,1),MAX(INDEX(en,i),a)))),
  REDUCE(0,cs,LAMBDA(a,i,a+INDEX(me,i)-IF(i=1,INDEX(st,1),MAX(INDEX(st,i),INDEX(me,i-1)))))
)*24

自定义函数

Function SumTimes(r As Range) As Double
  Dim a, i As Long, j As Long, ub As Long
  Dim ms As Double, mf As Double, Tot As Double
  a = Application.Sort(r): ub = UBound(a, 1)
  ms = a(1, 1): mf = a(1, 2): Tot = mf - ms
  For i = 2 To ub
    ms = IIf(a(i, 1) > mf, a(i, 1), mf)
    If a(i, 2) > mf Then mf = a(i, 2)
    Tot = Tot + mf - ms
  Next
  SumTimes = Tot * 24
End Function

算法

这里的算法非常简单：

对于第一个间隔，持续时间按原样计算。对于后续间隔，持续时间计算：

1. 好像与前一个（第 5 行）没有交集；
2. 如果它们相交，则为前一个的附加时间（第 4 行）。

最后，持续时间总和。