ВПР excel: подробная инструкция по использованию функции

Синтаксис

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

Элемент — может быть числовым (адрес ячейки) или текстовым («текст»).

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

Номер столбца — принимает целое число из диапазона от 1 до n, из него будет извлечен результат.

Интервальный поиск — приблизительное (наиболее близкое) соответствие критерию обозначается как 1 (истина), а точное соответствие обозначается как 0 (ложь). Этот логический аргумент является необязательным, если таблица отсортирована от минимального до максимального значения. Если таблица не отсортирована и аргумент опущен, это оценивается как true.

Важно! Значение для поиска должно быть слева (в первом столбце) от возвращаемого элемента.

В русифицированной версии Excel аргументы вводятся через знак «;», в английской — через запятую.

Получаем результат ВПР

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

Формула работала для одной строки.

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

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

Вот результат: мы настроили ВПР за несколько минут и она перевела цены в одно мгновение

Видео — «Быстрый перенос данных с помощью функции ВПР в Экселе»

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

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

Обычно возникает следующая ситуация. Вы отправляете заказ поставщику, через некоторое время получаете ответ в виде накладной и сверяете заказ с накладной.

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

Особенности использования формулы ВПР в Excel

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

1. Первую функцию можно считать общей для функций, которые используются для многих ячеек, написав формулу в одной из них, а затем скопировав ее в остальные. Здесь приходится учитывать относительность и абсолютность отсылок. Особенно в ВПР критерий (первое поле) должен иметь относительную ссылку (без знака $), так как каждая ячейка имеет свой собственный критерий. А вот поле «Таблица» должно иметь абсолютную ссылку (адрес поля пишется через $). Если этого не сделать, область будет «уходить вниз» при копировании формулы, и многие значения просто не будут найдены, так как искать будет негде.
2. Номер столбца, введенный в третье поле «Номер_столбца» при использовании мастера функций, должен считаться, начиная с самого критерия.
3. Функция ВПР из диапазона искомых данных возвращает первое значение сверху. Это значит, что если во второй таблице, куда мы пытаемся «подтянуть» какие-то данные, есть несколько ячеек с одинаковыми критериями, ВПР захватит первое значение сверху в пределах выбранного диапазона. Это следует помнить. Например, если мы хотим прибавить количество из другой таблицы к цене товара, а там этот товар встречается несколько раз (в нескольких строках), то к цене будет прибавлено первое сверху количество.
4. Должен быть установлен последний параметр в формуле, равный 0 (ноль). В противном случае формула может работать неправильно.
5. После использования ВПР лучше сразу удалить саму формулу, оставив только полученные значения. Это делается очень легко. Выделяем область с полученными значениями, нажимаем «копировать» и приклеиваем значения на то же место с помощью специального клея. Если таблицы расположены в разных книгах Excel, очень удобно разорвать внешние ссылки (и оставить вместо них только значения) с помощью специальной команды, расположенной по пути Данные → Изменить ссылки.

После вызова функции разрыва внешних ссылок появится диалоговое окно, в котором нужно нажать кнопку «Разорвать ссылку», а затем «Закрыть».

Это удалит все внешние ссылки сразу.

Что делать, если нужен поиск сразу по двум критериям

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

Например, у нас есть несколько похожих моделей разного цвета.

Таблица цен на модели в разных цветах

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

Здесь вы должны перевести цены на автомобили

Если мы пойдем по классическому пути ВПР, мы получим следующую функцию: fx=ВПР(A29;’автокаталог’!$A$29:$E$35;5;0). В таком виде ВПР найдет первую подходящую модель и увеличит стоимость. Параметр цвета учитываться не будет.

Соответственно цены на все Nissan Juke составят 1 850 000 рублей, на все Subaru Forester — 3 190 000 рублей, на все Toyota C-HR — 2 365 000 рублей.

Такой результат получится, если использовать обычную функцию ВПР

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

