10 примеров ВПР для начинающих и опытных пользователей

Функция ВПР является эталоном.

Если вы знаете, как использовать функцию ВПР, вы кое-что знаете об Excel.

Если вы этого не сделаете, вам лучше не указывать Excel в качестве одной из ваших сильных сторон в резюме.

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

Теперь, когда мы знаем важность этой функции Excel, имеет смысл полностью понять ее, чтобы мы могли с гордостью сказать: «Я знаю кое-что в Excel».

Это будет огромное руководство по ВПР (по моим меркам).

Я расскажу все об этом, а затем покажу вам полезный и практичный пример ВПР.

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

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

Давайте возьмем простой пример, чтобы понять, когда использовать Vlookup в Excel.

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

Вот как это работает:

• Вы заходите на доску объявлений и начинаете искать свое имя или регистрационный номер (двигайте пальцем вверх-вниз по списку).
• Как только вы найдете свое имя, вы переместите взгляд вправо от имени/регистрационного номера, чтобы увидеть свой результат.

Это именно то, что функция ВПР Excel делает для вас (не стесняйтесь использовать этот пример в своем следующем интервью).

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

синтаксис

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

Входные параметры

• искомое_значение –Это искомое значение, которое вы пытаетесь найти в крайнем левом столбце таблицы.Это может быть значение, ссылка на ячейку или текстовая строка.В примере с оценочным листом это будет ваше имя.
• таблица_массив –Это массив таблиц, в которых вы ищете значения.Это может быть ссылка на диапазон ячеек или именованный диапазон.В примере с таблицей баллов это будет вся таблица, содержащая баллы каждого по каждому предмету.
• столбец_индекс –Это номер индекса столбца, из которого вы хотите получить совпадающие значения.В примере с таблицей дробей, если вам нужна математическая дробь (это первый столбец в таблице, содержащий дробь), вы можете посмотреть столбец 1.Если вам нужна физическая оценка, вы можете посмотреть столбцы 1 и 2.
• [диапазон_поиска] —Здесь вы можете указать, хотите ли вы точное совпадение или приблизительное совпадение.Если опущено, по умолчанию TRUE — приблизительное совпадение(см. дополнительные примечания ниже).

Дополнительные примечания (скучно, но важно знать)

• Совпадения могут быть точными (FALSE или 0 в range_lookup) или приблизительными (TRUE или 1).
• При приблизительном поиске убедитесь, что список отсортирован по возрастанию (сверху вниз), иначе результаты могут быть неточными.
• Когда range_lookup имеет значение TRUE (приблизительный поиск) и данные сортируются в порядке возрастания:
  • Если функция ВПР не может найти значение, она возвращает наибольшее значение, меньшее, чем искомое_значение.
  • Возвращает ошибку #Н/Д, если искомое_значение меньше минимального значения.
  • Если искомое_значение является текстом, можно использовать подстановочные знаки (см. пример ниже).

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

10 примеров ВПР в Excel (базовый и расширенный)

Вот 10 полезных примеров использования Excel Vlookup, которые покажут вам, как использовать его в повседневной работе.

Пример 1. Найдите балл Брэда по математике

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

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

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

Вот формула ВПР, которая возвращает результат Брэда по математике:

=ВПР("Брэд",$A$3:$E$10,2,0)

Вышеприведенная формула имеет четыре параметра:

• «Брэд»: - Это значение поиска.
• $ 3: $ E 10 долларов - Это диапазон ячеек, на который мы смотрим.Помните, что Excel ищет значения поиска в крайнем левом столбце.В этом примере имя Брэд ищет в A3:A10, который является крайним левым столбцом указанного массива.
• 2 – Как только функция находит имя Брэда, она переходит ко второму столбцу массива и возвращает значение в той же строке, что и Брэд.Значение 2 здесь означает, что мы ищем оценки из второго столбца указанного массива.
• 0 – Это указывает функции ВПР искать только точные совпадения.

Вот как работает формула ВПР в приведенном выше примере.

