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

Excel
Содержание
  1. Функция ИНДЕКС в Excel — синтаксис и основные способы использования
  2. Форма массива ИНДЕКС
  3. Что нужно помнить
  4. Что возвращает функция
  5. Аргументы функции
  6. Дополнительная информация
  7. Пример работы функции ИНДЕКС в Excel от простого к сложному
  8. Вариант 1. Извлечение данных из столбца по номеру ячейки
  9. Вариант 2. Извлечение данных из двумерного диапазона
  10. Вариант 3. Несколько таблиц
  11. Вариант 4. Ссылка на столбец / строку
  12. Вариант 5. Ссылка на ячейку
  13. Функция ИНДЕКС двумерный и многомерный массив
  14. Пример формулы комбинации функций ИНДЕКС и СУММ
  15. Как использовать функцию ИНДЕКС в Excel — примеры формул
  16. 1. Получение N-го элемента из списка
  17. 2. Получение всех значений в строке или столбце
  18. 3. Использование ИНДЕКС с другими функциями (СУММ, СРЗНАЧ, МАКС, МИН)
  19. Пример 1. Вычислить среднее значение первых N элементов в списке
  20. Пример 2. Сумма элементов между указанными двумя элементами
  21. 4. Формула ИНДЕКС для создания динамических диапазонов и раскрывающихся списков
  22. Функция ИНДЕКС для ссылок

Функция ИНДЕКС в Excel — синтаксис и основные способы использования

В Excel есть две версии функции ИНДЕКС: форма массива и форма ссылки. Оба можно использовать во всех версиях Microsoft Excel 365, 2019, 2016, 2013, 2010, 2007 и 2003.

Форма массива ИНДЕКС

В этом случае функция ИНДЕКС возвращает значение элемента в таблице или массиве на основе указанных вами номеров строк и столбцов.

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

  • массив — это диапазон ячеек, именованный диапазон или таблица.
  • Номер строки — это номер строки в матрице, из которой должно быть возвращено значение. Если этот аргумент опущен, требуется следующий номер столбца.
  • Column_number — это номер столбца, из которого вы хотите получить значение. Если опущено, требуется номер строки.

Например, формула =ИНДЕКС(C2:F11;4;3) возвращает значение на пересечении четвертой строки и третьего столбца в диапазоне C2:F11, которое является значением в ячейке D4.

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

Вместо того, чтобы вводить в формулу номера строк и столбцов, вы можете включить ссылки на ячейки, чтобы получить более общую формулу:

=ИНДЕКС(C2:F11;I1;I2)

Таким образом, эта формула ИНДЕКС возвращает количество элементов точно на пересечении номера элемента, введенного в ячейку I1 (номер строки), и номера недели, введенного в ячейку I2 (номер столбца).

Примечание. Использование абсолютных ссылок ($C$2:$F$11) вместо относительных ссылок (C2:F11) в аргументе массива упрощает копирование формулы в другие ячейки. Кроме того, вы можете преобразовать диапазон в таблицу (Ctrl+T) и ссылаться на нее по имени таблицы.

Что нужно помнить

  1. Если аргумент массива состоит только из одной строки или столбца, вы можете указать или не указать соответствующий аргумент номера строки или номера столбца.
  2. Если аргумент массива включает более одной строки, а номер_строки опущен или равен 0, функция ИНДЕКС возвращает массив всего столбца. Аналогичным образом, если массив содержит более одного столбца и аргумент columnNumber опущен или равен 0, формула ИНДЕКС возвращает всю строку. Вот пример формулы, демонстрирующей такое поведение.
  3. Аргументы номера строки и номера столбца должны ссылаться на ячейку в матрице; в противном случае формула ИНДЕКС вернет ошибку #ССЫЛКА!.

Что возвращает функция

Возвращает данные из определенной строки и столбца данных таблицы.

Аргументы функции

  • array (массив) — последовательность ячеек или последовательность данных для поиска;
  • номер строки (row number) — номер строки, содержащей искомые данные;
  • номер_столбца (номер_столбца) (необязательный аргумент) — это номер столбца, содержащего искомые данные. Этот аргумент является необязательным. Но если критерий номера строки (номер строки) не указан в аргументах функции, аргумент номер столбца (номер столбца) должен быть указан);
  • номер_области (номер_области) — (необязательный аргумент) — если аргумент массива состоит из нескольких областей, это число будет использоваться для выбора всех областей.

