Построение наглядного расписания смен в Excel

Чтобы создать график работы в Excel, подготовьте таблицу с датами, именами сотрудников и временем смен, а затем выберите метод визуализации: условное форматирование для простых календарей или диаграмму Гантта для отображения длительности задач. Самый быстрый способ — использовать сетку «Сотрудники × Даты» и закрасить ячейки цветами в зависимости от типа смены (утро, день, ночь) через меню «Условное форматирование».

Подготовка исходных данных

Качество графика напрямую зависит от структуры входных данных. Перед построением визуализации создайте лист «Данные» со следующими обязательными столбцами:

Убедитесь, что даты и время распознаны Excel корректно (выравнивание по правому краю ячейки). Текстовые значения дат сломают формулы и сортировку.

Способ 1: Календарь с условным форматированием

Этот метод идеален для быстрого просмотра: кто работает сегодня и какая у него смена. Визуально это выглядит как цветная матрица.

Пошаговая инструкция

1. Создайте сетку:
   • В столбце A (начиная с A2) перечислите фамилии сотрудников.
   • В строке 1 (начиная с B1) протяните даты нужного месяца.
2. Настройте формулу:
   • Выделите всю область пересечения сотрудников и дат (например, B2:AF30).
   • Перейдите: Главная → Условное форматирование → Создать правило → Использовать формулу.
   • Введите формулу для поиска смены (предполагая, что данные на листе «Данные»):

        =СЧЁТЕСЛИМН(Данные!$B:$B; $A2; Данные!$A:$A; B$1) > 0
        ```
        *Логика:* Если имя сотрудника из столбца A и дата из заголовка строки 1 найдены в таблице данных, ячейка окрашивается.
3.  **Назначьте цвета:**
    *   Нажмите кнопку «Формат» и выберите цвет заливки (например, зеленый).
    *   Для разных типов смен (Ночь, Выходной) создайте отдельные правила с другими формулами и цветами.

Способ 2: Диаграмма Гантта для временных интервалов

Если нужно видеть не просто факт работы, а длительность и наложение смен во времени, используйте диаграмму Гантта.

Алгоритм построения

1. Подготовьте данные для графика: Создайте сводную таблицу или новый диапазон с колонками:
   • Сотрудник
   • Дата начала (числовое значение даты)
   • Длительность (в днях или долях дня). Формула: =(Конец_смены - Начало_смены) или просто количество дней задачи.
2. Вставка диаграммы:
   • Выделите данные.
   • Вкладка Вставка → Гистограмма → Линейчатая с накоплением (Stacked Bar).
3. Трансформация в Гантт:
   • На графике появятся два ряда столбцов: «Дата начала» (синий) и «Длительность» (оранжевый).
   • Кликните правой кнопкой мыши по ряду «Дата начала» → Формат ряда данных.
   • Установите Заливку → Нет заливки и Граница → Нет линий.
   • Ряд исчезнет, но сдвинет ряд «Длительность», создав эффект плавающих полосок времени.
4. Настройка осей:
   • Ось категорий (слева) должна содержать имена сотрудников. Если они идут в обратном порядке, нажмите на ось → Формат оси → галочка Обратный порядок категорий.
   • Ось дат (снизу) автоматически подстроится под минимальную и максимальную дату в данных.

Способ 3: Автоматический подсчет часов

Для контроля переработок и расчета зарплаты добавьте блок статистики рядом с графиком.

Используйте следующие формулы для анализа:

• Общее количество смен сотрудника: =СЧЁТЕСЛИ(Данные!$B:$B; "Иванов А.А.")
• Суммарное время работы (в часах): =СУММЕСЛИ(Данные!$B:$B; "Иванов А.А."; Данные!$F:$F) (Где столбец F содержит предварительно рассчитанную длительность одной смены в формате времени).
• Количество ночных смен: =СЧЁТЕСЛИМН(Данные!$B:$B; "Иванов А.А."; Данные!$E:$E; "Ночь")

Частые ошибки при создании графика

• Текстовый формат дат. Самая распространенная проблема. Если при сортировке даты не встают по порядку, значит, они записаны как текст. Исправление: выделите столбец → Данные → Текст по столбцам → Готово.
• Сбитые ссылки при копировании. При протягивании формул условного форматирования убедитесь, что ссылки на имя сотрудника закреплены по столбцу ($A2), а ссылки на дату — по строке (B$1).
• Отсутствие учета переходов через полночь. Если смена начинается в 22:00 и заканчивается в 06:00 следующего дня, простая формула Конец - Начало даст отрицательное значение. Используйте формулу: =ЕСЛИ(Конец<Начало; Конец+1; Конец) - Начало.

FAQ

Как сделать график смен на месяц автоматически? Используйте функцию ДАТА(год; месяц; 1) для старта и протяните вправо, либо примените умную таблицу (Ctrl+T), чтобы новые строки с данными автоматически подхватывались формулами и форматированием.

Можно ли распечатать график в цвете? Да, перед печатью перейдите в «Разметка страницы» и выберите режим «Просмотр разрывов страниц», чтобы убедиться, что вся сетка помещается на листы А4. Для экономии краски настройте условное форматирование на использование паттернов (штриховки) вместо сплошной заливки.

Как скрыть выходные дни в календаре? Добавьте правило условного форматирования с формулой =ИЛИ(ДЕНЬНЕД(B$1;2)=6; ДЕНЬНЕД(B$1;2)=7) и установите для него серую заливку. Это визуально отделит рабочие дни от выходных.