Перейти к содержимому
Учи.Онлайн Выбрать школу
Статья

Excel для HR: штатка, текучесть, отпуска и отчёт по найму без ручных подсчётов

Excel в работе HR — это файл, где каждый сотрудник занимает одну строку с табельным номером, подразделением и датами приёма и увольнения, а отчёты собираются формулами и сводными таблицами. Из такой таблицы кадровик сверяет штатное расписание с фактической расстановкой, считает текучесть по подразделениям, стаж на любую дату и даты окончания отпусков, а рекрутер строит воронку найма. Нужно освоить «умные» таблицы, функции дат, СЧЁТЕСЛИМН, ПРОСМОТРX и сводные. Ниже — учебный пример, ловушки с датами, порядок освоения и выбор обучения.

Автор: редакция Учи.ОнлайнОтветственный редактор: Дмитрий Игнатьев
Содержание
Иллюстрация к статье: Excel для HR: штатка, текучесть, отпуска и отчёт по найму без ручных подсчётов

Большинство ошибок в кадровых таблицах возникает не в сложных формулах, а в датах. Дата увольнения, вставленная из выгрузки как текст, не даёт сообщения об ошибке: СЧЁТЕСЛИМН просто её не видит, и текучесть за квартал выходит ниже реальной. Стаж «лет, месяцев, дней» по популярной формуле из интернета иногда показывает отрицательные дни. Поэтому начинать стоит с устройства файла и проверки дат, а формулы метрик подключать потом.

Какие задачи HR решает в Excel, а какие оставляет учётной системе

Приказы, личные карточки, табель и начисления живут в 1С:ЗУП или другой кадровой системе. Excel берёт то, чего система не показывает в удобном виде: сравнить штатное расписание с фактом по каждому отделу, собрать отчёт для руководителя в нужном разрезе, проверить график отпусков на пересечения, посчитать конверсию этапов подбора. Исходные данные берут выгрузкой из системы, а не перепечатывают вручную.

Другой инструмент нужен, когда данные каждую неделю приходят из нескольких систем: их удобнее связывать в Power Query или базе данных, а это уже HR-аналитика.

Как устроить файл сотрудников

Основа — лист «Сотрудники», оформленный как таблица (Ctrl+T). Ключ — табельный номер, а не ФИО: тёзки и смена фамилии ломают любой поиск по имени. Подразделение и причину увольнения выбирают из выпадающего списка через «Проверку данных», иначе «Склад Север» и «склад север » окажутся в сводной двумя разными отделами. Даты приёма и увольнения — настоящие даты, у работающих поле увольнения пустое.

Данные о работниках и кандидатах — персональные данные по 152-ФЗ, поэтому в файл для отчётов не переносят паспорта, СНИЛС и адреса, а лист с окладами хранят отдельно. Защита листа в Excel лишь запрещает править ячейки и не скрывает содержимое; закрыть файл от посторонних может только пароль на открытие, который шифрует книгу.

Второй лист, «Движения», часто забывают. В нём одна строка — одно событие: табельный номер, дата, вид (приём, перевод, увольнение) и подразделение после события. Без него прошлый квартал считается по текущему подразделению: переведённый в октябре с «Севера» на «Юг» задним числом «уходит» из летних цифр «Севера».

Учебный пример: отчёт по трём складам

Ситуация условная. В компании три склада, в штатном расписании 120 ставок. Руководитель просит к 10 октября отчёт за третий квартал: сколько ставок свободно, какая текучесть на каждом складе и как идёт найм кладовщиков.

Сначала HR-менеджер добавляет в таблицу столбец «Работает на дату» с отчётной датой в именованной ячейке Отчёт:

=И([@[Дата приёма]]<=Отчёт; ИЛИ([@[Дата увольнения]]=""; [@[Дата увольнения]]>=Отчёт))

Знак «больше или равно» здесь не случаен. По ТК днём прекращения трудового договора считается последний день работы, и сотрудник, уволенный 30 сентября, в этот день ещё числится. Если поставить строгое «больше», численность на конец квартала занизится на всех, кто ушёл в последний день.

