Функции ИНДЕКС и ПОИСКПОЗ в Excel лучшая альтернатива для ВПР

Здравствуй уважаемый читатель!

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

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

Итак, что же в сущности это за функция, что она делает, и как она пишется. Самое простое объяснение, о работе функции ВПР, она ищет в перечне значений, по заданному вами критерию, уникальному идентификатору, определенную информацию, которая с ним связана.

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

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

• искомое_значение – это именно то значение, которое нам нужно искать, а это может быть любое значение: число, дата, текст, ссылка на ячейку, которая содержит нужное значение или значение, получаемое другой формулой;
• таблица – это два и больше столбика с разнообразными данными, кстати, регистр символов в поиске, функцией не учитывается;
• номер_столбца – это номер столбика в указанном диапазоне, из которого будет получено значение, которое находится в найденной строке;
• интервальный_просмотр – этот параметр определяет, что же именно будем искать, для точного совпадения аргумент будет равен «ЛОЖЬ» (FALSE) или приблизительное совпадение, аргумент станет равным ИСТИНА (TRUE) . Этот параметр не является обязательным, но тем не менее он важен. В примерах, далее, я продемонстрирую, как создать формулы для точного и приблизительного совпадения.

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

ВПР(«GM»;$A$5:$B$10;2)

Формула ищет текст «GM», в столбике А на текущем листе.
Совет! При использовании аргумента «таблица», желательно использовать, такой вариант, как (это адрес ячейки со знаком $). В этом случае диапазон поиска станет закреплённым и не изменится при копировании формулы.

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

ВПР(”GM”;[Путь к файлу]База!А2:В10;2)

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

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

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

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

• «?» (знак вопроса ) – позволит заменить один любой символ;
• «*» (звёздочка) – заменит любое количество и последовательность символов.

ВПР(“A*”;$A$2:$B$10;1;ЛОЖЬ)

Совет! Для того что бы функция ВПР, корректно работала нужно в качестве четвертого аргумента использовать параметр «ЛОЖЬ».
Ну, если мы уже затронули тему точного или приближенного совпадения в синтаксисе функции ВПР, то давайте ее рассмотрим поподробнее:

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

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

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

=ВПР(200;$A$2:$B$15;2;ЛОЖЬ)

Несмотря на то, что значений 200 у нас несколько, получили только одно, так как при точном совпадении функции ВПР, система использует только первое значение которое было найдено в указанном диапазоне.
Теперь испытаем, работу функции ВПР для приблизительного совпадения значений. Поищем, какой автомобиль ездит со скоростью 260 км/час. Первое что вы делаете в случае, когда «интервальный просмотр» равняется «ИСТИНА» — вы выполняете сортировку вашего диапазона значений по первому столбику по порядку его возрастания. Это необходимо и важно, поскольку функция ВПР находит следующее наибольшее значение от заданного условия, а после поиск прекращается. Если же вы не последуете совету по сортировке, итогом будет сообщение об или другие странные результаты.

Для поиска используем ВПР следующего вида:

=ВПР(260;$A$2:$B$15;2;ИСТИНА)

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

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

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

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

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

Функция ВПР в Excel – общее описание и синтаксис

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

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

Первая буква в названии функции ВПР (VLOOKUP) означает В ертикальный (V ertical). По ней Вы можете отличить ВПР от ГПР (HLOOKUP), которая осуществляет поиск значения в верхней строке диапазона – Г оризонтальный (H orizontal).

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

Синтаксис функции ВПР

Функция ВПР (VLOOKUP) имеет вот такой синтаксис:

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

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

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

  VLOOKUP(40,A2:B15,2)
  =ВПР(40;A2:B15;2)

Если искомое значение будет меньше, чем наименьшее значение в первом столбце просматриваемого диапазона, функция ВПР сообщит об ошибке #N/A (#Н/Д).

• table_array (таблица) – два или более столбца с данными.Запомните, функция ВПР всегда ищет значение в первом столбце диапазона, заданного в аргументе table_array (таблица). В просматриваемом диапазоне могут быть различные данные, например, текст, даты, числа, логические значения. Регистр символов не учитывается функцией, то есть символы верхнего и нижнего регистра считаются одинаковыми.Итак, наша формула будет искать значение 40 в ячейках от A2 до A15 , потому что A – это первый столбец диапазона A2:B15, заданного в аргументе table_array (таблица):

  VLOOKUP(40,A2:B15,2)
  =ВПР(40;A2:B15;2)

Если значение аргумента col_index_num (номер_столбца) меньше 1 , то ВПР сообщит об ошибке #VALUE! (#ЗНАЧ!). А если оно больше количества столбцов в диапазоне table_array (таблица), функция вернет ошибку #REF! (#ССЫЛКА!).

• range_lookup (интервальный_просмотр) – определяет, что нужно искать:
  • точное совпадение, аргумент должен быть равен FALSE (ЛОЖЬ);
  • приблизительное совпадение, аргумент равен TRUE (ИСТИНА) или вовсе не указан.

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

Примеры с функцией ВПР

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

Как, используя ВПР, выполнить поиск на другом листе Excel

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

Чтобы, используя ВПР , выполнить поиск на другом листе Microsoft Excel, Вы должны в аргументе table_array (таблица) указать имя листа с восклицательным знаком, а затем диапазон ячеек. К примеру, следующая формула показывает, что диапазон A2:B15 находится на листе с именем Sheet2 .

VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)

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

Формула, показанная на скриншоте ниже, ищет текст “Product 1” в столбце A (это 1-ый столбец диапазона A2:B9) на листе Prices .

VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

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

Для аргумента table_array (таблица) желательно всегда использовать абсолютные ссылки (со знаком $). В таком случае диапазон поиска будет оставаться неизменным при копировании формулы в другие ячейки.

Поиск в другой рабочей книге с помощью ВПР

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

Например, ниже показана формула, которая ищет значение 40 на листе Sheet2 в книге Numbers.xlsx :

VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)

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

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

На снимке экрана, показанном ниже, видно формулу, в которой для поиска задан диапазон в рабочей книге PriceList.xlsx на листе Prices .

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

Если название рабочей книги или листа содержит пробелы, то его нужно заключить в апострофы:

VLOOKUP(40,"Sheet2"!A2:B15,2)
=ВПР(40;"Sheet2"!A2:B15;2)

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

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

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

Теперь Вы можете записать вот такую формулу для поиска цены товара Product 1 :

VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)

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

VLOOKUP("Product 1",PriceList.xlsx!Products,2)
=ВПР("Product 1";PriceList.xlsx!Products;2)

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

Если преобразовать диапазон ячеек в полноценную таблицу Excel, воспользовавшись командой Table (Таблица) на вкладке Insert (Вставка), то при выделении диапазона мышью, Microsoft Excel автоматически добавит в формулу названия столбцов (или название таблицы, если Вы выделите всю таблицу).

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

VLOOKUP("Product 1",Table46[:],2)
=ВПР("Product 1";Table46[:];2)

А может даже так:

VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)

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

Использование символов подстановки в формулах с ВПР

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

• Знак вопроса (?) – заменяет один любой символ.
• Звёздочка (*) – заменяет любую последовательность символов.

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

• Когда Вы не помните в точности текст, который нужно найти.
• Когда Вы хотите найти какое-то слово, которое является частью содержимого ячейки. Знайте, что ВПР ищет по содержимому ячейки целиком, как при включённой опции Match entire cell content (Ячейка целиком) в стандартном поиске Excel.
• Когда в ячейке содержатся дополнительные пробелы в начале или в конце содержимого. В такой ситуации Вы можете долго ломать голову, пытаясь понять, почему формула не работает.

Пример 1: Ищем текст, начинающийся или заканчивающийся определенными символами

Предположим, что Вы хотите найти определенного клиента в базе данных, показанной ниже. Вы не помните его фамилию, но знаете, что она начинается на “ack”. Вот такая формула отлично справится с этой задачей:

VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

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

VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Вот ещё несколько примеров с символами подстановки:

~ Находим имя, заканчивающееся на “man”:

VLOOKUP("*man",$A$2:$C$11,1,FALSE)
=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Находим имя, начинающееся на “ad” и заканчивающееся на “son”:

VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)
=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ Находим первое имя в списке, состоящее из 5 символов:

VLOOKUP("?????",$A$2:$C$11,1,FALSE)
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

Чтобы функция ВПР с символами подстановки работала правильно, в качестве четвёртого аргумента всегда нужно использовать FALSE (ЛОЖЬ). Если диапазон поиска содержит более одного значения, подходящего под условия поиска с символами подстановки, то будет возвращено первое найденное значение.

Пример 2: Сочетаем символы подстановки и ссылки на ячейки в формулах с ВПР

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

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

VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

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

Как видно на рисунке ниже, функция ВПР возвращает значение “Jeremy Hill”, поскольку его лицензионный ключ содержит последовательность символов из ячейки C1.

Заметьте, что аргумент table_array (таблица) на скриншоте сверху содержит имя таблицы (Table7) вместо указания диапазона ячеек. Так мы делали в предыдущем примере.

Точное или приближенное совпадение в функции ВПР

И, наконец, давайте рассмотрим поподробнее последний аргумент, который указывается для функции ВПР – range_lookup (интервальный_просмотр). Как уже упоминалось в начале урока, этот аргумент очень важен. Вы можете получить абсолютно разные результаты в одной и той же формуле при его значении TRUE (ПРАВДА) или FALSE (ЛОЖЬ).

Для начала давайте выясним, что в Microsoft Excel понимается под точным и приближенным совпадением.

• Если аргумент range_lookup FALSE (ЛОЖЬ), формула ищет точное совпадение, т.е. точно такое же значение, что задано в аргументе lookup_value (искомое_значение). Если в первом столбце диапазона table_array (таблица) встречается два или более значений, совпадающих с аргументом lookup_value (искомое_значение), то выбрано будет первое из них. Если совпадения не найдены, функция сообщит об ошибке #N/A (#Н/Д).Например, следующая формула сообщит об ошибке #N/A (#Н/Д), если в диапазоне A2:A15 нет значения 4 :

  VLOOKUP(4,A2:B15,2,FALSE)
  =ВПР(4;A2:B15;2;ЛОЖЬ)

• Если аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА), формула ищет приблизительное совпадение. Точнее, сначала функция ВПР ищет точное совпадение, а если такое не найдено, выбирает приблизительное. Приблизительное совпадение – это наибольшее значение, не превышающее заданного в аргументе lookup_value (искомое_значение).

Если аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или не указан, то значения в первом столбце диапазона должны быть отсортированы по возрастанию, то есть от меньшего к большему. Иначе функция ВПР может вернуть ошибочный результат.

Чтобы лучше понять важность выбора TRUE (ИСТИНА) или FALSE (ЛОЖЬ), давайте разберём ещё несколько формул с функцией ВПР и посмотрим на результаты.

Пример 1: Поиск точного совпадения при помощи ВПР

Как Вы помните, для поиска точного совпадения, четвёртый аргумент функции ВПР должен иметь значение FALSE (ЛОЖЬ).

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

VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Обратите внимание, что наш диапазон поиска (столбец A) содержит два значения 50 – в ячейках A5 и A6 . Формула возвращает значение из ячейки B5 . Почему? Потому что при поиске точного совпадения функция ВПР использует первое найденное значение, совпадающее с искомым.

Пример 2: Используем ВПР для поиска приблизительного совпадения

Когда Вы используете функцию ВПР для поиска приблизительного совпадения, т.е. когда аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или пропущен, первое, что Вы должны сделать, – выполнить сортировку диапазона по первому столбцу в порядке возрастания.

Это очень важно, поскольку функция ВПР возвращает следующее наибольшее значение после заданного, а затем поиск останавливается. Если Вы пренебрежете правильной сортировкой, дело закончится тем, что Вы получите очень странные результаты или сообщение об ошибке #N/A (#Н/Д).

Вот теперь можно использовать одну из следующих формул:

VLOOKUP(69,$A$2:$B$15,2,TRUE) или =VLOOKUP(69,$A$2:$B$15,2)
=ВПР(69;$A$2:$B$15;2;ИСТИНА) или =ВПР(69;$A$2:$B$15;2)

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

Как видите, формула возвратила результат Антилопа (Antelope), скорость которой 61 миля в час, хотя в списке есть также Гепард (Cheetah), который бежит со скоростью 70 миль в час, а 70 ближе к 69, чем 61, не так ли? Почему так происходит? Потому что функция ВПР при поиске приблизительного совпадения возвращает наибольшее значение, не превышающее искомое.

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

ВПР в Excel – это нужно запомнить!

1. Функция ВПР в Excel не может смотреть налево. Она всегда ищет значение в крайнем левом столбце диапазона, заданного аргументом table_array (таблица).
2. В функции ВПР все значения используются без учета регистра, то есть маленькие и большие буквы эквивалентны.
3. Если искомое значение меньше минимального значения в первом столбце просматриваемого диапазона, функция ВПР сообщит об ошибке #N/A (#Н/Д).
4. Если 3-й аргумент col_index_num (номер_столбца) меньше 1 , функция ВПР сообщит об ошибке #VALUE! (#ЗНАЧ!). Если же он больше количества столбцов в диапазоне table_array (таблица), функция сообщит об ошибке #REF! (#ССЫЛКА!).
5. Используйте абсолютные ссылки на ячейки в аргументе table_array (таблица), чтобы при копировании формулы сохранялся правильный диапазон поиска. Попробуйте в качестве альтернативы использовать именованные диапазоны или таблицы в Excel.
6. Когда выполняете поиск приблизительного совпадения, не забывайте, что первый столбец в исследуемом диапазоне должен быть отсортирован по возрастанию.
7. И, наконец, помните о важности четвертого аргумента. Используйте значения TRUE (ИСТИНА) или FALSE (ЛОЖЬ) обдуманно, и Вы избавитесь от многих головных болей.

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

Привет, друзья. Как часто вам приходится для какого-то значения искать соответствие в таблице Эксель? Например, нужно в справочнике найти адрес человека, или в прайсе – цену товара. Если такие задачи встречаются – этот пост именно для вас!

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

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

Синтаксис функции ВПР такой: =ВПР(Искомое_значение; таблица_для_поиска; номер_выводимого_столбца; [тип_сопоставления]) . Рассмотрим аргументы:

