Комплексная формула кутифа

Question

У меня есть набор данных, который в заголовках имеет: (имя сотрудника строки, выходной день строки, время заезда, время выезда).

Я хочу получить таблицу с метками строк с понедельника по пятницу, а в качестве заголовков столбцов - часы с 00:00 до 23:00. В каждой ячейке этой таблицы у меня должно быть количество сотрудников, которые работают в этот день в этот час (т. Е. Количество сотрудников, в смену которых входит этот час и которые не выходной).

Неполных часов нет, все сотрудники работают целые часы. Каждый сотрудник получает один выходной.

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

Answer 1

ОК, я сделал это Вот как -

Во-первых, я расширил ваш набор данных, чтобы облегчить жизнь. Поэтому после первых 4 столбцов добавьте столбцы с заголовком (начиная с E1) «00:00», «01:00» ... «23:00». Для первого сотрудника в «00:00» (ячейка E2) выведите формулу

=IF(AND(E$1>=$C2, E$1<=$D2),1,0)

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

Теперь, чтобы упростить себе жизнь, я составил итоговую таблицу (то есть, что вы ищете), с часами, прямо расположенными под этими новыми столбцами на том же листе. В моих примерах данных я перешел к 5-й строке для данных о сотрудниках, поэтому вам нужно настроить их на количество строк. В итоговой строке за понедельник для ячейки в столбце E (0:00) используйте формулу

=SUMIFS(E$2:E$5,$B$2:$B$5,"<>Monday")

Это должно составлять 1, только если они не работают в понедельник в 0:00. Скопируйте и заполните формулу еще на 4 ячейки и просто измените день в конце формулы на вторник, среду, четверг, пятницу. Добавьте метки строк с понедельника по пятницу слева от этих ячеек.

Наконец, скопируйте и заполните эту формулу (5 ячеек), чтобы заполнить всю итоговую таблицу (т. Е. До 23:00 столбца). В конце концов, это не слишком сложно и легко проверить, что делает то, что вы ожидаете, я думаю. Это все динамично, измените время входа / выхода или выходной, и общая таблица будет отражать это.