как сделать формулу в excel для табеля учета рабочего времени
Лучший табель учета рабочего времени в Excel
Табель учета рабочего времени – основной документ, содержащий информацию о числе явок и неявок на работу каждого сотрудника фирмы. Он передается в бухгалтерию. И на основании данных рассчитывается и начисляется заработная плата.
Законом предусмотрено 2 унифицированные формы табеля: Т-12 – для заполнения вручную; Т-13 – для автоматического контроля фактически отработанного времени (через турникет).
Данные вносятся каждый рабочий день. В конце месяца подсчитывается итог по явкам и неявкам каждого работника. Формирование отчета можно упростить, автоматизировав заполнение некоторых ячеек средствами Excel. Посмотрим, как.
Заполняем вводные данные функциями Excel
Формы Т-12 и Т-13 имеют практически одинаковый состав реквизитов.
Скачать табель учета рабочего времени:
В шапке 2 страницы формы (на примере Т-13) заполняем наименование организации и структурного подразделения. Так, как в учредительных документах.
Прописываем номер документа ручным методом. В графе «Дата составления» устанавливаем функцию СЕГОДНЯ. Для этого выделяем ячейку. В списке функций находим нужную и нажимаем 2 раза ОК.
В графе «Отчетный период» указываем первое и последнее число отчетного месяца.
Отводим поле за пределами табеля. Здесь мы и будем работать. Это поле ОПЕРАТОРА. Сначала сделаем свой календарик отчетного месяца.
Красное поле – даты. На зеленом поле проставляет единички, если день выходной. В ячейке Т2 ставим единицу, если табель составляется за полный месяц.
Теперь определим, сколько рабочих дней в месяце. Делаем это на оперативном поле. В нужную ячейку вставляем формулу =СЧЁТЕСЛИ(D3:R4;»»). Функция «СЧЁТЕСЛИ» подсчитывает количество непустых ячеек в том диапазоне, который задан в скобках.
Вводим вручную порядковый номер, ФИО и специальность сотрудников организации. Плюс табельный номер. Информацию берем из личных карточек работников.
Автоматизация табеля с помощью формул
Первый лист формы содержит условные обозначения для учета рабочего времени, цифровые и буквенные. Смысл автоматизации средствами Excel в том, чтобы при внесении обозначения отображалось количество часов.
Для примера возьмем такие варианты:
Сначала воспользуемся функцией «Выбор». Она позволит установить нужное значение в ячейку. На этом этапе нам понадобится календарь, который составляли в Поле Оператора. Если на какую-то дату приходится выходной, в табеле появляется «В». Рабочий – «Я». Пример: =ВЫБОР(D$3+1;»Я»;»В»). Формулу достаточно занести в одну ячейку. Потом «зацепить» ее за правый нижний угол и провести по всей строке. Получается так:
Нужно проделать такую же работу для второй половины месяца. Достаточно скопировать формулы и поменять те ячейки, на которые они ссылаются. Результат работы:
Все формулы копируем и вставляем в соответствующие ячейки по всему списку сотрудников. При заполнении такого табеля нужно будет корректировать условные обозначения по работникам.
Если изменяется календарь, то меняются выходные и явки. Вручную проставляем прогулы, отгулы и т.п. Все остальное посчитается автоматически.
Табель учета рабочего времени в excel. Учет времени работы сотрудников. Скачать пример
Табель учета рабочего времени в excel 2021
Табель учета рабочего времени в excel 2021 позволит существенно сократить трудозатраты на ведение личного учета или учета трудовой деятельности Ваших сотрудников. Табель состоит из 5 блоков: основная информация о работнике (ФИО, должность, норма раб дня и т.д.), календарь для расстановки рабочих активностей (раб. часы, больничные, отпуска и т.д.), а также 3 расчетных блока (учет активностей, расчет переработок, расчет заработной платы). В данной статье Вы найдете описание основных концепций построения табеля, инструкцию по его ведению, а также сможете скачать саму программу абсолютно бесплатно с помощью ссылок в начале и в конце статьи. Скачивайте, пользуйтесь, автоматизируйте свой рутинный ручной труд.
31.03.2021 табель был обновлен до версии 3.2 с учетом всех Ваших пожеланий и замечаний. Добавлены новые расчетные столбцы, в том числе, вычет НДФЛ, накопительный итог отработанных дней/часов за год. Появилась функция формирования расчетных листов по итогам месяца. Добавлена кнопка «Обновить», которая позволяет скорректировать штат сотрудников в течении месяца и обновить его в построенном табеле. Скачивайте, оставляйте свои комментарии под статьей или пишите мне лично, помогу доработать табель учета рабочего времени в excel под Ваши конкретные задачи. Мои контакты: goryaninov@bk.ru, +79507094770
Обучающее видео по работе с табелем учета рабочего времени
Подписывайтесь на YouTube канал ExcelLab, ставьте колокольчик, чтобы не пропустить выход новых обновлений табеля учета рабочего времени в excel. Обновления будут выходить с частотой 1-2 раза в месяц. Также будет много другого полезного софта по автоматизации, обучающие ролики, полезные приемы и хитрости Excel.
Построение табеля учета рабочего времени в excel 2021
Табель представляет собой самый обычный документ Excel с поддержкой макросов формата xlsm. После скачивания Вы увидите архив, который можно распаковать любым архиватором, например, WinRar или 7-Zip. Открыв табель, в верхней части документа, Вы поочередно увидите два стандартных сообщения от системы безопасности. Нажмите «Разрешить редактирование» и «Включить содержимое», чтобы разрешить работу макросу построения табеля.
Далее Вы попадаете на главную страницу построения «Настройки» (рисунок ниже). Чтобы лучше рассмотреть изображение кликните по картинке для ее увеличения.
Табель по умолчанию заполнен тестовыми данными. Если хотите пропустить этап ввода информации и посмотреть функционал документа, нажмите кнопку «Создать табель». Для того чтобы стереть тестовые данные нажмите кнопку «Очистить табель».
Заполните список сотрудников или введите свои данные, если ведете личный учет трудозатрат. Укажите порядковый номер записи, уникальный табельный номер сотрудника, введите ФИО, должность(не обязательно), выберите из выпадающего списка вид оплаты (оклад, часовая ставка), введите размер оклада или часовой ставки, заполните норму рабочего дня в часах. Норма рабочего дня используется при расчете переработок и недоработок в конце месяца.
Область настроек построения расположена под кнопкой «Создать табель». С помощью выпадающего списка выберите год и месяц учета, а также режим построения. Табель учета рабочего времени имеет три режима построения: стандартный, день/ночь, день/ночь+. Режимы типа «День/Ночь» создают расчет с разделением рабочих часов на дневные и ночные. Подробнее о режимах построения будет изложено далее.
После того как все данные введены нажмите кнопку «Создать табель» и программа сгенерирует табель учета рабочего времени за указанный месяц и год. Если у Вас не отображается кнопка построения, нажмите комбинацию клавиш «Alt+F8» и выполните макрос CreateTimesheet_click.
Управление списком сотрудников
Перед началом ввода данных необходимо удалить тестовые записи, нажав кнопку «Очистить табель». После процедуры очистки останется пустой список со строкой Итого, которую при необходимости также можно удалить, выбрав любую ячейку строки и нажав кнопку «Удалить элемент». Начать ввод можно с первой пустой строки.
Табель учета рабочего времени в excel 2021 имеет ряд полезных функций управления списком сотрудников, чтобы облегчить его редактирование и сделать более наглядным.
Используйте функцию «Добавить группу» если у Вас большой штат сотрудников и вы хотите разделить список на отделы, или же сгруппировать работников по сфере занятости. Выберите любую ячейку первой пустой строки, в которую хотите поместить строку с названием группы и нажмите соответствующую кнопку. Новая группа добавляется как заголовок подсписка, поэтому сначала необходимо добавить новую группу, а потом ввести данные ее участников. Функция не является обязательной, табель допускает ввод списка без групп если вы ведете табель для личного учета или у Вас нет необходимости делить список.
Функция «Добавить запись» вставляет пустую строку между любыми элементами списка. Например, если Вы хотите добавить нового сотрудника в уже сформированный список. Выберите любую ячейку строки, в которую хотите вставить новую запись, и нажмите соответствующую кнопку.
Описание структуры табеля
Построим табель в режиме «Стандартный» и рассмотрим его подробнее. Для удобства разделим его на 2 части. Рассмотрим первое изображение, кликните по картинке для ее увеличения.
Первая часть табеля учета рабочего времени в excel состоит из следующих областей:
Рассмотрим второе изображение, кликните по картинке для ее увеличения.
Вторая часть табеля учета рабочего времени в excel состоит из следующих областей:
Ведение табеля учета рабочего времени в excel
Табель учета рабочего времени необходимо вести, используя стандартные условные обозначения:
Пример заполненного табеля учета рабочего времени в excel в режиме «День/Ночь» можно увидеть на рисунке ниже. Кликните по картинке для ее увеличения.
Режимы построения табеля учета рабочего времени 2021
Табель учета рабочего времени в excel имеет три режима построения:
Послесловие
Благодарю Вас за прочтение данной статьи =). Жду Ваших отзывов и комментариев ниже. Скачивание программы является абсолютно бесплатным. Буду рад, если данная программа окажется полезной для Вас, с удовольствием отвечу на Ваши вопросы в комментариях.
Каталог статей
Титульный лист:
Количество рабочих дней в месяце может рассчитываться по формуле:
=ЧИСТРАБДНИ(ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1);ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;1)-1;Праздники20)
где исключаются праздничные дни, указанные где-нибудь в диапазоне, именованном здесь как «Праздники20». Здесь нужно вручную забить все праздничные и нерабочие дни года из производственного календаря
Табель подписывается последним числом месяца: =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;0)
— получаем 30, 31 или 28(29) число месяца.
Текущий месяц высчитывается по формуле:
=ВПР(МЕСЯЦ(СЕГОДНЯ()); <1;"января":2;"февраля":3;"марта":4;"апреля":5;"мая":6;"июня":7;"июля":8;"августа":9;"сентября":
10;»октября»:11;»ноября»:12;»декабря»>;2;)
В формуле прописываем нужное окончание месяца (январЬ или январЯ).
Последние две цифры текущего года:
=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;0)
Полностью дата (последнее число текущего месяца текущего года):
=КОНМЕСЯЦА(СЕГОДНЯ();0)
или, если требуется именно последний РАБОЧИЙ день, исключая праздничные/нерабочие дни: =РАБДЕНЬ(КОНМЕСЯЦА(СЕГОДНЯ();0)+1;-1;Праздники2020)
Лист «Табель»
Количество отработанных часов за месяц можно получить, складывая часы за полмесяца:
=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;»О»)))
то есть, каждое значение неявки считать отдельно.
Ваши замечания и предложения прошу оставлять в комментариях.
Последние публикации
Новости
Главные темы месяца
Отписаться от сообщений с форума
В работе Вам помогут:
«Кадровик-практик»
по кадровому
делопроизводству
и книги учета
Бесплатные ресурсы
Пошаговые инструкции
Образцы документов
Статьи и консультации
Разъяснения Роструда, НПА
Подпишитесь на наши рассылки:
— новые публикации,
— новости законодательства
Познавайте кадровый учет сами с нашим самоучителем по кадровому делопроизводству
Кадровый аудит своими силами. Найдите у себя эти ошибки и исправьте их
Фотообои с производственным календарем на 2021 год
Опросы по сложным вопросам кадровой практики
Как сделать формулу в excel для табеля учета рабочего времени
Все, кто работал в средних и крупных компаниях, наверняка сталкивались с таким документом, как табель учёта рабочего времени. Его заполняют руководители групп или отделов и сдают в отдел кадров. Хорошо, если последний создаёт его на год и распространяет в виде шаблона, но часто происходит доработка своего старого шаблона каждым ответственным.
В данной статье я хотел бы показать, как создать максимально универсальный шаблон табеля рабочего времени и попутно продемонстрировать ряд технологий применения условного форматирования и некоторых формул рабочего листа.
Готовый шаблон можно скачать тут.
Чем удобен данный шаблон:
Попробуйте поизменять ячейку Месяц в шаблоне для 2014 года, чтобы убедиться в корректном динамическом формировании всех колонок.
Введите несколько фамилий, чтобы убедиться, что форматирование строк появляется автоматически, а также подсвечиваются празники и выходные.
Теперь настало время разобраться, как же работает всё это «волшебство».
1. Для начала сделайте видимыми скрытые строки 3, 4 и 6.
2. Обратите внимание, что в Менеджере имён четырём ячейкам и одному диапазону даны символьные имена.
* Почему следует давать ячейкам, участвующим в формулах, символьные имена и что такое динамический диапазон, мы обязательно поговорим, в других статьях.
4. В строке с заголовком Индикатор с D6 до AH6 мы вычисляем нужна ли данная колонка для выбранного месяца (значение 1) или не нужна (значение 0).
5. Далее следует сформировать даты в строке 7.
6. А вот теперь настаёт главное веселье. Сейчас мы начинаем обсуждать то, ради чего писалась, по сути, вся эта статья.
Что же такое форматирование на основе формулы? Это когда Excel применяет настроенное форматирование, если формула условного форматирования (не ячейки, а именно формула внутри правила условного форматирования) принимает ИСТИННОЕ значение.
Правило №2 рисует рамку в диапазоне ФИО (колонки A:C ), если введена фамилия. Прелесть шаблона в том, что форматирование появляется только для заполненных фамилией сотрудника строк.
Правило №3 очень важно. Оно отменяет любое форматирование, если фамилия пуста. Причём данное правило снабжено галкой ( Stop If True ), что отменяет обработку правил, лежащих ниже этого.
Правила №№5-8 меняют фон в зависимости от кода, введенного в ячейку дня. Тут, полагаю, дополнительные комментарии излишни.
Как вы могли заметить, форматирование производится до 20-й строки, но никто не мешает вам в ваших шаблонах увеличить эту константу. Жаль только, что Excel не поддерживает динамические диапазоны в поле Applies to правил форматирования. Туда можно подставить именованный диапазон, но он заменится на статическую адресацию.
Ну вот и всё. Надеюсь, вам понравилось и вы оценили всю мощь условного форматирования Excel.