• Искомое значение – значение, которое будем искать. Это обязательный аргумент;
• Таблица для поиска – тот массив ячеек, в котором будет поиск. Столбец с искомыми значениями должен быть первым в этом массиве. Это тоже обязательный аргумент;
• Номер выводимого столбца – порядковый номер столбца (начиная с первого в массиве), из которого функция выведет данные при совпадении искомых значений. Обязательный аргумент;
• Тип сопоставления – выберите «1» (или «ИСТИНА») для нестрогого совпадения, «0» («ЛОЖЬ») – для полного совпадения. Аргумент необязателен, если его упустить – будет выполнен поиск нестрогого совпадения .

Поиск точного совпадения с помощью ВПР

Посмотрим на примере, как работает функция ВПР, когда выбран тип сопоставления «ЛОЖЬ», поиск точного совпадения. В массиве В5:Е10 указаны основные средства некой компании, их балансовая стоимость, инвентарный номер и место расположения. В ячейке В2 указано наименование, для которого нужно в таблице найти инвентарный номер и поместить его в ячейку С2 .

Функция ВПР в Excel

Запишем формулу: =ВПР(B2;B5:E10;3;ЛОЖЬ) .

Здесь первый аргумент указывает, что в таблице нужно искать значение из ячейки В2 , т.е. слово «Факс». Второй аргумент говорит, что таблица для поиска — в диапазоне В5:Е10 , а искать слово «Факс» нужно в первом столбце, т.е. в массиве В5:В10 . Третий аргумент сообщает программе, что результат расчета содержится в третьем столбце массива, т.е. D5:D10 . Четвёртый аргумент равен «ЛОЖЬ», т.е. требуется полное совпадение.

И так, функция получит строку «Факс» из ячейки В2 и будет искать его в массиве В5:В10 сверху вниз. Как только совпадение будет найдено (строка 8), функция вернёт соответствующее значение из столбца D , т.е. содержимое D8 . Именно это нам и требовалось, задача решена.

Если искомое значение не будет найдено, функция вернёт .

Поиск неточного совпадения с помощью ВПР

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

В массиве В5:С12 указаны процентные ставки по кредитам в зависимости от суммы займа. В ячейке В2 Указываем сумму кредита и хотим получить в С2 ставку для такой сделки. Задача сложна тем, что сумма может быть любой и вряд ли будет совпадать с указанными в массиве, поиск по точному совпадению не подходит:

Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА) . Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.

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

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

Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения ) :

• Искомое значение – данные или ссылка на данные, которые нужно искать;
• Массив для поиска – одна строка или столбец, в котором ищем аналогичное значение. Данный массив обязательно сортируем по возрастанию;
• Массив для отображения – диапазон, содержащий данные для выведения результатов. Естественно, он должен одного размера с массивом для поиска.

При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10) . Задача успешно решена!

Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.

Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [ Тип сопоставления ] ). Аргументы функции:

• Искомое значение – обязательный аргумент
• Просматриваемый массив – одна строка или столбец, в котором ищем совпадение. Обязательный аргумент
• Тип сопоставления – укажите «0» для поиска точного совпадения, «1» — ближайшее меньшее, «-1» — ближайшее большее. Поскольку функция проводит поиск с начала списка в конец, при поиске ближайшего меньшего – отсортируйте столбец поиска по убыванию. А при поиске большего – сортируйте его по возрастанию.

Позиция необходимого значения найдена, теперь можно вывести его на экран с помощью функции ИНДЕКС(Массив; Номер_строки; [Номер_столбца] ) :

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

Теперь скомбинируем эти функции, чтобы получить результат:

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

Чувствуете, как растут и крепчают Ваши знания и умения? Тогда не останавливайтесь, продолжайте читать ! В следующем посте мы будем рассматривать : будет сложно и интересно!

Функции ИНДЕКС и ПОИСКПОЗ в Excel – лучшая альтернатива для ВПР

​Смотрите также​Другими словами если в​​ соответствующей цены.​​Формула в ячейках исчезнет.​​ далеко не самым​​ использованием функции ВПР().​ с функцией​ один вариант ставки​Пример из жизни. Ставим​​ на другую деталь,​​Если всё сделано верно,​=ИНДЕКС($A$1:$E$11;4;5))​, значения в столбце​ заметно быстрее.​любой вставленный или​B​​ нужны, и поэтому​​Этот учебник рассказывает о​

​ нашей таблице повторяются​Переходим в ячейку второй​ Останутся только значения.​ ближайшим. Например, если​Пусть дана исходная таблица​​ВПР​​ комиссионных, равный 50%,​ задачу​ потому что функция​ Вы получите результат​Эта формула возвращает значение​ поиска должны быть​​Теперь, когда Вы понимаете​​ удалённый столбец изменит​, а конкретно –​ требуется помощь функции​ главных преимуществах функций​

​ значения «груши», «яблока»​ таблицы под названием​​​ попытаться найти ближайшую​​ (см. файл примера​.​ для тех продавцов,​Усложняем задачу​ ВПР нашла ближайшее​ как на рисунке​ на пересечении​ упорядочены по возрастанию,​ причины, из-за которых​​ результат формулы, поскольку​​ в ячейках​​ПОИСКПОЗ​​ИНДЕКС​ мы не сможем​ столбца «Цена».​Функция помогает сопоставить значения​ цену для 199,​​ лист Справочник).​​Если поэкспериментируем с несколькими​

• ​ кто сделал объём​Применяем функцию ВПР к​
• ​ число, меньшее или​ ниже:​
• ​4-ой​ а формула вернёт​
• ​ стоит изучать функции​ синтаксис​
  • ​B2:B10​.​
  • ​и​ просуммировать всех груш​
  • ​Выберите «Формулы»-«Ссылки и массивы»-«ВПР».​ в огромных таблицах.​
  • ​ то функция вернет​
  • ​Задача состоит в том,​ различными значениями итоговой​

Базовая информация об ИНДЕКС и ПОИСКПОЗ

​ продаж более $50000.​ решению задачи​ равное указанному (2345678).​​Как Вы, вероятно, уже​​строки и​​ максимальное значение, меньшее​​ПОИСКПОЗ​ВПР​, и возвращает число​Функция​ПОИСКПОЗ​

​ и яблок. Для​Ввести функцию ВПР​ Допустим, поменялся прайс.​ 150 (хотя ближайшее​ чтобы, выбрав нужный​ суммы продаж, то​​ А если кто-то​​Заключение​​ Эта ошибка может​​ заметили (и не​​5-го​​ или равное среднему.​

ИНДЕКС – синтаксис и применение функции

​и​​требует указывать весь​​3​MATCH​в Excel, которые​ этого нужно использовать​ можно и с​ Нам нужно сравнить​

​ мы убедимся, что​ продал на сумму​

• ​Проиллюстрируем эту статью примером​​ привести к неправильному​ раз), если вводить​столбца в диапазоне​Если указываете​
• ​ИНДЕКС​​ диапазон и конкретный​, поскольку «Japan» в​(ПОИСКПОЗ) в Excel​ делают их более​ функцию ПРОСМОТР(). Она​ помощью «мастера функций».​ старые цены с​​ Это опять следствие​​ его Наименование и​
• ​ формула работает правильно.​​ более $60000 –​ из реальной жизни​ выставлению счета клиенту.​ некорректное значение, например,​A1:E11​-1​, давайте перейдём к​​ номер столбца, из​​ списке на третьем​

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

​Когда функция​​ тому заплатить 60%​​ – расчёт комиссионных​

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

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

ПОИСКПОЗ – синтаксис и применение функции

​Функция​​ и возвращает относительную​​ВПР​ хорошо работать с​ которая находиться в​ столбец «Новая цена».​ меньше или равно​. Это "классическая" задача для​

​работает с базами​​Теперь формула в ячейке​​ ряда показателей продаж.​ ЛОЖЬ или 0,​ИНДЕКС​E4​​ упорядочены по убыванию,​​ применить теоретические знания​Например, если у Вас​INDEX​

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

• ​ B2, даже если​​ Мы начнём с​ а точного совпадения​/​. Просто? Да!​ а возвращено будет​ на практике.​ есть таблица​
• ​(ИНДЕКС) использует​​ в диапазоне.​ примеров формул, которые​ значениях.​
• ​ Или нажмите комбинацию​​ выбираем функцию ВПР.​Если нужно найти по​​ статью Справочник).​​Range_lookup​ она записана без​ очень простого варианта,​
  • ​ нет, вместо неправильного​​ПОИСКПОЗ​​В учебнике по​​ минимальное значение, большее​Любой учебник по​A1:C10​3​Например, если в диапазоне​ помогут Вам легко​Функция ВПР (Вертикальный ПРосмотр)​
  • ​ горячих клавиш SHIFT+F3.​​ Задаем аргументы (см.​ настоящему ближайшее к​Для вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) или =ВПР($E9;$A$13:$C$19;2;ИСТИНА) или =ВПР($E9;$A$13:$C$19;2) (т.е.​​(Интервальный_просмотр) должен принимать​​ ошибок, стала совершенно​​ и затем постепенно​​ значения формула возвращает​сообщает об ошибке​ВПР​​ или равное среднему.​​ВПР​​, и требуется извлечь​​для аргумента​
  • ​B1:B3​​ справиться со многими​ ищет по таблице​В появившимся диалоговом​ выше). Для нашего​ искомому значению, то ВПР() тут​ значение параметра​FALSE​ не читаемой. Думаю,​

​ будем усложнять его,​ в ячейку строку​​#N/A​​мы показывали пример​В нашем примере значения​твердит, что эта​ данные из столбца​row_num​

​содержатся значения New-York,​ сложными задачами, перед​ с данными и​ окне на поле​ примера: . Это​ не поможет. Такого​Интервальный_просмотр​(ЛОЖЬ). А значение,​​ что найдется мало​​ пока единственным рациональным​​ "#Н/Д". Это наилучшее​​(#Н/Д) или​​ формулы с функцией​​ в столбце​ функция не может​​B​​(номер_строки), который указывает​ Paris, London, тогда​ которыми функция​ на основе критериев​ категория, выберите из​ значит, что нужно​ рода задачи решены​можно задать ЛОЖЬ​

Как использовать ИНДЕКС и ПОИСКПОЗ в Excel

​ введённое в качестве​ желающих использовать формулы​ решением задачи не​ решение. В данном​#VALUE!​ВПР​​D​​ смотреть влево. Т.е.​​, то нужно задать​​ из какой строки​​ следующая формула возвратит​​ВПР​ запроса поиска, возвращает​ выпадающего списка: «Ссылки​ взять наименование материала​​ в разделе Ближайшее​​ или ИСТИНА или​Lookup_value​ с 4-мя уровнями​ станет использование функции​

​ случае "#Н/Д" не​(#ЗНАЧ!). Если Вы​​для поиска по​​упорядочены по возрастанию,​​ если просматриваемый столбец​​ значение​

​ ЧИСЛО. Там же можно​ вообще опустить). Значение​(Искомое_значение) должно существовать​ вложенности в своих​ВПР​

​ означает, что формула​ хотите заменить такое​ нескольким критериям. Однако,​ поэтому мы используем​

​ Т.е. получается простая​3​В нескольких недавних статьях​

• ​ определенного столбца. Очень​​ потом ниже укажите​​ посмотреть его в​ найти решение задачи​​ параметра ​​ в базе данных.​ проектах. Должен же​​. Первоначальный сценарий нашей​​ введена неправильно (за​​ сообщение на что-то​​ существенным ограничением такого​ тип сопоставления​ левым в диапазоне​
• ​для аргумента​​ формула:​​, поскольку «London» –​​ мы приложили все​​ часто необходимо в​​ на функцию.​​ «Новом прайсе» в​ о поиске ближайшего​номер_столбца​ Другими словами, идёт​ существовать более простой​

  
  

  ​ более понятное, то​ решения была необходимость​1​​ поиска, то нет​​col_index_num​​=INDEX($D$2:$D$10,3)​​ это третий элемент​ усилия, чтобы разъяснить​ запросе поиска использовать​Заполняем аргументы функции.​​ столбце А. Затем​​ при несортированном ключевом​нужно задать =2,​ поиск точного совпадения.​

​ способ?!​ так: если продавец​

​ номера). Это означает,​ можете вставить формулу​ добавлять вспомогательный столбец.​​. Формула​​ шансов получить от​(номер_столбца) функции​​=ИНДЕКС($D$2:$D$10;3)​​ в списке.​​ начинающим пользователям основы​​ сразу несколько условий.​​В поле «Исходное значение»​​ взять данные из​ столбце.​

​ т.к. номер столбца​В примере, что мы​И такой способ есть!​​ за год делает​​ что номер 2345678​ с​ Хорошая новость: формула​ИНДЕКС​​ВПР​​ВПР​​Формула говорит примерно следующее:​​=MATCH("London",B1:B3,0)​

​ вводим ссылку на​ второго столбца нового​Примечание​ Наименование равен 2​ рассмотрели в данной​ Нам поможет функция​​ объём продаж более​​ не был найден,​​ИНДЕКС​​ИНДЕКС​/​желаемый результат.​, вот так:​​ ищи в ячейках​​=ПОИСКПОЗ("London";B1:B3;0)​​ВПР​​ данная функция не​ ячейку под наименованием​ прайса (новую цену)​​. Для удобства, строка​​ (Ключевой столбец всегда​

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

​ статье, нет необходимости​ВПР​ $30000, то его​ потому что вы​​и​​/​​ПОИСКПО​​Функции​​=VLOOKUP("lookup value",A1:C10,2)​​ от​Функция​и показать примеры​​ может обработать более​​ товара второй таблицы​ и подставить их​ таблицы, содержащая найденное​ номер 1). ​ получать точное соответствие.​.​ комиссионные составляют 30%.​​ искали значение 2345768.​​ПОИСКПОЗ​​ПОИСКПОЗ​​З​​ПОИСКПОЗ​​=ВПР("lookup value";A1:C10;2)​D2​MATCH​ более сложных формул​

​ одного условия. Поэтому​ D3. В поле​​ в ячейку С2.​​ решение, выделена Условным форматированием.​​Для вывода Цены используйте​​ Это тот самый​Давайте немного изменим дизайн​ В противном случае,​​В этом примере показано,​​в функцию​​может искать по​​возвращает «Moscow», поскольку​​и​​Если позднее Вы вставите​

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

​до​​(ПОИСКПОЗ) имеет вот​ для продвинутых пользователей.​​ следует использовать весьма​​ «Таблица» вводим диапазон​Данные, представленные таким образом,​ Это можно сделать​ аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ) (значение параметра ​ случай, когда функция​ нашей таблицы. Мы​ комиссия составляет, лишь​ как работает функция.​​ЕСЛИОШИБКА​​ значениям в двух​​ величина населения города​​ИНДЕКС​ новый столбец между​D10​ такой синтаксис:​ Теперь мы попытаемся,​ простую формулу, которая​ всех значений первой​ можно сопоставлять. Находить​ с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10).​

