Функция INDEX в Excel

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

Что делает функция INDEX в Excel?

Функция INDEX в Excel позволяет получить значение в списке или таблице в зависимости от местоположения.

Возвращаемое значение

Значение в данном месте.

Аргументы

  • array — диапазон ячеек или константа массива.
  • row_num — позиция строки в ссылке или массиве.
  • col_num — [необязательно] Позиция столбца в ссылке или массиве.
  • area_num — [необязательный] Диапазон ссылок, который следует использовать.

Синтаксис

=INDEX(array, row_num, [col_num], [area_num])

Примечания по использованию

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

Функция INDEX в Excel

В наиболее распространенном использовании INDEX принимает три аргумента: array, row_num, и col_num. array — это диапазон или массив, из которого извлекаются значения. Row_num — это номер строки, из которой извлекается значение, а col_num — это номер столбца, из которого извлекается значение. Col_num является необязательным и не требуется, если массив одномерный.

В примере, показанном выше, цель — получить диаметр планеты Юпитер. Поскольку Юпитер — пятая планета в списке, а Диаметр — третий столбец, формула в G7 имеет вид:

=INDEX(B5:E13,5,3) // диаметр Юпитера

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

Основное использование

INDEX получает значение в заданном месте в диапазоне ячеек на основе числовой позиции. Если диапазон одномерный, вам нужно указать только номер строки. Если диапазон двумерный, вам потребуется указать номер строки и столбца. Например, чтобы получить третий элемент из одномерного диапазона A1:A5:

=INDEX(A1:A5,3) // возвращает значение в A3

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

=INDEX(A1:B5,2,2) // возвращает значение в B2
=INDEX(A1:B5,3,1) // возвращает значение в A3

ИНДЕКС и СООТВЕТСТВИЕ

В приведенных выше примерах позиция «жестко запрограммирована». Обычно функция MATCH используется для поиска позиций по INDEX. Например, на экране ниже функция MATCH используется для поиска «Марса» (G6) в строке 3 и передачи этой позиции в ИНДЕКС. Формула в G7:

=INDEX(B5:E13,MATCH(G6,B5:B13,0),3)

ПОИСКПОЗ передает номер строки (4) в ИНДЕКС. Номер столбца по-прежнему жестко запрограммирован как 3.

INDEX и MATCH с горизонтальной таблицей

На экране ниже приведенная выше таблица транспонирована горизонтально. Функция MATCH возвращает номер столбца (4), а номер строки жестко запрограммирован как 2. Формула в C10:

=INDEX(C4:K6,2,MATCH(C9,C4:K4,0))

Вся строка/столбец

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

=INDEX(range,0,n) // вся колонна целиком
=INDEX(range,n,0) // весь ряд

где n представляет номер возвращаемого столбца или строки.

Ссылка как результат

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

=INDEX(A1:A5,2) // возвращает A2

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

Две формы

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

Форма массива

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

INDEX(array,row_num,[col_num])

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

Справочная форма

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

INDEX(reference,row_num,[col_num],[area_num])

Как и форма массива INDEX, ссылочная форма INDEX возвращает ссылку на ячейку на пересечении row_num и col_num. Разница в том, что аргумент ссылки содержит более одного диапазона, а параметр area_num выбирает, какой диапазон следует использовать. Аргумент area_num предоставляется в виде числа, действующего как числовой индекс. Первый массив внутри ссылки равен 1, второй массив — 2 и так далее.

Например, в приведенной ниже формуле для параметра area_num указано значение 2, что соответствует диапазону A7:C10:

=INDEX((A1:C5,A7:C10),1,3,2)

В приведенной выше формуле ИНДЕКС вернет значение в строке 1 и столбце 3 A7:C10.

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

Обучаю основам работы с офисными программами.

Функция INDEX в Excel
Глава 1. Основы Outlook