Функция vlookup в excel примеры: инструкция на примере / Skillbox Media

VBA Excel. Метод WorksheetFunction.VLookup

Поиск значения в таблице (диапазоне, массиве) по значению в первом столбце таблицы с помощью метода VBA Excel WorksheetFunction.VLookup. Синтаксис, параметры, примеры.

1.
Метод WorksheetFunction.VLookup

1.1.
Синтаксис

1.2.
Параметры

2.
Примеры

2.1.
Поиск значения в таблице

2.2.
Поиск значения в массиве

Метод WorksheetFunction.VLookup

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

Синтаксис

Синтаксис метода WorksheetFunction. VLookup в VBA Excel:

Параметры

Описание параметров метода WorksheetFunction.VLookup:

Значения параметра Arg4 (Range_lookup), задающие точность сопоставления:

Значение	Точность сопоставления
True	Значение по умолчанию. Метод WorksheetFunction.VLookup находит точное или приблизительное совпадение Arg1 со значением в первом столбце. Если точное совпадение не найдено, используется самое большое значение, меньшее Arg1. Значения в первом столбце таблицы должны быть отсортированы по возрастанию.
False	Метод WorksheetFunction.VLookup находит только точное совпадение. Сортировка значений первого столбца таблицы не требуется. Если точное совпадение не найдено, генерируется ошибка.

Если значение параметра Arg1 является текстом, а Arg4=False, тогда в строке Arg1 можно использовать знаки подстановки (спецсимволы): знак вопроса (?) и звездочку (*). Знак вопроса заменяет один любой символ, а звездочка соответствует любой последовательности символов. Чтобы знак вопроса (?) и звездочка (*) обозначали сами себя, перед ними указывается тильда (~).

Примеры

Примеры обкатывались на следующей таблице:

Поиск значения в таблице

Результат работы кода:

Поиск значения в массиве

Результат работы кода:

Да, здесь можно немного улучшить структуру кода, применив оператор With...End With:

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

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

В этом учебном пособии рассматриваются:

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

Функция ВПР – это эталон.

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

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

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

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

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

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

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

Время взлета.

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

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

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

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

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

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

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

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

Синтаксис

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример 2. Двусторонний поиск

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

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

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

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

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

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

 =ВПР(G4,$A$3:$E$10,MATCH(h4,$A$2:$E$2,0),0) 

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

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

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

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

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

Примерно так, как показано ниже:

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

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

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

 =ВПР(G4,$A$3:$E$10,MATCH(h4,$A$2:$E$2) ,0),0) 

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

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

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

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

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

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

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

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

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

Примерно так, как показано ниже:

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

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

 =ВПР(G4,ВЫБОР(ЕСЛИ(h3="Единичный тест",1,ЕСЛИ(h3="Промежуточный",2,3)),$A$3:$ E$7,$A$11:$E$15,$A$19:$E$23),MATCH(h4,$A$2:$E$2,0),0)  

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

ВЫБОР(ЕСЛИ(h3=»Единичный тест»,1,ЕСЛИ(h3=»Промежуточный»,2,3)),$A$3:$E$7,$A$11: $E$15,$A$19:$E$23)

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

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

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

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

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

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

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

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

 =VLOOKUP(9. 99999999999999E+307,$A$1:$A$14,  TRUE)  

Обратите внимание, что в приведенной выше формуле используется приблизительное соответствие VLOOK UP (обратите внимание на TRUE в конце формулы вместо FALSE или 0). Также обратите внимание, что для работы этой формулы ВПР список не нужно сортировать.

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

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

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

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

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

 =VLOOKUP("zzz",$A$1:$A$8,1,  TRUE  ) 

Далее следует та же логика. 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 Ltd., и ABC Corporation в списке, он вернет первый и проигнорирует остальные.

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

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

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

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

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

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

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

 =VLOOKUP("Matt",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)*(СТРОКА($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)* (СТРОКА($A$2:$A$9))) – эта часть возвращает максимальное значение из массива чисел. В этом случае будет возвращено 2 (это номер строки, в которой есть точное совпадение).