Добавляем оператор IF(‘автокаталог’!$B$29:$B$35=B29, где:

• ‘каталог автомобилей’!$B$29:$B$35 — фиксированный цветовой диапазон автомобилей в таблице, из которой должны быть перенесены данные. Это вся колонка цен.
• B29 — желаемое значение цвета автомобиля в таблице, куда мы переносим данные. Это первая ячейка в столбце с цветом, дополнительным параметром поиска.

В результате получается функция: fx=ВПР(A29;ЕСЛИ(‘автокаталог’!$B$29:$B$35=B29;’автокаталог’!$A$29:$E$35);5;0). Теперь значения цены передаются корректно.

Так выглядит таблица, в которой функция ВПР передает данные на основе двух совпадений

Как использовать функцию ВПР в Google Таблицах? У них также есть функция Vlookup, но нет окна построителя формул. Так что приходится вводить вручную. Перечислите все аргументы через точку с запятой и не забудьте исправить диапазон. Чтобы исправить это, поставьте знак доллара перед каждым символом. В готовой формуле это будет выглядеть так: =ВПР(A2;’Лист1′!$A$2:$C$5;3;0).

Автозаполнение

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

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

• В нашем случае искомое значение равно A2. Это относительная ссылка на ячейку, поскольку в ней нет символов «$». Из-за этого ссылка на искомое значение меняется относительно каждой строки, когда автозаполнение происходит в других ячейках: A2 → A3 →… → A11. Это удобно, когда вам нужно повторить формулу на нескольких строках, потому что вам не придется вводить ее заново.
• Таблица фиксируется абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменяются во время автозаполнения. Таким образом, расчет будет правильным каждый раз и на основе таблицы.

Примеры использования ВПР в Excel

Пример 1. Ищем результат экзамена для студента

В приведенном ниже примере список студентов показан в левой колонке таблицы. Окончательные результаты экзаменов этих студентов показаны в правой части таблицы.

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

Мы можем узнать из формулы ниже:

=ВПР(«Петров»,$A$3:$E$10,3,0) — английская версия

=ВПР(«Петров»,$A$3:$E$10,3,0) – Русская версия

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

• «Петров» — фамилия, по которой ищем данные;
• $A$3:$E$10 — область данных с результатами экзамена;
• «3» – порядковый номер столбца диапазона данных со значением физической точки;
• «0» — точное совпадение с желаемым значением.

Ниже наглядно показано, как работает наша функция на примере.

Сначала он ищет в крайнем левом углу области данных фамилию «Петров». Поиск идет сверху вниз.

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

При таком подходе можно получить все данные по каждому ученику и предмету.

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

=ВПР(«Пески»,$A$20:$E$28,4,0) – английская версия

=ВПР(«Песков»,$A$20:$E$28,4,0) – Русская версия

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

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

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

Пример 2. Двухфакторный поиск данных

В приведенном выше примере мы вручную указали порядковый номер столбца данных в качестве атрибута «column_number.

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

Ниже приведен пример двухфакторного поиска:

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

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

Вот так будет выглядеть формула двухфакторного поиска:

=ВПР($G$5,$A$4:$E$12,ПОИСКПОЗ($H$4,$A$3:$E$3,0),0) — английская версия

=ВПР($G$5;$A$4:$E$12;ПОИСКПОЗ($H$4;$A$3:$E$3;0);0) — Русская версия

В приведенной выше формуле используется функция ПОИСКПОЗ, которая отвечает за порядковый номер столбца данных. ПОИСКПОЗ принимает имя субъекта в качестве значения поиска (в ячейке «H4») и возвращает его позицию в диапазоне ячеек «A2:E2”.

Если вы укажете «Математика», Excel вернет «2», потому что «Математика» находится в ячейке «B2» (вторая в этом массиве).

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

Используя метод из примера №2, нам приходится делать много ручной работы. Велик риск ошибиться и потерять много времени, особенно если вы работаете с большими объемами данных.

Здесь в качестве значений поиска лучше использовать выпадающие списки (в нашем примере это имена учеников и предметов).

На основе данных, выбранных вами из выпадающего списка, функция автоматически отобразит необходимые данные, как показано в примере ниже:

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

Ниже мы рассмотрим, как сделать такой стол.

Для этого нам понадобится формула из примера №2:

=ВПР($G$5,$A$4:$E$12,ПОИСКПОЗ($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 мы использовали одну таблицу с оценками учащихся по разным предметам. Это пример двухфакторного поиска, поскольку мы используем две переменные для получения оценки (имя учащегося и предмет).

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

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

Вот пример трехстороннего поиска:

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

Для таких расчетов нам поможет формула:

=ВПР(G5,ВЫБРАТЬ(ЕСЛИ(H3=»Вводный»,1,ЕСЛИ(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);MATCH(H4;$A$3:$E$3;0);0) — Русская версия

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

ВЫБЕРИТЕ (ЕСЛИ (H3 = «Вводный», 1, ЕСЛИ (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) ;MATCH(H4;$A$3:$E$3;0);0) — Русская версия

Первый аргумент в формуле: (IF(H3=»Вводный»,1,IF(H3=»Полугодовой»,2,3) или (IF(H3=»Вводный»));1;IF(H3=» Полугодовой»;2;3) проверяет ячейку «H3» и определяет, к какому уровню экзамена она относится. Если это «Вводный», функция возвращает данные из диапазона $A$4:$E$12, который содержит оценки за вступительный экзамен.

Если выбран параметр «Полугодовой», система возвращает данные из диапазона $A$16:$E$24, в противном случае она возвращает данные из диапазона $A$28:$E$36.

Такой подход делает выбор таблиц динамическим и трехфакторным.

Пример 5. Получаем последнее значение колонки с помощью функции VLOOKUP (ВПР)

С ВПР вы можете вычислить последнее число из списка.

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

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

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

Для этого нам понадобится следующая формула:

=ВПР(9.999999999999999E+307,$A$1:$A$14,ИСТИНА) — английская версия

=ПОИСК(9.999999999999999E+307,$A$1:$A$14,ИСТИНА)

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

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

• Если он находит точное совпадение, он возвращает это значение;
• Если он находит число больше, чем значение поиска => возвращает число в ячейке над ним;
• Если искомое значение больше, чем все числа в списке, функция возвращает последнее число в списке.

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

Число 9.99999999999999E + 307 — это наибольшее число, которое можно использовать в Excel при использовании в качестве критерия поиска, поэтому функция SKIP UP (ПОИСК ВВЕРХ) возвращает последнее число из списка.

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

=ВПР(«ура»,$A$1:$A$14,1,ИСТИНА) — английская версия

=ВПР(«яя»,$A$1:$A$14,1,ИСТИНА) — Русская версия

Здесь применима та же логика. Система просматривает все заголовки в списке. Так как «yay» является самым большим текстовым значением => результатом вычисления будет самое экстремальное значение из списка данных.

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

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

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

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

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

Однако вы можете использовать подстановочный знак в ВПР (VLOOKUP) для более точного сопоставления данных.

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

=VLOOKUP(«*»&C2&»*»,$A$2:$A$8,1,FALSE) — английская версия

=ВПР(«*»&C2&»*»;$A$2:$A$8;1;FALSE) — Русская версия

Как это работает?

В приведенной выше формуле мы добавили звездочки с обеих сторон от поискового значения. Указав такие «звездочки», вы даете 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) — английская версия

=ВПР(«Иван»,FLATSPACES($A$2:$A$15),1,0) – русская версия

Чтобы функция TRIM работала при использовании ВПР (VLOOKUP), необходимо вводить формулу после набора не клавишей Enter на клавиатуре, а горячей клавишей Ctrl+Shift+Enter.

Пример 8. Функция ВПР в Excel с несколькими условиями

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

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

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

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

Вы можете искать по учащемуся + названию курса + уровню экзамена, создав столбец справки, как показано в примере ниже:

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

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

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

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

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

=ВПР($F3&”|”&G$2,$C$2:$D$19,2,0) — английская версия

=ВПР($F3&”|”&G$2;$C$2:$D$19;2;0) – Русская версия

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

Читайте также: Как создать электронную почту на компьютере и телефоне