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 

В четвертом столбце показан результат, который я хочу вычислить с помощью формулы. Вышеприведенные данные могут быть показаны графически как:

Гистограмма

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

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

  • Как сделать функцию Excel «Auto Fit Row Height» действительно автоматической настройкой высоты строки?
  • Хотите использовать HLOOKUP для перетаскивания всего столбца, как я могу это сделать?
  • Если (AND) комбинация производит «Вы ввели слишком много аргументов для этой функции», ошибка
  • Почему Excel обрабатывает несколько дат по-другому, чем другие?
  • Как суммировать диапазон ячеек со значениями N / A?
  • Умножение значений в Excel 2010 (одна и та же ячейка)
  • Как построить несколько наборов данных с разными значениями x и y на одном графике?
  • Автоматическая последовательная нумерация по критериям ячейки
  • One Solution collect form web for “Excel для подсчета количества одновременных сеансов в зависимости от времени начала и окончания”

    Предполагая, что ваши данные находятся в столбцах от 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 + ENTER и скопировано по столбцу

    Объяснение:

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

    «Массив данных» для 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 суммирует эти 1s ….. но есть только одно ненулевое значение, поэтому результат равен 1 (представляющий количество разных пользователей с сеансами Открыто в то время)

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

    Введите описание изображения здесь

    Давайте будем гением компьютера.