​номер_столбца​ВПР​​ сохраним все те​​ 20%. Оформим это​​ Если ввести значение​.​ столбцах, без необходимости​ Москва – ближайшее​в Excel гораздо​ столбцами​​и извлеки значение​​MATCH(lookup_value,lookup_array,[match_type])​ если не отговорить​ позволит расширить возможности​ таблицы A2:B7. В​​ численную и процентную​​Примечание​нужно задать =3). ​должна переключиться в​ же поля и​ в виде таблицы:​

​ в ячейку B2​Синтаксис функции​​ создания вспомогательного столбца!​​ меньшее к среднему​ более гибкие, и​​A​​ из третьей строки,​ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])​​ Вас от использования​​ функции ВПР по​​ поле «Номер столбца»​​ разницу.​​: Если в ключевом​​Ключевой столбец в нашем​

​Продавец вводит данные о​ (первый аргумент), функция​ЕСЛИОШИБКА​​Предположим, у нас есть​​ значению (12 269​​ им все-равно, где​​и​ то есть из​​lookup_value​​ВПР​​ нескольким столбцам одновременно.​​ вводим значение 2,​До сих пор мы​ столбце имеется значение​

​ случае содержит числа​​ чтобы вернуть нам​​ их по-новому, в​​ своих продажах в​​ ВПР выполняет поиск​очень прост:​ список заказов, и​ 006).​ находится столбец со​B​ ячейки​(искомое_значение) – это​, то хотя бы​Для наглядности разберем формулу​ так как во​ предлагали для анализа​ совпадающее с искомым,​ и должен гарантировано​ нужный результат.​ более компактном виде:​ ячейку B1, а​​ в ячейках C2:E7​​IFERROR(value,value_if_error)​

​ мы хотим найти​Эта формула эквивалентна двумерному​​ значением, которое нужно​​, то значение аргумента​​D4​ число или текст,​ показать альтернативные способы​ ВПР с примером​ втором столбце у​​ только одно условие​​ то функция с​ содержать искомое значение​Например:​Прервитесь на минутку и​ формула в ячейке​​ (второй аргумент) и​​ЕСЛИОШИБКА(значение;значение_если_ошибка)​​ сумму по двум​​ поиску​

​ извлечь. Для примера,​ придется изменить с​​, так как счёт​​ который Вы ищите.​ реализации вертикального поиска​​ нескольких условий. Для​​ нас находиться цена,​​ – наименование материала.​​ параметром ​ (условие задачи). Если первый​​Мы хотим определить,​​ убедитесь, что новая​

​Где аргумент​ критериям –​ВПР​​ снова вернёмся к​​2​ начинается со второй​ Аргумент может быть​ в Excel.​​ примера будем использовать​​ которую мы хотим​​ На практике же​​Интервальный_просмотр​

​ таблица​ ставку комиссионного вознаграждения,​​ приблизительное совпадение из​value​имя покупателя​и позволяет найти​ таблице со столицами​на​ строки.​ значением, в том​Зачем нам это? –​ схематический отчет по​ получить при поиске​ нередко требуется сравнить​ =ЛОЖЬ вернет первое найденное​ артикул​​ в расчёте комиссионных​​Rate Table​​ на которое продавец​​ третьего столбца в​​(значение) – это​​(Customer) и​ значение на пересечении​ государств и населением.​​3​​Вот такой результат получится​

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

​ ОК.​ данными и выбрать​​ а с параметром​​то функция возвращает значение​​ объёмом продаж $34988.​​ данные, что и​ свою очередь, полученная​ E (третий аргумент).​ предмет наличия ошибки​

ИНДЕКС и ПОИСКПОЗ – примеры формул

​(Product). Дело усложняется​ столбца.​ запишем формулу​​ результат из только​​Важно! Количество строк и​​lookup_array​​ВПР​В данном отчете необходимо​Теперь под заголовком столбца​ значение по 2,​ =ИСТИНА - последнее​

Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС

​ ошибки​​ Функция​​ предыдущая таблица пороговых​ ставка используется в​В данном примере четвертый​ (в нашем случае​ тем, что один​В этом примере формула​ПОИСКПОЗ​ что вставленного столбца.​​ столбцов в массиве,​​(просматриваемый_массив) – диапазон​

​– это не​​ найти показатель выручки​​ второй таблицы «Товар»​​ 3-м и т.д.​​ (см. картинку ниже).​ #Н/Д. ​ВПР​ значений.​ ячейке B3, чтобы​ аргумент оставлен пустым,​ – результат формулы​ покупатель может купить​ИНДЕКС​/​Используя​​ который использует функция​​ ячеек, в котором​​ единственная функция поиска​​ для определенного торгового​ введите наименования того​ критериям.​Если столбец, по которому​

​Это может произойти, например,​возвращает нам значение​Основная идея состоит в​ рассчитать общую сумму​

​ сразу несколько разных​/​ИНДЕКС​ПОИСКПОЗ​

• ​INDEX​​ происходит поиск.​​ в Excel, и​ представителя в определенную​ товара по котором​

  
  

• ​ при опечатке при​ 30%, что является​​ том, чтобы использовать​​ комиссионных, которую продавец​ приблизительное совпадение.​/​ продуктов, и имена​​ПОИСКПОЗ​​, которая покажет, какое​
• ​/​(ИНДЕКС), должно соответствовать​

  
  

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

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

​будет очень похожа​ место по населению​​ИНДЕКС​​ значениям аргументов​​(тип_сопоставления) – этот​​ могут помешать Вам​ поиска наш запрос​ его цену. И​ по какой цене​ ВПР() не поможет.​ с вводом искомого​ почему же формула​

​ВПР​​ перемножение ячеек B1​​ несложно будет освоить​); а аргумент​​ на листе​​ на формулы, которые​ занимает столица России​​, Вы можете удалять​​row_num​

​ должен содержать 2​

​ нажмите Enter.​​ привезли гофрированный картон​​ В этом случае​ артикула можно использовать Выпадающий​​ выбрала строку, содержащую​​для определения нужной​ и B2).​​ и функцию ГПР.​​value_if_error​

​ (Москва).​

​ или добавлять столбцы​​(номер_строки) и​​ПОИСКПОЗ​ во многих ситуациях.​​ условия:​​Функция позволяет нам быстро​ от ОАО «Восток».​ нужно использовать функции​ список (см. ячейку ​​ именно 30%, а​​ тарифной ставки по​

​(значение_если_ошибка) – это​

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

​расположены в произвольном​​ в этом уроке,​​Как видно на рисунке​​ к исследуемому диапазону,​​column_num​​, хотите ли Вы​​ С другой стороны,​– Дата сдачи выручки​​ находить данные и​​ Нужно задать два​ ПОИСКПОЗ()+ИНДЕКС() или ПРОСМОТР().​​Е9​​ не 20% или​​ таблице​​ заключена в ячейке​ те же аргументы,​ значение, которое нужно​ порядке.​ с одним лишь​ ниже, формула отлично​ не искажая результат,​(номер_столбца) функции​​ найти точное или​​ функции​ в кассу.​

• ​ получать по ним​​ условия для поиска​​Функция ВПР в Excel​).​ 40%? Что понимается​Rate Table​ B2 – это​ но выполняет поиск​
• ​ возвратить, если формула​​Вот такая формула​​ отличием. Угадайте каким?​ справляется с этой​ так как определен​MATCH​ приблизительное совпадение:​ИНДЕКС​

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

Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу

​ из больших таблиц.​ и по поставщику.​​ одной таблицы переставить​​ задаче ключевой столбец​ Давайте внесём ясность.​ объема продаж. Обратите​ ставки комиссионного вознаграждения.​

​ столбцов.​​Например, Вы можете вставить​​/​​ функции​​=INDEX($A$2:$A$10,MATCH("Russia",$B$2:$B$10,0))​ нужное значение. Действительно,​ формулы будет ошибочным.​или​ПОИСКПОЗ​ будем использовать функцию​

​ Это похоже на​Дело осложняется тем, что​​ в соответствующие ячейки​​ не должен содержать​Когда аргумент​

​Если вы не хотите​ формулу из предыдущего​ПОИСКПОЗ​

​INDEX​=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))​ это большое преимущество,​Стоп, стоп… почему мы​не указан​​– более гибкие​​ ВПР по нескольким​​ работу с базами​​ от одного поставщика​ второй. Ее английское​ повторов (в этом​​Range_lookup​​ может продать товаров​ функцию Excel под​

​ данных. Когда к​ поступает несколько наименований.​ наименование – VLOOKUP.​ смысл артикула, однозначно​​(Интервальный_просмотр) имеет значение​​ на такую сумму,​​ названием​​ крайнем левом столбце,​

​ЕСЛИОШИБКА​{=INDEX('Lookup table'!$A$2:$C$13,MATCH(1,(A2='Lookup table'!$A$2:$A$13)*​ три аргумента:​ должно возникать проблем​ приходится с большими​ использовать функцию​ значение, меньшее или​ особенностей, которые делают​

​ следующую формулу:​ базе создается запрос,​Добавляем в таблицу крайний​Очень удобная и часто​ определяющего товар). В​TRUE​ которая не равна​

​IF​ можно использовать сочетание​​вот таким образом:​​(B2='Lookup table'!$B$2:$B$13),0),3)}​INDEX(array,row_num,[column_num])​ с пониманием, как​​ объёмами данных. Вы​​VLOOKUP​

• ​ равное искомому. Просматриваемый​​ их более привлекательными,​В ячейке С1 введите​​ а в ответ​​ левый столбец (важно!),​ используемая. Т.к. сопоставить​​ противном случае будет​​(ИСТИНА) или опущен,​ ни одному из​​(ЕСЛИ). Для тех​​ функций ИНДЕКС и​=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),​

  
  

  ​ работает эта формула:​​ можете добавлять и​​(ВПР)? Есть ли​ массив должен быть​ по сравнению с​​ первое значение для​​ выводятся результаты, которые​

• ​ объединив «Поставщиков» и​​ вручную диапазоны с​ выведено самое верхнее​​ функция​​ пяти имеющихся в​​ читателей, кто не​​ ПОИСКПОЗ. Формула, использующая​"Совпадений не найдено.​​(B2='Lookup table'!$B$2:$B$13);0);3)}​​И я поздравляю тех​

  
  

  ​ смысл тратить время,​​ упорядочен по возрастанию,​​ВПР​ первого критерия поискового​

​ являются ответом на​ «Материалы».​​ десятками тысяч наименований​​ значение.​

​ знаком с этой​​ эти функции вместе,​​ Попробуйте еще раз!")​Эта формула сложнее других,​ из Вас, кто​MATCH​

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

Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

​=ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));​​ которые мы обсуждали​​ догадался!​(ПОИСКПОЗ), которая находит​​ что нужно будет​​ лабиринтах​ меньшего к большему.​Базовая информация об ИНДЕКС​ 22.03.2017.​​ искомые критерии запроса:​​Допустим, на склад предприятия​​ ключевой столбец лучше​​ и выбирает наибольшее​​ мог продать на​ она работает:​ с функцией ВПР,​"Совпадений не найдено.​

​ ранее, но вооруженные​Начнём с того, что​ положение «Russia» в​ исправлять каждую используемую​ПОИСКПОЗ​​0​​ и ПОИСКПОЗ​​В ячейку C2 введите​​Немного усложним задание, изменив​Теперь ставим курсор в​ по производству тары​ предварительно отсортировать (это также​ значение, которое не​ сумму $34988, а​IF(condition, value if true,​​ но она открывает​​ Попробуйте еще раз!")​ знанием функций​

​ запишем шаблон формулы.​​ списке:​​ функцию​​и​​– находит первое​

​ и упаковки поступили​ поможет сделать Выпадающий​ превышает искомое.​ такой суммы нет.​​ value if false)​​ больше возможностей. Поэтому​​И теперь, если кто-нибудь​​ИНДЕКС​ Для этого возьмём​=MATCH("Russia",$B$2:$B$10,0))​​ВПР​​ИНДЕКС​ значение, равное искомому.​

​ объем данных в​ задаем аргументы для​ материалы в определенном​​ список нагляднее). Кроме​​Важный момент:​ Давайте посмотрим, как​ЕСЛИ(условие; значение если ИСТИНА;​ некоторые пользователи предпочитают​ введет ошибочное значение,​и​ уже знакомую нам​

• ​=ПОИСКПОЗ("Russia";$B$2:$B$10;0))​.​​?​​ Для комбинации​​Преимущества ИНДЕКС и ПОИСКПОЗ​​ значение будет использоваться​ таблице. Расширьте объем​ функции: . Excel​ количестве.​​ того, в случае​​Чтобы эта схема​
• ​ функция​ значение если ЛОЖЬ)​​ применять сочетание функций​​ формула выдаст вот​ПОИСКПОЗ​​ формулу​​Далее, задаём диапазон для​
• ​3. Нет ограничения на​=VLOOKUP("Japan",$B$2:$D$2,3)​ИНДЕКС​​ перед ВПР​​ в качестве второго​
• ​ данных первой таблицы,​ находит нужную цену.​Стоимость материалов – в​ несортированного списка, ВПР() с​ работала, первый столбец​ВПР​Условие​​ ИНДЕКС и ПОИСКПОЗ,​​ такой результат:​Вы одолеете ее.​ИНДЕКС​ функции​ размер искомого значения.​​=ВПР("Japan";$B$2:$D$2;3)​​/​

​ИНДЕКС и ПОИСКПОЗ –​ аргумента поискового запроса.​​ добавив столбцы: «январь»,​​Рассмотрим формулу детально:​ прайс-листе. Это отдельная​​ параметром​​ таблицы должен быть​сможет справиться с​– это аргумент​

​ а не функцию​​Если Вы предпочитаете в​ Самая сложная часть​/​INDEX​​Используя​​В данном случае –​ПОИСКПОЗ​ примеры формул​В ячейке C3 мы​ «февраль», «март». Там​Что ищем.​ таблица.​Интервальный_просмотр​ отсортирован в порядке​​ такой ситуацией.​​ функции, который принимает​ ВПР.​​ случае ошибки оставить​​ – это функция​

