Используем функцию ВПР в Excel для поиска и отбора значений!

Доброго времени суток друзья!

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

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

У функции ВПР есть и тёмная сторона, она же младшая сестра, которой не суждены лавры старшей – это функция ГПР. Это антипод ВПР и используется для поиска по строкам, а это происходит крайне редко. В случае, когда она является вашим главным инструментом, где-то вы повернули не туда и стоить вернутся назад.

А теперь давайте перейдем уже к практическим описаниям и примерам, на основе которых вам станет понятно, о чём идет речь. Сразу повторюсь, что читать стоит внимательно и всё пойдет путём.

1. Поиск по диапазонам

Это самый классический способ поиска с помощью функции ВПР. Будем определять по стоимости заказа, какую скидку заслуживает потребитель.

2. Поиск по текстовым строкам

Ну и кроме циферок, функция ВПР в Excel умеет искать и среди текста, но, увы, регистр букв она не учитывает, с этим стоит смириться и взять во внимание при решении задач. Хотя, честно говоря, очень мало задач где нужно и важен регистр букв. Но, есть и хорошая новость, функция работает с символами подстановки и этим расширяет свои возможности очень значительно. Всего доступно два символа для подстановки:

• «?»— подменяет один любой символ в текстовом значении;
• «*»— заменяет любые символы в любых количествах.

3. Убираем лишние пробелы

Достаточно частенько поднимается вопрос о проблеме лишних пробелов в момент поиска нужного значения. Особенно это актуально когда ячейки с указанными параметрами от вас не зависят и работать надо уже с тем, что получили. В связи с этим завалом лишних пробелов можно использовать для очистки функцию СЖПРОБЕЛЫ.

4. Убираем выдачу ошибки #Н/Д в результате формуле

Как вам известно со своего опыта, результаты формул часто выдают ошибки по вычислениям, а это очень портит визуализацию вашей таблицы. Этого можно избежать, применив логическую функцию ЕСЛИОШИБКА. Как видите с примера, когда функция ВПР получит результат ошибку #Н/Д, она перехватывает итог и подставляет свое значение (пустая строка). А вот если ошибка не выявлена, тогда просто срабатывает формула и выводится полученный результат. Как видите, визуализация данных от этого очень выигрывает.

5. Оптимизируем массив поиска значений

В этом пункте рассмотрим наиболее оптимальное использование второго аргумента функции ВПР «таблица». Не редко пользователи забывают указать необходимый массив данных, как абсолютную ссылку $A$2:$B$7 и соответственно когда протягивают формулу, она начинает «плыть». Стоит запомнить, что этот аргумент просит о своем закреплении, не стоит этого забывать.

Очень хорошей идеей будет вынесение своей справочной таблицы на отдельный лист своей рабочей книги. Она не будет вам мешать «под рукой» и в целом будет более надежнее и сохраннее.

А ещё более интересный вариант, это предоставить необходимый массив в виде поименованного диапазона.

Некоторые пользователи пошли другим путем и указывают вместо конкретного диапазона, полные адреса столбиков, где размещается таблица, типа A:B. Это хорошо в том плане, что вам не нужно будет отслеживать, попадает или нет ваш массив в указанный диапазон. Минусом станет тот факт, что обработка этой конструкции займет немного больше времени, но это можно проигнорировать, так как технически задержка составит доли секунд.

В идеале, советую использовать рабочий массив в виде умной таблицы.

6. Работа в функции ВПР с разными форматами данных

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

• Возводим аргумент в степень (A2^1);
• Умножение на число (A2*1);
• Сложение аргумента с нулём (A2+0);
• Двойное минусовое (-A2).

Пример применения:

=ВПР(−−D7;$A$2:$B$7;3;0) — в случае, когда A7 имеет текст, а таблица — числа;

=ВПР((D7 & «»);$A$2:$B$7;3;0) — и обратный эффект.

Превратить числовое значение в текст можно произвести всего лишь сцепив его с пустой ячейкой, которая и помогает Excel преобразовывать типы данных.

7. Указываем столбик для извлечения с помощью функции СТОЛБЕЦ

В случаях, когда ваши таблицы расчётная и справочная похожи структурно, а разница только в количестве строк, то для автоматического определения и расчёта номера столбика, который подлежит извлечению, будем использовать функцию СТОЛБЕЦ.

Замечу, что все формулы ВПР будут одинаковыми, учитывается только первый аргумент, который формула будет подставлять автоматически. Для избежания ошибок в результатах, обязательно установите первый аргумент абсолютным.

8. Составляем ключ поиска с помощью «амперсанда» (&”|”&)

В случаях, когда нужно произвести поиск в нескольких столбиках сразу, необходимо использовать составной ключ для формирования поиска. В случаях, когда поиск проводится по числовым значениям значительно удобнее использовать функцию СУММЕСЛИМН и придумывать составной ключ нет необходимости, а вот текстовые значения придётся искать с помощью склеенного ключа для функции ВПР. А на этом у меня всё! Я очень надеюсь, что всё описанные тонкости использования функции ВПР в Excel вам понятны. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!