Главная » Блог » Excel

Что такое формула ВПР в Excel: Находим и сопоставляем данные, как Профи!

Вера Астахова

24.11.2025

5 Просмотры 0

В избранноеВ избранномRemoved 0

Представьте: у вас две таблицы. В одной список товаров с кодами, в другой — эти же коды с ценами. Нужно подтянуть цены к товарам. Вручную это займет несколько часов, а с функцией ВПР — пару минут. Не пугайтесь её названия. На самом деле, Vlookup — это ваш личный, сверхбыстрый секретарь, который умеет искать информацию по заданному критерию. Сегодня разберём эту волшебную функцию простым языком, без сложных терминов.

🛠️ Разбираем формулу на части

В формуле 4 аргумента или вопроса, на которые надо ответить.

$$\text{= ВПР}(\text{Искомое значение}; \text{Таблица}; \text{Номер столбца}; \text{Интервальный просмотр})$$

Давайте разберём каждый из них:

Что такое ВПР и зачем она нужна

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

Простыми словами: функция функционирует как справочник. Вы говорите «найди мне информацию про товар с кодом 12345», и функция находит этот код в большой таблице, а затем возвращает нужные данные — цену, название, категорию или что угодно.

Расшифровка названия:

В — вертикальный (функция ищет по вертикали, то есть по столбцам)

ПР — просмотр (просматривает таблицу, чтобы найти совпадение)

Как работает:

1. Принимает определенный набор символов в качестве запроса.
2. Подыскивает совпадение с этим запросом в крайнем левом столбце заданного диапазона.
3. Копирует значения из ячейки, которая находится в соседнем столбце, но на этой же строке.

Когда функция реально выручает

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

Функция мгновенно справляется с тысячами строк и задачами разной сложности. 

Если интересны другие функции программы, читайте подробные инструкции в нашем блоге: 

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

Исходные данные

Задача: Подставить цены из прайс-листа в таблицу заказов.

Решение:

1. Выбираем функцию и запрос

Кликните по ячейке C3 (здесь будет цена первого товара)

Наберите знак = и напишите ВПР(

Кликните на ячейку A3 (артикул) или напишите A3

2. Настраиваем диапазон запроса

Поставьте ;

Выделите диапазон E:F (весь прайс-лист) или напишите E:F

Важно: Выделяйте только тот диапазон, где будет поиск. Артикул всегда стоит в первом столбце диапазона!

3. Выбираем номер столбца

Поставьте ;

Напишите 3 (цена находится во втором столбце диапазона E:F)

4. Выбираем тип поиска

Поставьте ;

Напишите 0 (точное совпадение)

Закройте скобку ) и нажмите Enter

Полная запись: =ВПР(A3;E:F;3;0)

Что означает каждая часть:

A3 — берём артикул из первой строки заказов

E:F — ищем этот артикул в прайс-листе (E и F)

3 — возвращаем значение из третьего столбца прайс-листа (то есть из столбца F с ценами)

0 — ищем точное совпадение

5. Настраиваем функцию под всю таблицу

Выделите ячейку C2 с формулой. Важно: Измените формулу, добавив знаки $:

= ВПР(A2;$E:$F;2;0)

Наведите курсор на правый нижний угол ячейки (появится чёрный крестик)

Зажмите левую кнопку мыши и потяните вниз

Зачем нужен знак $:

Он фиксирует диапазон E:F. Без него при протягивании все будет меняться (E:F → F:G → G:H), и поиск сломается.

Готово! Цены автоматически подтянулись к каждому артикулу.

Как рассчитать скидку с приблизительным поиском?

Допустим, нужно сопоставить размер скидки с количеством покупок.

Важно: Значения в первом столбце надо отсортировать по возрастанию!

Формула в ячейке C2: = ВПР(B2;$G$2:$H$5;2;1)

Результат:

Клиент Иванов: 12 покупок → подходит диапазон «10 и больше» → скидка 10%

Клиент Петров: 45 покупок → подходит диапазон «20 и больше» → скидка 15%

Клиент Сидоров: 3 покупки → меньше минимума (5) → скидка 0% (или ошибка)

Последний аргумент 1 означает: «найди ближайшее меньшее или равное значение».

Ошибки функции и пути их решения

Важные особенности ВПР, о которых надо знать

1. Ищет только справа

Функция смотрит только в столбцы правее того, где подыскивает совпадение. Если данные слева — ВПР не сработает.

Пример:

Если цена слева, а артикул с другой стороны, ВПР не найдёт цену по артикулу.

Решение: Поменяйте данные в столбцах местами или используйте продвинутую функцию ИНДЕКС+ПОИСКПОЗ 

2. Берет первое найденное совпадение

Если в таблице несколько одинаковых значений, функция вернет данные только из первой найденной строки. Остальные игнорирует.

3. Функция чувствительна к регистру? Нет!

Функция не различает большие и маленькие буквы. Для неё «яблоко», «ЯБЛОКО» и «Яблоко» — одно и то же.

4. Фиксируйте диапазон знаком $