Сводные по штатному расписанию и по работающим дают расстановку: на складе «Север» 42 ставки и 38 человек на 37,5 ставки. Дальше считают уволившихся по собственному желанию:

=СЧЁТЕСЛИМН(Сотр[Подразделение];"Север";Сотр[Дата увольнения];">="&Нач;Сотр[Дата увольнения];"<="&Кон;Сотр[Причина];"собственное желание")

Формула вернула 3, хотя управляющий называет 5 уходов. Проверка простая: =СЧЁТЗ(Сотр[Дата увольнения])-СЧЁТ(Сотр[Дата увольнения]) показывает, сколько дат записано текстом. Две строки вставили из письма в виде «07.08.2026» с пробелом в конце. После исправления выходит 5 уволившихся при средней численности 40, текучесть по собственному желанию за квартал — 12,5 %. Для подразделения на прошлую дату используется лист «Движения» и ПРОСМОТРX с поиском с конца:

=ПРОСМОТРX(1; (Движ[Таб]=[@Таб])*(Движ[Дата]<=Отчёт); Движ[Подразделение]; "нет"; 0; -1)

Формула находит последнее событие до отчётной даты, если строки «Движений» отсортированы по дате.

Стаж и даты: где РАЗНДАТ подводит

Для стажа обычно берут РАЗНДАТ: =РАЗНДАТ(приём; Отчёт; «y») даёт полные годы, аргумент «ym» — месяцы сверх лет. Остаток дней многие считают аргументом «md», и здесь кроется ловушка. В справке Microsoft о функции РАЗНДАТ сказано, что функция оставлена для поддержки старых книг Lotus 1-2-3, что с аргументом «MD» результатом может быть отрицательное число, ноль или неверное значение и что использовать его не рекомендуется. Ошибочную ячейку в файле на сотни строк глазами не найти.

Надёжнее считать дни от даты, до которой уже набралось целое число месяцев:

=Отчёт-ДАТАМЕС([@[Дата приёма]]; РАЗНДАТ([@[Дата приёма]]; Отчёт; "m"))

Для приёма 15.03.2020 и отчётной даты 10.10.2026 получится 6 лет, 6 месяцев и 25 дней. Включать ли в стаж сам день окончания, решают один раз и прибавляют единицу во всех строках сразу.

Учтите и другое: стаж для отпуска не равен календарному. По ст. 121 ТК РФ в него не включаются прогулы, отпуск по уходу за ребёнком и та часть отпусков без сохранения зарплаты, что превышает 14 календарных дней за рабочий год. Если считать рабочий год просто от даты приёма, у таких сотрудников право на отпуск наступит в таблице раньше, чем по закону, поэтому нужен столбец с днями, сдвигающими рабочий год.

График отпусков и воронка найма

График отпусков утверждают не позднее чем за две недели до начала календарного года, и Excel помогает проверить его до подписи. Нерабочие праздничные дни в отпуск не входят и продлевают его, поэтому дату окончания удобно считать так: =РАБДЕНЬ.МЕЖД(Начало-1; Дней; "0000000"; Праздники). Строка из семи нулей означает «без выходных», а диапазон Праздники исключает только праздничные дни. Затем сводная проверяет, что у каждого набралось положенное число дней и хотя бы одна часть не короче 14 дней, а СЧЁТЕСЛИМН по календарной строке показывает, сколько людей одного отдела отдыхают в один день.

Для воронки найма заводят лист «Кандидаты»: одна строка — один кандидат на одну вакансию, столбцы с источником и датами отклика, интервью, оффера и выхода. Число прошедших этап — СЧЁТ по столбцу с датой. Частая ошибка — сравнивать выходы сентября с откликами сентября: вышедшие в сентябре откликались в июле. Конверсию честнее считать по когорте, то есть по месяцу отклика, и пересчитывать, когда когорта закроется.

