Функция ВПР

Чтобы найти данные в таблице или диапазоне по строкам, воспользуйтесь функцией ВПР, одной из функций поиска и работы со ссылками. Например, можно найти фамилию сотрудника по его номеру или его номер телефона по фамилии (как в телефонной книге).

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

Синтаксис

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

Например:

  • =ВПР(105,A2:C7,2,ИСТИНА)

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

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

Описание

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

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

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

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

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

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

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

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

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

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

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

  • Функция ИСТИНА предполагает, что первый столбец в таблице отсортирован в алфавитном порядке или по номерам, а затем выполняет поиск ближайшего значения. Это способ по умолчанию, если не указан другой.

  • Функция ЛОЖЬ осуществляет поиск точного значения в первом столбце.

На изображении ниже показано, как настроить функцию =VLOOKUP("Akers",B2:D5,2,FALSE) на листе, чтобы в результате возвращалось имя Kim.

Пример значения и массива, необходимых для создания формулы с функцией ВПР в Excel

Примеры

Чтобы использовать эти примеры в Excel, скопируйте данные из приведенной ниже таблицы и вставьте их на новый лист в ячейку A1.

ИД

Фамилия

Имя

Должность

Дата рождения

101

Зайцев

Алексей

Торговый представитель

08.12.1968

102

Иванов

Валерий

Вице-президент по продажам

19.02.1952

103

Сазонова

Инна

Торговый представитель

30.08.1663

104

Полякова

Дарья

Торговый представитель

19.09.1958

105

Жданов

Станислав

Менеджер по продажам

04.03.55

106

Кузьмина

Мария

Торговый представитель

02.07.63

Формула

Описание

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

Функция ищет значение Иванов в первом столбце (столбце B) таблицы B2:E7 и возвращает значение Валерий, найденное во втором столбце (столбце C) этой таблицы. Если для аргумента интервальный_просмотр указано значение ЛОЖЬ, функция возвращает точное совпадение.

=ВПР(102,A2:C7,2,ЛОЖЬ)

Функция ищет фамилию (точное совпадение) для аргумента искомое_значение со значением 102 в столбце A и возвращает результат Иванов. Если для аргумента искомое_значение указано значение 105, возвращается значение Жданов.

=ЕСЛИ(ВПР(103; А1:E7;2;ЛОЖЬ)="Кузьмина","Найдено","Не найдено")

Функция проверяет, соответствует ли фамилия сотрудника Кузьмина идентификатору 103. Так как фамилия сотрудницы под номером 103 на самом деле Сазонова, возвращается результат Не найдено. Если вы измените в формуле значение"Кузьмина" на "Сазонова", результат изменится на Найдено.

=ЦЕЛОЕ(ДОЛЯГОДА(ДАТА(2014;6;30); ВПР(105;A2:E7;5; ЛОЖЬ);1))

Функция вычисляет возраст сотрудника с идентификатором 105 для 2014 финансового года. Функция ДОЛЯГОДА используется для вычитания даты рождения из конечной даты финансового года и отображения результата 59 в виде целого числа с помощью функции ЦЕЛОЕ.

=ЕСЛИ(ЕНД(ВПР(105;A2:E7;2;ЛОЖЬ)) = ИСТИНА,"Сотрудник не найден",ВПР(105;A2:E7;2;ЛОЖЬ))

Если сотрудник под номером 105 существует, отображается его фамилия (Жданов). В противном случае отображается сообщение Сотрудник не найден. Функция ЕНД (см. функции Е) возвращает значение ИСТИНА, если функция ВПР возвращает значение ошибки #Н/Д.

=ВПР(104;A2:E7;3;ЛОЖЬ) & " " & ВПР(104;A2:E7;2;ЛОЖЬ) & " — это " & ВПР(104;A2:E7;4;ЛОЖЬ)

Для сотрудника под номером 104 значения трех ячеек объединяются в целое предложение Дарья Полякова — это торговый представитель.

Распространенные неполадки

Проблема

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

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

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

#Н/Д в ячейке

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

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

Узнайте больше об ошибках на листах, таких как #Н/Д, #ССЫЛКА! и др.

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

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

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

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

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

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

Рекомендации

Действие

Результат

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

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

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

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

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

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

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

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

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

Например, с помощью функции =VLOOKUP("Fontan?",B2:E7,2,FALSE) можно выполнить поиск всех случаев употребления фамилии Иванов в различных падежных формах.

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

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

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

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

Применяется к: Excel Starter, Excel for Mac 2011, Excel 2016 for Mac, Excel 2010, Excel 2013, Excel 2016 Preview, Excel 2007, Excel Online



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

Да Нет

Что мы могли бы улучшить?

255 осталось символов

Чтобы защитить вашу конфиденциальность, не указывайте контактные данные в своем отзыве. Обзор наших политика конфиденциальности.

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

Ресурсы поддержки

Изменить язык