Как сделать табель учета рабочего времени в excel с нуля?

Как сделать табель учета рабочего времени в excel с нуля?

Все, кто работал в средних и крупных компаниях, наверняка сталкивались с таким документом, как табель учёта рабочего времени. Его заполняют руководители групп или отделов и сдают в отдел кадров. Хорошо, если последний создаёт его на год и распространяет в виде шаблона, но часто происходит доработка своего старого шаблона каждым ответственным.

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

Готовый шаблон можно скачать тут.

Чем удобен данный шаблон:

• Чтобы поучить колонки с датами нужного месяца, надо просто указать нужный год и нужный месяц. Остальное шаблон сделает сам.
• Выходные дни автоматически подсвечиваются серым
• Праздничные дни подсвечиваются красным, если вы их указали на листе Праздники (заранее предугадать, каие праздники и куда наше правительство перенесёт затруднительно)
• Рамки во всей таблице рисуются автоматически при помощи условного форматирования — достаточно ввести новую фамилию сотрудника, чтобы это увидеть
• Больничные, отпуска, прогулы и рабочие дни выделяются цветом (при вводе соответствующего кода в ячейки)

Данный шаблон демонстрационный, поэтому тут нет подъитогов, которые обычно есть в табеле, подписей и т.д. Моя задача — демонстрация возможностей Excel.

Попробуйте поизменять ячейку Месяц в шаблоне для 2014 года, чтобы убедиться в корректном динамическом формировании всех колонок.

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

Теперь настало время разобраться, как же работает всё это «волшебство».

1. Для начала сделайте видимыми скрытые строки 3, 4 и 6.

2. Обратите внимание, что в Менеджере имён четырём ячейкам и одному диапазону даны символьные имена.

• Ячейка B1 поименована как TYear — тут мы выбирает нужный нам год
• Ячейка B2 поименована как TMonth — тут мы выбираем нужный месяц, вводя значение от 1 до 12
• Ячейка B3 поименована как TDays — тут мы вычисляем количество дней в выбранном месяце
• Ячейка B4 поименована как TOffset — здесь мы храним количество столбцов между первым столбцом с датой табеля и столбцом A
• На листе Праздники объявлен динамический диапазон Holidays

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

3. В ячейке B3 мы вычисляем длительность месяца, выбранного в полях Год и Месяц .

• DATE(Tyear;Tmonth;1) — первый день месяца
• EOMONTH(DATE(Tyear;Tmonth;1);0) — последний день месяца. Функция EOMONTH возвращает дату последнего дня месяца, переданного в первом аргументе, отстоящего на количество месяцев, переданных в качестве второго аргумента (в нашем случае это ноль)
• При вводе этой формулы в ячейку Excel автоматически меняет её формат на Дату, показывая нечто типа «31.01.1900». Необходимо руками указать что тип ячейки General , а не Date .

4. В строке с заголовком Индикатор с D6 до AH6 мы вычисляем нужна ли данная колонка для выбранного месяца (значение 1) или не нужна (значение 0).

Например, для февраля 2014 года столбцы AF:AH , которые предназначены для 29, 30 и 31-го числа месяца, очевидным образом не нужны, поэтому там вы увидите нули в отличие, например, от января 2014-го. Данные индикаторы используются для сигнализации правилам условного форматирования Excel, что данные столбцы форматировать надо (если 1) или не надо (если 0).

Индикатор получаем при помощи функции IF , в которой вычисляется логическое условие в первом аргументе. Если номер текущего столбца, поправленный на величену TOffset , меньше или равен числу дней в выбранном месяце, то формула выдаёт 1, если нет — 0. Просто и наглядно из-за использования именованных ячеек.

5. Далее следует сформировать даты в строке 7.

• Ячейка D7 имеет простейшую формулу =DATE(Tyear;Tmonth;1)
• Ячейки D8 и далее имееют формулы
• Данная формула проверяет индикатор в строке 6 и если оно равно 1 (1 = TRUE для Excel), то вычисляется дата на основе года, месяца, а день берётся из предыдущей ячейки +1
• В противном случае возвращается пустая строка

6. А вот теперь настаёт главное веселье. Сейчас мы начинаем обсуждать то, ради чего писалась, по сути, вся эта статья.

