Как сделать формулу ВПР в Excel: ищем место хранения костюма по коду. Разбираем точное совпадение, диапазон, номер столбца, закрепление ссылок, дубли и ошибки.
Д
Денис
Продакт LibraChat
8 мин чтения
«Плащ нашёлся. А где он хранится?» Мне нравится этот вопрос: он отделяет название вещи от значения, которое действительно нужно получить из справочника. Разберём, как сделать формулу ВПР для учебного списка костюмов. Вводим код, например K003, и возвращаем место хранения из той же строки. Нужен именно этот код, поэтому режим точного совпадения укажем явно.
ВПР ищет значение в первом столбце заданного диапазона, затем берёт результат из выбранного столбца найденной строки. Номер этого столбца считается внутри диапазона. Формуле нужно сообщить четыре вещи: что искать, где искать, откуда вернуть результат и какое совпадение использовать.
Как сделать формулу ВПР на небольшом справочнике
Четыре записи ещё можно просмотреть целиком: для каждой я вижу код, название и место хранения. Это условные данные для упражнения, не действующий учёт театрального гардероба. Коды обозначают отдельные экземпляры: одному коду соответствует одно место хранения.
Размести таблицу в A1:C5: заголовки в первой строке, четыре записи ниже.
В E1 напиши «Код для поиска», в F1 — «Место хранения». В E2 введи K003. Для результата используй F2, чтобы исходный справочник и поле запроса оставались отдельными участками листа.
```excel
=ВПР(E2;$A$2:$C$5;3;ЛОЖЬ)
```
После подтверждения ввода ожидается «Стойка A, секция 3». Проверка не требует доверять формуле на слово: найди K003 в первом столбце справочника и прочитай третью ячейку этой строки. Там находится нужное место, а название «Фартук» стоит во второй.
Разберём запись по частям:
Аргумент · В нашем примере · Зачем он нужен
Искомое значение — E2 — Код, который вводится для поиска
Таблица — $A$2:$C$5 — Диапазон с кодом слева и возвращаемыми данными справа
Номер столбца — 3 — Место хранения — третий столбец выбранного диапазона
Интервальный просмотр — ЛОЖЬ — Поиск точного совпадения
С аргументом 2 из той же строки вернётся название костюма, с аргументом 3 — место хранения. Я ищу место, поэтому правильная найденная строка сама по себе меня ещё не устраивает: важен столбец, из которого взят ответ.
В английской версии функция называется VLOOKUP. Та же запись с английскими именами и запятыми: `=VLOOKUP(E2,$A$2:$C$5,3,FALSE)`. Разделитель аргументов зависит от региональных настроек. Если твой Excel использует точку с запятой, сохрани её; имя функции и разделитель проверяй отдельно.
Что закрепить перед копированием и изменением таблицы
В исходной формуле E2 оставлена относительной ссылкой, а диапазон закреплён долларами. При копировании F2 вниз в F3 поиск должен брать код из E3 и продолжать просматривать тот же справочник A2:C5.
Я читаю скопированную формулу буквально: «Код следующего запроса, прежние четыре записи». Если снять закрепление, границы поиска могут сдвинуться вместе с формулой. Верхняя запись исчезнет из области поиска, а снизу попадёт лишняя строка. Это особенно неприятно, когда первые запросы случайно продолжают давать верные ответы.
Для контрольной пары введи в E3 код K001 и скопируй формулу из F2 в F3. В F3 ожидается «Стойка A, секция 2». Сверь строку формул: там должно быть `=ВПР(E3;$A$2:$C$5;3;ЛОЖЬ)`. Меняется адрес запроса, не адрес справочника.
Номер 3 не означает «колонка C на любом листе». Если перенести справочник в H:J, его третьим столбцом станет J, а первым — H. Правило отсчёта сохранится. В выбранном диапазоне код обязан находиться слева от возвращаемого значения: обычная ВПР не ищет код в произвольной колонке внутри таблицы.
Перед перестройкой справочника сохрани эту памятку:
добавилась запись ниже последней строки — проверь, входит ли она в диапазон поиска;
появились новые столбцы — пересчитай номер возвращаемого столбца внутри диапазона;
таблица переехала — проверь левую границу и закрепление адресов;
изменилось назначение кода — уточни, остаётся ли он уникальным идентификатором экземпляра.
Доллары защищают от сдвига при копировании, но сами по себе не превращают A2:C5 в автоматически расширяющийся справочник. Если дописать новый костюм за пределами диапазона, формула не обязана его увидеть. При расширении справочника я обращаюсь к новой записи: именно она покажет, не осталась ли граница поиска прежней. Затем беру одну из старых — для неё поиск должен сохранить прежний результат.
Почему точное совпадение не решает все ошибки
Последний аргумент лучше не оставлять пустым
Для нашего справочника используется ЛОЖЬ, или 0. Если четвёртый аргумент пропустить, ВПР по умолчанию применяет приблизительное совпадение. Этот режим связан с сортировкой первого столбца и подходит другим задачам. При поиске конкретного костюма похожий соседний код не заменяет нужный экземпляр.
При ЛОЖЬ предварительная сортировка кодов не требуется. Если K009 в диапазоне нет, ожидается ошибка #Н/Д, а не адрес ближайшего костюма. Я не маскирую её сразу пустой строкой: сначала выясняю, отсутствует ли запись или неверно устроен поиск.
Одинаковый код в двух строках оставляет неоднозначность
При точном поиске ВПР возвращает первое найденное совпадение. Если K003 встречается дважды с разными местами, формула не выдаст список обоих адресов и не выберет «самый свежий». Найденный текст может выглядеть вполне убедительно, хотя исходные данные противоречат друг другу.
Первое совпадение ещё не говорит мне, сколько раз встречается код. Поэтому я задаю справочнику другой вопрос: сколько строк ему соответствует? Можно посчитать их формулой `=СЧЁТЕСЛИ($A$2:$A$5;E2)`. Для исходного K003 ответ равен 1. Ноль означает отсутствие кода в этой области, число больше единицы — необходимость разобраться с дублями.
Не удаляй повтор автоматически лишь потому, что он мешает формуле. Возможно, один код ошибочно присвоили двум экземплярам, а возможно, в таблице хранят историю перемещений. Наш короткий справочник хранит текущее место одного экземпляра; история потребует другой структуры исходных данных.
Проверь тип и написание значения
В учебном примере K001–K004 — текстовые коды. Если в другом справочнике используются только цифры, число и текст с похожим видом могут не совпасть при поиске. Сначала проверь типы обеих сторон, а затем приводи данные к принятому формату. Простое изменение оформления ячейки не всегда преобразует уже сохранённое значение.
Для диагностики пригодны `ЕЧИСЛО` и `ЕТЕКСТ`: они проверяют тип, не превращая текст в число. Если ведущие нули являются частью идентификатора, не удаляй их ради числового формата. Код «001» и номер 1 могут обозначать разные вещи по правилам учёта.
Также посмотри, нет ли пробела перед кодом или после него. Латинская K и похожая кириллическая К — разные символы. При этом регистр латинских букв ВПР не различает. В нашем справочнике принят единый вид K001, чтобы человек тоже мог быстро сверить запись.
Знаки `*` и `?` в текстовом поиске имеют значение подстановочных символов даже при ЛОЖЬ. В выбранных кодах их нет; для поиска буквально таких знаков требуется специальная запись с `~`. Не добавляй звёздочку для «починки» отсутствующего кода, если нужен конкретный экземпляр.
Как проверить готовую формулу перед выдачей списка
Для этой задачи я использую короткий приёмочный набор:
K003 возвращает место фартука, а не его название.
K001 после копирования вниз остаётся доступным в закреплённом справочнике.
Отсутствующий K009 не подменяется соседним кодом.
Каждый код встречается ровно один раз в принятом текущем справочнике.
Если Excel показывает #ССЫЛКА!, проверь, не больше ли номер возвращаемого столбца количества столбцов диапазона. Для A2:C5 допустимы номера 1, 2 и 3. Если видишь #Н/Д, проверь наличие кода, диапазон, тип и лишние символы. Эти причины требуют разных исправлений.
Открой чат LibraChat, если нужно разобрать свою запись по аргументам:
Учебный справочник A2:C5 содержит код костюма, название и место хранения. Код запроса в E2. Формула =ВПР(E2;$A$2:$C$5;3;ЛОЖЬ). Проверь смысл каждого аргумента и предложи контрольные запросы. Если данных для вывода недостаточно, задай вопрос о диапазоне или дублях.
Выбери в своём справочнике одну известную запись и одну заведомо отсутствующую. Проверь обе до копирования формулы на весь список: результат первой должен совпасть с нужной колонкой, вторая не должна получить чужое место хранения.
