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

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

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

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

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

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

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

1. Скачать бланк Т-13
2. Скачать бланк Т-12

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

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

Картинка с сайта: exceltable.com

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

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

Картинка с сайта: exceltable.com

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

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

Картинка с сайта: exceltable.com

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



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

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

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

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

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

Картинка с сайта: exceltable.com

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

Картинка с сайта: exceltable.com

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

Картинка с сайта: exceltable.com

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

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

Картинка с сайта: exceltable.com

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

• Скачать табель учета рабочего времени
• Скачать баланс рабочего времени
• Бланк Т-13
• Бланк Т-12

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

Автор: Келли Последнее изменение: 2024 июля 08 г.

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

Создайте табель учета рабочего времени и сохраните как обычный шаблон Excel

Сохраняйте табель (выделение) только как мини-шаблон

Создать шаблон табеля в Excel

Шаг 1. Создайте новую книгу и подготовьте таблицу, как показано на следующем снимке экрана:

Картинка с сайта: ru.extendoffice.com

Шаг 2: Введите дату в таблицу:

(1) Укажите дату начала периода времени в ячейке B2.

(2) В ячейке A7 введите = ЕСЛИ (B2 <> «»; B2; «»), и нажмите Enter .

(3) В ячейке A8 введите = ЕСЛИ (A7 <> «», A7 + 1, «»), и перетащите маркер заливки, чтобы применить эту формулу к диапазону A9: A13.

Внимание: Если эти даты в диапазоне A7: A13 не отображаются в формате данных, выберите диапазон A7: A13 и нажмите Формат номера коробка в Номер регистрации группы на Главная вкладку, а затем укажите Короткое свидание в раскрывающемся списке.

Картинка с сайта: ru.extendoffice.com

Шаг 3: Введите дни недели в таблицу:

(1) В ячейке B7 введите = A7, и перетащите маркер заливки, чтобы применить эту формулу к диапазону B8: B13;

(2) Не снимая выделения с диапазона B7: B13, нажмите кнопку якорь в Номер регистрации группы на Главная вкладка;

(3) В открывшемся диалоговом окне «Формат ячеек» щелкните, чтобы выделить На заказ в категорию введите, введите дддд в поле ниже Тип, и нажмите OK .

Картинка с сайта: ru.extendoffice.com

Шаг 4: Добавьте проверку данных для столбцов времени:

(1) Выберите диапазон C7: F13, нажмите кнопку Данные > проверка достоверности данных;

(2) В диалоговом окне Проверка данных укажите Время в Разрешить коробка, укажите между в Данные коробка, укажите Время начала как 0:00, и укажите Время окончания как 23:59, и, наконец, нажмите кнопку OK кнопка. См. Снимок экрана ниже:

Картинка с сайта: ru.extendoffice.com

Шаг 5: Добавьте проверку данных для столбца «Больной» и «Отпуск»:

(1) Выберите диапазон I7: J13 и щелкните значок Данные > проверка достоверности данных;

(2) В диалоговом окне проверки достоверности данных укажите Разрешить as Десятичная дробь, указывать Данные as между, указывать Минимальный as 0и укажите Максимальный as 8, как последний щелчок OK .

Шаг 6: Введите исходные данные в столбцы «Время», «Больной» и «Отпуск», а затем рассчитайте рабочее время:

(1) В ячейке G7 введите =IF(((D7-C7)+(F7-E7))*24>8,8,((D7-C7)+(F7-E7))*24), и перетащите маркер заливки, чтобы применить эту формулу к диапазону G8: G13;

(2) В ячейке H7 введите =IF(((D7-C7)+(F7-E7))*24>8,((D7-C7)+(F7-E7))*24-8,0), и перетащите маркер заливки, чтобы применить эту формулу к диапазону H8: H13;

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

Шаг 7: Рассчитайте общее количество за день и за неделю:

(1) В ячейке K7 введите = СУММ (G7: J7), и перетащите маркер заливки, чтобы применить эту формулу к диапазону K8: K13;

(2) В ячейке G14 введите = СУММ (G7: G13), и перетащите маркер заливки, чтобы применить эту формулу к диапазону H14: K14.

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

Шаг 8: нажмите Файл > Сохранено > Компьютер > Вставить в Excel 2013; или щелкните Файл / Кнопка офиса > Сохранено в Excel 2007 и 2010.

Шаг 9. В диалоговом окне «Сохранить как» введите имя шаблона в поле Имя файла поле, щелкните Сохранить как поле и выберите Шаблон Excel(* .xltx) из раскрывающегося списка, а затем щелкните Сохранено .

Картинка с сайта: ru.extendoffice.com

Статьи по теме:

Табель учета рабочего времени Excel значительно сократит трудозатраты на ведение личных записей или запись работы ваших сотрудников. Табель состоит из 5 блоков: основные сведения о работнике (ФИО, должность, норма рабочего времени и т д.), календарь для организации трудовой деятельности (рабочее время, больничные, отпуска и т д.), а также 3 блока расчеты (учет деятельности, сверхурочных, начисление заработной платы). В этой статье вы найдете описание основ создания табеля, инструкцию по его ведению, а также можете скачать программу абсолютно бесплатно по ссылкам в начале и конце статьи. Скачивайте, используйте, автоматизируйте свою рутинную ручную работу.

