Группа "Гости"



Добро пожаловать!
О нас
Образование в ДОУ
Наш опрос
Оцените наш детский сад

В какой дополнительной платной услуге нуждается Ваш ребенок?
Поделиться
Чат
500

Каталог статей

Главная » Статьи » Копилка » ИКТ

Формулы Excel и GoogleSheets в табеле рабочего времени (дополнено)

Скачать файл "Табель рабочего времени" в excel

Форма Табеля взята с КонсультантПлюс, немного переделана для улучшения визуального восприятия, данные табеля перенесены на один лист, закреплена шапка, установлены разрыв листа и печать заголовков, добавлены формулы для расчета отработанных и пропущенных дней, часов.
Добавлен лист "Часы" (норма рабочих часов в день на ставку по должностям), на ячейки которого ссылаются формулы с листа "Табель", его можно скрыть. При ссылках на ячейки листа "Часы" следует добавлять символ $ перед значениями столбца и строки для закрепления адреса ячейки при копировании формул.

Титульный лист:

Количество рабочих дней в месяце может рассчитываться по формуле:
=ЧИСТРАБДНИ(ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1);ДАТА(ГОД(СЕГОДНЯ());
МЕСЯЦ(СЕГОДНЯ())+1;1)-1)

Но в случае наличия большого количества праздничных дней (например, в январе), формула легко не работает. Поэтому воспользуемся простым способом: сделаем значение этой ячейки равным значению отработанных дней кого-либо с основного листа табеля. Но учитывая, что именно этот сотрудник может отработать неполный месяц, возьмем максимальное значение из заданного диапазона, например, несколько ячеек с разных листов табеля: 
=МАКС('фев (2)'!BX10:CD11;'фев (2)'!BX14:CD15;'фев (2)'!BX30:CD31;'фев (2)'!BX38:CD39;'фев
(2)'!BX51:CD52)
 
Если есть декретницы, можно поступить проще: взять сумму пропущенных ими рабочих дней за две половины месяца: 
=СУММ('фев (2)'!DD194:DK194;'фев (2)'!DD196:DK196)
В любом случае надо проследить, чтобы значение получилось верным. Этот метод имел бы смысл, если вы каждый месяц вносите исправления в один и тот же лист (здесь лист "табель"), но я предпочитаю создавать копию листа "табель" для каждого следующего месяца, переименовав лист соответственно, так что проще проставить количество рабочих дней на титульном листе "от руки".

Табель подписывается последним числом месяца:  =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;0)
- получаем 30, 31 или 28(29) число месяца.
Текущий месяц высчитывается по формуле: 
=ВПР(МЕСЯЦ(СЕГОДНЯ());{1;"января":2;"февраля":3;"марта":4;"апреля":5;"мая":6;"июня":7;"июля":8;"августа":9;"сентября":
10;"октября":11;"ноября":12;"декабря"};2;)

В формуле прописываем нужное окончание месяца (январЬ или январЯ).
Последние две цифры текущего года
=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;0)
Полностью дата (последнее число текущего месяца текущего года): 
=КОНМЕСЯЦА(СЕГОДНЯ();0)

Лист "Табель"

Диапазон столбцов "Отметки о неявках на работу по числам месяца":
отработанные дни - пустые ячейки. Вручную проставляем выходные - В, пропуски: А - свой счет, Б - болезнь, О и ОУ - отпуска.
Диапазон столбцов "Отработано за":
за половину месяца отработанные дни рассчитываются по формуле: 
=СЧИТАТЬПУСТОТЫ(AW10:BL10)
То есть считаем пустые ячейки в диапазоне AW10:BL10 дней половины месяца;
отработанные часы считаем по формуле:
=BM10*часы!$AO$10
, где BM10- адрес ячейки с отработанными днями за полмесяца, который умножаем на часы!$AO$10 - адрес ячейки на листе "часы", где указана норма часов в день по должности. В адрес ячейки добавляем знак $ для закрепления адреса при копировании формулы.

За месяц количество отработанных дней получаем простым сложением отработанных дней за две половины месяца, например: 
=BM10+BM12
, где BM10 и BM12 - адреса ячеек с количеством отработанных дней за первую и вторую половины месяца.
Количество отработанных часов за месяц можно получить, складывая часы за полмесяца: 
=BM11+BM13
, или умножая количество отработанных дней на норму часов в день по должности с листа "часы": 
=BX18*часы!$AO$34.

Заполнение диапазона столбцов "Неявки за месяц":

