На страницах этого канала уже был пост про работу с функцией ВПР() в Excel. Сегодня хочу показать альтернативный способ выборки значений из таблицы.
У ВПР () есть один существенный недостаток - поиск значений в таблице осуществляется только по левому столбцу, а вернуть результат можно по столбцам, находящимся правее столбца поиска. В реальных рабочих кейсах далеко не всегда можно переформатировать таблицу для использования ВПР () - часто, столбец для поиска находится правее столбца с данными, которые нужно затянуть.
В такой ситуации на помощь может придти решение с использованием функций ИНДЕКС() и ПОИСКПОЗ(). Объясню на простом примере:
Задача: по табельному номеру затянуть ФИО сотрудника и дату его приема.
Для решения подобных задач в Excel можно воспользоваться функцией ИНДЕКС() которая возвращает значение или ссылку на значение из таблицы или диапазона. Синтаксис функции:
=ИНДЕКС(массив; номер_строки; [номер_столбца])
массив - исходная таблица или диапазон в из которого необходимо вернуть данные или ссылку на данные
номер_строки - номер строки из массива, которую необходимо вернуть
номер_столбца - параметр нужен в случае, если мы указываем в части "массив" - таблицу для указания номера столбца
В нашем кейсе в качестве массива нужно указать диапазон ячеек с ФИО и диапазон ячеек с датами приема. Чтобы найти номер_строки необходимо воспользоваться другой встроенной в Excel функцией - ПОИСКПОЗ(). Данная функция возвращает номер вхождения искомого значения в просматриваемый массив и имеет следующий синтаксис:
=ПОИСКПОЗ(искомое_значение; просматриваемый_массив; [тип_совпадения])
искомое_значение - значение, которое необходимо найти в заданном параметре "просматриваемый_массив"
тип_совпадения - указывает, каким образом искомое_значение сопоставляется со значениями в аргументе просматриваемый_массив. Может принимать следующие значения (более подробная информация во встроенной в Excel справке):
1 или без указания (по-умолчанию): находит наибольшее значение, которое меньше или равно значению аргумента искомое_значение.
0: находит первое значение, равное аргументу искомое_значение.
-1: находит наименьшее значение, которое больше или равно значению аргумента искомое_значение.
Таким образом, при помощи функции ПОИСКПОЗ() необходимо найти номер_строки в массиве значений "Табельный №", аналогичном по размерности массиву с ФИО или Датами приема и подставить в функцию ИНДЕКС().
В нашем примере готовое выражение для поля "ФИО" будет иметь вид:
=ИНДЕКС($A$2:$A$5;ПОИСКПОЗ(A11;$B$2:$B$5;0))
Для поля "Дата приема":
=ИНДЕКС($C$2:$C$5;ПОИСКПОЗ(A11;$B$2:$B$5;0))
Обратите внимание, что в функции ПОИСКПОЗ() в качестве аргумента для тип_совпадения стоит 0 - иными словами, ищем точное совпадение.
Везде, в качестве ссылок на диапазоны или ячейки можно использовать имена диапазонов.
Функция ИНДЕКС имеет мощный функционал и может быть применена в разных ситуациях. В текущем посте показан лишь один из вариантов ее использования.