ВПР и поиск по таблице

Учимся «подтягивать» данные из одной таблицы в другую по общему ключу — как соединить список учеников с их оценками.

ВПР (вертикальный просмотр, англ. VLOOKUP) ищет значение в первом столбце таблицы и возвращает значение из той же строки, но из нужного столбца.

Зачем это нужно

Данные редко лежат в одной таблице. Есть прайс-лист «товар → цена» и есть список покупок, где указаны только названия товаров. Чтобы рядом с каждой покупкой появилась цена, не нужно искать её глазами — за вас это сделает ВПР. То же на ЕГЭ: дан справочник «код → расшифровка», и надо проставить расшифровки к списку кодов. ВПР — это «склейка» таблиц по ключу.

Синтаксис ВПР

У функции четыре аргумента:

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

• искомое — что ищем (ключ);
• таблица — диапазон, в первом столбце которого ищется ключ;
• номер_столбца — из какого по счёту столбца таблицы вернуть результат (нумерация с 1 — это сам столбец ключа);
• интервальный_просмотр — ЛОЖЬ (0) для точного совпадения или ИСТИНА (1) для приближённого.

Пусть на листе есть прайс — диапазон E2:F5:

В ячейке A2 написано «Сыр». Формула

=ВПР(A2; E2:F5; 2; ЛОЖЬ)

найдёт «Сыр» в первом столбце (E) и вернёт значение из второго столбца (F) той же строки — 250. Номер столбца здесь 2, потому что внутри диапазона E:F цена — это второй столбец.

Точное и приближённое совпадение

Последний аргумент важнее, чем кажется.

ЛОЖЬ — точное совпадение

С ЛОЖЬ функция ищет ровно такое же значение. Если его нет — вернёт ошибку #Н/Д (нет данных). Для текстовых ключей (товары, фамилии, коды) почти всегда нужен именно этот режим, и таблицу сортировать не обязательно.

ИСТИНА — приближённое совпадение

С ИСТИНА функция ищет наибольшее значение, не превышающее искомое. Это удобно для шкал и диапазонов — например, перевод балла в оценку. Но есть жёсткое условие: первый столбец таблицы обязан быть отсортирован по возрастанию, иначе результат будет неверным. Пример шкалы баллов:

Для 73 баллов =ВПР(73; ...; 2; ИСТИНА) найдёт строку «60» (наибольшую, не превышающую 73) и вернёт оценку 4. Формально результат описывается так:

$$ \text{ВПР}(x) = v(k),\quad k = \max\{\,k_i \le x\,\} $$

где $k_i$ — ключи в отсортированном столбце, а $v(k)$ — значение в строке этого ключа.

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

В режиме точного поиска ВПР идёт по первому столбцу сверху вниз и останавливается на первом совпадении. Смоделируем это словарём в Python:

price = {"Хлеб": 40, "Молоко": 70, "Сыр": 250, "Яблоки": 120}

def vpr(key):
    return price.get(key, "#Н/Д")  # точное совпадение или ошибка

for tovar in ["Сыр", "Молоко", "Кефир"]:
    print(tovar, "->", vpr(tovar))

Вывод:

Сыр -> 250
Молоко -> 70
Кефир -> #Н/Д

«Кефира» в прайсе нет, поэтому функция вернула #Н/Д — ровно так же поведёт себя реальная ВПР с аргументом ЛОЖЬ.

ГПР, ПОИСКПОЗ и ИНДЕКС

У ВПР есть «горизонтальный» близнец — ГПР (HLOOKUP). Он устроен так же, но ищет ключ в первой строке таблицы и возвращает значение из нужной строки того же столбца. Применяют, когда данные лежат по горизонтали, а не по вертикали.

У ВПР есть слабые места: она ищет только в первом столбце и не умеет смотреть «влево» от ключа. Гибкая замена — связка ИНДЕКС + ПОИСКПОЗ.

• ПОИСКПОЗ(искомое; диапазон; 0) возвращает номер позиции ключа в диапазоне (третий аргумент 0 — точное совпадение).
• ИНДЕКС(диапазон; номер) возвращает значение из диапазона по этому номеру.

Соединив их, получаем поиск в любом направлении:

=ИНДЕКС(F2:F5; ПОИСКПОЗ(A2; E2:E5; 0))

ПОИСКПОЗ находит, в какой по счёту строке столбца E стоит «Сыр» (третья), а ИНДЕКС берёт из столбца F значение под этим номером — снова 250. Преимущество: столбец результата (F) может стоять как справа, так и слева от столбца ключа (E).

Частые ошибки

• Ошибка #Н/Д. Чаще всего это значит, что точного совпадения нет: лишний пробел, другой регистр или текст «123» против числа 123. Проверьте сам ключ.
• Забыли ЛОЖЬ. Если опустить последний аргумент, многие таблицы считают его ИСТИНА (приближённый поиск). На неотсортированных данных это даёт случайный неверный ответ. Для точного поиска всегда пишите 0 или ЛОЖЬ.
• Неверный номер столбца. Номер считается ВНУТРИ диапазона таблицы, а не по буквам листа. Если таблица E:F, то цена — это столбец 2, а не 6.
• Несортированный столбец при ИСТИНА. Приближённый поиск по неотсортированному первому столбцу молча возвращает мусор — без ошибки, что особенно опасно.

Итоги

• ВПР(искомое; таблица; номер_столбца; ЛОЖЬ) ищет ключ в первом столбце и возвращает значение из нужного столбца той же строки.
• Аргумент ЛОЖЬ (0) — точное совпадение, ИСТИНА (1) — приближённое, но требует отсортированного первого столбца.
• Отсутствие ключа даёт #Н/Д — это сигнал проверить пробелы, регистр и тип данных.
• ГПР — горизонтальный аналог; связка ИНДЕКС+ПОИСКПОЗ гибче ВПР и ищет в любом направлении.