• Войдите в диалоговое окно условного форматирования Home -> Conditional Formatting -> Manage Rules.
• Обязательно выберите Show formatting rules for = This Worksheet , чтобы увидеть все правила форматирования
• На данном экране перечислены все правила форматирования, которые существуют на этом рабочем листе
• В первой колонке ( Rule ) мы видим тип форматирования: в нашем случае это либо Formula: = , либо Cell Value = . То есть используются правила на основе либо формулы (сложный вариант, который вам, возможно, ещё не знаком), либо значения ячейки (самый простейший и понятный всем вариант)
• Во второй колонке ( Format ) вы видите образец форматирования, который будет применяться
• В третьей колонке ( Applies to ) указан диапазон ячеек, которые будут подвергаться форматированию с использованием формата из второй колонки
• В четвёртой колонке ( Stop If True ) выставляется флаг, который отменяет все нижележащие форматирования для ячейки из данного диапазона, если данное правило форматирования сработало

Что же такое форматирование на основе формулы? Это когда Excel применяет настроенное форматирование, если формула условного форматирования (не ячейки, а именно формула внутри правила условного форматирования) принимает ИСТИННОЕ значение.

Давайте посмотрим на правило №1, где введена формула =D$6=1 » для диапазона ячеек =$D$7:$AH$7 «. Данное правило должно применяться для оформления строки дат месяца голубым фоном и белым шрифтом. То есть, если «индикатор» дня (в строке 6) равен 1, то применяется бело-голубое форматирование, а если не равно, то не применяется. Но постойте! Почему в формуле мы видим только ячейку D$6 ? Ведь это подойдёт только для форматирования ячейки D7 , а, к примеру, для правильного форматирования ячейки Z7 , необходимо, чтобы формула ссылалась на ячейку Z6 ! Оказывается, что эту работу Excel проводит за нас и «протягивает» формулу условного форматирования вправо! То есть, форматируя ячейку D7 он смотрит равно ли единице значение в ячейке D6 , форматируя ячейку E7 — смотрит на E6 и так далее.

Для чего перед 6 стоит знак $ в формуле правила? В данном правиле это не выстрелило, но вообще говоря, это команда Excel не менять адрес строки (шестой), если бы мы протягивали формулу условного форматирования не только вправо (смотрите адрес нашего диапазона — =$D$7:$AH$7 ), но и вниз (например целевой диапазон мог бы быть =$D$7:$AH$8 «). Надеюсь, вы понимаете разницу между абсолютными и относительными адресами ячеек Excel.

Правило №2 рисует рамку в диапазоне ФИО (колонки A:C ), если введена фамилия. Прелесть шаблона в том, что форматирование появляется только для заполненных фамилией сотрудника строк.

Правило №3 очень важно. Оно отменяет любое форматирование, если фамилия пуста. Причём данное правило снабжено галкой ( Stop If True ), что отменяет обработку правил, лежащих ниже этого.

Правило №4 рисует рамки в диапазоне D8:AH20 .

Правила №№5-8 меняют фон в зависимости от кода, введенного в ячейку дня. Тут, полагаю, дополнительные комментарии излишни.

Правило №9 отмечает серым выходные, основываясь на формуле =AND(WEEKDAY(D$7;2)>5;D$6=1) «. То есть должны выполняться два условия (функция AND ): первое — дата в строке 7 это выходной (функция WEEKDAY возвращает номер дня недели, а условие требует чтобы день был больше 5, то есть 6 — суббота или 7 — воскресенье), второе — что данная дата «включена» индикатором.

И правило №10 срабатывает когда текущий день находится на листе Праздники , в диапазоне ячеек Holiday . Объяснять нюансы этой формулы в этой статье нецелесообразно, но мы это обязательно сделаем в других.

Как вы могли заметить, форматирование производится до 20-й строки, но никто не мешает вам в ваших шаблонах увеличить эту константу. Жаль только, что Excel не поддерживает динамические диапазоны в поле Applies to правил форматирования. Туда можно подставить именованный диапазон, но он заменится на статическую адресацию.

Ну вот и всё. Надеюсь, вам понравилось и вы оценили всю мощь условного форматирования Excel.

Табель учета рабочего времени в Excel. Унифицированная форма № Т-12&nbsp&nbsp

&nbsp&nbsp&nbsp&nbsp&nbspБывают домовые, а я — ОФИСНЫЙ!

&nbsp&nbsp&nbsp&nbsp&nbspОблегчаю трудовые будни!

Как не потерять качество бизнеса в погоне за количеством