С чего начать и в каком порядке осваивать

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

  1. Перенесите выгрузку сотрудников в таблицу с табельным номером, датами и выпадающими списками; найдите даты-текст через СЧЁТЗ и СЧЁТ.
  2. Добавьте столбец «Работает на дату» и посчитайте численность на 1-е число каждого месяца.
  3. Освойте РАЗНДАТ без «md», ДАТАМЕС и РАБДЕНЬ.МЕЖД на стаже и отпусках.
  4. Сверьте штатное расписание с фактом через ПРОСМОТРX по коду позиции.
  5. Постройте сводные по подразделениям и причинам увольнения со срезами.
  6. Заведите лист «Движения» и пересчитайте прошлый квартал.

Первые четыре шага можно пройти самостоятельно на обезличенной выгрузке. Если нужна программа, где Excel идёт вместе с кадровыми метриками, посмотрите курс Skillbox «HR-аналитика с нуля»: три месяца по 3–5 часов в неделю, от 173 часов практики и от 11 заданий. Численности, укомплектованности и текучести там отведён отдельный блок, а Excel разбирают в модуле «Excel, Python и автоматизация» как инструмент быстрых расчётов. Выдают сертификат школы, не диплом о переподготовке.

Открыть курс «HR-аналитика с нуля»

Упражнения и самопроверка

Хорошая тренировка — посчитать текучесть квартала двумя путями: через СЧЁТЕСЛИМН и сводной с фильтром по датам. Расхождение обычно указывает на даты-текст, лишние пробелы в названиях отделов или сотрудников без подразделения. Второе упражнение — посчитать стаж десяти сотрудников вручную по календарю и сравнить с формулой, специально включив приёмы 31-го числа. Третье — убедиться, что формула продлевает отпуск, захвативший 1 мая.

Результат готов, если коллега по вашему описанию правил получает те же числа, а отчёт обновляется заменой выгрузки без правки формул. Если дальше хочется уверенно работать с функциями дат, поиском и сводными, подойдёт курс МГУТУ «Excel + Google Таблицы с нуля до PRO»: 40 академических часов и четыре практические работы с проверкой, в программе проверка ввода, функции дат, сводные со срезами и Power Query. Стоимость на странице не указана — уточните её у школы.

Открыть курс «Excel + Google Таблицы с нуля до PRO»

Как навык влияет на доход

Отдельной надбавки «за Excel» в кадровых вакансиях обычно нет: таблицы указывают в требованиях рядом с 1С:ЗУП и знанием ТК. Открытых свежих данных, где сравнивались бы зарплаты HR с таким навыком и без него, найти не удалось, поэтому цифр здесь нет.

Влияние косвенное: тот, кто сам собирает отчёт по численности и текучести, получает задачи по планированию штата, а оттуда ведут пути в HR-аналитику, компенсации и льготы, HR бизнес-партнёрство. Проверить рынок можно на сайтах вакансий: задайте регион и опыт, сравните медиану предложений с требованием «сводные таблицы» и без него, следя, указана сумма до вычета налога или на руки.

Как выбрать обучение

Excel для HR редко преподают отдельно: его дают на общих курсах по таблицам или внутри HR-программ. Проверьте, на каких данных идут задания — кадровых или продажах, разбирают ли функции дат и проверяет ли работы человек.

Для тех, кто метит в бизнес-партнёры, есть курс Skillbox «HR бизнес-партнёр + ИИ» на пять месяцев с проектами «годовой план HR-отдела» и «карта жизненного цикла сотрудника». Excel на нём входит в дополнительные дисциплины вместе с PowerPoint, 1С:ЗУП и электронным документооборотом, а насколько глубоко его разбирают и сколько практики даёт тариф, на странице не сказано — уточните у школы.

Открыть курс «Профессия HR Бизнес-партнёр»

Если Excel уже рабочий инструмент, выбирайте HR-программу с метриками и проектом; если таблицы пугают, начните с короткого курса и переносите упражнения на свою выгрузку.

Сравнить школы по цене, формату и документам поможет рейтинг онлайн-школ по HR-менеджменту и HRBP. Это широкий рейтинг всего HR-направления, а не рейтинг по Excel. Проверка готовности простая: соберите по этим правилам отчёт по своей выгрузке за прошлый квартал и посмотрите, на каком шаге пришлось остановиться.