Совет
Попробуйте использовать новую функцию ПРОСМОТРX, улучшенную версию функции ВПР, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что делает ее проще и удобнее в использовании, чем предшественницу.
Находите данные по строкам в таблице или диапазоне с помощью функции ВПР. Например, можно найти цену автомобильной детали по ее номеру или найти имя сотрудника по его идентификатору.
Самая простая функция ВПР означает следующее:
=ВПР(искомое значение; место для его поиска; номер столбца в диапазоне с возвращаемым значением; возврат приблизительного или точного совпадения — указывается как 1/ИСТИНА или 0/ЛОЖЬ).
Совет
- Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (Фрукт) отображалось слева от возвращаемого значения, которое нужно найти (Количество).
- Если вы являетесь Microsoft Copilot подписчик Copilot может упростить вставку и использование функций VLookup или XLookup. См . статью Copilot, чтобы упростить поиск в Excel.
Технические подробности
Используйте функцию ВПР для поиска значения в таблице.
Синтаксис
ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])
Например:
- =ВПР(A2;A10:C20;2;ИСТИНА)
- =ВПР("Иванов";B2:E7;2;ЛОЖЬ)
- =ВПР(A2;'Сведения о клиенте'! A:F,3,FALSE)
| Имя аргумента | Описание |
|---|---|
| искомое_значение (обязательный) | Значение для поиска. Значение, которое нужно найти, должно находиться в первом столбце диапазона ячеек, указанного в аргументе table_array . Например, если массив таблицы охватывает ячейки B2:D7, то lookup_value должен находиться в столбце B. Lookup_value может быть значением или ссылкой на ячейку. |
| таблица (обязательный) | Диапазон ячеек, в которых ВПР будет искать lookup_value и возвращаемое значение. Вы можете использовать именованный диапазон или таблицу, а также имена в аргументе вместо ссылок на ячейки. Первый столбец в диапазоне ячеек должен содержать lookup_value. Диапазон ячеек также должен содержать возвращаемое значение, которое нужно найти. Узнайте, как выбирать диапазоны на листе . |
| номер_столбца (обязательный) | Номер столбца (начиная с 1 для самого левого столбца table_array), который содержит возвращаемое значение. |
| интервальный_просмотр (необязательный) | Логическое значение, определяющее, какое совпадение должна найти функция ВПР, — приблизительное или точное.
|
Начало работы
Для построения синтаксиса функции ВПР вам потребуется следующая информация:
- Значение, которое вам нужно найти, то есть искомое значение.
- Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
- Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона вы указываете B2:D11, следует считать B первым столбцом, C — вторым и т. д.
- При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.
Теперь объедините все перечисленное выше аргументы следующим образом:
=ВПР(искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением; приблизительное совпадение (ИСТИНА) или точное совпадение (ЛОЖЬ)).
Примеры
Вот несколько примеров использования функции ВПР.
Пример 1
Пример 2
Пример 3
Пример 4
Пример 5
Распространенные неполадки
| Проблема | Возможная причина |
|---|---|
| Неправильное возвращаемое значение | Если range_lookup имеет значение TRUE или отсутствует, первый столбец необходимо отсортировать в алфавитном или числовом порядке. Если первый столбец не отсортирован, возвращаемое значение может быть непредвиденным. Отсортируйте первый столбец или используйте значение ЛОЖЬ для точного соответствия. |
| #Н/Д в ячейке |
|
| #ССЫЛКА! в ячейке | Если col_index_num больше, чем количество столбцов в массиве таблиц, вы получите #REF! значение ошибки #ССЫЛКА!. Дополнительные сведения об устранении ошибок #ССЫЛКА! ошибки во ВПР см. в статье Исправление ошибки #REF!. |
| #ЗНАЧ! в ячейке | Если table_array меньше 1, вы получите #VALUE! значение ошибки #ЗНАЧ!. Дополнительные сведения об устранении ошибок #ЗНАЧ! ошибки во ВПР см. в статье Исправление ошибки #VALUE! в функции ВПР. |
| "#ИМЯ?" в ячейке | #NAME? Значение ошибки обычно означает, что в формуле отсутствуют кавычки. Во время поиска имени сотрудника убедитесь, что имя в формуле взято в кавычки. Например, в функции =ВПР("Иванов";B2:E7;2;ЛОЖЬ) имя необходимо указать в формате "Иванов" и никак иначе. Дополнительные сведения см. в разделе Исправление ошибки #ИМЯ?. |
| Ошибки #ПЕРЕНОС! в ячейке | Эта конкретная ошибка #SPILL! обычно означает, что формула использует неявное пересечение значения поиска и использует весь столбец в качестве ссылки. Например, =ВПР( A:A,A:C,2;FALSE). Вы можете устранить эту проблему, привязав ссылку подстановки с помощью оператора @, например: =ВПР(@A:A;A:C;2;ЛОЖЬ). Кроме того, вы можете использовать традиционный метод ВПР и ссылаться на одну ячейку вместо целого столбца: =ВПР(A2;A:C;2;ЛОЖЬ). |
Рекомендации
| Действия | Результат |
|---|---|
| Использование абсолютных ссылок для range_lookup | Использование абсолютных ссылок позволяет заполнить формулу так, чтобы она всегда отображала один и тот же диапазон точных подстановок. Узнайте, как использовать абсолютные ссылки на ячейки. |
| Не сохраняйте числовые значения или значения дат как текст. | При поиске значений числа или даты убедитесь, что данные в первом столбце table_array не хранятся в виде текстовых значений. Иначе функция ВПР может вернуть неправильное или непредвиденное значение. |
| Сортируйте первый столбец | Отсортируйте первый столбец table_array перед использованием функции ВПР, если range_lookup имеет значение TRUE. |
| Используйте подстановочные знаки | Если range_lookup имеет значение FALSE, а lookup_value — текст, в lookup_value можно использовать подстановочные знаки — вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед искомым символом. Например, с помощью функции =ВПР("Ивано?";B2:E7;2;ЛОЖЬ) будет выполнен поиск всех случаев употребления Иванов с последней буквой, которая может меняться. |
| Убедитесь, что данные не содержат ошибочных символов. | При поиске текстовых значений в первом столбце убедитесь, что данные в первом столбце не имеют начальных пробелов, конечных пробелов, несогласованного использования прямых ( или " ) и фигурных кавычек ( или ") или символов без печати. В этих случаях функция ВПР может возвращать непредвиденное значение. Для получения точных результатов попробуйте воспользоваться функциями ПЕЧСИМВ или СЖПРОБЕЛЫ. |
Дополнительные сведения
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществах.