☰

Excel 根据开始/结束时间计算并发会话数

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function microsoft-excel-2007
Excel 根据开始/结束时间计算并发会话数

我有一组海量数据需要处理。A 列中有用户名，BI 列中有会话开始日期/时间，CI 列中有会话结束日期/时间。

我正在尝试根据用户帐户计算同时有多少个并发会话在进行。我遇到的难题是，一个用户可以同时进行多个会话。

例如：

User     Start Time               End Time            Desired Result (license count)

JW      03/24/2015 14:00:44      03/24/2015 14:09:57     -->    4
TT      03/24/2015 13:58:14      03/24/2015 14:21:08     -->    3
DQ      03/24/2015 13:53:10      03/24/2015 14:15:39     -->    3
BB      03/24/2015 13:50:55      03/24/2015 14:20:42     -->    2
BA      03/24/2015 13:43:02      03/24/2015 13:57:26     -->    2
JW      03/24/2015 13:40:30      03/24/2015 13:48:38     -->    1
BA      03/24/2015 13:18:26      03/24/2015 13:18:44     -->    1
BA      03/24/2015 13:15:18      03/24/2015 13:15:22     -->    1
CT      03/24/2015 11:56:55      03/24/2015 11:58:21     -->    1
CT      03/24/2015 11:53:23      03/24/2015 11:56:55     -->    1
CT      03/24/2015 11:51:50      03/24/2015 11:53:23     -->    1
CT      03/24/2015 11:48:11      03/24/2015 12:16:36     -->    1
CT      03/24/2015 11:36:54      03/24/2015 11:37:50     -->    1
CT      03/24/2015 11:33:52      03/24/2015 11:39:38     -->    1
CT      03/24/2015 11:31:25      03/24/2015 11:34:01     -->    1

第四列显示了我希望能够用公式计算的结果。上述数据可以图形方式显示为：

 条形图

如您在示例末尾（以及图表底部）看到的，用户 CT 一次进行多个会话。这些连接仅算作一个许可证。

如果我需要澄清这一点，请告诉我。

答案1

假设您的数据在列中A，C从第 2 行开始，那么您可以在D2

=SUM(IF(FREQUENCY(IF(B$2:B$16<=B2,IF(C$2:C$16>=B2,MATCH(A$2:A$16,A$2:A$16,0))),ROW(A$2:A$16)-ROW(A$2)+1),1))

使用 ++CTRL确认并复制下列SHIFTENTER

解释：

这是一种常用技术，用于获取一列（在本例中为用户）中不同值的计数，其中其他列满足某些条件（在本例中，最新的开始时间/日期介于其他列中的开始时间/日期和结束时间/日期之间）。

“数据数组”是满足时间标准的行的函数FREQUENCY结果-并将找到MATCHMATCH第一的匹配值，因此，如果有重复用户，则MATCH每个用户都会返回相同的数字（并且会得到FALSE不满足条件的行）

“箱子FREQUENCY”包括所有可能的结果MATCH（在本例中为 1 到 15），因此如果满足条件（时间段包含最新的开始时间）并且用户相同，则相同号码在数据数组中返回并且进入相同的bin......因此，计算大于 0 的箱子数量就足以得到不同用户的数量。

具体来说，以第 2 行为例，数据数组变成这样：

{1;2;3;4;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

并且 4 个不同的值被返回到 4 个不同的容器中，因此你得到的结果为 4

....但是对于第 10 行，数据数组变成这样：

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;9;9;FALSE;9;FALSE;FALSE;FALSE}

其中有 3 行符合时间条件.....但全部属于同一个用户（CT），因此该MATCH函数对这三个行返回 9（中第一个“CT”条目的位置A2:A16），因此FREQUENCY在同一个 bin 中获取 3 个值，因此公式解析为：

=SUM(IF({0;0;0;0;0;0;0;0;3;0;0;0;0;0;0;0},1))

该IF函数为返回的数组中的每个非零值返回 1，并对FREQUENCY这些SUM1 求和.....但只有一个非零值，因此结果是1（表示当时打开会话的不同用户的数量）

请参阅所附屏幕截图

在此处输入图片描述

答案2

这是一个更短、更简单的公式，可以产生所需的结果，这似乎是

  • 此行下方的行数
    • 时间范围重叠，并且
    • 用户不同
  • 加一。

第一步是确定 当且仅当Start 1 < End 2 且End 1  > Start 2时，区间 Start 1  /End 1与区间 Start 2 /End 2 重叠。（如果您仔细思考，就会很容易明白；如果您将其画出来，就会更容易明白。）

barry houdini 使用 ≤ 和 ≥，所以我将使用相同的约定。据我所知，示例数据集中不存在一个会话的开始或结束时间与属于不同用户的会话的开始或结束时间完全重合的情况，因此这种方法的差异不应产生不同的结果（对于示例数据集）。

因此，对于每一行，我们要计算开始/结束记录中此行下方的行，这些行符合上述条件，并且用户 ID 不等于此行的用户 ID。然后加 1。这很简单

=COUNTIFS(B2:B$16, "<="&C2, C2:C$16, ">="&B2, A2:A$16, "<>"&A2) + 1

请注意，我定义了范围从当前行（表示为第 2 行，包含单元格A2、B2和C2）到绝对行号 16（表示为第 $16 行，包含单元格A16、B16和C16）。这导致COUNTIF仅查看当前行和后续行。请注意，这是不是数组公式。

我会发布一张截图，但它实际上与 barry 的完全相同，因此浪费带宽。

相关内容