Сначала он ищет значение Brad в крайнем левом столбце.Он просматривает значение в ячейке A6 сверху вниз.

Как только он находит значение, он перемещается вправо во втором столбце и захватывает значение из него.

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

Например, чтобы найти балл Марии по химии, используйте следующую формулу ВПР:

=ВПР("Мария",$A$3:$E$10,4,0)

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

Преимущество использования ссылок на ячейки заключается в том, что формула становится динамической.

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

Если вы введете искомое значение, которого нет в крайнем левом столбце, будет возвращена ошибка #Н/Д.

Пример 2 — двусторонний поиск

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

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

Вот пример двунаправленной функции ВПР.

Чтобы сделать эту формулу двустороннего поиска, вам также необходимо сделать столбец динамическим.Поэтому, когда пользователь меняет тему, формула автоматически выбирает правильный столбец (2 для математики, 3 для физики и т. д.).

Для этого вам нужно использовать функцию ПОИСКПОЗ в качестве параметра столбца.

Вот формула ВПР, которая сделает это:

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

В приведенной выше формуле в качестве номера столбца используется ПОИСКПОЗ(H3,$A$2:$E$2,0).Функция ПОИСКПОЗ принимает название темы в качестве значения поиска (в H3) и возвращает его позицию в A2:E2.Поэтому, если вы используете Math, он вернет 2, потому что Math находится в ячейке B2 (которая является второй ячейкой в ​​указанном диапазоне массива).

Пример 3. Использование раскрывающегося списка в качестве значения поиска

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

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

В зависимости от выбора формула автоматически обновит результат.

Как показано ниже:

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

Как мне это сделать:

Формула ВПР, используемая в этом примере, такая же, как и в примере 2.

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

Значения поиска были преобразованы в выпадающие списки.

Вот шаги для создания выпадающего списка:

• Выберите ячейку, для которой нужен раскрывающийся список.В этом примере в G4 нам нужно имя ученика.
• Перейдите в «Данные» -> «Инструменты данных» -> «Проверка данных».
• На вкладке «Настройки» диалогового окна «Проверка данных» выберите «Список» в раскрывающемся списке «Разрешить».
• В Источник выберите $A$3:$A$10.
• Щелкните ОК.

Теперь вы увидите раскрывающийся список в ячейке G4.Точно так же вы можете создать его в H3 для темы.

Пример 4. Трехсторонний поиск

Что такое трехсторонний поиск?

В примере 2 мы использовали справочную таблицу, содержащую баллы учащихся по разным предметам.Это пример двустороннего поиска, поскольку мы используем две переменные для получения баллов (имя учащегося и имя субъекта).

Теперь, скажем, через год студенту предстоит сдать три разных уровня экзаменов: модульные экзамены, промежуточные экзамены и выпускные экзамены (это экзамен со времен моего студенчества).

Трехсторонний поиск позволит получить баллы учащегося по определенному предмету на экзамене определенного уровня.

Как показано ниже:

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

Вот формула, используемая в ячейке H4:

