Автоматизация расписания и планирования задач в Excel

Создать рабочий график сотрудников или план-график проекта в Excel можно за 10–15 минут, используя встроенные функции автозаполнения, условного форматирования и простые формулы. Вместо ручного заполнения ячеек мы настроим таблицу так, чтобы она сама считала отработанные часы, подсвечивала смены цветами и визуализировала сроки задач в виде диаграммы Ганта. Ниже приведены два готовых решения: для посменного учета команды и для управления этапами проекта.

Часть 1. Табель учета рабочего времени (Сменный график)

Этот раздел поможет составить расписание для коллектива с плавающим графиком, сменами «день/ночь» или стандартной пятидневкой.

Подготовка структуры таблицы

1. Откройте новый лист. В ячейку A1 впишите название месяца (например, Апрель 2026).
2. В строке 3 создайте шапку:
   • A3: «Сотрудник»
   • B3:H3: Дни месяца (1, 2, 3...). Лайфхак: введите 01.04.2026 в B3, протяните вправо до конца месяца. Затем нажмите Ctrl+1 → вкладка «Число» → формат «Д» (отобразится только число дня).
   • I3: «Итого часов».
   • J3: «Норма выработки» (опционально).
3. В столбце A (начиная с A4) перечислите фамилии сотрудников.

Автоматизация через условное форматирование

Чтобы не вводить «9-18» или «Вых» вручную каждый раз, настроим цветовую индикацию. Выделите диапазон дней (например, B4:H15):

1. Перейдите на вкладку Главная → Условное форматирование → Правила выделения ячеек → Текст содержит.
2. Настройте правила:
   • Содержит 9-18 → Зеленая заливка (стандартная смена).
   • Содержит 20-8 или Н → Синяя заливка (ночная смена).
   • Содержит Вых или 0 → Серая заливка (выходной).
   • Содержит ОТ → Желтая заливка (отпуск/больничный).

Теперь при вводе кода смены ячейка автоматически окрасится в нужный цвет.

Формулы для подсчета часов

Чтобы график считал отработанное время автоматически, используйте функцию СЧЁТЕСЛИ.

В ячейку I4 (напротив первого сотрудника) вставьте формулу:

=СЧЁТЕСЛИ(B4:H4; "Д")*8 + СЧЁТЕСЛИ(B4:H4; "Н")*11 + СЧЁТЕСЛИ(B4:H4; "9-18")*9

Где:

• "Д"*8 — считает дни по 8 часов.
• "Н"*11 — считает ночи по 11 часов.
• "9-18"*9 — считает смены с перерывом (пример).

Протяните формулу вниз до последнего сотрудника. Внизу таблицы (строка под последним именем) добавьте =СУММ(I4:I15), чтобы видеть общий фонд рабочего времени отдела.

Часть 2. План-график проекта (Диаграмма Ганта)

Для визуализации сроков задач лучше всего подходит ленточная диаграмма (Ганта). Она строится на основе обычной столбчатой диаграммы с накоплением.

Шаг 1: Таблица данных

Создайте новую таблицу со следующими столбцами:

Важно: Столбец «Дата окончания» можно рассчитать формулой =Дата_начала + Длительность, но для построения графика он не используется напрямую.

Шаг 2: Построение диаграммы

1. Выделите столбцы «Задача», «Дата начала» и «Длительность».
2. Вкладка Вставка → Диаграммы → Гистограмма → Гистограмма с накоплением (второй значок в группе).
3. Появится график, где синие столбцы («Дата начала») стоят перед оранжевыми («Длительность»).

Шаг 3: Превращение в Гант

Сейчас диаграмма выглядит неправильно. Исправим это:

1. Скройте столбцы «Дата начала»: Кликните правой кнопкой мыши по любому синему столбцу на графике → Формат ряда данных → Заливка → Нет заливки. Также уберите Граница → Нет линий. Синие столбцы исчезнут, а оранжевые «повиснут» в воздухе, образуруя ленты задач.
2. Переверните ось задач: Кликните по вертикальной оси (с названиями задач) → Формат оси → поставьте галочку «Обратный порядок категорий». Теперь первая задача будет сверху.
3. Настройте даты: Если график начинается слишком далеко слева, кликните по горизонтальной оси (даты). В поле «Минимум» введите числовое значение даты старта. Как узнать число даты? В любой пустой ячейке введите дату начала проекта, затем измените формат ячейки на «Общий». Скопируйте полученное число (например, 45383) и вставьте его в настройки оси.

Сравнение методов планирования

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

1. Ручной ввод дат. Не пишите даты вручную (1, 2, 3...). Используйте маркер автозаполнения (протягивание за уголок ячейки), чтобы сохранить формат даты. Иначе формулы подсчета дней не сработают.
2. Отсутствие проверки нормы часов. Забудьте сверять часы глазами. Всегда добавляйте столбец «Итого» с формулой СУММ или СЧЁТЕСЛИ, чтобы сразу видеть переработки (>40 часов) или недоработки.
3. Слияние ячеек. Избегайте кнопки «Объединить ячейки» внутри диапазона данных. Это ломает сортировку, фильтры и работу формул. Для центрирования заголовков используйте формат «По центру выделения».
4. Неверный тип диаграммы. Для Ганта нельзя использовать обычную линейчатую диаграмму без накопления. Обязательно выбирайте тип «С накоплением», чтобы скрыть начальный отступ.

FAQ

Как сделать график скользящим на следующий месяц? Просто измените дату в первой ячейке шапки. Если использованы формулы для генерации дней недели (=ДЕНЬНЕД()) и автозаполнение, таблица расширится или обновится сама. Для диаграммы Ганта нужно будет скорректировать «Минимум» оси дат.

Можно ли защитить график от изменений сотрудниками? Да. Выделите ячейки, которые можно менять (например, столбец с фамилиями или ввод смен), нажмите Ctrl+1 → вкладка «Защита» → снимите галочку «Защищаемая ячейка». Затем включите защиту листа: Рецензирование → Защитить лист. Остальные ячейки (формулы, заголовки) редактировать будет нельзя.

Как распечатать график на одном листе А4? Перейдите в Разметка страницы → Ширина → выберите «1 страница». Excel автоматически уменьшит масштаб, чтобы вся таблица поместилась по ширине листа. Проверьте ориентацию (лучше «Альбомная»).