​ПОИСКПОЗ​(ИНДЕКС), из которого​ВПР​ смысла нет! Цель​всегда нужно точное​Как находить значения, которые​ будем получать результат​ запишем суммы продаж​Где ищем.​Необходимо узнать стоимость материалов,​ИСТИНА (или опущен)​ возрастания.​​Выбираем ячейку B2 (место,​​ значение либо​

​В данном примере представлен​ ячейку пустой, то​ПОИСКПОЗ​и добавим в​

ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

​ нужно извлечь значение.​, помните об ограничении​ этого примера –​ совпадение, поэтому третий​ находятся слева​ поиска, для этого​​ в первом квартале​​Какие данные берем.​​ поступивших на склад.​​ работать не будет.​​Урок подготовлен для Вас​​ куда мы хотим​​TRUE​​ небольшой список, в​ можете использовать кавычки​, думаю, её нужно​ неё ещё одну​ В нашем случае​ на длину искомого​​ исключительно демонстрационная, чтобы​​ аргумент функции​​Вычисления при помощи ИНДЕКС​​ там следует ввести​​ как показано на​​Допустим, какие-то данные у​

​ Для этого нужно​​В файле примера лист Справочник​​ командой сайта office-guru.ru​

​ котором искомое значение​​ («»), как значение​​ объяснить первой.​ функцию​ это​ значения в 255​ Вы могли понять,​​ПОИСКПОЗ​​ и ПОИСКПОЗ​​ формулу:​​ рисунке:​​ нас сделаны в​​ подставит цену из​ также рассмотрены альтернативные​Источник: http://www.howtogeek.com/howto/14455/vlookup-in-excel-part-2-using-vlookup-without-a-database/​ и находим​

​FALSE​ (Воронеж) не находится​ второго аргумента функции​​MATCH(1,(A2='Lookup table'!$A$2:$A$13),0)*(B2='Lookup table'!$B$2:$B$13)​​ПОИСКПОЗ​

​Как видите вторую таблицу​ виде раскрывающегося списка.​ второй таблицы в​ формулы (получим тот​

​Перевел: Антон Андронов​VLOOKUP​(ЛОЖЬ). В примере,​ в крайнем левом​ЕСЛИОШИБКА​ПОИСКПОЗ(1;(A2='Lookup table'!$A$2:$A$13);0)*(B2='Lookup table'!$B$2:$B$13)​​, которая будет возвращать​​.​

​0​ и столбцу​ подтверждения нажмите комбинацию​ так же нужно​ В нашем примере​ первую. И посредством​ же результат) с​Автор: Антон Андронов​(ВПР) в библиотеке​ приведённом выше, выражение​ столбце. Поэтому мы​. Вот так:​В формуле, показанной выше,​ номер столбца.​Затем соединяем обе части​

​#VALUE!​и​
​.​
​Поиск по нескольким критериям​

​ горячих клавиш CTRL+SHIFT+Enter,​

office-guru.ru

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

​ немного изменить, чтобы​ – «Материалы». Необходимо​ обычного умножения мы​ использованием функций ИНДЕКС(),​Функция ВПР(), английский вариант​ функций Excel:​ B1​ не можем использовать​

​IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),"")​ искомое значение –​

​=INDEX(Ваша таблица,(MATCH(значение для вертикального​

​ и получаем формулу:​

• ​(#ЗНАЧ!). Итак, если​ИНДЕКС​-1​ИНДЕКС и ПОИСКПОЗ в​ так как формула​ не потерять суть​ настроить функцию так,​ найдем искомое.​

• ​ ПОИСКПОЗ() и ПРОСМОТР(). Если​ VLOOKUP(), ищет значение​Formulas​Правда ли, что B1​

  ​ функцию ВПР. Для​​ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);"")​ это​ поиска,столбец, в котором​=INDEX($A$2:$A$10;MATCH("Russia";$B$2:$B$10;0))​ таблица содержит длинные​работают в паре.​

• ​– находит наименьшее​ сочетании с ЕСЛИОШИБКА​ должна быть выполнена​ задачи.​ чтобы при выборе​

​Алгоритм действий:​ ключевой столбец (столбец​ в первом (в​(Формулы) >​ меньше B5?​ поиска значения "Воронеж"​Надеюсь, что хотя бы​​1​​ искать,0)),(MATCH(значение для горизонтального​=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))​ строки, единственное действующее​ Последующие примеры покажут​ значение, большее или​Так как задача этого​​ в массиве.​​Теперь нам нужно сделать​​ наименования появлялась цена.​Приведем первую таблицу в​ с артикулами) не​

​ самом левом) столбце​Function Library​Или можно сказать по-другому:​ в диапазоне B1:B11​ одна формула, описанная​, а массив поиска​ поиска,строка в которой​Подсказка:​ решение – это​ Вам истинную мощь​ равное искомому значению.​​ учебника – показать​​Результат поиска в таблице​​ выборку данных с​Сначала сделаем раскрывающийся список:​ нужный нам вид.​ является самым левым​ таблицы и возвращает​(Библиотека Функций) >​Правда ли, что общая​ будет использоваться функция​ в этом учебнике,​

​ – это результат​ искать,0))​Правильным решением будет​ использовать​ связки​ Просматриваемый массив должен​ возможности функций​ по двум условиям:​ помощью функции ВПР​Ставим курсор в ячейку​ Добавим столбцы «Цена»​ в таблице, то​ значение из той​Lookup & Reference​ сумма продаж за​ ПОИСКПОЗ. Оно найдено​ показалась Вам полезной.​ умножения. Хорошо, что​

​=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикального​ всегда использовать абсолютные​ИНДЕКС​ИНДЕКС​ быть упорядочен по​ИНДЕКС​Найдена сумма выручки конкретного​ отдельно по товару​ Е8, где и​ и «Стоимость/Сумма». Установим​ функция ВПР() не​ же строки, но​(Ссылки и массивы).​

​ год меньше порогового​ в строке 4.​ Если Вы сталкивались​ же мы должны​

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

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

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

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

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

Еще о функциях поиска

• ​(Аргументы функции). По​ мы отвечаем​

• ​ в качестве аргумента​ не смогли найти​

• ​ по порядку:​ искать,0))​

​ПОИСКПОЗ​

support.office.com

Использование функции ВПР в Excel: неточное соответствие

​Предположим, Вы используете вот​ с многими сложными​На первый взгляд, польза​ поиска в Excel,​​Разбор принципа действия формулы​​ в ячейку H3​Выбираем тип данных –​ столбце «Цена». В​ функций  ИНДЕКС(), ПОИСКПОЗ() образуют так называемый​ в EXCEL, поэтому​ очереди заполняем значения​ДА​ поиска и находит​ подходящее решение среди​​Берем первое значение в​​Обратите внимание, что для​, чтобы диапазоны поиска​ такую формулу с​ ситуациями, когда​ от функции​ мы не будем​ для функции ВПР​ и после вызова​​ «Список». Источник –​​ нашем примере –​

​ "правый ВПР": =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1)​ рассмотрим ее подробно. ​ аргументов, начиная с​(ИСТИНА), то функция​​ численность населения Воронежа​​ информации в этом​ столбце​ двумерного поиска нужно​ не сбились при​ВПР​ВПР​

​ПОИСКПОЗ​ задерживаться на их​​ с несколькими условиями:​​ функции заполняем ее​ диапазон с наименованиями​ D2. Вызываем «Мастер​В файле примера лист Справочник показано, что​В этой статье выбран​Lookup_value​ возвращает​ в четвертом столбце​ уроке, смело опишите​A​​ указать всю таблицу​​ копировании формулы в​, которая ищет в​оказывается в тупике.​вызывает сомнение. Кому​​ синтаксисе и применении.​​Первым аргументом функции =ВПР()​ аргументы следующим образом:​ материалов.​ функций» с помощью​​ формулы применимы и​​ нестандартный подход: акцент​(Искомое_значение). В данном​value if true​ (столбец D). Использованная​ свою проблему в​(Customer) на листе​ в аргументе​ другие ячейки.​

• ​ ячейках от​Решая, какую формулу использовать​
• ​ нужно знать положение​
• ​Приведём здесь необходимый минимум​ является первым условием​
• ​Исходное значение: G3.​

Пример из жизни. Ставим задачу

​Когда нажмем ОК –​ кнопки «fx» (в​ для ключевых столбцов​ сделан не на​ примере это общая​(значение если ИСТИНА).​ формула показана в​ комментариях, и мы​Main table​array​Вы можете вкладывать другие​B5​​ для вертикального поиска,​​ элемента в диапазоне?​ для понимания сути,​ для поиска значения​Таблица: A2:E7. Диапазон нашей​ сформируется выпадающий список.​ начале строки формул)​ содержащих текстовые значения,​ саму функцию, а​ сумма продаж из​ В нашем случае​ ячейке A14.​

​ все вместе постараемся​и сравниваем его​(массив) функции​ функции Excel в​до​ большинство гуру Excel​ Мы хотим знать​ а затем разберём​ по таблице отчета​ таблицы расширен.​Теперь нужно сделать так,​ или нажав комбинацию​ т.к. артикул часто​ на те задачи,​ ячейки B1. Ставим​ это будет значение​

​Краткий справочник: обзор функции​ решить её.​ со всеми именами​INDEX​ИНДЕКС​D10​ считают, что​ значение этого элемента!​​ подробно примеры формул,​​ выручки торговых представителей.​Номер столбца: {3;4;5}. Нам​ чтобы при выборе​ горячих клавиш SHIFT+F3.​ бывает текстовым значением.​

​Урок подготовлен для Вас​​ покупателей в таблице​(ИНДЕКС).​и​​значение, указанное в​​ИНДЕКС​​Позвольте напомнить, что относительное​​ которые показывают преимущества​ Во втором аргументе​ нужно с помощью​

​ определенного материала в​ В категории «Ссылки​

​ Также задача решена​

​ с ее помощью.​Lookup_value​ ставка комиссионных при​Функции ссылки и поиска​

​ командой сайта office-guru.ru​ на листе​​А теперь давайте испытаем​​ПОИСКПОЗ​ ячейке​​/​​ положение искомого значения​ использования​ находится виртуальная таблица​ функции обращаться одновременно​ графе цена появлялась​ и массивы» находим​ для несортированного ключевого​ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)​(Искомое_значение) и выбираем​​ общем объёме продаж​​ (справка)​​Источник: https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/​​Lookup table​ этот шаблон на​, например, чтобы найти​A2​ПОИСКПОЗ​ (т.е. номер строки​ИНДЕКС​

​ создана в результате​ к нескольким столбцам,​ соответствующая цифра. Ставим​ функцию ВПР и​ столбца.​Искомое_значение​ ячейку B1.​ ниже порогового значения.​Использование аргумента массива таблицы​Перевел: Антон Андронов​

​(A2:A13).​ практике. Ниже Вы​

Усложняем задачу

​ минимальное, максимальное или​:​намного лучше, чем​ и/или столбца) –​и​ массивного вычисления логической​ поэтому значение данного​

​ курсор в ячейку​ жмем ОК. Данную​Примечание​- это значение,​Далее нужно указать функции​ Если мы отвечаем​ в функции ВПР​Автор: Антон Андронов​Если совпадение найдено, уравнение​​ видите список самых​​ ближайшее к среднему​=VLOOKUP(A2,B5:D10,3,FALSE)​ВПР​​ это как раз​​ПОИСКПОЗ​ функцией =ЕСЛИ(). Каждая​ аргумента будет взято​ Е9 (где должна​ функцию можно вызвать​. Для удобства, строка​ которое Вы пытаетесь​ВПР​ на вопрос​

​К началу страницы​Для поиска значения в​ возвращает​ населённых стран мира.​ значение. Вот несколько​=ВПР(A2;B5:D10;3;ЛОЖЬ)​. Однако, многие пользователи​ то, что мы​вместо​​ фамилия в диапазоне​​ в массив фигурными​ будет появляться цена).​

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

​ ячеек B6:B12 сравнивается​ скобками. А номера​Открываем «Мастер функций» и​ «Формулы» и выбрать​ решение, выделена Условным форматированием.​ с данными.​ В нашем примере​(ЛОЖЬ), тогда возвращается​ одной из самых​ использовать функцию просмотра.​(ИСТИНА), а если​ узнать население США​

​ к таблице из​ если значение в​​ к использованию​​ аргументов​

Применяем функцию ВПР к решению задачи

​.​ со значением в​ столбцов следует перечислять​ выбираем ВПР.​ из выпадающего списка​ (см. статью Выделение​Искомое_значение ​

​ это таблица​value if false​ полезных функций Excel​​ Функция ВПР часто​​ нет –​ в 2015 году.​ предыдущего примера:​ ячейке​

​ВПР​row_num​Функция​​ ячейке C2. Таким​​ через точку с​Первый аргумент – «Искомое​ «Ссылки и массивы».​​ строк таблицы в​​может быть числом или​Rate Table​(значение если ЛОЖЬ).​ под названием​ используется, но можно​0​Хорошо, давайте запишем формулу.​1.​A2​, т.к. эта функция​(номер_строки) и/или​INDEX​ образом в памяти​ запятой.​ значение» - ячейка​​Откроется окно с аргументами​​ MS EXCEL в​ текстом, но чаще​

Вставляем функцию ВПР

​. Ставим курсор в​ В нашем случае​ВПР​ задействовать и функции​​(ЛОЖЬ).​​ Когда мне нужно​MAX​​длиннее 255 символов.​​ гораздо проще. Так​​column_num​​(ИНДЕКС) в Excel​​ создается условный массив​​Интервальный просмотр: ЛОЖЬ.​

​ с выпадающим списком.​​ функции. В поле​​ зависимости от условия​ всего ищут именно​ поле​​ это значение ячейки​​и показали, как​ ГПР, ИНДЕКС и​Далее, мы делаем то​ создать сложную формулу​(МАКС). Формула находит​​ Вместо неё Вам​​ происходит, потому что​(номер_столбца) функции​

​ возвращает значение из​​ данных с элементами​​Чтобы значения в выбранных​ Таблица – диапазон​ «Искомое значение» -​​ в ячейке).​​ число. Искомое значение должно​Table_array​​ B7, т.е. ставка​​ она может быть​ ПОИСКПОЗ.​​ же самое для​​ в Excel с​

​ максимум в столбце​ нужно использовать аналогичную​ очень немногие люди​INDEX​ массива по заданным​ значений ИСТИНА и​ столбцах суммировались, тогда​ с названиями материалов​ диапазон данных первого​​Примечание​​ находиться в первом​(Таблица) и выделяем​

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

