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

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

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

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

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

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

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

Как можно использовать функцию 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)

Чтобы сделать эту формулу более гибкой, Вы можете использовать функцию 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))

Пример 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)

С результатом функции 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))

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

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

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

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

Пример 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))