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

- Функция ссылается на идентификатор «40», т.е. значение, которое необходимо найти в первом столбце диапазона A3:D9 и возвращает значение из 4-го столбца той же строки, в которой находится идентификатор 40, т.е. это значение зарплаты 87000 [code lang=»excel»]=ВПР(G2;A3:D9;4;0)[/code]
- Во втором случае функция ссылается на идентификатор «12», т.е. значение, которое необходимо найти в первом столбце диапазона A3:D9 и возвращает значение из 3-го столбца той же строки, в которой находится идентификатор 12, т.е. это значение Фамилии — Линкольн [code lang=»excel»]=ВПР(G6;A3:D9;3;0)[/code]
В обоих случаях, в качестве четвертого параметра стоял «0» — этот параметр называется «интервальный_просмотр». Его можно записывать как «0» или «1», а также как «ЛОЖЬ» или «ИСТИНА». Более подробно об этом аргументе ниже в разделе «Синтаксис»
На первый взгляд может показаться, что выглядит все немного запутанно, но, на самом деле, использовать эту опцию довольно просто.
Буква «В» в названии функции ВПР означает «вертикально». Функция ВПР используется вместо функции ГПР, если сравниваемые значения расположены в столбце слева от искомых данных.
Вот другой пример, который наглядно демонстрирует принцип работы функции ВПР:

Синтаксис
[code lang=»excel»]=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])[/code]Аргументы
Если значение аргумента номер_столбца:
- меньше 1, функция ВПР возвращает значение ошибки #ЗНАЧ!;
- больше, чем число столбцов в аргументе таблица, функция ВПР возвращает значение ошибки #ССЫЛ!.
Если аргумент интервальный_просмотр имеет значение ИСТИНА или опущен, то возвращается точное или приблизительное совпадение. Если точное совпадение не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение.
Внимание! Если аргумент интервальный_просмотр имеет значение ИСТИНА или опущен, значения в первом столбце аргумента таблица должны быть расположены в возрастающем порядке, иначе функция ВПР может вернуть неправильный результат.
Если аргумент интервальный_просмотр имеет значение ЛОЖЬ, функция ВПР ищет только точное совпадение. Если в первом столбце аргумента таблица имеется несколько значений, соответствующих аргументу искомое_значение, используется первое найденное значение. Если точное совпадение не найдено, возвращается значение ошибки #Н/Д.
Замечания
- При поиске в большом массиве, особенно, если на листе Excel много формул, у пользователя могут возникать проблемы с производительностью. Первое, что приходит на ум — это произвести апгрейд компьютера. Да, функция ВПР считается достаточно медленной, однако, мало кто знает, что ее скорость работы можно увеличить десятикратно, если использовать поставить аргумент равным ИСТИНА (или 1). Да, здесь есть нюанс в виде обязательной сортировки, поэтому такое ускорение будет работать не всегда. Кстати скорость увеличивается из-за сортировки, в этом случае нет необходимости сравнивать каждое значение из столбца.
- Функция ВПР осуществляет поиск всегда в самом первом (левом) столбце диапазона. Данное ограничение можно обойти, там где возможно, конечно, уменьшив аргумент таблица, чтобы искомый столбец стал самым первым
Поиск ВПР всегда в самом левом столбце - При поиске текстовых значений в первом столбце аргумента таблица убедитесь, что данные в этом столбце таблицы не содержат начальных пробелов, конечных пробелов, используемых не по правилам прямых ( ‘ или » ) и «парных» ( ‘ или “ ) кавычек или непечатаемых символов. В этих случаях функция ВПР может вернуть неправильное или непредвиденное значение
Возвращение ошибки функцией ВПР - При поиске числовых значений или значений дат убедитесь, что данные в первом столбце аргумента таблица не являются текстовыми значениями. В этом случае функция ВПР может вернуть неправильное или непредвиденное значение
- Если значение аргумента «интервальный_просмотр» — ЛОЖЬ, а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, поставьте перед ними знак тильды (~)
Использование заполнителей при поиске текстовых значений
Нюансы использования ВПР
Когда пользователь работает с функцией ВПР, не исключено появление различных ситуаций, когда что-то пошло не так или нужно обойти некоторые ограничения. Здесь и будут рассмотрены такие нюансы использования и решение различного рода потенциальных проблем.
Поиск в первом столбце
Как было сказано в замечаниях, одним из минусов является то, что поиск осуществляется только в самом левом столбце.
Не исключено, что столбец, в котором содержатся требуемые значения, располагается слева от столбца, где будет вестись поиск.
Если необходимо осуществлять поиск слева от искомого столбца, то нужно использовать связку ИНДЕКС + ПОИСКПОЗ. Понимание работы данной связки несколько сложнее ВПР, но, если вы освоите данную функцию, то использовать ИНДЕКС + ПОИСКПОЗ не составит большого труда.
Поиск до первого совпадения
Особенностью работы ВПР является поиск только до первого совпадения. Соответственно, если первый столбец диапазона не уникальный будет найдено только первое вхождение. Иногда именно это и нужно, иногда — нет, поэтому нужно иметь ввиду.

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

