Функция ВПР: как подтянуть цену из прайс-листа по коду
Функция ВПР нужна, когда данные разложены по двум таблицам: в одной заказы с кодом товара, в другой прайс с названием и ценой. Формула находит код в прайсе и возвращает цену в строку заказа — без копирования вручную и без ошибок.
- Точное и приблизительное совпадение
- Подсказки аргументов
- Бесплатно
- Без регистрации
Как сделать ВПР по шагам
Пример «ВПР: прайс-лист и заказы»
Откройте пример: слева прайс из восьми товаров с колонками «Код», «Товар» и «Цена», справа — заказы. В каждом заказе есть код, а цену нужно подставить из прайса.
Строка формул
Выделите ячейку цены в первом заказе и введите в строке формул =ВПР(F4;$A$4:$C$11;3;ЛОЖЬ). Код берётся из F4, диапазон прайса — A4:C11, третий столбец — цена, ЛОЖЬ включает точное совпадение.
Маркер заполнения
Протяните формулу вниз за маркер заполнения: знаки $ удерживают диапазон прайса, поэтому каждая строка ищет свой код. У заказа с неизвестным кодом стоит ЕСЛИОШИБКА, и вместо #Н/Д видно «не найдено».
Что умеет функция ВПР
| Возможность | Что это даёт |
|---|---|
| Точное совпадение | Четвёртый аргумент ЛОЖЬ или 0: подходит только код, совпадающий целиком. |
| Приблизительное совпадение | ИСТИНА или 1: ищет по отсортированному первому столбцу, например ставку по порогу суммы. |
| Подстановочные знаки | «*» и «?» в точном совпадении: «*доставка*» найдёт строку, где это слово есть в названии. |
| Регистр | «код-01» и «КОД-01» считаются одним и тем же значением. |
| ГПР | Ищет по горизонтали, когда заголовки стоят в первой строке таблицы. |
| ПРОСМОТРX | Ищет в любом столбце, возвращает значение из любого столбца и умеет задать результат «если не найдено». |
| ИНДЕКС и ПОИСКПОЗ | Пара функций для поиска, когда нужный столбец стоит слева от кода. |
| ЕСЛИОШИБКА и ЕСНД | Заменяют #Н/Д понятным текстом вроде «не найдено». |
Как работает функция ВПР
У ВПР четыре аргумента: что ищем, где ищем, из какого столбца вернуть значение и какое совпадение нужно. В русской редакции аргументы разделяются точкой с запятой, а десятичная часть пишется через запятую.
Формула =ВПР(F4;$A$4:$C$11;3;ЛОЖЬ) читается так: возьми код из F4, найди его в первом столбце диапазона A4:C11 и верни значение из третьего столбца — цены. ЛОЖЬ означает, что нужен ровно этот код, без «похожих» вариантов.
Диапазон лучше закрепить: F4 при копировании меняется, а $A$4:$C$11 остаётся на месте. Клавиша F4 при курсоре на ссылке переключает варианты $A$4, A$4, $A4 и A4.
- Первый аргумент — искомое значение: код товара, артикул или номер.
- Второй — диапазон, в первом столбце которого лежат те же коды.
- Третий — номер столбца внутри диапазона, откуда вернуть значение; цена в примере — третий.
- Четвёртый — тип совпадения: ЛОЖЬ для точного, ИСТИНА для приблизительного.
Функция ВПР в Excel и здесь: одинаковый результат
ВПР работает так же, как в Excel: те же четыре аргумента и те же результаты. Русская редакция показывает и принимает русские имена и точку с запятой, а английское имя с точкой с запятой тоже понимается: =VLOOKUP(F4;$A$4:$C$11;3;ЛОЖЬ).
При сохранении в XLSX формула записывается в стандартной английской форме, поэтому книга открывается в других программах. VseApp не связан с Microsoft — это отдельный редактор, который читает и сохраняет книги Excel.
Когда это нужно
- Прайс-лист поставщика: в заказах есть только код, а название и цену подтягивает ВПР.
- Остатки на складе: по артикулу найдите количество в другой таблице.
- Зарплатная ведомость: ставка или надбавка берётся из справочника по тарифному разряду.
- Учёба: по номеру студента подтяните оценку из ведомости.
- Отчёт по продажам: сведите список заказов с категориями товаров.
ВПР в экселе: точное совпадение
ВПР в экселе чаще всего используют с ЛОЖЬ: нужен именно этот код, а не соседний. Если в четвёртом аргументе оставить 0, результат тот же. Подстановочные знаки «*» и «?» работают только при точном совпадении и помогают искать по части названия.
Когда кода нет в первом столбце, функция возвращает #Н/Д. Оберните её в ЕСЛИОШИБКА(ВПР(…);«не найдено»), и в отчёте вместо ошибки появится понятный текст — так и сделано в примере с заказом, которого нет в прайсе.
Функция ВПР пример: приблизительное совпадение
Если нужен не точный код, а диапазон, ставьте ИСТИНА. Первый столбец в этом случае сортируют по возрастанию: например, сумма заказа и процент скидки. Формула =ВПР(B2;$A$2:$B$5;2;ИСТИНА) вернёт ставку для ближайшего нижнего порога.
Для поиска по нескольким условиям или в обратную сторону удобнее ПРОСМОТРX либо ИНДЕКС и ПОИСКПОЗ: они не требуют, чтобы ключ стоял в первом столбце.
Почему ВПР возвращает #Н/Д
- Кода нет в первом столбце прайса — проверьте, что таблица та самая.
- В коде лишний пробел: регистр не важен, а пробел в конце строки мешает совпадению.
- Диапазон не закреплён и при копировании съехал на строку ниже.
- Четвёртый аргумент пропущен, и функция ищет приблизительно.
- Код хранится как текст, хотя в прайсе это число, — выровняйте формат столбцов.
Ограничения
- ВПР ищет только в первом столбце диапазона и возвращает значение правее; для поиска влево нужны ИНДЕКС и ПОИСКПОЗ или ПРОСМОТРX.
- Приблизительное совпадение требует отсортированного первого столбца.
- Макросы VBA не выполняются, а старый формат XLS не открывается.
- Совместной правки в реальном времени нет.
Безопасность и приватность
Прайсы и заказы открываются и считаются в браузере на вашем устройстве, на сервер файл не загружается. В облако книга попадёт, только если нажать «Сохранить в облако» и войти; данные редакции хранятся в России. Готовую книгу можно открыть в редакторе XLSX или сразу сохранить в PDF.
На телефоне и компьютере
Формулу можно поправить в мобильном браузере на Android и iPhone: строка формул и подсказки работают так же. Для широкого прайса и проверки диапазонов удобнее компьютер, а на телефоне проще посмотреть результат и исправить один код.
Частые вопросы
Как работает функция ВПР?
ВПР ищет значение в первом столбце диапазона и возвращает данные из указанного столбца той же строки. Аргументы: что ищем, где ищем, номер столбца и тип совпадения. ЛОЖЬ или 0 — точное совпадение, ИСТИНА или 1 — приблизительное по отсортированным данным. Если значения нет, функция возвращает #Н/Д.
Как сделать ВПР в Excel?
Выделите ячейку, поставьте знак равенства, введите ВПР и откройте скобку. Укажите код, диапазон прайса с закреплёнными знаками $, номер столбца с нужным значением и ЛОЖЬ для точного совпадения. Закройте скобку, нажмите Enter и протяните формулу вниз за маркер заполнения.
Как пользоваться ВПР в Excel?
Сначала убедитесь, что код есть в первом столбце второй таблицы. Затем напишите =ВПР(код;диапазон;номер_столбца;ЛОЖЬ). Если формула возвращает #Н/Д, проверьте пробелы, закрепление диапазона и формат значений. Для понятного сообщения оберните её в ЕСЛИОШИБКА и подставьте текст «не найдено».
Как пользоваться функцией ВПР?
Откройте строку формул и введите ВПР с четырьмя аргументами. Начните с точного совпадения: так проще заметить ошибку в коде. Подсказка под ячейкой показывает назначение каждого аргумента, а F4 закрепляет ссылки. Результат можно протянуть на весь столбец одной формулой.
Как работает ВПР в эксель?
Так же, как в любой книге Excel: находит значение в первом столбце диапазона и возвращает значение из столбца справа. В русской редакции формула записывается с точкой с запятой: =ВПР(F4;$A$4:$C$11;3;ЛОЖЬ). Английские имена редакция тоже понимает, а в XLSX формула сохраняется в стандартном виде.
Как ВПР в Excel для чайников?
Представьте две таблицы: в одной код и цена, в другой код и заказ. ВПР говорит: возьми код заказа, найди такой же в прайсе и принеси цену из третьего столбца. Начните с ЛОЖЬ — точного совпадения — и закрепите диапазон знаками $, чтобы он не съехал.
Как использовать функцию ВПР?
Выберите ячейку результата и введите формулу с кодом, диапазоном и номером столбца. Четвёртый аргумент ЛОЖЬ оставьте для точного поиска. Если таблица большая, закрепите диапазон через F4 и протяните формулу вниз. Для ошибок добавьте ЕСЛИОШИБКА, чтобы в отчёте был текст, а не #Н/Д.
Это бесплатно? Будет ли водяной знак?
Да, инструмент бесплатный: без оплаты, пробного периода и водяных знаков. Все функции поиска доступны сразу, а регистрация не нужна, чтобы открыть книгу, посчитать формулу и скачать файл. Вход требуется только для сохранения книги в облако.
Безопасно ли загружать документы?
Загружать ничего не нужно: книга открывается и обрабатывается в браузере на вашем устройстве. Поэтому прайсы, заказы и ведомости не попадают на чужие серверы. В облако файл уйдёт, только если вы сами нажмёте «Сохранить в облако»; данные редакции хранятся в России.
Другие инструменты
Функция ВПР: как подтянуть цену из прайс-листа по коду
Подтяните цену, остаток или ставку из другой таблицы по коду формулой ВПР.
Открыть редактор