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

Основной синтаксис

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

Пока только замечу, что параметры 3 и 4 не обязательные и могут не указываться (запомните, что квадратные скобки при описании синтаксиса формул или команд говорят о необязательности параметра). Подобная вложенность квадратных скобок говорит о том, что формула применяется с 2-мя параметрами, либо с 3-мя, либо с 4-мя.

Возврат элемента диапазона

Обычное, типовое использование ИНДЕКС это:

  =ИНДЕКС( A1:C5 ; 2 ; 2 )

Формула вернёт значение из ячейки на пересечении второй строки и второго столбца диапазона A1:C5. Это значение 5 из B2. Для многих на этом понимание предназначения функции ИНДЕКС и заканчивается. Но только не для нас с вами, мой пытливый читатель!

Гораздо менее известным фактом является то, как ИНДЕКС работает с векторами. Вектор - это диапазон, состоящий из одного столбца или одной строки.

  =ИНДЕКС( A3:C3 ; 3 ) вернёт 9, и

  =ИНДЕКС( C1:C5 ; 3 ) тоже вернёт 9!

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

Что возвращает формула?

Ещё одна удивительная особенность - формула ИНДЕКС возвращает ссылку на ячейку, а не значение ячейки. Внимательный читатель, конечно же, запальчиво воскрикнет, что, мол за ерунда, почему тогда предыдущий пример не вернул нам С3, а вернул 9? Дело в том, что данный факт маскируется оператором присваивания (знак равно), с которого начинается любая формула.

  =ИНДЕКС( C1:C5 ; 3 ) фактически проходит этап =C3 и только затем превращается в 9.

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

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

• Конструкция ИНДЕКС(A5;1):ИНДЕКС(C5;1) должна возвращать нам A5:C5.

  И это действительно так, что можно подтвердить косвенно, ибо

    =СУММ(ИНДЕКС(A5;1):ИНДЕКС(C5;1)) возвращает 42 (13+14+15), а

    =ЧИСЛСТОЛБ(ИНДЕКС(A5;1):ИНДЕКС(C5;1)) возвращает 3.

  А можно подтвердить и напрямую через ввод формулы массива в любые 3 горизонтальных ячейки. Для этого выделите 3 ячейки за пределами A1:C5, вытянутые в строку, нажмите знак равно и введите буквально

    =ИНДЕКС(A5;1):ИНДЕКС(C5;1)

  и после этого нажмите Ctrl+Shift+Enter. После чего вы увидите следующую картину:

  

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

    {=ИНДЕКС(A5;1):ИНДЕКС(C5;1)} знайте, что это формулы массивов, которые надо вводить в ячейку или группу ячеек способом, который описан мною выше.

  Примечание: надеюсь вы понимаете, что ИНДЕКС(A5;1) вернёт ссылку на A5, так как любая ячейка может рассматриваться в качестве вектора с числом элементов равным 1. ЧИСЛСТОЛБ - формула, возвращающая количество столбцов в диапазоне.

• Конструкция

    =ОБЛАСТИ((ИНДЕКС(A1;1);ИНДЕКС(C3;1))) вернёт нам 2.

  Напомню, что функция ОБЛАСТИ возвращает количество областей внутри переданной ссылки. Этот вызов идентичен вызову =ОБЛАСТИ((A1;C3)), то есть ИНДЕКС нам успешно сгенерировал 2 области через оператор ";".

• Чтобы продемонстрировать вам, что ИНДЕКС можно использовать с оператором пересечения диапазонов, я должен сненерировать при помощи ИНДЕКС диапазоны с размером больше, чем одна ячейка, но это как раз то, что я только собираюсь вам объяснить ниже, поэтому доказательство я вам приведу, но его синтаксическая конструкция будет сложнее, чем могла бы быть. Я хочу сгенерировать через ИНДЕКС такой результат =B1:B5 A5:C5

  

  Формула кого-то может испугать, но на самом деле всё просто. Я буквально конструирую из ИНДЕКС именно то, что нам надо.

    =ИНДЕКС(B1;1):ИНДЕКС(B5;1) ИНДЕКС(A5;1):ИНДЕКС(C5;1)

  

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

Возврат столбца / строки

ИНДЕКС может возвращать не только ссылки отдельные ячейки, но и векторы.

Конструкция ИНДЕКС(A1:C5;;3) - вернёт вектор C1:C5 (столбец), так как, если пропустить параметр номер_строки (или указать его равным нулю), но указать параметр номер_столбца, то ИНДЕКС возвращает вектор указанного столбца в пределах диапазона из первого параметра.

таким образом:

  =СУММ(ИНДЕКС(A1:C5;;3)) вернёт 45,

  =ЧСТРОК(ИНДЕКС(A1:C5;;3)) вернёт 5, а

  {=ИНДЕКС(A1:C5;;3)} вернёт диапазон C1:C5.

