Функция ИНДЕКС в Excel: примеры, использование с ПОИСКПОЗ

Описание функции ИНДЕКС

ИНДЕКС относится к категории операторов «Ссылки и массивы» и имеет два разных набора аргументов:

1. Для массивов

Формула функции в этом случае выглядит так:

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

вы можете ввести один из двух аргументов: «Номер_строки» или «Номер_столбца». Все зависит от типа массива, с которым вы имеете дело. Например, если массив занимает только одну строку, аргумент «Row_number» не имеет значения, и заполняется только «Column_number». Для массива, занимающего только один столбец, все то же самое. Здесь есть нюанс: необходимо указывать номера строк и столбцов, ориентируясь не на общие обозначения на панелях координат программы, а на порядковые номера внутри указанного массива.

2. Для ссылок

Формула функции выглядит так:

= ИНДЕКС (Ссылка; Номер строки; Номер столбца; Номер области).

В этом случае, как и в формуле массива, может быть заполнен только один из аргументов: «Row_num» или «Column_number». Последний аргумент, Region_Number, заполняется, когда вам нужно работать с несколькими диапазонами, в противном случае это необязательно.

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

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

Допустим, у нас есть таблица с названиями товаров, их ценой, количеством и общей суммой.

Наша задача — вывести название пятой позиции в списке в заранее выбранной ячейке.

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

Как мы отмечали ранее, один из аргументов функции («Column_num» или «Row_number») может оставаться пустым, если выделенный массив является одномерным, т.е они занимают строку или столбец. На практике это выглядит так.

1. В окне аргументов функции в поле «Массив» выберите только ячейки первого столбца. Обозначим номер строки — 5 и номер столбца, соответственно, остается пустым, так как в выбранном нами массиве только один.
   
2. Нажав ОК, мы получим тот же результат в ячейке таблицы, хотя аргументы функции и ее формулы отличаются от исходной версии.
   

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

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

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

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

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

ВПР по двум условиям при помощи формулы массива.

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

Для этого в верхней части нашего списка обратите внимание на критерии поиска: дата и магазин. В ячейке B3 мы покажем сумму дохода.

Формула в B3 выглядит следующим образом:

{= ВПР (B1, SE (B6: B19 = B2, A6: C19, «»), 3,0)}

Обратите внимание на фигурные скобки, которые указывают, что это формула массива. То есть наша функция ВПР работает не с одиночными значениями, а единожды с массивами данных.

Разберем процесс подробно.

Ищем дату, записанную в ячейку B1. Но мы будем искать его только не в нашем исходном диапазоне данных, а в немного измененном. Для этого воспользуемся условием

ЕСЛИ (A6: B19 = B2; A6: C19;»»)

То есть, если имя магазина соответствует критерию в ячейке B2, мы сохраняем исходные значения из нашего диапазона. А если нет — заменяем их пробелами. И так по каждой строчке.

В результате мы получаем следующий виртуальный массив данных на основе нашей исходной таблицы:

Как видите, строки, в которых раньше было «Магазин 1», были заменены пустыми. А теперь будем искать нужную дату только в информации «Магазин 2». И извлеките значения дохода из третьего столбца.

Функция ВПР может справиться с этим типом работы.

Этот ход стал возможным благодаря применению формулы массива. Поэтому обратите особое внимание: вовсе не обязательно писать скобки в формуле от руки! В ячейке B3 напишите формулу

= ВПР (B1; SE (B6: B19 = B2; A6: C19; «»); 3,0)

А затем нажмите комбинацию клавиш CTRL + SHIFT + ENTER. В то же время Excel поймет, что вы хотите вставить формулу массива и заменить сами скобки.

Таким образом, функция ВПР выполняет поиск по двум столбцам в 2 этапа. Во-первых, мы удаляем диапазон данных из строк, которые не соответствуют одному из условий, с помощью функции ЕСЛИ и формулы массива. А затем, используя эту правильную информацию, мы выполняем обычный поиск только с одним секундным критерием, используя ВПР.

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

Вот как это будет выглядеть.