Как формулами настроить заполнение табеля учета рабочего времени? Шаблон такого табеля можно скачать в этом уроке.

MS Office для Windows

Все мои курсы

Шаблоны

Очередной EXCEL hand-made из серии: если я решила не работать – меня не остановить :) Сохранилась модель табеля УРВ, с которой я в своё время работала – пока нам не наладили ведение табелей в учётной системе.

Потребовалось всего 4 функции, чтобы настроить такой шаблон – это СУММ, СЧЁТ, ЕСЛИ, СЧЁТЕСЛИ. Работает шаблон по следующим правилам:

• В серой ячейке ввести первое число месяца, за который необходимо сделать табель.
• На листе «Исключения» уже есть списки праздничных и сокращённых дней на 2019 г., оформленные как умные таблицы – чтобы в случае увеличения числа строк нам не пришлось бы корректировать привязанные к ним формулы с листа «Табель» [Главная – Форматировать как таблицу (правее центра)]. На 2020 г. списки нужно обновить.
• На листе «Табель» в жёлтых ячейках формулы сами заполняют нужные даты; последние три ячейки с более интенсивной окраской содержат хитрые формулы, чтобы оставить ячейки пустыми, если дней в месяце меньше 31-го. С помощью окна [Формат ячеек] я настроила вывод на экран только номера дня – можете на любой жёлтой ячейке подсмотреть, как реализована эта настройка.
• В голубых ячйках самая сложная формула, которая последовательно проверяет, записана ли текущая дата в списке праздников и если это так, ставит букву «В». Далее формула проверяет список сокращённых дней и ставит цифру 7, если находит текущую дату в этом справочнике. И только в последнуюю очередь выясняет номер дня недели и если номер от 1 до 5 (от пн до пт), то ставит в ячейку 8 часов, либо букву «В» для оставшихся дней.
• В розовых ячейках трудятся простейшие функции СЧЁТ (считает числовые ячейки, а это и есть количество рабочих дней) и СУММ (считает общее кол-во отработанных часов, пропуская текстовые ячейки).
• В зелёных ячейках приведён пример подсчёта выходных и дней отпуска с помощью СЧЁТЕСЛИ.

Уфф, кажется, всё… СТОП! Про условное форматирование сообщить забыла – если вместо рабочих часов формула (или вручную) проставляет буквы, то шрифт автоматически становится красным. Подсмотреть настройку можно выделив любую голубую ячейку и заглянув в «Диспетчер правил условного форматирования» [Главная – Условное форматирование (правее центра) – Управление правилами (внизу списка)].

Лист «Табель» сделан как базовый шаблон, а общий табель по всем сотрудникам смоделирован на листе «ОБЩИЙ». Там все данные по дням заполнены с помощью прямых ссылок на базовый шаблон. Их можно нарушить и вручную проставить ОТ, например. Только сначала сделайте копию листа или копию файла.

Очень много примеров по проверке различных условий с помощью ЕСЛИ и её помощников содержится в моём курсе PRO ФУНКЦИИ.

ВИДЕО с примером в Instagram и Facebook можно посмотреть у olesya.excel по хэштегу #лис_ном_104