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

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

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

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

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

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

Синтаксис

Существует четыре вариации синтаксиса этой функции. Две русские версии:

1. =ИНДЕКС(массив; номер_строки; [номер_столбца]).
2. =ИНДЕКС(ссылка; номер_строки; [номер_столбца]; [номер_области]).

Две английские версии:

1. =INDEX (array, row_num, [col_num]).
2. =INDEX (array, row_num, [col_num], [area_num]).

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

Разберемся более подробно, что означает каждый из приведенных ниже аргументов:

• array – обозначает диапазон секторов или массив данных, по которому будет осуществляться поиск;
• row_num – обозначает номер строчки, где располагаются необходимые значения;
• [col_num] – обозначает номер столбика, где располагаются необходимые значения. Этот параметр является необязательным. В случае, когда в функции ИНДЕКС не описан аргумент номера строчки, этот параметр нужно указывать;
• [area_num] – используется в тех случаях, когда массив имеет некоторое количество диапазонов. Параметр является необязательным и применяется для осуществления выбора абсолютно всех диапазонов.

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

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

• Когда номер столбика или строчки равен нулю, функция осуществит возврат данных всего столбика или строчки.
• В случае применения функции ИНДЕКС перед ссылкой на ячейку произойдет возврат ссылки на ячейку вместо значения. Более подробно об этом поговорим в примерах, расположенных ниже.
• Обычно функция ИНДЕКС применяется вместе с функцией ПОИСКПОЗ.
• Функция ИНДЕКС отличается от функции ВПР тем, что она осуществляет возврат данных как слева от нужного показателя, так и справа.
• ИНДЕКС можно применять в 2-х различных видах: «Ссылки на данные» и «Массив данных».
• «Массив» применяется в тех случаях, когда необходимо отыскать показатели, основанные на определенных номерах столбиков и строчек табличной информации.
• «Ссылки на данные» применяются в тех случаях, когда необходимо отыскать показатели в некотором количестве таблиц, чтобы выбрать табличку, а затем помочь функции осуществить поиск по номеру столбца и строки.

Как работает функция ИНДЕКС в Excel?

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

Функция ИНДЕКС в Excel пошаговая инструкция

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

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

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

Цель: в выделенном секторе показать название пятой в списке позиции. Пошаговая инструкция выглядит так:

1. Кликаем на сектор, в который желаем в дальнейшем вывести результат манипуляций. Щелкаем кнопку «Вставить функцию», располагающуюся рядом со строкой для ввода формул.

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

3. На экране появилось маленькое окошко, предлагающее выбрать набор аргументов. Выбираем 1-й предложенный вариант и нажимаем «ОК».

4. В следующем окошке необходимо указать аргументы. В поле «Массив» вводим диапазон, в рамках которого будет осуществляться работа функции. Координаты можно вписать самостоятельно или же путем выделения необходимой области на рабочем листе при помощи зажатой ЛКМ.

5. В поле «Номер_строки» вбиваем число 5, так как этого требует поставленная перед нами задача.
6. В поле «Номер_столбца» вбиваем цифру 1, так как названия значений располагаются в 1-м столбике массива.
7. После проведения всех настроек жмем «ОК».

8. В выбранном секторе появился итоговый результат.

Обратите внимание! Один из аргументов можно не заполнять в случае, если массив является одномерным.

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

1. В строке «Массив» необходимо выделить только ячейки первого столбика. Вводим номер строки – 5, а номер столбика не заполняем, потому что массив является одномерным.

2. Щелкаем на «ОК». В итоге, в выделенном секторе получаем аналогичный результат.

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

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

Цель: выявить количество продаж в 4-й позиции за 2-й квартал в штуках. Пошаговое руководство выглядит следующим образом:

1. Кликаем на сектор, в который желаем в дальнейшем вывести результат манипуляций. Щелкаем кнопку «Вставить функцию», располагающуюся рядом со строчкой для ввода формул.

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

3. На экране появилось маленькое окошко, предлагающее выбрать набор аргументов. Выбираем второй предложенный вариант и нажимаем «ОК».

4. В следующем окошке необходимо указать аргументы. В поле «Ссылка» вводим те же значения, которые вводили в поле «Массив» в предыдущем примере. Главное отличие – указываем не один диапазон, а сразу четыре, разделяя их знаком «;». После описания необходимо поставить «(» в начале и «)» в конце.

5. В поле «Номер_строки» вводим значение 4, согласно условию нашей задачи.
6. В строчку «Номер_столбца» вводим значение 3, потому что нам необходимо выяснить количество продаж в штуках.
7. В строку «Номер области» вводим значение 2, потому как по условию задачи нам необходимо узнать информацию по второму кварталу.

8. После проведения всех манипуляций щелкаем на кнопку «ОК». Готово, выбранном секторе появился необходимый нам ответ.

Использование с оператором СУММ

Функцию ИНДЕКС часто применяют совместно с оператором СУММ. Общий вид оператора: =СУММ(Адрес_массива). Применив СУММ, к рассматриваемой нами табличке, мы сможем получить итоговую сумму. Формула для подсчета суммы будет выглядеть следующим образом: =СУММ(D2:D9).

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

