Извлечение значений с помощью INDEX

Функция INDEX отвечает на простой вопрос: «Какое значение находится в определённой позиции диапазона?» Вы указываете ей область ячеек и номер строки, а она возвращает то, что находится в этой позиции.

Представьте диапазон как пронумерованный список. INDEX — это всё равно что сказать: «Дайте мне элемент под номером 3». Пока функция ничего не ищет по имени, а просто получает значение по позиции.

Поэтому INDEX идеально подходит для извлечения значений, когда Вы уже знаете, где они находятся.

Самая простая форма INDEX принимает два элемента:

• массив — диапазон ячеек для поиска
• номер_строки — номер строки этого диапазона, которую нужно вернуть

Таким образом, =INDEX(B2:B6, 3) означает: "Из диапазона B2:B6 вернуть значение на 3-й позиции". Если в B2:B6 находятся имена, формула возвращает третье имя.

Номер строки отсчитывается внутри диапазона, а не от верхнего края листа.

Поначалу это может сбивать с толку. Если диапазон начинается со строки 2, ячейка в строке 2 находится на позиции 1, а не 2.

Представьте, что в B2:B6 находятся значения: Яблоко, Банан, Вишня, Финик, Инжир. На позиции 1 находится Яблоко (в ячейке B2), на позиции 2 — Банан, а на позиции 3 — Вишня.

Поэтому =INDEX(B2:B6, 3) возвращает Вишня. Позиция всегда отсчитывается относительно начала указанного диапазона.

Предположим, в столбце A перечислены имена сотрудников, и Вам нужно получить четвёртое имя. Ваши данные находятся в A2:A10.

Вы вводите =INDEX(A2:A10, 4). INDEX отсчитывает четыре позиции вниз от A2 и возвращает имя из A5.

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

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

=INDEX(array, row_num, column_num)

Теперь Вы указываете и строку, и столбец внутри блока. Например, =INDEX(A2:C10, 4, 2) возвращает значение из 4-й строки и 2-го столбца этого блока.

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

Представьте небольшую таблицу в A2:C5, где в столбце A находится регион, в столбце B — товар, а в столбце C — объём продаж. Вам нужно получить значение продаж из 3-й строки данных.

Продажи находятся в 3-м столбце, а 3-я строка данных имеет позицию 3. Поэтому Вы вводите =INDEX(A2:C5, 3, 3).

INDEX переходит к строке 3 и столбцу 3 этого блока и возвращает единственное значение продаж. Одна формула — точное пересечение.

Если задать одно из чисел равным 0 (или во многих случаях не указывать его), INDEX возвращает всю строку или столбец, а не одну ячейку.

=INDEX(A2:C5, 0, 2) возвращает весь 2-й столбец блока. В современных электронных таблицах результат заполняет несколько ячеек.

Это удобно, когда нужно передать другой функции целый столбец, а не одно значение.

Если Ваш диапазон состоит только из одного столбца, аргумент столбца можно не указывать. INDEX предполагает, что это столбец 1.

Например, для цен в C2:C20 формула =INDEX(C2:C20, 7) просто возвращает 7-ю цену. Номер столбца не нужен.

А для одной строки значений, например E1:J1, формула =INDEX(E1:J1, 1, 4) возвращает 4-е значение в этой строке. INDEX подстраивается под форму Ваших данных.

Возможно, Вы задаётесь вопросом, почему бы просто не ввести =A5. Преимущество INDEX в том, что позиция может быть результатом вычисления или ссылкой на ячейку, а не фиксированным числом.

Например, =INDEX(A2:A100, D1) возвращает строку, номер которой хранится в D1. Измените D1 на 5 — и INDEX вернёт 5-й элемент; измените его на 20 — и функция вернёт 20-й.

Именно это превращает INDEX из любопытной функции в динамический инструмент поиска.

Предположим, в A2:A11 находится таблица лидеров, а в F1 — раскрывающийся список, где пользователь выбирает место от 1 до 10.

Вы вводите =INDEX(A2:A11, F1). Когда кто-либо выбирает 1 в F1, он видит игрока, занявшего первое место; при выборе 3 отображается игрок, занявший третье место.

Формула не меняется — меняется только входное значение. Это основа всех гибких формул поиска, с которыми Вы познакомитесь далее.

Если запросить в INDEX несуществующую позицию, появится ошибка #REF!. Для диапазона из 5 элементов запрос позиции 6 завершится ошибкой.

Например, =INDEX(A2:A6, 9) возвращает #REF!, поскольку в диапазоне из 5 строк нет 9-го элемента.

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

Проверьте, насколько хорошо Вы поняли, как INDEX отсчитывает позиции.

Вы узнали, что INDEX получает значение по его позиции внутри диапазона:

• =INDEX(array, row_num) — для одного столбца или строки
• =INDEX(array, row_num, column_num) — для двумерного блока
• Позиции отсчитываются от начала диапазона, поэтому первая ячейка имеет позицию 1
• Позиция может быть числом, ссылкой на ячейку или результатом вычисления, благодаря чему INDEX работает динамически

Далее Вы познакомитесь с MATCH — функцией, которая автоматически находит эти позиции.