Функция ВПР
Совет: Попробуйте использовать новую функцию ПРОСМОТРX , улучшенную версию функции ВЛОП, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что упрощает и удобнее в использовании, чем предшественницу.
Если вам нужно найти что-то в таблице или диапазоне по строкам, используйте В ПРОСМОТР. Например, можно найти цену автомобильной части по номеру части или имя сотрудника на основе его ИД.
Совет: Ознакомьтесь с этими видеороликами с YouTube от Microsoft Creators, чтобы узнать больше о ВЛИО!
Самая простая функция ВПР означает следующее:
=ВРОТ.В.(Что вы хотите найти, где ее нужно найти, номер столбца в диапазоне, содержащего возвращаемую величину, возвращает приблизительное или точное совпадение, обозначенные как 1/ИСТИНА или 0/ЛОЖЬ).
Совет: Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (Фрукт) отображалось слева от возвращаемого значения, которое нужно найти (Количество).
Используйте функцию ВПР для поиска значения в таблице.
ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])
=ВLOOKUP(A2;’Сведения о клиенте’! A:F;3;ЛОЖЬ)
Значение для поиска. Иного значения должно быть в первом столбце диапазона ячеек, который указан в table_array.
Например, если массив таблицы охватывает ячейки B2:D7, lookup_value должны быть в столбце B.
Искомое_значение может являться значением или ссылкой на ячейку.
Диапазон ячеек, в котором будет выполнен поиск искомого_значения и возвращаемого значения с помощью функции ВПР. Можно использовать именуемый диапазон или таблицу, а не ссылки на ячейки, а имена в аргументе.
Первый столбец в диапазоне ячеев должен содержать lookup_value . Диапазон ячеев также должен включать возвращаемую величину, которая требуется найти.
Номер столбца (начиная с 1 в левом большинстве столбцов table_array),содержащий возвращаемую величину.
Логическое значение, определяющее, какое совпадение должна найти функция ВПР, — приблизительное или точное.
Приблизительное совпадение: 1/ИСТИНА предполагает, что первый столбец в таблице отсортировали по алфавиту или по числу, а затем будут выполнять поиск ближайшего значения. Это способ по умолчанию, если не указан другой. Например, =ВКП(90;A1:B100;2;ИСТИНА).
Точное совпадение: 0/ЛОЖЬ ищет точное значение в первом столбце. Например, =ВКП("Кузнецов";A1:B100;2;ЛОЖЬ).
Начало работы
Для построения синтаксиса функции ВПР вам потребуется следующая информация:
Значение, которое вам нужно найти, то есть искомое значение.
Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона указать диапазон B2:D11, следует посчитать B первым столбцом, C — вторым и так далее.
При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.
Теперь объедините все перечисленное выше аргументы следующим образом:
=ВЛОП(искомого значения; диапазон, содержащий искомые значения, номер столбца в диапазоне, содержащий возвращаемую величину, приблизительное совпадение (ИСТИНА) или Точное совпадение (ЛОЖЬ)).
Примеры
Вот несколько примеров использования функции ВПР.
Пример 1
Пример 2
Пример 3
Пример 4
Пример 5
Функцию ВЛОП можно использовать для объединения нескольких таблиц в одну, если одна из таблиц имеет поля, общие для всех остальных. Это особенно полезно, если вам нужно поделиться книгой с людьми, у которых есть более старые версии Excel, которые не поддерживают функции данных с несколькими таблицами в качестве источников данных, путем объединения источников в одну таблицу и изменения источника данных функции данных в новую таблицу, функцию данных можно использовать в более старых версиях Excel (при условии, что функция данных поддерживается более старой версией).
В этом столбце столбцы A–F и H имеют значения или формулы, которые используют только значения на этом сайте, а в остальных столбцах используется В., а для получения данных из других таблиц используются значения из столбцов A (код клиента) и B (Доверенность).
Скопируйте таблицу с общими полями на новый и придать ей имя.
Чтобы открыть диалоговое окно Управление отношениями, > в > управления отношениями нажмите кнопку Data > Data Tools (Управление отношениями).
Для каждой из указанных связей обратите внимание на следующее:
Поле, которое связывает таблицы (в скобки в диалоговом окне). Это первый lookup_value для формулы ВЛВП.
Имя связанной таблицы подытов. Это первый table_array в формуле ВЛИО.
Поле (столбец) в связанной таблице подытовки с данными, которые должны быть в новом столбце. Эта информация не отображается в диалоговом оке Управление связями. Чтобы узнать, какое поле нужно извлечь, необходимо посмотреть в связанной таблице подыска. Обратите внимание на номер столбца (A=1) — это col_index_num формуле.
Чтобы добавить поле в новую таблицу, введите формулу в первом пустом столбце с использованием сведений, собранных на шаге 3.
В нашем примере в столбце G для получения данных "Ставка счета" из четвертого столбца (col_index_num = 4) из таблицы "Доверенности" используется столбец "Доверенность" lookup_value(table_array) с формулой =В.В.ПРОСМОТР([@Attorney];tbl_Attorneys;4;ЛОЖЬ).
В формуле также можно использовать ссылку на ячейку и ссылку на диапазон. В нашем примере это будет =ВЛП(A2;’Защитники’! A:D,4;ЛОЖЬ).
Продолжайте добавлять поля, пока не будут добавлены все необходимые поля. При подготовке книги, содержащей функции данных, в которой используются несколько таблиц, измените источник данных функции на новую таблицу.
Функция ВПР в Excel. Как использовать?
Функция ВПР (VLOOKUP) в Excel используется для поиска данных из одной таблицы и сопоставления их с данными из другой таблицы.
Кликните по кнопке ниже для загрузки Excel файла с примерами работы функции ВПР :
- Видео-урок: Как использовать функцию ВПР в Excel
- Функция ВПР в Excel для чайников
- Синтаксис
- Аргументы функции
- Дополнительная информация
- Примеры использования ВПР в Excel
- Пример 1. Ищем результат экзамена для студента
- Пример 2. Двухфакторный поиск данных
- Пример 3. Используем выпадающий список при двух факторном поиске
- Пример 4. Трех факторный поиск данных ВПР
- Пример 5. Получаем последнее значение колонки с помощью функции VLOOKUP (ВПР)
- Пример 6. Частичный поиск с использованием символов подстановочных знаков и ВПР
- Пример 7. Функция ВПР возвращает ошибку, несмотря на совпадение в значении поиска
- Пример 8. Функция ВПР в Excel с несколькими условиями
- Пример 9. Обработка ошибок с помощью функции ВПР
Видео-урок: Как использовать функцию ВПР в Excel
Функция ВПР в Excel для чайников
На примере ниже, в таблице справа, мы хотим подставить данные результатов экзамена по математике для студента Сидорова. Нам известны данные по всем студентам из таблицы слева. Используя формулу, мы можем подставить необходимые для нас данные.
Синтаксис
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) – английская версия
=ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр]) – русская версия
Аргументы функции
- lookup_value(искомое_значение) – это величина для поиска, из крайнего левого столбца таблицы. Это может быть значение, ссылка на ячейку или текстовая строка. В примере со студентами это их фамилии;
- table_array (таблица) – это диапазон данных, в котором будет осуществлен поиск. Это может быть ссылка на диапазон ячеек или именованный диапазон. В примере с таблицей со студентами, это будет вся таблица, которая содержит оценку и фамилии студентов;
- col_index (номер_столбца) – это порядковый номер столбца в диапазоне с данными, из которого будет получена искомая величина;
- [range lookup]([интервальный_просмотр]) – этот аргумент указывает на точность совпадения данных при поиске. Укажите “0” – если точное, “1” – если приблизительное совпадение.
Дополнительная информация
- совпадение искомых данных может быть точным и приблизительным;
- при сопоставлении по приблизительной точности данных убедитесь, что данные в таблицах отсортированы в убывающем порядке (от большого к малому). Иначе, результат сопоставления будет некорректным;
- при сопоставлении данных по приблизительной точности:
– если функция не найдет искомое значение, она выдаст наибольшую величину, которая будет меньше чем значения поиска;
– если функция при сопоставлении выдает ошибку #N/A, то искомое значение меньше чем самая маленькая величина в искомом диапазоне;
– вы можете использовать подстановочные знаки для искомых значений.
Примеры использования ВПР в Excel
Пример 1. Ищем результат экзамена для студента
На примере ниже, в левой колонке таблицы указан список студентов. В правой части таблицы указаны итоговые баллы по экзаменам этих студентов.
Перед нами стоит задача узнать, какой балл получил студент Петров по Физике.
Узнать это мы можем с помощью формулы ниже:
=VLOOKUP(“Петров”,$A$3:$E$10,3,0) – английская версия
=ВПР(“Петров”;$A$3:$E$10;3;0) – русская версия
Формула выше состоит из четырех аргументов:
- “Петров” – фамилия, данные по которой, ищем;
- $A$3:$E$10 – диапазон данных с результатами экзаменов;
- “3” – порядковый номер столбца диапазона данных со значением оценки по Физике;
- “0” – точное совпадение искомого значения.
Ниже, наглядно изображено как работает по примеру наша функция.
Сначала, она ищет в крайней левой колонке диапазона данных фамилию “Петров”. Поиск происходит сверху вниз.
Как только Excel нашел точное совпадение “Петров” в диапазоне данных, система “шагает” в право, на третью колонку для отображения искомых данных.
Используя этот подход вы можете получить любые данные по каждому студенту и учебному предмету.
Например, чтобы найти результаты экзамена для студента Пескова по Химии, нам нужна формула:
=VLOOKUP(“Песков”, $A$20:$E$28,4,0) – английская версия
=ВПР(“Песков”;$A$20:$E$28;4;0) – русская версия
В приведенном выше примере фамилия студента указана в двойных кавычках. Также, можно использовать ссылку на ячейку с искомым значением. Ссылка на ячейку делает формулу динамической.
Например, если у вас есть ячейка с фамилией, и вы ищете оценку по математике, результат будет автоматически обновляться при изменении имени учащегося (как показано ниже):
Если в качестве аргумента поиска будет введена фамилия студента, которой нет в диапазоне данных, функция выдаст ошибку.
Пример 2. Двухфакторный поиск данных
На примере выше мы указывали вручную в качестве атрибута “номер_столбца” порядковый номер колонки с данными.
Но что, если мы хотим сделать поиск динамическим? Что если, мы хотим вводить имя студента и название предмета и функция автоматически выведет нужные данные? Для этого нам потребуется использовать двух факторный поиск.
Ниже пример двух факторного поиска:
Чтобы сделать двух факторную формулу поиска, важно сделать выбор данных из колонок динамическим. Так, когда пользователь меняет название предмета: Физика, Математика и т.д., в формуле изменяется соответствующий атрибут столбца с данными.
Чтобы сделать это, нам потребуется функция MATCH (ПОИСКПОЗ) в качестве аргумента отвечающего за порядковый номер колонки.
Так будет выглядеть формула для двухфакторного поиска:
=VLOOKUP($G$5,$A$4:$E$12,MATCH($H$4,$A$3:$E$3,0),0) – английская версия
=ВПР($G$5;$A$4:$E$12;ПОИСКПОЗ($H$4;$A$3:$E$3;0);0) – русская версия
В формуле выше используется функция MATCH (ПОИСКПОЗ) , которая отвечает за порядковый номер колонки с данными. MATCH (ПОИСКПОЗ) принимает название учебного предмета как значение поиска (в ячейке “H4”) и возвращает его позицию в диапазоне ячеек “A2:E2”.
Если указать “Математика”, Excel вернет “2”, поскольку “Математика” находится в ячейке “B2” (вторая по счету в этом массиве).
Пример 3. Используем выпадающий список при двух факторном поиске
Используя способ из примера №2, нам требуется делать много ручной работы. Высок риск допустить ошибку и потратить много времени, особенно, если вы работаете с большим объемом данных.
Здесь лучше использовать выпадающие списки как значения для поиска (в нашем примере это фамилии студентов и учебные предметы).
Основываясь на тех данных, что вы выберете из выпадающего списка, функция автоматически выведет необходимые данные, как это указано на примере ниже:
Такой подход позволяет создать дашборд, с помощью которого вы легко сможете обрабатывать большие списки с данными.
Ниже мы рассмотрим, как сделать такую таблицу.
Для этого нам потребуется формула из Примера №2:
=VLOOKUP($G$5,$A$4:$E$12,MATCH($H$4,$A$3:$E$3,0),0) – английская версия
=ВПР($G$5;$A$4:$E$12;ПОИСКПОЗ($H$4;$A$3:$E$3;0);0) – русская версия
Данные поиска были преобразованы в выпадающие списки.
Чтобы сделать выпадающий список, нужно:
- Выбрать ячейку, в которой вы хотите создать выпадающий список. В нашем примере в ячейке “G4” мы указали фамилии студентов.
- Перейти к вкладке Данные -> Проверка данных.
- В диалоговом окне «Проверка данных» на вкладке «Настройки» выбрать «Список» и в раскрывающемся списке выберите «Разрешить».
- В источнике выбрать $A$3:$A$10
- Нажать «ОК».
Теперь у вас появится раскрывающийся список в ячейке “G4”. Аналогично, вы можете создать его в “H3” для предметов.
Пример 4. Трех факторный поиск данных ВПР
Что такое трех факторный поиск?
В Примере 2 мы использовали одну таблицу с оценками для студентов по разным предметам. Это пример двухфакторного поиска, поскольку мы используем две переменные для получения оценки (фамилия студента и предмет).
Теперь предположим, что к концу года студент прошел три уровня экзаменов: «Вступительный», «Полугодовой» и «Финальный экзамен».
Трехсторонний поиск – это возможность получить отметки студента по предмету с определенным уровнем экзамена.
Вот пример трехстороннего поиска:
В приведенном выше примере, функция ищет данные в трех разных таблицах с данными («Вступительный», «Полугодовой» и «Финальный экзамен») и возвращает значения оценок студента по определенному предмету по конкретному уровню экзамена.
Для таких расчетов нам поможет формула:
=VLOOKUP(G5,CHOOSE(IF(H3=”Вступительный”,1,IF(H3=”Полугодовой”,2,3)),$A$4:$E$12,$A$16:$E$24,$A$28:$E$36),MATCH(H4,$A$3:$E$3,0),0) – английская версия
=ВПР(G5;ВЫБОР(ЕСЛИ(H3=”Вступительный”;1;ЕСЛИ(H3=”Полугодовой”;2;3));$A$4:$E$12;$A$16:$E$24;$A$28:$E$36);ПОИСКПОЗ(H4;$A$3:$E$3;0);0) – русская версия
Эта формула использует функцию CHOOSE (ВЫБОР), чтобы убедиться, что данные выбраны из правильной таблицы. Давайте проанализируем часть формулы CHOOSE (ВЫБОР):
CHOOSE(IF(H3=”Вступительный”,1,IF(H3=”Полугодовой”,2,3)),$A$4:$E$12,$A$16:$E$24,$A$28:$E$36),MATCH(H4,$A$3:$E$3,0),0) – английская версия
ВЫБОР(ЕСЛИ(H3=”Вступительный”;1;ЕСЛИ(H3=”Полугодовой”;2;3));$A$4:$E$12;$A$16:$E$24;$A$28:$E$36);ПОИСКПОЗ(H4;$A$3:$E$3;0);0) – русская версия
Первый аргумент формулы (IF(H3=”Вступительный”,1,IF(H3=”Полугодовой”,2,3) или (ЕСЛИ(H3=”Вступительный”;1;ЕСЛИ(H3=”Полугодовой”;2;3) проверяет ячейку “H3” и определяет, на какой уровень экзамена ссылаются. Если это “Вступительный”, функция возвращает данные из диапазона $A$4:$E$12, который содержит оценки для вступительного экзамена.
Если выбран уровень “Полугодовой”, то система возвращает данные из диапазона $A$16:$E$24, в другом случае она возвращает данные из диапазона $A$28:$E$36.
Такой подход делает массив таблиц динамическим и трех факторным.
Пример 5. Получаем последнее значение колонки с помощью функции VLOOKUP (ВПР)
С помощью ВПР вы можете вычислить последнее число из списка.
Наибольшее положительное число, которое вы можете использовать в Excel, равно 9.99999999999999E + 307 . Это также означает, что самый большой номер поиска в номере VLOOKUP также 9.99999999999999E + 307 .
Я не думаю, что вам когда-нибудь понадобится какой-то расчет, в котором участвует такое большое число. Но с его помощью мы можем получить последнее число в списке.
Предположим, у вас есть набор данных (в диапазоне ячеек A1:A14), как показано ниже, и вы хотите получить число из последней ячейки в списке.
Для этого нам подойдет следующая формула:
=VLOOKUP(9.99999999999999E+307,$A$1:$A$14,TRUE) – английская версия
=ВПР(9.99999999999999E+307;$A$1:$A$14;ИСТИНА)
Обратите внимание, что в формуле выше используется приблизительная точность совпадения данных (несмотря на это нет необходимости упорядочивать список).
Вот как работает функция с приблизительной точностью совпадения данных. Она просматривает левую колонку сверху вниз.
- Если он находит точное совпадение, она возвращает это значение;
- Если она находит число, превышающее значение поиска => возвращает число в ячейке над ним;
- Если значение поиска больше всех чисел в списке, функция возвращает последнее число из списка.
В нашем примере работает третий сценарий.
Число 9.99999999999999E + 307 это самое большое число, которое можно использовать в Excel, когда оно используется как критерий поиска, тогда функция VLOOKUP (ВПР) возвращает последнее число из списка.
Таким же образом вы можете использовать этот принцип для возврата последнего текстового элемента из списка. Вот формула, которая может это сделать:
=VLOOKUP(“яяя”,$A$1:$A$14,1,TRUE) – английская версия
=ВПР(“яяя”;$A$1:$A$14;1;ИСТИНА) – русская версия
Здесь действует та же логика. Система просматривает все названия из списка. Так как “яяя” самое большое текстовое значение => результатом вычисления будет самое крайнее значение из списка данных.
Пример 6. Частичный поиск с использованием символов подстановочных знаков и ВПР
Символы подстановки в Excel могут быть полезными во многих ситуациях.
Частичный поиск необходим, когда вам нужно искать значение в списке, в котором нет точного соответствия искомым данным.
Например, у вас есть набор данных, как показано ниже, и вы хотите найти компанию “ABC” в списке, но в списке есть только “ABC Ltd” вместо “ABC”.
Вы не можете использовать “ABC” в качестве значения поиска, так как в столбце “A” нет точного соответствия. Приблизительное совпадение часто приводит к ошибочным результатам и требует сортировки списка в порядке возрастания.
Однако, вы можете использовать подстановочный знак в VLOOKUP (ВПР) для более точного соответствия данных.
Введите следующую формулу в ячейку “D2” и перетащите ее в другие ячейки:
Как это работает?
В формуле, указанной выше мы добавили к значению поиска “звездочки” с двух сторон. Указывая такие “звездочки” вы даете Excel понять, что готовы осуществить поиск, при котором в искомых данных как в начале так и в конце значения могут быть любые другие слова, буквы или цифры.
Таким образом, осуществляя поиск, Excel будет понимать, что необходимо искать значение, которое содержит слово из ячейки “С2” (в нашем примере).
Например, в ячейке “C2” указано название компании “ABC”, поэтому функция просматривает значения в диапазоне данных “A2:A8” и ищет “ABC”. Она находит соответствие в ячейке “A2”, так как та содержит слово “ABC” в “ABC Ltd”. Не имеет значения, есть ли какие-либо символы слева или справа от “ABC”.
Примечание: ВПР всегда возвращает первое совпадающее значение и перестает искать дальше. Поэтому, если у вас есть название компании “ABC Ltd”. и “ABC Corporation” в списке, она вернет первый по порядку и проигнорирует остальные.
Пример 7. Функция ВПР возвращает ошибку, несмотря на совпадение в значении поиска
Это может свести вас с ума, когда вы увидите, что имеется соответствующее значение поиска, а функция возвращает ошибку.
Например, у вас в таблице есть очевидное соответствие (Иван), но система по-прежнему возвращает ошибку.
Рассматривая значения в таблице глубже, вы можете заметить, что в диапазоне данных значение “Иван “ написано с пробелом. Если в диапазоне данных по которым вы осуществляете поиск есть пробелы до значений, после или между словами, то функция их не сопоставит.
Решение есть, и зовут его – функция TRIM. Она удаляет все лишние пробелы в значениях.
Ниже формула, которая поможет вам сопоставить данные несмотря на лишние пробелы в диапазоне данных.
=VLOOKUP(“Иван”,TRIM($A$2:$A$15),1,0) – английская версия
=ВПР(“Иван”,СЖПРОБЕЛЫ($A$2:$A$15);1;0) – русская версия
Для того, чтобы функция TRIM (СЖПРОБЕЛЫ) заработала при использовании VLOOKUP (ВПР) , следует вводить формулу после набора не с помощью клавиши Enter на клавиатуре, а с помощью сочетания клавиш Ctrl + Shift + Enter.
Пример 8. Функция ВПР в Excel с несколькими условиями
Функция в своей базовой форме может искать одно значение поиска и возвращать соответствующие данные из указанного диапазона данных.
Но часто нам необходимо использовать VLOOKUP с поиском по несколькими критериям.
Представим, что у вас есть таблица с результатами экзаменов студентов по трем уровням экзамена: «Вступительный», «Полугодовой» и «Финальный экзамен»:
Сопоставить данные по конкретному студенту, предмету и уровню экзамена с помощью функции ВПР – нелегкая задача, так как сопоставляя данные по студенту и предмету, она будет выдавать результат первого совпадения, но далеко не факт, что этот результат будет корректным и соответствовать необходимому уровню экзамена.
Осуществить поиск по студенту + названию предмета + уровню экзамена можно с помощью создания вспомогательного столбца, как показано на примере ниже:
Теперь, нам необходимо создать уникальный ID для каждого студента и уровня экзамена с помощью формулы во вспомогательном столбце: =A2&”|”&B2 .
Протяните эту формулу в каждую ячейку столбца данной таблицы. Таким образом мы создадим уникальный ID, по которому мы можем сопоставлять данные.
Благодаря вспомогательному столбцу мы избежали того, что при сопоставлении данных, функция будет выдавать данные по не нужному уровню экзамена.
Теперь вы можете использовать значения вспомогательного столбца в качестве значений поиска.
Ниже формула, с помощью которой удобно сопоставлять данные из вашей таблицы в список студентов, распределенных по уровню экзамена.
С помощью этой формулы мы объединили имя студента и уровень экзамена, чтобы получить значение поиска которое мы проверяем его во вспомогательном столбце и получить данные.
Пример 9. Обработка ошибок с помощью функции ВПР
Функция возвращает ошибку каждый раз, когда не может сопоставить значение поиска в заданном диапазоне данных.
В Excel есть возможность заменить сообщения об ошибке на заданное вами текстовое или числовое значение типа “Нет”, “Не доступно”, “Нет данных” и.т.д.
На примере ниже, мы пытаемся сопоставить результаты экзамена студента Ивана из таблицы. Но так как данных по Ивану в таблице нет, функция выдает ошибку.
Для того, чтобы заменить сообщение ошибки на какое-то более формальное значение нам поможет функция ЕСЛИОШИБКА(IFERROR) :
=IFERROR(VLOOKUP(D2,$A$2:$B$7,2,0),”Нет данных”) – английская версия
=ЕСЛИОШИБКА(ВПР(D2;$A$2:$B$7;2;0);”Нет данных”) – русская версия
Функция ЕСЛИОШИБКА(IFERROR) проверяет, если в результате вычислений по формуле выдается ошибка, то она выдает, то значение, которое вы указали в функции ЕСЛИОШИБКА(IFERROR), например “Нет данных”.
Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!