Четыре способа использования впр с несколькими условиями. Функции индекс и поискпоз в excel – лучшая альтернатива для впр

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

Использование функции СТОЛБЕЦ для указания колонки извлечения

Если таблица, в которую вы извлекаете данные при помощи ВПР, имеет ту же самую структуру, что и справочная таблица, но просто содержит меньшее количество строк, то в ВПР можно использовать функцию СТОЛБЕЦ() для автоматического расчёта номеров извлекаемых столбцов. При этом все ВПР-формулы будут одинаковыми (с поправкой на первый параметр, который меняется автоматически)! Обратите внимание, что у первого параметра координата столбца абсолютная.

Создание составного ключа через &»|»&

Если возникает необходимость искать по нескольким столбцам одновременно, то необходимо делать составной ключ для поиска. Если бы возвращаемое значение было не текстовым (как тут в случае с полем «Код»), а числовым, то для этого подошла бы более удобная формула СУММЕСЛИМН (SUMIFS) и составной ключ столбца не потребовался бы вовсе.

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

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

Совет: Ознакомьтесь с этими видеороликами , чтобы получить дополнительную информацию о функции ВПР!

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

ВПР(искомое значение; диапазон для поиска значения; номер столбца в диапазоне с возвращаемым значением; точное или приблизительное совпадение - указывается как 0/ЛОЖЬ или 1/ИСТИНА).

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

Используйте функцию ВПР для поиска значения в таблице.

Синтаксис

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

Например:

ВПР(105;A2:C7;2;ИСТИНА)

ВПР("Иванов";B2:E7;2;ЛОЖЬ)

Имя аргумента	Описание
искомое_значение (обязательный)	Значение для поиска. Искомое значение должно находиться в первом столбце диапазона ячеек, указанного в таблице . Например, если Таблица-массив охватывает ячейки B2: D7, то искомое_значение должен находиться В столбце B. Посмотрите рисунок ниже. Искомое_значение может быть значением или ссылкой на ячейку.
таблица (обязательный)	Диапазон ячеек, в котором будет выполнен поиск искомого_значения и возвращаемого значения с помощью функции ВПР. Первый столбец в диапазоне ячеек должен содержать искомое_значение (например, фамилию, как показано на рисунке ниже). Диапазон ячеек также должен содержать возвращаемое значение (например, имя, как показано на рисунке ниже), которое нужно найти.
номер_столбца (обязательный)	Номер столбца (начиная с 1 для крайнего левого столбца таблицы ), содержащий возвращаемое значение.
интервальный_просмотр (необязательный)	Логическое значение, определяющее, какое совпадение должна найти функция ВПР , - приблизительное или точное. Вариант ИСТИНА предполагает, что первый столбец в таблице отсортирован в алфавитном порядке или по номерам, а затем выполняет поиск ближайшего значения. Это способ по умолчанию, если не указан другой. Вариант ЛОЖЬ осуществляет поиск точного значения в первом столбце.

Начало работы

Для построения синтаксиса функции ВПР вам потребуется следующая информация:

Значение, которое вам нужно найти, то есть искомое значение.

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

Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона вы указываете B2:D11, следует считать B первым столбцом, C - вторым и т. д.

При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.

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

ВПР(искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением; при желании укажите ИСТИНА для поиска приблизительного или ЛОЖЬ для поиска точного совпадения).

Примеры

Вот несколько примеров функции ВПР:

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

Распространенные неполадки

Проблема	Возможная причина
Неправильное возвращаемое значение	Если аргумент интервальный_просмотр имеет значение ИСТИНА или не указан, первый столбец должны быть отсортирован по алфавиту или по номерам. Если первый столбец не отсортирован, возвращаемое значение может быть непредвиденным. Отсортируйте первый столбец или используйте значение ЛОЖЬ для точного соответствия.
#Н/Д в ячейке	Если аргумент интервальный_просмотр имеет значение ИСТИНА, а значение аргумента искомое_значение меньше, чем наименьшее значение в первом столбце таблицы , будет возвращено значение ошибки #Н/Д. Если аргумент интервальный_просмотр имеет значение ЛОЖЬ, значение ошибки #Н/Д означает, что найти точное число не удалось. Дополнительные сведения об устранении ошибок #Н/Д в функции ВПР см. в статье Исправление ошибки #Н/Д в функции ВПР .
	Если значение " Номер_столбца " больше, чем число столбцов в таблице , вы получите #REF! В противном случае TE102825393 выдаст ошибку «#ЗНАЧ!».
