Решение требует использования двух формул массива:
Массив введите (Ctrl+Shift+Enter) следующую формулу в F2 и скопируйте-вставьте / заполните вниз в остальную часть столбца таблицы (не забудьте удалить { и }):
{=MIN(IF($A$2:$A$15=$E2,$B$2:$B$15,MAX($B$2:$B$15)))}
Массив введите (Ctrl+Shift+Enter) следующую формулу в G2 и скопируйте-вставьте / заполните вниз в оставшуюся часть столбца таблицы (не забудьте удалить { и }):
{=MAX(IF($A$2:$A$15=$E2,$C$2:$C$15,MIN($C$2:$C$15)))}
Обе эти формулы работают путем генерации массива соответствующих моментов времени, когда строка соответствует соответствующему пользователю, и специального значения, если это не так. Хитрость заключается в том, чтобы выбрать специальное значение, чтобы оно не мешало функциям =MIN(…) и =MAX(…) используемым для извлечения первого входа в систему и последнего выхода из системы.
Эти специальные значения являются максимальным из всех времен входа в систему для случая MIN и минимальным из всех времен выхода из системы для случая MAX .
Конечно, вы можете просто использовать любое произвольно большое число для случая MIN
{=MIN(IF($A$2:$A$15=$E2,$B$2:$B$15,10^9))}
и ноль в случае MAX
{=MAX(IF($A$2:$A$15=$E2,$C$2:$C$15,0))}
РЕДАКТИРОВАТЬ:
Если какие-либо данные в отчете (User , Login или Logout) могут отсутствовать, то формулы необходимо адаптировать. Проще всего изменить последние две формулы выше.
Формула F2 становится
{=IFERROR(1/(1/(1/(1/MIN(IF($A$2:$A$15=$E2,$B$2:$B$15,10^9)))-1/(1/10^9)))+10^9,"")}
и формула G2 меняется на
{=IFERROR(1/(1/MAX(IF($A$2:$A$15=$E2,$C$2:$C$15,0))),"")}
Чтобы понять, как работают эти модификации, лучше начать со второй формулы.
1/(1/MAX(…)) преобразует нулевой результат в #DIV/0! ошибка, но оставляет все остальные значения в покое *. Нулевой результат возникает, если либо нет времени входа для указанного пользователя, либо для этого пользователя вообще нет записей.
IFERROR() затем преобразует ошибку в пустое. (Обратите внимание, что при желании вы можете заменить любую строку на пробел.)
Первая формула немного сложнее, так как при отсутствии совпадающих записей возвращается результат 10^9 вместо нуля. (Ноль все еще возвращается в случае отсутствия времени выхода из системы, хотя.)
Таким образом, требуется преобразование 10^9 в пробел, а также преобразование в ноль. Это второе преобразование выполняется путем вычитания 10^9 из первого результата преобразования с нуля в ошибку и преобразования его в ошибку, если она равна нулю. Первоначальный результат затем восстанавливается путем добавления 10^9 . Конечно, если частичный результат был ошибкой, ошибка сохраняется.
-1/(1/10^9) вместо -10^9 требуется для учета ошибок с плавающей запятой.
IFERROR() затем преобразует ошибку в пробел, как это было во второй формуле.