Надоели убытки? Выявляем нерентабельных клиентов

Брать или не брать кредит? Как не попасть в долговую яму

Что нужно знать о кадровых документах

Проверьте, соблюдается ли Трудовой Кодекс в вашей компании

Ежегодный оплачиваемый отпуск. На что вы имеете право?

Как провести презентацию и заслужить доверие аудитории

Семь правил обогащения

Вы нашли полезную информацию и хотите отблагодарить меня?

EXCEL ДЛЯ «ЧАЙНИКОВ» И НЕ ТОЛЬКО

Автоматизированный табель учета рабочего времени Т-12 с буквенными и цифровыми кодами в Excel

ВНИМАНИЕ: Прежде чем пользоваться предложенным автоматизированным табелем, убедитесь в том, что у вас в Excel настроен автоматический режим обновления ячеек. Читайте о том, как его установить.

* Табель работает со следующими кодами явки: 01 (явка), 02 (ночное время), 03 (работа в выходные дни), 04 (сверхурочная работа), 05 (вахтовый метод), 06 (командировка), 12 (сокращенный рабочий день), 21 (сокращенный рабочий день). Если вы используете другие коды явки, необходимо внести изменения в формулу.

** Табель работает со следующими кодами явки: Я (явка), Н (ночное время), РП (работа в выходные дни), С (сверхурочная работа), ВМ (вахтовый метод), К (командировка), УВ (сокращенный рабочий день), ЛЧ (сокращенный рабочий день). Если вы используете другие коды явки, необходимо внести изменения в формулу.

Как автоматизировать табель учета рабочего времени в Excel и работать с ним

Как известно, Табель учета рабочего времени — это главный документ, в соответствии с которым начисляется заработная плата. Т.е. документ этот очень важный и допускать ошибки в нем крайне нежелательно. Мало того, что нужно внимательно занести данные о явках, больничных, отгулах, работе в выходные дни, нужно еще и посчитать общее количество отработанных дней и часов для каждого работника.

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

Главная проблема, с которой мы столкнемся, — это специальные коды, которые используются для обозначения дней. Так день явки обозначается как «Я» или «01», больничный — «Б» или «19», работа в выходной день — «РП» или «03» и т.д. (подробно см. 1 лист Табеля учета рабочего времени)

Каким же образом можно сосчитать буквы или цифры в текстовом формате?

Для того, чтобы сложить значения, необходимо присвоить всем явочным дням значение единицы, а неявочным — нуля.

Для решения этой задачи, вновь воспользуемся логическим оператором «ЕСЛИ», который имеется в функциях Excel (Функции (обведено красным) => Другие функции => Логические => ЕСЛИ).

Логический оператор «ЕСЛИ» поможет нам преобразовать буквенные или числовые выражения в текстовом формате в цифры.

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

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

Ну, а теперь справа от всех полей Табеля начнем заводить формулы с помощью оператора «ЕСЛИ». Выбираем любую ячейку на уровне соответствующего поля таблицы и вызываем оператор.

Заполняем поля оператора.

Смысл операции заключается в том, чтобы присвоить текстовому формату «01» (либо «Я») значение равное «1». Итак, логическое выражение будет выглядеть следующим образом:
если адрес преобразуемой ячейки (в нашем случае D8) = «01», то значение ячейки AV8 будет считаться равным «1»

Прописываем данное выражение в полях оператора. Значение, которому присваивается число (в нашем случае «01») обязательно нужно взять в кавычки:

Поле Значение_если_ложь можно оставить пустым, либо записать в него «0». Мы оставим его пустым.

Теперь только после появления в ячейке D8 значения «01» в ячейке AV8 появится единица. В противном случае мы увидим надпись «ложь» (либо ничего, в случае, если бы мы поставили «0» в поле оператора Значение_если_ложь). Например:

Текстовый формат ячейки в Excel

Стоит обратить внимание на один существенный момент. Если вы вводите в ячейку значение «01», а в ней появляется «1», значит, формат ячейки является числовым, а не текстовым. Его нужно поменять. Иначе формула работать не будет.

Выделяем нужную ячейку, вызываем правой кнопкой мыши меню и выбираем «Формат ячеек». В закладке «Число» выбираем «текстовый» формат.

После смены формата нужно еще раз ввести значение в ячейку.

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

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

В этом случае необходимо обновить ячейку с формулой вручную (щелкнуть по ней два раза и нажать «enter») либо нажать кнопку F9, которая одновременно обновляет весь лист Excel.