#ЗНАЧ! в ячейке	Если инфо_таблица меньше 1, вы получите #VALUE! В противном случае TE102825393 выдаст ошибку «#ЗНАЧ!». Дополнительные сведения об устранении ошибок #ЗНАЧ! в функции ВПР см. в статье Исправление ошибки #ЗНАЧ! в функции ВПР .
#ИМЯ? в ячейке	Значение ошибки #ИМЯ? чаще всего появляется, если в формуле пропущены кавычки. Во время поиска имени сотрудника убедитесь, что имя в формуле взято в кавычки. Например, в функции =ВПР("Иванов";B2:E7;2;ЛОЖЬ) имя необходимо указать в формате "Иванов" и никак иначе.

Действие	Результат
	Использование абсолютных ссылок позволяет заполнить формулу так, чтобы она всегда отображала один и тот же диапазон точных подстановок.
Не сохраняйте числовые значения или значения дат как текст.	При поиске числовых значений или значений дат убедитесь, что данные в первом столбце аргумента таблица не являются текстовыми значениями. Иначе функция ВПР может вернуть неправильное или непредвиденное значение.
Сортируйте первый столбец	Если для аргумента интервальный_просмотр указано значение ИСТИНА, прежде чем использовать функцию ВПР, отсортируйте первый столбец таблицы .
Используйте подстановочные знаки	Если Интервальный_просмотр имеет значение ложь, а Искомое_значение - текст, можно использовать подстановочные знаки - вопросительный знак (_км_) и звездочку (*) - в Искомое_значение . Вопросительный знак соответствует одному символу. Звездочка соответствует любой последовательности знаков. Если вы хотите найти реальный вопросительный знак или звездочку, введите знак тильда (~) перед символом. Например, с помощью функции =VLOOKUP("Fontan?",B2:E7,2,FALSE) можно выполнить поиск всех случаев употребления фамилии Иванов в различных падежных формах.
Убедитесь, что данные не содержат ошибочных символов.	При поиске текстовых значений в первом столбце убедитесь, что данные в нем не содержат начальных или конечных пробелов, недопустимых прямых (" или ") и изогнутых (‘ или “) кавычек либо непечатаемых символов. В этих случаях функция ВПР может возвращать непредвиденное значение. Для получения точных результатов попробуйте воспользоваться функциями

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

Синтаксис и описание функции ВПР в Excel

Итак, так как второе название этой статьи «Функция ВПР в Excel для чайников », начнем с того что узнаем, что же такое функция ВПР и что она делает? Функция ВПР на английском VLOOKUP, ищет указанное значение и возвращает соответствующее значение из другого столбца.

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

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

Функция ВПР доступна во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.

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

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

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

1. искомое_значение - это значение для поиска.

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

• Поиск числа : =ВПР(40; A2:B15; 2) - формула будет искать число 40.
• Поиск текста : =ВПР(«яблоки»; A2:B15; 2) - формула будет искать текст «яблоки». Обратите внимание, что вы всегда включаете текстовые значения в «двойные кавычки».
• Поиск значения из другой ячейки : =ВПР(C2; A2:B15; 2) - формула будет искать значение в ячейке C2.

1. таблица - это два или более столбца данных.

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

Итак, наша формула =ВПР(40; A2:B15; 2) будет искать «40» в ячейках от A2 до A15, потому что A - это первый столбец таблицы A2: B15.

1. номер_столбца - номер столбца в таблице, из которой должно быть возвращено значение в соответствующей строке.

Самый левый столбец в указанной таблице равен 1, второй столбец - 2, третий - 3 и т. д.

4. интервальный_просмотр определяет, ищете ли вы точное соответствие (ЛОЖЬ) или приблизительное соответствие (ИСТИНА или опущено). Этот последний параметр является необязательным, но очень важным.

Функция ВПР в Excel примеры

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

Функция ВПР на разных листах

На практике формулы ВПР редко используются для поиска данных на одном листе. Чаще всего вам придется искать и вытаскивать соответствующие данные с другого листа.