Дополнительная информация

  • Если номер строки или столбца равен «0», функция возвращает данные для всей строки или столбца;
  • Если функция используется перед ссылкой на ячейку (например, A1), она возвращает ссылку на ячейку вместо значения (см примеры ниже);
  • Чаще всего ИНДЕКС (ИНДЕКС) используется совместно с функцией ПОИСКПОЗ (ПОИСКПОЗ);
  • В отличие от функции ВПР (VLOOKUP), функция ИНДЕКС (INDEX) может возвращать данные как справа, так и слева от искомого значения;
  • Функция используется в двух формах — массив данных и форма связи данных:

— Форма «Массив» используется, когда вы хотите найти значения на основе определенного количества строк и столбцов в таблице;

— Форма «Связи данных» используется при поиске значений в нескольких таблицах (используйте аргумент номер_области (номер_области) для выбора таблицы и только затем ориентируйте функцию по номерам строк и столбцов.

Пример работы функции ИНДЕКС в Excel от простого к сложному

Давайте сначала рассмотрим простейший пример приложения. У нас есть несколько городов. Пусть нашей первой задачей будет извлечение пятого города из нашей одномерной базы данных. Синтаксис нашей формулы будет следующим: ИНДЕКС(массив;номер строки). Начинаем писать формулу (ячейка B9). Первый аргумент — все города B3:C7, а второй — номер нужной нам строки, в которой хранится информация, которую мы хотим получить — пятый город:

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

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

Читайте также: Примеры функции ТРАНСП для переворачивания таблиц в Excel

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

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

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

Простая версия ИНДЕКС

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

ИНДЕКС в связи с MATCH

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

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

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

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

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

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

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

2D-поиск с ИНДЕКСОМ и ПОИСКПОЗОМ

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

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

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

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

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

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

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

INDEX выдает ссылку на весь столбец строки

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

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

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

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

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

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

Функция ИНДЕКС двумерный и многомерный массив

Теперь наш массив двумерный. Пусть сегодняшней задачей будет узнать количество туристов в Ливерпуле. В ячейку B19 пишем формулу. Также изменится синтаксис: ИНДЕКС(матрица; номер строки; номер столбца), добавим третий аргумент — номер столбца, где хранятся нужные нам данные:

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

На этот раз у нас три двумерных таблицы — три страны с разными городами и их посетителями. Нам нужно найти количество туристов в городе Ницца во Франции или, если переписать наш синтаксис, количество посетителей в третьей строке второго столбца второй таблицы. В первую очередь, как и прежде, нам нужно выбрать площадь. НО теперь у нас их три, а значит, мы выделяем все три, заключив эту часть синтаксиса в скобки ранее. Затем добавляем уже известные координаты искомой информации — строки и столбца, а также номер таблицы, содержащей искомую область:

И получаем готовую формулу с решением задачи:

Пример формулы комбинации функций ИНДЕКС и СУММ

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

  1. на 2018 год.
  2. для города Манчестер за весь период.

В этом нам поможет знакомая каждому пользователю простая функция СУММ. Он упакует результат поиска, выполненного INDEX, и выполнит операцию сложения найденных значений. Для выполнения первой задачи мы должны сначала выбрать диапазон только чисел (без столбца городов, так как мы суммируем числовые значения C43:E47). Итак, в месте, где мы писали номер строки, пишем 0. Благодаря этому ИНДЕКС не будет «искать» данные по строке, а просто перейдет к следующей операции. И следующая операция — пишем столбец 2 и получаем ответ на первый абзац:

Количество туристов на 2018 год в пяти городах Великобритании составляет 40 987 человек.

Аналогично этой схеме ищем ответ на второй пункт. Только теперь нам нужно считать данные из строки, поэтому вместо номера столбца ставим ноль:

И получаем ответ на второй пункт — всего за три года Манчестер посетило 2474 человека.

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

Формулировка функции может быть изменена в зависимости от постановки задачи. Предположим, нам нужно определить и показать, сколько туристов было вместе в 2017 году в первых трех городах списка — Кембридже, Манчестере и Лондоне. Затем снова используем функцию СУММ, но в то место, где обозначен конец диапазона значений, которые мы ищем, вставляем ИНДЕКС. Проще говоря, область суммирования выглядит так: (начало:конец). Началом будет, как обычно, первая ячейка массива, а конец мы заменим. Указываем количество городов, для которых будем считать людей (действие 1). Итак, когда мы пишем функцию ИНДЕКС, мы указываем, что первый аргумент — это числовой диапазон для всех пяти городов на 2017 год, а второй — это наше количество городов (ячейка D64):

Аналогично можно выполнить следующую задачу: найти общее количество посетителей Оксфорда за 2017-2018 годы. В данном случае делаем все так же, только выбираем горизонтальный диапазон C61:E61 :

В конце не обязательно обращаться к ячейке с написанным номером искомых значений, можно сразу написать цифру 3 в первом примере и цифру 2 во втором, все будет работать точно так же:

и

идентичны.

Комбинация нескольких функций может делать то же самое, что и одна функция, но менее требовательна к расположению данных с точки зрения размера или количества.

Как использовать функцию ИНДЕКС в Excel — примеры формул

Функция Excel ИНДЕКС сама по себе может не иметь особого практического применения, но в сочетании с другими функциями, такими как ПОИСКПОЗ или СЧЁТ, она может создавать очень эффективные формулы.

Кроме того, во всех наших формулах ИНДЕКС (кроме последней) мы будем использовать данные ниже.

1. Получение N-го элемента из списка

Это основное использование функции ИНДЕКС и ее простейшая формула. Чтобы получить конкретный элемент из списка, просто введите: =ИНДЕКС(диапазон; n), где диапазон — это диапазон ячеек или именованный диапазон, а n — позиция элемента, который вы хотите извлечь из него.

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

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

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

  • A5:E13 — это ссылка на область данных.
  • Line_number равен 4, потому что вы ищете четвертый элемент в списке,
  • Номер столбца равен 1, потому что мы хотим извлечь название планеты.

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

Кроме того, вы не можете отсортировать таблицу вручную, а можете сделать это с помощью формулы.

=ИНДЕКС(ЧЕРНЫЙ(A5:E13;3;-1);B1;1)

Функция SORT сортирует таблицу по третьему столбцу в порядке убывания. Ну, тогда ИНДЕКС сделает все остальное.

2. Получение всех значений в строке или столбце

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

Такие формулы ИНДЕКС вряд ли будут использоваться поодиночке, потому что Excel не может уместить диапазон значений, возвращаемых формулой, в одну ячейку, и вы получите ошибку #ЗНАЧ!. Но если вы используете ИНДЕКС в сочетании с другими функциями, такими как СУММ или СРЗНАЧ, вы получите отличные результаты.

Например, вы можете использовать следующую формулу для расчета средней температуры планет в Солнечной системе:

=СРЗНАЧ(ИНДЕКС(A4:E12;;4))

В приведенной выше формуле аргументом является столбец номер 4, потому что температура находится в четвертом столбце нашей таблицы. Параметр line_number опущен.

Таким же образом можно найти минимальную и максимальную температуры:

=МИН(ИНДЕКС(A4:E12;;4))

=МАКС(ИНДЕКС(A4:E12;;4))

И вычисляем общую массу планет (масса вторая колонка в таблице):

=СУММ(ИНДЕКС(A4:E12;;2))

С практической точки зрения функция ИНДЕКС в приведенной выше формуле избыточна. Вы можете просто ввести =AVERAGE(D4:D12) и получить те же результаты.

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

Например, формула =СУММ(ИНДЕКС(A4:E12;;C1)) позволит вам вычислить сумму столбца, номер которого вы вводите в ячейку C1.

3. Использование ИНДЕКС с другими функциями (СУММ, СРЗНАЧ, МАКС, МИН)

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

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

Допустим, у вас есть формула =СРЗНАЧ(D4:D12), которая возвращает среднее значение значений в ячейках D4:D12. Вместо того, чтобы вводить диапазон непосредственно в формулу, вы можете заменить либо D4, либо D12, либо обе функции ИНДЕКС, например:

= СРЕДНЕЕ (D4: ИНДЕКС (D1: D20; 12))

Обе приведенные выше формулы дадут одинаковый результат, поскольку функция ИНДЕКС также возвращает ссылку на ячейку B12 (номер строки — 12, номер столбца опущен). Разница в том, что диапазон формулы СРЗНАЧ/ИНДЕКС является динамическим, и когда вы изменяете аргумент номера строки на ИНДЕКС, диапазон, обрабатываемый функцией СРЗНАЧ, также изменится, и формула вернет другой результат.

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

Пример 1. Вычислить среднее значение первых N элементов в списке

Допустим, вы хотите узнать средний диаметр N крупнейших планет Солнечной системы.

Количество позиций, которое мы будем использовать для расчета, указано в ячейке B1.

Затем вы сортируете таблицу по столбцу диаметра от наибольшего к наименьшему и используете следующую формулу для расчета среднего значения:

=СРЕДНЕЕ(C5:ИНДЕКС(C5:C13;B1))

Как видите, мы можем выбрать количество значений для расчета среднего без корректировки самой формулы

Пример 2. Сумма элементов между указанными двумя элементами

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

Например, в следующей формуле функция СУММ используется для возврата суммы значений в столбце Масса между двумя элементами в ячейках B1 и E1:

=СУММ(ИНДЕКС(B5:B13;B1):ИНДЕКС(B5:B13;E1))

4. Формула ИНДЕКС для создания динамических диапазонов и раскрывающихся списков

Как это часто бывает, когда вы начинаете упорядочивать данные на листе, вы можете не знать, сколько записей у вас получится. Это не относится к нашей планетарной таблице, которая кажется полной, но кто знает…

В любом случае, если у вас есть различное количество элементов в заданном столбце, скажем, от A1 до An, вы можете создать динамически именованный диапазон, включающий все ячейки данных. Однако вы хотите, чтобы область автоматически корректировалась при добавлении новых элементов или удалении некоторых из существующих. Например, если у вас сейчас 10 элементов, ваш именованный диапазон будет A1:A10. Если вы добавите новую запись, именованный диапазон автоматически расширится до A1:A11, а если вы передумаете и удалите эти вновь добавленные данные, диапазон автоматически вернется к A1:A10.

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

Одним из способов определения динамического диапазона является использование функции Excel OFFSET :

=СМЕЩЕНИЕ($A$2; 0; 0; СЧЁТ($A1:$A100)-1; 1)

Другим возможным решением является использование ИНДЕКС Excel вместе с COUNTA:

=$A$2:ИНДЕКС($A2:$A100, СЧЁТ($A2:$A100))

В обеих формулах A2 — это ячейка, содержащая первый элемент в списке. Динамический диапазон, генерируемый обеими формулами, будет идентичен.

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

В этом примере в столбце A 9 непустых ячеек, поэтому COUNT возвращает 9. Таким образом, INDEX возвращает $A$9, то есть последнюю использованную ячейку в столбце A (обычно INDEX возвращает значение, но в этой формуле оператор ссылки (:) заставляя его возвращать ссылку). А поскольку $A$1 является нашей отправной точкой, конечным результатом формулы будет диапазон $A$1:$A$9.

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

Примечание. Самый простой способ создать динамически обновляемый раскрывающийся список — создать именованный список на основе электронной таблицы Excel. В этом случае вам не нужны сложные формулы, так как таблицы Excel сами по себе являются динамическими диапазонами.

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

Функция ИНДЕКС для ссылок

Теперь давайте посмотрим, как можно работать с несколькими таблицами с помощью функции ИНДЕКС. В данном случае нам нужен список аргументов для ссылок с полем «Area_Number”.

Допустим, у нас есть 4 таблицы. Каждый из них предоставляет информацию о продажах за определенный период времени (1-й, 2-й, 3-й и 4-й квартал).

Таблицы в Excel

Нам нужно узнать продажи четвертой позиции («Системный блок») за второй квартал в штуках.

  1. Встаем в ячейку, где планируем вывести конечный результат и нажимаем кнопку «Вставить функцию» (fx).Вставка функции в ячейку электронной таблицы Excel
  2. Выберите функцию ИНДЕКС и нажмите OK.Выбор функции ИНДЕКС в Excel
  3. В дополнительном окне останавливаемся на втором варианте (для ознакомления) и нажимаем кнопку ОК.Выбор списка аргументов для функции ИНДЕКС в Excel
  4. Появится окно с аргументами функции для заполнения:
    • Поле «Ссылка» заполняется так же, как и аргумент «Массив» в примере выше (вручную или с помощью метода select в самой таблице). Разница лишь в том, что в этом случае нам нужно указать 4 диапазона ячеек вместо одного за раз и перечислить их через точку с запятой. Они указывают первый регион, вставляют символ «;», затем указывают второй регион и т д. После того, как все сделано, ставим открывающую и закрывающую скобки в начале и в конце ссылки соответственно. Заполнение аргументов функции ИНДЕКС в Excel
    • в значение аргумента «Номер_строки» пишем цифру 4, так как нас интересуют данные на четвертой позиции.
    • в поле «Колонка_номер» пишем цифру 3, так как нам не нужны продажи в единицах, а это третья колонка в выбранных диапазонах.
    • в поле аргумента «Номер области» указать число 2, так как необходимо отобразить данные за второй квартал, который соответствует второй выделенной области в аргументе «Связь”.
    • когда все будет готово, нажмите ОК.Заполнение аргументов функции ИНДЕКС в Excel
  5. В выбранной ячейке с функцией отобразится требуемый результат согласно заданным условиям в аргументах.Результат функции ИНДЕКС в электронной таблице Excel
Оцените статью
Блог о Microsoft Word