ВПР в Excel: точное совпадение, поиск по справочнику и исправление ошибок

Как устроен поиск через ВПР

Функция получает четыре аргумента: что искать, в какой таблице, из какого столбца вернуть результат и какой способ совпадения использовать. Для поиска конкретного кода в последнем аргументе укажем ЛОЖЬ. Так Excel будет искать точное значение, а не подходящее приблизительно.

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

В официальной справке Microsoft по ВПР показаны эти аргументы и различие между точным и приблизительным совпадением. Для первого рабочего примера не пропускайте последний аргумент: явная запись ЛОЖЬ делает выбранный способ поиска понятным и вам, и коллеге.

Смысл функции удобно пересказать одним предложением: «Найди код из заказа в первом столбце справочника и верни цену из нужного столбца найденной строки». Если такое предложение не получается, сначала уточните устройство таблиц, а уже потом пишите формулу.

Подготовим две таблицы

На учебном листе разместим заказы в столбцах A и B. В A находятся коды, в B — количество. Справочник поместим в столбцах H–J: код, название и цена. Эти адреса понадобятся дальше при чтении формулы.

Заголовки справочника находятся в H1:J1, сами данные — в H2:J4.

Код A999 добавлен намеренно: в справочнике его нет. Такой пример сразу показывает, как функция ведёт себя при отсутствии совпадения. Если тренироваться только на идеальных данных, первая реальная ошибка может выглядеть неожиданной.

В курсе «Excel и Google-таблицы для бухгалтеров» от «Бруноям» ВПР изучается вместе с другими функциями, большими таблицами и анализом данных. В описании предусмотрены практические задания и работа с наставником. Такая программа подойдёт, если поиск по справочнику — часть вашей регулярной работы с выгрузками и вам нужна последовательная практика на похожих задачах.

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

Напишем первую формулу

В ячейке C2 введём формулу для названия:

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

Первый аргумент A2 — код первой строки заказа. Второй аргумент $H$2:$J$4 — диапазон справочника. Третий аргумент 2 означает второй столбец внутри этого диапазона, то есть столбец I с названием. Последний аргумент ЛОЖЬ задаёт точное совпадение.

Для A101 получится «Лампа». Обратите внимание: число 2 отсчитывается не от края листа, а от начала выбранного справочника. В диапазоне H:J столбец H — первый, I — второй, J — третий. Номер буквы столбца на листе здесь не нужен.

Если ваша версия Excel использует английские названия функций, эта же функция называется VLOOKUP. Настройки также могут менять разделитель аргументов. Поэтому при переносе формулы из статьи полезно сверить язык функций и подсказку, которую показывает сам Excel.

Зачем нужны знаки доллара

Теперь скопируйте формулу из C2 вниз. Ссылка A2 должна превратиться в A3, затем в A4: в каждой строке ищется код соответствующего заказа. А диапазон справочника должен остаться на месте. Именно поэтому его адрес записан со знаками доллара.

Если написать H2:J4 без фиксации и протянуть формулу, диапазон в следующей строке станет H3:J5. Первая строка справочника выпадет из поиска, снизу добавится лишняя. Некоторые ответы ещё могут выглядеть правильными, поэтому такая ошибка не всегда заметна сразу.

Проверьте формулы в первой и последней строках. В нашем учебном примере отличаются только ссылки на код заказа. Диапазон $H$2:$J$4 и номер возвращаемого столбца остаются одинаковыми. Эта короткая проверка полезнее, чем доверие к тому, что автозаполнение прошло без предупреждений.

Подтянем цену и рассчитаем сумму

В столбце D будем возвращать цену. Теперь нужен третий столбец справочника:

=ВПР(A2;$H$2:$J$4;3;ЛОЖЬ)

Для A101 получим 1 200. В столбце E можно умножить количество на найденную цену: для первой строки это B2 × D2 = 2 × 1 200 = 2 400. Для A103 получится 3 × 500 = 1 500, для A102 — 1 × 800 = 800.

Сумма по трём найденным товарам равна 4 700. Но весь список заказов пока не проверен: строка A999 осталась без цены. Поэтому 4 700 нельзя молча назвать стоимостью всех заказов. В отчёте нужно отдельно отметить, что одна строка требует уточнения справочника.

Этот пример показывает, почему правильная формула ещё не гарантирует полноту результата. Она может безошибочно находить существующие коды, но в исходных данных остаются записи, которых справочник не содержит. Их обработка — отдельная часть задачи.

