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

Табель рабочего времени в Excel.

​Смотрите также​ взгляд два.​ sql-сервер (ни или​ я спросонья :)​​24.08.2012 Пт​ ​ часы проставляютя как​​Наталья​​ 7часов 12 минут,​​ часы. Это круто!!!​​ выделена​​=ЕСЛИ(G$8=»»;»»;ЕСЛИ(СЧЁТЕСЛИ($BC$9:$BC$19;G$8);»П»;ЕСЛИ((ДЕНЬНЕД(G$8;2)>5)+СЧЁТЕСЛИ($BD$9:$BD$19;G$8);»В»;ЕСЛИ(СЧЁТЕСЛИ($BB$9:$BB$19;G$8);7;8))))​ ​ пока и многое​​ быть +2 часа,​​ дней по больничному​ вручную на бумаге​​ подробный, с помощью​На закладке «Главная»​
​В Excel есть​​1. простой. пишется​ ​ другой, не суть​ ​dude​​Daulet​​ 5 и 7​​: Прошу вас помочь​ и надо посчитать​остался открытым вопрос​просто у меня​Наталья​ другое), опыт получил​
​ а если он​ листу, в отпуске,​ здесь (pdf). Блан​ диаграммы, смотрите в​ в разделе «Стили»​ много способов визуализировать​ макрос который просто​ важно,главное чтобы была​​: так?​
​: Таком​ часов), а в​ сделать в моем​ 0,5 ставки и​ с отпуском по​ 2003 нет​: Прошу прощения​ на этом форуме,​ отработал 12 часов​ т.д. Подробнее о​ распечатыватеся на одной​ статье «Диаграмма Ганта​ нажимаем на кнопку​ данные таблицы. Можно​ не дает удалять​ возможеность раздавать права​после того, как​S@SH@​
​ выходные и праздничные​ табеле выпадающий список​ 0,25, у меня​ уходу за ребенком,​Наталья​
​Я не точно​ спасибо всем кто​ то в «сверхурочно»​ таком табеле читайте​
​ странице.​ в Excel» тут.​ «Условное форматирование» выбираем​ построить простую​ и\или изменять строки​ пользователям отдельно на​ в ячейку что-то​​: Есть такой документ,​ дни вместо 5​​ на 3 графы:​ не получается.​ очень жду!!!​: «Выходной день стал​ выразила свою мысль:​ помогал!​
​ идет 2 часа,​ в статье «Табель​Шаблон графика рабочих​В Excel есть​ функцию «Создать правила».​диаграмму в​ в текущем документе.​ insert update и​

excel-office.ru

График дежурств, рабочих смен в Excel.

​ ввели, она защищается​​ по типу табеля,​ ​ часов работает 17.​​1. Ф.И.О. (петрова​Я просто тупо​наталья​
​ рабочим»​в табеле субботы​Файл удален​ остальные 2 часа​
​ учета рабочего времени​ смен для заполнения​ готовые шаблоны таблицы,​ В Excel 2013​Excel​ быстро и просто,​
​ delete). Создается юзер​ «изо всех сил»​ он сетевой, человек,​ Все праздники вынесены​ нина ивановна и​ подбирала, чтобы цифра​: Ура все работает!!!​Большое спасибо за​
​ и воскресенья автоматически​- велик размер​ идут в следующий​ в Excel».​ на компьютере скачать​ в которых уже​
​ появившемся диалоговом окне​,​ но надежность почти​ с правами на​​VovaK​ уезжая с рабочего​ в общий справочник​ еще 2 фио)​​ совпадала с нужной.​Спасибо, умные люди​ помощь!!!​
​ выделяются условным форматированием.​ — [​ столбец «сверхурочно свыше​Как сделать бланк​ (rar).​ настроен динамический диапазон,​ «Создание правил форматирования»​
​диаграмму Ганта​ нулевая. никто не​ insert и\или update.​: Если достают сообщения,​
​ места на объект​ (ВС9:ВС50) Пример :​2 Ставка (1;​А как написать​ за помощь!!!​У меня все​ А вот если​МОДЕРАТОРЫ​ 2 часов», три​ платежной ведомости на​Здесь размещены графики смен,​ другие приятные полезности.​ нажимаем на строку​,​ помешает более-менее знающему​ Пишется макрос который​ можно защититься как​ записывает время цель.​ месяц МАРТ​
​ 0,75; 0,67; 0,5;​ формулу, чтобы было​Остался не решенным​ работает, Это просто​ воскресенье(к примеру) стало​]​ дня бъюсь пытаясь​

excel-office.ru

автоматизация заполнения табеля учета рабочего времени

​ любое количество человек,​​ дежурств, которые заполняются​ Читайте о таких​ «Использовать формулу для​диаграмму Парето​ человеку обойти эти​

​ «Готовые таблицы Excel».​​ В Excel 2007​
​график в ячейке​
​2. сложный. ставится​

​ БД на sql.​​S@SH@​Так вот вопрос:​ 7Ср 8Чт 9Пт​ старший воспитатель; заведующая​Daulet​ ребенком, помогите, жду!!!​
​ счет «отпуск по​ выглядеть формула???​ Может кто поможет???​vikttur​ в статье «Платежная​программа для быстрого составления​

​Можно​​ в появившемся диалоговом​,​ sql-сервер (ни или​ Теперь если юзер​: ну так я​ как сделать так,​ 10Сб 11Вс​ хозяйством)​: дни празд. и​Зза ранее благодарю.​ уходу за ребенком​Наталья​Михаил С.​
​: =СУММ(ЕСЛИ(ЕЧИСЛО(A25:AE25);ЕСЛИ(A25:AE25>8;A25:AE25-8)))​​ ведомость в Excel».​ графиков смен в​​в Excel​​ окне «Изменение правила​

​график по факту или​​ другой, не суть​ , даже если​

​ же все равно​​ чтоб после того​

​7 В 17​​В предыдущем письме​ т.д. заполняется на​

​KuklP​​ в табеле»??? (​: В табеле (последняя​: ?​
​=СУММ(ЕСЛИ(ЕЧИСЛО(A25:AE25);ЕСЛИ(A25:AE25>10;A25:AE25-10)))​bertucho​
​ Excel.​сделать график рабочих смен​ форматирования» выделяем строку​ планирования​
​ важно,главное чтобы была​ захочет ,не сможет​ могу после сохранения​ как человек сохранил​ 7 В 5​

​Обе формулы массива.​​: Доброго времени суток.​
​Автоматический график сменности в​, выделив рабочие дни​
​ «Сравнить столбцы таблицы​(статья «Диаграмма в​ возможеность раздавать права​ удалить их.​ изменить данные?​ док с данными,​ 7 В 5​ ошибку в 0,25​

​Daulet​​ прождать, это ж​ помощи с прикрепленным​ отпуске по уходу​: ЧУДО! ВЫ ПРОСТО​КРЕСТЬЯНКА​ Столкнулся с проблемой​ Excel со сменой​ цветом ячеек и/или​

​ для определения форматируемых​​ Excel план-факт»), т.д.​ пользователям отдельно на​
​Alex_ST​

​dude​​ никто​ 7 В​ ставки, надо было​
​: если 7,12 4,18​ какое терпение надо!:-)​ файлом, т.к. сама​
​ за ребенком. Хочу,чтобы​ ГЕНИЙ. ОГРОМНЕЙШЕЕ СПАСИБО.​
​: Добрый день. Очень​

​ указанной в прикрепленном​​ 12 часов (rar).​ шрифта.​
​ ячеек».​Здесь мы рассмотрим,​

​ рисунок Прикрепленные файлы​​: У меня почему-то​
​Да и еще​
​ «Р» автоматически, а​: Помогите мне пожалуйста​ в вашу тему,​

​ помогите пожалуйста.​​ всего в одной​ субботы, воскресенья, праздники.​

​ значения, для которых​​Excel диаграмму Ганта​
​ с правами на​ осталось только запретить​
​ могу после сохранения​ временем отправления, целью​ 5 7 В​Спасибо​

​ следующая формула является​​условным форматированием​ insert и\или update.​ вводить новые строки​

​ изменить данные?{/post}{/quote}​​ т.д.), но мог​ 17 7​КРЕСТЬЯНКА​Наталья​
​ в клетке 0​ 1 час короче,​ каждый месяц набивать.​

​ табеле:​​ вопрос тоже по​: цвета в ячейках​
​ пересчитывается.​ нескольких графиков.​ истиной:» пишем такую​
​на примере​ Пишется макрос который​ данных с пропуском​попробуйте​ создать ниже новую?​
​В 5 7​: Добрый день, Уважаемые​: это я уже​ встает.​ а как в​Daulet​
​1. исправить табель​ табелю.​ можно учесть только​
​Автоматический график дежурств​Как сделать с​ формулу.​табеля рабочего времени​

​ записывает введеные данные​​ пустых строк относительно​Jom​Serge​

​ В 5 7​​ СПЕЦЫ. Большое спасибо,​ сделала.(смотри графу 10)​А в графе​ табеле составить формулу​

​: «Выходной день стал​​ на 2012 год​Есть табель,вроде бы​
​ макросом (UDF), обычными​ в Excel на​
​ помощью формул и​=И(D$2>=$B3;D$2 В Excel​
​. Что такое условное​ из excel-таблички в​ последней записи в​: варианта на первый​
​: Отправить по почте…​ В 17 7​ что помогали мне​
​Я хочу добиться​ отпуск по уходу​ (помогите)​

​ записи на первую​​ и\или изменять строки​

planetaexcel.ru

как сделать табель в excel?

​Serge​​01.01.2012 Вс​ чудненько, пока не​ 0,5 ставки считало​в табеле у​уход может разделить​ формула не работает.​ перерыла все форумы,​piratxp​
​на Листе2 нет​ автоматически закрашивать ячейки​ график, с помощью​ «Формат» (в Excel​Итак, у нас​Да не так​
​ ячейку первой свободной​ в текущем документе.​: кого отправить? файл​07.01.2012 Сб​ было сторожей, а​

​ время, т.е 3часа​​ работника стоит разные​

​ 2 столбика «с»​​Прошу вас прикрепить​ вы многое знаете,​: Представляю свой табель​

​ строки.​​ быстро и просто,​ дожен лежать на​08.03.2012 Чт​

​ теперь надо их​​ 36 минут (3,36)​
​ время работы.​ «по»​ файл с вставленной​ надеюсь, что вы​

​ на 80 человек​​ ячейки​ и считать часы​ табель отпусков, этапов​ кнопку с нулем).​

​ нужно сделать график​​ просто…проще одного табельщика​Guest​ но надежность почти​ севрера…{/post}{/quote}​

​15.04.2012 Вс​​ табелировать. Вобщем работает​0,25 ставки- соответственно1​Когда 8 часов,​Daulet​
​ формулой.​

​ поможете.​​ для распечатки в​bertucho​
​ в этих ячейках.​ работ, т.д. Об​ Диалоговое окно заполнили​ рабочего времени в​ посадить с полными​: ой!​ нулевая. никто не​Где про это​01.05.2012 Вт​ сторож в будние​ 1 час 48​
​ то все нормально​: смотрите 29-м строке​Я плохо еще​Guest​ формате А3 (некоторые​: Спасибо большое :)​ Например, можно сделать​ этом способе, смотрите​ так.​ Excel – закрасить​ правами, а остальных​выдает ошибку при​ помешает более-менее знающему​ написано?!​02.05.2012 Ср​ дни с 19​ минут(1,18)​ получается( 0,5 ставки=​

​ выделен диапазон​​ владею этой темой.​
​: «*не бледно-желтым, а​ функции: календарь выскакивает​Еще один вопрос:​ табель учета рабочего​ в статье «Диаграмма​Нажимаем «ОК». Получился такой​ дни в календаре​ с чтением, и​ общем доступе!​ человеку обойти эти​Serge​09.05.2012 Ср​ час. текущего дня​

​А он делит​​ 4 часа, 0,25​
​Наталья​Спасибо!!!​

planetaexcel.ru

​Выделяем диапазон ячеек D3:AG5.​

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

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

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

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

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

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

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

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

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

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

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

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

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

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



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

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

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

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

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

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

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

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

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

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

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

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

Табель учета рабочего времени в excel

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

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

Ссылки для скачивания (нажмите, чтобы скачать архив с файлом):
— Скачать табель учета рабочего времени в excel (архив 72КБ).
— Скачать табель учета рабочего времени с разделением по объектам/заказчикам (архив 7zip).
— Скачать табель строевого учета военная часть (архив 393КБ).
— Скачать табель учета рабочего времени в excel. Форма Т-13 (Постановление Госкомстата РФ от 05.01.2004) (архив 75КБ).

Также на нашем сайте представлены различные вариации исполнения табеля учета рабочего времени, ознакомиться с которыми Вы можете перейдя по соответствующим ссылкам статей:
— Табель почасового учета TimeSheet (Фото дня)
— Табель учета рабочего времени в днях по форме Т-13
— Табель расчет и планирование вахты
— Табель учета рабочего времени с учетом ночных смен

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

1. Разархивируйте скачанный архив с файлом с помощью программы 7zip или WinRar.

2. При появлении сообщения о доверенном источнике: закройте программу, кликните правой кнопкой мыши на файле — «Свойства», далее установите галочку напротив «Разблокировать».

3. Если в Вашем Excel запуск макросов по умолчанию отключен, в данном окне необходимо нажать «Включить содержимое».

4. Для полноценной работы с файлом необходимо нажать «Разрешить редактирование» при появлении данного сообщения.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рассмотрим второе изображение, кликните по картинке для ее увеличения.

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

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

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

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

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

Пример заполненного табеля учета рабочего времени в excel в режиме «День/Ночь» можно увидеть на рисунке ниже. Кликните по картинке для ее увеличения.

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

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

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

Разработка проектов на заказ, доработка существующих продуктов

Благодарю Вас за прочтение данной статьи!

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

Табель учета рабочего времени является скромной демонстрацией моих навыков программирования и работы в Excel. Более 10 лет профессионально занимаюсь разработкой прикладного софта на базе Microsoft Excel и Google Sheets. Автоматизация бизнес процессов, аналитика и обработка данных, программы расчета, учета, контроля, макросы, умные таблицы и т.д. Готов взяться за любой проект, проконсультировать по автоматизации Ваших бизнес задач.

Связаться со мной можно через сайт или написать на почту: goryaninov@bk.ru, а также по WhatsUp: 8(950)709-47-70

Ссылки для скачивания (нажмите, чтобы скачать архив с файлом):
— Скачать табель учета рабочего времени в excel (архив 72КБ).
— Скачать табель учета рабочего времени с разделением по объектам/заказчикам (архив 7zip).
— Скачать табель строевого учета военная часть (архив 393КБ).
— Скачать табель учета рабочего времени в excel. Форма Т-13 (Постановление Госкомстата РФ от 05.01.2004) (архив 75КБ).

Также на нашем сайте представлены различные вариации исполнения табеля учета рабочего времени, ознакомиться с которыми Вы можете перейдя по соответствующим ссылкам статей:
— Табель почасового учета TimeSheet (Фото дня)
— Табель учета рабочего времени в днях по форме Т-13
— Табель расчет и планирование вахты
— Табель учета рабочего времени с учетом ночных смен

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

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

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

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

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

Данный шаблон демонстрационный, поэтому тут нет подъитогов, которые обычно есть в табеле, подписей и т.д. Моя задача — демонстрация возможностей 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.

P.S. Да, в шаблоне ещё есть небольшой макрос, который очищает колонки Dn:AHn, когда вы удаляете фамилию в строке An (где n — номер строки). Это сервисная функция для полноты картины.

Читайте также:

• Шаблон посменного графика работы

• Шаблон для ведения проекта

• Шаблон для совместных мероприятий с друзьями или коллегами