Наверное самая популярная функция из продвинутых в Excel — это ВПР. Многие знают и часто пользуются. Но при этом у нее есть два значительных недостатка, например, как сделать «Левый ВПР «. Я использую сам ВПР, только если нужно сделать что-то быстро. В файлах для «регулярного» использования я делаю конструкцию ИНДЕКС и ПОИСКПОЗ. Чем она лучше?
- При добавлении столбца в таблицу данных не нужно менять номер столбца в самой формуле (как в ВПР). Столбец подвинется автоматом
- Можно сделать ВПР наоборот, т.е. сделать выбор из таблицы справа-налево. В ВПР первый столбец всегда должен быть для поиска.
Как все это сделать, читаем ниже :)
Вводная информация
Использовать буду пример из этой статьи. Для того, чтобы лучше понять применение этой конструкции в сравнение с ВПР:
=ИНДЕКС($G:$G;ПОИСКПОЗ($J:$J;$H:$H;0);1)
Как просто заполнить, если видишь формулу первый раз?
Сперва, определитесь, где таблица изначальная, а где для получения данных! Поэтапно, что заполнять.
- Вставляем формулу в нужную область таблицы
- На место $G:$G поставьте те ячейки, в которых должно быть найдено значение, и соответственно, должно появиться в результате. Мы ищем фамилию, значит ищите столбец с фамилиями в начальной таблице.
- Замените $J:$J на в зависимости, от которых должны вернуться значения в ячейку. Нам нужны Фамилии в зависимости от ТС — вставляем те ТС, около которых должны появиться значения.
- Вместо $H:$H заполняем столбец, в котором нужно найти соответствующее значение. Т.е. мы ищем Фамилию по ТС, значит вставляем столбец с ТС в изначальной таблице.
Если вам интересно, для чего в формуле знаки $ читайте здесь
ИНДЕКС и ПОИСКПОЗ. Что это за функции?
ИНДЕКС и ПОИСКПОЗ — очень сильные функции, которые в комбинации с другими дают отличный результат.
ИНДЕКС
=ИНДЕКС(массив; номер_строки; номер_столбца)
Возвращает значение на пересечении указанной строки и столбца определенного диапазона. Т.е. изначально она работает с двумерными массивами.
Выбрав массив данных J1:K4 и задав номер строки и столбца равным двум, мы получили соответствующее значение.
Посмотрев на изначальную формулу
=ИНДЕКС($G:$G;ПОИСКПОЗ($J:$J;$H:$H;0);1)
Мы увидим, что вместо второго аргумента (номер строки) у нас формула ПОИСКПОЗ. Что тут она делает?
ПОИСКПОЗ — это поиск по значению. Функция ищет заданное значение в строке или в столбце и возвращает ее порядковый номер (от начала диапазона). Т.е. во втором аргументе функции ИНДЕКС мы находим нужный нам номер ТС, получаем его номер, например 2.
И уже в одномерном массиве $G:$G находим ячейку с номером строки = 2. Так это будет работать для каждой ячейки в столбце J.
Вот так вроде бы не очень сложная конструкция, но, как я писал выше, очень эффективная. Т.к. вам не придется постоянно менять номер искомого значения, как в ВПР и можно делать поиск как направо, так и налево :)
Пишите комментарии, если вопросы остались.
Пример, как всегда!
Комментарии:
К этой записи пока нет комментариев. Возможно вы будете первым?