Функция ИНДЕКС (англ. INDEX) в Excel с примерами

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

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

Посмотрите на вот этот пример

Есть таблица с продажами различных фруктов в разных магазинах – это область A2:F10

Напомню синтаксис функции ИНДЕКС:

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

где массив – это наша таблица A2:F10

номер_строки – как следует из описания – это номер строки нашего массива (таблицы), Обратите внимание! Номер строки необходимо считать именно по нашему указанному массиву, а не вообще с первой строки. Наша таблица начинается со второй строки.

номер_столбца – это номер столбца указанного массива. В нашем случае первый столбец совпадает с первым столбцом нашего массива.

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

=ИНДЕКС(A2:F10;5;3) – смотрите рисунок выше

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

Функция ИНДЕКС в Excel с функцией ПОИСКПОЗ

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

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

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

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

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

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

Чтобы найти номер строки используем функцию ПОИСКПОЗ – поиск позиции. Синтаксис функции:

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

искомое_значение – нашем случае, в ячейке L4 мы ищем груши, поэтому искомое значение у нас будет K4

просматриваемый массив – нашем примере нам необходимо найти груши с столбце с фруктами – это диапазон A2:A10

тип_сопоставления – указываем 0, так как мы ищем полное совпадение.

Формула будет иметь следующий вид:

=ПОИСКПОЗ(K4;A2:A10;0) – итогом данной формулы будет позиция 3 в диапазоне A2:A10

аналогично, только в горизонтальном виде находим номер столбца.

искомое_значение – магазин перекресток или ячейка L3

просматриваемый массив – магазин мы находим в строке с магазинами – это диапазон A2:F2

тип_сопоставления – указываем 0, так как мы ищем точное совпадение.

в итоге получаем формулу:

=ПОИСКПОЗ(L3;A2:F2;0) – итогом данной формулы будет позиция 3 в диапазоне A2:F2

Теперь в нашу формулу =ИНДЕКС(A2:F10;3,3) вместо номера строки и номера столбца пропишем раноценные значения, но в виде формул:

=ИНДЕКС(A2:F10;ПОИСКПОЗ(K4;A2:A10;0);ПОИСКПОЗ(L3;A2:F2;0))

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

наш массив A2:F10 имеет относительный адрес, поэтому при протягивании формулы вниз и право диапазон так же будет сдвигаться, а он у нас постоянный, поэтому пропишем преобразуем его в абсолютный адрес, для этого пропишем знаки долларов перед столбцами и строки (можно выделить данный диапазон  в формуле и нажать клавишу F4).

A2:F10 → $A$2:$F$10

Далее идет номер строки с формулой ПОИСКПОЗ(K4;A2:A10;0), при протягивании вниз у нас автоматически K4 (Груши) поменяется на K5 (Сливы), что нам и требуется, но диапазон, А2:A10 у нас постоянный, поэтому пропишем его в абсолютном виде А2:A10 → $А$2:$A$10

Все отлично, но когда мы будем протягивать формулу вправо, то K4 (Груши) автоматически поменяется на L4, нам же необходимо, чтобы при протягивании право K4 не менялось. Но мы помним, что в то же время нам необходимо, чтобы K4 менялось при протягивании вниз. Поэтому нам необходимо закрепить только столбец (K), а строка должна меняться. Пропишем знак доллар только перед столбцом  К4 → $K4

В итоге формула поиска номера строки будет выглядеть ПОИСКПОЗ($K4;$A$2:$A$10;0)

Аналогично с номером столбца, диапазон должен быть полностью закреплен, при протягивании вправо столбец должен меняться, а при протягивании вниз номер строки (строка с магазинами) не должен меняться. Для этого пропишем знак доллара только перед номером строки L3 → L$3

В итоге формула поиска номера столбца будет выглядеть ПОИСКПОЗ(L$3;$A$2:$F$2;0)

Итоговая формула, которую вы можете протянуть вниз и вправо

=ИНДЕКС($A$2:$F$10;ПОИСКПОЗ($K4;$A$2:$A$10;0);ПОИСКПОЗ(L$3;$A$2:$F$2;0))

=INDEX($A$2:$F$10;MATCH($K4;$A$2:$A$10;0);MATCH(L$3;$A$2:$F$2;0)) – англ. версии

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

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

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

Скачать пример файла – Функция-ИНДЕКС-и-ПОИСКПОЗ.xlsx