Чем ИНДЕКС и ПОИСКПОЗ лучше ВПР в Excel

Ранее мы уже объясняли новичкам, как использовать базовые функции ВПР (англ. VLOOKUP, аббревиатура расшифровывается как “функция вертикального просмотра”). А опытным пользователям показали несколько формул посложнее.

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

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

Основы ИНДЕКС ПОИСКПОЗ

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

Функция ИНДЕКС: синтаксис и применение

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

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

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

Пример простой формулы:

=ИНДЕКС(А1:С10,2,3)

Функция будет производить поиск в диапазоне от А1 до С10. Цифры показывают, из какой строки (2) и колонки (3) показать искомое значение. Результатом будет ячейка С2.

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

Функция ПОИСКПОЗ: синтаксис и использование

Функция ПОИСКПОЗ() ищет нужное значение и показывает его примерный номер в указанной области поиска.

Синтаксис Поискпоз() выглядит так:

=ПОИСКПОЗ(искомое значение, просматриваемый массив, тип сопоставления)

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

Например, в диапазоне В1:В3 прописаны Нью-Йорк, Париж, Лондон. Указанная ниже формула покажет номер 3, потому что Лондон является третьим по списку:

=ПОИСКПОЗ(Лондон,В1:В3,0)

Как работать с функцией ИНДЕКС ПОИСКПОЗ 

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

=ИНДЕКС(колонка из которой возвращается значение, ПОИСКПОЗ(значение для поиска, колонка в которой искать, 0))

Все еще сложно понять, как это работает? Может быть на примере получится объяснить лучше. Предположим, у Вас есть список мировых столиц и численность их населения:

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

=ИНДЕКС(С2:С10, ПОИСКПОЗ(Япония, А2:А10,0))

Объяснение:

• Функция ПОИСКПОЗ() ищет значение – «Япония» в массиве А2:А10 и выдает цифру 3, потому что Япония — это третье значение по списку. 
• Эта цифра идет в «номер строки» в формуле ИНДЕКС() и указывает функции на необходимость вывести значение из этой строки.

Таким образом, вышеуказанная формула становится стандартной формулой ИНДЕКС(С2:С10,3). Формула проводит поиск в ячейках от С2 до С10 и выдает данные из третьей ячейки этого диапазона, то есть С4, потому что отсчет начинается со второй строки.

Не хотите прописывать название города в формуле? Тогда напишите его в любой ячейке, скажем, F1, и используйте её как ссылку в формуле ПОИСКПОЗ(). И у вас получится динамическая формула поиска:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Важно! Количество строк в массиве ИНДЕКС() должно быть такое же, как и количество строк в рассматриваемом массиве в ПОИСКПОЗ(), иначе вы получите неправильный результат.

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

=ВПР(F1, A2:C10, 3, Ложь)

 Какой смысл тратить время в попытках разобраться во всех этих сложностях ИНДЕКС ПОИСКПОЗ?

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

ИНДЕКС ПОИСКПОЗ или ВПР

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

Ниже мы приведем ключевые преимущества ИНДЕКС() и ПОИСКПОЗ() над ВПР():

 

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

 

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

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

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

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

Примеры формул 

Мы уже выяснили полезность этих функций, так что можно перейти к самой интересной части: к применению знаний на практике.

Формула для поиска справа налево

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

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

В ячейке G1 прописываем значение, которое нужно найти, а потом используем указанную ниже формулу для поиска в диапазоне С1:С10 и возвращаем соответствующее значение из А2:А10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Подсказка. Если вы планируете использовать эту формулу для нескольких ячеек, убедитесь, что вы зафиксировали диапазоны с помощью абсолютной адресации (например, $А$2: $А$10 и $С$2:4С$10).

ИНДЕКС ПОИСКПОЗ ПОИСКПОЗ  для поиска в колонках и строках

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

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

