Как то раз мне понадобилось заполнить должности сотрудников на определенные даты по их ФИО, учитывая все их карьерные перемещения. В статье рассмотрим способы, как это сделать с помощью формул в Excel.
Постановка задачи
Допустим, есть реестр должностей:
Как видно по таблице, двое сотрудников в течение года меняли должности. В столбце С указана дата вступления в должность. На другом листе Excel вторая табличка - с датами отчетов и фамилиями, а должности предстоит заполнить:
То есть, нужно найти должность, которую занимал сотрудник на заданную дату. Также нужно учесть, что сотрудника может не быть в штате на момент составления отчета. Это можно сделать сочетанием функций МАКС и ФИЛЬТР.
Сделаем реестр "умным"
Для начала превратим реестр в "умный" реестр при помощи умных таблиц. Это упростит работу с формулами, но в целом не обязательно.
Далее дадим реестру название. Вместо названия по умолчанию в поле "Имя таблицы" укажем "Реестр". Теперь в формулах вместо ссылок на диапазон можно использовать понятные названия столбцов (например, Реестр[ФИО] или Реестр[должность])
На этом подготовительный этап окончен, можно приступать к вычислениям должностей.
Вычисляем должности
Перейдем на лист с табличкой для заполнения и в ячейку С2 забьем формулу:
=ФИЛЬТР(Реестр[должность]; (Реестр[ФИО] = B2) * (Реестр[действует с] = МАКС(ФИЛЬТР(Реестр[действует с];(Реестр[ФИО] = B2) * (Реестр[действует с] <= A2); 0))); "Отсутствовал в штате на эту дату")
Затем растянем ее на весь столбец. Результат:
Должности заполнены😎😎😎
Разбор формулы
Разберем формулу по частям. Чтобы было нагляднее, для примера возьмем формулу из ячейки С10:
Сама формула:
=ФИЛЬТР(Реестр[должность]; (Реестр[ФИО] = B10) * (Реестр[действует с] = МАКС(ФИЛЬТР(Реестр[действует с];(Реестр[ФИО] = B10) * (Реестр[действует с] <= A10); 0))); "Отсутствовал в штате на эту дату")
Разберем, что делает функция МАКС
МАКС(ФИЛЬТР(Реестр[действует с];(Реестр[ФИО] = B10) * (Реестр[действует с] <= A10); 0))
Что тут происходит:
Сначала фильтруются все записи в реестре по двум условиям:
ФИО должно совпадать с указанным в ячейке B10 (Иванов А. В.)
Дата вступления в должность должна быть не позже даты отчета (ячейка A10)
Знак * между скобками означает, что нужно, чтобы выполнились оба условия. В нашем случае останется 3 подходящих записи:
Затем из всех подходящих дат выбирается самая поздняя (используя МАКС), то есть наиболее близкая к дате отчета:
Если бы подходящих записей не нашлось, формула вернула бы 0.
По сути, формула определяет, когда сотрудник занял последнюю (на дату отчета) должность.
Важный нюанс. В качестве диапазона для фильтрации выбран не весь реестр, а только столбец с датами.
МАКС(ФИЛЬТР(Реестр[действует с];(Реестр[ФИО] = B10) * (Реестр[действует с] <= A10); 0))
Если вместо этого выбрать весь реестр, функция ФИЛЬТР вернет не список дат, а диапазон, вместе с ФИО и должностями. Тогда могут возникнуть проблемы с поиском максимальной даты.
Примечание. В нашем примере проблем то не возникнет - МАКС проигнорирует текстовые значения и найдет-таки максимальную дату. Но, если бы в реестре был еще один числовой столбец, или столбец с датами, то МАКС нашел бы максимум среди ВСЕХ дат и чисел.
Итак, что делает МАКС разобрали, переходим к формуле целиком.
=ФИЛЬТР(Реестр[должность]; (Реестр[ФИО] = B10) * (Реестр[действует с] = МАКС(...))); "Отсутствовал в штате на эту дату")
Что происходит:
Фильтруется список должностей из реестра по двум критериям:
ФИО должно совпадать с указанным в ячейке B10
Дата начала действия должности должна совпадать с той, что мы вычислили на предыдущем шаге (с помощью МАКС)
Если подходящая должность найдена - возвращается её название. Если нет - выводится сообщение: “Отсутствовал в штате на эту дату”.
Увеличение реестра
Что, если нужно добавить в реестр нового сотрудника или запись о смене должности?
Так как мы преобразовали реестр в умную таблицу, то можно без проблем добавлять в него новые строки. Формула отработает корректно.
Увольнение сотрудника
Что, если сотрудник уволился?
Нужно просто добавить в реестр запись об этом:
Изменения отразятся в отчетной табличке:
Область применения
Где это может пригодиться?
По полученной табличке можно создать шаблонные отчеты в ворде: если вам нужно сделать много однотипных отчетов в ворде, в которых меняются только даты, ФИО и должности, то вы можете автоматизировать этот процесс. Про то, как это сделать, можно посмотреть в статьях: