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

• Пошаговое объяснение аргументов
• Синтаксис и аргументы
• Примеры ВПР
  Точное совпадение против приблизительного совпадения   |   ВПР с несколькими условиями
• ВПР Распространенные ошибки и решения
  # Н / Д ошибка   |   #ЗНАЧ ошибка   |   #REF-ошибка   |   Неверное значение

Примеры ВПР .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. Найдите приблизительное или точное совпадение

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

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

В этом примере я использую FALSE для точного соответствия. Теперь формула выглядит так:

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

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

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

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

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

• Искомое_значение (обязательно): значение (действительное значение или ссылка на ячейку), которое вы ищете. Помните, что это значение должно быть в первом столбце table_array.
• Таблица_массив (обязательно): диапазон ячеек содержит как столбец искомого значения, так и столбец возвращаемого значения.
• Col_index (обязательно): целое число представляет номер столбца, содержащего возвращаемое значение. Он начинается с номера 1 для крайнего левого столбца table_array.
• Диапазон_поиска (необязательно): логическое значение, определяющее, хотите ли вы, чтобы функция ВПР находила приблизительное или точное совпадение.
  • Примерное совпадение - Установите этот аргумент в ИСТИНА, 1 или оставь это пустой.
    Важнo: чтобы найти приблизительное совпадение, значения в первом столбце table_array должны быть отсортированы в порядке возрастания на случай, если функция ВПР вернет неверный результат.
  • Точное совпадение - Установите этот аргумент в НЕПРАВДА or 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, и возвращает название оценки 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: значение поиска не находится в первом столбце table_array

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

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

Решения

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

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

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

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

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

Причина 2: значение поиска не найдено в столбце поиска (точное совпадение)

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

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

Решения

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

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

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

Причина 3: искомое значение меньше наименьшего значения в столбце поиска (приблизительное совпадение)

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

Решения

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

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

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

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

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

Решения

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

• Примените функцию преобразования в число
  
  Нажмите на ячейку, в которой вы хотите преобразовать текст в число, выберите эту кнопку  рядом с ячейкой, а затем выберите Преобразовать в число.
  
• Примените удобный инструмент для пакетного преобразования между текстом и числом
  
  Ассоциация Преобразование текста в число особенность Kutools for Excel помогает вам легко преобразовать диапазон ячеек из текста в число и наоборот. Получите 30-дневную бесплатную пробную версию прямо сейчас!
  

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

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

Решения

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

#VALUE возвращается ошибка

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

Причина 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: аргумент col_index меньше 1

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

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

Решения

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

#REF возвращается ошибка

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

Причина: Аргумент col_index больше, чем количество столбцов.

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

Решения

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

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

  Внимание: В приведенной выше формуле ПОИСКПОЗ("Электронная почта",B5:E5, 0) функция используется для получения номера столбца "Эл. адрес" в диапазоне дат B6:E12. Здесь результат равен 4, который используется в качестве col_index в функции ВПР.

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

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

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

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

Решения

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

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

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

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

Решения

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

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

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

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

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

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

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

Статьи по теме