1. Для первого аргумента оператора СУММ выставляем координаты сектора, являющегося точкой начала суммирующего диапазона.
2. 2-й аргумент задается при помощи функции ИНДЕКС. Кликаем на строчку для ввода формул и вбиваем следующее значение: =СУММ(D2:ИНДЕКС(D2:D9;8)). Число 8 говорит о том, что мы выставляем ограничение для выбранного диапазона между секторами D2 и D9.

3. Жмем на клавишу «Enter», чтобы вывести конечный результат в выделенном изначально секторе.

Сочетание с функцией ПОИСКПОЗ

Переходим к разбору более сложных задач. Ниже будет рассмотрен пример использования функции ИНДЕКС с оператором ПОИСКПОЗ. ПОИСКПОЗ позволяет осуществить возврат указанного показателя в выделенном диапазоне секторов. Общий вид формулы: =ПОИСКПОЗ(Искомое_значение,Просматриваемый_массив,[Тип_сопоставления]). Разберем каждый показатель функции более подробно:

• Искомое значение. Этот аргумент указывает значение, которое нужно отыскать в выделенной области.
• Просматриваемый массив. Область секторов для поиска искомого показателя.
• Тип сопоставления. Аргумент не является обязательным и применяется для более точного поиска.

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

1. Первоначально заполняем ячейку F1.

2. Кликаем на сектор, в который желаем в дальнейшем вывести результат манипуляций. Щелкаем кнопку «Вставить функцию», располагающуюся рядом со строкой для ввода формул.

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

4. На экране появилось маленькое окошко, предлагающее выбрать набор аргументов. Выбираем первый предложенный вариант и нажимаем «ОК».

5. В строку «Массив» вводим сектор столбика, в котором располагается стоимость позиций.

6. В строчку «Номер_строки» добавляем функцию ПОИСКПОЗ. Для реализации данного действия нажимаем на маленькую стрелочку, находящуюся около элемента «Вставить функцию». Раскрылся список, где нужно выбрать «Другие функции». Появилось новое окошко «Мастер функций», в котором нажимаем «Ссылки и массивы», а затем выбираем ПОИСКПОЗ. После проведения всех манипуляций жмем «ОК».

7. В поле «Искомое_значение» вводим координаты ячейки, по наполнению которой будет осуществляться поиск в главном массиве. В поле «Просматриваемый_массив» вбиваем диапазон для поиска искомого показателя. Вводим 0 в поле «Тип_сопоставления».

8. В строке для ввода формул необходимо кликнуть на «ИНДЕКС».

9. На экране в очередной раз отобразился перечень аргументов. Замечаем, что показатели автоматом заполнились необходимыми данными. Ничего не трогаем и кликаем на «ОК». Стоит заметить, что поле «Номер_строки» можно самостоятельно заполнить, используя синтаксис оператора ПОИСКПОЗ.

10. В итоге мы получили в выделенном секторе необходимый результат – стоимость позиции. Различные изменения стоимости в главной таблице будут отражаться в этой ячейке. Это же правило работает и с изменением наименования во вспомогательной табличке.

Обработка нескольких таблиц

Рассмотри процесс обработки нескольких таблиц. К примеру, у нас есть 3 таблички. В них отображается зарплата сотрудников по месяцам. Цель: выявить зарплату второго сотрудника за 3-й месяц. Пошаговое руководство выглядит следующим образом:

1. Кликаем на ячейку, в которую желаем в дальнейшем вывести результат манипуляций. Щелкаем кнопку «Вставить функцию», располагающуюся рядом со строкой для ввода формул.
2. На дисплее открылось окошко под названием «Вставка функции». Раскрываем список, находящийся рядом с надписью «Категория:», и нажимаем «Ссылки и массивы». Далее в окошке с перечнем функций выбираем «ИНДЕКС». После проведения всех действий щелкаем на кнопку «ОК».
3. На экране появилось маленькое окошко, предлагающее выбрать набор аргументов. Выбираем 2-й предложенный вариант и нажимаем «ОК».
4. В строку «Ссылка» вводим координаты каждого диапазона. В строчку «Номер строки» вбиваем число 2, потому как мы осуществляем поиск 2-й фамилии в перечне. В строчку «Номер столбца» вводим число 3. В строку «Номер области» тоже вбиваем число 3. После проведения всех манипуляций кликаем на «ОК».

5. В выбранный заранее сектор вывелись необходимые результаты.

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

Дополнительно разберем еще один пример использования. Например, у нас есть следующая табличная информация:

Чтобы узнать результат Андрея по дисциплине «Физика», необходимо применить следующую формулу: =ИНДЕКС($B$3:$E$9;3;2). Здесь мы произвели определение показателей нужного диапазона: $B$3:$E$9. Цифра 3 означает номер строчки, где располагается результат Андрея. Цифра 2 означает номер столбца, где располагается дисциплина «Физика».

Ошибки

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

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

Заключение

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