• Теперь мы просто используем это число в качестве значения поиска и используем массив поиска как B2:C9

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

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

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

Но часто возникает необходимость использовать ВПР в Excel с несколькими критериями.

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

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

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

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

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

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

Теперь, несмотря на то, что имена повторяются, повторений при объединении имени с уровнем проверки нет.

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

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

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

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

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

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

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

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

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

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

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

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

Вот формула:

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

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

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

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

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

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

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

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

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

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

• Функция ГПР в Excel.
• Функция XLOOKUP в Excel
• Функция ИНДЕКС в Excel.
• Функция Excel НЕПРЯМАЯ.
• Excel Функция ПОИСКПОЗ.
• Функция СМЕЩЕНИЯ Excel.

Вам также могут понравиться следующие учебники по Excel:

• VLOOKUP Vs. ИНДЕКС/ПОИСКПОЗ – На этом дебаты заканчиваются.
• Примеры сопоставления индексов Excel
• Как сделать функцию ВПР чувствительной к регистру.
• Получение нескольких значений поиска без повторения в одной ячейке
• Избегайте вложенных функций ЕСЛИ в Excel с помощью ВПР

ВПР Excel — подробное руководство с 8 примерами

ВПР — одна из самых полезных и важных функций в Microsoft Excel. Обычно он используется для поиска определенного значения в огромных листах данных, где ручное вмешательство может быть обременительным. Буква «V» в VLOOKUP означает «вертикальный», поэтому эту функцию иногда также называют вертикальным поиском.

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

Вы также можете прочитать мой предыдущий пост: «Как расположить по алфавиту в Excel», там я использовал Excel VLOOKUP для сортировки списка.

Примечание : Это длинный пост из более чем 2700 слов. Если вы не хотите читать весь пост, вы можете использовать приведенные ниже ссылки для перехода к нужному разделу.

Содержание

Определение ВПР в Excel

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

Для чего используется функция вертикального поиска?

Эта функция в основном используется для следующих задач:

1. Для поиска одного или набора значений в таблице данных.
2. Чтобы добавить столбец в таблицу данных из какой-либо другой таблицы на основе некоторых уникальных (атрибутов).

Синтаксис функции ВПР

Ее синтаксис следующий:

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

Здесь, ‘ поиск_ значение ‘ указывает значение для поиска внутри ‘ table_array ‘. Это может быть либо значение, либо ссылка.

‘ table_array ’ — это диапазон с двумя или более столбцами. table_array ’ аргумент может получить ссылку на диапазон или именованный диапазон. Крайний левый столбец этого диапазона должен содержать « lookup_value ».

‘ column_index ’ — это относительный индекс столбца, значение которого должно быть возвращено функцией ВПР. « column_index » 1 будет возвращать значения из первого столбца в « table_array ». Точно так же ‘ column_index ’ 2 будет возвращать значения из второго столбца в ‘ table_array ’.

‘ range_lookup ’ — это логическое значение, которое указывает, хотите ли вы, чтобы функция ВПР находила точное или приблизительное совпадение. Если его значение равно «Истина», будет возвращено либо приблизительное, либо точное совпадение. Здесь, если точное совпадение не найдено, возвращается следующее значение, которое меньше ‘ lookup_value ’. Если его значение равно «False», будет возвращено только точное совпадение.

Несколько важных моментов о функции вертикального поиска

• Функция ВПР выполняет поиск без учета регистра.
• ВПР Excel возвращает «#Н/Д», если не удается найти « искомое_значение » внутри « table_array ».
• Возвращает ошибку #VALUE!, если значение column_index меньше 1.
• Возвращает ошибку #REF!, если значение column_index больше числа столбцов в « table_array ».
• Значение по умолчанию ‘ range_lookup — ИСТИНА. Таким образом, лучше опустить этот аргумент, если вам нужно выполнить либо приблизительное, либо точное совпадение.
• Вертикальный поиск позволяет использовать подстановочные знаки в аргументе ‘ lookup_value ’. Мы обсудим это позже в этой статье.

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

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

Предположим, у нас есть таблица, показанная ниже.

