Многие знают и часто пользуются. Но при этом у нее есть два значительных недостатка, например как сделать «Левый ВПР «. Я использую сам ВПР, только если нужно сделать что-то быстро. В файлах для «регулярного» использования я делаю конструкцию ИНДЕКС и ПОИСКПОЗ. Чем она лучше?

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

Как все это сделать, читаем ниже:)

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

Как просто заполнить, если видишь формулу первый раз?

Сперва определитесь где таблица изначальная а где для получения данных! Поэтапно, что заполнять.

1. Вставляем формулу в нужную область таблицы
2. На место $G:$G поставьте те ячейки, в которых должно быть найдено значение и соответственно, должно появиться в результате. Мы ищем фамилию, значит в ищите столбец с фамилиями в начальной таблице.
3. Замените $J:$J на в зависимости от которых должны вернуться значения в ячейку. Нам нужны Фамилии в зависимости от ТС — вставляем те ТС около которых должны появиться значения.
4. Вместо $H:$H заполняем столбец с в котором нужно найти соответствующее значение. Т.е. мы ищем Фамилию по ТС, значит вставляем столбец с ТС в изначальной таблице.

Если вам интересно для чего в формуле знаки $ читайте

ИНДЕКС и ПОИСКПОЗ. Что это за функции?

ИНДЕКС и ПОИСКПОЗ — очень сильные функции, которые в комбинации с другими дают отличный результат.

ИНДЕКС(массив; номер_строки; номер_столбца)

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

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

Посмотрев на изначальную формулу

ИНДЕКС($G:$G;ПОИСКПОЗ($J:$J;$H:$H;0);1)

Мы увидим, что вместо второго аргумента (номер строки) у нас формула ПОИСКПОЗ. Что тут она делает?

ПОИСКПОЗ — это поиск по значению. Функция ищет заданное значение в строке или в столбце и возвращает ее порядковый номер (от начала диапазона). Т.е. во втором аргументе функции ИНДЕКС мы находим нужный нам номер ТС, получаем его номер, например 2.

И уже в одномерном массиве $G:$G находим ячейку с номером строки = 2. Так это будет работать для каждой ячейки в столбце J.

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

Пишите комментарии, если вопросы остались.

Как всегда!

Поделитесь нашей статьей в ваших соцсетях:

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

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

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

Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

Как найти значение в другой таблице или сила ВПР

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

и из первой таблицы необходимо подставить во вторую дату для каждой фамилии. Для трех записей это не проблема и руками сделать - все очевидно. Но в жизни это таблицы на тысячи записей и поиск с подстановкой данных вручную может занять не один час. Плюс еще пара ложек дегтя: мало того, что ФИО расположены совершенно в разном порядке в обеих таблицах и количество записей в таблицах разное, так еще таблицы расположены на разных листах/книгах. Я полагаю, что убедил вас в том, что подстановка данных руками вообще не вариант. Зато ВПР (VLOOKUP) здесь будет незаменима. При этом практически ничего не надо будет делать - только записать в первую ячейку столбца С второй таблицы(туда, куда необходимо подставить даты из первой таблицы) такую формулу:
=ВПР($A2 ;Лист1!$A$2:$C$4 ;3;0)
Записать формулу можно либо непосредственно в ячейку, либо воспользовавшись диспетчером функций , выбрав в категории Ссылки и массивы ВПР и по отдельности указав нужные критерии. Теперь копируем(Ctrl +C ) ячейку с формулой, выделяем все ячейки столбца С до конца данных и вставляем(Ctrl +V ).

