Когда нужно сопоставить данные из нескольких таблиц, ручной поиск и копирование могут занять слишком много времени. Допустим, вам нужно найти цены товаров по артикулам, подтянуть контакты клиентов из общей базы или добавить к списку заказов имена ответственных менеджеров. Если строк сотни, такая работа отнимает часы и повышает риск ошибок.
ВПР в Excel автоматизирует этот процесс: находит нужные значения в одной таблице и переносит связанные с ними данные в другую. В статье разберем, как работает функция ВПР в Эксель, как применить ее на конкретной таблице и почему она иногда выдает ошибки.
Что такое функция ВПР в Excel и как она работает
Формула ВПР в Excel находит нужное значение в первом столбце выбранной таблицы и показывает данные, которые находятся в этой же строке, но в другом столбце.
Например, вы можете найти сотрудника по табельному номеру — и автоматически получить его должность.
Название расшифровывается как «вертикальный просмотр»: Excel последовательно проверяет значения в столбце поиска и возвращает результат из выбранного столбца. ВПР — это та же функция VLOOKUP Excel, только в русском переводе.
Проще всего сравнить ее с телефонным справочником. Вы знаете фамилию (искомое значение), находите ее в списке и смотрите номер в соседней колонке. Функция ВПР в Экселе делает то же самое, только сразу для сотен и тысяч строк.
Какие задачи поможет решить этот инструмент на практике:
- подтянуть цены из прайса в таблицу заказов по артикулам;
- сопоставить список клиентов с базой, где указаны их менеджеры или города;
- найти должность или отдел сотрудника по табельному номеру;
- связать данные из двух отчетов, если строки в них идут в разном порядке.
Синтаксис: из чего состоит формула ВПР в Excel
Функция ВПР в Экселе включает четыре аргумента:
=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])
| Аргумент | Что означает | Пример |
|---|---|---|
искомое_значение | Что нужно найти: число, текст или ссылка на ячейку. | B2 |
таблица | Диапазон, где идет поиск. Искомое значение должно стоять в его первом столбце. | Прайс!$A$2:$D$9 |
номер_столбца | Номер столбца внутри диапазона, откуда взять результат. Счет начинается с 1 от первого столбца диапазона, а не от края листа. | 4 |
интервальный_просмотр | Тип совпадения: ЛОЖЬ (или 0) — точное, ИСТИНА (или 1) — приблизительное. | ЛОЖЬ |
Для нашего примера формула будет такой:
=ВПР(B2;Прайс!$A$2:$D$9;4;ЛОЖЬ)
Четвертый аргумент необязательный, и именно с ним связана половина ошибок новичков. Если его пропустить, Excel по умолчанию включит приблизительный поиск.
Для артикулов, фамилий, номеров заказов и других точных данных всегда указывайте ЛОЖЬ или 0.
Приблизительный поиск (ИСТИНА или 1) нужен для диапазонов, например, для скидок или налоговых ставок в зависимости от суммы. В этом режиме первый столбец таблицы обязательно сортируют по возрастанию, иначе результат будет непредсказуемым.
Ограничения функции Vlookup в Excel
У формулы есть особенности, которые стоит знать заранее:
- Ищет только в первом столбце диапазона. Данные для результата можно взять только из столбцов, которые находятся правее столбца поиска. Если нужное значение стоит левее искомого, ВПР в Эксель не подойдет: например, нельзя найти цену по артикулу, если цена расположена в столбце A, а артикул — в столбце B.
- Возвращает первое совпадение. Если в первом столбце есть дубли, ВПР возьмет данные из первой найденной строки и не покажет остальные совпадения.
- Не различает регистр. Для нее «ABC» и «abc» — одно и то же.
- Возвращает одно значение за раз. Чтобы получить два столбца, формулу вводят дважды с разными номерами столбца.
В новых версиях Excel (2021, 2024 и Microsoft 365) есть более гибкая функция ПРОСМОТРX: она ищет в любом направлении и по умолчанию дает точное совпадение.
Но ВПР по-прежнему востребована: она работает во всех версиях программы, включая Excel 2016 и 2019, а также в Google Таблицах.
Как применять функцию ВПР в Excel: пошаговая инструкция и пример
Чтобы разобраться в настройках функции, разберем, как применять формулу ВПР в Excel для чайников.
Допустим, вы продаете компьютерную периферию. На листе «Прайс» хранится справочник товаров, а на листе «Заказы» — список заказов, где указаны только артикулы и количество. Нужно автоматически подставить название и цену товара, а затем посчитать сумму.
Лист «Прайс» (диапазон A1:D9, заголовки в первой строке):
Лист «Заказы»:
Чтобы применить функцию ВПР в Экселе:
1. Проверьте данные. Артикулы в прайсе не должны повторяться и должны стоять в первом столбце диапазона. Важно, чтобы формат ячеек в огромном списке был одинаковым (текст с текстом, числа с числами). В таблице не должно быть лишних пробелов.
2. Выберите ячейку для результата. На листе «Заказы» это D2 — первая ячейка будущего столбца «Товар». Формулу можно ввести вручную, начав с =ВПР(, или собрать через мастера: кнопка fx, затем поиск функции ВПР. Новичку проще использовать второй вариант, при котором каждый аргумент вводится в отдельное поле, и формулу ВПР в Экселе почти невозможно собрать с синтаксической ошибкой.
3. Укажите искомое значение. Нажмите на ячейку B2 с артикулом первого заказа.
4. Задайте таблицу. Перейдите на лист «Прайс» и выделите диапазон A2:D9. Затем закрепите его клавишей F4 (на Mac — Cmd + T или Fn + F4, в зависимости от клавиатуры). В ссылке появятся знаки доллара: $A$2:$D$9. Без этого при копировании формулы вниз диапазон сдвинется, и часть артикулов не найдется.
5. Введите номер столбца. Название товара стоит во втором столбце диапазона, поэтому указываем 2.
6. Выберите тип совпадения. Артикул нужно найти точно, поэтому ставим ЛОЖЬ (или 0).
7. Подтвердите и растяните формулу. Нажмите Enter, затем потяните маркер автозаполнения (квадрат в правом нижнем углу ячейки) вниз до последнего заказа.
Готовая формула ВПР в Эксель в ячейке D2:
=ВПР(B2;Прайс!$A$2:$D$9;2;0)
В русской версии Excel аргументы разделяются точкой с запятой, в английской — запятой.
Теперь подтянем цену с помощью той же формулы, только поменяем номер столбца на 4.
Формула для ячейки E2:
=ВПР(B2;Прайс!$A$2:$D$9;4;0)
Сумму в ячейке F2 считаем обычным умножением: =C2*E2.
Итого по шести заказам — 68 650 руб. Если цена в прайсе изменится, достаточно обновить ее в одном месте: ВПР в Экселе подтянет новое значение, и суммы пересчитаются автоматически.
Как выполнить приблизительный поиск с помощью функции ВПР в Эксель: пошаговая инструкция и пример
Функция приблизительного поиска подойдет, если вам нужно найти не точное значение, а подходящий диапазон. Допустим, скидка зависит от суммы заказа.
Создадим на листе «Скидки» таблицу, отсортированную по возрастанию:
Формула в ячейке G2, где в F2 лежит сумма заказа:
=ВПР(F2;Скидки!$A$2:$B$5;2;1)
В итоге по этой формуле мы рассчитали скидки для каждого товара. А затем вычислили сумму покупки с учетом скидки:
Частые ошибки при работе с формулой ВПР в Excel
Даже если формула составлена правильно, Excel иногда не находит нужное значение или возвращает неожиданный результат. Разберем самые распространенные проблемы и способы их решения.
1. Ошибка #Н/Д — значение не найдено
Это самая частая ошибка при работе с ВПР в Экселе. Она появляется, когда Excel не может найти искомое значение в первом столбце указанного диапазона.
Возможные причины:
- В таблицах есть опечатки. Например, в одном списке указан артикул K-101, а в другом — K-10I (последний символ — буква I, а не цифра 1).
- В одной ячейке есть лишние пробелы.
- В одной таблице артикул записан как текст, а в другой — как число.
- Искомое значение отсутствует в справочнике.
- Используется приблизительный поиск, а искомое значение меньше наименьшего значения в первом столбце диапазона.
Как исправить: проверьте написание данных в обеих таблицах, удалите лишние пробелы и убедитесь, что форматы ячеек совпадают. Если значение действительно может отсутствовать, добавьте обработку ошибки:
=ЕСЛИОШИБКА(ВПР(B2;Прайс!$A$2:$D$9;4;ЛОЖЬ);"Не найдено")
Теперь вместо #Н/Д Excel покажет текст «Не найдено». Это удобно, если в таблице много строк и не все артикулы обязательно должны присутствовать в справочнике.
2. Ошибка #ССЫЛКА! — указан неправильный номер столбца
Такая ошибка появляется, если номер столбца в третьем аргументе больше количества столбцов в выбранном диапазоне.
Например, вы указали таблицу A2:D9, в которой четыре столбца, но в формуле ВПР в Эксель написали:
=ВПР(B2;Прайс!$A$2:$D$9;5;ЛОЖЬ)
Excel не может вернуть значение из пятого столбца, потому что его нет в диапазоне.
Как исправить: пересчитайте столбцы внутри выбранного диапазона. В примере с прайсом артикул находится в первом столбце, название товара — во втором, категория — в третьем, цена — в четвертом. Поэтому для получения цены нужно указать 4.
=ВПР(B2;Прайс!$A$2:$D$9;4;ЛОЖЬ)
3. Формула возвращает неправильное значение
Причина может быть в четвертом аргументе — интервальный_просмотр. Если вместо ЛОЖЬ (0) указать ИСТИНА (1) или вовсе пропустить этот аргумент, Excel будет искать приблизительное совпадение.
Например, вы хотите найти цену товара по артикулу, но функция ВПР в Эксель возвращает данные из другой строки. Такое возможно, если включен приблизительный поиск.
Как исправить: для поиска артикулов, номеров заказов, имен и других конкретных значений всегда указывайте ЛОЖЬ или 0:
=ВПР(B2;Прайс!$A$2:$D$9;4;ЛОЖЬ)
4. При копировании формулы Excel ищет не в той таблице
Если после протягивания формулы вниз часть значений находится, а часть нет, проверьте ссылки на диапазон.
Например, первоначально формула выглядит так:
=ВПР(B2;Прайс!A2:D9;2;ЛОЖЬ)
После копирования на следующую строку Excel автоматически изменит диапазон на A3:D10, затем на A4:D11 и так далее. В результате поиск будет выполняться не по всему справочнику.
Как исправить: закрепите диапазон с помощью абсолютных ссылок. Для этого выделите ссылку на таблицу в строке формул и нажмите F4 (в Excel для Mac используйте соответствующую комбинацию для вашей клавиатуры).
Правильная формула:
=ВПР(B2;Прайс!$A$2:$D$9;2;ЛОЖЬ)
Теперь при копировании вниз диапазон останется неизменным, а ссылка на артикул будет меняться.
5. ВПР в Эксель возвращает не то совпадение
Если в первом столбце справочника есть одинаковые значения, функция вернет результат из первой подходящей строки. Остальные совпадения она проигнорирует.
Например, в базе клиентов два человека с одинаковой фамилией. Если искать данные только по фамилии, ВПР вернет информацию о первом найденном человеке.
Как исправить: используйте уникальный идентификатор для поиска: номер клиента, ИНН, ID заказа или другой параметр, который не повторяется. Если уникального значения нет, можно объединить несколько столбцов в один вспомогательный ключ.
6. ВПР в Эксель не находит значение, которое расположено левее искомого столбца
ВПР всегда ищет значение в первом столбце выбранного диапазона и возвращает данные только из столбцов правее него.
Например, если в таблице сначала указано название товара, а затем артикул, найти название по артикулу с помощью обычной формулы ВПР в Excel не получится.
Как исправить: переставьте столбцы так, чтобы артикул оказался первым, или используйте более гибкую функцию ПРОСМОТРX (XLOOKUP), если она доступна в вашей версии Excel. Эта функция ищет в любом направлении и по умолчанию выполняет точное сравнение. Microsoft рекомендует ее как более удобную альтернативу ВПР.
Главное о формуле ВПР в Excel
ВПР помогает быстро находить информацию в больших таблицах и связывать данные из разных источников. Чтобы пользоваться функцией без ошибок, запомните несколько правил:
- ВПР ищет значения по вертикали. Функция находит искомое значение в первом столбце выбранного диапазона и возвращает данные из другой колонки той же строки.
- У формулы ВПР в Эксель четыре аргумента. Нужно указать, что искать, где искать, из какого столбца вернуть результат и какой тип совпадения использовать.
- Для точного поиска выбирайте ЛОЖЬ или 0. Это особенно важно при работе с артикулами, номерами заказов, именами и другими конкретными значениями.
- ИСТИНА или 1 включает приблизительный поиск. В этом режиме Excel ищет точное совпадение или ближайшее меньшее значение. Такой вариант подходит, например, для определения скидки в зависимости от суммы заказа или расчета бонусов по диапазонам продаж.
- Закрепляйте диапазон абсолютными ссылками. Знаки $ не дадут Excel сместить таблицу при копировании формулы.
- Проверяйте исходные данные. Опечатки, пробелы и разные форматы ячеек могут помешать поиску.
- Помните об ограничениях. ВПР в Эксель ищет значение только в первом столбце выбранной таблицы. Результат можно получить только из столбцов, расположенных правее столбца поиска. Если искомое значение встречается несколько раз, ВПР вернет данные только из первой подходящей строки.
