Здесь размещены шаблоны табеля учета рабочего времени в Excel
.
В шаблонах производится автоматический подсчет рабочих часов и минут
.
Автоматически устанавливаются дни недели в зависимости от месяца
. Например: в феврале месяце первое число приходится на субботу. Вы ставите в ячейке, под цифрой "1" обозначение "Сб", нажимаете "Enter" и все дни недели поменяются на правильные дни недели в этом месяце.
Ставить часы и минуты работы в табеле нужно через двоеточие.
Так настроены ячейки, чтобы правильно считать время. Например: 8:15
.
Подробнее смотрите в статье "Как посчитать время в Excel".
Для скорости заполнения табеля, можно заполнить первую строку, затем копировать вниз по табелю, вносить поправки по больничным листам, отпуску, др.
=ЕСЛИ(C$9="Пт";6;ЕСЛИ(ИЛИ(C$9="Сб";C$9="Вс");"В";8))
Эту формулу можно установить в ячейки С10:AG54. Сначала в этих ячейках и в столбце H10:H54 (итого часов, минут) нужно установить общий формат ячеек. Эта формула будет автоматически ставить в табеле часы работы с понедельника по четверг - по 8 часов, в пятницу - 6 часов. Количество часов в формуле можно изменить.
Этот бланк с формулами можно сохранить шаблоном. При необходимости можно вручную менять количество отработанных часов по конкретному человеку.
Табель настроен так, что распечатывается на одной странице листа формата А4. Если, для кого - то это мелко, можно изменить настройки.
Как настроить таблицу для печати, смотрите в статье "Печать в Еxcel" .
В табеле можно производить изменения, корректировки шаблона. Можно в новом столбце установить формулу, которая будет умножать время на деньги. Смотрите "Как умножить время на число в Excel".
Шаблон табеля учета рабочего времени в Excel скачать (rar).
В следующем табеле две строки на одного человека, чтобы считать отдельно дневные и ночные часы .
Скачать шаблон (rar).
Как сделать табель рабочего времени, табель дежурств, отпусков, т.д., смотрите в статье "Табель рабочего времени в Excel" .
Такой автоматический гарфик смен можно скачать .
Можно сделать несколько разных бланков в одной книге Excel, соединить их формулами, получится
программа в Excel
. Смотрите образец такой программы в статье "Табель, ведомость, расчетный лист в Excel"
.
Как самим установить автоматическую смену дней недели, в зависимости от месяца, смотрите в статье "День недели по дате Excel ".
Можно в таком бланке установить ссылки, по которым можно будет перейти в другую таблицу на другом листе с полными данными конкретного сотрудника. Какими способами сделать такие ссылки, смотрите в статье "Гиперссылка в Excel на другой лист" .
Можно самим сделать любую программу. Какие возможности Excel можно использовать для этого, смотрите в статье "Как создать программу в Excel" .
Как расчитать комиссионные и премию сотрудникам, читайте в статье "Функция сравнения в Excel" .
Как копировать готовый бланк из Word в Excel, смотрите в статье "
Заключая с работниками трудовые контракты хозяйствующий субъект должен организовать учет времени их работы. Для этих целей может применяться табель учета рабочего времени, который открывается каждый месяц, и в нем ответственное лицо отражает время работы сотрудников, их отпуска, больничные и другие виды неявки на работу. По данным, содержащимся в этом документе, впоследствии начисляется зарплата.
Законодательство требует от администрации организации или ИП организации и ведения учета периодов работы по каждому сотруднику. Заполнение табеля учета рабочего времени может производиться ответственным лицом, которое определяется распоряжением руководства.
Чаще всего такими лицами могут выступать руководители подразделений, кадровые работники, бухгалтера и т. д. В их обязанность входит внесение в табель с помощью кодов и шифров периодов работы.
С развитием технических средств для учета времени работы может также использоваться специализированная система с использованием карт, с помощью которых происходит фиксирование появления и ухода работника на предприятии. Учет рабочего времени может вестись сплошным отражением работы или суммировано.
В дальнейшем информация из табеля учета времени работы используются при начислении зарплаты, особенно при повременной системе. Вместе с трудовым контрактом табель рабочего времени является одним из обоснований расходов предприятия, особенно при налогообложении.
Табель рабочего времени не только фиксирует рабочее время, но и позволяет контролировать соблюдение работником трудовой дисциплины. С помощью его осуществляется контроль за соблюдением норм продолжительности работы и выявлением сверхурочной работы. Многие отчеты, сдаваемые в статистику и содержащие данные кадрового учета, заполняются на основании табеля.
Важно! Если в компании не ведется табель рабочего времени, то контролирующие органы могут применить к ней соответствующие меры наказания.
Как учитывается рабочее время сотрудника?
Законом устанавливается два типа нормативов - шестидневная рабочая неделя (36 часов) и пятидневная рабочая неделя (40 часов). То есть работники могут работать пять дней при восьмичасовом дне работы, или же шесть дней - при шестичасовом дне. Нарушение их допускается в редких случаях - при суммированном учете или же ненормированном графике.
В первом случае применяются нормы в более укрупненном периоде времени, например, квартал, полугодие и т. д. Получается что в меньшем периоде работы факт может не соответствовать действующим нормам, но он не должен превышать нормативы в выбранные более крупные временные промежутки.
Для некоторых работников может применяться пониженная дневная норма или же недельная. Как именно нужно учитывать время работы сотрудников необходимо зафиксировать в . В табеле необходимо также отражать все время, когда работник не работал, но числился на предприятии.
К таким периодам может относиться:
- Больничный.
- Период простоев и т. д.
Табель открывается в начале месяца, а по его окончании его закрывают. Ответственное лицо в середине месяца подводит промежуточный итог, отражая данные за первую часть времени работы. Документ подписывается руководителем подразделения и передается для проверки в кадровую службу. Затем данный он передается в бухгалтерию для расчета зарплаты.
Внимание! Табель учета рабочего времени на 2017 год может быть, как и в предыдущие периоды, двух видов – форма т-12 и форма т-13. Первый предполагает не только учет времени работы, но и возможность начисления зарплаты. Форма Т-13 используется только для фиксации времени работы, для начисления оплаты труда используются другие документы.
Правомерно ли применять электронные системы контроля?
Законодательством предусмотрена обязанность работодателя по учете времени работника. Для этих целей он имеет право использовать различные электронные системы. Но чтобы их применять на практике, администрация компании должна в правилах внутреннего распорядка и в заключаемых с работниками трудовых контрактах отразить данный момент.
Если этого не сделать, то применять данные электронные системы нельзя.
С помощью различных средств фиксируется время появления и уходы с предприятия. В дальнейшем подобная система на основании полученных данных заполняет в автоматическом режиме табель.
Скачать бланк и образец заполнения табеля
Табель учета рабочего времени скачать бланк в формате Excel, по и по .
В формате Word.
В формате Excel.
Внимание! Если причина отсутствия неизвестна, то в табеле нужно проставить буквенный шифр «НН». В дальнейшем, этот код уточняется. Если сотрудник в этот период болел, то код исправляется на «Б». В том случае, когда оправдательных документов нет, то вместо кода «НН» вносится код «ПР».
Праздничные дни выпали на время отпуска
Согласно ТК, если на период отпуска выпадают праздничные дни, то в расчет календарных дней они не включаются.
Когда сотруднику предоставляется ежегодный отпуск, то в течение его периода выходные дни в табеле не отмечаются, потому как они входят в число календарных дней - на их место ставится буквенный код «ОТ» либо цифровое обозначение 09 для ежегодного отпуска, а также код ОД либо обозначение 10 - для дополнительного отпуска.
Внимание! Нерабочие праздничные дни в число календарных дней не включаются. Поэтому, в табеле такие дни необходимо обозначать буквенным кодом «В» либо цифровым 26.
Сотрудник заболел во время отпуска
Если во время нахождения на отдыхе работник заболел, то для подтверждения этого факта ему необходимо предоставить правильно оформленный больничный. В результате, дни отдыха должны быть продлены на время нахождения на больничном, или же перенесены на другое время.
Первоначально, время отпуска должно отмечаться в табеле буквенным кодом «ОТ», либо цифровым обозначением 09. После того, как предоставлен больничный листок, то табель должен быть скорректирован - на дни болезни вместо предыдущего обозначения записывается код «Б» либо цифровое обозначение 19.
Командировка выпала на выходные
Согласно письму Минтруда, все дни командировки в табеле нужно обязательно отмечать, даже если они выпадают на выходные дни. Для этого нужно использовать обозначения в табеле – специальный буквенный код «К» или цифровое обозначение 06. При этом количество часов проставлять не надо.
Если в момент нахождения в командировке сотрудник работал в выходные дни, то в табеле они отмечаются кодом «РВ» – работа в выходные, либо цифровым обозначением 03. Число часов работы при этом нужно проставлять только в одном случае - когда руководство компании дало работнику конкретное указание, какое количество часов выходной день ему необходимо посвятить работе.
Внимание! Более подробно о том, как оплачивается, отмечается , а также другие особенности описаны в данной статье.
В любых организациях в обязательном порядке ведется табель учета рабочего времени. Правила оформления этого документа, его назначение и готовый пример, который можно использовать в качестве образца – все это рассматривается далее.
Основное назначение
Планируемая продолжительность смены и общее количество рабочих дней в месяце практически всегда будут отличаться от часов и дней, которые были отработаны фактически. Для фиксирования факта и ведется табель учета: он позволяет собрать все сведения о рабочем времени, которое было на самом деле отработано.
Назначение этого документа двойное:
- Получить сведения обо всем отработанном периоде времени.
- Получить данные по неявкам за аналогичный период.
Подобная информация пригодится, прежде всего, бухгалтеру. Также сведения понадобятся некоторым проверяющим лицам – соответствующее описание приведено в таблице.
| бухгалтер | расчет всех выплат, которые необходимо перечислить работающим: зарплата, отпускные, командировочные и др. |
| представитель ФНС | инспекторов интересует корректность начисления выплат и налогов с них: часто компанию проверяют на предмет того, не была ли занижена налоговая база |
| сотрудник ФСС | отработанное время интересует фонд в связи с расчетом социальных пособий (например, по уходу за ребенком) |
| работник трудовой инспекции | инспекторов интересует, не были ли нарушены права работающих |
| представитель Росстата | сотрудники Росстата собирают статистические данные – например, по информации из табеля оформляют единую |
Форма: бланк и образец
Каждая компания вправе применять как собственный образец, так и вести учет рабочего времени с помощью специальной формы Т-12 . Можно взять за основу именно ее бланк (он приводится ниже) и адаптировать под потребности компании.
Документ состоит из 3 основных частей:
- На титульной странице приводится единая система кодов, с помощью которых обозначают, например, дополнительные выходные (в счет переработок), заболевания по больничным, простой в связи с виной работодателя и др. Каждое обстоятельство имеет буквенный и цифровой код.
- Вторая (табличная) часть – это собственно учет рабочего времени. Он осуществляется в каждодневном режиме (включая дни выходные и праздничные).
- И третья часть представлена также в виде таблицы. В ней приводятся сведения по выплату зарплаты (суммы, часы и дни, ставка).
Вот как выглядит пустой бланк формы Т-12.
Наряду с формой Т-12 существует и Т-13. В ней отсутствует последняя (третья) часть – то есть этот документ представляет собой простой табель для фиксирования рабочего времени, без расчетов по зарплате. Готовый пример документа показан ниже.
Порядок ведения
Ведут документ специально назначенные сотрудники: они фиксируют рабочее время по каждому дню. Как правило, ответственный за корректное оформление – это директор структурного подразделения (например, отдела продаж). Ответственность может разделить и его заместитель. Если компания достаточно большая, вводят специальную должность табельщика, который и фиксирует все сведения.
В любом случае ответственные лица всегда назначаются руководителем, о чем выходит соответствующий приказ (образец свободный) – например, документ, показанный ниже.
ОБРАТИТЕ ВНИМАНИЕ. С текстом приказа все ответственные должны ознакомиться и поставить свою подпись и дату.
В общем случае порядок выглядит так:
- Ответственное лицо фиксирует сведения за каждый день.
- После заполнения (по истечению месяца) документ передают в отдел кадров.
- После отдела кадров он поступает в бухгалтерию.
- Последняя подпись остается за руководителем структурного подразделения.
ОБРАТИТЕ ВНИМАНИЕ. Когда документ будет заполнен и подписан всеми ответственными лицами, то его подшивают и отправляют в архив на хранение. Минимальный срок хранения составляет 5 лет. Но если труд на предприятии осуществляется в опасных и вредных условиях, время хранения значительно увеличивается – не менее 75 лет.
Как заполнять табель: пошаговая инструкция
При заполнении применяется единая система обозначений. В табеле осуществляется учет отработанного времени в соответствии с правилом «для одной должности предназначается одна позиция». Берутся во внимание только те сотрудники, которые имеют трудовой договор, в том числе внутренние совместители – по ним, в частности, необходимо прописать сведения дважды.
Не учитываются данные по таким работникам:
- трудящимся неофициально;
- внешним совместителям;
- работающим на основании гражданского договора.
Порядок заполнения предусматривает корректное оформление титульного листа и собственно табличной части.
Титульный лист
Здесь фиксируются такие сведения:
- Название компании (допускается краткий вариант, например, ООО «Альфа»).
- Коды по ОКУД и ОКПО.
- Номер – систему нумерации компания выбирает самостоятельно. Например, распространен вариант последовательного присвоения номеров, в течение календарного года.
- Отчетный период – т.е. месяц с датами начала и окончания ведения документа.
- Под датой составления имеется в виду последний день, после которого его должны подписать все ответственные сотрудники. Затем документ переходит в архивное хранение.
Табличная часть
Здесь нужно заполнить все графы:
Сведения для расчета зарплаты
Если ведется форма Т-12, то заполняется и этот раздел. Здесь фиксируются сведения в основном о 2 формах выплаты:
- Собственно зарплата (обозначается 4-значным кодом 2000).
- Выплаты за отпускные дни (обозначается кодом 2012).
Все суммы списываются с так называемого корреспондирующего счета – он будет одним и тем же, вне зависимости от вида выплаты.
Как обычно, рабочее время фиксируется с учетом суммарного количества дней и часов.
В конце документа ставят подписи все уполномоченные сотрудники:
- лицо, ведущее документ (если таковой есть)
- представитель отдела кадров;
- начальник подразделения.
Видео инструкция по оформлению
Видео комментарий по заполнению:
Дополнительный лист
Есть несколько случаев, когда самого табеля не хватит, поскольку учет предполагает фиксирование дополнительной информации. Тогда следует оформить еще один лист:
- Если сотрудник увольняется в середине месяца или в начале. В этом случае все фактически отработанные дни и часы фиксируются как раз на дополнительном листе. А в бланке делают запись «Уволен» ровно той датой, когда и состоялось увольнение. Затем документ сдается вместе с дополнительным листом.
- Также он понадобится, когда работник не работал, но при этом на контакт не вышел и о причинах своей неявки не уведомил. Если он так и не появился (или не взял документов, подтверждающих уважительность причины), и наступило время сдавать документ, ставится код 30 (буквенное обозначение «НН»).
В подобных случаях лучше делать все отметки карандашом. Если впоследствии выяснится, что работник, например, получил больничный, следует проставить отметку по коду 19 (буква «Б»).
Суммированный учет: особенности расчета
Если нормальное количество часов (8 часов в день и 40 часов в неделю) соблюдать не удается, то общую продолжительность фактически выработанного времени определяют как простую сумму. Такой порядок существует на предприятиях, которые:
- работают круглосуточно, посменно;
- используют гибкий график;
- организуют вахтовый метод работы.
Тогда основным понятием становится учетный период. – календарный месяц, 1 квартал или весь год. Конкретный интервал выбирается в зависимости от особенностей работы предприятия. Например, если предполагается труд в опасных и вредных условиях, то за основу берется период в 1 квартал.
Если в течение какого-либо времени сотрудник не выполнял работу по уважительным причинам, то это время не учитывается (т.е. оно исключается полностью).
Нестандартные ситуации: что делать
В большинстве случаев документ оформлять достаточно просто, поскольку учет основан на равномерном, одинаковом распределении рабочего времени по каждому сотруднику. Однако подобный порядок нередко нарушается в силу определенных обстоятельств, например:
- Если коллега отпрашивается, и руководитель не возражает, то фиксируют только фактически отработанные часы (целым числом). Отсутствие обозначают пометкой «Я» или двумя цифрами «01».
- Если он заболел, то ставят «Б», а в нижней части поля оставляют пустыми. Разумеется, наличие больничного – обязательное требование для такой пометки.
- Если был запланирован , и работник взял его по согласованию, в соответствии с действующим порядком, ставят обозначение «НВ» (в цифровом варианте код «28»). Бывают случаи, когда истинные причины отсутствия временно неизвестны. Тогда можно поставить «НН», но в случае прояснения ситуации выбирается соответствующее обозначение, а «НН» зачеркивается.
- Если коллега отправился в командировку, помечают «К». Когда он официально вернется и приступит к обычным обязанностям, ставят букву «Я».
Как внести изменения
Учет рабочего времени предполагает фиксирование информации, которая может измениться в течение дня или недели, поэтому и табель предусматривает возможность внесения изменений. Корректировки необходимо вносить для того, чтобы отразить фактическую ситуацию, истинность которой подтверждается документально.
При этом существуют 2 способа внесения исправлений:
- Допускается наличие неточностей, но при этом документ дополняется так называемым корректирующим бланком. Обе бумаги подаются для проверки вместе.
- Корректируется исходный документ, а дополнительный бланк не составляется. Тогда нужно осторожно перечеркнуть все неверные данные. Это делается с помощью горизонтальной черты. Если ошибочные сведения содержатся в соседних полях, прочеркивается единая линия.
При этом обязательно вносится соответствующая запись, которая детально объясняет ситуацию, например:
Ее можно сделать на ближайшем свободном поле.
Ответственность за некорректное ведение
Корректное ведение документа – прямая обязанность компании. В противном случае могут последовать довольно ощутимые штрафные выплаты.
Интересно, что в Трудовом кодексе нет как таковой статьи, которая устанавливает ответственность за отсутствие табеля. Однако в КоАП она есть – это статья 5.27. Регламентируется, что отсутствие документа приведет к штрафным санкциям:
- Для ответственных лиц от 1000 до 5000 рублей.
- Для компании как юридического лица от 30000 до 50000 рублей.
В случае наличия табеля с ошибками применяются иные меры. Они устанавливаются в зависимости от того, будет ли выявлен злой умысел, повлекший эти ошибки. Например, компания приписывает сотруднику дни, фактически не отработанные, чтобы «уменьшить» свою прибыль и благодаря этому оплатить меньший налог. Санкции будут применяться в соответствии с конкретными обстоятельствами нарушения (во многом они будут зависеть от суммы).
1
оценок, среднее: 5,00
из 5)
Табель учета рабочего времени – служит основанием для расчета зарплаты сотрудникам. Данный документ содержит сведения о наличии сотрудников на своих рабочих местах или же об их отсутствии с указанием причины. Для заполнения можно воспользоваться унифицированной формой Т-13 или же формой Т-12. В рамках этой статьи мы подробно рассмотрим особенности заполнения табеля формы Т-13, приведем образец заполнения табеля учета рабочего времени на примере формы Т-13. Кроме того, вы можете скачать бланки обеих форм ниже.
Табель учета рабочего времени представляет собой таблицу, в которой для каждого сотрудника предприятия учитывается явки и неявки на работу. Выводится общее количество отработанных дней и часов за месяц. Помимо этого, указываются кодовые обозначения причин неявок на работу.
Табель учета рабочего времени форма Т-13 – образец заполнения
Прежде всего, стоит отметить, что заполнение бланка может выполняться двумя способами:
- отмечаются все явки, неявки на работу ежедневно;
- отмечаются только невыходы на работу, опоздания и прочие отклонения от нормального рабочего дня.
В нашем образце табеля использован первый метод учета рабочего времени.
Прежде всего, нужно внести данные о выходах сотрудников на работу в течение месяца в табель учета рабочего времени. Бланк Т-13 для этого имеет таблицу, в которой для каждого сотрудника заполняется отдельная строка.
В графе 2 и 3 ставится ФИО, должность работника, его табельный номер.
В графе 4 отмечаются явки и неявки на работу. Для каждого календарного дня месяца – отдельная клетка, сверху указывается кодовое обозначение, ниже количество часов отработанных за этот день. Возможен вариант, когда нижняя клетка не заполняется, при этом учет рабочего времени ведется в днях.
Кодовые обозначения можно найти на титульном листе табеля форма Т-12.
В представленном ниже образце заполнения формы Т-13 для учета рабочего времени были использованы следующие буквенные коды:
- Я – день отработан полностью;
- В – выходной день;
- К – нахождение работника в командировке;
- Б – на больничном;
- ОТ – в отпуске.
По окончании месяца следует посчитать количество отработанных дней и часов в первой и второй половине месяца, для этого табель имеет графы 5, 6. В пятой – в верхней клетке отражается количество отработанных дней за первую и вторую половину месяца, в нижней клетке – количество отработанных часов за каждую половину месяца. В шестой – указывается количество отработанных дней (верхняя клетка) и часов (нижняя клетка) за весь месяц. На рисунке ниже показан пример заполнения 1-6 колонку табеля учета рабочего времени.
Пример заполнения 1-6 колонку табеля учета рабочего времени (форма Т-13)
Графы 7-9 бланка Т-13 содержат сведения, которые пригодятся для .
Для каждого вида оплаты труда существует отдельное кодовое обозначение, например:
- 2000 – это кодовое обозначения оплаты труда и командировочных;
- 2012 – код табеля учета рабочего времени для оплаты отпускных;
- 2300 – код при оплате больничного листа и пр.
Наш пример заполнения табеля учета использования рабочего времени содержит указанные выше три кода.
Если для всех работников использован только один вид оплаты труда, то следует заполнить следующие данные:
- код вида оплаты – вверху граф 7-9, единый для всех работников;
- корреспондирующий счет – счет, на который относятся затраты по данному виду оплаты труда;
- 7, 8 графы остаются пустыми;
- 9 – количество отработанных за месяц дней (часов) для каждого работника.
Если же используются несколько видов оплаты труда, то заполнять нужно все три графы: 7, 8, 9.
В седьмой ставится код оплаты труда, в восьмой – счет для отнесения затрат по данному виду оплаты, в девятой – количество дней или часов, соответствующих данному виду оплаты. Эти данные заполняются для каждого работника, указанного в табеле рабочего времени.
Остальные графы таблицы содержат сведения о причинах неявок, опозданий работников.
Здесь также имеются свои кодовые обозначения причин неявок, например:
- К – командировка;
- Б – больничный;
- ОТ – отпуск и пр.
Рядом с указанием кода причины ставится количество часов или дней отсутствия работника по этой причине. Заполнение табеля учета рабочего времени обычно возлагается на руководителей отделов или подразделений либо же на работников кадровой службы.
Заполнение табеля учета рабочего времени по форме Т-13 (колонки 7-13)
Для организации кадрового учета в компании начинающим кадровикам и бухгалтерам отлично подходит авторский курс Ольги Ликиной (бухгалтера М.Видео менеджмент) ⇓
Скачать табель учета рабочего времени (форма Т-13). Образец и бланк в Excel
Ниже можно скачать бланк табеля учета рабочего времени на одном листе, а также в качестве примера ниже можно посмотреть заполненный образец документа.
Видео-урок “Табель учета рабочего времени. Форма Т-13”
В данном видео-уроке показан подробный пример заполнения табеля учета рабочего времени по форме Т-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. Просто и наглядно из-за использования именованных ячеек.
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. Да, в шаблоне ещё есть небольшой макрос, который очищает колонки D n:AH n, когда вы удаляете фамилию в строке A n (где n - номер строки). Это сервисная функция для полноты картины.
