一直在寻找,但找不到解决方案。希望找到包含 200 行以上的 Excel 表的唯一时间总和。
例如。
在上图中,总的唯一时间为 6 小时(12:00 – 14:00 2 小时,14:00 – 16:00 2 小时,17:00 – 19:00 2 小时)。
任何帮助都将不胜感激!谢谢
除了手动添加每行唯一时间然后求和之外,不知道从哪里开始。
8
5 个回答
5
在 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)。如果输入数据的粒度更高或更低,则可以进行调整。
2
-
于是我研究了一下这个公式,虽然可行,但是算法很差。
– -
最大时间间隔为 1048576 分钟(约 2 年)。实际上,这并不符合预期。这可能是动态数组的限制,谁知道呢。
–
|
首先,我假设工作表/数据按开始时间(升序)排序,然后按结束时间(升序)排序。这样工作就容易多了,因为一切都组织得井井有条。一旦得到结果,你总是可以按另一种方式排序。
我在这里使用的逻辑是假设我遇到的第一行是“完整的”……然后将后面的行与前面的行进行比较,将开始时间“重新定位”为上一行的结束时间……如果有重叠。
我在 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)
)
我们要做的第一件事是强制startDate
s 和endDate
s 分别位于单个列数组(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
)
)
11
-
看来这个巨大的公式是有效的。我希望你不要使用每分钟表。干得好。
– -
感谢@rotabor的赞美!我希望Pb、JvDV或者你能缩短它;)
– -
这是一个令人惊奇的公式,是我永远无法做到的,但它似乎无法处理超过 1 年的数据。
– -
@nkalvi – 它在两个月或多个月之间的多天内也无法工作 –
– -
@nkalvi – rotabor 对我的努力并不满意,但也许你可以将我的方法与你的方法结合起来。扩展每个日期时间,不是基于小时,而是基于整个日期时间,然后堆叠它们。
–
|
任务是合并(或求和)重叠的时间间隔。
有两个选项:工作表公式或用户定义函数。
解决方案 1
该解决方案基于快速算法,内存消耗低,包括一个循环来收集数据、一个排序和一个循环来获取结果。它适用于无限数量的间隔(在公式版本中,受 Excel 动态数组限制,<2^19 个间隔)和无限的时间戳值。
间隔的结束时间应大于或等于开始时间。无需进行初步排序。
公式
输入:
- StartTimes – 开始时间范围,图片上为A2:A4
- 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
算法
这里的算法非常简单:
对于第一个间隔,持续时间按原样计算。对于后续间隔,持续时间计算:
- 好像与前一个(第 5 行)没有交集;
- 如果它们相交,则为前一个的附加时间(第 4 行)。
最后,持续时间总和。
0
|
–
–
–
–
–
|