5 вариантов использования функции ИНДЕКС (INDEX)

Бывает у вас такое: смотришь на человека и думаешь "что за @#$%)(*?" А потом при близком знакомстве оказывается, что он знает пять языков, прыгает с парашютом, имеет семеро детей и черный пояс в шахматах, да и, вообще, добрейшей души человек и умница?

Так и в Microsoft Excel: есть несколько похожих функций, про которых фраза "внешность обманчива" работает на 100%. Одна из наиболее многогранных и полезных - функция ИНДЕКС (INDEX). Далеко не все пользователи Excel про нее знают, и еще меньше используют все её возможности. Давайте разберем варианты ее применения, ибо их аж целых пять.

Вариант 1. Извлечение данных из столбца по номеру ячейки

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

=ИНДЕКС(Диапазон_столбец; Порядковый_номер_ячейки)

Простой вариант ИНДЕКС

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

ИНДЕКС в связке с ПОИСКПОЗ

... но, в отличие от ВПР, могут извлекать значения левее поискового столбца и номер столбца-результата высчитывать не нужно.

Вариант 2. Извлечение данных из двумерного диапазона

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

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

ИНДЕКС 2 вариант

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

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

Двумерный поиск с ИНДЕКС и ПОИСКПОЗ

Вариант 3. Несколько таблиц

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

