В этой статье разбираем функцию ВПР в Excel от и до: что она делает, как не запутаться в четырех аргументах и что делать, если Excel выдает #Н/Д. Никакой воды — только суть, примеры и разбор «граблей», на которые наступают 90% новичков.
Главное о функции ВПР
Функция ВПР ищет значение в первом столбце таблицы и возвращает результат из соседнего столбца;
ВПР всегда ищет слева направо. Если данные, которые нужно вернуть, находятся левее искомого, функция не сработает;
Синтаксис состоит из четырех аргументов: искомое значение, диапазон, номер столбца, интервальный просмотр;
Последний аргумент функции отвечает за тип поиска: 0 — точное совпадение, 1 — приблизительное;
Ошибка #Н/Д появляется, если значение не найдено. Причина обычно в разных типах данных или лишних пробелах;
Если вставить ВПР в ЕСЛИОШИБКА, вместо #Н/Д можно показывать ноль или пустую ячейку;
Если нужно искать справа налево, используют связку ИНДЕКС+ПОИСКПОЗ или ПРОСМОТРX (XLOOKUP).
Что такое функция ВПР в Excel и для чего она нужна
Аббревиатура ВПР расшифровывается как Вертикальный ПРосмотр. В английской версии Excel она называется VLOOKUP.
Чтобы лучше понять, как она устроена, представьте телефонный справочник. Вы ищете фамилию «Иванов» (это искомое значение), ведете пальцем вниз по столбцу с фамилиями (это вертикальный просмотр), находите нужную строку и смотрите в соседний столбец, чтобы узнать номер телефона (это результат).
Функция ВПР выполняет ту же работу, но за миллисекунды и без ошибок:
Она берет значение, которое вы указали (например, артикул товара).
Ищет его в первом (крайнем левом) столбце указанного вами диапазона.
Как только находит точное совпадение, она отсчитывает заданное количество столбцов вправо и возвращает значение из этой ячейки.
Как работает функция ВПР: синтаксис и аргументы
Чтобы функция сработала, нужно правильно заполнить четыре аргумента. Синтаксис выглядит так: =ВПР (искомое_значение; таблица; номер_столбца; [интервальный_просмотр])
В зависимости от версии Excel аргументы могут называться иначе: =ВПР (запрос, диапазон, номер_столбца, [отсортировано]). Смысл от этого не меняется.
Теперь давайте разберем каждый аргумент отдельно.
Аргумент 1: Искомое значение (запрос)
Запрос — это то, что вы ищете. Это может быть число, текст или ссылка на ячейку. Важно: тип данных должен совпадать. К примеру, если в ячейке стоит число 101, а вы ищете текст «101», функция не найдет совпадение и выдаст ошибку.
Аргумент 2: Таблица (Диапазон)
Это диапазон ячеек, в котором происходит поиск. Важно, чтобы первый столбец этого диапазона содержал те самые «искомые значения». Например, если вы ищете товар по артикулу, то артикул должен быть в первом столбце диапазона. Если выделить диапазон со столбца с названиями, ВПР будет искать название, а не артикул.
Аргумент 3: Номер столбца
Это порядковый номер столбца внутри выделенного диапазона, откуда нужно взять данные. Счет начинается с 1 — это первый столбец диапазона.
Аргумент 4: Интервальный просмотр ([отсортировано])
Самый коварный аргумент, из-за которого ВПР часто врет.
0 или ЛОЖЬ (False) — точное совпадение. Excel ищет только идентичное значение. Это правильный выбор в 99% случаев.
1 или ИСТИНА (True) — приблизительное совпадение. Excel найдет ближайшее меньшее значение. Используется для налоговых ставок или скидок по диапазону. Требует сортировки первого столбца по возрастанию, иначе результат будет непредсказуемым.
Совет: если используете 1 (приблизительное совпадение), первый столбец таблицы обязательно должен быть отсортирован по возрастанию. Иначе Excel вернет непредсказуемый результат.
Как пользоваться функцией ВПР: пошаговая инструкция с примерами
Представим типичную рабочую задачу: у нас есть Лист 1 с заказами (там есть артикул, но нет цены) и Лист 2 с прайс-листом (там есть артикул и цена). Нам нужно подтянуть цену в заказы.
Шаг 1. Подготовка и вызов функции
Если вы планируете добавлять новые строки в таблицу с данными, отформатируйте ее как «умную таблицу» (Ctrl+T). Тогда при добавлении строк данные будут автоматически попадать в формулы, которые ссылаются на эту таблицу.
Установите курсор в пустую ячейку рядом с артикулом в первой таблице (например, C2). Нажмите на значок fx или напишите =ВПР(.
Шаг 2. Задаем искомое значение
Кликните на ячейку с артикулом (A2). В формуле появляется A2;. Это значение, которое мы ищем.
Шаг 3. Выбираем таблицу
Переходим на второй лист и выделяем мышкой диапазон прайс-листа, начиная со столбца с артикулами.
Шаг 4. Указываем номер столбца
Считаем: в выделенном диапазоне A2: B5 столбец A — это первый (там артикулы), столбец B — второй (там цены). Нам нужна цена, значит пишем 2.
Шаг 5. Ставим интервальный просмотр
Пишем 0 (или ЛОЖЬ), чтобы найти точное совпадение.
Шаг 6. Готово
Нажимаем Enter. Формула выглядит так: =ВПР (A2;Лист2!$A$2:$B$5;2;0). Цена подтянулась. Теперь можно «протянуть» формулу вниз за правый нижний угол ячейки.
Если ВПР не находит значение и выдает #Н/Д, используйте формулу с ЕСЛИОШИБКА, чтобы вместо ошибки показывался ноль или пустая ячейка:
=ЕСЛИОШИБКА (ВПР (A2;Лист2!$A$2:$B$5;2;0); 0)
Ошибки при использовании функции ВПР: какие выдает и как исправить
Даже если вам кажется, что все сделали правильно, Excel может выдать ошибку. Разберем самые частые из них в таблице.
| Ошибка |
|
| |||
|---|---|---|---|---|---|
|
| Причина №1: Нет точного совпадения (опечатка). Причина №2: Разный тип данных (указано число, а не текст). Причина №3: Искомое значение не в первом столбце диапазона. | |||
|
|
| |||
|
|
| |||
|
|
|
Главный враг: «#Н/Д»
Если Excel пишет #Н/Д, а вы видите это значение глазами — почти наверняка проблема в форматировании.
Текст, а не число: В одной таблице артикул может быть текстом ('123), а в другой числом (123). Используйте функцию =ЕТЕКСТ () и =ЕЧИСЛО () для проверки.
Лишние пробелы: Невидимые пробелы ломают поиск. Используйте функцию =СЖПРОБЕЛЫ ().
Скрытые символы: Иногда данные копируются с неразрывными пробелами.
Чтобы заменить #Н/Д на пустую ячейку или ноль, введите в любой ячейке на листе 1 формулу ЕСЛИОШИБКА:
=ЕСЛИОШИБКА (ВПР (A2;Лист2!$A$2:$B$5;2;0); 0)
Вопросы и ответы
Ниже — ответы на вопросы, которые чаще всего задают про функцию ВПР.
Чем ВПР отличается от ГПР?
ВПР ищет по вертикали (сверху вниз) и возвращает данные из столбца справа. ГПР (Горизонтальный ПРосмотр) ищет по горизонтали (слева направо) и возвращает данные из строки снизу. ГПР используется, если данные в таблице растут «вбок», а не «вниз».
Почему ВПР не находит значение, хотя оно точно есть?
С вероятностью 99% это проблема типа данных. Если вы ищете число, а в таблице оно записано как текст (или наоборот), ВПР не увидит совпадения. Также проверьте, нет ли лишних пробелов или скрытых символов в ячейках.
Что лучше: ВПР или ИНДЕКС+ПОИСКПОЗ?
ВПР проще для новичков и быстрее работает на небольших таблицах. Но у ВПР есть жесткое ограничение: он не умеет искать справа налево. Если вам нужно вернуть значение из столбца, который находится слева от искомого, используйте связку ИНДЕКС+ПОИСКПОЗ или новую функцию ПРОСМОТРX (XLOOKUP) в Excel 2021 и Excel 365.
ВПР работает в Google Таблицах?
Да, функция ВПР работает и в Google Таблицах — с теми же аргументами и синтаксисом.