Функция ВПР (VLOOKUP)

Функция ВПР является одной из наиболее часто используемых и универсальных при работе с данными в Excel. Она не лишена недостатков, но ее обязательно следует освоить для понимания механизма работы с данными в Excel. Внимание. В августе 2019 Microsoft представила замену для ВПР — функцию ПРОСМОТРX, которая лучше во всем но требует поддержки динамических массивов

Описание функции ВПР

ВПР на английском — это VLOOKUP. ВПР осуществляет поиск указанного значения в определенном диапазоне (либо таблице из нескольких столбцов) и возвращает значение, которое находится в одной строке с искомым, из этого же диапазона. Наглядно, алгоритм действий получается следующий:

Функция ссылается на идентификатор «40», т.е. значение, которое необходимо найти в первом столбце диапазона A3:D9 и возвращает значение из 4-го столбца той же строки, в которой находится идентификатор 40, т.е. это значение зарплаты 87000

В обоих случаях, в качестве четвертого параметра стоял «0» — этот параметр называется «интервальный_просмотр» . Его можно записывать как «0» или «1», а также как «ЛОЖЬ» или «ИСТИНА». Более подробно об этом аргументе ниже в разделе «Синтаксис»

На первый взгляд может показаться, что выглядит все немного запутанно, но, на самом деле, использовать эту опцию довольно просто.

Буква «В» в названии функции ВПР означает «вертикально». Функция ВПР используется вместо функции ГПР, если сравниваемые значения расположены в столбце слева от искомых данных.

Вот другой пример, который наглядно демонстрирует принцип работы функции ВПР:

Принцип работы функции ВПР из категории «Ссылки и массивы»

Синтаксис

Аргументы

Обязательный. Значение, которое должно быть найдено в первом столбце таблицы или диапазона. Аргумент искомое_значение может быть значением или ссылкой. Если искомое_значение меньше, чем наименьшее значение в первом столбце аргумента таблица, функция ВПР возвращает значение ошибки #Н/Д.

Обязательный. Номер столбца в аргументе таблица, из которого возвращается совпадающее значение. Если номер_столбца равен 1, то возвращается значение из первого столбца аргумента таблица; если номер_столбца равен 2, — значение из второго столбца аргумента таблица и т. д.

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

Если аргумент интервальный_просмотр имеет значение ИСТИНА или опущен, то возвращается точное или приблизительное совпадение. Если точное совпадение не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение.

Внимание! Если аргумент интервальный_просмотр имеет значение ИСТИНА или опущен, значения в первом столбце аргумента таблица должны быть расположены в возрастающем порядке, иначе функция ВПР может вернуть неправильный результат.

Если аргумент интервальный_просмотр имеет значение ЛОЖЬ , функция ВПР ищет только точное совпадение. Если в первом столбце аргумента таблица имеется несколько значений, соответствующих аргументу искомое_значение, используется первое найденное значение. Если точное совпадение не найдено, возвращается значение ошибки #Н/Д.

Замечания

Нюансы использования ВПР

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

Поиск в первом столбце

Как было сказано в замечаниях, одним из минусов является то, что поиск осуществляется только в самом левом столбце.

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

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

Поиск до первого совпадения

Особенностью работы ВПР является поиск только до первого совпадения. Соответственно, если первый столбец диапазона не уникальный будет найдено только первое вхождение. Иногда именно это и нужно, иногда — нет, поэтому нужно иметь ввиду.

Данное ограничение можно обойти, решение не самое простое, но детально описано в курсе Excel.

Независимость от регистра

Для Excel и ВПР не важно в каком регистре (заглавные или прописные буквы) записано искомое значение и как оно записано в самом диапазоне.

Лишние пробелы

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

Различие в формате данных

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

Все можно исправить посредством приведения параметров в одинаковый формат. К примеру:

Существует несколько вариаций, как изменить формат текста на цифру:

Прекращение проявления ошибки #Н/Д

Если есть риск возникновения ошибки при вычислении формулы, можно воспользоваться функцией ЕСЛИОШИБКА (IFERROR), которая вернет определенное значение, в случае возникновения ошибки

Не зафиксирован массив

В примерах выше мы не использовали закрепление массива, т.к. всегда искали одно значение, т.е. не пользовались автозаполнением.

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

Массив можно оформить как умную таблицу. Команда «Форматировать как таблицу» из вкладки «Главная», либо горячее сочетание Ctrl+T. В этом случае ссылки на такой диапазон будут автоматически становиться абсолютными. Но при работе с такими таблицами в Excel есть свои нюансы и, не всегда это подойдет

Относительный поиск

Рассмотрим подробнее примеры с неточным совпадением, и здесь не лишним будет напомнить, что если точное совпадение не найдено, то возвращается наибольшее значение, которое меньше, чем искомое значение.

Вот так выглядит формула, если для ее построения использовать логическую функцию ЕСЛИ (да-да, с использованием ПЕРЕКЛЮЧ или ЕСЛИМН решение тоже будет лучше, но, допустим, у вас не самый свежий Excel, да и ВПР здесь выигрывает и у новых логических функций тоже):

посмотрите на рисунке на формуле с ЕСЛИ и сравните с таковой ВПР и неточным совпадением, последняя выглядит значительно лучше:

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

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

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

[expert_bq id=»1570″]Для вывода Наименования используйте формулу ВПР E9; A 13 C 19;2;ЛОЖЬ или ВПР E9; A 13 C 19;2;ИСТИНА или ВПР E9; A 13 C 19;2 т. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Рассматривая значения в таблице глубже, вы можете заметить, что в диапазоне данных значение “Иван “ написано с пробелом. Если в диапазоне данных по которым вы осуществляете поиск есть пробелы до значений, после или между словами, то функция их не сопоставит.

Пример использования функции ВПР в Эксель: пошаговая инструкция

В Примере 2 мы использовали одну таблицу с оценками для студентов по разным предметам. Это пример двухфакторного поиска, поскольку мы используем две переменные для получения оценки (фамилия студента и предмет).