Index
BurkotФункцию ИНДЕКС можно найти на вкладке "Формулы" в библиотеке функций в категории "Ссылки и массивы".
Функция ИНДЕКС в библиотеке формул
Функция ИНДЕКС возвращает значение (диапазон значений) или ссылку на ячейку (диапазон ячеек) из таблицы, диапазона или массива констант.
Общий синтаксис функции выглядит так:
ИНДЕКС(диапазон(ы); номер_строки; [номер_столбца]; [номер_области])
Разберем аргументы подробнее.
Аргумент 1 - Диапазон(ы)
Этот аргумент указывает диапазон, таблицу или массив, из которого будет извлекаться искомое значение или ссылка. В качестве этого аргумента может быть указана:
· ссылка на одномерный диапазон, например А1:А10 или А1:X1
· ссылка на двумерный диапазон, например А1:B20
· ссылка на несмежные диапазоны, например (А1:B5;D1:E5;H1:I5) (такую ссылку всегда следует брать в скобки, чтобы Excel понял, что ";" в этом случае выступает как оператор объединения диапазонов, а не как разделитель аргументов в функции). Диапазоны должны находиться на одном листе.
· массив констант, например {1;2;3:4;5;6:7;8;9}. При использовании такого аргумента, функция принимает так называемую Форму массива и не может вернуть ссылку на диапазон. Также недоступен аргумент "номер области". Все остальные примеры использования аргумента относятся к "Ссылочной форме" синтаксиса функции ИНДЕКС.
Аргумент 2 - Номер строки
Указывает на номер строки, из которой нужно вернуть значение, или ссылку на которую нужно сделать в результате вычисления функции. Имеются следующие особенности:
· если в первом аргументе задан одномерный диапазон (строка или столбец), то аргумент "Номер строки" указывает на порядковый номер извлекаемого из диапазона элемента;
· если аргумент опущен или равен нулю, то возвращаются все строки;
· аргумент должен указывать на строку в пределах диапазона. Например, если для диапазона A1:A10 указать номер строки 15, то функция вернет ошибку #ССЫЛКА!, так как строка находится за пределами диапазона.
Аргумент 3 - Номер столбца
Указывает на номер столбца, из которого нужно вернуть значение или ссылку на который нужно сделать в результате вычисления функции. Имеются следующие особенности:
· если в первом аргументе задан одномерный диапазон (строка или столбец), то аргумент "Номер столбца" можно не указывать (номер элемента определится по аргументу "Номер строки");
· если аргумент опущен или равен нулю, то возвращаются все столбцы;
· аргумент должен указывать на столбец в пределах диапазона. Например, если для диапазона A1:C1 указать номер столбца 5, то функция вернет ошибку #ССЫЛКА!, так как столбец находится за пределами диапазона.
Аргумент 4 - Номер области
Используется, когда в качестве первого аргумента указан несмежный диапазон. В таком случае Excel нумерует все диапазоны в порядке, в котором они введены в функцию, и возвратит результат из того диапазона, номер которого указан в этом аргументе. Если указать номер больше, чем указано диапазонов, то формула вернет ошибку #ССЫЛКА!
В зависимости от контекста, результатом вычисления функции может быть:
· значение из ячейки (когда аргументы указывают строго на одну ячейку);
· массив значений (когда аргументы указывают на массив, например, опущен аргумент № 2, или № 3, или оба);
· ссылка на диапазон (когда в формуле перед функцией находится один из операторов работы с диапазонами: двоеточие, точка с запятой или пробел).
Примеры использования функции ИНДЕКС
Связка ИНДЕКС + ПОИСКПОЗ
Пожалуй, самый популярный вариант использования данной функции. Позволяет извлекать из диапазона значение, на ходу вычисляя строку и столбец, из которых это значение нужно достать. Часто используется как альтернатива функции ВПР (при этом, в отличие от нее, может находить значения слева от ключевого поля).
Исходные данные
Чтобы в ячейку B18 всегда выводилось значение, находящееся в таблице A2:H14 на пересечении месяца, указанного в B16, и года, указанного в B17, нужно ввести формулу: =ИНДЕКС($B$3:$H$14;ПОИСКПОЗ(B16;$A$3:$A$14;0);ПОИСКПОЗ(B17;$B$2:$H$2;0)).
Функции ПОИСКПОЗ находят номера строки и столбца, а ИНДЕКС возвращает значение, находящееся на их пересечении (гифка ниже в хорошем качестве).
Пример работы связки ИНДЕКС+ПОИСКПОЗ
Возврат значения из нужной таблицы
Усложним задачу. Добавим в первый аргумент несколько несмежных диапазонов, а определять номер области будем с помощью всё той же функции ПОИСКПОЗ.
Исходные данные
Чтобы в ячейку B18 всегда выводилось значение, находящееся в таблице соответствующего года (указанного в B17) на пересечении месяца, указанного в B16, и магазина, указанного в B15, нужно ввести формулу: =ИНДЕКС((B2:D13;G2:I13;L2:N13);ПОИСКПОЗ(B16;A2:A13;0);ПОИСКПОЗ(B15;B1:D1;0);ПОИСКПОЗ(B17;{2015;2016;2017};0)).
Функции ПОИСКПОЗ находят номера строки, столбца и таблицы, а ИНДЕКС возвращает значение, находящееся на их пересечении (обратите внимание, что в последней функции ПОИСКПОЗ в качестве аргумента используется массив констант {2015;2016;2017}). Гифка ниже в хорошем качестве
Пример работы с несмежными диапазонами
Сумма всего столбца / всей строки
Используя тот факт, что если опустить один аргумент, то функция вернет массив значений, можно получить ссылку на целую строку или столбец. Если нужны сами значения - придется использовать формулу массива, а если их нужно, например, просуммировать, то можно обойтись обычной функцией СУММ (аргументом указать функцию ИНДЕКС).
Исходные данные
Чтобы в ячейках B17:H17 выводились все значения строки соответствующего месяца, кроме итогового столбца, нужно выделить этот диапазон, ввести формулу, указанную ниже и нажать Ctrl+Shift+Enter.
=ИНДЕКС($B$3:$H$14;ПОИСКПОЗ(B16;$A$3:$A$14;0);)
Чтобы отобразить сразу суммарное значение для одного месяца за все года, нужно ввести в B18 следующую формулу:
=СУММ(ИНДЕКС($B$3:$H$14;ПОИСКПОЗ(B16;$A$3:$A$14;0);))
Гифка ниже в хорошем качестве.
Пример возврата целой строки
Динамический диапазон
Обычно для создания динамических диапазонов применяют функцию СМЕЩ, но можно использовать и ИНДЕКС, помня о том, что она может возвращать ссылки на ячейки. В отличие от СМЕЩ, функция ИНДЕКС не является "летучей", а значит не пересчитывается при каждом изменении листа и не тормозит работу файла.
Исходные данные
Чтобы в ячейку B18 всегда выводилась сумма значений из таблицы за указанный год и период с месяца по месяц включительно, нужно ввести в нее следующую формулу:
=СУММ(ИНДЕКС($B$2:$H$13;ПОИСКПОЗ(B15;$A$2:$A$13;0);ПОИСКПОЗ(B17;$B$1:$H$1;0)):ИНДЕКС(B2:H13;ПОИСКПОЗ(B16;A2:A13;0);ПОИСКПОЗ(B17;B1:H1;0)))
В этой формуле первая функция ИНДЕКС находит ячейку на пересечении указанного года и месяца, с которого идет отсчет. Вторая функия - находит ячейку на пересечении указанного года и месяца, до которого идет отсчет. Так как между ними в формуле стоит оператор указания диапазона (двоеточие), то обе они возвращают ссылки, которые в итоге создают динамический диапазон. После чего функция СУММ находит сумму этого диапазона (гифка ниже в хорошем качестве).
Пример создания динамического диапазона
Как видите, функция ИНДЕКС очень универсальна и многогранна. Без ее освоения трудно будет реализовывать в Excel действительно сложные и красивые формулы.