Если вы хотите настроить автоматическое обновление страницы (а это просто необходимо сделать в нашем случае, иначе каждый раз придется вручную обновлять все ячейки), зайдите в «Сервис» => «Параметры» => «Вычисления» и выберите вариант «автоматически»:

Итак, мы ввели в ячейку одно условие. Но как быть со всем остальным списком обозначения дней?

Все довольно-таки просто! В строке состояния копируем все выражение кроме знака «=»,

ставим «;» перед последней скобкой выражения и вставляем после него скопированное выражение:

Исправляем во втором выражении «01» на «03» (работа в выходной день):

Готово! Смысл этого выражения будет звучать так:
Если значение ячейки D8 соответствует “01”, то значение ячейки AV8 нужно считать за 1, если нет, то действует новое условие: [если значение ячейки D8 соответствует «03», то значение ячейки AV8 нужно считать за «1»].
И так со всеми последующими условиями.

Теперь добавим любой код дня неявки. Скопируем второе выражение, сразу захватив «;»:

и вставим опять же перед всеми скобками:

Исправляем «03» на «16» (отпуск за свой счет), и «1» на «0», чтобы этот день не учитывался как отработанный:

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

Учтем, что логический оператор «ЕСЛИ» позволяет использовать не более 7-ми аргументов, поэтому если количество используемых вами кодов больше 7-ми, можно проставить коды только для явочных дней. Все остальные коды Excel в любом случае посчитает как «0».

Копируем полученную формулу и вставляем во все ячейки, которые соответствуют ячейкам Табеля учета рабочего времени:

Теперь можно считать итоговые значения. Выделяем ячейку с итоговой суммой за первую половину месяца и с помощью автосуммы складываем значения, полученные в поле справа от Табеля учета рабочего времени:

То же самое делаем со второй половиной месяца.

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

Однако на практике можно столкнуться с тем, что при работе с табелем (копирование, вставка, вырезание ячеек) происходит сбой формата ячеек. Поэтому отметим одну особенность подсчета суммы в Excel.

Для того, чтобы сосчитать значения в любом формате нужно пользоваться ручным суммированием. Автосумма считает только значения в числовом формате.

Используем в нашем табеле эту особенность:

Для этого активизируем ячейку, в которой хотим создать формулу, и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем (выделяем левой клавишей мыши) соответствующие ячейки в следующем порядке:

= [Количество дней(часов) за 1 половину месяца] + [Количество дней(часов) за 2 половину месяца] ENTER

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

С помощью абсолютной ссылки посчитаем дни неявки:

и часы неявки по формуле:

= [Количество дней в месяце] * [Количество часов в день] — [Отработанное количество часов] ENTER

Все полученные формулы копируем и вставляем в соответствующие ячейки табеля до конца списка сотрудников.

Все, что можно было автоматизировать, мы автоматизировали. Теперь можно скрыть столбцы, которые находятся справа от Табеля учета рабочего времени.

Табель с числовыми кодами готов (СКАЧАТЬ)! Пользуйтесь на здоровье.

Для создания табеля с буквенным кодами (СКАЧАТЬ) нужно заменить соответствующие цифровые коды на буквенные.

Если вы найдете способ автоматизировать оставшиеся поля, напишите мне по адресу: ngt@inbox.ru

Остался один маленький нюанс. В случае, если количество сотрудников превышает число строк готового табеля, необходимо скопировать готовый лист и добавить столько его копий в файл, сколько необходимо для внесения всех сотрудников.

Как скопировать лист и вставить в существующий файл

Активизируем нужный нам лист (внизу страницы нажимаем на него левой клавишей мыши), вызываем меню правой клавишей мыши и выбираем «Переместить/скопировать»:

Выбираем лист, перед которым нужно поместить копируемый лист и стмечаем галочкой квадратик «создавать копию»:

Нажимаем ОК и получаем новый лист, полностью идентичный копируемому. Созданная копия помечается цифрой 2, заключенной в скобки — (2).

Полученный лист можно переименовать. Таких листов можно сделать неограниченное количество.

Лучший табель учета рабочего времени в Excel

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

Законом предусмотрено 2 унифицированные формы табеля: Т-12 – для заполнения вручную; Т-13 – для автоматического контроля фактически отработанного времени (через турникет).

