ВПР (VLOOKUP) в Excel: полный гайд с примерами формул и VBA для поиска данных

Узнайте, как использовать функцию ВПР (VLOOKUP) в Excel для поиска данных в таблицах. Полный разбор с примерами, формулами, VBA-макросами и лайфхаками для ускорения работы.

🚀 ВПР в Excel: Полный гайд с формулами, макросами и практикой

Функция ВПР (VLOOKUP) — одна из самых популярных и при этом самых часто вызывающих вопросы функций Excel. Её задача проста: найти значение в первом столбце таблицы и вернуть данные из другой колонки в той же строке.

Многие пользователи сталкиваются с трудностями: ошибки #Н/Д, неправильные результаты, «съезжающие» ссылки и путаница с параметрами. В этой статье я разберу ВПР от А до Я, приведу примеры формул, покажу типичные ошибки, а также дам VBA-макросы для автоматизации.

📌 Основной синтаксис ВПР

Формула:

=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])

Разберём аргументы:

• искомое_значение – что ищем (например, фамилия сотрудника).
• таблица – диапазон, где искать (важно, чтобы первый столбец содержал искомое значение).
• номер_столбца – номер столбца в таблице, из которого взять результат.
• [интервальный_просмотр] – ЛОЖЬ для точного совпадения, ИСТИНА для приблизительного (чаще всего используем ЛОЖЬ).

🔎 Простой пример использования

У нас есть таблица должностей и окладов:

ДолжностьОкладСтажёр10 000Менеджер25 000Начальник отдела45 000

Нужно, чтобы Excel автоматически подставлял оклад в другой таблице по должности.

Формула:

=ВПР(A2;$E$2:$F$4;2;ЛОЖЬ)

• A2 – должность сотрудника
• $E$2:$F$4 – таблица с должностями и окладами
• 2 – столбец с окладами
• ЛОЖЬ – точное совпадение

📊 Пример использования ВПР с аргументом ИСТИНА (приблизительное совпадение)

Иногда нужно не точное значение, а ближайшее меньшее.

Например, у нас есть таблица тарифов:

Баллы | Скидка
0 | 0%
100 | 5%
200 | 10%
300 | 15%

Нужно, чтобы Excel подставлял скидку в зависимости от баллов клиента.

Формула:

=ВПР(A2;$E$2:$F$5;2;ИСТИНА)

• A2 — количество баллов клиента
• $E$2:$F$5 — таблица с тарифами
• 2 — столбец со скидкой
• ИСТИНА — ищем ближайшее меньшее значение

📌 Если в ячейке A2 = 250, Excel найдет ближайшее меньшее (200) и вернет 10%.

⚠️ Важно: таблица с тарифами должна быть отсортирована по возрастанию, иначе результат будет неверным.

⚠️ Типичные ошибки при работе с ВПР

1. #Н/Д – значение не найдено (часто из-за лишнего пробела).
2. Неверный столбец – указали не тот номер.
3. Диапазон «съехал» – не зафиксировали ссылки $.
4. Нет точного совпадения – забыли указать ЛОЖЬ.

🛠 Как исправить ошибки

• Использовать функцию СЖПРОБЕЛЫ для удаления лишних пробелов.
• Закреплять диапазон с помощью F4.
• Проверять правильность написания значений.
• Использовать ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(ВПР(A2;$E$2:$F$4;2;ЛОЖЬ);"Нет данных")

Подпишитесь на наш канал в Телеграм. Там готовые шаблоны, чек-листы и гайды. А также так вы не пропустите ничего важного.

💡 Продвинутые примеры ВПР

1. ВПР с автоматическим выбором столбца

Иногда нужно, чтобы Excel возвращал разные столбцы в зависимости от параметра.

=ВПР(A2;$E$2:$H$10;ПОИСКПОЗ("Оклад";$E$1:$H$1;0);ЛОЖЬ)

Здесь ПОИСКПОЗ сам находит номер столбца.

2. ВПР с объединением нескольких условий

Если нужно искать сразу по нескольким критериям (например, Фамилия + Город), можно создать вспомогательный столбец:

=B2&"_"&C2

А затем использовать ВПР уже по этому полю.

3. ВПР + ЕСЛИ для динамического поиска

=ЕСЛИ(D2="Москва";ВПР(A2;$E$2:$F$10;2;ЛОЖЬ);ВПР(A2;$H$2:$I$10;2;ЛОЖЬ))

🔥 VBA для ВПР

Иногда удобнее автоматизировать поиск данных через макрос.

Пример VBA-кода:

Sub FindSalary()

Dim searchValue As String

Dim result As Variant

searchValue = Range("A2").Value

result = Application.WorksheetFunction.VLookup(searchValue, Range("E2:F10"), 2, False)

Range("B2").Value = result

End Sub

Этот макрос ищет значение из ячейки A2 в таблице E2:F10 и выводит оклад в B2.

📊 ВПР и замена на XLOOKUP

В новых версиях Excel появилась функция XLOOKUP (ПОИСКX), которая решает многие проблемы ВПР:

=XLOOKUP(A2;E2:E10;F2:F10;"Нет данных")

Преимущества XLOOKUP:

• Не нужно считать номер столбца.
• Можно искать не только слева направо.
• Удобная обработка ошибок.

🎯 Практическое задание

1. Сделайте таблицу сотрудников с должностями и окладами.
2. Настройте ВПР для подстановки окладов.
3. Добавьте формулу с ЕСЛИОШИБКА.
4. Реализуйте макрос поиска оклада через VBA.
5. Попробуйте переписать формулу на ПОИСКX (если у вас Excel 365).

Скачать файлы:

• Excel_VLOOKUP_Pack.xlsx — готовый шаблон с рабочими формулами (VLOOKUP/COUNTIF/COUNTIFS/SUMIFS), реалистичными данными и листом «Инструкция».
• Excel_VLOOKUP_Macros.bas — модуль VBA с макросами для автозаполнения окладов и тарифов, а также функцией очистки лишних пробелов.

📢 Поддержите канал

Если материал был полезен — подпишитесь на канал и оставьте комментарий 👍

Ваши вопросы и опыт помогают делать статьи ещё лучше!