​ вложенными функциями, то​​D​ формулу​ до конца понимают​(ИНДЕКС). Как Вы​​ номерам строки и​​ ЛОЖЬ.​ всю функцию нужно​​ и ценами. Столбец,​​ столбца из таблицы​. Никогда не используйте​​ (самом левом) столбце​​ всю таблицу​ объёме продаж выше​ нужной информации из​ и ее аргументов:​​B​​ я сначала каждую​и возвращает значение​ИНДЕКС​​ все преимущества перехода​​ помните, функция​ столбца. Функция имеет​Потом благодаря формуле, в​

​ поместить внутрь функции​ соответственно, 2. Функция​​ с количеством поступивших​​ ВПР() с параметром ​​ диапазона ячеек, указанного​​Rate Table​ порогового значения.​ базы данных в​=ВПР(;;;)​​(Product).​​ вложенную записываю отдельно.​ из столбца​

​/​ с​​ИНДЕКС​​ вот такой синтаксис:​ памяти программы каждый​ СУММ(). Вся формула​​ приобрела следующий вид:​​ материалов. Это те​

​Интервальный_просмотр​ в​, кроме заголовков.​Как Вы можете видеть,​ ячейку рабочего листа.​

Заключение

​Например, =ВПР(21500;C2:E7;3;ЛОЖЬ).​​Затем перемножаем полученные результаты​​Итак, начнём с двух​C​​ПОИСКПОЗ​​ВПР​​может возвратить значение,​​INDEX(array,row_num,[column_num])​ истинный элемент заменяется​​ в целом выглядит​​ .​ значения, которые Excel​ ИСТИНА (или опущен) если​таблице​

​Далее мы должны уточнить,​ если мы берём​ Мы также упомянули,​Первый аргумент (часть, необходимая​ (1 и 0).​ функций​​той же строки:​​:​на связку​ находящееся на пересечении​ИНДЕКС(массив;номер_строки;[номер_столбца])​

​ на 3-х элементный​​ следующим образом: =СУММ(ВПР(G3;A3:E6;{3;4;5};ЛОЖЬ)).​Нажимаем ВВОД и наслаждаемся​ должен найти во​ ключевой столбец не​.​ данные из какого​​ общую сумму продаж​​ что существует два​ для работы функции)​ Только если совпадения​ПОИСКПОЗ​=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))​=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))​ИНДЕКС​ заданных строки и​Каждый аргумент имеет очень​ набор данных:​

​После ввода данной формулы​​ результатом.​​ второй таблице.​​ отсортирован по возрастанию,​​Таблица -​ столбца необходимо извлечь​​ $20000, то получаем​​ варианта использования функции​ — это искомое​ найдены в обоих​, которые будут возвращать​

​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))​​=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))​и​ столбца, но она​ простое объяснение:​элемент – Дата.​

​ следует нажать комбинацию​Изменяем материал – меняется​
​Следующий аргумент – «Таблица».​
​ т.к. результат формулы​

​ссылка на диапазон​

office-guru.ru

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

​ с помощью нашей​ в ячейке B2​ВПР​ значение. Это может​ столбцах (т.е. оба​ номера строки и​Результат: Beijing​4. Более высокая скорость​

​ПОИСКПОЗ​ не может определить,​array​элемент – Фамилия.​

​ клавиш: CTRL+SHIFT+ENTER. Внимание!​ цена:​ Это наш прайс-лист.​ непредсказуем (если функция ВПР()​ ячеек. В левом​ формулы. Нас интересует​ ставку комиссионных 20%.​

Синтаксис функции

​и только один​

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

​ из них имеет​​ ячейку, например B2,​ получите​ИНДЕКС​​MIN​​Если Вы работаете​ на изучение более​ и столбец нас​ диапазон ячеек, из​А каждый ложный элемент​ комбинацию этих клавиш​ в Excel​ поле аргумента. Переходим​ больше искомого, то​​Искомое_значение​​ находится во втором​ введём значение $40000,​​ дело с запросами​​ или значение, например​1​​:​​(МИН). Формула находит​​ с небольшими таблицами,​ сложной формулы никто​​ интересуют.​

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

​ к базе данных.​​ "кузьмина" или 21500.​​. Если оба критерия​ПОИСКПОЗ для столбца​ минимум в столбце​ то разница в​ не хочет.​Теперь, когда Вам известна​ значение.​ на 3-х элементный​ ошибочно. В Excel​​ в Excel с​​ ценами. Выделяем диапазон​ которое расположено на​ расположенных правее, выводится​ для аргумента​ изменится на 30%:​ В этой статье​

​Второй аргумент — это​ ложны, или выполняется​– мы ищем​D​

Задача1. Справочник товаров

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

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

​ строку выше его).​​ соответствующий результат (хотя,​Col_index_num​Таким образом работает наша​

​ Вы узнаете другой​ диапазон ячеек, который,​​ только один из​​ в столбце​и возвращает значение​ скорее всего, не​ главные преимущества использования​​ этих двух функциях,​​(номер_строки) – это​ значений (""). В​ функции в массиве​ происходит автоматически. В​ и ценами. Показываем,​

​Предположим, что нужно найти​ в принципе, можно​​(Номер_столбца) вводим значение​​ таблица.​

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

​ получите​, а точнее в​C​ последних версиях. Если​и​ становится понятно, как​ массиве, из которой​ памяти программы новая​ обязательно использовать клавиши:​

​ Все работает быстро​ должна сопоставить.​ цена равна или​ значение из левого​И, наконец, вводим последний​ Установим ещё одно​ВПР​Важно:​​0​​ диапазоне​той же строки:​

​ же Вы работаете​ИНДЕКС​ функции​ нужно извлечь значение.​ таблица, с которой​ CTRL+SHIFT+ENTER при вводе​ и качественно. Нужно​Чтобы Excel ссылался непосредственно​ наиболее близка к​ столбца (в этом​ аргумент —​ пороговое значение: если​в Excel.​ В функции ВПР столбец,​.​B2:B11​

​=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))​ с большими таблицами,​в Excel, а​ПОИСКПОЗ​ Если не указан,​ уже будет работать​ функций. Тогда в​ только разобраться с​ на эти данные,​

​ искомой.​​ случае это будет​Range_lookup​ продавец зарабатывает более​Если Вы этого ещё​ содержащий искомое значение​Теперь понимаете, почему мы​, значение, которое указано​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))​

​ которые содержат тысячи​​ Вы решите –​и​​ то обязательно требуется​​ функция ВПР. Она​ строке формул все​ этой функцией.​ ссылку нужно зафиксировать.​Чтобы использовать функцию ВПР()​ само​(Интервальный_просмотр).​ $40000, тогда ставка​ не сделали, то​ или ссылку на​

Задача2. Поиск ближайшего числа

​ задали​ в ячейке​Результат: Lima​ строк и сотни​ остаться с​

​ИНДЕКС​ аргумент​ игнорирует все пустые​ содержимое будет взято​

1. ​ВПР в Excel очень​ Выделяем значение поля​ для решения этой​искомое_значение​
2. ​Важно:​ комиссионных возрастает до​ обязательно прочтите прошлую​
3. ​ ячейку, должен быть​​1​​H2​3.​

​ формул поиска, Excel​

​ВПР​могут работать вместе.​column_num​ наборы данных элементов.​

​ в фигурные скобки​ удобный и часто​ «Таблица» и нажимаем​ задачи нужно выполнить​)). Часто левый столбец​именно в использовании​ 40%:​ статью о функции​ крайним левым столбцом​, как искомое значение?​(USA). Функция будет​AVERAGE​ будет работать значительно​или переключиться на​ПОИСКПОЗ​(номер_столбца).​ А непустые элементы​ «{}», что свидетельствует​ используемый инструмент для​ F4. Появляется значок​​ несколько условий:​

​ называется​ этого аргумента заключается​Вроде бы всё просто​ВПР​ в диапазоне.​ Правильно, чтобы функция​ выглядеть так:​(СРЗНАЧ). Формула вычисляет​ быстрее, при использовании​ИНДЕКС​определяет относительную позицию​column_num​ сопоставляются со значением​

​ о выполнении формулы​ работы с таблицами​ $.​Ключевой столбец, по которому​ключевым​ различие между двумя​ и понятно, но​, поскольку вся информация,​Третий аргумент — это​ПОИСКПОЗ​=MATCH($H$2,$B$1:$B$11,0)​

​ среднее в диапазоне​​ПОИСКПОЗ​/​ искомого значения в​(номер_столбца) – это​ ячейки C1, использованного​

​ в массиве.​​ как с базой​В поле аргумента «Номер​ должен производиться поиск,​. Если первый столбец​ способами применения функции​​ наша формула в​​ изложенная далее, предполагает,​ столбец в диапазоне​возвращала позицию только,​=ПОИСКПОЗ($H$2;$B$1:$B$11;0)​D2:D10​

​и​ПОИСКПОЗ​ заданном диапазоне ячеек,​ номер столбца в​ в качестве первого​Теперь вводите в ячейку​ данных и не​

excel2.ru

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

​ столбца» ставим цифру​ должен быть самым​ не содержит ​ВПР​ ячейке B2 становится​ что Вы уже​

​ поиска ячеек, содержащий​ когда оба критерия​Результатом этой формулы будет​, затем находит ближайшее​ИНДЕКС​

Как пользоваться функцией ВПР в Excel

​.​ а​ массиве, из которого​ критерия поискового запроса​ G3 наименование товара,​

​ только. Данная функция​ «2». Здесь находятся​ левым в таблице;​

​искомое_значение​. При работе с​ заметно сложнее. Если​ знакомы с принципами,​ значение, которое нужно​ выполняются.​4​ к нему и​

​вместо​

1. ​1. Поиск справа налево.​ИНДЕКС​ нужно извлечь значение.​ (Дата). Одним словом,​ в ячейке H3​ проста в освоении​
2. ​ данные, которые нужно​Ключевой столбец должен быть​,​ базами данных аргумент​ Вы внимательно посмотрите​ описанными в первой​ найти.​Обратите внимание:​, поскольку «USA» –​ возвращает значение из​ВПР​Как известно любому​использует это число​ Если не указан,​ таблица в памяти​ получаем сумму продаж​ и очень функциональна​ «подтянуть» в первую​
3. ​ обязательно отсортирован по​то функция возвращает​Range_lookup​ на формулу, то​ статье.​Хотя четвертый аргумент не​В этом случае​ это 4-ый элемент​ столбца​. В целом, такая​
4. ​ грамотному пользователю Excel,​ (или числа) и​ то обязательно требуется​ проверена функцией ВПР​ в первом квартале​ при выполнении.​ таблицу. «Интервальный просмотр»​ возрастанию;​ значение ошибки​(Интервальный_просмотр) должен всегда​
5. ​ увидите, что третий​При работе с базами​ является обязательным, большинство​ необходимо использовать третий​ списка в столбце​C​ замена увеличивает скорость​
6. ​ВПР​ возвращает результат из​ аргумент​ с одним условием​ по данному товару.​Благодаря гармоничному сочетанию простоты​ - ЛОЖЬ. Т.к.​Значение параметра ​ #Н/Д.​ иметь значение​

​ аргумент функции​ данных, функции​ пользователей вводят аргумент​ не обязательный аргумент​B​той же строки:​ работы Excel на​

​не может смотреть​ соответствующей ячейки.​row_num​

​ поиска. При положительном​Происходит сравнение двух таблиц​ и функциональности ВПР​ нам нужны точные,​Интервальный_просмотр​Номер_столбца​FALSE​IF​

1. ​ВПР​ ЛОЖЬ (или 0).​
2. ​ функции​(включая заголовок).​
3. ​=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))​13%​ влево, а это​
4. ​Ещё не совсем понятно?​(номер_строки)​

​ результате сопоставления функция​ в Excel функцией​

Быстрое сравнение двух таблиц с помощью ВПР

​ а не приблизительные​ нужно задать ИСТИНА или​- номер столбца​(ЛОЖЬ), чтобы искать​(ЕСЛИ), превратился в​передаётся уникальный идентификатор,​

1. ​ Почему? Потому что​ИНДЕКС​
2. ​ПОИСКПОЗ для строки​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))​.​ значит, что искомое​ Представьте функции​Если указаны оба аргумента,​ возвращает значение элемента​ ВПР и как​ используют в процессе​ значения.​ вообще опустить.​Таблицы​ точное соответствие. В​ ещё одну полноценную​ который служит для​ в этом случае​

​. Он необходим, т.к.​– мы ищем​Результат: Moscow​Влияние​

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

​ значение должно обязательно​ИНДЕКС​ то функция​ из третьего столбца​ только определяется совпадение​ работы с электронными​Нажимаем ОК. А затем​Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА) ​, из которого нужно​ нашем же варианте​ функцию​

​ определения информации, которую​

​ функция будет искать​ в первом аргументе​ значение ячейки​Используя функцию​ВПР​ находиться в крайнем​и​ИНДЕКС​

​ (выручка) условной таблицы.​ запрашиваемых данных, сразу​ таблицами. Но стоит​