=ИНДЕКС((Диапазон1;Диапазон2;Диапазон3); Номер_строки; Номер_столбца; Номер_диапазона

ИНДЕКС с неск.таблицами

Обратите особое внимание, что в этом случае первый аргумент – список диапазонов - заключается в скобки, а сами диапазоны перечисляются через точку с запятой.

Вариант 4. Ссылка на столбец / строку

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

ИНДЕКС выдающая ссылку на целую строку-столбец

Обратите внимание, что поскольку ИНДЕКС выдает в этом варианте не конкретное значение ячейки, а ссылку на диапазон, то для подсчета потребуется заключить ее в дополнительную функцию, например СУММ (SUM), СРЗНАЧ (AVERAGE) и т.п.

Вариант 5. Ссылка на ячейку

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

ИНДЕКС как часть ссылки

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

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

Ссылки по теме


СохранитьСохранить


Спасибо за подробное объяснение. Практически пользуюсь первым и вторым примером очень давно и часто. Особенно при мгновенном составлении бланка ответа на исходящий, с учетом выборки из базы и отдельно выбора должности и фио подписанта.
MCH
08.12.2016 22:38:29
Не указано в статье, что функция ИНДЕКС() не может возвратить массив значений, если номера строк (или столбцов) ей передавать в виде массива констант
На примере данных "Варианта 5":
Нужно получить массив из чисел 1го, 3го и 4го элемента
Вариант
=ИНДЕКС(B2:B7;{1;3;4})
- не работает (не возвращает массив значений)
можно убедится, следующие формулы возвращают только первое значение:
=СУММ(ИНДЕКС(B2:B7;{1;3;4}))
=СЧЁТ(ИНДЕКС(B2:B7;{1;3;4}))

Однако это ограничение можно обойти используя конструкцию:
=ИНДЕКС(B2:B7;Ч(ИНДЕКС({1;3;4};)))
15.12.2016 17:26:28
жаль, но так и неполучилось найти сумму по указанной конструкции (индекс из индекса)
можно полную формулу, как это должно выглядеть?
MCH
15.12.2016 17:39:50
Скачиваем файл-пример
На листе "ИНДЕКС 7"
вводим формулу:
=СУММ(ИНДЕКС(B2:B7;Ч(ИНДЕКС({1;3;4};))))

Получим результат: 16976
Фактически вычисляется: СУММ({6050;9672;1254})
22.03.2018 10:08:46
Ваша 1ая формула возвращает массив значений в виде строки.  Транспонируйте до
{=ИНДЕКС(B2:B7;ТРАНСП({1;3;4}))}
или после
{=ТРАНСП(ИНДЕКС(B2:B7;{1;3;4}))}
или сразу
{=ИНДЕКС(B2:B7;{1:3:4})}
MCH
22.03.2018 17:46:41
Семен Киселев, предложенные Вами варианты возвращают только одно единственное значение, а не массив, если формула вводятся в одну ячейку, а не в диапазон, убедится в этом можно если попытаться вычислить сумму или количество значений, или нажать F9 при редактировании формулы
23.03.2018 16:22:18
Да, я Вас не понял. Спасибо за пост с Ч()! Вообще, странная штука
Ч(ИНДЕКС({1;3;4};))
- возвращает массив значений без формулы массива, но попытка ввести эту формулу в ячейку не как формулу массива дает #ЗНАЧ!, а не 1. И при просмотре аргументов внешнего ИНДЕКСа Вашей формулы тоже видим #ЗНАЧ!, хотя формула отлично работает.
Нашёл обсуждение фишки и примеры применения, но не нашёл объяснения, почему она работает. Также ставит в тупик вопрос, почему без формулы массива ответ формулы ниже - это ячейка С4:
=ИНДЕКС(B2:D7;3;)
09.12.2016 09:50:04
Возник вопрос подскажите: ЕСЛИ в столбце одинаковые данные, второй столбец напротив каждого значения стоит опр дата-- КАК вычислить самую позднюю дату?
Без проблем, используя формулу массива.
Имеем в столбце D со 2 строки по 12 наименования товара, в столбце Е так же по строкам даты.
Допустим в ячейку F18 вносим наименование товара и в ячейке с формулой  {=MAKC(ЕСЛИ(F18=D2:D12;E2:E12))} получаем максимальную дату для выбранного товара.
Не забываем про сочетание Ctrl+Shift+Enter при вводе формулы
12.12.2016 13:52:33
Спасибо! Мегаполезная функция! 8) Осталось создать привычку ее использовать. ;)
18.12.2016 17:45:34
Я вообще без неё не представляю работу с Excel, без неё и без СУММПРОИЗВ: динамическое определение диапазонов; фильтры и выборки данных в одну формулу; ситуативные графики.
Например как-то так использую, чтобы вытащить из любой плоской таблицы данные по сложному условию (и никакой тебе предварительной сортировки и фильтрации):
=ИНДЕКС(ИмяТабл;    
  ПОИСКПОЗ(1;ИНДЕКС( 
    (условие1=ИНДЕКС(ИмяТабл;;1))*((условие2=ИНДЕКС(ИмяТабл;;3))+(условие3>ИНДЕКС(ИмяТабл;;7))););0); 
  ПОИСКПОЗ("*имя столбца*";ИНДЕКС(ИмяТабл;1;);0) 
)
А ещё вот очень хорошая статья на тему (я по ней учился :) )
24.12.2023 11:18:39
vladjuha, хорошо бы ещё пример на такую формулу посмотреть. Для наглядности и лучшего понимания.
26.12.2016 10:27:04
Спасибо Николай за полезный урок. Куча примеров все просто супер. Я например не догадывался что индекс может посчитать сумму первых n чисел в диапазоне. Как говорится век живи век учись.
27.12.2016 07:57:37
Пятый пример интересен. Думаю его можно будет применить для равномерного распределения суммы на определенное количество дней при составлении календарного графика выполнения работ имеющих свою стоимость. 8)
18.01.2017 21:47:29
Здравствуйте!
Есть вопрос по варианту 5 из статьи. В приведенном примере вычисляется сумма первых трех значений столбца. А если нужно сделать наоборот, т.е. посчитать сумму трех последних значений столбца? Как это можно реализовать с помощью ИНДЕКС и СУММ? Я пробовал написать формулу самостоятельно, но что-то не получается. Если не трудно, то подскажите как это сделать. Заранее благодарен.
18.05.2017 18:06:46
Это можно сделать так:
 =СУММ(ИНДЕКС(B2:B7;СЧЁТ(B2:B7)-F4+1):B7)
14.04.2017 16:22:52
Николай, думаю Ваш термин «Левый ВПР» для функции «ИНДЕКС» - это самый лучший пиар!
Я люблю еще использовать «ИНДЕКС» для вычисления «названий месяца на русском», получается красиво:

=ИНДЕКС({"Январь":"Февраль":"Март":"Апрель":"Май":"Июнь":"Июль":"Август":"Сентябрь":"Октябрь":"Ноябрь":"Декабрь"};МЕСЯЦ(A2))
30.05.2017 11:21:40
Помогите ради бога! По непонятной причине связка ИНДЕКС + ПОИСКПОЗ второй день издевается надо мной :). Работаю с EXcel 2010.
Например, скачал файл с примером с этой страницы, на листе с 1 примером добавил как указано пример с городами и странами
BCDE
13РоссияМоскваПариж#ССЫЛКА!
14КитайПекин
15АнглияЛондон
16ФранцияПариж
17ГерманияБерлин
18ВенгрияБудапешт
В ячейку E13 введена формула в соответствии со статьей:   =ИНДЕКС(B13:C18;ПОИСКПОЗ(D13;C13:C18;0))