Данные вносятся каждый рабочий день. В конце месяца подсчитывается итог по явкам и неявкам каждого работника. Формирование отчета можно упростить, автоматизировав заполнение некоторых ячеек средствами Excel. Посмотрим, как.

Заполняем вводные данные функциями Excel

Формы Т-12 и Т-13 имеют практически одинаковый состав реквизитов.

Скачать табель учета рабочего времени:

В шапке 2 страницы формы (на примере Т-13) заполняем наименование организации и структурного подразделения. Так, как в учредительных документах.

Прописываем номер документа ручным методом. В графе «Дата составления» устанавливаем функцию СЕГОДНЯ. Для этого выделяем ячейку. В списке функций находим нужную и нажимаем 2 раза ОК.

В графе «Отчетный период» указываем первое и последнее число отчетного месяца.

Отводим поле за пределами табеля. Здесь мы и будем работать. Это поле ОПЕРАТОРА. Сначала сделаем свой календарик отчетного месяца.

Красное поле – даты. На зеленом поле проставляет единички, если день выходной. В ячейке Т2 ставим единицу, если табель составляется за полный месяц.

Теперь определим, сколько рабочих дней в месяце. Делаем это на оперативном поле. В нужную ячейку вставляем формулу =СЧЁТЕСЛИ(D3:R4;»»). Функция «СЧЁТЕСЛИ» подсчитывает количество непустых ячеек в том диапазоне, который задан в скобках.

Вводим вручную порядковый номер, ФИО и специальность сотрудников организации. Плюс табельный номер. Информацию берем из личных карточек работников.

Автоматизация табеля с помощью формул

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

Для примера возьмем такие варианты:

• В – выходной;
• Я – явка (рабочий день);
• ОТ – отпуск;
• К – командировка;
• Б – больничный.

Сначала воспользуемся функцией «Выбор». Она позволит установить нужное значение в ячейку. На этом этапе нам понадобится календарь, который составляли в Поле Оператора. Если на какую-то дату приходится выходной, в табеле появляется «В». Рабочий – «Я». Пример: =ВЫБОР(D$3+1;»Я»;»В»). Формулу достаточно занести в одну ячейку. Потом «зацепить» ее за правый нижний угол и провести по всей строке. Получается так:

Теперь сделаем так, чтобы в явочные дни у людей стояли «восьмерки». Воспользуемся функцией «Если». Выделяем первую ячейку в ряду под условными обозначениями. «Вставить функцию» – «Если». Аргументы функции: логическое выражение – адрес преобразуемой ячейки (ячейка выше) = «В». «Если истина» — «» или «0». Если в этот день действительно выходной – 0 рабочих часов. «Если ложь» – 8 (без кавычек). Пример: =ЕСЛИ(AW24=»В»;»»;8). «Цепляем» нижний правый угол ячейки с формулой и размножаем ее по всему ряду. Получается так:

Нужно проделать такую же работу для второй половины месяца. Достаточно скопировать формулы и поменять те ячейки, на которые они ссылаются. Результат работы:

Теперь подведем итоги: подсчитаем количество явок каждого работника. Поможет формула «СЧЁТЕСЛИ». Диапазон для анализа – весь ряд, по которому мы хотим получить результат. Критерий – наличие в ячейках буквы «Я» (явка) или «К» (командировка). Пример: . В результате мы получаем число рабочих для конкретного сотрудника дней.

Посчитаем количество рабочих часов. Есть два способа. С помощью функции «Сумма» — простой, но недостаточно эффективный. Посложнее, но надежнее – задействовав функцию «СЧЁТЕСЛИ». Пример формулы: . Где AW25:DA25 – диапазон, первая и последняя ячейки ряда с количеством часов. Критерий для рабочего дня («Я»)– «=8». Для командировки – «=К» (в нашем примере оплачивается 10 часов). Результат после введения формулы:

Все формулы копируем и вставляем в соответствующие ячейки по всему списку сотрудников. При заполнении такого табеля нужно будет корректировать условные обозначения по работникам.

Если изменяется календарь, то меняются выходные и явки. Вручную проставляем прогулы, отгулы и т.п. Все остальное посчитается автоматически.

Табель учета рабочего времени в excel. Учет времени работы сотрудников. Скачать пример

24.11.2018 табель был обновлен до версии 2.0 с учетом всех Ваших пожеланий и замечаний. Скачивайте, пользуйтесь, оставляйте свои комментарии под статьей.

