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

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

=ВПР(Искомое_значение; Таблица_где_ищем; Номер_столбца_результатов; Тип_поиска)

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

Итак, начнём повышать уровень знаний в период самоизоляции))

Допустим, у нас имеются две таблицы – Заказы и прайс-лист:

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

Примеры использования ВПР:

- подставить из штатного расписания данные о сотруднике (адрес, оклад, телефон) по его ФИО;

- подставить из каталога продукции подробную информацию о товаре по его артикулу;

- подставить из реестра договоров по номеру договора все подробности его заключения (с кем заключен, реквизиты, сумму и т.д.);

- и так далее.

Выделяем первую ячейку (D3), куда будем вводить функцию ВПР, на вкладке «Формулы» нажимаем «Вставить функцию». В категории «Ссылки и массивы» (Lookup and Reference) находим ВПР (VLOOKUP) и жмём ОК

Появится окно ввода аргументов для функции:

Заполняем поля по очереди:

- Искомое значение – наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа (слово «Вода» из ячейки B3).

- Таблица – таблица, из которой берутся искомые значения, т.е. наш прайс-лист. Чтобы при копировании функции вниз на весь столбец ссылка на прайс не сбилась, ее нужно сделать абсолютной, нажав клавишу F4.

- Номер_столбца – порядковый номер (не буква!) столбца в прайс-листе, из которого берём значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно, нам нужна цена из столбца с номером 2.

- Интервальный_просмотр – ЛОЖЬ (0) или ИСТИНА (1):

- ЛОЖЬ КЛАДИ - поиск точного соответствия. Если товар отсутствует в прайс-листе или написан с ошибкой, то функция выдаст ошибку #Н/Д.

- ИСТИНА - поиск приблизительного соответствия. Функция попытается найти товар с максимально похожим наименованием и выдаст цену для этого товара.

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

После ввода всех аргументов нажимаем ОК и протягиваем введенную функцию на весь столбец.

Функция ВПР выдаёт ошибку #Н/Д если:

1. Включен точный поиск (Интервальный просмотр=0) и искомого наименования нет в Таблице.

2. Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск, не отсортирована по возрастанию наименований.

3. Форматы ячеек первого столбца и образцовой таблицы отличаются (числовой и текстовый). Можно использовать функции Ч и ТЕКСТ для преобразования форматов данных.

4. Функция не может найти нужного значения, потому что в коде есть пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ и ПЕЧСИМВ для их удаления:

=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));$G$3:$H$19;0)

=VLOOKUP(TRIM(CLEAN(B3));$G$3:$H$19;0)

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

Итого получаем, 5 минут на заполнение формулы и куча сэкономленного времени и нервов, в которое имитируем бурную деятельность и читаем Пикабу)) Всем здоровья, берегите себя!

Дополнение к посту: #comment_166519497