Функция ВПР

Совет: Попробуйте использовать новую функцию ПРОСМОТРX , улучшенную версию функции В ПРОСМОТР, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что упрощает и удобнее использовать предшественницу.

Используйте В ПРОСМОТР, если вам нужно найти что-то в таблице или диапазоне по строкам. Например, можно найти цену автомобильной части по ее номеру или имя сотрудника на основе его ИД.

Совет: Ознакомьтесь с этими видео с YouTube от Microsoft Creators для получения дополнительной справки по В ПРОСМОТР!

Самая простая функция ВПР означает следующее:

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

Браузер не поддерживает видео.

Совет: Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (Фрукт) отображалось слева от возвращаемого значения, которое нужно найти (Количество).

Используйте функцию ВПР для поиска значения в таблице.

Синтаксис

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

Например:

  • =В ПРОСМОТР(A2;A10:C20;2;ИСТИНА)

  • =ВПР("Иванов";B2:E7;2;ЛОЖЬ)

  • =В ПРОСМОТР(A2;'Сведения о клиенте'! A:F;3;ЛОЖЬ)

Имя аргумента

Описание

искомое_значение    (обязательный)

Значение для поиска. И look up value must be in the first column of the range of cells you specify in the table_array argument.

Например, если массив таблицы охватывает ячейки B2:D7, lookup_value должен быть в столбце B.

Искомое_значение может являться значением или ссылкой на ячейку.

таблица    (обязательный)

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

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

Узнайте, как выбирать диапазоны на листе .

номер_столбца    (обязательный)

Номер столбца (начиная с 1 для левого table_array),содержащий возвращаемого значения.

интервальный_просмотр    (необязательный)

Логическое значение, определяющее, какое совпадение должна найти функция ВПР, — приблизительное или точное.

  • Приблизительное совпадение— 1 и ИСТИНА предполагает, что первый столбец в таблице отсортировали по алфавиту или по числу, а затем ищут ближайшее значение. Это способ по умолчанию, если не указан другой. Например, =В ПРОСМОТР(90;A1:B100;2;ИСТИНА).

  • Точное совпадение — 0 и ЛОЖЬ ищет точное значение в первом столбце. Например, =В ПРОСМОТР("Кузнецов";A1:B100;2;ЛОЖЬ).

Начало работы

Для построения синтаксиса функции ВПР вам потребуется следующая информация:

  1. Значение, которое вам нужно найти, то есть искомое значение.

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

  3. Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона указан диапазон B2:D11, следует считать B первым столбцом, C — вторым и так далее.

  4. При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.

Теперь объедините все перечисленное выше аргументы следующим образом:

=В ПРОСМОТР(искомого значения; диапазон, содержащий искомые значения, номер столбца в диапазоне, содержащего возвращаемую величину, приблизительное совпадение (ИСТИНА) или Точное совпадение (ЛОЖЬ).)

Примеры

Вот несколько примеров использования функции ВПР.

Пример 1

Пример 1 функции ВПР

Пример 2

Пример 2 функции ВПР

Пример 3

Пример 3 функции ВПР

Пример 4

Пример 4 функции ВПР

Пример 5

Пример 5 функции ВПР

С помощью функции ВЛОП можно объединить несколько таблиц в одну, если одна из них имеет поля, общие с остальными. Это особенно полезно, если вам нужно поделиться книгой с людьми, у которых есть более старые версии Excel, которые не поддерживают функции данных с несколькими таблицами в качестве источников данных. Для этого нужно объединить источники в одну таблицу и изменить источник данных на новую таблицу. Функцию данных можно использовать в более старых версиях Excel (при условии, что сами функции данных поддерживаются более старой версией).

A worksheet with columns that use VLOOKUP to get data from other tables

В этих столбцах A–F и H используются значения или формулы, в которые используются только значения на этом сайте, а в остальных столбцах используется В., а для получения данных из других таблиц используются значения В., а также столбцы A (код клиента) и B (юрист).

  1. Скопируйте таблицу с общими полями на новый и придай ей имя.

  2. Нажмите кнопку ">">"Отношения", чтобы открыть диалоговое окно "Управление связями".

    Диалоговое окно "Управление связями"
  3. Для каждой связи в списке обратите внимание на следующее:

    • Поле, которое связывает таблицы (перечисленные в скобки в диалоговом окне). Это первый lookup_value для формулы ВЛОП.

    • Имя связанной таблицы подпапок. Это и есть table_array в формуле В ПРОСМОТР.

    • Поле (столбец) связанной таблицы подытов, данные из нового столбца в который вы хотите ввести. В диалоговом окте "Управление связями" эти сведения не отбираются: чтобы узнать, какое поле нужно извлечь, нужно будет посмотреть в связанной таблице подзаметки. Обратите внимание на номер столбца (A=1), который является col_index_num формуле.

  4. Чтобы добавить поле в новую таблицу, введите формулу в первом пустом столбце, используя сведения, собранные на шаге 3.

    В нашем примере в столбце G для получения данных "Ставка счета" lookup_valueиз четвертого столбца(col_index_num = 4) из таблицы "Юристы" используется таблица tblAttorneys (the table_array)с формулой =В ПРОСМОТР([@Attorney];tbl_Attorneys;4;ЛОЖЬ).

    В формуле также можно использовать ссылку на ячейку и ссылку на диапазон. В нашем примере это будет =В ПРОСМОТР(A2;'Юристы'! A:D,4,FALSE).

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

