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

Функция ВПР - Введение

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

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

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

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

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

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

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

Так что пристегнитесь.

Пришло время взлета.

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

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

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

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

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

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

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

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

Синтаксис

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

Входные аргументы

• lookup_value - это искомое значение, которое вы пытаетесь найти в крайнем левом столбце таблицы. Это может быть значение, ссылка на ячейку или текстовая строка. В примере с оценочным листом это будет ваше имя.
• table_array - это массив таблиц, в котором вы ищите значение. Это может быть ссылка на диапазон ячеек или именованный диапазон. В примере с оценочным листом это будет вся таблица, содержащая оценки для всех по каждому предмету.
• col_index - это номер индекса столбца, из которого вы хотите получить совпадающее значение. В примере с оценочным листом, если вам нужны оценки по математике (который является первым столбцом в таблице, который содержит оценки), вы должны посмотреть в столбец 1. Если вы хотите получить оценки по физике, вы должны посмотреть в столбец 2.
• [range_lookup] - здесь вы указываете, хотите ли вы точное или приблизительное совпадение. Если опущено, по умолчанию используется ИСТИНА - приблизительное совпадение. (см. дополнительные примечания ниже).

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

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

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

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

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

Пример 1. Определение баллов Брэда по математике

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

= ВПР (G4; $ A $ 3: $ E $ 10; ПОИСКПОЗ (H3; $ A $ 2: $ E $ 2,0); 0)

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

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

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

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

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

Что-то вроде того, что показано ниже:

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

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

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

= ВПР (G4; $ A $ 3: $ E $ 10; ПОИСКПОЗ (H3; $ A $ 2: $ E $ 2,0); 0)

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

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

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

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

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

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

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

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

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

Что-то вроде того, что показано ниже:

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

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

= ВПР (G4; ВЫБРАТЬ (ЕСЛИ (H2 = "Модульный тест"; 1; ЕСЛИ (H2 = "Среднесрочный"; 2,3)), $ A $ 3: $ E $ 7, $ A $ 11: $ E $ 15, $ A $ 19: $ E $ 23), ПОИСКПОЗ (H3, $ A $ 2: $ E $ 2,0), 0) 

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

ВЫБЕРИТЕ (IF (H2 = «Unit Test», 1, IF (H2 = «Midterm», 2,3)), $ A $ 3: $ E $ 7, $ A $ 11: $ E $ 15, $ A $ 19: $ E $ 23. )

Первым аргументом формулы является IF (H2 = «Unit Test», 1, IF (H2 = «Midterm», 2,3)), который проверяет ячейку H2 и определяет, на какой уровень экзамена идет речь. Если это модульный тест, он возвращает $ A $ 3: $ E $ 7, в котором указаны баллы для модульного теста. Если это Midterm, он возвращает $ A $ 11: $ E $ 15, иначе он возвращает $ A $ 19: $ E $ 23.

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

Пример 5 - Получение последнего значения из списка

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

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

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

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

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

= ВПР (9.99999999999999E + 307, 1 доллар США: 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; FALSE)

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

В приведенной выше формуле вместо использования искомого значения как есть, оно окружено с обеих сторон символом подстановки звездочка (*) - «*» & 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, чтобы убедиться, что все дополнительные пробелы исчезли, а затем использовать функцию VLOOKUP как обычно.

Пример 8 - Выполнение поиска с учетом регистра

По умолчанию значение подстановки в функции ВПР не чувствительно к регистру. Например, если ваше значение поиска MATT, matt или Matt, то для функции VLOOKUP все равно. Независимо от случая, он вернет первое совпадающее значение.

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

Вот пример:

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

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

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

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

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

= ВПР (МАКС (ТОЧНО (E2; $ A $ 2: $ A $ 9) * (СТРОКА ($ A $ 2: $ A $ 9)))), $ B $ 2: $ C $ 9,2,0)

Теперь давайте разберемся и разберемся, что это делает:

• EXACT (E2, $ A $ 2: $ A $ 9) - эта часть сравнивает значение поиска в E2 со всеми значениями в A2: A9. Он возвращает массив значений ИСТИНА / ЛОЖЬ, где ИСТИНА возвращается при точном совпадении. В этом случае он вернет следующий массив: {ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}.
• 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 VLOOKUP в своей базовой форме может искать одно значение поиска и возвращать соответствующее значение из указанной строки.

Но часто возникает необходимость использовать ВПР в 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 возвращает ошибку, если не может найти указанное значение подстановки. Возможно, вы не хотите, чтобы уродливое значение ошибки нарушало эстетику ваших данных, если функция VLOOKUP не может найти значение.

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

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

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

Вот формула:

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

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

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

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

Также см: Как обрабатывать ошибки ВПР в Excel.

Это все в этом руководстве по ВПР.

Я попытался охватить основные примеры использования функции Vlookup в Excel. Если вы хотите, чтобы в этот список было добавлено больше примеров, дайте мне знать в разделе комментариев.

Примечание. Я изо всех сил старался вычитать это руководство, но если вы обнаружите какие-либо ошибки или орфографические ошибки, сообщите мне об этом 🙂

С использованием Функция ВПР в Excel - Видео

• Функция Excel HLOOKUP.
• Функция Excel XLOOKUP
• Функция ИНДЕКС Excel.
• Функция НЕПРЯМАЯ в Excel.
• Функция ПОИСКПОЗ в Excel.
• Функция смещения Excel.