Функция INDEX (ИНДЕКС) в Excel возвращает значение в заданном месте диапазона или массива. Вы можете использовать ИНДЕКС для получения отдельных значений или целых строк и столбцов. Функция ПОИСКПОЗ часто используется вместе с ИНДЕКС для указания номеров строк и столбцов.
Что делает функция INDEX в Excel?
Функция INDEX в Excel позволяет получить значение в списке или таблице в зависимости от местоположения.
Возвращаемое значение
Значение в данном месте.
Аргументы
- array — диапазон ячеек или константа массива.
- row_num — позиция строки в ссылке или массиве.
- col_num — [необязательно] Позиция столбца в ссылке или массиве.
- area_num — [необязательный] Диапазон ссылок, который следует использовать.
Синтаксис
=INDEX(array, row_num, [col_num], [area_num])
Примечания по использованию
Функция ИНДЕКС возвращает значение в заданном месте диапазона или массива. INDEX — мощная и универсальная функция. Вы можете использовать функцию для получения отдельных значений или целых строк и столбцов. ИНДЕКС часто используется вместе с функцией MATCH. В этом сценарии функция находит и передает позицию в функцию ИНДЕКС, и возвращает значение в этой позиции.
В наиболее распространенном использовании 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.