Почему появляется ошибка #Н/Д

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

Проверьте пробелы в начале и конце, похожие латинские и русские буквы, а также тип данных. Например, число 125 и текстовая запись кода «00125» имеют разный смысл, если ведущие нули являются частью идентификатора. Превращать все коды в числа ради исчезновения ошибки опасно для точности справочника.

Другой частый случай — лишний символ после копирования из внешней системы. Сравните длину двух значений и посмотрите исходную запись в строке формул. Если источник регулярно присылает такие значения, очистку лучше сделать отдельным понятным шагом, а не исправлять десятки строк вручную каждый месяц.

В справке Microsoft об ошибке #Н/Д разобраны отсутствие совпадения и связанные с поиском ситуации. При проверке двигайтесь от конкретного к общему: сначала одна проблемная строка, затем правило, по которому подготовлен весь столбец кодов.

Можно ли спрятать ошибку через ЕСЛИОШИБКА

Для удобного отображения иногда используют такую запись:

=ЕСЛИОШИБКА(ВПР(A2;$H$2:$J$4;3;ЛОЖЬ);"Проверить код")

Теперь вместо сообщения об ошибке появится понятная пометка. Однако отсутствие товара в справочнике никуда не исчезнет. Кроме того, ЕСЛИОШИБКА реагирует на разные ошибки выражения, поэтому такая оболочка не объясняет причину сбоя сама по себе.

На этапе проверки лучше видеть проблемные строки отдельно. Можно сначала посчитать, сколько кодов не найдено, и сверить их со справочником. Лишь после этого решать, как отображать статус в готовом отчёте: текстом, отдельным столбцом или списком для ответственного сотрудника.

Особенно внимательно относитесь к подстановке нуля. Для цены ноль может выглядеть как настоящее значение. В итоге заказ попадёт в итоговую сумму как бесплатный, хотя цена просто не была найдена. Пометка «Проверить код» оставляет проблему заметной и не придаёт ей выдуманного числового значения.

Что произойдёт, если код повторяется в справочнике

Добавьте в конец справочника ещё одну строку A101 с другой ценой и расширьте диапазон поиска. ВПР по точному совпадению вернёт значение из первого найденного совпадения. Она не определяет, какая из двух цен актуальная, и не объединяет их в один осмысленный ответ.

Поэтому перед рабочим использованием нужно понять устройство ключа. Возможно, код действительно должен быть уникальным. А возможно, цена зависит ещё и от даты, склада или типа покупателя. Во втором случае поиск только по коду не описывает задачу целиком.

Для учебной проверки можно составить список повторов и обсудить, почему они появились. Если у товара несколько цен по датам, сначала нужно выбрать нужную дату или подготовить справочник под конкретный период. Удаление повторов без этого решения способно оставить случайную запись.

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

Когда одной ВПР становится неудобно

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

Если искать нужно в другом направлении, пригодятся другие способы поиска, доступные в вашей версии Excel. Если регулярно объединяются большие выгрузки из нескольких файлов, стоит изучить Power Query. Сравнивать инструменты полезно по рабочей задаче: единичный поиск и повторяемая подготовка данных требуют разного объёма настройки.

В материале об Excel для бухгалтера рассмотрено, как такие операции входят в общую работу с выгрузками и сверками. Наш пример можно использовать как отдельную тренировку перед переходом к более сложному отчёту.

Как проверить, что вы освоили функцию

Возьмите новый справочник из пяти товаров и список из десяти заказов. Намеренно добавьте отсутствующий код, лишний пробел и повтор в справочнике. Сначала получите результаты, затем объясните поведение каждой проблемной строки. Цель упражнения — не только добиться отсутствия ошибок на экране.

Попробуйте вслух прочитать формулу, показать фиксированный диапазон и объяснить номер возвращаемого столбца. Затем поменяйте цену одного товара в источнике и проверьте, какие строки заказа изменились. Это поможет увидеть связь между исходными данными и расчётом.

Если вы хотите уверенно использовать функции в регулярных сверках, изучите программу «Excel и Google-таблицы для бухгалтеров». Сопоставьте её с задачами своего отдела и уточните формат обратной связи по работам. Проверка собственных ошибок будет особенно полезна там, где неверный поиск незаметно меняет итоговые суммы.