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

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

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

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

Основы ИНДЕКСНОЕ СООТВЕТСТВИЕ

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

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

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

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

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

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

=ИНДЕКС(A1:S10,2,3)

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

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

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

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

Синтаксис searchpos() выглядит следующим образом:

=MATCH(значение для поиска, массив для поиска, тип соответствия)

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

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

=EXPOSE(Лондон,B1:B3,0)

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

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

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

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

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

=ИНДЕКС(C2:C10, ПОИСКПОЗ(Япония, A2:A10,0))

Объяснение:

• Функция ПОИСКПОЗ() ищет значение — «Япония» в массиве A2:A10 и возвращает число 3, поскольку Япония — третье значение в списке. 
• Эта цифра идет наномер строчки» в формуле INDEX() и сообщает функции, что нужно напечатать значение из этой строки.

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

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

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

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

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

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

 Какой смысл тратить время, пытаясь разобраться во всех этих сложностях INDEX MATCH?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В ячейку G1 записываем искомое значение, а затем используем следующую формулу для поиска в диапазоне C1:C10 и возвращаем соответствующее значение из A2:A10:

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

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

ИНДЕКС БОЛЬШЕ РАСКРЫТОГО  для поиска в столбцах и строках

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

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

=ИНДЕКС(массив, ПОИСКПОЗ(значение вертикального поиска, столбец поиска, 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+ВВОД.

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

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

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

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

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

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

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

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

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

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

СРЕДНЕЕ, МАКСИМАЛЬНОЕ и МИНИМАЛЬНОЕ в ИНДЕКС и СОВПАДЕНИЕ

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

ИНДЕКСНОЕ СООТВЕТСТВИЕ и МАКС.

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

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

ИНДЕКСНОЕ СОВПАДЕНИЕ и МИН.

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

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

ПОИСКОВЫЙ ИНДЕКС и ЗМЕЙ

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

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

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

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

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

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

ИНДЕКСНОЕ СООТВЕТСТВИЕ и ESND/IFERROR

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

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

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

Если вы хотите отловить все ошибки, то, кроме В XNUMX может быть использован IFERROR:

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

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

Мы надеемся, что наше руководство по использованию функции INDEX MATCH() оказалось для вас полезным.