Проблема

Возможная причина

Неправильное возвращаемое значение

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

#Н/Д в ячейке

  • Если аргумент интервальный_просмотр имеет значение ИСТИНА, а значение аргумента искомое_значение меньше, чем наименьшее значение в первом столбце таблицы, будет возвращено значение ошибки #Н/Д.

  • Если аргумент интервальный_просмотр имеет значение ЛОЖЬ, значение ошибки #Н/Д означает, что найти точное число не удалось.

Дополнительные сведения об устранении ошибок #Н/Д в функции ВПР см. в статье Исправление ошибки #Н/Д в функции ВПР.

#ССЫЛКА! в ячейке

Если значение аргумента номер_столбца больше, чем число столбцов в таблице, появится значение ошибки #ССЫЛКА!.

Дополнительные сведения об устранении ошибок #ССЫЛКА! в функции ВПР см. в статье Исправление ошибки #ССЫЛКА!.

#ЗНАЧ! в ячейке

Если значение аргумента таблица меньше 1, появится значение ошибки #ЗНАЧ!.

Дополнительные сведения об устранении ошибок #ЗНАЧ! в функции ВПР см. в статье Исправление ошибки #ЗНАЧ! в функции ВПР.

#ИМЯ? в ячейке

Значение ошибки #ИМЯ? чаще всего появляется, если в формуле пропущены кавычки. Во время поиска имени сотрудника убедитесь, что имя в формуле взято в кавычки. Например, в функции =ВПР("Иванов";B2:E7;2;ЛОЖЬ) имя необходимо указать в формате "Иванов" и никак иначе.

Дополнительные сведения см. в статье Исправление ошибки #ИМЯ?.

Ошибки #ПЕРЕНОС! в ячейке

Эта конкретная #SPILL! обычно означает, что формула использует неявное пересечение для искомого значения и использует в качестве ссылки весь столбец. Например, =В ПРОСМОТР(A:A;A:C;2;ЛОЖЬ). Эту проблему можно устранить, привязав ссылку подыска к оператору@: =В ПРОСМОТР(@A:A;A:C;2;ЛОЖЬ). Кроме того, вы можете использовать традиционный метод В ПРОСМОТР и ссылаться на одну ячейку, а не весь столбец: =В.(A2;A:C;2;ЛОЖЬ).

Действие

Примечания

Используйте абсолютные ссылки в аргументе интервальный_просмотр

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

Узнайте, как использовать абсолютные ссылки на ячейки.

Не сохраняйте числовые значения или значения дат как текст.

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

Сортируйте первый столбец

Если для аргумента интервальный_просмотр указано значение ИСТИНА, прежде чем использовать функцию ВПР, отсортируйте первый столбец таблицы.

Используйте подстановочные знаки

Если значение аргумента интервальный_просмотр — ЛОЖЬ, а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед искомым символом.

Например, =В. ("Иван?";B2:E7;2;ЛОЖЬ) будет искать все экземпляры Шрифтана с одной из букв, которые могут отличаться.

Убедитесь, что данные не содержат ошибочных символов.

При поиске текстовых значений в первом столбце убедитесь, что данные в нем не содержат начальных или конечных пробелов, недопустимых прямых (' или ") и изогнутых (‘ или “) кавычек либо непечатаемых символов. В этих случаях функция ВПР может возвращать непредвиденное значение.

Для получения точных результатов попробуйте воспользоваться функциями ПЕЧСИМВ или СЖПРОБЕЛЫ.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Краткий справочник. Обновление ВРОТ
Краткий справочник. Советы по устранению неполадок с ВРОТ
YouTube: видео с вносями вookup от Microsoft Creators
Исправление ошибки #VALUE! в функции ВЛОП
Исправление ошибки #Н/Д в функции ВПР
Общие сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул
Поиск ошибок в формулах
Функции Excel (по алфавиту)
Функции Excel (по категориям)
Функция ВПР (бесплатное ознакомительное видео)

Нужна дополнительная помощь?

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×