=ИНДЕКС(массив, ПОИСКПОЗ(вертикальное искомое значение, искомая колонка, 0), ПОИСКПОЗ(горизонтальное искомое значение, искомая строка,0))

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

Целевая страна указана в ячейке G1 (вертикальное искомое значение), а целевой год — в ячейке G2 (горизонтальное искомое значение). Формула будет выглядеть так:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Как работает эта формула

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

• ПОИСКПОЗ(G1,A2:A11,0) – ищет значение (G1) в диапазоне A2:A11 и показывает номер этого значения, в нашем случае это 2;
• ПОИСКПОЗ(G2,B1:D1,0) – ищет значение (G2) в диапазоне B1:D1. В данном случае результат был 3.

Найденные номера строк и колонок отправляются в соответствующее значение в формуле ИНДЕКС():

=ИНДЕКС(B2:D11,2,3)

В результате, имеем значение, которое находится в ячейке на пересечении 2 строки и 3 колонки в диапазоне B2:D11. И формула показывает искомое значение, которое находится в ячейке D3.

Поиск по нескольким условиям с помощью ИНДЕКС и ПОИСКПОЗ

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

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

Вот общая формула поиска по нескольким условиям для ИНДЕКС() ПОИСКПОЗ():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Заметка: эту формулу нужно использовать вместе с сочетанием клавиш CTRL+SHIFT+ENTER.

Предположим, вам нужно найти искомое значение, основанное на 2 условиях: Покупатель и Продукт.

Для этого нужна следующая формула:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

В этой формуле С2:С10 — диапазон в котором будет проходить поиск, F1 – это условие, А2:А10 — это диапазон для сравнения условия, F2 – условие 2, В2:В10 – диапазон для сравнения условия 2.

Не забывайте в конце работы с формулой нажать сочетание CTRL+SHIFT+ENTER – Excel автоматически закроет формулу фигурными скобками, как указано в примере:

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

Как работают эти формулы

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

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

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

СРЗНАЧ, МАКС и МИН в ИНДЕКС и ПОИСКПОЗ

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

ИНДЕКС ПОИСКПОЗ и МАКС

Чтобы найти наибольшее значение в колонке D и показать его в колонке C, используем формулу: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

ИНДЕКС ПОИСКПОЗ и МИН

Чтобы обнаружить наименьшее значение в колонке D и вывести его в колонке С, используется такая формула:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

ИНДЕКС ПОИСКПОЗ и СРЗНАЧ

Чтобы найти среднее значение в колонке D и вывести это значение в С:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

В зависимости от того, как записаны ваши данные, в качестве третьего аргумента для ПОИСКПОЗ() используется либо 1, либо 0, либо -1:

• если колонки отсортированы по возрастанию – ставьте 1 (тогда формула рассчитает максимальное значение, которое меньше или равняется среднему значению);
• если сортировка по убыванию, тогда -1 (формула выведет минимальное значение, которое больше или равняется среднему);
• если же массив поиска содержит значение, которое в точности равняется среднему, тогда ставьте 0. 

 В нашем примере, численность населения отсортирована в убывающем порядке, поэтому мы ставим -1. И в результате получаем Токио, так как значение населения (13,189, 000) самое близкое к среднему значению (12,269,006).

ВПР() тоже может проводить такие расчеты, но только как формула массива: ВПР со СРЗНАЧ, МИН и МАКС.

ИНДЕКС ПОИСКПОЗ и ЕСНД/ЕСЛИОШИБКА

Вы, наверное, уже заметили, что, если формула не может найти нужное значение, она выдает ошибку #Н/Д. Вы можете заменить стандартное сообщение об ошибке чем-то более информативным. Например, задать в формуле аргумент ЕСНД:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

С этой формулой, если ввести данные, которых нет в таблице, форма вам выдаст указанное сообщение.

Если же хотите отловить все ошибки, тогда кроме ЕСНД можно использовать ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)), «Что-то пошло не так!»)

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

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