Сначала основной принцип работы: ВПР ищет в первом столбце аргумента Таблица значение, указанное аргументом Искомое_значение . При нахождении нужного значения функция возвращает значение напротив найденного значения, но из столбца , указанного аргументом Номер_столбца . С интервальным просмотром разберемся чуть позже. ВПР может вернуть только одно значений - первое, подходящее под критерий. Если искомое значение не найдено(отсутствует в таблице) , то результатом функции будет #Н/Д . Не надо этого бояться - это даже полезно. Вы точно будете знать, каких записей нет и таким образом можете сравнивать две таблицы друг с другом. Иногда получается так, что Вы видите: данные есть в обеих таблицах, но ВПР выдает #Н/Д. Значит данные в Ваших таблицах не идентичны. В какой-то из них есть лишние неприметные пробелы(обычно перед значением или после), либо знаки кириллицы перемешаны со знаками латиницы. Так же #Н/Д будет, если критерии числа и в искомой таблице они записаны как текст(как правило в левом верхнем углу такой ячейки появляется зеленый треугольничек) , а в итоговой - как числа. Или наоборот.

Описание аргументов ВПР
$A2 - аргумент Искомое_значение (назовем его Критерий для краткости). Это то, что мы ищем. Т.е. для первой записи второй таблицы это будет Петров С.А. Здесь можно указать либо непосредственно текст критерия(в этом случае он должен быть в кавычках - =ВПР("Петров С.А" ;Лист1!$A$2:$C$4;3;0) , либо ссылку на ячейку, с данным текстом(как в примере функции) . Есть небольшой нюанс: так же можно применять символы подстановки: "*" и "?". Это очень удобно, если Вам надо найти значения лишь по части строки. Например, Вы можете не вводить полностью "Петров С.А", а ввести лишь фамилию и знак звездочки - "Петров*". Тогда будет выведена любая запись, которая начинается на "Петров". Если же Вам надо найти запись, в которой в любом месте строки встречается фамилия "Петров" , то можно указать так: "*петров*" . Если хотите найти фамилию Петров и неважно какие инициалы будут у имени-отчества(если ФИО записаны в виде Иванов И.И.), то здесь в самый раз такой вид: "Иванов?.?." . Часто необходимо для каждой строки указать свое значение(в столбце А Фамилии и надо их все найти). В таком случае всегда указываются ссылки на ячейки столбца А. Например, в ячейке A1 записано: Иванов. Так же известно, что Иванов есть в другой таблице, но после фамилии могут быть записаны и имя и отчество(или еще что-то). Но нам нужно найти только строку, которая начинается на фамилию. Тогда необходимо записать следующим образом: A1 &"*" . Эта запись будет равнозначна "Иванов*" . В A1 записано Иванов, амперсанд(&) используется для объединения в одну строку двух текстовых значений. Звездочка в кавычках (как и положено быть тексту внутри формулы). Таким образом и получаем:
A1&"*" =>
"Иванов"&"*" =>
"Иванов*"
Очень удобно, если значений для поиска много.
Если надо определить есть ли хоть где-то слово в строке, то звездочки ставим с обеих сторон: "*"& A1 &"*"

Лист1!$A$2:$C$4 - аргумент Таблица . Указывается диапазон ячеек. Только диапазон должен содержать данные от первой ячейки с данными до самой последней. Это не обязательно должен быть указанный в примере диапазон. Если строк 100, то Лист1!$A$2:$C$100 . Важно помнить три вещи: первое, это Таблица всегда должна начинаться с того столбца, в котором ищем Критерий . И никак иначе. В противном случае ничего найдено не будет или результат будет совсем не тот, которого ожидаете. Второе: аргумент Таблица должен быть "закреплен" . Что это значит. Видите знаки доллара - $? Это и есть закрепление(если точнее, то это называется абсолютной ссылкой на диапазон) . Как это делается. Выделяете текст ссылки(только один диапазон - один критерий) и жмете F4 до тех пор, пока не увидите, что и перед обозначением имени столбца и перед номером строки не появились доллары. Если этого не сделать, то при копировании формулы аргумент Таблица будет "съезжать" и результат опять-таки будет неверным. И последнее - таблица должна содержать столбцы от первого(в котором ищем) до последнего(из которого необходимо возвращать значения). В примере Лист1!$A$2:$C$4 - значит не получится вернуть значение из столбца D(4), т.к. в таблице только три столбца.

3 - Номер_столбца . Здесь просто указываем номер столбца в аргументе Таблица , значения из которого нам необходимо подставить в качестве результата. В примере это Дата принятия - т.е. столбец №3. Если бы нужен был отдел, то указали бы 2, а если бы нам понадобилось просто сравнить есть ли фамилии одной таблицы в другой, то можно было бы указать и 1. Важно: аргумент Номер_столбца не должен превышать кол-во столбцов в аргументе Таблица . Иначе результатом формулы будет ошибка #ССЫЛКА! . Например, если в качестве указан диапазон $B$2:$C$4 и необходимо вернуть данные из столбца С, то правильно указать 2. Т.к. аргумент Таблица ($B$2:$C$4) содержит только два столбца - В и С. Если же попытаться указать номер столбца 3(каким по счету он является на листе), то получим ошибку #ССЫЛКА! , т.к. третьего столбца в указанном диапазоне просто нет.

Практический совет: если аргумент Таблица имеет слишком большое кол-во столбцов и Вам необходимо вернуть результат из последнего столбца, то совсем необязательно высчитывать их кол-во. Можно указать так: =ВПР($A2 ;Лист1! $A$2:$C$4 ;ЧИСЛСТОЛБ(Лист1! $A$2:$C$4);0) . К слову в данном случае Лист1! тоже можно убрать, как лишнее: =ВПР($A2 ;Лист1! $A$2:$C$4 ;ЧИСЛСТОЛБ($A$2:$C$4);0) .

0 - Интервальный_просмотр - очень интересный аргумент. Может быть равен либо ИСТИНА либо ЛОЖЬ. Сразу возникает вопрос: а почему в моей формуле там 0? Все очень просто - Excel в формулах может воспринимать 0 как ЛОЖЬ, а 1 как ИСТИНА. Если в ВПР указать данный параметр равный 0 или ЛОЖЬ, то будет происходить поиск точного соответствия заданному Критерию. Это не имеет никакого отношения к знакам подстановки("*" и "?"). Если же использовать 1 или ИСТИНА(или же вообще не указывать последний аргумент, т.к. по умолчанию он равен ИСТИНА), то...Очень долгая история. Вкратце - ВПР будет искать наиболее похожее значение, подходящее под Критерий . Иногда очень полезно. Правда, если использовать данный параметр, то необходимо, чтобы список в аргументе Таблица был отсортирован по возрастанию. Обращаю внимание на то, что сортировка необходима только в том случае, если аргумент Интервальный_просмотр у Вас равен ИСТИНА или 1. Если же 0 или ЛОЖЬ - сортировка не нужна.

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

Как избежать ошибки #Н/Д(#N/A) в ВПР?
Еще частая проблема - многие не хотят видеть #Н/Д результатом, если совпадение не найдено. Это легко обойти:
=ЕСЛИ(ЕНД(ВПР($A2 ;Лист1! $A$2:$C$4 ;3;0));"";ВПР($A2 ;Лист1! $A$2:$C$4 ;3;0)))
Теперь если ВПР не найдет совпадения, то ячейка будет пустой.
А пользователям версий Excel 2007 и выше можно использовать ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ВПР($A2 ;Лист1! $A$2:$C$4 ;3;0);"")

