Автоматизация заполнения должностей в Excel

Как то раз мне понадобилось заполнить должности сотрудников на определенные даты по их ФИО, учитывая все их карьерные перемещения. В статье рассмотрим способы, как это сделать с помощью формул в Excel.

Постановка задачи

Допустим, есть реестр должностей:

Как видно по таблице, двое сотрудников в течение года меняли должности. В столбце С указана дата вступления в должность. На другом листе Excel вторая табличка — с датами отчетов и фамилиями, а должности предстоит заполнить:

То есть, нужно найти должность, которую занимал сотрудник на заданную дату. Также нужно учесть, что сотрудника может не быть в штате на момент составления отчета. Это можно сделать сочетанием функций ИНДЕКС и ФИЛЬТР.

Сделаем реестр «умным»

Для начала превратим реестр в «умный» реестр при помощи умных таблиц. Это упростит работу с формулами, но в целом не обязательно.

Далее дадим реестру название. Вместо названия по умолчанию в поле «Имя таблицы» укажем «Реестр». Теперь в формулах вместо ссылок на диапазон можно использовать понятные названия столбцов (например, Реестр[ФИО] или Реестр[должность])

На этом подготовительный этап окончен, можно приступать к вычислениям должностей.

Вычисляем должности

Перейдем на лист с табличкой для заполнения и в ячейку С2 забьем формулу:

=ФИЛЬТР(Реестр[должность]; (Реестр[ФИО] = B2) * (Реестр[действует с] = МАКС(ФИЛЬТР(Реестр[действует с];(Реестр[ФИО] = B2) * (Реестр[действует с] <= A2); 0))); "Отсутствовал в штате на эту дату")

Затем растянем ее на весь столбец. Результат:

Должности заполнены😎😎😎

Разбор формулы

Разберем формулу по частям. Чтобы было нагляднее, для примера возьмем формулу из ячейки С10:

Сама формула:

=ФИЛЬТР(Реестр[должность]; (Реестр[ФИО] = B10) * (Реестр[действует с] = МАКС(ФИЛЬТР(Реестр[действует с];(Реестр[ФИО] = B10) * (Реестр[действует с] <= A10); 0))); "Отсутствовал в штате на эту дату")

Разберем, что делает функция МАКС

МАКС(ФИЛЬТР(Реестр[действует с];(Реестр[ФИО] = B10) * (Реестр[действует с] <= A10); 0))

Что тут происходит:

1. Сначала фильтруются все записи в реестре по двум условиям:

   • ФИО должно совпадать с указанным в ячейке B10 (Иванов А. В.)

   • Дата вступления в должность должна быть не позже даты отчета (ячейка A10)

   Знак * между скобками означает, что нужно, чтобы выполнились оба условия. В нашем случае останется 3 подходящих записи:

   

2. Затем из всех подходящих дат выбирается самая поздняя (используя МАКС), то есть наиболее близкая к дате отчета:

   

   Если бы подходящих записей не нашлось, формула вернула бы 0.

По сути, формула определяет, когда сотрудник занял последнюю (на дату отчета) должность.

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

МАКС(ФИЛЬТР(Реестр[действует с];(Реестр[ФИО] = B10) * (Реестр[действует с] <= A10); 0))

Если вместо этого выбрать весь реестр, функция ФИЛЬТР вернет не список дат, а диапазон, вместе с ФИО и должностями. Тогда могут возникнуть проблемы с поиском максимальной даты.

Примечание. В нашем примере проблем то не возникнет — МАКС проигнорирует текстовые значения и найдет-таки максимальную дату. Но, если бы в реестре был еще один числовой столбец, или столбец с датами, то МАКС нашел бы максимум среди ВСЕХ дат и чисел.

Итак, что делает МАКС разобрали, переходим к формуле целиком.

=ФИЛЬТР(Реестр[должность]; (Реестр[ФИО] = B10) * (Реестр[действует с] = МАКС(...))); "Отсутствовал в штате на эту дату")

Что происходит:

1. Фильтруется список должностей из реестра по двум критериям:

   • ФИО должно совпадать с указанным в ячейке B10

   • Дата начала действия должности должна совпадать с той, что мы вычислили на предыдущем шаге (с помощью МАКС)

2. Если подходящая должность найдена — возвращается её название. Если нет — выводится сообщение: “Отсутствовал в штате на эту дату”.

Увеличение реестра

Что, если нужно добавить в реестр нового сотрудника или запись о смене должности?

Так как мы преобразовали реестр в умную таблицу, то можно без проблем добавлять в него новые строки. Формула отработает корректно.

Увольнение сотрудника

Что, если сотрудник уволился?

Нужно просто добавить в реестр запись об этом:

Изменения отразятся в отчетной табличке:

Область применения

Где это может пригодиться?

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