Лишние пробелы
В замечаниях мы показали, как незаметный пробел может вызывать ошибку работы данной функции. Если существует риск загромождения ячеек чрезмерным количеством пробелов, следует очистить ячейки с помощью функции СЖПРОБЕЛЫ (TRIM).
Различие в формате данных
Если один параметр ВПР дает отсылку к ячейке содержащей цифру, которая прописана текстом, а начальный столбец массива содержит цифры в верном, числовом формате, или же наоборот, поиск не даст результатов.
Все можно исправить посредством приведения параметров в одинаковый формат. К примеру:
[code lang=»excel»]=ВПР(−−G2; A3:D9; 4; 0)[/code] — если в D5 буквы, а в таблице — цифры; [code lang=»excel»]=ВПР(G2 & «»); A3:D9; 4; 0)[/code] — и иначе.
Существует несколько вариаций, как изменить формат текста на цифру:
- Возвести в степень: G2^1;
- Двойное отрицание: —G2;
- Прибавить ноль: G2+0;
- Умножить на один: G2*1.
Прекращение проявления ошибки #Н/Д
Если есть риск возникновения ошибки при вычислении формулы, можно воспользоваться функцией ЕСЛИОШИБКА (IFERROR), которая вернет определенное значение, в случае возникновения ошибки
Не зафиксирован массив
В примерах выше мы не использовали закрепление массива, т.к. всегда искали одно значение, т.е. не пользовались автозаполнением.
Однако, в подавляющем большинстве случаев, функцию ВПР вы будете использовать именно с автозаполнением. В этом случае лучше всего использовать абсолютную ссылку для закрепления диапазона поиска.
Массив можно оформить как умную таблицу. Команда «Форматировать как таблицу» из вкладки «Главная», либо горячее сочетание Ctrl+T. В этом случае ссылки на такой диапазон будут автоматически становиться абсолютными. Но при работе с такими таблицами в Excel есть свои нюансы и, не всегда это подойдет

Относительный поиск
При использовании ВПР пользователи, как правило всегда ставят аргумент интервальный_просмотр равным «0» (ЛОЖЬ), что означает, что нужно выполнить точный поиск и значения в первом столбце не отсортированы. Однако, если поиск осуществляется числовых значений, в некоторых случаях, хорошим решением будет отсортировать значения и выполнить не точный поиск. А в некоторых случаях неточный поиск вообще будет единственным решением. Рассмотрим подробнее.
Рассмотрим подробнее примеры с неточным совпадением, и здесь не лишним будет напомнить, что если точное совпадение не найдено, то возвращается наибольшее значение, которое меньше, чем искомое значение.
- 0 — 60: F
- 60 — 70: D
- 70 — 80: C
- 80 — 90: B
- 90 — 100: A
Вот так выглядит формула, если для ее построения использовать логическую функцию ЕСЛИ (да-да, с использованием ПЕРЕКЛЮЧ или ЕСЛИМН решение тоже будет лучше, но, допустим, у вас не самый свежий Excel, да и ВПР здесь выигрывает и у новых логических функций тоже):

посмотрите на рисунке на формуле с ЕСЛИ и сравните с таковой ВПР и неточным совпадением, последняя выглядит значительно лучше:
[code lang=»excel»]=ВПР(E1;A2:B6;2;1)[/code]Поскольку, если ВПР не находит точное совпадение, то функция вернет максимальное значение, которое меньше искомого. Значит нам просто нужно ввести очень большое число, которое заведомо больше зарплаты любого сотрудника. Главное не забыть сортировку по зарплате сделать.

Пример
Видео работы функции
Дополнительные материалы
Как сравнить два файла MS Excel
#Excelwordlchamp 1-1 Решаем задачи первого тура