Табель рабочего времени в Excel: от структуры до автоматических расчетов

Иван Корнев·13.04.2026·⏱4 мин

Чтобы вести табель рабочего времени в Excel, создайте файл с тремя листами: «Настройки» (нормы часов), «Табель» (ежедневный ввод данных) и «Итоги» (автоматический расчет). Используйте формулу =(Время_ухода - Время_прихода - Перерыв)*24 для перевода времени в числовые часы и функцию СУММЕСЛИ для подсчета итогов за месяц. Это позволит избежать ошибок при начислении зарплаты и контролировать переработки.

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

Главное правило: Всегда храните время в формате «время» (чч:мм), а для расчетов переводите его в числа (умножая на 24), чтобы избежать ошибок округления в Excel.

Оптимальная структура файла

Хаотичное заполнение одной таблицы приводит к ошибкам. Разделите файл на логические блоки.

Лист 1: «Настройки» (Settings)

Здесь хранятся константы, которые могут измениться. Это упрощает редактирование файла в будущем.

Ячейка B1: Норма часов в день (например, 8).
Ячейка B2: Длительность перерыва в часах (например, 1 или 0 если не учитывается).
Ячейка B3: Ставка переработки (коэффициент, например 1.5 или 2).
Справочник кодов: Таблица с расшифровкой буквенных обозначений (Я — явка, ОТ — отпуск, Б — больничный).

Лист 2: «Табель» (Data Entry)

Основной рабочий лист. Рекомендуемая структура колонок:

A: ФИО сотрудника.
B: Должность.
C–AG: Дни месяца (1–31). В каждой ячейке дня лучше использовать два под-столбца или формат «Приход/Уход», но для простоты часто делают так:
- Вариант А (простой): В ячейке пишется код (Я, ОТ) или количество часов вручную.
- Вариант Б (точный): Отдельные колонки «Приход» и «Уход» для каждого дня (занимает много места, но дает точность до минуты).
AH: Итого отработано часов (формула).
AI: Итого ночных/праздничных (если нужно).
AJ: Переработка/Недоработка.

Лист 3: «Дашборд» или «Итоги»

Сводная таблица, которая тянет данные с листа «Табель» для передачи в бухгалтерию.

Формулы для автоматического расчета

Самая частая проблема — неправильный расчет разницы во времени. Если сотрудник пришел в 9:00, а ушел в 18:00, простая разница даст формат времени. Для зарплатных ведомостей нужны числа.

Расчет часов за один день

Допустим, в ячейке C5 время прихода, в D5 — время ухода, а в ячейке Настройки!$B$2 указан перерыв.

Формула для получения количества часов (число):

=МАКС(0; (D5 - C5 - Настройки!$B$2/24)) * 24

Пояснение: Мы вычитаем перерыв (переведенный в долю суток делением на 24). Функция МАКС(0; ...) нужна, чтобы при ошибочном вводе (уход раньше прихода) не получилось отрицательное число. Умножение на 24 переводит формат времени (0,375) в часы (9).

Ошибка формата: Если после ввода формулы вы видите странное время (например, 216:00 вместо 9), измените формат ячейки на «Числовой» или «Общий».

Подсчет переработок

Если норма часа задана в Настройки!$B$1 (например, 8), формула переработки для строки сотрудника будет такой:

=МАКС(0; AH5 - Настройки!$B$1)

Где AH5 — ячейка с итоговым количеством часов за день или месяц.

Суммирование по типу отсутствия

Если вы используете буквенные коды (Я, ОТ, Б) в диапазоне дней (например, C5:AG5), посчитать количество дней отпуска можно функцией СЧЁТЕСЛИ:

=СЧЁТЕСЛИ(C5:AG5; "ОТ")

Для подсчета общей суммы часов, где в ячейках стоят числа, используйте обычную СУММ:

=СУММ(C5:AG5)

Визуализация и контроль ошибок

Чтобы табель было удобно читать, используйте Условное форматирование.

Выделите диапазон с часами.
Выберите: Главная → Условное форматирование → Правила выделения ячеек.
Красный цвет: если значение меньше нормы (недоработка). Формула: =A1 < 8.
Зеленый цвет: если есть переработка. Формула: =A1 > 8.
Желтый цвет: для выходных дней, если там случайно введены часы работы.

Это позволяет мгновенно увидеть аномалии перед отправкой отчета.

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

Ошибка	Последствие	Как исправить
Ручной ввод чисел вместо времени	Невозможность суммировать «9:00» и число «9»	Приведите все данные к единому формату. Лучше хранить время как время, а считать через формулы.
Отсутствие учета перехода через полночь	Формула выдает ошибку или отрицательное число	Добавьте условие: если время ухода меньше времени прихода, прибавляйте 1 (сутки) к времени ухода.
Разные форматы ячеек	Сумма считается неверно (как текст)	Выделите весь столбец и установите формат «Числовой» или «Время» до начала ввода.
Игнорирование праздников	Завышение нормы часов в месяце	В листе «Настройки» сделайте список праздников и вычитайте их из общей нормы месяца формулой.

Ответы на популярные вопросы (FAQ)

Как учесть смену, переходящую на следующий день (ночную)? В формуле расчета добавьте проверку: =ЕСЛИ(Время_ухода < Время_прихода; Время_ухода + 1; Время_ухода) - Время_прихода. Это добавит одни сутки к времени ухода, если оно меньше времени прихода.

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

Как автоматически подтянуть текущую дату? В заголовке таблицы используйте формулу =СЕГОДНЯ(). Однако для исторических табелей лучше вписывать дату вручную или использовать =ДАТА(2026; 4; 1), чтобы отчет не «уезжал» при открытии в следующем месяце.

Нужно ли учитывать минутные опоздания? Зависит от политики компании. Если нужно округлять до получаса, используйте формулу: =ОКРУГЛ(Часы*2; 0)/2. Это округлит 1 час 15 минут до 1.5 часов, а 1 час 10 минут — до 1 часа.