Функция ВПР в Excel: примеры и частые ошибки
ВПР — одна из самых нужных функций Excel для тех, кто работает с прайсами, остатками и отчётами. Она подтягивает данные из одной таблицы в другую по общему ключу. Разберём синтаксис, примеры и частые ошибки.
Синтаксис
=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])
- искомое_значение — что ищем (например, артикул в A2);
- таблица — где ищем; искомое должно быть в первом столбце этого диапазона;
- номер_столбца — из какого столбца диапазона вернуть результат;
- интервальный_просмотр — почти всегда
ЛОЖЬ(точное совпадение).
Пример: подтянуть цену из прайса
На листе «Заказ» в столбце A — артикулы. На листе «Прайс» в A — артикулы, в B — названия, в C — цены.
=ВПР(A2;Прайс!$A$2:$C$1000;3;ЛОЖЬ)
Знаки $ закрепляют диапазон, чтобы он не «съезжал» при протягивании формулы вниз.
Почему ВПР выдаёт #Н/Д
| Причина | Решение |
|---|---|
| Лишние пробелы в начале или конце | =ВПР(СЖПРОБЕЛЫ(A2);...) или очистить исходные данные |
| Число в одной таблице, текст в другой | Привести к одному формату: ТЕКСТ(A2;"0") или ЗНАЧЕН(A2) |
| Искомое не в первом столбце диапазона | Сдвинуть диапазон или использовать ПРОСМОТРX |
| Значения действительно нет | Обернуть в ЕСЛИОШИБКА(...;"нет в прайсе") |
| Артикулы записаны по-разному («KT-1700» и «KT1700») | Нормализовать ключи — см. матчинг |
Альтернативы
ПРОСМОТРX (XLOOKUP)
=ПРОСМОТРX(A2;Прайс!A:A;Прайс!C:C;"нет в прайсе") — короче, гибче и без номера столбца.
ИНДЕКС + ПОИСКПОЗ
=ИНДЕКС(Прайс!C:C;ПОИСКПОЗ(A2;Прайс!A:A;0)) — работает во всех версиях и ищет в любом столбце.
Ограничения ВПР
- возвращает только первое найденное совпадение — дубли в прайсе дадут неверный результат (см. удаление дубликатов);
- на десятках тысяч строк с множеством формул файл заметно тормозит;
- при добавлении столбцов в исходную таблицу номер столбца может стать неверным.
Для регулярного сведения больших таблиц надёжнее Power Query. А если источников много и названия не совпадают, сведение можно доверить нам — см. объединение данных в единый отчёт.
ВПР — это что в Excel
ВПР расшифровывается как «вертикальный просмотр». Функция ищет значение в первом столбце таблицы сверху вниз (вертикально) и возвращает значение из той же строки в указанном столбце. В английской версии Excel она называется VLOOKUP. Есть и ГПР — горизонтальный просмотр, который ищет по строке.
Как перенести данные из одной таблицы в другую с помощью ВПР
Пошагово на примере «подтянуть остатки со склада в таблицу заказа»:
- Откройте обе таблицы — можно в одной книге на разных листах или в разных файлах.
- В таблице заказа встаньте в первую пустую ячейку столбца «Остаток».
- Введите
=ВПР(, щёлкните ячейку с артикулом в этой строке, поставьте точку с запятой. - Перейдите на лист «Склад» и выделите столбцы от артикула до остатка, нажмите F4, чтобы закрепить диапазон.
- Укажите номер столбца остатка в выделенном диапазоне и
ЛОЖЬ. - Нажмите Enter и протяните формулу вниз.
Если источник в другом файле, держите его открытым при первом вводе формулы — Excel запишет полный путь.
ВПР с несколькими условиями
ВПР ищет по одному столбцу. Если нужно искать по двум условиям — например, артикул + город, — создайте вспомогательный ключ в исходной таблице: =A2&"|"&B2, и ищите по такому же ключу. В новых версиях Excel то же самое удобнее сделать через ФИЛЬТР или ПРОСМОТРX с массивами.
Приблизительный поиск: когда ИСТИНА полезна
Четвёртый аргумент ИСТИНА ищет ближайшее меньшее значение в отсортированном столбце. Это удобно для шкал: скидка по сумме заказа, комиссия по ценовому диапазону, налоговые ставки. Для поиска артикулов и названий всегда используйте ЛОЖЬ.
ВПР в Google Таблицах
В Google Таблицах функция называется VLOOKUP (в русском интерфейсе тоже может отображаться как ВПР) и работает так же: =VLOOKUP(A2;Склад!A:C;3;FALSE). Там же есть XLOOKUP — аналог ПРОСМОТРX.
Когда ВПР — не лучший выбор
- Десятки тысяч строк — файл тормозит; лучше Power Query.
- Разные названия одного товара — ВПР не найдёт «KT-1700» по «KT1700»; нужна нормализация или матчинг.
- Регулярное сведение многих источников — лучше автоматизировать процесс целиком: объединение данных.
Читайте также
Сводная таблица в Excel: как сделать и использовать
Пошаговая инструкция с примером отчёта по продажам и ценам.
Читать →Power Query: что это и как автоматизировать сбор данных
Загрузка, очистка и объединение таблиц без формул и макросов.
Читать →Как удалить дубликаты в Excel и Google Таблицах
Встроенные инструменты, формулы и поиск «почти одинаковых» строк.
Читать →Консолидация данных: что это и как сделать в Excel
Сведение данных с разных листов и из разных файлов в одну таблицу.
Читать →Частые вопросы
Почему ВПР выдаёт #Н/Д?
Значение не найдено. Чаще всего из-за лишних пробелов, разного формата (число против текста), опечаток или потому что искомое значение находится не в первом столбце диапазона.
Что лучше: ВПР или ПРОСМОТРX?
ПРОСМОТРX гибче: ищет в любом столбце, по умолчанию ищет точное совпадение и умеет возвращать значение «если не найдено». Но она есть только в новых версиях Excel; ВПР работает везде.
Как расшифровывается ВПР?
Вертикальный просмотр: функция ищет значение в первом столбце диапазона сверху вниз и возвращает данные из той же строки. В английском Excel — VLOOKUP.
Как перенести данные из одной таблицы в другую с помощью ВПР?
В целевой таблице введите =ВПР(ключ; закреплённый диапазон исходной таблицы; номер столбца с нужными данными; ЛОЖЬ) и протяните формулу вниз.
Нужны данные для вашего бизнеса? Опишите задачу
Пришлите ссылку на сайт или маркетплейс и напишите, какие данные нужны. Мы оценим задачу, назовём срок и стоимость и покажем пример результата на ваших данных до оплаты.
- Ответим в течение рабочего дня
- Пробная выгрузка до начала работ
- Разовый сбор или регулярный отчёт по расписанию
- Только открытые источники — без персональных данных
Или напрямую: +7 705 277 80 80 · WhatsApp · info@life-com.kz