Чтобы использовать функцию ВПР с другого листа Excel, вы должны ввести имя рабочего листа и восклицательный знак в аргументе таблица перед диапазоном ячеек, например, =ВПР(40;Лист2!A2:B15;2). Формула указывает, что диапазон поиска A2:B15 находится в Лист2.

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

Формула, которую вы видите на изображении ниже, ищет текст в ячейке А2 («Продукт 3 ») в столбце A (1-й столбец диапазона поиска A2:B9) на листе «Цены »:

ВПР(A2;Цены!$A$2:$B$8;2;ЛОЖЬ)

Функция ВПР в Excel - Функция ВПР на разных листах

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

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

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

Функция ВПР в Excel - Присвоение имени диапазону

Теперь вы можете написать следующую формулу ВПР, чтобы получить цену Продукта 1:

ВПР(«Продукт 1»;Продукты;2)

Функция ВПР в Excel - Пример функции ВПР с именем диапазона

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

Если вы преобразовали диапазон ячеек в полнофункциональную таблицу Excel (вкладка «Вставка» --> «Таблица»), вы можете выбрать диапазон поиска с помощью мыши, а Microsoft Excel автоматически добавит имена колонок или имя таблицы в формулу:

Функция ВПР в Excel - Пример функции ВПР с именем таблицы

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

ВПР("Продукт 1";Таблица6[[Продукт]:[Цена]];2)

или даже =ВПР("Продукт 1";Таблица6;2).

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

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

Рассмотрим пример функции ВПР с несколькими условиями . У нас есть следующие исходные данные:

Функция ВПР в Excel - Таблица исходных данных

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

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

Итак на листе «Цены » вставляем столбец и в ячейке А2 вводим следующую формулу:

При помощи этой формулы мы значение столбца «Продукт » и «Тип ». Заполняем все ячейки.

Теперь таблица для поиска выглядит следующим образом:

Функция ВПР в Excel - Добавление вспомогательного столбца

1. Теперь в ячейке С2 на листе «Продажи » напишем следующую формулу ВПР:

ВПР(A2&B2;Цены!$A$1:$D$8;4;ЛОЖЬ)

Заполняем для остальных ячеек и в результате получаем цены для каждого продукта в соответствии с типом:

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

Теперь разберем ошибки функции ВПР .

Почему не работает функция ВПР

В этой части статьи мы рассмотрим почему не работает функция ВПР и возможные ошибки функции ВПР .

Тип ошибки	Причина	Решение
	Неверное расположение столбца, по которому происходит поиск	Столбец таблицы, по которому происходит поиск ОБЯЗАТЕЛЬНО должен быть крайним левым. Перенесите столбец, по которому происходит поиск в крайнее левое положение таблицы. Или создайте вспомогательный дублирующий столбец, слева в таблице.
	Не закреплен диапазон таблицы	Если первое значение было выведено правильно, а после протягивания формулы ВПР в некоторых ячейках встречается ошибка #Н/Д, то диапазон таблицы не закреплен. Используйте ($) для закрепления диапазона таблицы, чтобы при заполнении формула использовала один и тот же диапазон. Или используйте
	Не удалось найти точное совпадение (если в интервальном просмотре выбран поиск точного значения (0)
	В интервальном просмотре выполняется поиск ближайшего значения (1), а таблица , по которой происходит поиск не отсортирована.	Отсортируйте первый столбец таблицы по возрастанию наименований.
		Используйте функции ПЕЧСИМВ или СЖПРОБЕЛЫ.
	Значение номер столбца превышает число столбцов в таблице	Проверьте номер столбца, содержащий возвращаемое значение.
	В формуле пропущены кавычки	Если вы используете в качестве искомого значения не ссылку на ячейку, а текст, то его необходимо заключить в кавычки. Например: ВПР("Продукт 1"; Цены!$A$2:$B$8;2;0)

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

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

Функция ВПР()

Предположим, у вас есть таблица с данными о работниках. В первой колонке хранится табельный номер сотрудника, в остальных – другие данные (ФИО, отдел и т.д.). Если у вас есть табельный номер, то можно воспользоваться функцией ВПР, чтобы вернуть определенную информацию о сотруднике. Синтаксис формулы =ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр]). Она говоритExcel: «Найди в таблице строку, первая ячейка которой совпадает с искомым_значением, и верни значение ячейки с порядковым номером номер_столбца ».