Формулой 
=ЕСЛИ(СУММ(СЧЁТЕСЛИ(AW10:BL10;{"ОУ";"Б";"А";"О"}))=0;"";СУММ(СЧЁТЕСЛИ(AW10:BL10;
{"ОУ";"Б";"А";"О"})))

считаем пропущенные рабочие дни,
а формулой 
=ЕСЛИ(СУММ(СЧЁТЕСЛИ(AW10:BL10;{"ОУ";"Б";"А";"О"}))=0;"";СУММ(СЧЁТЕСЛИ(AW10:BL10;
{"ОУ";"Б";"А";"О"}))*часы!$AO$10)

считаем пропущенные рабочие часы.
Аналогично считаем пропущенные педагогические рабочие дни и часы. 
В данных формулах подсчитываем количество ячеек с обозначениями пропусков: (см. условные обозначения на титульном листе) О, ОУ, А или Б:
СЧЁТЕСЛИ(AW10:BL10;{"ОУ";"Б";"А";"О"})
Если в учреждении встречаются другие виды пропусков, можно добавить их обозначения в формулу через запятую.

Далее решаем проблему с отображением на листе нулевых значений, то есть, чтобы не выводился "0,00", если у сотрудника нет пропусков. Задаем условие: если количество ячеек с данными в виде О, ОУ, А или Б равно нулю, то в ячейке с формулой не выводятся никакие данные (двойные кавычки "" в формуле), иначе выводится число пропусков. Важно: в формуле не прописываем выходные (В), чтобы они не считались неявками.

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

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

P/S На момент публикации (январь 2018г.) в образце приведен пример табеля за февраль 2018 года, но на титульном листе даты выводятся январские, а количество рабочих дней взято с листа "табель" за февраль. Соответственно, в каком месяце вы скачаете данный файл, такие даты на титульном листе у вас и будут, т.к. задействована формула СЕГОДНЯ(), но наполнение листа "табель" не изменится.

 

Создать копию файла в GoogleSheets

При загрузке на гугл-диск все формулы переносятся и работают отлично, кроме формул для подсчета пропущенных дней и часов. Считает только значение, которое прописано в формуле первым, в данном случае это "ОУ".

Формула подсчета нескольких значений тоже не работает:

=IF(SUM(COUNTIFS(AW10:BL10;"ОУ";AW10:BL10;"Б";AW10:BL10;"А";AW10:BL10;"О"))=0;"";
SUM(COUNTIFS(AW10:BL10;"ОУ";AW10:BL10;"Б";AW10:BL10;"А";AW10:BL10;"О")))

 

Заработала формула только в таком виде:

=ЕСЛИ(СУММ(СЧЁТЕСЛИ(AW10:BL10;"ОУ");СЧЁТЕСЛИ(AW10:BL10;"Б");СЧЁТЕСЛИ(AW10:BL10;"А");
СЧЁТЕСЛИ(AW10:BL10;"О"))=0;"";СУММ(СЧЁТЕСЛИ(AW10:BL10;"ОУ");СЧЁТЕСЛИ(AW10:BL10;"Б");
СЧЁТЕСЛИ(AW10:BL10;"А");СЧЁТЕСЛИ(AW10:BL10;"О")))

то есть, каждое значение неявки считать отдельно.

Ваши замечания и предложения прошу оставлять в комментариях.

Категория: ИКТ | Добавил: adminer6126 (30.01.2018)
Просмотров: 45 | Рейтинг: 5.0/2
Вход на сайт
Поиск
Условия для детей
Родителям
Копилка
Отчеты
АХР
Наше творчество

Статистика

Онлайн всего: 1
Гостей: 1
Пользователей: 0
ЭО Ресурсы
 

 
 
 
 
 
 
 
 
 
 
 
Категории раздела
В помощь воспитателю [11]В помощь родителям [6]
Пожарная безопасность [2]Развитие речи [14]
Работа с родителями [21]Музыкальная шкатулка [3]
Методическая работа [2]Интерактивные примочки [11]
Лексические темы [17]Правила дорожного движения [3]
Здоровый образ жизни [7]
Питание, закаливание, физкультура
Патриотическое воспитание [7]
Аттестация педработников [2]ИКТ [29]
Презентации Smart Notebook [18]Экологическое воспитание [3]
Приобщение к чтению [14]Петербург. Россия [1]

Design by Olga Ermolick. © 2009-2018. All Rights Reserved
 Сайт ГБДОУ №17 Центрального района Санкт-Петербурга

Политика организации в отношении обработки персональных данных на сайте