Батьянов Денис , IT тренер и консультант
http://perfect-excel.ru На своём сайте я уже делился с уважаемыми читателями своими наработками по поводу табеля рабочего времени. Сегодня я предлагаю вашему вниманию график планирования посменной работы отдела. Он будет полезен для структур, которые работают в режиме 24 x 7 или около того, и вынуждены работать сменами. Это всяческие службы поддержки, кол-центры и т.п.
Процесс планирования смен в подобных структурах очень трудоёмок и, как правило, не слишком нагляден и информативен. Данная работа призвана облегчить такого рода деятельность. Шаблон сделан на основе уже упоминаемого табеля рабочего времени, унаследовав все его положительные свойства. Он является хорошей иллюстрацией к более-менее зрелому использованию условного форматирования Excel, и, хотя я не собираюсь подробно описывать, что и как там устроено, но готов ответить на все ваши вопросы по его устройству, если таковые появятся.
Файл для скачивания
Функционал
Встроенный универсальный календарь.
Простой метод учета рабочего времени в Excel. Эксель для начинающих
Вам не придётся думать сколько дней в феврале 2016 года, уточнять с какого дня недели начинается месяц и тому подобное. Всё, что вы должны сделать, чтобы получить правильную заготовку нужного вам месяца, это указать год и месяц. Субботы и воскресенья выделяются серым, что позволяет вам легко ориентироваться в днях недели.
Выделение праздников.
Шаблон отметит красным фоном все праздники, которые вы перечислите на листе Настройки. 
Выбор смены из справочника.
Вид смены выбирается строго из списка, что избавляет вас от ошибок ввода. 
Смены описаны в Настройках в специальной таблице. Можно удалить лишние и добавить свои. 
Смены автоматически выделяются цветом.
Поскольку всё форматирование реализовано через условное форматирование, то есть оно не хранится непосредственно в ячейках, то это очень удобно, так как ячейки можно копировать и удалять, на заботясь, что остатнутся следы от предыдущей смены.
Подсчёт смен и часов по человеку.
Автоматически подсчитывается по каждому человеку отработанное количество смен и количество рабочих часов.
Учёт рабочего времени за день.
За каждый день подсчитывается количество отработанных смен, часов, количество больных сотрудников, сотрудников в отпусках и сотрудников, находящихся во внеочередных выходных (если сотрудник работает 2 дня по 11 часов, то вы ему обязаны дать 2 выходных дня непосредственно после этих рабочих смен).
Создание графика работы в Excel / Урок эксель для начинающих
Подсчёт количества сотрудников по часам суток.
Тем, кто обеспечивает работу в режиме 24 х 7 важно знать сколько сотрудников будет работать в каждый конкретный час, чтобы балансировать нагрузку на отдел и уровень сервиса. Известно, например, что максимальное количество звонков поступит с 10 утра до 17 вечера и надо, например, иметь 5 специалистов, чтобы справиться с этой нагрузкой, а с 19:00 до 7:00 утра хватит одного дежурного специалиста. Для этого предназначена панель подсчёта сверху шаблона. Она отображает ситуацию того дня, в столбце которого вы стоите. Кстати, нулевые значения выделяются, чтобы бросались в глаза.
Два внеочередных выходных.
Как мы уже упоминали, если сотрудник отработал 2 смены подряд по 11 часов, то вы по закону должны предоставить ему 2 выходных подряд сразу после этих двух смен. В шаблон встроен автоматический механизм контроля этой ситуации. Если вы не предоставили сотруднику 2 выходных после таких смен, то фамилия сотрудника подсвечивается красным. Единственная проблема возникает на границе месяца, но я с ней ничего не могу поделать.
Источник: www.audit-it.ru
Табель учета рабочего времени в excel
Видео: Простой табель учета рабочего времени в excel
Здесь размещены шаблоны табеля учета рабочего времени в Excel. В шаблонах производится автоматический подсчет рабочих часов и минут.
Автоматически устанавливаются дни недели в зависимости от месяца. Например: в феврале месяце первое число приходится на субботу.
Вы ставите в ячейке, под цифрой «1» обозначение «Сб», нажимаете «Enter» и все дни недели поменяются на правильные дни недели в этом месяце.
Ставить часы и минуты работы в табеле нужно через двоеточие. Так настроены ячейки, чтобы правильно считать время. Например: 8:15.
Для скорости заполнения табеля, можно заполнить первую строку, затем копировать вниз по табелю, вносить поправки по больничным листам, отпуску, др.
Видео: 0_Программу учёта рабочего времени в файле Excel самим создать
Нам приятно, что наши бланки — программы помогают вашей работе. Наша читательница Вера дополнила наш бланк «Табель учета рабочего времени» такой формулой.
Эту формулу можно установить в ячейки С10:AG54. Сначала в этих ячейках и в столбце H10:H54 (итого часов, минут) нужно установить общий формат ячеек.
Эта формула будет автоматически ставить в табеле часы работы с понедельника по четверг — по 8 часов, в пятницу — 6 часов.
Количество часов в формуле можно изменить. Этот бланк с формулами можно сохранить шаблоном. При необходимости можно вручную менять количество отработанных часов по конкретному человеку.
Табель настроен так, что распечатывается на одной странице листа формата А4. Если, для кого — то это мелко, можно изменить настройки.
В табеле можно производить изменения, корректировки шаблона.
Можно в новом столбце установить формулу, которая будет умножать время на деньги.
Шаблон табеля учета рабочего времени в Excel скачать (rar). В следующем табеле две строки на одного человека, чтобы считать отдельно дневные и ночные часы.
Такой автоматический гарфик смен можно скачать здесь. Можно сделать несколько разных бланков в одной книге Excel, соединить их формулами, получится программа в Excel.
Смотрите образец такой программы в статье «Табель, ведомость, расчетный лист в Excel» тут. Как самим установить автоматическую смену дней недели, в зависимости от месяца, смотрите в статье «День недели по дате Excel».
Можно в таком бланке установить ссылки, по которым можно будет перейти в другую таблицу на другом листе с полными данными конкретного сотрудника.
Источник: j4web.ru
Расписание в Excel — Как создать шаблон расписания в Excel?
Расписание — это система учета количества времени, потраченного сотрудником на каждую работу. В Excel мы обычно используем табель рабочего времени для расчета времени сотрудника, такого как время входа и выхода, сколько часов сотрудник проработал за день, какое именно «перерыв» он потратил. В Excel этот табель будет суммировать количество часов, отработанных каждым сотрудником, чтобы рассчитать эти сроки, мы можем использовать табель для его уточнения.
Как создать шаблон расписания в Excel?
Создать шаблон расписания в Excel очень просто и легко. Давайте разберемся, как создать шаблон расписания в Excel с некоторыми примерами.
Вы можете скачать этот шаблон Excel по расписанию здесь — Шаблон Excel по расписанию
Расписание Excel — Пример # 1
В этом примере мы собираемся создать расписание сотрудников, рассчитав, сколько часов он проработал. Давайте рассмотрим приведенную ниже базу данных сотрудников с параметрами IN и OUT.
Приведенная выше база данных сотрудников содержит дату, имя сотрудника, ВРЕМЯ и ВЫХОД. Теперь нам нужно узнать, сколько часов работал сотрудник, выполнив следующие действия:
- Создайте новый столбец с именем Количество отработанных часов.
- Убедитесь, что ячейка имеет правильный формат времени.
- Чтобы рассчитать количество часов сотрудников, мы рассчитаем формулу как ВРЕМЯ ОТКЛЮЧЕНИЯ — В СРОКИ.
- По умолчанию Excel возвращает результат в десятичных числах, как показано ниже, где это не точное количество часов.
- Нам нужно изменить этот общий формат на время, отформатировав ячейку, как показано ниже.
- Как только мы нажмем на ячейку формата, мы получим диалоговое окно ниже и выберите точный формат времени, а затем нажмите ОК.
- После применения формата мы получим результат вывода следующим образом.
- Если у нас есть тайминги IN и OUT, мы можем использовать формулу, как показано выше, но в этом примере у нас есть BREAK TIME IN и TIME OUT. Следовательно, мы можем использовать простую формулу расписания Excel как = (E2-D2) + (G2-F2) .
- Таким образом, применяя эту формулу, мы узнаем, сколько часов сотрудник проработал в течение дня, и результат будет отображаться следующим образом.
- Нам просто нужно перетащить ячейку H2 вниз, и формула будет применена ко всем ячейкам.
В приведенном выше примере мы можем видеть разницу во времени, которая показывает, сколько часов сотрудник проработал, а сроки разрыва были предоставлены каждому сотруднику как время OUT.
Расписание Excel — Пример № 2
В предыдущем примере мы видели, сколько часов сотрудник проработал, используя обычную арифметическую формулу, теперь в этом примере мы будем использовать функцию времени в формате 24 часа.
Предположим, что руководство хочет платить сотрудникам на почасовой основе, исходя из сроков, которые они проработали.
Давайте рассмотрим базу данных сотрудников с соответствующей записью времени, как показано ниже.
Здесь мы видим, что время входа и выхода сотрудника для разных сотрудников. Теперь нам нужно рассчитать количество часов, отработанных сотрудником, выполнив следующие шаги:
- Вставьте новый столбец с именем Часы работы.
- Примените обычную формулу расписания Excel как = E2-D2, как показано ниже.
- Теперь мы можем видеть, что десятичные значения появились для отработанных часов.
- Эта ошибка обычно происходит, потому что время не в 24-часовом формате.
- Чтобы применить формулу расписания Excel, умножив на 24 как = (E2-D2) * 24.
- После применения приведенной выше формулы мы получим следующий результат:
- Перетащите ячейку F2 вниз, и формула будет применена ко всем ячейкам, как показано ниже.
Теперь мы подсчитаем, сколько сотрудников будут получать почасовую оплату. Предположим, что сотруднику будет выплачиваться 20 рупий в час, и выполните следующие действия, указанные ниже.
- Вставьте два новых столбца, как « Почасовая ставка» и « Брутто-оплата», как указано ниже.
- В столбце почасовой ставки введите 20 рупий / -.
- Чтобы применить формулу расписания Excel в качестве валовой заработной платы = почасовой ставки * отработанных часов .
- Мы получим следующий результат следующим образом.
- Перетащите ячейку H2 вниз, и формула будет применена ко всем ячейкам, как показано ниже.
Здесь мы рассчитали валовую заработную плату работника на основе количества отработанных часов.
В любом случае, мы можем создать новый столбец Почасовая ставка как Rs.20 / — в фиксированном столбце и умножить его на количество отработанных часов. Давайте посмотрим на примере следующим образом.
Расписание Excel — Пример № 3
Давайте рассмотрим ту же запись данных о сотрудниках, которая имеет записи TIME IN и TIME OUT, следующим образом.
Здесь мы создали новый фиксированный столбец с почасовой ставкой как Rs.30 / -. Таким образом, сотрудник будет получать оплату 30 рупий / — в час.
Теперь почасовая ставка имеет фиксированный столбец, поэтому при изменении ставки она заполняется и отражается в столбце валовой оплаты, как показано в следующих шагах.
- Вставьте формулу расписания Excel в столбец валовой оплаты как = $ c $ 1 * F5, показанный ниже, то есть валовая оплата = почасовая ставка * отработанные часы .
- Мы можем видеть, что валовая оплата была рассчитана на основе почасовой ставки.
- Перетащите ячейку G2 вниз, и формула будет применена ко всем ячейкам, как показано ниже.
- Далее мы рассчитаем общую валовую заработную плату, добавив валовую заработную плату сотрудников.
- Используйте формулу = СУММА (G5: G19).
- Мы получим следующий вывод следующим образом.
Таким образом, мы рассчитали общую валовую заработную плату как 3900 рупий / — и почасовую ставку как 30 рупий / — при изменении ставки по часам значения будут автоматически изменены, а та же общая сумма брутто также будет изменена.
Не посмотрим, что произойдет, если почасовая ставка будет изменена на 10 рупий / — и мы получим следующий результат следующим образом.
Теперь мы можем видеть разницу у каждого сотрудника в том, что валовая заработная плата снижается из-за изменений в почасовой ставке, и в то же время мы получаем общую валовую заработную плату в размере 1300 рупий / — и почасовую ставку в размере 10 рупий /.
То, что нужно запомнить
- Поддерживать правильный формат времени при создании расписания для сотрудников.
- Убедитесь, что все ячейки отформатированы в 24-часовом формате, иначе Excel выдаст десятичное значение.
- Убедитесь, что AM и PM указаны в таймингах, потому что в случае, если OUT TIME больше, чем IN TIME, Excel не сможет рассчитать количество отработанных часов.
Рекомендуемые статьи
Это было руководство по расписанию в Excel. Здесь мы обсудим, как создать шаблон расписания в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете посмотреть следующие статьи, чтобы узнать больше —
- Рассчитать процентное увеличение в Excel
- Объедините имя и фамилию в Excel
- Табличные стили в Excel
- Вставить новый лист в Excel
Источник: ru.education-wiki.com