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

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

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

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

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

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

Нахождение должности при помощи функций ИНДЕКС и ФИЛЬТР

Первое что сделаем — применим функцию ФИЛЬТР. Она фильтрует диапазон и возвращает только строки, подходящие под условия фильтрации.

Для начала вычислим должность первого сотрудника. Сперва отфильтруем должности по его ФИО. Для этого забьем в ячейку С2 формулу:

=ФИЛЬТР(Реестр!A2:D7;(Реестр!A2:A7 = B2))

Примечание. “Реестр!” — ссылка на лист с реестром должностей из первого скрина, так как таблички на разных листах Excel.

Результат:

Дальше отфильтруем по дате. Нужно чтоб осталась только одна должность — та, которая действует на дату отчета. Иными словами, дата отчета должна быть больше или равна дате вступления в должность и меньше или равна дате завершения полномочий. Добавим эти условия в формулу:

=ФИЛЬТР(Реестр!A2:D7;(Реестр!A2:A7 = B2) * (Реестр!C2:C7 <= A2) * (Реестр!D2:D7 >= A2))

Знак * между круглыми скобками нужен для того, чтобы выполнились все три условия одновременно.

Результат:

После фильтрации осталась всего одна строка с нужной должностью. Осталось убрать лишние столбцы с ФИО и датами. Для этого воспользуемся функцией ИНДЕКС. Она принимает на вход диапазон и возвращает искомую ячейку из него. В нашем случае нужна ячейка из первой (и единственной) строки и 2 столбца отфильтрованного диапазона:

=ИНДЕКС(ФИЛЬТР(Реестр!A2:D7;(Реестр!A2:A7 = B2) * (Реестр!C2:C7 <= A2) * (Реестр!D2:D7 >= A2));1;2)

Результат:

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

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

Видим, что из-за относительной адресации “сползает” диапазон реестра

Чтобы он не сползал, вставим значки $:

=ИНДЕКС(ФИЛЬТР(Реестр!$A$2:$D$7;(Реестр!$A$2:$A$7 = B2) * (Реестр!$C$2:$C$7 <= A2) * (Реестр!$D$2:$D$7 >= A2));1;2)

Результат:

Масштабирование. Увеличение реестра должностей

Что, если в реестр нужно добавить нового сотрудника или новое изменение в должности, или мы знаем, что это может понадобиться в будущем? Можно заранее задать диапазон для фильтрации большей длины, “с запасом”:

=ИНДЕКС(ФИЛЬТР(Реестр!$A$2:$D$100;(Реестр!$A$2:$A$100 = B2) * (Реестр!$C$2:$C$100 <= A2) * (Реестр!$D$2:$D$100 >= A2));1;2)

Вместо диапазона $A$2:$D$7 выставляем, например, $A$2:$D$100 — теперь можно будет добавить до 100 строк в реестр, заполнение сработает корректно. А можно вовсе убрать длину диапазона:

=ИНДЕКС(ФИЛЬТР(Реестр!$A:$D;(Реестр!$A:$A = B2) * (Реестр!$C:$C <= A2) * (Реестр!$D:$D >= A2));1;2) — тогда можно расширять реестр до бесконечности.

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

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

Автоматизируем создание отчетов в Word с данными из Excel на Python

Два пути к автоматизации: как создавать отчеты в Word массово