Функция ВПР в Excel

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

• Пошаговое объяснение аргументов
• Синтаксис и аргументы
• Примеры использования ВПР (VLOOKUP)
  Точное и приблизительное совпадение|ВПР (VLOOKUP) с несколькими условиями
• Распространённые ошибки ВПР (VLOOKUP) и их решения
  Ошибка #Н/Д|Ошибка #ЗНАЧ!|Ошибка #ССЫЛ!|Неверное значение

Примеры ВПР.xlsx Ошибки ВПР.xlsx

Связанные видео

Пошаговое объяснение аргументов

Как видно на приведённом выше снимке экрана, функция ВПР используется для поиска электронной почты по заданному идентификатору. Сейчас я подробно разберу, как применять ВПР в этом примере, шаг за шагом объяснив каждый аргумент.

Шаг 1: Начните функцию ВПР

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

=VLOOKUP(

Шаг 2: Укажите искомое значение

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

=VLOOKUP(G6

Шаг 3: Укажите массив таблицы

Далее укажите диапазон ячеек, включающий как искомое значение, так и то, которое нужно вернуть. В данном случае я выбираю диапазон B6:E12. Теперь формула выглядит так:

=VLOOKUP(G6,B6:E12

=VLOOKUP(G6,$B$6:$E$12

Шаг 4: Укажите столбец, из которого нужно вернуть значение

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

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

=VLOOKUP(G6,B6:E12,4

Шаг 5: Найдите приблизительное или точное совпадение

Наконец, укажите, нужно ли вам приблизительное или точное совпадение.

• Чтобы найти точное совпадение, необходимо использовать ЛОЖЬ в качестве последнего аргумента.
• Чтобы найти приблизительное совпадение, укажите ИСТИНА в качестве последнего аргумента или просто оставьте его пустым.

В этом примере я использую ЛОЖЬ для точного совпадения. Теперь формула выглядит следующим образом:

=VLOOKUP(G6,B6:E12,4,FALSE

Нажмите клавишу Enter, чтобы получить результат

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

Синтаксис и аргументы

=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])

• Искомое_значение(обязательно): значение (число или)ссылка на ячейку), которое вы ищете. Обратите внимание: это значение должно находиться в первом столбце диапазона_таблицы.
• Диапазон_таблицы (обязательно): диапазон ячеек, содержащий как столбец с искомым значением, так и столбец со возвращаемым значением.
• Номер столбца (обязательно): целое число, указывающее номер столбца, содержащего возвращаемое значение. Отсчёт начинается с 1 для крайнего левого столбца диапазона таблицы.
• Интервальный_поиск(необязательно): логическое значение, определяющее, должен ли ВПР (VLOOKUP) находить приблизительное или точное совпадение.
  • Приблизительное совпадение — установите этот аргумент в значение ИСТИНА, 1 или оставьте его пустым.
    Важно: чтобы найти приблизительное совпадение, значения в первом столбце массива_таблицы должны быть отсортированы по возрастанию, иначе ВПР может вернуть неверный результат.
  • Точное совпадение — установите этот аргумент в значение ЛОЖЬ или 0.

Примеры

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

Пример 1: Точное и приблизительное совпадение в ВПР

Если вы испытываете затруднения с пониманием разницы между точным и приблизительным совпадением при использовании ВПР, этот раздел поможет вам разобраться.

Точное совпадение в ВПР

В этом примере я ищу имена, соответствующие баллам из диапазона E6:E8, поэтому ввожу следующую формулу в ячейку F6 и перетаскиваю маркер автозаполнения до F8. В этой формуле последний аргумент задан как ЛОЖЬ, чтобы обеспечить поиск точного совпадения.

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

Однако, поскольку балл 98 отсутствует в первом столбце Диапазон данных, ВПР возвращает ошибку #Н/Д.

Приблизительное совпадение в ВПР

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

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

Поскольку балл 98 отсутствует, ВПР находит наибольшее значение, меньшее 98, — то есть 95, — и возвращает имя, соответствующее этому баллу, как наиболее близкий результат.

Пример 2: Использование ВПР с несколькими критериями

В этом разделе показано, как использовать ВПР с несколькими условиями в Excel. Как видно на приведённом ниже снимке экрана, если вы пытаетесь найти зарплату по имени (в ячейке H5) и отделу (в ячейке H6), следуйте приведённым ниже шагам.

Шаг 1: Добавьте вспомогательный столбец для объединения значений из столбцов поиска

В данном случае необходимо создать вспомогательный столбец для объединения значений из столбца Имя и столбца Отдел.

1. Добавьте вспомогательный столбец слева от вашего Диапазон данных и задайте заголовок этому столбцу. См. снимок экрана:
   
   
2. В этом вспомогательном столбце выберите первую ячейку под заголовком, введите следующую формулу в Строку формул и нажмите Enter.
   

   =C6&," "&,D6

   
   Примечания: в этой формуле мы используем амперсанд (&,) для объединения текста из двух столбцов в одну строку.

   • C6 — это имя столбца Имя, используемого для объединения, а D6 — первый отдел из столбца Отдел, используемого для объединения.
   • Значения этих двух ячеек объединяются с пробелом между ними.
3. Выберите ячейку с результатом, затем перетащите маркер автозаполнения вниз, чтобы применить формулу ко всем остальным ячейкам в этом столбце.
   
   

Шаг 2: Примените функцию ВПР с заданными критериями

Выберите ячейку для вывода результата (например, I7), введите следующую формулу в Строку формул и нажмите Enter.

=VLOOKUP(I5&, " "&,I6,B6:F12,5,FALSE)

Результат

Распространённые ошибки ВПР и их решения

В этом разделе приведены типичные ошибки при работе с ВПР и способы их устранения.

Возвращается ошибка #Н/Д

Наиболее распространённая ошибка ВПР — это ошибка #Н/Д, означающая, что Excel не смог найти искомое значение. Ниже приведены некоторые причины, по которым ВПР может вернуть ошибку #Н/Д.

Причина 1: Искомое значение отсутствует в первом столбце массива_таблицы

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

Как показано на снимке экрана ниже, я хочу найти имя по указанной должности. Искомое значение ()менеджер по продажам) находится во втором столбце таблицы-массива, а возвращаемое значение расположено слева от столбца поиска — поэтому функция ВПР выдаёт ошибку #Н/Д.

Решения

Вы можете устранить эту ошибку, применив любое из следующих решений.

• Измените порядок столбцов
  
  Вы можете изменить порядок столбцов, чтобы столбец поиска стал первым в диапазоне_таблицы.
• Используйте функции ИНДЕКС и ПОИСКПОЗ вместе
  
  Здесь мы используем функции ИНДЕКС и ПОИСКПОЗ в связке как альтернативу ВПР (VLOOKUP) для решения этой задачи.

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

  
• Используйте функцию ПРОСМОТРХ (XLOOKUP) (доступна в Excel 365, Excel 2021 и более поздних версиях)
  

  =XLOOKUP(F6,C6:C12,B6:B12)

Причина 2: The lookup value is not found in the lookup column (exact match)

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

Как показано в приведённом ниже примере, мы пытаемся найти имя по заданному баллу 98 в ячейке E6. Однако этот балл отсутствует в первом столбце Диапазон данных, поэтому ВПР возвращает ошибку #Н/Д.

Решения

Чтобы устранить эту ошибку, попробуйте одно из следующих решений.

• Если вы хотите, чтобы ВПР (VLOOKUP) искал наибольшее значение, меньшее искомого, измените последний аргумент ЛОЖЬ (точное совпадение) на ИСТИНА (приблизительное совпадение). Подробнее — в Пример 1: Точное и приблизительное совпадение с помощью ВПР (VLOOKUP).
• Чтобы избежать изменения последнего аргумента и получать напоминание, если искомое значение не найдено, заключите функцию ВПР (VLOOKUP) в функцию ЕСЛИОШИБКА (IFERROR):
  

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

Причина 3: The lookup value is smaller than the smallest value in the lookup column (approximate match)

Как видно на приведённом ниже снимке экрана, выполняется поиск приблизительного совпадения. Поскольку искомое значение (в данном случае — идентификатор 1001) меньше наименьшего значения в столбце поиска (1002), функция ВПР возвращает ошибку #Н/Д.

Решения

Вот два решения для вас.

• Убедитесь, что искомое значение Значение больше или равно является наименьшим в столбце поиска.
• Если вы хотите, чтобы Excel напоминал вам об отсутствии искомого значения, просто поместите функцию ВПР (VLOOKUP) внутрь функции ЕСЛИОШИБКА (IFERROR), как показано ниже:
  

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

Причина 4: Числа отформатированы как текст

Как видно на приведённом ниже снимке экрана, ошибка #Н/Д в данном примере вызвана несоответствием типов данных между ячейкой поиска (G6) и and the lookup column (B6:B12) исходной таблицы. Здесь значение в G6 является числом, а значения в диапазоне B6:B12 — числами, отформатированными как текст.

Решения

Чтобы решить эту проблему, необходимо преобразовать искомое значение обратно в число. Вот два метода для этого.

• Примените функцию «Преобразовать в число»
  
  Щёлкните ячейку, которую нужно преобразовать из Текст в число, выберите эту кнопку рядом с ячейкой и затем выберите Преобразовать в число.
  
• Примените удобный инструмент для пакетного преобразования Преобразование между текстом и числом
  
  Функция Преобразование между текстом и числомиз Kutools для Excelпозволяет легко преобразовать диапазон ячеек из Текст в число и обратно.Получите 30-дневную полнофункциональную пробную версию прямо сейчас!

Причина 5: Массив_таблицы не является константным при перетаскивании формулы ВПР в другие ячейки

Как показано на скриншоте ниже, в ячейках E6 и E7 находятся два Диапазон значений поиска. После получения первого результата в F6 перетащите формулу ВПР из ячейки F6 в F7 — в результате вернётся ошибка #Н/Д. Это происходит потому, что ссылки на ячейки (B6:C12) по умолчанию являются относительными и автоматически изменяются при перемещении по строкам вниз. Диапазон таблицы смещается вниз до B7:C13, где уже отсутствует искомое значение 73.

Решение

Чтобы диапазон таблицы оставался неизменным, зафиксируйте его, добавив знак $ перед номерами строк и столбцов в ссылках на ячейки. Чтобы узнать больше об абсолютных ссылках в Excel, ознакомьтесь с этим руководством: Абсолютные ссылки в Excel (как создавать и использовать).

Возвращается ошибка #ЗНАЧ!

Следующие условия могут привести к тому, что функция ВПР вернёт ошибку #ЗНАЧ!

Причина 1: Искомое значение превышает 255 символов

Как показано на скриншоте ниже, искомое значение в ячейке H4 превышает 255 символов, поэтому функция ВПР возвращает ошибку #ЗНАЧ!

Решения

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

• ИНДЕКС и ПОИСКПОЗ:
  

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))

  
• Функция ПРОСМОТРХ (XLOOKUP)(доступна в Excel 365, Excel 2021 и более поздних версиях):
  

  =XLOOKUP(H4,B5:B11,E5:E11)

