Загрузите образец книги
В этом руководстве показано, как использовать Функция ВПР в Excel в Excel, чтобы найти значение.
Обзор функции ВПР
Функция VLOOKUP Vlookup означает вертикальный поиск. Он ищет значение в крайнем левом столбце таблицы. Затем возвращает значение на указанное количество столбцов справа от найденного значения. Это то же самое, что и hlookup, за исключением того, что он ищет значения по вертикали, а не по горизонтали.
(Обратите внимание, как появляются входные данные формулы)
Синтаксис и аргументы функции ВПР:
| 1 | = ВПР (значение_просмотра, массив_таблиц, номер_столбца, поиск_диапазона) |
lookup_value - Значение, которое вы хотите найти.
table_array - Диапазон данных, содержащий как значение, которое вы хотите найти, так и значение, которое должен вернуть vlookup. Столбец, содержащий значения поиска, должен быть крайним левым столбцом.
col_index_num - Номер столбца диапазона данных, из которого вы хотите вернуть значение.
range_lookup - Правда или ложь. FALSE ищет точное совпадение. TRUE ищет ближайшее совпадение, которое меньше или равно lookup_value. Если выбрано значение ИСТИНА, крайний левый столбец (столбец подстановки) должен быть отсортирован по возрастанию (от самого низкого до самого высокого).
Что такое функция ВПР?
В качестве одной из самых старых функций в мире электронных таблиц функция ВПР используется для Vэротический Поиск. У него есть несколько ограничений, которые часто преодолеваются с помощью других функций, таких как INDEX / MATCH. Однако у него есть то преимущество, что он представляет собой единственную функцию, которая сама может выполнять поиск точных совпадений. Кроме того, это одна из первых функций, о которых люди узнают.
Базовый пример
Давайте посмотрим на образец данных из журнала успеваемости. Мы рассмотрим несколько примеров извлечения информации для конкретных учащихся.
Если мы хотим узнать, в каком классе находится Боб, мы должны написать формулу:
| 1 | = ВПР («Боб»; A2: C5; 2; ЛОЖЬ) |
Важно помнить, что искомый элемент (Боб) должен находиться в первом столбце нашего диапазона поиска (A2: C5). Мы сказали функции, что хотим вернуть значение из 2nd столбец, который в данном случае является столбцом B. Наконец, мы указали, что хотим выполнить точное совпадение поместив False в качестве последнего аргумента. Здесь ответ будет «Чтение».
Боковой совет: Вы также можете использовать число 0 вместо False в качестве последнего аргумента, поскольку они имеют одинаковое значение. Некоторые люди предпочитают это, потому что так писать быстрее. Просто знайте, что оба приемлемы.
Сдвинутые данные
Чтобы внести некоторые пояснения в наш первый пример, элемент поиска не обязательно должен находиться в столбце A вашей электронной таблицы, а должен находиться только в первом столбце диапазона поиска. Давайте использовать тот же набор данных:
А теперь давайте найдем оценку за естествознание. Наша формула была бы
| 1 | = ВПР ("Наука"; B2: C5; 2; ЛОЖЬ) |
Это по-прежнему действующая формула, поскольку первый столбец нашего диапазона поиска - это столбец B, в котором будет найден наш поисковый запрос «Наука». Мы возвращаем значение из 2nd столбец диапазона поиска, которым в данном случае является столбец C. Тогда ответ будет «A-».
Использование подстановочных знаков
Функция ВПР поддерживает использование подстановочных знаков «*» и «?». при поиске. Например, предположим, что мы забыли, как пишется имя Фрэнка, и просто хотели найти имя, которое начинается с буквы «F». Мы могли бы написать формулу
| 1 | = ВПР ("F *"; A2: C5; 2; ЛОЖЬ) |
Это сможет найти имя Фрэнк в строке 5, а затем вернуть значение из 2nd относительный столбец. В этом случае ответ будет «Наука».
Неточное совпадение
В большинстве случаев вам нужно убедиться, что последний аргумент в ВПР равен False (или 0), чтобы получить точное совпадение. Однако в некоторых случаях вы можете искать неточное совпадение. Если у вас есть список отсортированных данных, вы также можете использовать ВПР, чтобы вернуть результат для элемента, который является либо таким же, либо следующим наименьшим. Это часто используется при работе с увеличивающимся диапазоном чисел, например, в налоговой таблице или комиссионных бонусах.
Допустим, вы хотите найти налоговую ставку для дохода, указанного в ячейке D2. Формула в D4 может быть:
| 1 | = ВПР (D2; A2: B6; 2; ИСТИНА) |
Разница в этой формуле в том, что наш последний аргумент - «Верно». В нашем конкретном примере мы видим, что когда наш человек вводит доход в размере 45 000 долларов, у него будет ставка налога в размере 15%.
Примечание: Хотя обычно нам нужно точное совпадение с False в качестве аргумента, если вы забыли указать 4th аргумент в ВПР, значение по умолчанию - Истина. Это может привести к неожиданным результатам, особенно при работе с текстовыми значениями.
Динамический столбец
ВПР требует от вас аргументации, указывающей, из какого столбца вы хотите вернуть значение, но может возникнуть ситуация, когда вы не знаете, где будет находиться столбец, или вы хотите разрешить пользователю изменить столбец, из которого следует вернуться. В этих случаях может быть полезно использовать функцию ПОИСКПОЗ для определения номера столбца.
Давайте снова рассмотрим наш пример с зачетной книжкой с некоторыми данными в E2 и E4. Чтобы получить номер столбца, мы могли бы написать формулу
| 1 | = ПОИСКПОЗ (E2; A1: C1; 0) |
Это попытается найти точное положение «Grade» в диапазоне A1: C1. Ответ будет 3. Зная это, мы можем подключить его к функции ВПР и написать формулу в E6 следующим образом:
| 1 | = ВПР (E4; A2: C5; ПОИСКПОЗ (E2; A1: C1; 0); 0) |
Таким образом, функция ПОИСКПОЗ вернет 3, и это говорит ВПР вернуть результат из 3rd столбец в диапазоне A2: C5. В целом, мы получаем желаемый результат «C». Наша формула теперь динамична в том смысле, что мы можем изменить либо столбец для просмотра, либо имя для поиска.
Ограничения ВПР
Как упоминалось в начале статьи, самым большим недостатком функции ВПР является то, что она требует, чтобы поисковый запрос находился в крайнем левом столбце диапазона поиска. Хотя есть несколько хитрых уловок, которые можно использовать, чтобы преодолеть это, распространенной альтернативой является использование ИНДЕКС и ПОИСКПОЗ. Эта комбинация дает вам больше гибкости, а иногда даже может быть более быстрым вычислением.
ВПР в Google Таблицах
Функция ВПР в Google Таблицах работает точно так же, как и в Excel:
Дополнительные замечания
Используйте функцию ВПР для ВЕРТИКАЛЬНОГО поиска. ВПР ищет точное совпадение (range_lookup = FALSE) или ближайшее совпадение, которое равно или меньше lookup_value (range_lookup = ИСТИНА, только числовые значения) в первой строке table_array. Затем он возвращает соответствующее значение, количество строк на n ниже совпадения.
При использовании ВПР для поиска точного совпадения сначала вы определяете идентифицирующее значение, которое хотите найти, как lookup_value. Это идентифицирующее значение может быть SSN, идентификатором сотрудника, именем или другим уникальным идентификатором.
Затем вы определяете диапазон (называемый table_array), который содержит идентификаторы в верхней строке и любые значения, которые вы в конечном итоге хотите найти в строках под ней. ВАЖНО: Уникальные идентификаторы должны быть в верхней строке. Если это не так, вы должны либо переместить строку наверх, либо использовать ПОИСКПОЗ / ИНДЕКС вместо ВПР.
В-третьих, определите номер строки (row_index) принадлежащий table_array что вы хотите вернуться. Имейте в виду, что первая строка, содержащая уникальные идентификаторы, - это строка 1. Вторая строка - это строка 2 и т. Д.
Наконец, вы должны указать, следует ли искать точное совпадение (FALSE) или ближайшее совпадение (TRUE) в range_lookup. Если выбрана опция точного совпадения, а точное совпадение не найдено, возвращается ошибка (# N / A). Чтобы формула возвращала пустое значение, «не найдено» или любое другое значение вместо значения ошибки (# Н / Д), используйте функцию ЕСЛИОШИБКА с функцией ВПР.
Чтобы использовать функцию ВПР для получения приблизительного набора совпадений: range_lookup = ИСТИНА. Этот параметр доступен только для числовых значений. Значения необходимо отсортировать по возрастанию.
Вместо этого для горизонтального поиска используйте функцию HLOOKUP.
Примеры ВПР в VBA
Вы также можете использовать функцию ВПР в VBA. Тип:application.worksheetfunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Для аргументов функции (lookup_value и т. Д.) Вы можете либо ввести их непосредственно в функцию, либо определить переменные для использования вместо них.
Вернуться к списку всех функций в Excel
