Функция ВПР в MS Excel

Анатолий Опарин / март, 2019

Одной из самых популярных функционалов у продвинутых пользователей MS Excel являются формулы и функции. Согласно введенной в ячейку формуле данные в неё могут подтягиваться динамически в зависимости от содержания других ячеек. Одной из самых популярных функций является ВПР.
Название функции происходит от «Вертикальный ПРосмотр», что олицетворяет принцип её действия – она просматривает столбец сверху вниз по вертикали, сначала ищет нужное значение ячейки, а потом показывает пользователю значение из соответствующей ячейки той же строки в одном из соседних столбцов справа. Для запоминания названия функции я бы предложим другую этимологию – ВПРаво. Ведь именно справа формула ищет соответствие. В английской версии Excel эта функция называется VLOOKUP.

Функция имеет 4 параметра:

=ВПР(<КОНТЕКСТ>; <ТАБЛИЦА>; <СТОЛБЕЦ>; <ИНТЕРВАЛЬНЫЙ_ПРОСМОРТ>)

<КОНТЕКСТ> - адрес ячейки, в которой будет находится поисковый контекст;
<ТАБЛИЦА> - диапазон ячеек, в котором содержится таблица со столбцом поиска контекста и со столбцом поиска соответствия;
<СТОЛБЕЦ> - номер столбца из таблицы данных <ТАБЛИЦА>, откуда будет возвращено значение соответствующей ячейки;
<ИНТЕРВАЛЬНЫЙ_ПРОСМОРТ> - необязательный параметр, который указывает на то, как искать контекст в первом столбце – точно (значение ЛОЖЬ или 0) или приближенно по ближнему меньшему (значение ИСТИНА или 1, или отсутствие значения).

ПРАКТИЧЕСКИЙ ПРИМЕР

У нас есть таблица-источник, в которой хранится информации о количестве товара на складе:

Товар      Количество
----------------------
Хлеб        50
Пончики     100
Печенье	    40
Торты	    50
Пироги	    40

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

Дело решается тем, что во вторую ячейку надо вписать формулу:

=ВПР(A8; A2:B6; 2; ЛОЖЬ)

В итоге в первой ячейке появится возможность выбирать значение из столбца «Товар» по списку, при этом вторая ячейка будет принимать соответствующее значение из столбца «Количество» автоматически:

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

1. Выделить ячейку A8;
2. На панели меню «Данные» нажать кнопку (Проверка данных) – появится окно настройки;
3. В поле «Тип данных» выбрать «Список»;
4. Поместить курсор в поле «Источник» и обрисовать мышкой диапазон ячеек, откуда будет формироваться список – получится =$A$2:$A$6;
5. Нажать кнопку [OK] – ячейка A8 преобразуется в выбор из списка:

УЧЁТ ОШИБОК

В ячейке выбора товара A8 пользователь может задавать значение вручную, а не выбирать из списка. Даже в формуле на месте <КОНТЕКСТ> можно в кавычках написать искомое слово. В этом случае есть вероятность того, что будет запрошен товар, которого в столбце поиска нет. Тогда в ячейке с формулой появится ошибка #Н/Д. Такого «некрасивого» значения можно избежать, если формулу усовершенствовать.

=ЕСЛИОШИБКА(ВПР(A8;A2:B6;2;ЛОЖЬ); "Товар закончился")

Здесь в кавычках перед последней закрывающей скобкой можно написать желаемое извещение. Формула ЕСЛИОШИБКА принимает два аргументы, разделенных точкой с запятой. Если первый аргумент в виде формулы дает ошибку, то выводится второй аргумент. Если первый аргумент отрабатывает без ошибки, то выводится результат этой формулы. В качестве второго аргумента можно задать пустую строку "".

ИНТЕРВАЛЬНЫЙ ПРОСМОТР

Значение <ИНТЕРВАЛЬНЫЙ_ПРОСМОТР> в простых примерах указывается как ЛОЖЬ, т.е. мы заказываем точный поиск значения <КОНТЕКСТ>. А когда может пригодиться значение ИСТИНА?
Например, тогда, когда нам требуется узнать, попадает ли <КОНТЕКСТ> в какой-либо интервал значений. То есть, если есть критерии для параметра <ТАБЛИЦА> 100, 200, 300, а значение параметра <КОНТЕКСТ> равно 180, то при <ИНТЕРВАЛЬНЫЙ_ПРОСМОТР> = ИСТИНА формула ВПР даст результат 100, потому что ИЩЕТ БЛИЖНЕЕ МЕНЬШЕЕ, а для 180 ближнее меньшее – это 100.

Допустим, у нас есть таблица данных:

Мы хотим в столбце «Партия» дать словесную характеристику количеству согласно второй таблицы критериев:

В ячейках «Партия» первой таблицы данных будем размещать формулу ВПР. Но мы уже не может <ИНТЕРВАЛЬНЫЙ_ПРОСМОТР> устанавливать в ЛОЖЬ, потому что будем искать не точное соответствие количества, а вхождение кол-ва в интервал от 100 до 199, от 200 до 299...

1. В ячейке E2 написать формулу: =ВПР(D2;$G$2:$H$5;2) – здесь обратите внимание, что адрес ячейки для <КОНТЕКСТ> мы написали в формате относительного адреса D2, а диапазон ячеек для параметра <ТАБЛИЦА> написали в формате абсолютного адреса $G$2:$H$5 (причина – ниже), параметр <ИНТЕРВАЛЬНЫЙ_ПРОСМОТР> вообще не указали, значит, он включен.
2. После вставки формулы и нажатия клавиши [Enter] ячейка примет значение «Среднее», потому что значение 220 из таблицы данных входит в диапазон «Средняя» таблицы критериев, для 220 ближнее меньшее – это 200.
3. Выделить ячейку E2 и потянуть за маленький черный квадратик в нижнем правом углу ячейки вниз, чтобы распространить формулу по всему столбцу. Тут положительно скажется наше решение по различному написанию адресов параметров <КОНТЕКСТ> и <ТАБЛИЦА>. А именно, адрес <КОНТЕКСТ> будет инкрементально меняться, чтобы соответствовать номеру строки, а адрес <ТАБЛИЦА> будет оставаться статичным.