Задача: Наша цель — найти зарплату любого конкретного сотрудника (скажем, Бенджамина Мутриси) на основе его имени.

Решение: Теперь давайте попробуем применить ВПР, чтобы найти решение.

lookup_value: Это значение, на основе которого должен выполняться поиск. В нашем случае искомое_значение находится в ячейке B4, то есть «Бенджамин Мутриси».

table_array: Это диапазон таблицы, из которой должны быть получены значения. Обратите внимание, что это table_array ’ всегда должен содержать ‘ lookup_value ’ в крайнем левом столбце.

col_index_num: Указывает позиционную ссылку столбца, который должен возвращать ВПР.

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

Таким образом, в этом случае функция ВПР будет выглядеть так:

=ВПР(B4,B7:D14,3,ЛОЖЬ)

5 Примеры функции ВПР для начинающих

Теперь давайте перейдем к некоторым примерам:

Пример 1: В этом примере у нас есть список учеников с их оценками. Теперь нам нужно найти счет ученика с именем «Винсент».

Чтобы найти решение нашей проблемы, мы использовали вертикальный поиск как:

=ВПР(E4,A3:C16,3,ЛОЖЬ)

и это дает результат 75.

Объяснение: 905 32

• Первый аргумент функции, т.е. ‘ lookup_value ’ = E4 (Ссылка на «Винсент»).
• Второй аргумент, т. е. ‘ table_array ’ = A3:C16 (диапазон таблицы учеников).
• Третий аргумент, т. е. ‘ column_index ’ = 3 (номер столбца, значение которого должна возвращать функция ВПР).
• Четвертый аргумент, т. е. ‘ range_lookup ’ = FALSE (означает, что нам нужно только точное совпадение).

Пример 2: В следующем примере у нас та же таблица, но теперь наша задача — найти фамилию студента «Эммануэль».

Итак, мы будем использовать ВПР как:

=ВПР(E4,A3:C16,2,ЛОЖЬ)

и в результате получится «Шаули».

Объяснение:

• Первый аргумент функции, т.е. ‘ lookup_value ’ = E4 (Ссылка на «Эммануэль»).
• Второй аргумент, т. е. ‘ table_array ’ = A3:C16 (диапазон таблицы учеников).
• Третий аргумент, т. е. ‘ column_index ’ = 2 (номер столбца, значение которого должна возвращать функция ВПР).
• Четвертый аргумент, т. е. ‘ range_lookup ’ = FALSE (означает, что нам нужно только точное совпадение).

Пример 3: В этом примере у нас есть таблица «Таблица элементов». Таблица содержит 3 столбца, а именно атомную массу, плотность и имя элемента.

Здесь мы должны найти плотность элемента, атомная масса которого равна 15 или чуть меньше.

Теперь здесь мы можем использовать формулу вертикального поиска:

=ВПР(15,A3:B14,2)

что дает 1.25.

Объяснение:

• Первый аргумент функции, т.е. ‘ lookup_value ’ = 15 (Атомная масса для поиска).
• Второй аргумент, т. е. ‘ table_array ’ = A3:B14 (диапазон таблицы элементов).
• Третий аргумент, т. е. ‘ column_index ’ = 2 (номер столбца, значение которого должна возвращать функция вертикального поиска).
• Четвертый аргумент, то есть ‘ range_lookup ’, опущен, и, следовательно, его значение равно true. Итак, сначала он ищет 15 в столбце атомной массы. А когда ему не удается найти 15, он возвращает плотность элемента чуть меньше 15.

Пример 4: В этом примере у нас есть 2 таблицы, как показано ниже. Наша задача — применить вертикальный поиск на основе «имен элементов» и получить точки плавления элементов из второй таблицы.

Это можно сделать с помощью формулы:

=VLOOKUP(C3,$F$3:$G$14,2,FALSE)

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

Объяснение:

• Первый аргумент функции, т.е. ‘ lookup_value ’ = C3 (Ссылка на первый элемент).
• Второй аргумент, т. е. « table_array » = $F$3:$G$14 (диапазон таблицы элементов 2) — если вам интересно, что это за знаки «$» вместе со ссылкой на таблицу, вам следует прочитать этот пост.
• Третий аргумент, т. е. ‘ column_index ’ = 2 (номер столбца, значение которого должна возвращать функция вертикального поиска).
• Четвертый аргумент, т. е. ‘ range_lookup ’ = FALSE (означает, что нам нужно только точное совпадение).

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

Пример 5: В этом примере мы будем использовать операторы подстановки вместе с вертикальным поиском. Здесь мы должны найти оценку ученика, чье имя начинается на «А».

Для этого мы можем использовать формулу:

=VLOOKUP("A*",A3:C16,3,FALSE)

что дает результат 66.

Объяснение:

Как правило, мы можно использовать следующие два оператора подстановки с функцией ВПР Excel.

Примечание. Простое размещение знака тильды (~) перед любым подстановочным знаком указывает Excel, что подстановочный знак (‘*’ или ‘?’) следует рассматривать как строку, а не как подстановочный знак. .

• Первый аргумент функции, т. е. ‘ lookup_value ’ = «A*» (любое слово, начинающееся с буквы «A»).
• Второй аргумент, т. е. ‘ table_array ’ = A3:C16 (диапазон таблицы учеников).
• Третий аргумент, т. е. ‘ column_index ’ = 3 (номер столбца, значение которого должна возвращать функция вертикального поиска).
• Четвертый аргумент, т. е. ‘ range_lookup ’ = FALSE (означает, что нам нужно только точное совпадение). Пример 6 . Теперь, используя вертикальный поиск, нам нужно найти, все ли элементы в «дочернем списке» также присутствуют в «основном списке».

  В таком случае мы будем использовать формулу:

  =ЕСЛИ(ИСНА(ВПР(B2,$G$2:$G$17,1,0)),"Не существует", "Существует")

  В этой формуле используется оператор If, функция ISNA и функция ВПР.

  Объяснение:

  Здесь оператор IF проверяет, являются ли выходные данные функции ВПР ошибкой #NA или нет. ЕСЛИ выводится ошибка #NA, это означает, что текущий элемент дочернего списка отсутствует в основном списке. И, следовательно, оператор IF пишет перед ним «Не существует». Однако, если элемент присутствует в главном списке, он пишет «Существует».

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

  Для этого мы можем использовать ВПР как:

  = ВПР(C3,$F$4:$G$9,2,ИСТИНА)

  , а затем перетащите эту формулу в ячейки ниже.

  Объяснение:

  • Первый аргумент функции, т.е. ‘ lookup_value ’ = C3 (Ссылка на первый элемент).
  • Второй аргумент, т. е. ‘ table_array ’ = $F$4:$G$9 (диапазон оценок и таблица оценок).
  • Третий аргумент, т. е. ‘ column_index ’ = 2 (номер столбца, значение которого должна возвращать функция ВПР).
  • Четвертый аргумент, т. е. ‘ range_lookup ’ = True (соответствует как точным значениям, так и значениям немного меньшим).

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

  В этом примере «Таблица учащихся» находится на «Листе 1», а «Таблица оценок и баллов» — на «Листе 2». Таким образом, вы можете использовать ВПР как:

  =ВПР(C3,Лист2!$B$3:$C$8,2,ИСТИНА)

  Объяснение:

  • Первый аргумент функции, т.е. искомое_значение ‘ = C3 (ссылка для первого элемента).
  • Второй аргумент, т. е. ‘ table_array ’ = Sheet2!$B$3:$C$8 (диапазон оценок и таблица оценок, представленная на Sheet2).
  • Третий аргумент, т. е. ‘ column_index ’ = 2 (номер столбца, значение которого должна возвращать функция вертикального поиска).
  • Четвертый аргумент, т. е. ‘ range_lookup ’ = True (соответствует как точному, так и немного меньшим значениям).

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

  Подробнее см. анимацию ниже:

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

  Функцию ВПР также можно использовать в VBA. Ниже приведен пример кода:

   Sub Vertical_lookup_test() 
   Dim Result As Variant 
   Dim myVal As String ' Может быть целым, длинным, двойным и т. д. 
   Dim Rng As Range 
   Dim Clm As Integer 
   Set Rng = ActiveSheet.Range( "A:E") 'Set Range 
   myVal = "Florian" 'Значение для поиска 
   Clm = 3 'Выбираемый столбец 
   Result = Application.VLookup(myVal, Rng, Clm, False) 
   Если IsError(Результат), то 
   Результат = "Не найдено!" 
   End If 
   MsgBox Result 
   End Sub 

  Перед использованием приведенного выше кода убедитесь, что вы изменили следующие значения:

  1. Измените Set Rng = ActiveSheet.Range("A:E") на желаемый диапазон использовать.

  2. Измените myVal = "Florian" на значение, которое вы хотите использовать для вертикального поиска. Вы также можете использовать функцию InputBox, чтобы получить это значение от пользователя во время выполнения.

  3. Измените Clm = 3 на столбец, который вы хотите получить с помощью ВПР.

  Рекомендуемая литература : ВПР в VBA – с примерами

  Как вернуть несколько столбцов из ВПР?

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

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

  Выполните следующие шаги, чтобы использовать этот :

  Предположим, нам нужно получить все детали «Флориан». Итак, мы будем действовать следующим образом:

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

  2. Затем, не щелкая где-либо еще, введите формулу:

  =ВПР("Флориан",A:D,{1,2,3,4},ЛОЖЬ)

  в строке формул. Третий аргумент, то есть {1,2,3,4}, указывает столбцы, которые необходимо извлечь.

  3. После этого просто нажмите клавиши Ctrl + Shift + Enter. При этом приведенная выше формула будет заключена в фигурные скобки, а в выбранных вами ячейках будут отображаться выбранные столбцы.

  Отрицательный VLOOKUP

  Во всех предыдущих примерах вы, должно быть, видели, что мы всегда использовали ‘ lookup_value ’, которое присутствует в самом левом столбце диапазона. Итак, возникает вопрос, можем ли мы использовать « lookup_value », которого нет в крайнем левом столбце?

  Посмотрите, что происходит, когда ваше « искомое_значение » не находится с крайней левой стороны, и вы вводите отрицательное значение « column_index », надеясь получить значение слева от « искомое_значение ».

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

   Функция NEGATIVE_VLOOKUP(lookup_value, table_array As Range, col_index_num As Integer, CloseMatch As Boolean) 
   Dim RowNbr As Long 
   RowNbr = Application.WorksheetFunction.Match(lookup_value, table_array.Resize(, 1), CloseMatch) 
   NEGATIVE_VLOOKUP = table_array(RowNbr, 1).Offset(0, col_index_num) 
   End Function 

  Это UDF использует Функции Match и Offset для выполнения задачи.

  Как использовать отрицательную функцию VLOOKUP UDF

  Давайте разберемся, как использовать эту функцию. В нашем примере у нас есть студенческий стол, и мы должны найти имя «Petit».

  Итак, мы будем использовать эту формулу как:

  =ОТРИЦАТЕЛЬНЫЙ_ВПР("Petit",B:B,-1,FALSE)

  Объяснение:

  • Первый аргумент UDF — это ‘ искомое_значение ‘.
  • Второй аргумент — это диапазон, в котором эта пользовательская функция должна искать ‘ lookup_value ’.
  • Третий аргумент — это позиционная ссылка на столбец, который должен возвращать этот UDF.
  • Последний аргумент — это логическое значение, указывающее точное или приблизительное совпадение.

  Аналогично, в той же таблице, если нам нужно найти имя учащегося с 98 баллами по естественным наукам, формула будет выглядеть так:

  =ОТРИЦАТЕЛЬНЫЙ_ВПР(98,D:D,-3,ЛОЖЬ)

  Создание нескольких критериев ВПР

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

  Например, У нас есть таблица Student, как показано на рисунке ниже. Теперь, как вы можете видеть, есть два ученика с именем «Кристоф» (в A4 и A6). Таким образом, применение ВПР к имени может привести к несогласованности.