Обещанная ПОИСКПОЗ

Данная функция ищет значение, указанное параметром Искомое_значение в аргументе Просматриваемый_массив . А результатом функции является номер позиции найденного значения в Просматриваемом_массиве . Именно номер позиции, а не само значение. В принципе её я не буду расписывать так же подробно, потому как основные моменты ровно такие же. Если бы мы хотели применить её для таблицы выше, то она была бы такой:
=ПОИСКПОЗ($A2 ;Лист1! $A$2:$A$4 ;0)
$A2 - Искомое_значение. Здесь все ровно так же, как и с ВПР. Так же допустимы символы подстановки и ровно в таком же исполнении.

Лист1! $A$2:$A$4 - Просматриваемый_массив. Основное отличие от ВПР - допускается указать массив лишь с одним столбцом. Это должен быть тот столбец, в котором мы собираемся искать Искомое_значение . Если попытаться указать более одного столбца, то функция вернет ошибку.

Тип_сопоставления(0) - то же самое, что и в ВПР Интервальный_просмотр . С теми же особенностями. Отличается разве что возможностью поиска наименьшего от искомого или наибольшего. Но на этом я не буду останавливаться в данной статье.

С основным разобрались. Но ведь нам надо вернуть не номер позиции, а само значение. Значит ПОИСКПОЗ в чистом виде нам не подходит. По крайней мере одна, сама по себе. Но если её использовать вместе с функцией ИНДЕКС - то это то, что нам нужно и даже больше.
=ИНДЕКС(Лист1! $A$2:$C$4 ;ПОИСКПОЗ($A2 ;Лист1! $A$2:$A$4 ;0);2)
Такая формула результатом вернет то же, что и ВПР.

