30 функций Excel за 30 дней: ИНДЕКС (INDEX)

Вчера в марафоне 30 функций Excel за 30 дней мы искали текстовые строки, используя функцию FIND (НАЙТИ), и выяснили, что она чувствительна к регистру, в отличие от функции SEARCH (ПОИСК).

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

  • вместе с EXACT (СОВПАД) – для поиска названия по точному совпадению в списке;
  • вместе с AREAS (ОБЛАСТИ) – для поиска последней области в именованном диапазоне;
  • вместе с COLUMNS (ЧИСЛСТОЛБ) – для подсчета суммы последнего столбца в именованном диапазоне;
  • вместе с MATCH (ПОИСКПОЗ) – для поиска имени участника, угадавшего ближайшее значение.

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

Функция 24: INDEX (ИНДЕКС)

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

Функция ИНДЕКС в Excel

Как можно использовать функцию INDEX (ИНДЕКС)?

Функция INDEX (ИНДЕКС) может возвратить значение или ссылку на значение. Вы можете использовать ее, чтобы:

  • Найти сумму продаж для выбранного месяца.
  • Получить ссылку на выбранную строку, столбец или область.
  • Создать динамический диапазон, основанный на подсчете.
  • Отсортировать столбец с текстовыми данными в алфавитном порядке.

Синтаксис INDEX (ИНДЕКС)

Функция INDEX (ИНДЕКС) имеет две синтаксические формы – массива и ссылочная. Форма массива возвращает значение, а ссылочная – ссылку.

Форма массива имеет вот такой синтаксис:

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

  • array (массив) – массив констант или диапазон ячеек.
  • Если аргумент array (массив) имеет только 1 строку или 1 столбец, то соответствующий аргумент с номером строки/столбца не обязателен.
  • Если аргумент array (массив) содержит более 1-й строки и 1-го столбца:
    • и задано значение только аргумента row_num (номер_строки), то будет возвращен массив всех значений этой строки.
    • если же задано значение только аргумента column_num (номер_столбца), то массив всех значений этого столбца.
  • Если аргумент row_num (номер_строки) не указан, то требуется указать column_num (номер_столбца).
  • Если аргумент column_num (номер_столбца) не указан, то требуется указать row_num (номер_строки).
  • Если указаны оба аргумента, то функция возвращает значение ячейки на пересечении указанных строки и столбца.
  • Если в качестве аргумента row_num (номер_строки) или column_num (номер_столбца) указать ноль, то функция возвратит массив значений всего столбца или всей строки соответственно.

Ссылочная форма имеет вот такой синтаксис:

INDEX(reference,row_num,column_num,area_num)
ИНДЕКС(ссылка;номер_строки;номер_столбца;номер_области)

  • reference (ссылка) может ссылаться на один или несколько диапазонов ячеек, включая несмежные диапазоны, которые должны быть заключены в круглые скобки.
  • Если каждая область в reference (ссылка) имеет только 1 строку или 1 столбец, то соответствующий аргумент с номером строки/столбца не обязателен.
  • area_num (номер_области) указывает номер области в аргументе reference (ссылка), из которой нужно вернуть результат.
  • Если аргумент area_num (номер_области) не указан, то будет выбрана 1 область.
  • Если аргумент row_num (номер_строки) или column_num (номер_столбца) равен нулю, то функция возвратит ссылку на весь столбец или всю строку соответственно.
  • Результат – это ссылка, которая может быть использована другими функциями.

Ловушки INDEX (ИНДЕКС)

Если row_num (номер_строки) и column_num (номер_столбца) указывают на ячейку, не принадлежащую заданному массиву или ссылке, функция INDEX (ИНДЕКС) сообщит об ошибке #REF! (#ССЫЛКА!).

Пример 1: Находим сумму продаж для выбранного месяца

Введите номер строки, и функция INDEX (ИНДЕКС) возвратит сумму продаж из этой строки. Здесь указан номер месяца 4, поэтому результатом будет сумма продаж за апрель (Apr).

=INDEX($C$2:$C$8,F2)
=ИНДЕКС($C$2:$C$8;F2)