Когда копируете формулу, диапазон поиска должен оставаться неизменным. Используйте абсолютные ссылки: =ВПР(A2;$D$2:$E$100;2;0)

Знаки $ фиксируют диапазон D2:E100, чтобы он не съезжал при копировании.

Типы фиксации:

• $D$2:$E$100 — полная фиксация
• $D2:$E100 — фиксация только информации в столбцах
• D$2:E$100 — фиксация только строк

Как искать данные, которые находятся СЛЕВА

ВПР ищет только справа от первого столбца. Если нужные данные с левой стороны — функция не будет работать.

Пример:

Если ищете по коду товара (средний столбец), а цена с левой стороны — ВПР не найдёт.

Решение 1: Поменяйте данные в столбцах местами

Самый простой способ — переставьте так, чтобы код был первым:

Решение 2: Используйте массивы. Способ для продвинутых пользователей.

Массив в Excel — это виртуальная таблица внутри формулы, где можно менять данные в столбцах местами.

Что такое массив:

Массив — это набор данных в фигурных скобках { }. Можно сделать «виртуальную таблицу», переставив то что в столбцах нужным образом.

Разделители в массивах:

\ — разделяет столбцы

; — разделяет строки

=ВПР(H2;{D:D\C:C};2;0)

Что означает:

{D:D\C:C} — создаем массив из двух столбцов

\ — обратный слэш разделяет информацию в столбцах

Теперь D (код) стал первым, C (цена) — вторым

Анализирует D, возвращает из C

Решение 3: ИНДЕКС + ПОИСКПОЗ (универсальное решение)

Эта связка сработает в любом направлении без ограничений:

=ИНДЕКС(C:C;ПОИСКПОЗ(A2;D:D;0))

Расшифровка:

ПОИСКПОЗ(A2;D:D;0) — выводит позицию кода в столбце D

ИНДЕКС(C:C;…) — возвращает стоимость из столбца C по найденной позиции

Функция сработает в любом направлении, быстрее на больших таблицах и не ломается при вставке новой информации.

Поиск по части текста

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

1. Символ * (звёздочка) — любое количество любых символов

Задача: Найти любой товар, в названии которого есть слово «iPhone»

=ВПР(«*искомое_слово*»;A:B;2;ЛОЖЬ)

Где:

• *искомое_слово* — часть текста, которую ищете
• A:B — диапазон поиска (первый столбец A для поиска, второй B для результата)
• 2 — число столбца с результатом
• ЛОЖЬ — совпало с учётом подстановочных знаков

Что найдёт:

«Apple iPhone 14» 

«iPhone Pro Max» 

«Чехол для iPhone»

«iPhone 123» 

НЕ найдёт:

«Айфон» из-за другого написания.

2. Символ ? (знак вопроса) — ровно один любой символ

Задача: Найти iPhone с конкретным количеством букв, цифр после слова

=ВПР(«iPhone??;A:B;2;0)

Что найдёт:

«iPhone 14» ✅ (два символа: 1 и 4)

НЕ найдёт:

«iPhone 123» ❌ (три символа)

3. Комбинация

=ВПР(«Samsung*64??»;A:B;2;0)

Найдёт:

«Samsung Galaxy 64GB» ✅

«Samsung Note 64Гб» ✅

Не найдёт:

«Samsung 128GB» ❌ (не 64)

«Samsung 64» ❌ (не хватает двух букв после 64)

Важно!

Если совпадений несколько — функция вернёт только первое

Подстановочные знаки работают только с текстом

Для точного поиска * или ? используйте ~* и ~?

ВПР с несколькими условиями одновременно

Стандартная функция работает только по одному критерию. Но иногда нужно найти товар сразу по двум параметрам, например: модель + цвет.

Пример: Нужно найти цену iPhone белого цвета.

Решение: Создайте вспомогательный столбец

Шаг 1: Добавьте новый столбец, который склеивает оба критерия

В таблице-источнике (столбец A): =B2&»_»&C2

Результат: «iPhone_Чёрный», «Samsung_Белый»

Таблица теперь выглядит так:

Шаг 2: Создайте такой же склеенный ключ для поиска

В основной таблице:

=E2&»_»&F2

Где E2 — модель, F2 — цвет.

Результат: «iPhone_Белый»

Шаг 3: Используйте склеенный ключ =ВПР(A3;A:D;4;0)

Где A3 — со склеенным ключом «iPhone_Белый»

Результат: 72000

Альтернативы ВПР: когда нужно что-то другое

ВПР — отличная функция для начала, но у нее есть ограничения, которые могут замедлить работу или вообще помешать решить задачу, поэтому стоит использовать мощные альтернативы.

Функция ГПР (горизонтальный просмотр)

Работает так же, но анализирует строки, а не то данные в столбцах. Используется редко, когда данные расположены горизонтально.

=ГПР(искомое_значение;диапазон;номер_строки;0)

С помощью функции ГПР можно например, найти стоимость, если товары расположены в строках, а не столбцах.

Связка ИНДЕКС + ПОИСКПОЗ