Аргументы функции ИНДЕКС
Лист1! $A$2:$C$4 - Массив. В качестве этого аргумента мы указываем диапазон, из которого хотим получить значения. Может быть как один столбец, так и несколько. В случае, если столбец один, то последний аргумент функции указывать не надо. К слову - данный аргумент может совершенно не совпадать с тем, который мы указываем в аргументе Просматриваемый_массив функции ПОИСКПОЗ.

Далее идут Номер_строки и Номер_столбца. Именно в качестве Номера_строки мы и подставляем ПОИСКПОЗ, которая возвращает нам номер позиции в массиве. На этом все и строится. ИНДЕКС возвращает значение из Массива, которое находится в указанной строке(Номер_строки) Массива и указанном столбце(Номер_столбца), если столбцов более одного. Важно знать, что в данной связке кол-во строк в аргументе Массив функции ИНДЕКС и кол-во строк в аргументе Просматриваемый_массив функции ПОИСКПОЗ должно совпадать. И начинаться с одной и той же строки. Это в обычных случаях, если Вы не преследуете иные цели.
Так же как и в случае с ВПР, ИНДЕКС в случае не нахождения искомого значения возвращает #Н/Д. И обойти подобные ошибки можно так же:
Для всех версий Excel(включая 2003 и раньше):
=ЕСЛИ(ЕНД(ПОИСКПОЗ($A2 ;Лист1! $A$2:$A$4 ;0));"";ИНДЕКС(Лист1! $A$2:$C$4 ;ПОИСКПОЗ($A2 ;Лист1! $A$2:$A$4 ;0);2))
Для версий 2007 и выше:
=ЕСЛИОШИБКА(ИНДЕКС(Лист1! $A$2:$C$4 ;ПОИСКПОЗ($A2 ;Лист1! $A$2:$A$4 ;0);2);"")

Работа с критериями длиннее 255 символов
Есть у ИНДЕКС-ПОИСКПОЗ и еще одно преимущество перед ВПР. Дело в том, что ВПР не может искать значения, длина строки которых содержит более 255 символов . Это случается редко, но случается. Можно, конечно, обмануть ВПР и урезать критерий:
=ВПР(ПСТР($A2 ;1;255);ПСТР(Лист1!$A$2:$C$4 ;1;255);3;0)
но это формула массива . Да и к тому же далеко не всегда такая формула вернет нужный результат. Если первые 255 символов идентичны первым 255 символам в таблице, а дальше знаки различаются - формула этого уже не увидит. Да и возвращает формула исключительно текстовые значения, что в случаях, когда возвращаться должны числа, не очень удобно.

