跨多日期精确到秒的重叠时间段并发次数统计需求

我有如下结构的时间区间数据（示例1）：

需要精确到秒统计重叠时间段的并发次数，并且要统计每组独立重叠区间的最高并发数。比如下面的示例2数据：

前5行的时间段互相重叠，最高并发数为5；最后2行的时间段互相重叠但不与前5行重叠，需单独统计为一组，最高并发数为2。同时数据涉及多日期，解决方案必须支持跨日期场景（示例3数据如下）：

之前尝试过的方案仅支持天级精度，无法满足秒级统计需求。

方案一：Excel动态数组公式（适合Excel 365/2021） #

步骤1：提取并排序所有时间节点 #

假设你的Start时间在A列，End时间在B列，找个空白单元格（比如D2）输入下面的公式，它会把所有开始、结束时间提取出来，去重后按时间排序：

=SORT(UNIQUE(VSTACK(A2:A27,B2:B27)))

把A2:A27、B2:B27换成你实际的数据范围就行

步骤2：计算每个时间点的并发数 #

在E2单元格输入这个公式，逐个计算每个时间节点上有多少个时间段在运行：

=BYROW(D2:D53,LAMBDA(x,SUM(--(A2:A27<=x)*(B2:B27>x))))

D2:D53是步骤1生成的时间节点范围，记得同步替换A/B列的范围

步骤3：分组统计每个重叠区间的最高并发 #

先在F2输入这个公式，给每个时间节点标记所属的区间组：

=SCAN(0,E2:E53,LAMBDA(a,b,IF(b=0,a,a+(b<>INDEX(E2:E53,MAX(1,ROW()-1))))))

然后在G2输入这个，生成每个组对应的并发数：

=UNIQUE(HSTACK(F2:F53,E2:E53))

最后用PIVOTBY函数直接算出每个组的最高并发：

=PIVOTBY(F2:F53,E2:E53,E2:E53,MAX,,0)

方案二：Power Query（大数据量更高效） #

如果数据量较大，用Power Query处理更稳定：

步骤1：导入数据到Power Query #

选中你的数据区域，点击数据选项卡 -> 从表格/区域，进入Power Query编辑器，先把Start和End列的类型改成日期/时间。

步骤2：生成时间节点与并发数 #

1. 添加自定义列，命名为事件，公式：

{{"开始", [Start]}, {"结束", [End]}}

2. 点击事件列右侧的展开按钮，将其拆分为新行。
3. 把展开后的两列重命名为事件类型和时间点，然后按时间点排序。
4. 添加自定义列并发数，公式：

List.Sum(List.Transform(Table.Range(#"排序的行",0,Index.Position()), (row) => if row[事件类型] = "开始" then 1 else -1))

步骤3：分组统计最高并发 #

1. 添加自定义列区间组，公式：

List.Accumulate(List.Range(#"添加自定义"[并发数],0,Index.Position()), 0, (state, current) => if current = 0 then state else state + (current <> List.Range(#"添加自定义"[并发数],0,Index.Position()-1){Index.Position()-1}?))

2. 筛选掉并发数为0的行（这些是区间之间的分隔点）。
3. 点击转换选项卡 -> 分组依据，分组列选区间组，新列名设为最高并发，操作选最大值，列选并发数。
4. 点击关闭并上载，即可得到每个独立重叠区间的最高并发次数。

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