=VLOOKUP(G4,CHOOSE(IF(H2="Unit Test",1,IF(H2="Midterm",2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23),MATCH(H3,$A$2:$E$2,0),0)

В этой формуле используется функция ВЫБОР, чтобы убедиться, что указана правильная таблица.Давайте проанализируем ВЫБОР части формулы:

CHOOSE(IF(H2=”Unit Test”,1,IF(H2=”Midterm”,2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23)

Первый аргумент формулы — ЕСЛИ (H2 = «Единичный тест», 1, ЕСЛИ (H2 = «Промежуточный», 2,3)), который проверяет ячейку H2 и видит указанный уровень теста.Если это модульный тест, возвращается $A$3:$E$7, который содержит оценку модульного теста.Возвращает $A$11:$E$15, если это промежуточный экзамен, в противном случае возвращает $A$19:$E$23.

Это делает массив таблицы ВПР динамическим, что делает его трехсторонним поиском.

Пример 5. Получить последнее значение из списка

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

Самое большое положительное число, которое вы можете использовать в Excel, равно 9.99999999999999E + 307 . Это также означает, что наибольший поисковый номер в ВПР одинаков.

Я не думаю, что вам когда-либо понадобятся вычисления с такими большими числами.Это именно то, что мы можем использовать, чтобы получить последнее число в списке.

Предположим, у вас есть набор данных, подобный следующему(в формате A1:A14), и вы хотите получить последнее число в списке.

Вот формула, которую вы можете использовать:

=ВПР(9.99999999999999E+307,$A$1:$A$14,ИСТИНА)

Обратите внимание, что в приведенной выше формуле используется приблизительное соответствие ВПР  (Обратите внимание на ИСТИНА в конце формулы, а не на ЛОЖЬ или 0).Также обратите внимание, что эта формула ВПР работает без сортировки списка.

Вот как примерно работает функция ВПР.Он сканирует крайний левый столбец сверху вниз.

• Это значение возвращается, если найдено точное совпадение.
• Если он находит значение выше, чем значение поиска, он возвращает значение в ячейке над ним.
• Если искомое значение больше всех значений в списке, возвращается последнее значение.

В приведенном выше примере работает третий случай.

из-за9.99999999999999E + 307— это наибольшее число, которое можно использовать в Excel, поэтому, когда оно используется в качестве значения поиска, оно возвращает последнее число в списке.

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

=ВПР("zzz",$A$1:$A$8,1, ИСТИНА )

Следуйте той же логике.Excel просматривает все имена, и, поскольку zzz считается большим, чем любое имя/текст, начинающийся с буквы перед zzz, он вернет последний элемент в списке.

Пример 6. Частичный поиск с использованием подстановочных знаков и ВПР

Подстановочные знаки Excel полезны во многих ситуациях.

Именно это волшебное зелье наделяет ваш рецепт сверхспособностями.

Частичный поиск требуется, когда вам нужно найти значение в списке, а точное совпадение отсутствует.

Например, предположим, что у вас есть набор данных, подобный следующему, и вы хотите найти компанию ABC в списке, но в списке есть ABC Ltd вместо ABC.

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

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

Введите следующую формулу в ячейку D2 и перетащите ее в другие ячейки:

=ВПР("*"&C2&"*",$A$2:$A$8,1,ЛОЖЬ)

Как работает эта формула?

В приведенной выше формуле вместо того, чтобы использовать искомое значение как есть, оно окружено звездочками с подстановочными знаками (*) - "*"&C2&"*"

Звездочки — это подстановочные знаки в Excel, которые могут представлять любое количество символов.

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

Например, ячейка C2 содержит ABC, поэтому функция ВПР просматривает имена в ячейках A2:A8 и ищет ABC.Он находит совпадение в ячейке A2, поскольку она содержит ABC от ABC Ltd. Не имеет значения, есть ли символы слева или справа от ABC.Совпадение будет считаться до тех пор, пока в текстовой строке не будет ABC.

Примечание. Функция ВПР всегда возвращает первое совпавшее значение и останавливает дальнейшие поиски.Итак, если в списке есть ABC ОООи ABC Corporation, он вернет первое и проигнорирует остальные.

Пример 7. ВПР возвращает ошибку, несмотря на совпадение значения поиска

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

Например, в приведенном ниже случае совпадение (Мэтт) есть, но функция ВПР по-прежнему возвращает ошибку.

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

Обычно это происходит, когда вы импортируете данные из базы данных или получаете данные от кого-то другого.Эти начальные/конечные пробелы имеют тенденцию заползать внутрь.

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

Вот формула, которая даст вам правильный результат.

=ВПР("Мэтт",ОТРЕЗАТЬ($A$2:$A$9),1,0)

Поскольку это формула массива, используйте Control+Shift+Enter вместо Enter.

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

Пример 8. Поиск с учетом регистра

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

Но если вам нужен поиск с учетом регистра, вам нужно использовать функцию EXACT и функцию VLOOKUP.

Это пример:

Как видите, три ячейки имеют одно и то же имя (в A2, A4 и A5), но с разным регистром заглавных букв.Справа у нас есть три имени (Мэтт, МЭТТ и Мэтт) и их баллы по математике.

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

Чтобы сделать его чувствительным к регистру, нам нужно использовать вспомогательный столбец (как показано ниже):

Чтобы получить значение в столбце справки, используйте функцию =СТРОКА().Он получит только номер строки в ячейке.

Если у вас есть вспомогательный столбец, вот формула, которая дает результат поиска с учетом регистра.

=VLOOKUP(MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))),$B$2:$C$9,2,0)

Теперь давайте разберем его и поймем, что он делает:

• EXACT(E2,$A$2:$A$9) — эта часть сравнивает искомое значение в E2 со всеми значениями в A2:A9.Он возвращает массив TRUE/FALSE, где TRUE возвращается в случае точного совпадения.В этом случае он вернет следующий массив: {ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}.
• EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9) - Эта часть умножает массив ИСТИНА/ЛОЖЬ на номер строки. Пока есть ИСТИНА, он даст номер строки , иначе возвращает 0. В этом случае возвращается {2;0;0;0;0;0;0;0}.
• MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))) – 這部分返回數字數組中的最大值。В этом случае он вернет 2 (это точный номер строки).
• Теперь мы просто используем это число в качестве значения поиска и используем массив поиска как B2: C9.