Здравствуйте, дорогие друзья!

Сегодня я представлю Вашему вниманию свой вариант реализации одной из насущных проблем – табель учета рабочего времени в excel. В данной статье Вы найдете описание основных концепций построения табеля, инструкцию по его ведению, а также сможете скачать программу с помощью ссылок в начале и в конце статьи. Скачивайте, пользуйтесь, автоматизируйте свой ручной труд =).

Построение табеля учета рабочего времени в excel

Скачав и открыв табель, Вы попадаете на главную страницу построения «Настройки» (Рисунок 1).

Начиная со второй строки, необходимо заполнить список сотрудников, по которым Вы будете вести табель. Укажите уникальный номер сотрудника, ФИО, должность(не обязательно), норму рабочего дня в часах(не обязательно). Норма рабочего дня используется при расчете переработок и недоработок сотрудников в течении месяца, если Вы не заполнили это поле вначале, его можно будет отредактировать в построенном табеле. В ячейках «F2» и «F3» с помощью выпадающего списка выберите год и месяц для построения. Выпадающий список появляется при выборе ячейки.

После того как все данные введены нажмите кнопку «Создать табель» и программа сгенерирует табель учета рабочего времени за указанный месяц и год (Рисунок 2).

Табель учета рабочего времени в excel состоит из следующих областей:

1. Легенда таблицы – строка 1, в которой указан месяц и год построения, а также условные обозначения табеля.
2. Список сотрудников (колонка «А»), заполненный нами на листе «Настройки».
3. Шапка таблицы с проставленными числами и днями недели, выходные дни (Сб и Вс) для наглядности выделены светло-зеленым цветом.
4. Область управления, в которой проставляются условные обозначения.
5. Расчетные столбцы – столбцы формул для учета основных показателей.

Ведение табеля учета рабочего времени в excel

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

• Рабочий день отмечается цифрой от 1 до 24, которая обозначает количество часов, отработанных сотрудником за день. Отмеченный день автоматически подкрашивается светло-синим цветом с помощью правил условного форматирования.
• День отпуска отмечается сокращением оп. Автоматически подкрашивается светло-розовым цветом.
• Праздничный день отмечается сокращением пр. Автоматически подкрашивается салатовым цветом.
• Прогул отмечается обозначением пг. Автоматически подкрашивается серым цветом.
• Отгул обозначается сокращением ог. Автоматически подкрашивается бледно-оранжевым цветом.
• Отпуск без содержания обозначается сокращением обс. Автоматически подкрашивается сиреневым цветом.
• Учебный отпуск обозначается буквой у. Обозначение подкрашивается желтым цветом.
• Командировка обозначается буквой к. Подкрашивается зеленым цветом.
• Больничный отмечается маленькой буквой б. Автоматически подкрашивается светло-желтым цветом.

Пример заполненного табеля учета рабочего времени в excel можно увидеть на Рисунке 3 ниже:

Формульный блок расчетных показателей (столбцы «AG:AJ») добавлен для удобства подведения месячных итогов. Данный блок состоит из четырех столбцов:

1. Рабочих дней – количество дней, отработанных сотрудником за отчетный месяц (количество синих ячеек).
2. Рабочих часов – количество часов, отработанных сотрудником в отчетном месяце (сумма значений синих ячеек).
3. Дней отпуска – количество дней отпуска, использованных сотрудником за отчетный месяц (количество ячеек с обозначением оп).
4. Дней больнич.– количество дней, которые сотрудник провел на больничном (количество ячеек с обозначением б).
5. Перераб. часов — кол-о часов переработки. Считает часы, которые превышают норму дневной выработки, указанной в столбце «раб. день (ч.)»
6. Недораб. часов — кол-о часов, которых не достает до нормы дневной выработки. Пример расчета: норма 8ч, стоит 6ч, недоработка — 2ч; норма 8ч, стоит 12ч, переработка — 4ч; норма 8ч, стоит 1й день 6ч, 2й день 10ч, переработка — 0ч, недоработка — 0ч.
7. Отгулов — количество дней,в которые сотрудник брал отгулы (количество ячеек ог).
8. Празд. дней — кол-о праздничных дней в месяце. Праздничные дни отмечаются сокращением пр.
9. Прогулов дней — кол-о прогулов, отмечается сокращением пг.
10. Отп. без содержан. — кол-о дней, которые сотрудник провел в отпуске без содержания. Обозначение — обс.
11. Учебного отпуска — кол-о дней учебного отпуска, обозначение у.
12. Дней командир. — кол-о дней, которые сотрудник провел в командировке. Обозначение к.

