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

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

Табель подписывается последним числом месяца: =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;0)
— получаем 30, 31 или 28(29) число месяца.

Текущий месяц высчитывается по формуле:
=ВПР(МЕСЯЦ(СЕГОДНЯ()); 10;»октября»:11;»ноября»:12;»декабря»>;2;)
В формуле прописываем нужное окончание месяца (январЬ или январЯ).

Последние две цифры текущего года:
=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;0)

Полностью дата (последнее число текущего месяца текущего года):
=КОНМЕСЯЦА(СЕГОДНЯ();0)

или, если требуется именно последний РАБОЧИЙ день, исключая праздничные/нерабочие дни: =РАБДЕНЬ(КОНМЕСЯЦА(СЕГОДНЯ();0)+1;-1;Праздники2022)

отработанные часы считаем по формуле:
=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;)
Если в учреждении встречаются другие виды пропусков, можно добавить их обозначения в формулу через запятую.

P.S. Если вдруг человек увольняется или поступает на работу посреди месяца (ставим ноль в табеле), добавляем 0 (ноль) в перечень условных обозначений: .

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

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

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

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

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

=ЕСЛИ(СУММ(СЧЁТЕСЛИ(AW10:BL10;»ОУ»);СЧЁТЕСЛИ(AW10:BL10;»Б»);СЧЁТЕСЛИ(AW10:BL10;»А»);
СЧЁТЕСЛИ(AW10:BL10;»О»))=0;»»;СУММ(СЧЁТЕСЛИ(AW10:BL10;»ОУ»);СЧЁТЕСЛИ(AW10:BL10;»Б»);
СЧЁТЕСЛИ(AW10:BL10;»А»);СЧЁТЕСЛИ(AW10:BL10;»О»)))
то есть, каждое значение неявки считать отдельно.

Одним из самых распространенных примеров применения автозаполнения является ввод подряд идущих чисел. Допустим, нам нужно выполнить нумерацию от 1 до 25. Чтобы не вводить значения вручную, делаем так:

Как сделать автозаполнение ячеек таблицы Excel: формулами и другими данными

Индикатор получаем при помощи функции IF , в которой вычисляется логическое условие в первом аргументе. Если номер текущего столбца, поправленный на величену TOffset , меньше или равен числу дней в выбранном месяце, то формула выдаёт 1, если нет — 0. Просто и наглядно из-за использования именованных ячеек.

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

А как быть с кадровиками?

1. Выберите ячейку.
2. В группе Дата/Время нажмите на кнопку «Вставить дату и время» > Всплывающий календарь с часами появятся рядом с ячейкой.
   Или: по правому клику мыши выберите пункт «Вставить дату и время».
   Или: используйте сочетание клавиш: нажмите Ctrl+; (точка с запятой на английской раскладке), затем отпустите клавиши и нажмите Ctrl+Shift+; (точка с запятой на английской раскладке).
3. Установите время при помощи колеса прокрутки мыши или стрелок Вверх/Вниз > Выберите дату из всплывающего календаря > Готово.
   Обратите внимание на формат: это то, что вам нужно? Вы можете задать другой формат по умолчанию для Всплывающего Календаря и Часов .
4. Чтобы изменить значение, нажмите на иконку справа от ячейки > Измените дату и время.

1. В таблицу отдельными строками включаются отдельные периоды отпусков
2. В таблице указываются даты начала отпуска и продолжительность
3. Список упорядочен по алфавиту фамилий сотрудников и по возрастанию дат начала