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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  
  

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  
  

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

  
  

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

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

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

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

Результат: Beijing

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

Результат: Lima

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

Результат: Moscow

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  
  

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

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

  
  

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

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

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

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

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

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

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

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

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

В формуле, показанной выше, искомое значение – это 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! (#ЗНАЧ!). Если Вы хотите заменить такое сообщение на что-то более понятное, то можете вставить формулу с ИНДЕКС и ПОИСКПОЗ в функцию ЕСЛИОШИБКА.

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

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

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

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

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

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

Функции ИНДЕКС и ПОИСКПОЗ в 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

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