Послесловие

Благодарю Вас за прочтение данной статьи =). Жду Ваших отзывов и комментариев ниже. Скачивание программы является абсолютно бесплатным. Буду рад, если данная программа окажется полезной для Вас, с удовольствием отвечу на Ваши вопросы в комментариях.
Табель учета рабочего времени является скромной демонстрацией моих навыков программирования и работы в Excel. Более 10 лет профессионально занимаюсь программированием в области автоматизации как в Excel, так и в веб сфере (написание сайтов, парсинг сайтов, написание ботов, имитация действий пользователя и т.д.). Готов взяться за любую задачу, связаться со мной можно через сайт или написать на почту: goryaninov@bk.ru
Скачать табель учета рабочего времени в excel.

24.11.2018 табель учета рабочего времени в excel был обновлен до версии 2.0 с учетом всех Ваших пожеланий и замечаний. Скачивайте, пользуйтесь, оставляйте свои комментарии под статьей.

Обучающее видео

Каталог статей

Форма Табеля взята с КонсультантПлюс, немного переделана для улучшения визуального восприятия, данные табеля перенесены на один лист, закреплена шапка, установлены разрыв листа и печать заголовков, добавлены формулы для расчета отработанных и пропущенных дней, часов.
Добавлен лист «Часы» (норма рабочих часов в день на ставку по должностям), на ячейки которого ссылаются формулы с листа «Табель», его можно скрыть. При ссылках на ячейки листа «Часы» следует добавлять символ $ перед значениями столбца и строки для закрепления адреса ячейки при копировании формул.

Титульный лист:

Количество рабочих дней в месяце может рассчитываться по формуле:
=ЧИСТРАБДНИ(ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1);ДАТА(ГОД(СЕГОДНЯ());
МЕСЯЦ(СЕГОДНЯ())+1;1)-1)
Но в случае наличия большого количества праздничных дней (например, в январе), формула легко не работает. Поэтому воспользуемся простым способом: сделаем значение этой ячейки равным значению отработанных дней кого-либо с основного листа табеля. Но учитывая, что именно этот сотрудник может отработать неполный месяц, возьмем максимальное значение из заданного диапазона, например, несколько ячеек с разных листов табеля:
=МАКС(‘фев (2)’!BX10:CD11;’фев (2)’!BX14:CD15;’фев (2)’!BX30:CD31;’фев (2)’!BX38:CD39;’фев
(2)’!BX51:CD52)
Если есть декретницы, можно поступить проще: взять сумму пропущенных ими рабочих дней за две половины месяца:
=СУММ(‘фев (2)’!DD194:DK194;’фев (2)’!DD196:DK196)
В любом случае надо проследить, чтобы значение получилось верным. Этот метод имел бы смысл, если вы каждый месяц вносите исправления в один и тот же лист (здесь лист «табель»), но я предпочитаю создавать копию листа «табель» для каждого следующего месяца, переименовав лист соответственно, так что проще проставить количество рабочих дней на титульном листе «от руки».

Табель подписывается последним числом месяца: =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;0)
— получаем 30, 31 или 28(29) число месяца.

Текущий месяц высчитывается по формуле:
=ВПР(МЕСЯЦ(СЕГОДНЯ()); <1;"января":2;"февраля":3;"марта":4;"апреля":5;"мая":6;"июня":7;"июля":8;"августа":9;"сентября":
10;»октября»:11;»ноября»:12;»декабря»>;2;)
В формуле прописываем нужное окончание месяца (январЬ или январЯ).

Последние две цифры текущего года:
=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;0)

Полностью дата (последнее число текущего месяца текущего года):
=КОНМЕСЯЦА(СЕГОДНЯ();0)

Лист «Табель»

Диапазон столбцов «Отметки о неявках на работу по числам месяца»:
отработанные дни — пустые ячейки. Вручную проставляем выходные — В, пропуски: А — свой счет, Б — болезнь, О и ОУ — отпуска; 0 (ноль часов) — уволился.
Диапазон столбцов «Отработано за«:
за половину месяца отработанные дни рассчитываются по формуле:
=СЧИТАТЬПУСТОТЫ(AW10:BL10)
То есть считаем пустые ячейки в диапазоне AW10:BL10 дней половины месяца;