В случае формулы массива обратите внимание, что, если вы ввели формулу массива в диапазон ячеек, который превышает размер возвращаемого массива, то лишние ячейки получат значения #Н/Д (смотрите ситуацию 2 на рисунке). А, если формула возвращает вертикальный массив, а принимающий диапазон расположен горизонтально, то все ячейки горизонтального диапазона получат одинаковое значение - из первого элемента возвращаемого вектора (смотрите ситуацию 3 на рисунке), и наоборот, то есть направление векторов должно совпадать.

По аналогии, конструкция ИНДЕКС(A1:C5;3;) вернёт вектор A3:C3, то есть строку. Заметьте, что наличие второй точки с запятой принципиально, и за ней должна быть либо пустота (пробелы игнорируются), либо ноль.

А теперь вернёмся к формуле:

  =ИНДЕКС(B1;1):ИНДЕКС(B5;1) ИНДЕКС(A5;1):ИНДЕКС(C5;1) и с учётом новых знаний упростим её до вида:

  =ИНДЕКС(A1:C5;;2) ИНДЕКС(A1:C5;5;) - выглядит гораздо более лаконично!

Выбор области

Помните, что в описании синтаксиса ИНДЕКС первый параметр назывался диапазон(ы). Пришло время поговорить вот про эту букву "ы". Выходит диапазонов может быть несколько? Да, их может быть несколько, когда они перечислены через уже упоминаемый выше оператор объединения, - ";" и взяты в скобки. Вот так:

  =СУММ( ИНДЕКС( (A1:B5;B1:C5); ; ; A9) )

Если A9 содержит 1, то ИНДЕКС вернёт диапазон A1:B5, а если 2, то B1:C5. Обратите внимание, что второй и третий параметры опущены, это означает, что исходные диапазоны вообще не будут подвергаться какому-либо усечению и вернутся, как есть (до этого мы "отщипывали" то строку, то столбец). В первом случае сумма будет 75, во втором - 85.

Выходит ИНДЕКС может возвращать ячейки, вектора и диапазоны. Невероятная гибкость!

Файл примера

Скачать

Динамические диапазоны

Выше мы с вами убедились, что ИНДЕКС может творить всё что угодно с диапазонами. Поэтому совсем неудивительно, что человечество использует его для создания динамических диапазонов. У меня есть на эту тему отдельная статья, но некоторые примеры я приведу всё равно.

  =A1:ИНДЕКС(A:A;СЧЁТЗ(A:A)) - классика жанра, нижняя граница диапазона определяется при помощи ИНДЕКС.

Наиболее распространенные способы определения нижней границы можно посмотреть тут:

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

  =ИНДЕКС( ME[Начисление]; ПОИСКПОЗ( 'L3'!$G$2; ME[Телефон]; 0 ) ):ИНДЕКС( ME[Начисление]; ПОИСКПОЗ( 'L3'!$G$2; ME[Телефон]; 1 ) )

ПОИСКПОЗ с третьим параметром, равным 0, используется для определения первой строки с номер телефона, а она же с параметром, равным 1, - для определения последней строки. ИНДЕКС же помогает сформировать динамический диапазон. Аналогично формируется ИД Период. ИД Начисления использован для построения ряда диаграммы, а ИД Период для значений оси.

Более мощная замена ВПР

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

Прощайте ВПР() и СМЕЩ()

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

Подводим итоги

Итак ИНДЕКС:

• Возвращает ссылки, а не значения, что позволяет с его помощью формировать очень эффективные динамические диапазоны

• Может возвращать ссылки как на отдельные ячейки, на вектора и на целые диапазоны

• Позволяет выбирать области - ещё одна степень свободы

• Это не летучая функция

• ИНДЕКС+ПОИСКПОЗ быстрее и гибче ВПР для целей извлечения данных

• Незаменим в формулах массивов

Это ключевая формула в Excel. Знать её просто необходимо. Серьёзная работа в Excel без неё немыслима. Ура!

P.S.

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

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

Под массивом тут подразумеваются конструкции вида {12:34:76:99} - столбец, или {12;34;76;99} - строка. Ясно, что ссылку от такого массива не вернёшь, - только значение и поэтому вроде бы как получается, что это отдельная синтаксическая форма. Пример:

  =ИНДЕКС({1;2;3:4;5;6:7;8;9};2;2) вернёт 5.

Массив {1;2;3:4;5;6:7;8;9} - не что иное, как

Читайте также:

• Список всех формул Excel и классификация их востребованности

• Видеоурок по функции ВПР (VLOOKUP)

• 7 причин полюбить именованные диапазоны

• Функция ВПР (VLOOKUP) или тайна четвёртого параметра

• Производительность функций извлечения данных

• Усовершенствованные динамические именованные диапазоны

• Введение в формулы массивов