Поэтому лучше использовать такую хитрую формулу:
=ИНДЕКС(Лист1!$A$2:$C$4 ;СУММПРОИЗВ(ПОИСКПОЗ(ИСТИНА;Лист1!$A$2:$A$4 =$A2 ;0));2)
Здесь я в формулах использовал одинаковые диапазоны для удобочитаемости, но в примере для скачивания они различаются от указанных здесь.
Сама формула построена на возможности функции СУММПРОИЗВ преобразовывать в массивные вычисления некоторых функций внутри неё. В данном случае ПОИСКПОЗ ищет позицию строки, в которой критерий равен значению в строке. Подстановочные символы здесь применить уже не получится.

В прилагаемом к статье примере Вы найдете примеры использования всех описанных случаев и пример того, почему ИНДЕКС и ПОИСКПОЗ порой предпочтительнее ВПР.

Скачать пример

(26,0 KiB, 13 776 скачиваний)

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

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

Итак, мы с вами рассмотрим четыре варианта создания подстановочной функции с двумя условиями:

1. Использование функции СУММПРОИЗВ

Ну а начнем мы с вами с самого простого.

Использование дополнительной колонки

В большинстве случаев сложные проблемы становятся проще и более управляемыми, если их разбить на маленькие кусочки. Тоже самое касается при построении формул в Excel.

Рассмотрим классический пример. У нас имеется таблица с продажами по месяцам и городам. И нам необходимо определить значение продаж, соответствующее двум условиям: месяц – Февраль и город – Самара.

Использование функции ВПР в классическом виде нам не поможет, так как она сможет вернуть значение, соответствующее только одному условию. Из положения нам поможет выйти дополнительный столбец, в котором мы объединим значения столбцов Месяц и Город. Для этого в ячейке А2 прописываем формулу =B2&C2 и протягиваем данную формулу до ячейки А13. Теперь мы сможем использовать значения столбца А, чтобы вернуть необходимое значение. Прописываем в ячейке G3 формулу:

ВПР(G1&G2;A2:D13;4;0)

Данная формула объединяет два условия ячеек G1 и G2 в одну строку и просматривает его в столбце А. После того, как нужное условие было обнаружено, формула возвращает значение с четвертого столбца таблицы A1:D13, т.е. столбца Продажи.

Использование функции ВЫБОР для создания новой таблицы просмотра

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

Использование функции ВЫБОР подразумевает создание новой таблицы для просмотра, в котором значения столбцов Месяц и Город уже объединены. Наша формула будет выглядеть следующим образом:

ВПР(G1&G2;ВЫБОР({1;2};B2:B13&C2:C13;D2:D13);2;0)

Основной момент данной формулы заключается в части ВЫБОР({1;2};B2:B13&C2:C13;D2:D13), который делает две вещи:

1. Объединяет значения столбцов Месяц и Город в один массив: ЯнвМосква, ФевМосква …
2. Объединяет два массива в таблицу, состоящую из двух столбцов.

Результатом работы данной функции будет таблица, которая выглядит следующим образом:

Теперь формула стала более понятной.

ВАЖНО: Так как мы использовали формулу массива, по окончании ввода формулы нажмите Ctrl+Shift+Enter, чтобы дать знать программе о наших намерениях. После нажатия данной комбинации клавиш, программа автоматически установит фигурные скобки в начале и в конце формулы.

Использование функций ИНДЕКС и ПОИСКПОЗ

Третий способ, который мы с вами рассмотрим, также предполагает использование формулы массива и задействует функции ИНДЕКС и ПОИСКПОЗ.

Формула будет выглядеть следующим образом.

ИНДЕКС(D2:D13;ПОИСКПОЗ(1;(B2:B13=G1)*(C2:C13=G2);0))

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