Примечание. Так как это формула массива, используйте Control + Shift + Enter вместо простого ввода.

Пример 9. Использование функции ВПР с несколькими условиями

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

Но обычно ВПР требуется в Excel с несколькими критериями.

Предположим, у вас есть данные, содержащие имена учащихся, типы тестов и результаты по математике (показаны ниже):

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

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

Это можно сделать с помощью вспомогательных столбцов.Первый шаг — вставить вспомогательный столбец слева от дроби.

Теперь, чтобы создать уникальный квалификатор для каждого экземпляра имени, используйте следующую формулу в C2: =A2&”|”&B2

Скопируйте эту формулу во все ячейки вспомогательного столбца.Это создаст уникальное значение поиска для каждого экземпляра имени (показано ниже):

Теперь, когда имена повторяются, при объединении имен с уровнями экзамена дубликатов нет.

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

Это формула, которая дает вам результат в G3:I8.

=ВПР($F3&"|"&G$2,$C$2:$D$19,2,0)

Здесь мы объединяем имя студента и уровень экзамена, чтобы получить значение поиска, мы используем это значение поиска, чтобы проверить во вспомогательном столбце совпадающие записи.

Примечание. В приведенном выше примере мы использовали | Используется как разделитель при добавлении текста во вспомогательные столбцы.В некоторых очень редких (но возможных) случаях у вас может быть два разных критерия, но их объединение даст один и тот же результат.Это пример:

Обратите внимание, что хотя A2 и A3 различны, а B2 и B3 различны, в конечном итоге их комбинация одинакова.Однако если использовать разделители, то даже комбинация будет другой (D2 и D3).

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

Пример 10. Обработка ошибок при использовании функции ВПР

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

Вы можете легко удалить значения ошибок с полным текстом любого значения, например «Недоступно» или «Не найдено».

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

Чтобы удалить эту ошибку и заменить ее чем-то осмысленным, заключите функцию ВПР в функцию ЕСЛИОШИБКА.

Вот формула:

=ЕСЛИОШИБКА(ВПР(D2,$A$2:$B$7,2,0),"Не найдено")

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

Функция ЕСЛИОШИБКА доступна начиная с Excel 2007.Если вы используете предыдущую версию, используйте следующие функции:

=IF(ISERROR(VLOOKUP(D2,$A$2:$B$7,2,0)),"Not Found",VLOOKUP(D2,$A$2:$B$7,2,0))

Это все, что касается этого руководства по ВПР.

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

Связанные функции Excel:

• Функция ГПР Excel.
• Функция XLOOKUP в Excel
• Индексная функция Excel.
• Косвенная функция Excel.
• Функция сопоставления Excel.
• Функция смещения Excel.