Более гибкая замена, т.к. работает в любом направлении. А также, работает быстрее на больших таблицах и не ломается при вставке нового столбца.

=ИНДЕКС(E:E;ПОИСКПОЗ(A2;D:D;0))

Как работает:

ПОИСКПОЗ(A2;D:D;0) — выводит номер строки, где находится значение A2 в столбце D

ИНДЕКС(E:E;…) — возвращает значение из столбца E в найденной строке

Функция XLOOKUP

Современная замена, имеет более понятный синтаксис. Работает в любом направлении, можно задать значение по умолчанию и заменить ошибку.

=XLOOKUP(A2;D:D;E:E;»Не найдено»)

К сожалению, работает только в новой версии Excel (Office 365, Excel 2021 и новее).

Лайфхаки для работы 

1. Учитывайте особенности автозаполнения 

Как протянуть: кликнуть на ячейку с формулой, навести на правый нижний угол, зажать крестик и протянуть вниз. 

После протягивания вниз:

Как сделать, чтобы диапазон не “съезжал”?

Используйте $ для фиксации диапазона.

= ВПР(A2;$E$2:$F$10;2;0)

После протягивания: 

В формуле из всех составляющих зафиксировать надо только диапазон, чтобы он оставался при протягивании одинаковым. Добавлять $ можно вручную. Второй способ — выделить диапазон Е2:F10 и нажать F4. Получим мгновенно $E$2:$F$10.

2. Используйте выпадающие списки

Создайте списки допустимых значений для поиска — это исключит опечатки и ошибки #Н/Д.

Как сделать:

Данные → Проверка данных → Список → укажите диапазон со значениями.

3. Добавляйте пояснения

В примечаниях к ячейке объясняйте, что делает функция. Через месяц вы сами забудете логику.

Как вставить:

Правой кнопкой → Добавить примечание

4. Форматируйте таблицы

Преобразуйте данные в умную таблицу (Ctrl+T). Тогда в формуле можно использовать название таблицы вместо диапазона: = ВПР(A2;ТаблицаПрайс;2;0)

Это удобнее и понятнее.

5. Копируйте формулы, а не значения

Когда нужно скопировать формулу на другой лист, используйте Ctrl+C и Ctrl+V. Если нужны только значения — сначала вставьте формулу, потом скопируйте результат и вставьте через «Специальная вставка → Значения».

6. Проверяйте результаты выборочно

После создания формул проверьте несколько строк вручную. Убедитесь, всё ли подтягивается верно.

7. Защитите формулы от случайного удаления

После создания сложной ВПР защитите ячейки с формулами:

• Выделите ячейки с формулами
• Правая кнопка → Формат ячеек → Защита → поставьте галочку «Защищаемая ячейка»

• Рецензирование → Защитить лист → установите пароль

Теперь никто случайно не сотрёт вашу работу.

8. Используйте именованные диапазоны

Вместо: = ВПР(A2;$B$2:$D$100;3;0)

Создайте имя «ПрайсЛист» для диапазона B2:D100 и пишите:

= ВПР(A2;ПрайсЛист;3;0)

Как создать:

Выделите B2:D100

В поле имени (с левой стороны от строки формул) введите «ПрайсЛист»

Нажмите Enter

Или: Формулы → Диспетчер имён → Создать

9. Комбинируйте ВПР с ЕСЛИОШИБКА для чистоты

=ЕСЛИОШИБКА(ВПР(A2;Прайс!B:C;2;0);»Товар не найден»)

Вместо некрасивой ошибки #Н/Д пользователь увидит понятное сообщение.

10. Скрывайте вспомогательные столбцы

Если у вас есть технические данные в столбцах с промежуточными расчетами или склеенными ключами для поиска по нескольким условиям, просто скройте их. Таблица станет чище и понятнее.

Как сделать:

Правой кнопкой на букву столбца → «Скрыть столбец». Чтобы показать обратно — выделите и нажмите «Показать».

11. Используйте горячие клавиши

F4 — переключение между типами ссылок (A1 → $A$1 → A$1 → $A1)

Ctrl+` (буква Ё) — показать все формулы в таблице

F9 — вычислить часть формулы (выделите часть и нажмите F9)

FAQ (Часто задаваемые вопросы)

Заключение

ВПР — одна из самых полезных функций Excel. Освоив ее, вы сэкономите сотни часов на рутинной работе. Да, поначалу синтаксис кажется сложным, но после десятка попыток формула будет получаться автоматически.

Главное про Vlookup в Excel или кратко как пользоваться формулой:

• ВПР ищет по первому столбцу диапазона.
• Ставьте 0 в конце для точного поиска.
• Фиксируйте диапазон знаком $ при копировании.
• Используйте ЕСЛИОШИБКА для обработки ошибок.
• Проверяйте форматы данных — они должны совпадать.
• Для поиска в левой части используйте массивы или ИНДЕКС+ПОИСКПОЗ.
• Подстановочные знаки (* и ?) помогут искать по части текста.
• Склеивайте критерии для поиска по нескольким условиям.