Как сделать табель учета рабочего времени в excel автоматически
Табель учета времени excel 2021 — почасовой учет занятости сотрудников («Time Sheet»). Позволяет детализировать трудозатраты персонала в разрезе дня, проанализировав которые можно дать точную оценку эффективности работы.
Текущая версия табеля от 21.01.2021. Скачивайте, пользуйтесь, оставляйте свои комментарии под статьей.
Если Вам нужен стандартный табель учета рабочих дней/часов, Вы можете подобрать и скачать подходящий перейдя по ссылкам статей:
— Табель учета рабочего времени в днях
— Табель учета рабочего времени в днях по форме Т-13
Новый почасовой табель учета времени excel 2021
Кризисное время требует от современного менеджмента принятие эффективных управленческих решений. Основой для принятия любого взвешенного решения служат актуальные структурированные данные. Упростить процесс сбора и структурирования информации должны высокотехнологичные точные инструменты — программы. В данной статье мы рассмотрим один из новейших инструментов – табель учета времени excel 2021 по принципу Time Sheet. На нашем сайте Вы также сможете скачать табель учета времени excel 2021 бесплатно и безопасно.
Название Time Sheet берет свое начало в английском языке от слова Time — время и Sheet – лист, является аналогом нашего понятия — табель. Табель учета времени excel 2021 – это таблица почасового учета рабочего времени сотрудника, которая разбивается на список задач и время их выполнения. Данная интерпретация учета рабочего времени excel обеспечивает возможность детально рассмотреть трудозатраты персонала по типам активностей на любом временном отрезке, будь то рабочий день, неделя, месяц, квартал или год.
Как говорится, от слов к делу! Давайте рассмотрим табель примере изображения листа «Табель», приведенного ниже:
Ведение табеля учета времени excel 2021
Табель учета ведется каждым сотрудником по отдельности и состоит из двух листов: «Табель» и «Справочник». Давайте для удобства разделим лист «Табель» на области и рассмотрим заполнение каждой из них.
- Зеленая зона – общая информация о сотруднике: ФИО сотрудника, занимаемая должность.
- Красная зона – список задач. Заполняется номер задачи (порядковый номер, либо номер из системы учета задач, если такая используется), задача и активность (или тип задачи). Столбец Активность – поле с выпадающим списком, который подтягивается с листа «Справочник», что дает возможность завести любое количество активностей, актуальных для Ваших сотрудников, но которые Вы хотели бы поделить задачи. Данная классификация позволит нам в дальнейшем проанализировать трудозатраты персонала по типу активности за любой расчетный период.
- Желтая зона – трудозатраты (часы). Сотрудник оценивает свои временные затраты по каждой задаче и вписывает их в соответствующие ячейки. Для удобства в 7й строке и столбце «Итого» автоматически считаются суммы трудозатрат по дню и по задаче.
- Синяя зона – управление отображаемым периодом. Для того чтобы отобразить данные за конкретный период выберите даты «с-по» и нажмите кнопку «Обновить». В таблице отобразятся актуальные данные за выбранный период, автоматические суммы пересчитаются.
Для добавления новой задачи в табель выберите первую пустую строку в конце списка задачи и введите необходимые данные. Не используйте вставку строки, чтобы не нарушить порядок расчетов. Удаление задачи осуществляется с помощью операции удаления строки. Задача может быть удалена из любой части табеля.
Пример листа «Справочник» программы Табель учета времени excel 2021 приведен ниже:
На листе «Справочник» необходимо ввести активности, на которые Вы хотите разделить трудозатраты Вашего персонала. Список автоматически подтянется в поле выбора «Активность» листа «Табель».
Послесловие
Спасибо за прочтение данной статьи! Это лишь первая часть макроса для сбора данных сотрудников. Скоро выйдет вторая менеджерская часть макроса, в которой будет реализована возможность анализа данных от сотрудников, построение сводных, отчетов и т.д. Следите за обновлениями! Скачивание программы является абсолютно бесплатным. Буду рад, если данная программа окажется полезной для Вас, с удовольствием отвечу на Ваши вопросы в комментариях.
Текущая версия табеля от 21.01.2021. Скачивайте, пользуйтесь, оставляйте свои комментарии под статьей.
Если зашли на сайт в поисках табеля учета рабочего времени, чтобы научится тому, как его сделать в программе Excel или просто скачать готовый табель в формате Excel, можно утверждать с уверенностью 90%, что Вы знаете, для чего нужен табель учета рабочего времени и что он себя представляет. На тот случай, если я ошибаюсь, и Вы не представляете, что это за документ — краткая справка о его предназначении:
Табель (так называемая форма Т-12) ведется организациями для учета отработанного работником времени за определенный период. Форма Т-12 утверждена приказом N 52Н Минфина от 30.03.2015 г. , и должна заполняться в соответствии с данным приказом.
- Заголовок и номер табеля;
- Информация о периоде вносимых данных;
- КОДЫ (Форма по ОКУД, Дата по ОКПО, Номер корректировки);
- Наименование учреждения;
- Наименование структурного подразделения;
- Вид табеля: первичный или корректирующий;
- ФИО, должность сотрудника и учетный номер;
- Даты и количество отработанных дней и часов;
- Подписи исполнителей и отметка бухгалтерии.
На отдельной вкладке «Автоматизация учета» создадим таблицу, которая будет автоматически рассчитывать время работы сотрудников.
На вкладке «Календарь» создаем вспомогательную таблицу для расчета количества рабочих и выходных дней в месяце.
В таблице «Автоматизация учета» применяем следующие функции и методы:
- В ячейках N1-T1 создаем выпадающие списки с названиями месяцев и лет. Это упростит выбор и избавит от внесения некорректных значений с ошибками и пробелами.
- В ячейках B3-B5 при помощи функций СЦЕПИТЬ и ВПР «подтягиваем» количество дней из вкладки «Календарь».
=ВПР(СЦЕПИТЬ(N1;» «;R1);Календарь!C:F;4;0)
- Рабочее время вычисляем умножением отработанных дней(смен) на количество часов в смене.
- Ввод дней недели автоматизируем при помощи функции =ЕСЛИОШИБКА(ДЕНЬНЕД(СЦЕПИТЬ(C12;» «;$N$1;» «;$R$1));»-«) ,
Табель учета рабочего времени – основной документ, содержащий информацию о числе явок и неявок на работу каждого сотрудника фирмы. Он передается в бухгалтерию. И на основании данных рассчитывается и начисляется заработная плата.
Законом предусмотрено 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 как показано ниже на примере.
Составляющие баланса рабочего времени
Данный анализ содержит ожидаемые показатели и фактическое использование рабочего времени. Единицы измерения – человеко-дни / человеко-часы.
- календарный фонд = количеству дней в году;
- номинальный (табельный) = все дни минус выходные и праздники;
- максимально возможный = номинальный минус дни отпуска;
- явочный = максимально возможный минус неявки;
- фактический = явочный минус простои.
Такой отчет составляется ежемесячно для каждого структурного подразделения. Впоследствии плановое время сравнивается с фактическим временем. Показатели анализируются в абсолютных величинах и в коэффициентах:
- факт. время /макс. возможное;
- неявки /табельное или макс. возможное;
- явочн. /макс. возможное.
Таким образом, основная задача баланса:
- Повышение эффективности использования фонда рабочего времени.
- Определение плановой численности специалистов.
Расчет баланса рабочего времени
Допустим, предприятие уже существует. Есть штатное расписание, график отпусков, табель учета рабочего времени за прошлые периоды. Необходимо рассчитать:
- номинальный фонд рабочих часов;
- часы, приходящиеся на отпуск;
- максимально возможный фонд;
- явочный плановый фонд.
«Шапка» учебного баланса выглядит следующим образом:
- Табельные значения берутся из графика работ конкретного специалиста. Например, в декабре 2015 г 31 календарный день. Отнимаем праздники – получаем 28. Сорокачасовая рабочая неделя, пятидневка – 20 рабочих дней или 160 рабочих часов.
- Количество «отпускных» дней берется из графика отпусков. Если у работника на анализируемый период запланирован отпуск, то для расчета максимально количества рабочих часов из табельных значений отнимаются плановые отпуска.
- Плановый отпуск сопоставляется с рабочими днями по графику. В таблице отражаются дни и часы, приходящиеся на рабочие смены.
- Чтобы рассчитать явочное время, необходимо спрогнозировать неявочные дни. Максимально точно это сделать невозможно. Поэтому возьмем средний процент неявок за предыдущий квартал. Полученное значение вычтем из максимально возможного фонда. Результат – плановый явочный фонд.
Сделаем прогноз количества рабочих часов на условный месяц.
В столбце «Максимально возможное время» расчет производится автоматически. Вводим формулу: = F4 – H4 (табельные значения минус плановый отпуск). Для дней и часов соответственно.
Для расчета не явочных часов нужны средние показатели неявок. Удобнее всего, если учет времени тоже ведется в Excel. Ежемесячно подсчитываются «явки» и «неявки» по каждому сотруднику.
Тогда при составлении баланса на новый период можно в графу «Явочное время» ввести формулу со ссылкой на «Учет раб.вр.» за предыдущие 3 месяца.
Например, такую: =J4-(('Учет раб.вр. Март'!B3+'Учет раб.вр. Апрель'!B3+'Учет раб.вр. Май'!B3)/3). Или с использованием функции СРЗНАЧ: =K4-(СРЗНАЧ('Учет раб.вр. Март'!C3;'Учет раб.вр. Апрель'!C3;'Учет раб.вр. Май'!C3)).
По такому же алгоритму заполняются данные по всем сотрудникам и подразделениями. Формулы копируются на весь столбец.
Впоследствии на основе баланса можно составлять аналитическую таблицу для сравнения плановых и фактических показателей.
Баланс времени одного среднесписочного рабочего
На одного человека составляют балансы, как правило, на промышленных предприятиях.
- Определение количества рабочих дней. От числа календарных дней отнимаются праздники и выходные, неявки на работу по уважительным причинам, простои (если они заняли целый день).
- Расчет продолжительности трудового дня. От номинальной продолжительности отнимаются установленные законодательством временных потерь (для кормящих матерей, для работающих в тяжелых условиях и т.д.).
- Расчет эффективного фонда времени одного среднесписочного рабочего.
Внесем показатели и известные величины в таблицу Excel:
Все остальные числовые значения по показателям найдем с помощью формул:
- Нерабочие дни (строка 2): праздничные + выходные (=C4+C5).
- Номинальный фонд (строка 3): календарные дни – нерабочие (=C2-C3).
- Неявочные дни (строка 4): сумма всех дней, пропущенных по уважительным и неуважительным причинам (=СУММ(C9:C15)).
- Явочные дни (строка 5): номинальный фонд– неявки (=C6-C7).
- Временные потери (строка 7): сумма всех «сокращений» трудового дня, связанных с праздниками и льготными условиями труда (=СУММ(C20:C24)).
- Средняя продолжительность рабочего дня (строка 8): среднеарифметическая величина, взвешенная с учетом установленной государством продолжительности рабочей смены. Формула: =(C17*(C17*C16)+5*C18)/((C17*C16)+C18)).
Разделим по «математическим» действиям:
- Сначала найдем бюджет рабочего времени: продолжительность смены * явочные дни.
- При пятидневной рабочей неделе в числителе: установленная продолжительность смены * бюджет раб. час + 5 * временные потери.
- В знаменателе: сумма бюджета и потерь.
Полезный фонд (строка 9): бюджет рабочего времени (продолжительность раб. дня * явочный фонд) – временные потери. Формула: =(C17*C16)-C18.
Фактический и нормативный балансы рабочего времени
Расширяем таблицу с нормативным балансом – добавляем столбец с фактически использованным рабочим временем за период и с отклонениями.
Вносим фактические значения. Отклонения находим с помощью формул. Анализ готов.
Табель учета рабочего времени excel 2022 — это новая версия 3.3, завоевавшего доверие огромного количества пользователей, учета рабочего времени версии 3.2, который вы также можете найти на страницах нашего сайта. В новой версии табеля учета 2022 Вы найдете расширенный функционал по управлению штатом сотрудников, простой и понятный механизм учета рабочего времени и иных активностей Вашего персонала, отлаженные алгоритмы начисления заработной платы, формирования индивидуальных расчетных листов, а также листов ознакомления с графиком на подпись.
Важно! Если Вы ведете личный учет рабочего времени или же штат Вашей организации небольшой, воспользуйтесь табелем учета версии 3.2. Он обладает абсолютно идентичной механикой учета рабочего времени и расчета заработной платы, но с более лаконичным и простым интерфейсом. Для этого перейдите на соответствующую статью по данной ссылке: Табель учета 3.2 для небольшого штата сотрудников
Также на нашем сайте представлены различные вариации исполнения табеля учета рабочего времени, ознакомиться с которыми Вы можете перейдя по соответствующим ссылкам статей:
— Табель почасового учета TimeSheet (Фото дня)
— Табель учета рабочего времени в днях по форме Т-13
— Табель расчет и планирование вахты
— Табель учета рабочего времени с учетом ночных смен
Построение табеля учета рабочего времени 2022
Табель учета рабочего времени excel 2022 состоит из двух листов:
-
Настройки — перечень справочных таблиц, настройка внешнего вида табеля и расчетных столбцов (активности, переработки, начисления, удержания и т.д.)
-
Штат — лист управления персоналом. Содержит основные данные о сотрудниках, работающих в организации (личные данные: фио, адрес, телефон и т.д., а также должность, отдел, вид оплаты, дату приема, дату увольнения и т.д.)
Файл учета рабочего времени 2022 для наглядности заполнен тестовыми данными, поэтому сразу можно перейти к построению табеля, нажав кнопку «Создать табель» на листе «Штат». Либо Вы можете ввести данные своей организации, о чем подробнее будет рассказано ниже.
Заполнение листа «Настройки»
Лист «Настройки» включает справочники и таблицу настроек построения. Справочник названий отделов/групп/бригад и справочник должностей содержат столбцы «Порядок отображения в табеле», которые служат для настройки очередности вывода элементов в таблице учета. Самым приоритетным элементам присваивается значение 1, далее это значение увеличивается по мере уменьшения приоритета отображения. Справочники заполнены тестовыми данными для примера ввода. Список праздников по производственному календарю заполняется на усмотрение организации и ее графика работы в праздничные дни.
Таблица настроек построения содержит информацию об ответственных за составление графика лицах, а также расчетные столбцы активностей и учетных показателей, которые для удобства разделены на блоки. Если та или иная активность или характеристика не используется для расчетов рабочего времени в Вашей компании, выберите напротив нее значение «Нет». Если показатель имеет столбец с расчетным коэффициентом, отредактируйте его по своему усмотрению. Укажите даты аванса и получения заработной платы
Внимание! Не удаляйте строки листа настроек стандартным excel удалением всей строки, т.к. это может привести к поломке табеля. Воспользуйтесь очисткой выбранных данных через Delete или аккуратно удалите диапазон данных с помощью функции «удаление со сдвигом».
Заполнение листа «Штат»
Лист «Штат» табеля учета рабочего времени excel является перечнем сотрудников организации. По Вашим пожеланиям, в новой версии 3.3 2022 в отличии от версии 3.2 2021 штат вынесен на отдельный лист и его функционал существенно расширен. Столбцы таблицы можно скрывать и отображать используя кнопку «Столбцы», при нажатии на которую появится диалоговое окно с их перечнем. Также добавлена кнопка «Уволенные», которая служит для формирования списка уволенных сотрудников за определенный временной промежуток.
Добавление новых сотрудников на лист «Штат» производится как обычным копированием из любого источника данных, так и ручным заполнением полей таблицы. Но есть и третий способ, который является нововведением версии 3.3 2022 — «Редактор сотрудников».
Используя редактор Вы можете отредактировать данные заведенного ранее работника или добавить нового. А для того, чтобы все внесенные Вами изменения отразились в построенном табеле учета, на форме необходимо установить чекбокс «Внести изменения в построенный табель» и выбрать нужный лист месячного учета в поле со списком. В верхней части редактора сотрудников реализованы два вида поиска: поиск по отделу и поиск по вводу фамилии (поиск по всем) для удобной навигации по сформированному перечню сотрудников.
Работа в табеле учета рабочего времени 2022
Рабочую область построенного табеля учета рабочего времени в excel 2022 условно можно разделить на три блока: общая информация о сотруднике с листа «Штат», календарь для ведения фактического учета рабочего времени и итоговый расчетный блок. Первые два блока представлены на изображении ниже:
В блок общей информации данные подтягиваются с листа «Штат». Лишь показатель «Кол-о плановых дней» высчитывается программой на основе календаря. Его нужно отредактировать, если кол-о плановых дней в вашей организации в данном месяце отличается от рассчитанного табелем учета рабочего времени 2022.
Для примера на следующем изображении представлена лишь часть расчетных столбцов третьего итогового блока. Для полного ознакомления с представленным функционалом табеля учета рабочего времени в excel 2022 постройте его:
В итоговом блоке подсчитывается общее количество часов и дней, отработанных работником в месяц, также есть блок нарастающего итога (не представлен на изображении), который суммирует данные показатели за все расчетные месяцы. Есть блок учета стандартных активностей: больничных, отпусков, прогулов, отгулов, декретного отпуска, отпуска без содержания и т.д.. Блок учета переработок умеет определять переработку первые 2 часа, последующие часы, работу в выходные и праздники, а также общую переработку и недоработку по плановому количеству часов. Далее идет часть расчета заработной платы, на основе данных по оплате сотрудника и его фактически отработанного времени. В настройках есть возможность выставить оплату больничного и отпуска (не представлено на изображении). Следом идут блоки начислений и удержаний, все расчетные столбцы которых можно включить/выключить на листе «Настройки». В завершении строится блок выплат, который показывает фактическую сумму денег, заработанную сотрудником (с учетом удержаний и начислений), поделенную на столбцы: «аванс» и «к получению».
Формирование расчетных листов табеля учета рабочего времени 2022
По завершении месяца табель учета рабочего времени 2022 предоставляет возможность сформировать расчетные листы по каждому сотруднику в формате таблиц excel. Для того чтобы их построить нажмите кнопку «Расчетные листы». Пример расчетного листа приведен на рисунке ниже:
Создание листов ознакомления табеля учета рабочего времени excel
Перед началом рабочего месяца табель учета рабочего времени 2022 предоставляет возможность запланировать графики сотрудников и расставить соответствующие рабочие часы в календаре. Далее Вы можете сформировать и распечатать «Листы ознакомления» с графиком на подпись сотрудникам в формате таблиц excel. Для того чтобы их построить нажмите кнопку «Листы ознакомления». Пример приведен на рисунке ниже:
Послесловие
В данной статье представлена общая инструкция по работе с табелем учета рабочего времени 2022 в excel, а также описаны основные возможности и настройки табеля. Предлагаю Вам скачать саму программу, по одной из представленных ссылок вверху или внизу статьи, и самостоятельно изучить все ее возможности.
Благодарю за прочтение данной статьи. По традиции, жду Ваших комментариев и отзывов, а также пожеланий по улучшению и развитию табеля учета рабочего времени excel 2022!). Представленные механики расчетов переработок, заработной платы могут отличаться от Ваших, т.к. являются индивидуальной спецификой рабочего учета каждой компании. В данном случае Вы можете сделать индивидуальный заказ на доработку и настройку табеля под Ваши потребности.
комментария 2 Добавить комментарий
Добрый день. Мне понравилась ваша программа, хорошо с подсчетом отработанного времени, даже можно начислить заработную плату.
У вас НДФЛ удерживантся только по стандартным начислениям- 13%, а если льготы: 1,2- или несколько детей, ребенок-инвалид? Если можно доработать эти функции — ваша программа будет ПРОСТО СУПЕР.
Добрый день! Благодарю за Ваш отзыв, буду иметь ввиду на перспективу доработок для новой версии. Также напоминаю, что всегда есть возможность оперативной индивидуальной доработки табеля. Спасибо!
Читайте также: