Вертикальный поиск в Excel

Обновлено: 21.11.2024

ВПР означает "вертикальный поиск". Это функция, которая заставляет Excel искать определенное значение в столбце (так называемый «табличный массив»), чтобы вернуть значение из другого столбца в той же строке. Эта статья научит вас пользоваться функцией ВПР.

О функции ВПР

Функция ВПР состоит из 4 компонентов:

  1. Значение, которое вы хотите найти;
  2. Диапазон, в котором вы хотите найти значение и возвращаемое значение;
  3. Номер столбца в определенном диапазоне, который содержит возвращаемое значение;
  4. 0 или FALSE для точного совпадения с искомым значением; 1 или TRUE для приблизительного совпадения.

Синтаксис: ВПР([значение], [диапазон], [номер столбца], [ложь или истина])

Пример ВПР

В нашем примере у нас есть список фруктов, их количество на складе и текущая цена. Мы хотим быстро найти цену на киви в этой таблице.

Сначала выберите ячейку, в которой вы хотите опубликовать текущую цену:

Итак, в выбранной ячейке мы начинаем вводить: =ВПР( :

Затем мы выбираем значение, которое хотим найти, в данном случае это «Киви» в ячейке B12:

Далее мы вводим ‘;’ и выбираем диапазон (или массив таблиц), в котором мы хотим найти искомое значение и возвращаемое значение. В данном случае это диапазон (A2:C10):

Затем мы вводим ‘;’ и номер столбца в пределах определенного диапазона, в котором мы хотим искать возвращаемое значение. В данном случае это столбец 3, за которым следуют «;» и «0» или «ЛОЖЬ» для точного совпадения со значением поиска «Киви»:

Когда мы нажимаем ввод, мы получаем соответствующую цену из строки, содержащей значение «Киви» в выбранном массиве таблиц:

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

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

ВПР – это функция Excel для поиска данных в таблице, организованной по вертикали. Функция ВПР поддерживает приблизительное и точное совпадение, а также подстановочные знаки (* ?) для частичного совпадения. Значения поиска должны отображаться в первом столбце таблицы, переданной в функцию ВПР.

  • lookup_value – значение, которое нужно искать в первом столбце таблицы.
  • table_array – таблица, из которой нужно получить значение.
  • column_index_num – столбец в таблице, из которого нужно получить значение.
  • range_lookup — [необязательно] TRUE = приблизительное совпадение (по умолчанию). FALSE = точное совпадение.

ВПР – это функция Excel для получения данных из таблицы, организованной по вертикали. Значения поиска должны отображаться в первом столбце таблицы, переданной в функцию ВПР. ВПР поддерживает приблизительное и точное совпадение, а также подстановочные знаки (* ?) для частичного совпадения.

Введение

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

С другой стороны, функция ВПР ограничена и имеет опасные значения по умолчанию. В отличие от ИНДЕКС и ПОИСКПОЗ (или XLOOKUP), для функции ВПР требуется полная таблица со значениями поиска в столбце first. Это затрудняет использование ВПР с несколькими критериями. Кроме того, поведение сопоставления по умолчанию функции ВПР упрощает получение неправильных результатов. Не бойся. Ключом к успешному использованию функции ВПР является освоение основ. Ознакомьтесь с полным обзором.

Аргументы

ВПР принимает четыре аргумента: искомое_значение, массив_таблиц, номер_индекса_столбца и искатель_диапазона. Искомое_значение — это значение для поиска, а table_array — это диапазон вертикальных данных для просмотра. Первый столбец table_array должен содержать значения поиска для поиска. Аргумент column_index_num — это номер столбца значения, которое необходимо получить, где первый столбец table_array — это столбец 1. Наконец, range_lookup управляет поведением сопоставления. . Если range_lookup имеет значение TRUE, функция ВПР выполнит приблизительное совпадение. Если range_lookup имеет значение FALSE, функция ВПР выполнит точное совпадение. Важно: параметр range_lookup является необязательным и по умолчанию имеет значение TRUE, поэтому по умолчанию функция ВПР будет выполнять приблизительное совпадение. Дополнительную информацию о сопоставлении см. ниже.

V – вертикальное

Цель ВПР – поиск информации в таблице, подобной этой:

С номером заказа в столбце B в качестве lookup_value функция ВПР может получить Cust. ID, сумма, имя и состояние для любого заказа. Например, чтобы получить имя для заказа 1004, используйте следующую формулу:

Для поиска горизонтальных данных можно использовать ГПР, ИНДЕКС и ПОИСКПОЗ или КСПР.

ВПР основан на номерах столбцов

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

Изменив только column_index_num, можно искать столбцы 2, 3 и 4:

Примечание: обычно мы используем абсолютную ссылку для H3 ($H$3) и B4:E13 ($B$4:$E$13), чтобы предотвратить их изменение при копировании формулы. Выше ссылки даны относительно, чтобы их было легче читать.

ВПР выглядит правильно

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

Чтобы найти значения слева, см. ИНДЕКС и ПОИСКПОЗ или XLOOKUP.

Режимы соответствия

ВПР имеет два режима сопоставления, точное и приблизительное, управляемые четвертым аргументом, range_lookup. Слово «диапазон» в данном случае относится к «диапазону значений» — когда range_lookup имеет значение TRUE, функция VLOOKUP будет соответствовать диапазону значений, а не точному значению. Хорошим примером этого является использование функции ВПР для подсчета оценок. Когда range_lookup имеет значение FALSE, функция ВПР выполняет точное совпадение, как в приведенном выше примере.

Важно: range_lookup необязательное значение по умолчанию TRUE. Это означает, что режимом по умолчанию является приблизительное совпадение, что может быть опасно. Установите для параметра range_lookup значение FALSE, чтобы обеспечить точное соответствие:

Совет: всегда указывайте значение для range_lookup в качестве напоминания об ожидаемом поведении.

Примечание. Вы также можете указать ноль (0) для точного совпадения и 1 для приблизительного совпадения.

Пример точного соответствия

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

Формула в H6 для поиска года на основе точного совпадения названия фильма:

Пример приблизительного соответствия

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

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

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

Только первое совпадение

В случае дублирования совпадающих значений функция ВПР найдет первое совпадение. На приведенном ниже экране функция ВПР настроена на поиск цены для цвета «Зеленый». Есть три строки с зеленым цветом, и функция ВПР возвращает цену в первой строке, 17 долларов США. Формула в ячейке F5:

Чтобы получить несколько совпадений в операции поиска, см. функцию ФИЛЬТР.

Подстановочный знак

Функция ВПР поддерживает подстановочные знаки, что позволяет выполнять частичное совпадение искомого значения. Например, вы можете использовать ВПР для извлечения информации из таблицы с частичным искомое_значение и подстановочным знаком. Чтобы использовать подстановочные знаки с ВПР, вы должны использовать режим точного совпадения, указав ЛОЖЬ для диапазон поиска. Формула в H7 извлекает имя «Анджелина» после ввода «Ban*» в ячейку H4:

Двусторонний поиск

Внутри функции ВПР номер_индекса_столбца обычно жестко закодирован как статическое число. Однако вы также можете создать индекс динамического столбца, используя функцию ПОИСКПОЗ, чтобы найти нужный столбец. Этот метод позволяет вам создать динамический двусторонний поиск, совпадающий по обеим строкам и столбцам. На приведенном ниже экране функция ВПР настроена на поиск по имени и месяцу. Формула в H6:

Несколько критериев

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

ВПР выполняет те же действия для создания значения поиска. Формула в H6:

Подробнее см. в этом примере. Более продвинутый и гибкий подход см. в этом примере.

Примечание. INDEX, MATCH и XLOOKUP лучше подходят для поиска по нескольким критериям.

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

Чтобы «перехватить» ошибку NA и вернуть другое значение, вы можете использовать функцию IFNA следующим образом:

Формула в H6:

поиск меню

Урок 19. Как использовать функцию ВПР Excel

Как использовать функцию ВПР Excel

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

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

Что такое ВПР?

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

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

Мы добавим нашу формулу в ячейку F2, но вы можете добавить ее в любую пустую ячейку. Как и в любой формуле, вы начнете со знака равенства (=). Затем введите имя формулы. Наши аргументы должны быть заключены в круглые скобки, поэтому введите открывающую круглую скобку. Пока это должно выглядеть так:

=ВПР(

Добавление аргументов

Теперь мы добавим наши аргументы. Аргументы сообщат ВПР, что искать и где искать.

Первый аргумент — это название объекта, который вы ищете, в данном случае это фоторамка. Поскольку аргумент представляет собой текст, нам нужно будет заключить его в двойные кавычки:

=ВПР("Фоторамка"

Второй аргумент — это диапазон ячеек, содержащий данные. В этом примере наши данные находятся в формате A2:B16. Как и в любой функции, вам нужно будет использовать запятую для разделения каждого аргумента:

=ВПР("Фоторамка", A2:B16

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

Третий аргумент — это порядковый номер столбца. Это проще, чем кажется: первый столбец диапазона равен 1, второй столбец — 2 и т. д. В данном случае мы пытаемся найти цену товара, а цены содержатся во втором столбце. Это означает, что наш третий аргумент будет равен 2:

=ВПР("Фоторамка", A2:B16, 2

Четвертый аргумент сообщает функции ВПР, следует ли искать приблизительные совпадения, и может принимать значения ИСТИНА или ЛОЖЬ. Если это TRUE, он будет искать приблизительные совпадения. Как правило, это полезно только в том случае, если в первом столбце есть числовые значения, которые были отсортированы. Поскольку мы ищем только точные совпадения, четвертый аргумент должен быть FALSE. Это наш последний аргумент, поэтому закройте скобки:

=ВПР("Фоторамка", A2:B16, 2, ЛОЖЬ)

Вот оно! Когда вы нажмете Enter, он должен дать вам ответ: 9,99.

Как это работает

Давайте посмотрим, как работает эта формула. Сначала выполняется поиск по вертикали вниз по первому столбцу (VLOOKUP — сокращение от вертикального поиска). Когда он находит «Фоторамка», он переходит ко второму столбцу, чтобы найти цену.

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

Если мы хотим найти цену другого товара, мы можем просто изменить первый аргумент:

=ВПР("Футболка", A2:B16, 2, ЛОЖЬ)

=ВПР("Подарочная корзина", A2:B16, 2, ЛОЖЬ)

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

Еще один пример

Готовы ли вы к чуть более сложному примеру? Мы собираемся внести в таблицу несколько изменений, чтобы сделать ее более реалистичной.

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

Мы также добавили третий столбец с категорией для каждого элемента. Это даст нам возможность найти цену или категорию. Вот как сейчас выглядит таблица:

Наша формула будет похожа на предыдущий пример, но нам нужно изменить первые три аргумента. Начнем с изменения первого аргумента на ссылку на ячейку (не забудьте удалить кавычки):

=ВПР(E2, A2:B16, 2, ЛОЖЬ)

Чтобы найти категорию, нам нужно изменить второй и третий аргументы. Во-первых, мы изменим диапазон на A2:C16, чтобы он включал третий столбец. Далее мы изменим номер индекса столбца на 3, потому что наши категории находятся в третьем столбце:

=ВПР(E2, A2:C16, 3, ЛОЖЬ)

Когда вы нажмете Enter, вы увидите, что подарочная корзина находится в категории «Подарки».

Если мы хотим найти категорию другого элемента, мы можем просто изменить имя элемента в ячейке E2:

Попробуйте это!

Если вам нужно больше практики, посмотрите, сможете ли вы найти следующее:

  • Цена кофейной кружки
  • Категория пейзажной живописи
  • Цена сервировочной тарелки
  • Категория шарфа

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

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

Функция ВПР — одна из самых популярных функций в Excel. Эта страница содержит много простых примеров ВПР.

Точное соответствие

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

<р>1. Приведенная ниже функция ВПР ищет значение 53 (первый аргумент) в крайнем левом столбце красной таблицы (второй аргумент).

<р>2. Значение 4 (третий аргумент) указывает функции ВПР вернуть значение в той же строке из четвертого столбца красной таблицы.

<р>3. Вот еще один пример. Вместо того, чтобы возвращать зарплату, приведенная ниже функция ВПР возвращает фамилию (третьему аргументу присвоено значение 3) идентификатора 79.

Приблизительное соответствие

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

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

<р>2. К счастью, логическое значение ИСТИНА (четвертый аргумент) указывает функции ВПР вернуть приблизительное совпадение. Если функция ВПР не может найти значение 85 в первом столбце, она вернет наибольшее значение меньше 85. В этом примере это будет значение 80.

<р>3. Значение 2 (третий аргумент) указывает функции ВПР вернуть значение в той же строке из второго столбца красной таблицы.

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

Vlookup выглядит правильно

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

<р>1. Например, приведенная ниже функция ВПР ищет имя и возвращает фамилию.

<р>2. Если вы измените порядковый номер столбца (третий аргумент) на 3, функция ВПР ищет имя и возвращает зарплату.

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

Первое совпадение

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

Объяснение: функция ВПР возвращает зарплату Мии Кларк, а не Мии Рид.

Vlookup нечувствителен к регистру

Функция ВПР в Excel выполняет поиск без учета регистра. Например, приведенная ниже функция ВПР ищет MIA (ячейка G2) в крайнем левом столбце таблицы.

Ширина

Объяснение: функция ВПР нечувствительна к регистру, поэтому она ищет MIA или Mia, или Mia, или miA и т. д. В результате функция ВПР возвращает зарплату Мии Кларк (первая инстанция). Используйте ИНДЕКС, ПОИСКПОЗ и ИСТОЧНИК в Excel, чтобы выполнить поиск с учетом регистра.

Несколько критериев

Хотите найти значение по нескольким критериям? Используйте ИНДЕКС и ПОИСКПОЗ в Excel, чтобы выполнить поиск по двум столбцам.

Примечание: приведенная выше формула массива ищет зарплату Джеймса Кларка, а не Джеймса Смита или Джеймса Андерсона.

<р>1. Например, приведенная ниже функция ВПР не может найти значение 28 в крайнем левом столбце.

Несколько таблиц поиска

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

<р>1. Создайте два именованных диапазона: Table1 и Table2.

<р>2. Выберите ячейку E4 и введите функцию ВПР, показанную ниже.

Объяснение: бонус зависит от рынка (Великобритания или США) и суммы продаж. Второй аргумент функции ВПР делает свое дело. В Великобритании функция ВПР использует Таблицу 1, в США функция ВПР использует Таблицу 2. Установите для четвертого аргумента функции ВПР значение ИСТИНА, чтобы вернуть приблизительное совпадение.

<р>4. Выберите ячейку E4, нажмите в правом нижнем углу ячейки E4 и перетащите ее вниз к ячейке E10.

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

Индексировать и сопоставлять

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

Поиск

Если у вас Excel 365 или Excel 2021, используйте XLOOKUP вместо VLOOKUP. Функция XLOOKUP проще в использовании и имеет некоторые дополнительные преимущества.

Читайте также: