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

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

Что такое функция ВПР в Эксель – область применения

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

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

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

Примеры для наглядности: в таблицах 1,2 – исходные данные, таблице 3 – что должно получиться.

Исходные данные таблица 1

Объединенные данные таблица 3

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

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

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

Необходимо заполнить значения для функции ВПР

Результат налицо – в таблице 3 (смотреть выше).

ВПР – инструкция для работы с двумя условиями

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

Пример, необходимо в таблицу 4, вставить цену из таблицы 5.

Характеристики телефонов таблица 4

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

Добавляем крайний левый столбец. Например, называем его «Объединение». В первую ячейку значений, у нас B 2, пишем конструкцию «= B 2& C 2». Размножаем с помощью мыши. Получается, как в таблице 6.

Характеристики телефонов таблица 6

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

Смотрите видеоурок как пользоваться функцией ВПР в Эксель для чайников:

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

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

Кликните по кнопке ниже для загрузки Excel файла с примерами работы функции ВПР :

Видео-урок: Как использовать функцию ВПР в Excel

Функция ВПР в Excel для чайников

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

Синтаксис

=VLOOKUP(lookup_value, table_array, col_index_num, ) – английская версия

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

Аргументы функции

• lookup_value (искомое_значение) – это величина для поиска, из крайнего левого столбца таблицы. Это может быть значение, ссылка на ячейку или текстовая строка. В примере со студентами это их фамилии;
• table_array (таблица) – это диапазон данных, в котором будет осуществлен поиск. Это может быть ссылка на диапазон ячеек или именованный диапазон. В примере с таблицей со студентами, это будет вся таблица, которая содержит оценку и фамилии студентов;
• col_index (номер_столбца) – это порядковый номер столбца в диапазоне с данными, из которого будет получена искомая величина;
• ([интервальный_просмотр]) – этот аргумент указывает на точность совпадения данных при поиске. Укажите “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. Двухфакторный поиск данных

На примере выше мы указывали вручную в качестве атрибута “номер_столбца” порядковый номер колонки с данными.

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

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

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

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

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

– английская версия

– русская версия

В формуле выше используется функция , которая отвечает за порядковый номер колонки с данными. принимает название учебного предмета как значение поиска (в ячейке “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” и перетащите ее в другие ячейки:

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

=ВПР(“*”&C2&”*”;$A$2:$A$8;1;ЛОЖЬ) – русская версия

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

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

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

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

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

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

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

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

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

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

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

Пример 9. Обработка ошибок с помощью функции ВПР

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

В Excel есть возможность заменить сообщения об ошибке на заданное вами текстовое или числовое значение типа “Нет”, “Не доступно”, “Нет данных” и.т.д.

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

Для того, чтобы заменить сообщение ошибки на какое-то более формальное значение нам поможет функция

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

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

Как работает функция ВПР в Excel: пример

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

В поле «Исходное значение» вводим ссылку на ячейку под наименованием товара второй таблицы D3. В поле «Таблица» вводим диапазон всех значений первой таблицы A2:B7. В поле «Номер столбца» вводим значение 2, так как во втором столбце у нас находиться цена, которую мы хотим получить при поиске товара. И нажимаем ОК.

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

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

Функция ВПР в Excel и две таблицы

Немного усложним задание, изменив структуру и увеличив объем данных в таблице. Расширьте объем данных первой таблицы, добавив столбцы: «январь», «февраль», «март». Там запишем суммы продаж в первом квартале как показано на рисунке:

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

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

1. Исходное значение: G3.
2. Таблица: A2:E7. Диапазон нашей таблицы расширен.
3. Номер столбца: {3;4;5}. Нам нужно с помощью функции обращаться одновременно к нескольким столбцам, поэтому значение данного аргумента будет взято в массив фигурными скобками. А номера столбцов следует перечислять через точку с запятой.
4. Интервальный просмотр: ЛОЖЬ.
5. Чтобы значения в выбранных столбцах суммировались, тогда всю функцию нужно поместить внутрь функции СУММ(). Вся формула в целом выглядит следующим образом: =СУММ(ВПР(G3;A3:E6;{3;4;5};ЛОЖЬ)).
6. После ввода данной формулы следует нажать комбинацию клавиш: CTRL+SHIFT+ENTER. Внимание! Если не нажать комбинацию этих клавиш формула будет работать ошибочно . В Excel иногда приходиться выполнять функции в массиве для этого нужно обязательно использовать клавиши: CTRL+SHIFT+ENTER при вводе функций. Тогда в строке формул все содержимое будет взято в фигурные скобки «{}», что свидетельствует о выполнении формулы в массиве.

Теперь вводите в ячейку G3 наименование товара, в ячейке H3 получаем сумму продаж в первом квартале по данному товару.

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

Примечание. Если ввести вручную крайние фигурные скобки в строку формул то это не приведет ни ка какому результату. Выполнить функцию циклическим массивом можно только через комбинацию горячих клавиш: CTRL+SHIFT+ENTER.

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

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

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

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

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

Нажмите по верхней ячейке в первой таблице в столбце Цена, а потом кнопочку «fx» в строке формул, чтобы открыть окно мастера функций.

Следующее, что мы делаем – прописываем аргументы в предложенные поля.

Ставьте курсив в поле «Искомое_значение» и выделяйте в первой таблице то значение, которое будем искать. У меня это яблоко.

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

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

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

В «Интервальный_просмотр» пишем «ЛОЖЬ» – если искать нужно точные совпадения, или «Истина» – если значения могут быть приближенные. Для нашего примера выбираем первое. Если ничего не указать в данном поле, то по умолчанию выберется второе. Потом нажимайте «ОК» .

Здесь обратите внимание на следующее, если работаете с числами и указываете «Истина» , то вторая таблица (это наш прайс) обязательно должна быть отсортирована по возрастанию. Например, при поиске 5,25 найдется 5,27 и возьмутся данные с этой строки, хотя ниже может еще быть и число 5,2599 – но формула дальше смотреть не будет, поскольку она думает, что ниже числа только больше.

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

ВПР(А2;$G$2:$H$12;2;ЛОЖЬ)

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

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

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

При добавлении столбцов на лист, данные для аргумента «Таблица» функции автоматически изменятся. В примере прайс сдвинут на 2 столбца вправо. Выделим любую ячейку с формулой и видим, что вместо $G$2:$H$12 теперь $I$2:$J$14 .

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

В открывшемся окне «Тип данных» будет «Список» , ниже указываем область источника – это названия фруктов, то есть тот столбец, который есть и в первой и во второй таблице. Нажимайте «ОК» .

Выделяю F2 и вставляю функцию ВПР. Аргумент первый – это сделанный список (F1 ).

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

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

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

Жмем по любой ячейке в столбце D и вставляем один новый.

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

Вставляем функцию и указываем аргументы. Сначала то, что будем искать, в примере яблоко (А2 ). Для выбора диапазона из нового прайса, поставьте курсор в поле «Таблица» и перейдите на нужный лист, у меня «Лист1» .

Выделяем мышкой необходимые столбцы и строки, без заголовков.

Дальше делаем абсолютные ссылки на ячейки: «Лист1!$A$2:$B$12» . Выделите строчку и нажмите «F4» , чтобы к адресам ячеек добавился знак доллара. Указываем столбец (2 ) и пишем «ЛОЖЬ» .

В конце нажмите кнопку «ОК» .

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

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

(13 оценок, среднее: 5,00 из 5)

Вебмастер. Высшее образование по специальности "Защита информации".. Автор большинства статей и уроков компьютерной грамотности

Необходимое предисловие

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

Проблема

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

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

Стоимость по коду заказа найти легко - обычный ВПР тут поможет на раз-два. А вот как найти название товара по коду? На тренингах этот вопрос я чаще всего слышу в формулировке "а как сделать левый ВПР"?

Давайте разберем несколько способов.

Способ 1. Лобовая атака

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




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

Способ 2. Виртуальная перестановка столбцов функцией ВЫБОР

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




Ничего сверхъестественного, на первый взгляд, но тут есть пара хитрых моментов.

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

ВЫБОР(2; A1:A10 ; D1:D10 ; B1:B10 )

Во-вторых , вместо простого одиночного номера извлекаемого элемента в первом аргументе функции ВЫБОР можно задать массив констант в фигурных скобках, например, так:

ВЫБОР({1;2} ; A1:A10 ; D1:D10 ; B1:B10 )


Тогда на выходе мы получим два первых диапазона (A1:A10 и D1:D10), склеенных в единое целое.

И вот теперь все это можно вложить внутрь нашей ВПР , чтобы реализовать «левый поиск»:




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

Минусы такого способа - это скорость (примерно в 5-7 раз медленнее обычного ВПР) и некоторая непривычность для коллег (а может это даже плюс!)

Способ 3. Связка функций ИНДЕКС и ПОИСКПОЗ

Если не держаться имеено за функцию ВПР, то можно использовать ее более мощный аналог - связку двух очень полезных функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) :




Функция ПОИСКПОЗ ищет заданное значение (С2, т.е. код нужного нам заказа) в одномерном диапазоне (столбце кодов в таблице C10:C25) и выдает в качестве результата порядковый номер ячейки, где нашла искомое - в нашем случае это будет число 4, т.к. код нужного нам заказа четвертый в таблице.

А затем в дело вступает функция ИНДЕКС , которая умеет извлекать данные из вертикального массива-столбца (названия товаров в B10:B25) по порядковому номеру (который предварительно нашла ПОИСКПОЗ ). Таким образом, ИНДЕКС выдаст нам содержимое четвертой ячейки из столбца Товар , что и требовалось.

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

Похожий пример (с видео) я разбирал ранее . А уж про функцию ИНДЕКС можно говорить :)

Способ 4. Функция СУММЕСЛИ(МН)

Если нужно извлечь из таблицы именно число (допустим, объем в литрах), то иногда проще использовать для реализации "левого ВПР" функцию выборочного суммирования СУММЕСЛИ (SUMIF) или ее старшую сестру - функцию СУММЕСЛИМН (SUMIFS) :




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

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

Способ 5. Готовая макрофункция из PLEX

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

• искать по нескольким столбцам сразу (до 3)
• выдавать результаты из любого столбца (левее или правее - не важно)
• выдавать не только первое встретившееся значение, а нужное по порядку
• можно задать, что вывести, если ничего не найдено вместо ошибки #Н/Д




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

Но как один из вариантов - пойдет:)

Ссылки по теме

Отличный способ. Спасибо.
Научите: Как можно с помощью индекс и поискпозиции выбирать значения из первого столбца, когда ищешь во втором столбце, но при этом нужно во втором столбце найти значения с максимальной датой в четвертом?
На вскидку приходит функция наибольший, но куда ее вставить....)))вот такая таблица

маршрут	ид	префикс	платеж
запад2	251458	БР7652И	16.01.2017
Кашира1	252412	БР7652И	17.01.2017
С5-1	253016	БР7272И	18.01.2017
С3-1	254392	БР7652И	19.01.2017
Кашира1	255031	БР7249И	20.01.2017
Кашира1	257321	БР7759И	21.01.2017
СВ1-1	257569	БР7761И	22.01.2017
Пушкино1	259373	БР7647И	23.01.2017
Коломна1	259591	БР7315И	24.01.2017
Кашира1	260300	БР7544И	25.01.2017

вот так я ищу

префикс	ид
БР7652И	ИНДЕКС(A2:D11;ПОИСКПОЗ(D20;C2:C11;0);ПОИСКПОЗ(E19;A1:D1;0))

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