Excel для подсчета количества одновременных сеансов на основе времени начала / окончания

Question

У меня есть огромный набор данных, которые я пытаюсь обработать. В столбце A у меня есть имя пользователя, в столбце BI - дата / время начала сеанса, в столбце C - дата / время окончания сеанса.

Я пытаюсь подсчитать, сколько одновременных сеансов происходит одновременно в зависимости от учетной записи пользователя. Сложность, с которой я сталкиваюсь, заключается в том, что один пользователь может проводить несколько сеансов одновременно.

Например:

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 проводит несколько сеансов одновременно.  Эти подключения будут считаться только одной лицензией.

Дайте мне знать, если мне нужно уточнить это.

Answer 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+SHIFT+ВВОД и копируется в столбец

Объяснение:

Это распространенная методика, используемая для получения количества разных значений в одном столбце (в данном случае пользователей), когда в других столбцах соблюдаются некоторые критерии (в этом случае самое позднее время / дата начала находится между временем начала / датой и концом). время / дата в других столбцах).

"Массив данных" для FREQUENCY является результатом функции MATCH для строк, где соблюдаются временные критерии - и MATCH найдет первое совпадающее значение, поэтому, если у вас есть повторяющиеся пользователи, 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 получает 3 значения в одном бине, поэтому формула разрешается следующим образом:

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

Функция IF возвращает 1 для каждого ненулевого значения в массиве, возвращенного FREQUENCY и SUM суммирует эти 1 с ....., но есть только одно ненулевое значение, поэтому результат равен 1 (представляя количество разных пользователей с сессиями открыть в то время)

Смотрите скриншот прилагается

Answer 2

Вот гораздо более короткая, простая формула, которая дает желаемый результат, который, кажется,

• количество строк ниже этого, для которого
  • временные диапазоны перекрываются, и
  • пользователь другой
• плюс один.

Первый шаг - выяснить, что интервал Начало ₁/ Конец ₁ перекрывается с интервалом Начало ₂/ Конец _2, если и только если Начало ₁ <Конец ₂ и Конец ₁ > Начало ₂.  (Это легко увидеть, если вы думаете об этом; легче, если вы рисуете.)

Барри Гудини использовал ≤ и ≥, поэтому я буду использовать то же соглашение.  AFAICT, в примере набора данных отсутствуют экземпляры, в которых время начала или окончания одного сеанса точно совпадает с временем начала или окончания сеанса, принадлежащего другому пользователю, поэтому это различие в подходе не должно давать других результатов (для примера Набор данных).

Таким образом, для каждой строки мы хотим подсчитать строки ниже этой в начальной / конечной записи, для которой вышеприведенное является истинным, и идентификатор пользователя не равен идентификатору пользователя для этой строки.  И добавить 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 искать только текущую строку и следующие.  И обратите внимание, что это не формула массива.

Я бы опубликовал скриншот, но он был бы (фактически) идентичен снимку Барри, и, следовательно, трата пропускной способности.