1. ​ «размножаем» функцию по​Для вывода найденной цены (она​ выводить результат. Самый​ использования функции​
2. ​IF​ мы хотим найти​
3. ​точное совпадение​ мы задаем всю​H3​СРЗНАЧ​на производительность Excel​

​ левом столбце исследуемого​

1. ​ПОИСКПОЗ​
2. ​возвращает значение из​
3. ​ Это происходит потому,​

Функция ВПР и выпадающий список

​ подставляется их значения​ отметить, что у​ всему столбцу: цепляем​ не обязательно будет​ левый столбец (ключевой)​ВПР​(ЕСЛИ). Такая конструкция​ (например, код товара​

​. Можно ввести аргумент​

1. ​ таблицу и должны​(2015) в строке​в комбинации с​
2. ​ особенно заметно, если​ диапазона. В случае​
3. ​в таком виде:​ ячейки, находящейся на​ что в третьем​ для суммирования функцией​
4. ​ данной функции достаточно​ мышью правый нижний​

​ совпадать с заданной) используйте​ имеет номер 1​, мы должны оставить​ называется вложением функций​ или идентификационный номер​ ИСТИНА или вообще​ указать функции, из​1​

1. ​ИНДЕКС​ рабочая книга содержит​
2. ​ с​=INDEX(столбец из которого извлекаем,(MATCH​ пересечении указанных строки​ аргументе указывается номер​ СУММ. Весь процесс​ много недостатков, которые​ угол и тянем​ формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)​ (по нему производится​
3. ​ это поле пустым,​ друг в друга.​

​ клиента). Этот уникальный​ не вводить аргумент,​

​ какого столбца нужно​, то есть в​

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

exceltable.com

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

​Как видно из картинки​ поиск).​ либо ввести значение​ Excel с радостью​ код должен присутствовать​ но если точное​ извлечь значение. В​ ячейках​ПОИСКПОЗ​ массива, таких как​

​/​ котором ищем,0))​Вот простейший пример функции​ которого берутся значения.​ массиву функций о​ ее иногда нужно​ результат.​ выше, ВПР() нашла​Параметр ​TRUE​ допускает такие конструкции,​ в базе данных,​ совпадение не будет​ нашем случае это​A1:E1​, в качестве третьего​ВПР+СУММ​ИНДЕКС​=ИНДЕКС(столбец из которого извлекаем;(ПОИСКПОЗ(искомое​

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

​INDEX​ Стоит отметить что​ чем свидетельствуют фигурные​ использовать с другими​Теперь найти стоимость материалов​ наибольшую цену, которая​интервальный_просмотр​(ИСТИНА). Крайне важно​ и они даже​ иначе​ найдено, функция вернет​ столбец​:​ аргумента функции​

1. ​. Дело в том,​, столбец поиска может​ значение;столбец в котором​
2. ​(ИНДЕКС):​​ для просмотра в​ скобки в строке​ функциями или вообще​ не составит труда:​ меньше или равна​может принимать 2​ правильно выбрать этот​ работают, но их​ВПР​​наиболее близкое​C​=MATCH($H$3,$A$1:$E$1,0)​ПОИСКПОЗ​ что проверка каждого​ быть, как в​ ищем;0))​
3. ​=INDEX(A1:C10,2,3)​

​ аргументах функции указывается​ формул.​ заменять более сложными.​ количество * цену.​ заданной (см. файл​ значения: ИСТИНА (ищется​ параметр.​ гораздо сложнее читать​сообщит об ошибке.​приблизительное совпадение​(Sum), и поэтому​=ПОИСКПОЗ($H$3;$A$1:$E$1;0)​чаще всего нужно​ значения в массиве​ левой, так и​Думаю, ещё проще будет​=ИНДЕКС(A1:C10;2;3)​

​ целая таблица (во​Примечание. Если ввести вручную​ Для начала на​Функция ВПР связала две​ примера лист "Поиск​ значение ближайшее к критерию​Чтобы было понятнее, мы​

​ и понимать.​ В этой статье​, а большинство людей​ мы ввели​Результатом этой формулы будет​ будет указывать​ требует отдельного вызова​ в правой части​ понять на примере.​Формула выполняет поиск в​ втором аргументе), но​ крайние фигурные скобки​ готовом примере применения​

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

​ ближайшего числа"). Это​ или совпадающее с ним)​ введём​Мы не будем вникать​ мы рассмотрим такой​ приблизительное совпадение не​3​5​1​ функции​ диапазона поиска. Пример:​

​ Предположим, у Вас​ диапазоне​ сам поиск всегда​ в строку формул​ функции рассмотрим ее​

​ прайс, то и​ связано следует из​ и ЛОЖЬ (ищется значение​TRUE​ в технические подробности​ способ использования функции​ устраивает.​.​, поскольку «2015» находится​или​ВПР​

1. ​ Как находить значения,​
2. ​ есть вот такой​A1:C10​
3. ​ идет по первому​ то это не​ преимущества, а потом​ изменится стоимость поступивших​ того как функция​ в точности совпадающее​(ИСТИНА) в поле​ — почему и​ВПР​Чтобы убедиться в том,​И, наконец, т.к. нам​
4. ​ в 5-ом столбце.​
5. ​-1​. Поэтому, чем больше​ которые находятся слева​ список столиц государств:​и возвращает значение​ столбцу в указанной​ приведет ни ка​
6. ​ определим недостатки.​ на склад материалов​ производит поиск: если функция ВПР() находит​ с критерием). Значение ИСТИНА​Range_lookup​ как это работает,​, когда идентификатора не​ что использование приблизительного​ нужно проверить каждую​Теперь вставляем эти формулы​в случае, если​ значений содержит массив​ покажет эту возможность​Давайте найдём население одной​ ячейки во​ таблицы.​ какому результату. Выполнить​Функция ВПР предназначена для​ (сегодня поступивших). Чтобы​

​ значение, которое больше​ предполагает, что первый​(Интервальный_просмотр). Хотя, если​ и не будем​ существует в базе​ совпадения может иметь​

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

​ оставить поле пустым,​ вдаваться в нюансы​ данных вообще. Как​ серьезные последствия, предположим,​ эта формула должна​ИНДЕКС​ что просматриваемый диапазон​ формул массива содержит​2. Безопасное добавление или​ Японии, используя следующую​

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

​таблице​ это не будет​

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

exceltable.com

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

​Выделяем столбец со вставленными​ расположено на строку​отсортирован в алфавитном​ ошибкой, так как​ Ведь это статья,​ВПР​ детали с идентификатором​ Вы можете видеть​=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))​ среднему. Если же​ медленнее работает Excel.​Формулы с функцией​=INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))​столбце, то есть​А из какого столбца​ CTRL+SHIFT+ENTER.​ Например, если таблица​ ценами.​ выше его. Как​

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

​ порядке или по​TRUE​ посвященная функции​переключилась в режим​ 2345768, но вы​ это по фигурным​=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))​

​ Вы уверены, что​С другой стороны, формула​ВПР​=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ("Japan";$B$2:$B$10;0))​ из ячейки​ брать возвращаемое значение​Стоит отметить, что главным​ состоит из двух​

1. ​Правая кнопка мыши –​ следствие, если искомое​
2. ​ возрастанию. Это способ​

​— это его​ВПР​ приближенной работы, и​ перепутали две цифры​ скобкам, в которые​

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

​.​ третьем аргументе.​

​ является отсутствие возможности​ и «Цена». Рядом​Не снимая выделения, правая​

​ по умолчанию, если​Мы заполнили все параметры.​ руководство по Excel.​

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

​ формулу, не забудьте​ они возвращают, формула​для поиска точного​ИНДЕКС​ добавить столбец в​

1. ​Функция​
2. ​ на практике Вы​
3. ​ аргументе функции указывает​

​ исходных значений в​ которая будет искать​ «Специальная вставка».​ ошибку ​Ниже в статье рассмотрены​ОК​ было, формула усложняется!​ хотим найти. В​=ВПР​ нажать​ станет легкой и​ совпадения.​просто совершает поиск​ таблицу поиска. Для​MATCH​ далеко не всегда​ на то, то​ запросе.​ в первой таблице​Поставить галочку напротив «Значения».​#Н/Д.​ популярные задачи, которые​, и Excel создаёт​ А что, если​ определённых обстоятельствах именно​(2345678;A1:E7;5)​Ctrl+Shift+Enter​ понятной:​Если указываете​ и возвращает результат,​ функции​(ПОИСКПОЗ) ищет значение​ знаете, какие строка​ совпадение должно быть​Скачать пример функции ВПР​ по наименованию товара​ ОК.​Найденное значение может быть​ можно решить с​ для нас формулу​

​ мы введем еще​ так и нужно.​. Формула возвращает цену​

​.​=INDEX($A$1:$E$11,4,5))​1​ выполняя аналогичную работу​

​ВПР​ «Japan» в столбце​ и столбец Вам​ абсолютно точным.​ с двумя таблицами​

exceltable.com

​ и получать значение​

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

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

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

Базовая информация об ИНДЕКС и ПОИСКПОЗ

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

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

ИНДЕКС – синтаксис и применение функции

Функция INDEX (ИНДЕКС) в Excel возвращает значение из массива по заданным номерам строки и столбца. Функция имеет вот такой синтаксис:

Каждый аргумент имеет очень простое объяснение:

• array (массив) – это диапазон ячеек, из которого необходимо извлечь значение.
• row_num (номер_строки) – это номер строки в массиве, из которой нужно извлечь значение. Если не указан, то обязательно требуется аргумент column_num (номер_столбца).
• column_num (номер_столбца) – это номер столбца в массиве, из которого нужно извлечь значение. Если не указан, то обязательно требуется аргумент row_num (номер_строки)

Если указаны оба аргумента, то функция ИНДЕКС возвращает значение из ячейки, находящейся на пересечении указанных строки и столбца.

Вот простейший пример функции INDEX (ИНДЕКС):

INDEX(A1:C10,2,3)
=ИНДЕКС(A1:C10;2;3)

Формула выполняет поиск в диапазоне A1:C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, то есть из ячейки C2 .

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

ПОИСКПОЗ – синтаксис и применение функции

Функция MATCH (ПОИСКПОЗ) в Excel ищет указанное значение в диапазоне ячеек и возвращает относительную позицию этого значения в диапазоне.

Например, если в диапазоне B1:B3 содержатся значения New-York, Paris, London, тогда следующая формула возвратит цифру 3 , поскольку “London” – это третий элемент в списке.

MATCH("London",B1:B3,0)
=ПОИСКПОЗ("London";B1:B3;0)

Функция MATCH (ПОИСКПОЗ) имеет вот такой синтаксис:

MATCH(lookup_value,lookup_array,)
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

• lookup_value (искомое_значение) – это число или текст, который Вы ищите. Аргумент может быть значением, в том числе логическим, или ссылкой на ячейку.
• lookup_array (просматриваемый_массив) – диапазон ячеек, в котором происходит поиск.
• match_type (тип_сопоставления) – этот аргумент сообщает функции ПОИСКПОЗ , хотите ли Вы найти точное или приблизительное совпадение:
  • 1 или не указан – находит максимальное значение, меньшее или равное искомому. Просматриваемый массив должен быть упорядочен по возрастанию, то есть от меньшего к большему.
  • 0 – находит первое значение, равное искомому. Для комбинации ИНДЕКС /ПОИСКПОЗ всегда нужно точное совпадение, поэтому третий аргумент функции ПОИСКПОЗ должен быть равен 0 .
  • -1 – находит наименьшее значение, большее или равное искомому значению. Просматриваемый массив должен быть упорядочен по убыванию, то есть от большего к меньшему.

На первый взгляд, польза от функции ПОИСКПОЗ вызывает сомнение. Кому нужно знать положение элемента в диапазоне? Мы хотим знать значение этого элемента!

Позвольте напомнить, что относительное положение искомого значения (т.е. номер строки и/или столбца) – это как раз то, что мы должны указать для аргументов row_num (номер_строки) и/или column_num (номер_столбца) функции INDEX (ИНДЕКС). Как Вы помните, функция ИНДЕКС может возвратить значение, находящееся на пересечении заданных строки и столбца, но она не может определить, какие именно строка и столбец нас интересуют.

Как использовать ИНДЕКС и ПОИСКПОЗ в Excel

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

Ещё не совсем понятно? Представьте функции ИНДЕКС и ПОИСКПОЗ в таком виде:

INDEX(,(MATCH (искомое значение ,столбец в котором ищем ,0))
=ИНДЕКС(столбец из которого извлекаем ;(ПОИСКПОЗ(искомое значение ;столбец в котором ищем ;0))

Думаю, ещё проще будет понять на примере. Предположим, у Вас есть вот такой список столиц государств:

Давайте найдём население одной из столиц, например, Японии, используя следующую формулу:

INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))
=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ("Japan";$B$2:$B$10;0))

Теперь давайте разберем, что делает каждый элемент этой формулы:

• Функция MATCH (ПОИСКПОЗ) ищет значение “Japan” в столбце B , а конкретно – в ячейках B2:B10 , и возвращает число 3 , поскольку “Japan” в списке на третьем месте.
• Функция INDEX (ИНДЕКС) использует 3 для аргумента row_num (номер_строки), который указывает из какой строки нужно возвратить значение. Т.е. получается простая формула:

  INDEX($D$2:$D$10,3)
  =ИНДЕКС($D$2:$D$10;3)

  Формула говорит примерно следующее: ищи в ячейках от D2 до D10 и извлеки значение из третьей строки, то есть из ячейки D4 , так как счёт начинается со второй строки.

Вот такой результат получится в Excel:

Важно! Количество строк и столбцов в массиве, который использует функция INDEX (ИНДЕКС), должно соответствовать значениям аргументов row_num (номер_строки) и column_num (номер_столбца) функции MATCH (ПОИСКПОЗ). Иначе результат формулы будет ошибочным.

Стоп, стоп… почему мы не можем просто использовать функцию VLOOKUP (ВПР)? Есть ли смысл тратить время, пытаясь разобраться в лабиринтах ПОИСКПОЗ и ИНДЕКС ?

VLOOKUP("Japan",$B$2:$D$2,3)
=ВПР("Japan";$B$2:$D$2;3)

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

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

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

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

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

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

Например, если у Вас есть таблица A1:C10 , и требуется извлечь данные из столбца B , то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР , вот так:

VLOOKUP("lookup value",A1:C10,2)
=ВПР("lookup value";A1:C10;2)

Если позднее Вы вставите новый столбец между столбцами A и B , то значение аргумента придется изменить с 2 на 3 , иначе формула возвратит результат из только что вставленного столбца.

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

3. Нет ограничения на размер искомого значения. Используя ВПР , помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС /ПОИСКПОЗ .

Предположим, Вы используете вот такую формулу с ВПР , которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2 :

VLOOKUP(A2,B5:D10,3,FALSE)
=ВПР(A2;B5:D10;3;ЛОЖЬ)

Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС /ПОИСКПОЗ :

INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))
=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))

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

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

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

ИНДЕКС и ПОИСКПОЗ – примеры формул

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

Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС

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

Функции ПОИСКПОЗ и ИНДЕКС в Excel гораздо более гибкие, и им все-равно, где находится столбец со значением, которое нужно извлечь. Для примера, снова вернёмся к таблице со столицами государств и населением. На этот раз запишем формулу ПОИСКПОЗ /ИНДЕКС , которая покажет, какое место по населению занимает столица России (Москва).

Как видно на рисунке ниже, формула отлично справляется с этой задачей:

INDEX($A$2:$A$10,MATCH("Russia",$B$2:$B$10,0))

Теперь у Вас не должно возникать проблем с пониманием, как работает эта формула:

• Во-первых, задействуем функцию MATCH (ПОИСКПОЗ), которая находит положение “Russia” в списке:

  MATCH("Russia",$B$2:$B$10,0))
  =ПОИСКПОЗ("Russia";$B$2:$B$10;0))

• Далее, задаём диапазон для функции INDEX (ИНДЕКС), из которого нужно извлечь значение. В нашем случае это A2:A10 .
• Затем соединяем обе части и получаем формулу:

  INDEX($A$2:$A$10;MATCH("Russia";$B$2:$B$10;0))
  =ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))

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

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

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

