Сводные данные электронной таблицы на основе диапазонов даты и времени

Question

У меня есть электронная таблица, содержащая данные регистрации в определенной системе

Log in time        Log out time       Id        User_type
2018-03-21 18:28   2018-03-21 19:59   1584550   S1
2018-03-21 14:57   2018-03-22 00:29   1584549   S1
2018-03-21 11:01   2018-03-21 12:01   1584548   C2
2018-03-21 01:25   2018-03-21 03:01   1584547   C2
2018-03-20 20:20   2018-03-20 21:14   1584546   S2
... (hundreds of rows) ...

Это данные за одну неделю, 168 часов. Что мне нужно, так это почасовая разбивка числа людей, вошедших в систему. Результат будет выглядеть примерно так:

19/03/2018 0:00-0:59   5
19/03/2018 1:00-1:59   4
19/03/2018 2:00-2:59   6
19/03/2018 3:00-3:59   8
19/03/2018 4:00-4:59   9
-- and so on...

У меня уже есть идея, как сделать это с помощью скрипта Groovy, суммируя его в циклах, но может быть есть встроенная функция Excel, которую я мог бы использовать для этого, верно?

Буду признателен за понимание, спасибо.

Answer 1

Для исходных данных в списке A:D (строка 1 - заголовок), часов (с данными) в столбце F используйте следующую формулу в G1:

=COUNTIFS($A:$A,"<" & F1,$B:$B,">" & F1)

Перетащите его вниз по списку даты и часа.

Формула подсчитывает количество зарегистрированных пользователей, перекрытых для каждого значения даты-часа (точки). Чтобы подсчитать частичное перекрытие по диапазону, вы можете отредактировать формулу (и, возможно, добавить еще один столбец в список даты-часа, от-до).