Комбинация функций ИНДЕКС и ПОИСКПОЗ в Excel

В Excel есть более 400 различных функций.

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

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

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

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

Итак, сначала давайте рассмотрим что же это за функции по отдельности.

ИНДЕКС

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

Как с адресом квартиры, например. Как бы много не было адресов, если вы сообщите полный — вас всегда можно будет найти.

Итак, чтобы найти вас нужен полный адрес, а чтобы найти что-то в Excel нужен адрес строки и столбика.

Так и работает эта функция.

Вот пример, строка 9, столбик 5 выдаст такой результат:

Функция довольно простая и её редко где можно применить, но…

Мы конечно же можем комбинировать её с другими функциями! В таком случае она будет очень даже кстати

Вот синтаскис:

=ИНДЕКС(диапазон;строка;столбик)  

• Диапазон — диапазон ячеек, среди которого будет выполнен поиск;
• Строка/столбик — аргумента может быть сразу 2, а может быть только 1, например, строка.
• Также можно указывать дополнительные диапазоны.

Чаще всего дополнительные диапазоны не используются, но всякое бывает.

ПОИСКПОЗ

А эта функция находит адрес ячейки по заданным критериям.

Как она это делает?

По совпадениям

К примеру, у вас есть следующая табличка и вы ищете ячейку, в которой есть слово «Mark», это будет выглядеть так:

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

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

Вот синтаксис

=ПОИСКПОЗ(критерий;диапазон;тип)  

• Критерий — критерий, с которым будет выполняться сравнение каждой из ячеек;
• Диапазон — диапазон ячеек, среди которого будет выполняться сравнение;
• Тип — всего три типа, рассмотрим далее.

Тип

Итак, что же такое тип?

Объяснение:

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

Грубо говоря:

• Функции ИНДЕКС «на входе» нужен адрес ячейки, результатом будет её значение;
• А функция ПОИСКПОЗ ищет адрес нужной ячейки по критериям.

Комбинация

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

Так как лучший способ что-то объяснить — показать примеры, я так и поступлю.

У меня заготовлены для вас 10 примеров использования и начнем мы, конечно же, с простого.

Первый пример использования — ПОИСК

Итак, выполним обычный поиск.

Допустим у нас есть такая табличка:

Предположим, что нам необходимо найти оценки Джима.

Ниже приведена формула, с помощью которой это можно легко сделать:

=ИНДЕКС($A$2:$B$11;ПОИСКПОЗ("Jim",$A$2:$A$11;0);2)

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

Это был простой пример и поставленную задачу легко выполнить с помощью использования обычной функции ПРОСМОТР.

А теперь давайте посмотрим в чем преимущества нашей комбинации, по сравнению с функцией ПРОСМОТР.

Допустим, теперь у нас такая же табличка, но в другом формате:

Итак, как же в данной ситуации использовать нашу комбинацию?

Вот формула:

=ИНДЕКС($B$1:$K$2;2;ПОИСКПОЗ(“Jim”;$B$1:$K$1;0))

Если будете использовать такой же метод, не забудьте поменять диапазон.

В таком случае использовать ПРОСМОТР будет невозможно.

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

Получаем данные из столбика слева

Эта ситуация встречается очень-очень часто.

Задача: вывести данные из столбика левее от столбика со значениями.

Как на картинке:

Нам нужно вывести продажи продавца «Майкл».

Функция ПРОСМОТР, изначально, не предназначена для таких задач. Но можно ли как-то все-таки с помощью неё выполнить эту задачу?

Можно, но формула будет огромной и непонятной.

Лучше всего, в такой ситуации, использовать нашу комбинацию.

Вот формула:

=ИНДЕКС($A$2:$C$11;ПОИСКПОЗ("Michael";C2:C11;0);2)  

Функция ПРОСМОТР выводит значения только из столбика справа от столбика со значениями

Получаем данные из нескольких столбиков одновременно

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

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

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

Давайте используем нашу комбинацию и выполним задачу!

Формула будет выглядеть так:

=ИНДЕКС($B$2:$D$11;ПОИСКПОЗ($F$3;$A$2:$A$11;0);ПОИСКПОЗ(G$2;$B$1:$D$1;0))  

Как это работает?

Функция ИНДЕКС задает диапазон.

Первая функция ПОИСКПОЗ ищет позицию значения имени текущего ученика.

Вторая функция ПОИСКПОЗ ищет позицию значения по разным предметам и отдает нам.

Все это передается в ИНДЕКС и она выводит нам имя и оценки ученика.