1. MAX (МАКС). Формула находит максимум в столбце D C той же строки:

INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))

Результат: Beijing

2. MIN (МИН). Формула находит минимум в столбце D и возвращает значение из столбца C той же строки:

INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))

Результат: Lima

3. AVERAGE (СРЗНАЧ). Формула вычисляет среднее в диапазоне D2:D10 , затем находит ближайшее к нему и возвращает значение из столбца C той же строки:

INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))

Результат: Moscow

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

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

• Если указываете 1 , значения в столбце поиска должны быть упорядочены по возрастанию, а формула вернёт максимальное значение, меньшее или равное среднему.
• Если указываете -1 , значения в столбце поиска должны быть упорядочены по убыванию, а возвращено будет минимальное значение, большее или равное среднему.

В нашем примере значения в столбце D упорядочены по возрастанию, поэтому мы используем тип сопоставления 1 . Формула ИНДЕКС /ПОИСКПО З возвращает “Moscow”, поскольку величина населения города Москва – ближайшее меньшее к среднему значению (12 269 006).

Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу

Эта формула эквивалентна двумерному поиску ВПР и позволяет найти значение на пересечении определённой строки и столбца.

В этом примере формула ИНДЕКС /ПОИСКПОЗ будет очень похожа на формулы, которые мы уже обсуждали в этом уроке, с одним лишь отличием. Угадайте каким?

Как Вы помните, синтаксис функции INDEX (ИНДЕКС) позволяет использовать три аргумента:

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

И я поздравляю тех из Вас, кто догадался!

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

INDEX(Ваша таблица ,(MATCH(,столбец, в котором искать ,0)),(MATCH(,строка в которой искать ,0))
=ИНДЕКС(Ваша таблица ,(MATCH(значение для вертикального поиска ,столбец, в котором искать ,0)),(MATCH(значение для горизонтального поиска ,строка в которой искать ,0))

Обратите внимание, что для двумерного поиска нужно указать всю таблицу в аргументе array (массив) функции INDEX (ИНДЕКС).

А теперь давайте испытаем этот шаблон на практике. Ниже Вы видите список самых населённых стран мира. Предположим, наша задача узнать население США в 2015 году.

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

Итак, начнём с двух функций ПОИСКПОЗ , которые будут возвращать номера строки и столбца для функции ИНДЕКС :

• ПОИСКПОЗ для столбца – мы ищем в столбце B , а точнее в диапазоне B2:B11 , значение, которое указано в ячейке H2 (USA). Функция будет выглядеть так:

  MATCH($H$2,$B$1:$B$11,0)
  =ПОИСКПОЗ($H$2;$B$1:$B$11;0)

  4 , поскольку “USA” – это 4-ый элемент списка в столбце B (включая заголовок).

• ПОИСКПОЗ для строки – мы ищем значение ячейки H3 (2015) в строке 1 , то есть в ячейках A1:E1 :

  MATCH($H$3,$A$1:$E$1,0)
  =ПОИСКПОЗ($H$3;$A$1:$E$1;0)

  Результатом этой формулы будет 5 , поскольку “2015” находится в 5-ом столбце.

Теперь вставляем эти формулы в функцию ИНДЕКС и вуаля:

INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))
=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))

Если заменить функции ПОИСКПОЗ на значения, которые они возвращают, формула станет легкой и понятной:

INDEX($A$1:$E$11,4,5))
=ИНДЕКС($A$1:$E$11;4;5))

Эта формула возвращает значение на пересечении 4-ой строки и 5-го столбца в диапазоне A1:E11 , то есть значение ячейки E4 . Просто? Да!

Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

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

Предположим, у нас есть список заказов, и мы хотим найти сумму по двум критериям – имя покупателя (Customer) и продукт (Product). Дело усложняется тем, что один покупатель может купить сразу несколько разных продуктов, и имена покупателей в таблице на листе Lookup table расположены в произвольном порядке.

Вот такая формула ИНДЕКС /ПОИСКПОЗ решает задачу:

{=INDEX("Lookup table"!$A$2:$C$13,MATCH(1,(A2="Lookup table"!$A$2:$A$13)*
(B2="Lookup table"!$B$2:$B$13),0),3)}
{=ИНДЕКС("Lookup table"!$A$2:$C$13;ПОИСКПОЗ(1;(A2="Lookup table"!$A$2:$A$13)*
(B2="Lookup table"!$B$2:$B$13);0);3)}

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

MATCH(1,(A2="Lookup table"!$A$2:$A$13),0)*(B2="Lookup table"!$B$2:$B$13)
ПОИСКПОЗ(1;(A2="Lookup table"!$A$2:$A$13);0)*(B2="Lookup table"!$B$2:$B$13)

В формуле, показанной выше, искомое значение – это 1 , а массив поиска – это результат умножения. Хорошо, что же мы должны перемножить и почему? Давайте разберем все по порядку:

• Берем первое значение в столбце A (Customer) на листе Main table и сравниваем его со всеми именами покупателей в таблице на листе Lookup table (A2:A13).
• Если совпадение найдено, уравнение возвращает 1 (ИСТИНА), а если нет – 0 (ЛОЖЬ).
• Далее, мы делаем то же самое для значений столбца B (Product).
• Затем перемножаем полученные результаты (1 и 0). Только если совпадения найдены в обоих столбцах (т.е. оба критерия истинны), Вы получите 1 . Если оба критерия ложны, или выполняется только один из них – Вы получите 0 .

Теперь понимаете, почему мы задали 1 , как искомое значение? Правильно, чтобы функция ПОИСКПОЗ возвращала позицию только, когда оба критерия выполняются.

Обратите внимание: В этом случае необходимо использовать третий не обязательный аргумент функции ИНДЕКС . Он необходим, т.к. в первом аргументе мы задаем всю таблицу и должны указать функции, из какого столбца нужно извлечь значение. В нашем случае это столбец C (Sum), и поэтому мы ввели 3 .

И, наконец, т.к. нам нужно проверить каждую ячейку в массиве, эта формула должна быть формулой массива. Вы можете видеть это по фигурным скобкам, в которые она заключена. Поэтому, когда закончите вводить формулу, не забудьте нажать Ctrl+Shift+Enter .

Если всё сделано верно, Вы получите результат как на рисунке ниже:

ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

Как Вы, вероятно, уже заметили (и не раз), если вводить некорректное значение, например, которого нет в просматриваемом массиве, формула ИНДЕКС /ПОИСКПОЗ сообщает об ошибке #N/A (#Н/Д) или #VALUE! (#ЗНАЧ!). Если Вы хотите заменить такое сообщение на что-то более понятное, то можете вставить формулу с ИНДЕКС и ПОИСКПОЗ в функцию ЕСЛИОШИБКА .

Синтаксис функции ЕСЛИОШИБКА очень прост:

IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)

Где аргумент value (значение) – это значение, проверяемое на предмет наличия ошибки (в нашем случае – результат формулы ИНДЕКС /ПОИСКПОЗ ); а аргумент value_if_error (значение_если_ошибка) – это значение, которое нужно возвратить, если формула выдаст ошибку.

Например, Вы можете вставить в функцию ЕСЛИОШИБКА вот таким образом:

IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),
"Совпадений не найдено. Попробуйте еще раз!") =ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));
"Совпадений не найдено. Попробуйте еще раз!")

И теперь, если кто-нибудь введет ошибочное значение, формула выдаст вот такой результат:

Если Вы предпочитаете в случае ошибки оставить ячейку пустой, то можете использовать кавычки (“”), как значение второго аргумента функции ЕСЛИОШИБКА . Вот так:

IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),"")
ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);"")

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

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

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

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

VLOOKUP (Vertical Lookup) – это еще одно название функции, которое можно встретить в англоязычной версии табличного процессора.

Сама аббревиатура ВПР означает «вертикальный просмотр».

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

Также, в Excel есть противоположная функция под названием HLOOKUP или ГПР – горизонтальный просмотр.

Единственное отличие работы опций состоит в том, что ГПР производит поиск в таблице по перебору столбцов, а не строчек.

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

Как выглядит синтаксис ВПР?

Синтаксис функции в Excel – это набор параметров, с помощью которых её можно вызвать и задать. Запись аналогична с методом записи математических функций.

• Используйте уже созданный документ, либо откройте новый пустой лист;
• Кликните на клавишу «Формулы» , как показано на рисунке ниже;
• В строке поиска напечатайте «ВПР» или «VLOOKUP» в зависимости от языка программы;
• Настройте категорию «Полный перечень» ;
• Кликните на «Найти» .

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

За скобками указывается название функции, а внутри скобок – её параметры. Внутри формулы каждый отдельный параметр прописывается в угловых <> скобах.

Общий вид описания для ВПР выглядит так:

Рис.3 – перечень параметров

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

• <ЧТО> - первый элемент. Вместо него вам нужно прописать именно то значение, которое вы хотите найти в таблице. Также, можно вписывать адрес ячейки в таблице;
• <НОМЕР_СТОЛБЦА> - тут нужно напечатать номер столбика, в рамках которого будет осуществляться перебор данных.
• <ГДЕ> - здесь юзер определяет количество ячеек, задавая их размерность в виде двумерного массива данных. Первый столбик – это элемент «ЧТО»;
• <ОТСОРТИРОВАНО> - этот элемент функции ВПР отвечает за сортировку первого столбца по возрастанию (первый столбик для «ГДЕ»). В результате успешной сортировки, значение становится истинным (единица). Если возникают какие-либо неточности или ошибки во время ввода параметров – появляется ложное значение сортировки (ноль). Стоит заметить, что во время задания ВПР <ОТСОРТИРОВАНО> можно пропустить, и тогда его значение по умолчанию принимается как истина.

Как работает ВПР. Полезный пример

Чтобы лучше понять принцип работы VLOOKUP, перейдём к рассмотрению конкретных примеров. Возьмём простейшую таблицу с двумя колонками. Пусть она обозначает код и наименование товара.

После заполнения таблицы кликнем на пустую ячейку и и результат ВПР. Кликните на вкладку «Формулы» и выберите VLOOKUP.

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

Рис.4 – пример поиска в простой таблице

На рисунке выше в цветных ячейках указывается значение для товара. Если вы не ввели значение для сортировки, то функция автоматически воспринимает это как единицу.

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

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

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

Поиск будет остановлен автоматически, как только найдется совпадение параметра «ЧТО» и имени товара.

Если в таблице нет введенного вами идентификатора для имени продукции, в результате выполнения поиска VLOOKUP будет получено значение «Н/Д», что означает отсутствие элемента для заданного номера.

Рис.5 – второй пример для ВПР

Когда использовать ВПР?

Выше описаны два варианта применения VLOOKUP.

Первая вариация VLOOKUP подойдет для следующих случаев:

• Когда необходимо разделить значения объекта табличного процессора по его диапазонам;
• Для тех таблиц, в которых параметр ГДЕ может содержать несколько идентичных значений. В таком случае, формула вернет только то, которое находится в последней строчке относительно массива;
• Когда нужно искать значения, которые больше того, которое может содержаться в первом столбике. Так вы найдете последнюю строку таблицы практическим мгновенно.

Первый вариант правописания VLOOKUP не может найти элемент, если не было найдено значения меньше искомого или равное ему. В ячейке для результата вернется только «Н/Д».

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

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

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

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

Рис.6 – пример поиска текстового значения

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

Рис.7 – ВПР при удалении пробелов

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

Поиск в Excel по нескольким критериям

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

Пример 1: Поиск по 2-м разным критериям

Предположим, у нас есть список заказов и мы хотим найти Количество товара (Qty.), основываясь на двух критериях – Имя клиента (Customer) и Название продукта (Product). Дело усложняется тем, что каждый из покупателей заказывал несколько видов товаров, как это видно из таблицы ниже:

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

VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)

– эта формула вернет результат 15 , соответствующий товару Apples , так как это первое совпадающее значение.

Есть простой обходной путь – создать дополнительный столбец, в котором объединить все нужные критерии. В нашем примере это столбцы Имя клиента (Customer) и Название продукта (Product). Не забывайте, что объединенный столбец должен быть всегда крайним левым в диапазоне поиска, поскольку именно левый столбец функция ВПР просматривает при поиске значения.

Итак, Вы добавляете вспомогательный столбец в таблицу и копируете по всем его ячейкам формулу вида: =B2&C2 . Если хочется, чтобы строка была более читаемой, можно разделить объединенные значения пробелом: =B2&” “&C2 . После этого можно использовать следующую формулу:

VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)

VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

Где ячейка B1 содержит объединенное значение аргумента lookup_value (искомое_значение), а 4 – аргумент col_index_num (номер_столбца), т.е. номер столбца, содержащего данные, которые необходимо извлечь.

Пример 2: ВПР по двум критериям с просматриваемой таблицей на другом листе

Если Вам необходимо обновить основную таблицу (Main table), добавив данные из второй таблицы (Lookup table), которая находится на другом листе или в другой рабочей книге Excel, то Вы можете собрать искомое значение непосредственно в формуле, которую вставляете в основную таблицу.

Как и в предыдущем примере, Вам понадобится в таблице поиска (Lookup table) вспомогательный столбец с объединенными значениями. Этот столбец должен быть крайним левым в заданном для поиска диапазоне.

Итак, формула с ВПР может быть такой:

VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)

Здесь в столбцах B и C содержатся имена клиентов и названия продуктов соответственно, а ссылка Orders!$A&$2:$D$2 определяет таблицу для поиска на другом листе.

Чтобы сделать формулу более читаемой, Вы можете задать имя для просматриваемого диапазона, и тогда формула станет выглядеть гораздо проще:

VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)

Чтобы формула работала, значения в крайнем левом столбце просматриваемой таблицы должны быть объединены точно так же, как и в критерии поиска. На рисунке выше мы объединили значения и поставили между ними пробел, точно так же необходимо сделать в первом аргументе функции (B2&” “&C2).

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

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

Извлекаем 2-е, 3-е и т.д. значения, используя ВПР

Вы уже знаете, что ВПР может возвратить только одно совпадающее значение, точнее – первое найденное. Но как быть, если в просматриваемом массиве это значение повторяется несколько раз, и Вы хотите извлечь 2-е или 3-е из них? А что если все значения? Задачка кажется замысловатой, но решение существует!

Предположим, в одном столбце таблицы записаны имена клиентов (Customer Name), а в другом – товары (Product), которые они купили. Попробуем найти 2-й, 3-й и 4-й товары, купленные заданным клиентом.

