Учет рабочего времени в Excel. IV

Учет рабочего времени в ExcelУчет рабочего времени в Excel. часть IV.

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

Сейчас нам необходимо внести формулы, подсчитывающие итоговые значения за месяц.

Все формулы, которые мы будем использовать, рассматривались в статье «Суммирование по условию«.

Подсчет отработанных дней и часов

В полях «Итого отработано…» просто заносим формулу суммирования значений отработанного времени. Обратите внимание, что формат времени не всегда считает исходя из общего количества отработанных часов. Часто, по умолчанию, устанавливается формат, исходящий из 24 часов. Для исправления ошибки выделите все ячейки с суммой часов, и замените формат на 37:30:55. Он считает не теряя часы за пределами 24.

А вот для подсчета количества отработанных дней нужно будет использовать функцию подсчета количества ячеек по условию (подробнее о ней здесь). Пишем: =СЧЕТЕСЛИ(AG9:CN9;»>0″)+СЧЕТЕСЛИ(CV9:FG9;»>0″). Как вы понимаете, поскольку диапазоны у нас разорваны, то мы складываем два диапазона по отдельности.

Копируем формулу во все остальные ячейки. При копировании ссылки будут смещаться правильно. Если отобразится временной формат, то, попросту, замените его на числовой (с помощью контекстного меню «Формат ячеек«), без указания десятичных дробей.

Подсчет отработанных часов за месяц обеспечиваем простейшей формулой складывания промежуточных формул, которые мы писали ранее в полях «Итого отработано…«.

Подсчет прочих данных об отработанном времени

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

По этой же причине я игнорирую пункт подсчета количества ночных часов. Раз уж я изначально предложил не рассматривать подобные случаи, то и сейчас начинать это делать бессмысленно. Тем не менее, при возникновении интереса, можно прописать алгоритмы учета работы и в ночное время.

С подсчетом количества часов, отработанных в праздничные дни, все — просто. Используем функцию «СУММЕСЛИ«: =СУММЕСЛИ(AG10:CN10;»=РВ»;AG9:CN9)+СУММЕСЛИ(CV10:FG10;»=РВ»;CV9:FG9)

Смотрим — что получилось. Укажем в один из дней код «РВ«. Получилась десятичная дробь. Проблема в том, что так настроился формат ячеек. Просто выделяем все ячейки, формат которых должен быть временной, и устанавливаем, собственно, тот, что указывает часы и минуты. Копируем формулу на остальные ячейки.

Количество неявок предлагаю указывать в днях, поскольку логика этого табеля предполагает именно такой подход. Неявка на работу отмечается кодами «ПР» и «НН«. Используем функцию «СЧЕТЕСЛИ«.  Поскольку у нас два условия, то мы их складываем. А, поскольку, два диапазона, до складываем еще два условия и по второму диапазону. Таким образом получается формула: =СЧЁТЕСЛИ(AG10:CN10;»=НН»)+СЧЁТЕСЛИ(AG10:CN10;»=ПР»)+СЧЁТЕСЛИ(CV10:FG10;»=НН»)+СЧЁТЕСЛИ(CV10:FG10;»=ПР»)

Копируем эту формулу на другие ячейки.

В колонке «Из них по причинам…» предусмотрено по две ячейки. Заносим в каждую из них коды «ПР» и «НН«, соответственно. А в следующих ячейках, также, с помощью функции «СЧЕТЕСЛИ» подсчитываем количество ячеек, отмеченных этими кодами. Копируем формулы на все прочие ячейки.

Аналогично заносим формулу подсчета количества ячеек с кодами «В» в колонке «Количество выходных и праздничных дней»: =СЧЁТЕСЛИ(AG10:CN10;»=В»)+СЧЁТЕСЛИ(CV10:FG10;»=В»). Должно получиться как на картинке.

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

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *