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

Question

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

Я также собираюсь определить, когда идентификатор пользователя зарегистрирован в устаревшей ERP-системе unix с нескольких компьютеров одновременно - потенциально они делятся своими учетными данными с коллегой.

Пример данных:

Record #    Time In    Time Out    User      PC Name
1           10:54      10:55       Al        PC1
2           10:55      16:55       Dave      PC42
3           09:11      09:29       Al        PC1
4           11:02      18:03       Al        PC1
5           16:19      17:38       George    PC678
6           16:43      17:41       Al        PC678
7           16:46      17:39       Dave      PC42
8           17:25      17:49       Al        PC42

Значения «Время входа» и «Время ожидания» фактически являются полной датой / временем, поэтому вам не нужно беспокоиться о сравнении событий, которые произошли в разные дни.  Я показываю только время здесь, чтобы не предоставлять слишком много информации.  Приведенные выше данные могут быть представлены графически в виде:

    

В конце списка (и в нижней части диаграммы) вы можете видеть, что пользователь Al одновременно обращается к системе с нескольких компьютеров, а также другие пользователи, которые также получают доступ к системе с тех же компьютеров. Пользователь Al либо обходится и входит в систему (маловероятно), либо другие пользователи могут войти в систему как Al для выполнения задач, которые их разрешения не позволяют им делать. В идеале я хотел бы иметь новый столбец, чтобы подчеркнуть, что в то время были одновременные сеансы с идентификатором пользователя Al на разных ПК. Записи № 4, 6 и 8 будут предупреждены - Записи № 2 и 7 в порядке, так как один и тот же пользователь вошел в систему на том же ПК.

Answer 1

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

=COUNTIFS(A$2:A$9, "<"&B2, B$2:B$9, ">"&A2, C$2:C$9, "="&C2)

для вашего исходного макета данных (где Time In - это столбец A , Time Out - это столбец B , а User - это столбец C).  Это оценивает как минимум 1, потому что каждая строка перекрывает себя.  Вы хотите увидеть, какие строки перекрывают другие строки, поэтому мы делаем

=IF(COUNTIFS(B$2:B$9, "<"&C2, C$2:C$9, ">"&B2, D$2:D$9, "="&D2) > 1,  "Overlap",  "")

После того, как я изменил время входа для записи № 7 (пользовательский Дейв на ПК42) с 16:46 на 16:56, чтобы устранить наложение с записью № 2 (также пользовательский Дейв на ПК42), которая, как я полагаю, была непреднамеренной, я получаю следующее Результаты:

        

что, я считаю, то, что вы хотите.

Хорошо, если возможно и допустимо, чтобы пользователь регистрировался на одном ПК для перекрывающихся периодов времени (как это делает Дейв на ПК42 в записях 2 и 7), и вы не хотите, чтобы они считались перекрывающимися, тогда нам нужно чтобы изменить нашу формулу для подсчета только тех строк, где ПК отличается:

=IF(COUNTIFS(A$2:A$9, "<"&B2, B$2:B$9, ">"&A2, C$2:C$9, "="&C2, D$2:D$9, "<>"&D2) > 0, "Overlap", "")

Обратите внимание, что мы должны изменить тест с > 1 на > 0 потому что строки больше не перекрывают друг друга.