Сначала рассмотрим функцию ПОИСКПОЗ(1;(B2:B13=G1)*(C2:C13=G2);0). В данном случае последовательно сравнивается значение ячейки G1 с каждым значением ячеек диапазона B2:B13 и возвращается ИСТИНА, если значения совпадают и ЛОЖЬ, если нет. Такое же сравнение производится со значением ячейки G2 и диапазоном C2:C13. Далее мы сравниваем оба эти массива, состоящих из ИСТИНА и ЛОЖЬ. Комбинация ИСТИНА * ИСТИНА дает нам результат 1 (ИСТИНА). Давайте посмотрим на картинку ниже, которая поможет объяснить принцип работы более наглядно.

Теперь мы можем сказать, где находится строка, удовлетворяющая обоим условиям. Функция ПОИСКПОЗ отыскивает положение 1 в результирующем массиве и возвращает 6, так как единица встречается в шестой строке. Далее функция ИНДЕКС возвращает значение шестой строки диапазона D2:D13.

Использование СУММПРОИЗВ

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

СУММПРОИЗВ((B2:B13=G1)*(C2:C13=G2);D2:D13)

Принцип работы данной формулы схож с принципом работы предыдущего подхода. Создается виртуальная таблица, в которой сравниваются значения ячеек G1 и G2 с диапазонами B2:B13 и С2:С13 соответственно. Далее оба этих массива сопоставляются и получается массив из единиц и нулей, где единица присваивается той строке, в которой оба условия совпали. Далее данный виртуальный массив перемножается на диапазон D2:D13. Так как в нашем виртуальном массиве будет только одна единица в шестой строке, формула вернёт результат 189.

Данная функция не будет работать, если в диапазоне D2:D13 имеются текстовые значения.

Чтобы понять, как работает данная формула, рекомендую прочитать статью о функции СУММПРОИЗ.

ИТОГ

Итак, какой же способ использовать? Хотя все они работают стабильно, я предпочитаю первый способ. В своей ежедневной работе, я предпочитаю работать с файлами, которые просты для понимания и поддаются изменениям. Оба эти требования отвечают условиям первого подхода.

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

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

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

Итак, чтобы решить задачу в нашем примере нам необходимо сначала заполнить столбец «С» в таблице заказов, т.е найти цену товаров в таблице «Прайс лист», а затем, чтобы узнать стоимость — перемножить цену на количество товаров.

Синтаксис функции ВПР (VLOOKUP) в Excel

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

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

Разберем функцию на нашем примере. Вставим курсов в ячейку C3 и наберем формулу =ВПР(A3;$F$2:$H$22;3;0)

в английской версии =VLOOKUP (A3;$F$2:$H$22;3;0)

В данной формуле с ВПР (англ. VLOOKUP):

A3 – искомое_значение . В нашем случае это «Ведро», т.е нам необходимо найти «ведро» в таблице «Прайс лист»

$F$2:$H$22 – таблица . В нашем примере это таблица «Прайс лист» (F2:H22). В диапазон данной таблицы вставлены знаки $ для его закрепления, чтобы он не сдвигался вниз, когда мы будем протягивать формулу. Знак доллара в Excel превращает относительный диапазон в абсолютный.

3 – номер столбца . В нашем случае это цифра «3», так как цена находится в третьем столбце нашей таблицы «Прайс лист».

0 – интервальный просмотр. Может принимать только два значения 0 или 1: 0 – ищет точное совпадение, 1 – приблизительное. В 99% случаях требуется искать точное значение (в нашем случае нам необходимо искать слово «Ведро»). Поэтому практически всегда указывается цифра 0.

Таким образом, логика функции ВПР Excel в нашем примере следующая. Функция ищет искомое значение («ведро») в крайнем левом столбце таблицы («Прайс лист»), после того как находит — возвращает значение ячейки находящейся в указанном столбце той же строки , т.е цену 120 рублей.

После этого переходим в ячейку D3 и находит стоимость товаров. Прописываем формулу =C3*B3 , т.е перемножаем цену товара на количество.

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