Причина 2: Аргумент «номер_столбца» меньше 1

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

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

Решение

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

Возвращается ошибка #ССЫЛ!

В этом разделе описана одна из причин, по которой функция ВПР возвращает ошибку #ССЫЛ!, а также предложены решения этой проблемы.

Причина: Аргумент «номер_столбца» превышает количество столбцов

Как видно на скриншоте ниже, диапазон таблицы содержит всего 4 столбца. Однако номер столбца, указанный в формуле ВПР, равен 5, что превышает количество столбцов в диапазоне таблицы. В результате функция ВПР не может найти нужный столбец и возвращает ошибку #ССЫЛ!

Решения

• Укажите правильный номер столбца
  Убедитесь, что аргумент номера столбца в вашей формуле ВПР (VLOOKUP) соответствует допустимому столбцу в диапазоне таблицы.
• Автоматически получайте номер столбца по указанному заголовку
  ПОИСКПОЗ

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  Примечание: в приведённой выше формуле функция MATCH(«Email»,B5:E5, 0) используется для определения номера столбца «Email» в диапазоне B5:E5. В данном случае результат равен 4, и это значение подставляется в качестве аргумента номер_столбца функции ВПР (VLOOKUP).

Возвращается неверное значение

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

Причина 1: Столбец поиска не отсортирован по возрастанию

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

Решение

Сортировка столбца поиска по возрастанию поможет решить эту проблему. Чтобы сделать это, выполните следующие действия:

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

Причина 2: Столбец был вставлен или удалён

Как показано на скриншоте ниже, искомое значение изначально находилось в четвёртом столбце диапазона таблицы, поэтому я указал номер столбца как 4. Однако после вставки нового столбца нужный столбец стал пятым в диапазоне, из-за чего функция ВПР вернула результат из неправильного столбца.

Решения

Вот два решения для вас.

• Вы можете вручную изменить номер столбца, чтобы он соответствовал позиции Столбец для возврата. Формулу здесь следует изменить на:

  =VLOOKUP(H6,B6:F12,5,FALSE)

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

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

Примечания по другим функциям

• Функция ВПР (VLOOKUP) ищет значения только слева направо.
  Искомое значение должно находиться в крайнем левом столбце, а результат — в любом столбце правее него.
• Если вы оставите последний аргумент пустым, ВПР (VLOOKUP) по умолчанию будет использовать приблизительное совпадение.
• ВПР (VLOOKUP) выполняет поиск без учёта регистра.
• При наличии нескольких совпадений ВПР (VLOOKUP) возвращает только первое найденное совпадение в диапазоне таблицы в соответствии с порядком строк.

Связанные статьи