Самое удобное то, что функция остается динамическое. То есть если, например, оценки учеников поменяются, функция сделает перерасчет.

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

Получаем данные выполняя поиск по нескольким критериям одновременно

Допустим, у нас есть такая табличка:

Нужно вывести оценки только для ученика с именем «Mark» и фамилией «Long».

Имя и фамилия находятся в разных столбиках, что же делать?

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

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

Вот формула:

=ИНДЕКС($C$2:$C$11;ПОИСКПОЗ($E$3&"|"&$F$3;$A$2:A11&"|"&$B$2:$B$11;0))  

Как это работает?

Функция ПОИСКПОЗ как бы объединяет значение имени и фамилии, а после сравнивает их с указанным критерием.

И никакие дополнительные столбики для расчетов не нужны.

Эту же задачу можно выполнить с помощью функции ПРОСМОТР, но вам нужен будет дополнительный столбик для расчетов.

Получаем данные из нескольких столбиков и суммируем их

Итак, в прошлом примере мы выводили значение только одной ячейки, которая подходит по критериям.

Но что если нужно вычислить значения сразу нескольких столбиков, для ячеек, удовлетворяющих критериям, а после еще и суммировать их? Можно ли сделать все это в одной формуле?

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

Допустим, нам нужно вычислить общее количество баллов Джима по всем предметам.

Сначала мы получим значения всех оценок, а после просто сложим их.

Вот табличка:

Вот формула:

=СУММ(ИНДЕКС($B$2:$D$11;ПОИСКПОЗ($F$4;$A$2:$A$11;0);0))  

Как это работает?

Обратите внимание, номер столбика, в нашем случае, «0».

Суть в том, что, если мы сделали так, то функция вернет все значения, а это то что нам и нужно.

Так, создается массив значений из трех оценок.

Если мы оставим его как есть, то будет ошибка. Так как функция не предназначена для вывода массива как результат.

Но он, конечно же, никуда не девается. А просто остается в памяти Excel. Соответственно, мы можем работать с ним, просто вывести на экран не можем.

Итак, просто используем функцию СУММ.

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

Неполное соответствие

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

Но как это, не полностью?

Текстовые значения — всегда полное соответствие, запомните это. А вот числа могут и не полностью соответствовать.

Как обычно — объясню все на примере.

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

Вот пример:

Если ученик набрал от 0 до 33 баллов — оценка F и так далее.

Формула будет выглядеть так.

=ИНДЕКС($F$3:$F$8;ПОИСКПОЗ(B2;$E$3:$E$8;1);1)  

Как это работает?

Обратите внимание, что мы использовали тип «1» в функции ПОИСКПОЗ.

Как только функция найдет значение равное или меньшее — она вернет адрес ячейки с оценкой.

Если ученик набрал 85 баллов — функция вернет 5. А индекс уже подставит адрес ячейки с оценкой B и «поставит» её ученику.

Не забудьте, так как мы используем тип “1”, оценки должны быть отсортированы по возрастанию.

То что мы описали выше можно получить с помощью функции ПРОСМОТР

=ПРОСМОТР(B2;$E$3:$F$8;2;ИСТИНА)  

Если мы изменим порядок на убывающий, относительно оценок — результат останется прежним.

Только не забудьте поменять тип в функции ПОИСКПОЗ на «-1».

Формула:

=ИНДЕКС($F$3:$F$8;ПОИСКПОЗ(B2;$E$3:$E$8;-1);1)  

Учитываем регистр

До сих пор мы не обращали внимание на регистр. Но что если в задаче необходимо учитывать и его?

Сейчас объясню что это значит: допустим в функции ПОИСКПОЗ у нас есть критерий ДЖИМ. В таком случае «ДЖИМ», «Джим» и «джим» будут правильными вариантами. Но что если вам нужно сделать так, чтобы правильным вариантом был только, например, «Джим»?

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

Допустим, у нас есть следующая табличка:

Как вы можете заметить, Джима здесь два, один с большой буквы, второй с маленькой.

Так как же нам разделить их и выбрать какого-то определенного?

Это формула массива, поэтому подтверждаем введение CTRL + SHIFT + ENTER:

=ИНДЕКС($B$2:$B$11;ПОИСКПОЗ(ИСТИНА;СОВПАД(D3;A2:A11);0);1)

Как это работает?

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

Ищем самое близкое к критерию численное значение

Итак, с этого момента будет немного посложней.

Допустим, у вас есть такая табличка:

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

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