Важно! Если в вашей организации много сотрудников, используйте табель учета рабочего времени версии 3.3. Имеет абсолютно идентичные механики контроля времени и начисления заработной платы, но с более продвинутым и функциональным интерфейсом создания сотрудников. Для этого перейдите в соответствующую статью по этой ссылке: Табель учета рабочего времени 3.3 для большого штата сотрудников

Обучающее видео по работе с табелем учета рабочего времени

Подписывайтесь на YouTube-канал ExcelLab, ставьте колокольчик, чтобы не пропустить выход новых обновлений табеля учета рабочего времени в Excel. Обновления будут выходить 1-2 раза в месяц. Также будет много другого полезного программного обеспечения для автоматизации, видеоуроков, советов и рекомендаций по Excel.

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

Табель учета рабочего времени в Excel — наиболее распространенный документ Excel с поддержкой макросов в формате xlsm. После скачивания вы увидите архив, который можно распаковать любым архиватором, например WinRar или 7-Zip. После открытия табеля вверху документа вы увидите поочередно два стандартных сообщения системы безопасности Excel. Нажмите «Разрешить редактирование» и «Включить содержимое», чтобы включить макрос построения расписания.

Картинка с сайта: zavistnik.com

Картинка с сайта: zavistnik.com

Затем перейдите на главную страницу здания «Настройки» (рисунок ниже). Для лучшего просмотра изображения нажмите на изображение, чтобы увеличить его.

Картинка с сайта: zavistnik.com

Табель учета рабочего времени по умолчанию заполняется тестовыми данными. Если вы хотите пропустить этап ввода информации и просмотреть функционал документа, нажмите кнопку «Создать табель учета рабочего времени». Чтобы очистить данные теста, нажмите кнопку «Очистить табель».

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

Область настроек сборки находится под кнопкой «Создать расписание». В раскрывающемся списке выберите месяц и год учета, а также режим создания. Табель учета рабочего времени имеет три режима построения: стандартный, день/ночь, день/ночь+. Режимы «день/ночь» создают расчет путем деления рабочего времени на день и ночь. Подробнее о режимах строительства будет рассказано ниже.

После ввода всех данных нажмите кнопку «Создать табель учета рабочего времени» и программа сформирует табель учета рабочего времени за указанный месяц и год. Если вы не видите кнопку сборки, нажмите Alt+F8 и запустите макрос CreateTimesheet_click.

Управление списком сотрудников

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

Картинка с сайта: zavistnik.com

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

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

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

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

Описание структуры табеля

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

Картинка с сайта: zavistnik.com

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

1. Условные обозначения таблиц: строки 1-3 с указанием месяца и года постройки, а также условные обозначения учета трудовой деятельности;
2. В строке 4 макрос задает статус дня: «at» — выходной, «pr» — праздничный, «pp» — предпраздничный;
3. Столбцы с желтой шапкой — список сотрудников, введенный нами из листа «Настройки»;
4. Календарь для настройки графиков и рабочих действий. Выходные и праздничные дни для наглядности выделены светло-зеленым цветом.

Рассмотрим второе изображение, нажмите на изображение, чтобы увеличить его.

Картинка с сайта: zavistnik.com

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

1. Блок «Учетные дни», в котором ведется учет всей трудовой деятельности сотрудника в течение месяца. Отображение определенных столбцов можно настроить в листе «Настройки»;
2. Блок «Расчет сверхурочной работы». Здесь макрос считает плановые сверхурочные/недоработки (от количества часов по графику производства), сверхурочные — первые 2 часа и последующие часы, работу в выходные и праздничные дни. Над колонками указаны коэффициенты повышения заработной платы по соответствующим видам деятельности, которые можно изменить вручную. Отображение столбцов также настраивается на листе «Настройки».

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

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

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

На рисунке ниже вы можете увидеть пример заполненного табеля в Excel в режиме День/Ночь. Нажмите на изображение, чтобы увеличить его.

Картинка с сайта: zavistnik.com

Режимы построения табеля учета рабочего времени 2022

Табель учета рабочего времени в Excel имеет три режима построения:

• Стандартный режим. Для каждой записи сотрудника только одна строка назначается для выполнения действий. Нет деления часов на день и ночь. Он отлично подойдет для организаций, где сотрудники работают по обычному ежедневному графику без ежедневных смен и смен, или где не важен учет ночных часов в сменах и сменах.
• Дневной/ночной режим. Для каждой записи сотрудника отводится две строки: «день ч» и «ночь». Обе линии могут быть заполнены одновременно или по отдельности. В этом расписании вы можете комбинировать обслуживание расписания как для стандартных рабочих, так и для сменных рабочих.
• Режим День/Ночь+. Для каждой записи сотрудника отведено три строки: «отметка», «день ч» и «время ночи». Отличается от режима «День/Ночь» наличием дополнительной линии «метки». Применяется, когда необходимо указать вид графика, по которому работает работник, либо указать объект, на котором выполняется работа, либо любое другое обозначение по усмотрению работодателя.

Послесловие

Спасибо, что прочитали эту статью =). Мы с нетерпением ждем ваших отзывов и комментариев ниже. Загрузка программы абсолютно бесплатна. Буду рад, если эта программа окажется вам полезной, с удовольствием отвечу на ваши вопросы в комментариях.

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