Но случаются ситуации, когда у вас есть имя сотрудника и необходимо вернуть табельный номер. На рисунке в ячейке A10 – имя работника и требуется определить табельный номер в ячейке B10.

Когда ключевое поле находится правее данных, которые вы хотите получить, ВПР не поможет. Если, конечно, была бы возможность задать номер_столбца -1, тогда проблем бы не было. Одним из распространенных решений является добавление нового столбца A, копирование имен сотрудников в этот столбец, заполнить табельные номера с помощью ВПР, сохранить их как значения и удалить временную колонку A.

Функция ИНДЕКС()

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

Начнем с функции ИНДЕКС. Кошмарное название. Когда кто-нибудь говорит «индекс», у меня в голове не возникает ни единой ассоциации, чем же занимается эта функция. А требует она целых три аргумента: =ИНДЕКС(массив; номер_строки; [номер_столбца]).

Говоря по-простому, Excel идет в массив данных и возвращает значение, находящееся на пересечении указанной строки и столбца. Как будто бы просто. Таким образом, формула =ИНДЕКС($A$2:$C$6;4;2) вернет значение, находящееся в ячейке B5.

Применительно к нашей проблеме, чтобы вернуть табельный номер работника, формула должна выглядеть следующим образом =ИНДЕКС($A$2:$A$6;?;1). Выглядит как бессмыслица, но если мы заменим знак вопроса формулой ПОИСКПОЗ, у нас есть решение.

Функция ПОИСКПОЗ()

Синтаксис этой функции таков: =ПОИСКПОЗ(искомое_значение; просматриваемы_массив; [тип_сопоставления]).

Она говорит Excel: «Найди искомое_значение в массиве данных и верни номер строки массива, в которой это значение встречается». Таким образом, чтобы найти в какой строке находиться имя сотрудника в ячейке A10, необходимо прописать формулу =ПОИСКПОЗ(A10; $B$2:$B$6; 0). Если в ячейке A10 будет имя «Колин Фарел», тогда ПОИСКПОЗ вернет 5-ю строку массива B2:B6.

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

ИНДЕКС($A$2:$B$6;ПОИСКПОЗ(A10;$B$2:$B$6;0);1)

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

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

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

Использование функции

Рассмотрим структуру ВПР, какие аргументы она задействует. Как и любая другая функция в Excel начинается она со знака равенства (=). Далее имя функции и аргументы, заключенные в круглые скобки.

ВПР содержит 4 аргумента .

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

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

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

Последний аргумент – интервальный просмотр, здесь может быть 2 значения: 0 – ЛОЖЬ , 1 — ИСТИНА . отвечает за точный поиск (совпадения при просмотре сверху вниз). Если ничего не находит, то возвращается ошибка Н/Д (нет данных), 1 – приблизительный .

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

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

Первый простой пример – имеются 2 таблицы. В одной указываются Товары и их идентификаторы (ID). Во второй , с помощью фильтра по ID, мы хотим получить наименование товара .

После знака равно вводим ВПР , затем Enter и Fx для ввода аргументов.

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

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

Для второго аргумента выделяем диапазон таблицы .

Обязательно необходимо зафиксировать (указать знаки доллара или клавиша F4 для всего диапазона) и сделать абсолютные ссылки для того чтобы диапазон не «сползал», так как в данном случае не указывается имя таблица.

– то, что нужно вернуть. В этом примере требуется вернуть 2 столбец (Товар ). Для точного поиска 4 аргумент – .

Введя все значения, жмём кнопку ОК .

Теперь при изменении в фильтре номера ID будет изменяться наименование товара.

Теперь посмотрим другой пример .

Теперь нужно получить партию для каждого наименования товара по критерию Количество .

Например, для мелкой партии количество должно быть от 100 до 200 , средней – 200-300 и т.д.

Искомым значением в данном случае будет количество , Таблицу выбираем диапазон Критерий – Партия (фиксируем F4). Номер столбца 2, интервальный просмотр в этом случае должен быть 1 (позволит получить ближайшее меньшее значение к искомому значению).

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