отработанные часы считаем по формуле:
=BM10*часы!$AO$10
, где BM10— адрес ячейки с отработанными днями за полмесяца, который умножаем на часы!$AO$10 — адрес ячейки на листе «часы», где указана норма часов в день по должности. В адрес ячейки добавляем знак $ для закрепления адреса при копировании формулы.

За месяц количество отработанных дней получаем простым сложением отработанных дней за две половины месяца, например:
=BM10+BM12
, где BM10 и BM12 — адреса ячеек с количеством отработанных дней за первую и вторую половины месяца.

Количество отработанных часов за месяц можно получить, складывая часы за полмесяца:
=BM11+BM13
, или умножая количество отработанных дней на норму часов в день по должности с листа «часы»:
=BX18*часы!$AO$34.

Заполнение диапазона столбцов «Неявки за месяц»:

Формулой
=ЕСЛИ(СУММ(СЧЁТЕСЛИ(AW10:BL10;<"ОУ";"Б";"А";"О";0>))=0;»»;СУММ(СЧЁТЕСЛИ(AW10:BL10;
<"ОУ";"Б";"А";"О";0>)))
считаем пропущенные рабочие дни,

а формулой
=ЕСЛИ(СУММ(СЧЁТЕСЛИ(AW10:BL10;<"ОУ";"Б";"А";"О";0>))=0;»»;СУММ(СЧЁТЕСЛИ(AW10:BL10;
<"ОУ";"Б";"А";"О";0>))*часы!$AO$10)
считаем пропущенные рабочие часы.

Аналогично считаем пропущенные педагогические рабочие дни и часы.
В данных формулах подсчитываем количество ячеек с обозначениями пропусков: (см. условные обозначения на титульном листе) О, ОУ, А или Б:
СЧЁТЕСЛИ(AW10:BL10;<"ОУ";"Б";"А";"О">)
Если в учреждении встречаются другие виды пропусков, можно добавить их обозначения в формулу через запятую.

P.S. Если вдруг человек увольняется или поступает на работу посреди месяца (ставим ноль в табеле), добавляем 0 (ноль) в перечень условных обозначений: .

В этих же формулах решаем проблему с отображением на листе нулевых значений, то есть, чтобы не выводился «0,00», если у сотрудника нет пропусков. Задаем условие: если количество ячеек с данными в виде О, ОУ, А или Б равно нулю, то в ячейке с формулой не выводятся никакие данные (двойные кавычки «» в формуле), иначе выводится число пропусков. Важно: в формуле не прописываем выходные (В), чтобы они не считались неявками.

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

Подсчет часов и дней замещения логично автоматизировать только у сотрудников, кому увеличение объема работ или совмещение установлено по приказу на срок тарификации (в примере это машинист по стирке, рабочий по КОРЗ за работу на несколько зданий). Не систематические замещения кратковременно отсутствующих сотрудников высчитывать формулами смысла нет.

P/S На момент публикации (январь 2018г.) в образце приведен пример табеля за февраль 2018 года, но на титульном листе даты выводятся январские, а количество рабочих дней взято с листа «табель» за февраль. Соответственно, в каком месяце вы скачаете данный файл, такие даты на титульном листе у вас и будут, т.к. задействована формула СЕГОДНЯ(), но наполнение листа «табель» не изменится.

При загрузке на гугл-диск все формулы переносятся и работают отлично, кроме формул для подсчета пропущенных дней и часов. Считает только значение, которое прописано в формуле первым, в данном случае это «ОУ».

Формула подсчета нескольких значений тоже не работает:

Заработала формула только в таком виде:

=ЕСЛИ(СУММ(СЧЁТЕСЛИ(AW10:BL10;»ОУ»);СЧЁТЕСЛИ(AW10:BL10;»Б»);СЧЁТЕСЛИ(AW10:BL10;»А»);
СЧЁТЕСЛИ(AW10:BL10;»О»))=0;»»;СУММ(СЧЁТЕСЛИ(AW10:BL10;»ОУ»);СЧЁТЕСЛИ(AW10:BL10;»Б»);
СЧЁТЕСЛИ(AW10:BL10;»А»);СЧЁТЕСЛИ(AW10:BL10;»О»)))
то есть, каждое значение неявки считать отдельно.

Ваши замечания и предложения прошу оставлять в комментариях.