=ИНДЕКС($A$2:$A$15;ПОИСКПОЗ(МИН(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))  

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

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

Тоже самое и делает наша комбинация функций.

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

Комбинация функций совместно с операторами поиска

Еще один интересный пример — использование нашей комбинации функций вместе с операторами поиска («*»,«?» и так далее).

Допустим, у нас есть такая табличка:

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

Но данные мы без проблем получим, просто будем использовать для этого оператор («*»).

Вот формула:

=ИНДЕКС($B$2:$B$10;ПОИСКПОЗ(D2&”*”;$A$2:$A$10;0);1)

Как это работает?

Так как мы ищем не полное совпадение, а частичное, мы используем функцию ПОИСКПОЗ совместно с («*»).

Этот оператор значит то, что функция ПОИСКПОЗ будет искать Apple и любое продолжение строки.

И таким нехитрым образом она получает значение Apple Inc, так как нашла Apple и продолжение строки.

Если вы хотите найти строку, где Apple будет в конце — используйте «Apple» , а если не знаете где конкретно находится слово, или оно находится посередине, то можно использовать «Apple».

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

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

Что это значит?

С обычным поиском все более-менее понятно, но что за многосторонний поиск?

Сразу пример, допустим нам нужно не только узнать оценки ученика, по конкретному предмету, но и конкретизировать это, например, на промежуточном экзамене?

Формула будет выглядеть так:

=ИНДЕКС(($B$3:$D$7;$B$11:$D$15;$B$19:$D$23);ПОИСКПОЗ($F$5;$A$3:$A$7;0);ПОИСКПОЗ(G$4;$B$2:$D$2;0);(ЕСЛИ(G$3="Unit Test";1;ЕСЛИ(G$3="Mid Term";2;3))))

Эта формула выполняла поиск сразу по 3 критериям: имени ученика, предмету и типу экзамена.

Как это работает?

• ($B$3:$D$7;$B$11:$D$15;$B$19:$D$23): три диапазона для поиска всех данных;
• ПОИСКПОЗ($F$5;$A$3:$A$7;0): Поиск ученика по имени;
• ПОИСКПОЗ(G$4,$B$2:$D$2,0): Поиск предмета по названию;
• Основная хитрость находится в этой части формулы.В случае если экзамен первого типа, вернется один, а следом ИНДЕКС будет использовать первый массив данных. Если второй тип, то вернется два и так далее.

Лично мне такое встречалось редко, но все равно вам лучше знать как работать с этим.

Почему комбинация функций ИНДЕКС и ПОИСКПОЗ гораздо лучше чем функция ПРОСМОТР

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

И правда, чаще всего нашу комбинацию использовать гораздо удобнее.

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

Однако, если необходимо сделать обычный поиск — лучше всего подойдет ПРОСМОТР.

Комбинация ИНДЕКС и ПОИСКПОЗ это как функция ПРОСМОТР только более «прокаченная»

Итак, пройдемся по причинам, почему же наша комбинация лучше.

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

Если вам нужно вывести значение слева от того столбика, в котором вы искали — вы не сделаете это с помощью ПРОСМОТР.

И да вы можете комбинировать ПРОСМОТР с какими-либо другими функциями и все-таки найти с помощью неё значение, но уверяю вас, вы потратите кучу времени впустую.

Нашей комбинации не проблема заголовки в строках

А вот для функции ПРОСМОТР это большая проблема.

Просмотр работает только с вертикальными табличками и никак иначе.

Просмотр не может выполняться если данные отсортированы по убыванию

Одно скажу в защиту ПРОСМОТР, в области неполного соответствия критерию они на одном уровне.

Однако и тут у первой функции есть минус. Она не может работать с данными, которые отсортированы по убыванию.

Для комбинации функций ИНДЕКС и ПОИСКПОЗ нет разницы где расположены данные

Например, у вас есть такая табличка:

Если вы для поиска значений в ней используете ПРОСМОТР, а потом удаляете какой-либо столбик — все сразу же ломается. Просто потому, что функция работает по такому принципу.

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

Также конечно можно сделать и комбинируя ПОИСКПОЗ и ПРОСМОТР, но у этой комбинации все равно намного меньше возможностей.

Но функция ПРОСМОТР гораздо проще

Функция ПРОСМОТР одна из самых популярных в Excel. Я думаю это потому, что она очень простая. Максимум в ней — 4 аргумента, никаких проблем при вызове быть не может. Минусы есть — это правда, но если вы делаете обычный поиск — использование этой функции это самый оптимальный вариант.

Гораздо быстрее выучить и использовать ПРОСМОТР для новичка, в этом спору нет.

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

Надеюсь, эта статья помогла вам!