Простейший способ – добавить вспомогательный столбец перед столбцом Customer Name и заполнить его именами клиентов с номером повторения каждого имени, например, John Doe1 , John Doe2 и т.д. Фокус с нумерацией сделаем при помощи функции COUNTIF (СЧЁТЕСЛИ), учитывая, что имена клиентов находятся в столбце B:

B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)

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

• Находим 2-й Dan Brown :

  VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)
  =ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)

• Находим 3-й товар, заказанный покупателем Dan Brown :

  VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)
  =ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)

Если Вы ищите только 2-е повторение, то можете сделать это без вспомогательного столбца, создав более сложную формулу:

IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4,0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4;0)+2)&":$C16");2;ИСТИНА);"")

В этой формуле:

• $F$2 – ячейка, содержащая имя покупателя (она неизменна, обратите внимание – ссылка абсолютная);
• $B$ – столбец Customer Name ;
• Table4 – Ваша таблица (на этом месте также может быть обычный диапазон);
• $C16 – конечная ячейка Вашей таблицы или диапазона.

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

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

Извлекаем все повторения искомого значения

Как упоминалось выше, ВПР не может извлечь все повторяющиеся значения из просматриваемого диапазона. Чтобы сделать это, Вам потребуется чуть более сложная формула, составленная из нескольких функций Excel, таких как INDEX (ИНДЕКС), SMALL (НАИМЕНЬШИЙ) и ROW (СТРОКА)

Например, формула, представленная ниже, находит все повторения значения из ячейки F2 в диапазоне B2:B16 и возвращает результат из тех же строк в столбце C.

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}

Введите эту формулу массива в несколько смежных ячеек, например, в ячейки F4:F8 , как показано на рисунке ниже. Количество ячеек должно быть равным или большим, чем максимально возможное число повторений искомого значения. Не забудьте нажать Ctrl+Shift+Enter , чтобы правильно ввести формулу массива.

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

Часть 1:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")

$F$2=B2:B16 – сравниваем значение в ячейке F2 с каждым из значений диапазона B2:B16. Если найдено совпадение, то выражение СТРОКА(C2:C16)-1 возвращает номер соответствующей строки (значение -1 позволяет не включать строку заголовков). Если совпадений нет, функция IF (ЕСЛИ) возвращает пустую строку.

Результатом функции IF (ЕСЛИ) окажется вот такой горизонтальный массив: {1,"",3,"",5,"","","","","","",12,"","",""}

Часть 2:

ROW()-3
СТРОКА()-3

Здесь функция ROW (СТРОКА) действует как дополнительный счётчик. Так как формула скопирована в ячейки F4:F9, мы вычитаем число 3 из результата функции, чтобы получить значение 1 в ячейке F4 (строка 4, вычитаем 3), чтобы получить 2 в ячейке F5 (строка 5, вычитаем 3) и так далее.

Часть 3:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

Функция SMALL (НАИМЕНЬШИЙ) возвращает n-ое наименьшее значение в массиве данных. В нашем случае, какую по счёту позицию (от наименьшего) возвращать – определено функцией ROW (СТРОКА) (смотри Часть 2). Так, для ячейки F4 функция НАИМЕНЬШИЙ({массив};1) возвращает 1-й (наименьший) элемент массива, то есть 1 . Для ячейки F5 возвращает 2-й наименьший элемент массива, то есть 3 , и так далее.

Часть 4:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

Функция INDEX (ИНДЕКС) просто возвращает значение определённой ячейки в массиве C2:C16 . Для ячейки F4 функция ИНДЕКС($C$2:$C$16;1) возвратит Apples , для F5 функция ИНДЕКС($C$2:$C$16;3) возвратит Sweets и так далее.

Часть 5:

IFERROR()
ЕСЛИОШИБКА()

В завершение, мы помещаем формулу внутрь функции IFERROR (ЕСЛИОШИБКА), поскольку вряд ли Вас обрадует сообщение об ошибке #N/A (#Н/Д) в случае, если количество ячеек, в которые скопирована формула, будет меньше, чем количество повторяющихся значений в просматриваемом диапазоне.

Двумерный поиск по известным строке и столбцу

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

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

Существует несколько способов выполнить двумерный поиск. Познакомьтесь с возможными вариантами и выберите наиболее подходящий.

Функции ВПР и ПОИСКПОЗ

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

VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)

Формула выше – это обычная функция ВПР , которая ищет точное совпадение значения “Lemons” в ячейках от A2 до A9. Но так как Вы не знаете, в каком именно столбце находятся продажи за март, то не сможете задать номер столбца для третьего аргумента функции ВПР . Вместо этого используется функция ПОИСКПОЗ , чтобы определить этот столбец.

MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)

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

• Ищем символы “Mar” – аргумент lookup_value (искомое_значение);
• Ищем в ячейках от A1 до I1 – аргумент lookup_array (просматриваемый_массив);
• Возвращаем точное совпадение – аргумент match_type (тип_сопоставления).

Использовав 0 в третьем аргументе, Вы говорите функции ПОИСКПОЗ искать первое значение, в точности совпадающее с искомым значением. Это равносильно значению FALSE (ЛОЖЬ) для четвёртого аргумента ВПР .

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

Функция СУММПРОИЗВ

Функция СУММПРОИЗВ (SUMPRODUCT) возвращает сумму произведений выбранных массивов:

SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

Функции ИНДЕКС и ПОИСКПОЗ

В следующей статье я буду объяснять эти функции во всех деталях, так что сейчас можете просто скопировать эту формулу:

INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))

Именованные диапазоны и оператор пересечения

Если Вы не в восторге от всех этих сложных формул Excel, Вам может понравиться вот такой наглядный и запоминающийся способ:

При вводе имени, Microsoft Excel будет показывать подсказку со списком подходящих имен, так же, как при вводе формулы.

1. Нажмите Enter и проверьте результат

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

Используем несколько ВПР в одной формуле

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

Давайте разберем следующий пример. У нас есть основная таблица (Main table) со столбцом SKU (new) , куда необходимо добавить столбец с соответствующими ценами из другой таблицы. Кроме этого, у нас есть 2 таблицы поиска. Первая (Lookup table 1) содержит обновленные номера SKU (new) и названия товаров, а вторая (Lookup table 2) – названия товаров и старые номера SKU (old) .

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

1. Запишите функцию ВПР , которая находит имя товара в таблице Lookup table 1 , используя SKU , как искомое значение:

   VLOOKUP(A2,New_SKU,2,FALSE)
   =ВПР(A2;New_SKU;2;ЛОЖЬ)

   Здесь New_SKU – именованный диапазон $A:$B в таблице Lookup table 1 , а 2 – это столбец B, который содержит названия товаров (смотрите на рисунке выше)

2. Запишите формулу для вставки цен из таблицы Lookup table 2 на основе известных названий товаров. Для этого вставьте созданную ранее формулу в качестве искомого значения для новой функции ВПР :

   VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)
   =ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)

   Здесь Price – именованный диапазон $A:$C в таблице Lookup table 2 , а 3 – это столбец C, содержащий цены.

На рисунке ниже виден результат, возвращаемый созданной нами формулой:

Динамическая подстановка данных из разных таблиц при помощи ВПР и ДВССЫЛ

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

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

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

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

VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)

• $D$2 – это ячейка, содержащая название товара. Обратите внимание, здесь мы используем абсолютные ссылки, чтобы избежать изменения искомого значения при копировании формулы в другие ячейки.
• $D3 – это ячейка с названием региона. Используем абсолютную ссылку для столбца и относительную ссылку для строки, поскольку планируем копировать формулу в другие ячейки того же столбца.
• FL_Sal es и CA_Sales – названия таблиц (или именованных диапазонов), в которых содержаться соответствующие отчеты о продажах. Вы, конечно же, можете использовать обычные названия листов и ссылки на диапазоны ячеек, например ‘FL Sheet’!$A$3:$B$10 , но именованные диапазоны гораздо удобнее.

Однако, когда таких таблиц много, функция ЕСЛИ – это не лучшее решение. Вместо нее можно использовать функцию ДВССЫЛ (INDIRECT), чтобы возвратить нужный диапазон поиска.

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

VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)

• $D$2 – это ячейка с названием товара, она неизменна благодаря абсолютной ссылке.
• $D3 – это ячейка, содержащая первую часть названия региона. В нашем примере это FL .
• _Sales – общая часть названия всех именованных диапазонов или таблиц. Соединенная со значением в ячейке D3, она образует полное имя требуемого диапазона. Ниже приведены некоторые подробности для тех, кто не имеет опыта работы с функцией ДВССЫЛ .

Как работают ДВССЫЛ и ВПР

Во-первых, позвольте напомнить синтаксис функции ДВССЫЛ (INDIRECT):

• A1 , если аргумент равен TRUE (ИСТИНА) или не указан;
• R1C1 , если F ALSE (ЛОЖЬ).

Итак, давайте вернемся к нашим отчетам по продажам. Если Вы помните, то каждый отчёт – это отдельная таблица, расположенная на отдельном листе. Чтобы формула работала верно, Вы должны дать названия своим таблицам (или диапазонам), причем все названия должны иметь общую часть. Например, так: CA_Sales , FL_Sales , TX_Sales и так далее. Как видите, во всех именах присутствует “_Sales”.

Функция ДВССЫЛ соединяет значение в столбце D и текстовую строку “_Sales”, тем самым сообщая ВПР в какой таблице искать. Если в ячейке D3 находится значение “FL”, формула выполнит поиск в таблице FL_Sales , если “CA” – в таблице CA_Sales и так далее.

Результат работы функций ВПР и ДВССЫЛ будет следующий:

Если данные расположены в разных книгах Excel, то необходимо добавить имя книги перед именованным диапазоном, например:

VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)

Если функция ДВССЫЛ ссылается на другую книгу, то эта книга должна быть открытой. Если же она закрыта, функция сообщит об ошибке #REF! (#ССЫЛ!).

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

1. Таблицы должны располагаться в одной книге Excel.
2. Искать можно только среди статических данных (не формул).
3. Условие поиска должно располагаться в первом столбце используемых данных.

Формула ВПР в Excel

Синтаксис ВПР в русифицированном Excel имеет вид:

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

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

Критерий поиска

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

Диапазон данных

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

Номер столбца для итогового значения

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

Условие для поиска

Логическое значение (истина/1 или ложь/0), которое указывает приблизительное совпадение искать (1) или точное (0).

ВПР в Excel: примеры функции

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

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


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


Появится окно ВПР для таблицы Excel.


Чтобы указать первый аргумент (критерий), поставьте курсор в первую строку и щелкните по ячейке H3. Ее адрес появится в строке. Для выделения диапазона поставьте курсор во вторую строку и начните выделять мышью. Окно свернется до строки. Это делается для того, чтобы окно не мешало видеть Вам весь диапазон и не мешало выполнять действия.


Как только Вы закончите выделение и отпустите левую кнопку мыши, окно вернется в свое нормальное состояние, а во второй строке появится адрес диапазона. Он вычисляется от левой верхней ячейки до правой нижней. Их адреса разделены оператором «:» - берутся все адреса между первым и последним.


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


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


Результат обескураживает. «Н/Д» означает некорректные данные для функции. Мы не указали значение в ячейке H3, и функция ищет пустое значение.


Введем название месяца и значение изменится.


Только оно не соответствует действительности, ведь настоящее фактическое количество выпущенной продукции в январе равно 2000.
Это влияние аргумента «Условие поиска». Изменим его на 0. Для этого поставьте указатель на ячейку с формулой и снова нажмите Fx. В открывшемся окне введите «0» в последнюю строку.


Нажимайте «ОК». Как видим, результат изменился.


Чтобы проверить второе условие из начала нашей статьи (среди формул функция не ищет) изменим условия для функции. Увеличим диапазон и попробуем вывести значение из столбца с вычисляемыми значениями. Укажите значения как на скриншоте.


Нажмите «Ок». Как видите, результат поиска оказался 0, хотя в таблице стоит значение 85%.


ВПР в Excel «понимает» только фиксированные значения.

Сравнение данных двух таблиц Excel

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

На двух листах мы имеем одинаковые таблицы с разными данными.

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

В ячейку B2 введем функцию ВПР. В качестве первого аргумента укажем ячейку с месяцем на текущем листе, а диапазон выберем с листа «Цех1». Чтобы при копировании диапазон не смещался, нажмите F4 после выбора диапазона. Это сделает ссылку абсолютной.


Растяните формулу на весь столбец.

Аналогично введите формулу в следующий столбец, только диапазон выделяйте на листе «Цех2».


После копирования Вы получите сводный отчет с двух листов.

Подстановка данных из одной таблицы Excel в другую

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


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


В результате столбец второй таблицы будет скопирован в первую.


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

Отличного Вам дня!

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

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

Более подробно о функциях ВПР и ПРОСМОТР.

Функция ПОИСКПОЗ в Excel

Функция ПОИСКПОЗ возвращает относительное расположение ячейки в заданном диапазоне Excel, содержимое которой соответствует искомому значению. Т.е. данная функция возвращает не само содержимое, а его местоположение в массиве данных.

Например, на рисунке ниже формула вернет число 5, поскольку имя “Дарья” находится в пятой строке диапазона A1:A9.

В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.

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

• 0 – функция ПОИСКПОЗ ищет первое значение в точности равное заданному. Сортировка не требуется.
• 1 или вовсе опущено – функция ПОИСКПОЗ ищет самое большое значение, которое меньше или равно заданному. Требуется сортировка в порядке возрастания.
• -1 – функция ПОИСКПОЗ ищет самое маленькое значение, которое больше или равно заданному. Требуется сортировка в порядке убывания.

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

Функция ИНДЕКС в Excel

Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.

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

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

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

Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):

Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel

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

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

Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 – тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

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

В данной формуле функция ИНДЕКС принимает все 3 аргумента:

1. Первый аргумент – это диапазон B2:E13, в котором мы осуществляем поиск.
2. Вторым аргументом функции ИНДЕКС является номер строки. Номер мы получаем с помощью функции ПОИСКПОЗ(C15;A2:A13;0). Для наглядности вычислим, что же возвращает нам данная формула:
3. Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем с помощью функции ПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:

Если подставить в исходную громоздкую формулу вместо функций ПОИСКПОЗ уже вычисленные данные из ячеек D15 и D16, то формула преобразится в более компактный и понятный вид:

=ИНДЕКС(B2:E13;D15;D16)

Как видите, все достаточно просто!

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