ВПР vs. ИНДЕКС & ПОИСКПОЗ

ВПР – классическая, даже, пожалуй, легендарная функция. Эдакий первый этап для многих на пути к становлению активным пользователем функций в Excel. Тем не менее, у ВПР есть целый ряд недостатков: во-первых, ВПР не может возвращать значения, находящиеся слева от первого указанного столбца. Во-вторых, достаточно проблематичным может оказаться перемещение столбцов в таблице, на которую уже ссылаются определенные функции ВПР.


Именно поэтому, в этом видео я хочу тебе рассказать про использование комбинации функций ИНДЕКС и ПОИСКПОЗ в качестве универсальной и более мощной замены функции ВПР. Рассматривать будем пример из предыдущего поста с прошлой недели.


Итак, при выборе значения в динамическом выпадающем списке ячейки С2, в ячейках С3:С5 должны подтягиваться значения из умной таблицы ниже (как добавляются и работают динамические выпадающие списки в Excel мы рассмотрели вот в этом посте):

ВПР vs. ИНДЕКС & ПОИСКПОЗ Microsoft Excel, Vba, Видео, Длиннопост

Сперва пропишем формулу в ячейке С3, а в оставшихся двух затем просто вставим копию готовой формулы.


Итак, ставим знак равно и прописываем функцию ИНДЕКС:

ВПР vs. ИНДЕКС & ПОИСКПОЗ Microsoft Excel, Vba, Видео, Длиннопост

Данная функция работает следующим образом. В ней мы выбираем определенный диапазон (в нашем случае это будет диапазон значения умной таблицы – использовать будем структурированную ссылку), и посредством указания номера строки и номера столбца мы получаем определенную ячейку нашей таблицы.


Вот визуализированный пример, в котором таким образом мы выделяем ячейку со значение «Ford Mondeo» в области значений умной таблицы, выделяя четвертую строку и второй столбец:

ВПР vs. ИНДЕКС & ПОИСКПОЗ Microsoft Excel, Vba, Видео, Длиннопост

Так что указываем первый аргумент в нашей функции ИНДЕКС:

ВПР vs. ИНДЕКС & ПОИСКПОЗ Microsoft Excel, Vba, Видео, Длиннопост

Ну и следующие два аргумента функции индекс служат для указания номера строки и столбца. Естественно, указывать эти номера мы будем динамическим способом, а именно при помощи функции ПОИСКПОЗ.


Вот что делает эта функция: ей мы указываем определенный диапазон (второй аргумент), и говорим, какое значение нужно искать в этом диапазоне (первый аргумент):

ВПР vs. ИНДЕКС & ПОИСКПОЗ Microsoft Excel, Vba, Видео, Длиннопост

Найдя значение в указанном диапазоне, ПОИСКПОЗ возвращает его порядковую позицию – в случае строки эту будет индекс 7. И да, в последнем аргументе функции ПОИСКПОЗ выставляем 0 для поиска точного совпадения.


Ну и тоже самое делаем для третьего аргумента функции ИНДЕКС – с помощью функции ПОИСКПОЗ динамическим образом указываем нужный нам столбец:

ВПР vs. ИНДЕКС & ПОИСКПОЗ Microsoft Excel, Vba, Видео, Длиннопост

Супер! Теперь просто протягиваем формулу вниз и всё готово:

ВПР vs. ИНДЕКС & ПОИСКПОЗ Microsoft Excel, Vba, Видео, Длиннопост

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

ВПР vs. ИНДЕКС & ПОИСКПОЗ Microsoft Excel, Vba, Видео, Длиннопост

Вот такая интересная, и очень практичная формула!


Ну и напоследок приглашаю тебя на свой YouTube-канал, посмотреть вот это видео:

В нём я еще более углубленно и с упоминанием других важных пунктов рассказал о ИНДЕКС и ПОИСКПОЗ, а также привел пример практического применения комбинации этих функций для создания автоматических формуляров.


Приятного просмотра! 😊

MS, Libreoffice & Google docs

722 поста15K подписчиков

Добавить пост

Правила сообщества

1. Не нарушать правила Пикабу

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях


Важно - сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде "пост - отстой", это оскорбление автора и будет наказываться баном.