Функция ИНДЕКС в Excel

Чтобы сделать эту формулу более гибкой, Вы можете использовать функцию MATCH (ПОИСКПОЗ) для определения номера строки по месяцу, который может быть выбран из выпадающего списка.

=INDEX($C$2:$C$8,MATCH($F$2,$D$2:$D$8,0))
=ИНДЕКС($C$2:$C$8;ПОИСКПОЗ($F$2;$D$2:$D$8;0))

Функция ИНДЕКС в Excel

Пример 2: Получаем ссылку на определенную строку, столбец или область

В этом примере именованный диапазон MonthAmts состоит из 3-х несмежных диапазонов. Диапазон MonthAmts имеет 3 области – по одной для каждого месяца – и в каждой области по 4 строки и 2 столбца. Вот формула для имени MonthAmts:

='Ex02'!$B$3:$C$6,'Ex02'!$E$3:$F$6,'Ex02'!$H$3:$I$6
='Ex02'!$B$3:$C$6;'Ex02'!$E$3:$F$6;'Ex02'!$H$3:$I$6

При помощи функции INDEX (ИНДЕКС) Вы можете возвратить стоимость (Cost) или сумму выручки (Rev) для определённого региона и месяца.

=INDEX(MonthAmts,B10,C10,D10)
=ИНДЕКС(MonthAmts;B10;C10;D10)

Функция ИНДЕКС в Excel

С результатом функции INDEX (ИНДЕКС) можно произвести операцию умножения, как при вычислении налога (Tax) в ячейке F10:

=0.05*INDEX(MonthAmts,B10,C10,D10)
=0,05*ИНДЕКС(MonthAmts;B10;C10;D10)

или она может возвратить ссылку для функции CELL (ЯЧЕЙКА), чтобы показать адрес полученного результата в ячейке G10.

=CELL("address",INDEX(MonthAmts,B10,C10,D10))
=ЯЧЕЙКА("адрес";ИНДЕКС(MonthAmts;B10;C10;D10))

Функция ИНДЕКС в Excel

Пример 3: Создаём динамический диапазон, основанный на подсчете

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

='Ex03'!$C$1:INDEX('Ex03'!$C:$C,COUNTA('Ex03'!$C:$C))
='Ex03'!$C$1:ИНДЕКС('Ex03'!$C:$C;СЧЁТЗ('Ex03'!$C:$C))

Функция ИНДЕКС в Excel

Если в список в столбце C добавить ещё один месяц, то он автоматически появится в выпадающем списке в ячейке F2, который использует MonthList, как источник данных.

Функция ИНДЕКС в Excel

Пример 4: Сортируем столбец с текстовыми данными в алфавитном порядке

В финальном примере функция INDEX (ИНДЕКС) работает в сочетании с несколькими другими функциями, чтобы возвратить список месяцев, отсортированный в алфавитном порядке. Функция COUNTIF (СЧЁТЕСЛИ) подсчитывает, как много месяцев стоит перед каждым конкретным месяцем в списке (создается массив). SMALL (НАИМЕНЬШИЙ) возвращает n-ое наименьшее значение в созданном массиве, а MATCH (ПОИСКПОЗ) на базе этого значения возвращает номер строки нужного месяца.

Эта формула должна быть введена в ячейку E4, как формула массива, нажатием Ctrl+Shift+Enter. А затем скопирована с остальные ячейки диапазона E4:E9.

=INDEX($C$4:$C$9,MATCH(SMALL(COUNTIF($C$4:$C$9,"<"&$C$4:$C$9),
ROW(E4)-ROW(E$3)),COUNTIF($C$4:$C$9,"<"&$C$4:$C$9),0))

=ИНДЕКС($C$4:$C$9;ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ($C$4:$C$9;"<"&$C$4:$C$9);
СТРОКА(E4)-СТРОКА(E$3));СЧЁТЕСЛИ($C$4:$C$9;"<"&$C$4:$C$9);0))

Функция ИНДЕКС в Excel

ОфисГуру
Adblock
detector