ВПР vs. ИНДЕКС & ПОИСКПОЗ
ВПР – классическая, даже, пожалуй, легендарная функция. Эдакий первый этап для многих на пути к становлению активным пользователем функций в Excel. Тем не менее, у ВПР есть целый ряд недостатков: во-первых, ВПР не может возвращать значения, находящиеся слева от первого указанного столбца. Во-вторых, достаточно проблематичным может оказаться перемещение столбцов в таблице, на которую уже ссылаются определенные функции ВПР.
Именно поэтому, в этом видео я хочу тебе рассказать про использование комбинации функций ИНДЕКС и ПОИСКПОЗ в качестве универсальной и более мощной замены функции ВПР. Рассматривать будем пример из предыдущего поста с прошлой недели.
Итак, при выборе значения в динамическом выпадающем списке ячейки С2, в ячейках С3:С5 должны подтягиваться значения из умной таблицы ниже (как добавляются и работают динамические выпадающие списки в Excel мы рассмотрели вот в этом посте):
Сперва пропишем формулу в ячейке С3, а в оставшихся двух затем просто вставим копию готовой формулы.
Итак, ставим знак равно и прописываем функцию ИНДЕКС:
Данная функция работает следующим образом. В ней мы выбираем определенный диапазон (в нашем случае это будет диапазон значения умной таблицы – использовать будем структурированную ссылку), и посредством указания номера строки и номера столбца мы получаем определенную ячейку нашей таблицы.
Вот визуализированный пример, в котором таким образом мы выделяем ячейку со значение «Ford Mondeo» в области значений умной таблицы, выделяя четвертую строку и второй столбец:
Так что указываем первый аргумент в нашей функции ИНДЕКС:
Ну и следующие два аргумента функции индекс служат для указания номера строки и столбца. Естественно, указывать эти номера мы будем динамическим способом, а именно при помощи функции ПОИСКПОЗ.
Вот что делает эта функция: ей мы указываем определенный диапазон (второй аргумент), и говорим, какое значение нужно искать в этом диапазоне (первый аргумент):
Найдя значение в указанном диапазоне, ПОИСКПОЗ возвращает его порядковую позицию – в случае строки эту будет индекс 7. И да, в последнем аргументе функции ПОИСКПОЗ выставляем 0 для поиска точного совпадения.
Ну и тоже самое делаем для третьего аргумента функции ИНДЕКС – с помощью функции ПОИСКПОЗ динамическим образом указываем нужный нам столбец:
Супер! Теперь просто протягиваем формулу вниз и всё готово:
Ну и теперь о плюсах этой формулы в сравнении с ВПР: во-первых, столбец искомых значений в отличии от ВПР может находиться на любой позиции, и поиск может происходить как, скажем так, влево, так и вправо от диапазона с искомыми значениями. Во-вторых, даже если на нашу таблицу ссылаются уже прописанные комбинации ИНДЕКС и ПОИСКПОЗ, мы всегда абсолютно спокойно можем изменять позиции отдельных столбцов. Вот пример с теми же формулами без единого изменения:
Вот такая интересная, и очень практичная формула!
Ну и напоследок приглашаю тебя на свой YouTube-канал, посмотреть вот это видео:
В нём я еще более углубленно и с упоминанием других важных пунктов рассказал о ИНДЕКС и ПОИСКПОЗ, а также привел пример практического применения комбинации этих функций для создания автоматических формуляров.
Приятного просмотра! 😊
MS, Libreoffice & Google docs
730 постов14.9K подписчиков
Правила сообщества
1. Не нарушать правила Пикабу
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Важно - сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.
Утверждения вроде "пост - отстой", это оскорбление автора и будет наказываться баном.