В чем ошибка?
13.07.2017 14:52:53
Николай, почему в 5 примере ИНДЕКС стал адресом, а не значением? В чём логика существования данной хитрости?
Может так можно хитрить с чем-нибудь ещё, расскажите, пожалуйста, поподробнее про эту младшую сестру ДВССЫЛ.
22.03.2018 11:09:56
Я не Николай, но ИНДЕКС всегда, когда может, возвращает ссылку. Просто введенная в ячейку ссылка выдает значение в ячейке по ссылке. Если превратить диапазон, передаваемый функции ИНДЕКС в массив (двойным транспонированием), то "магия" перестанет работать (исчезнет возможность трактовать первый аргумент ИНДЕКСа как диапазон):
{=СУММ(B2:ИНДЕКС(ТРАНСП(ТРАНСП(B2:B15));5))}
05.12.2017 17:28:01
Добрый день!

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

Je vous remercie pour vos etudes et je vous souhaite tres bonne journee,
23.03.2018 10:15:39
Помогите пожалуйста. Хочу сделать некий справочник. В котором по вертикали много значений, Потом они соответствуют группировке в столбце вверхней символом "*", потом в другой таблице нужно чтобы по значяению из первой таблице высветилось название уже группировки
связьдоставкаПодбор персоналаМаркетингНа персоналОХЗКомандировкиКредитДля производстваПрочее
Доставка до ТК**
Доставка до офиса**
Интернет мегафон офис**
Интернет цех**
Зарплата.ру**
Авито**
Мотивация менеждеры**
Связь- тел. 90-80-20**
Дизайн лендинга**
Связь тел. офис onlinePBX**
Обработка фотографий**
Платформа lp**
Электроснабжение цех**
Яндекс диск**
Услуги доставки груза**
Фриланс**
11.04.2018 10:37:40
Со 2 захода всё равно не понял, даже какая из 2 (или 3?) таблиц приведена.
11.04.2018 10:44:42
Я прошу прощения, не совсем понял сначала как здесь вставить файл. На другом разделе сайта нашел решение уже. Благодарю за желание помочь
31.08.2019 08:48:53
Добрый день. подскажите пожалуйста, ни где, не могу найти пример, когда нужно собрать данные из разных листов на одной странице в таблице. ВПР и индекс+поискпоз, не хотят работать.

вот пример таблицы
https://docs.google.com/spreadsheets/d/12o7td4QzeCLDzxnUHtbPMJC-DSPo5lbey47uFQQWP3w/edit#gid=0

Суть задачи:

По столбцу заголовка на листе1 нужно найти совпадения на лист4, лист5 и лист6 и вывести результат.
в таблице разные формулы для проверки их можно удалить. оставить только рабочий результат.


заранее спасибо
22.10.2021 16:10:47
И вот мое предложение для index вместо vlookup !

                    3=C:C   4=D
INDEX($A$1:$L$100;MATCH(M1;INDEX($A$1:$L$100;0;3);0);4) Это моя любимая!

=  
                           2=D
VLOOKUP(M1;$C$1:$L$100;2;FALSE)

=

XLOOKUP(M1;$C$1:$C$100;$D$1;$D$100;"не найден";0;1)
M1 - lookup_value
            $C$1:$C$100 - lookup_array
           $D$1;$D$100 - return_array
"не найден" - [if_not_found]
        0 - [match_mode] (0-exact match)
                               1 - [search_mode] (1 - Search first to last / -1 -Search last to first)

А вот и бонус, чтобы увидеть, что из этого получится!

 INDEX($A$1:$L$100;MATCH($M$1;INDEX($A$1:$L$100;0;3);0);)
12.02.2022 09:32:46
Вариант 4 - это просто божественно! Особенно в связке с поискпоз!:like:
08.07.2022 08:44:32
Ребята подскажите как сделать ссылку на другой файл: по названию одной из строки таблицы из определенной ячейки в другой файл. Проблема в том что из этой таблицы порой удаляются строки, соответственно и обычная ссылка сбивается.
Наверх