Функция "ИНДЕКС" в Excel: описание, применение и примеры

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

Описание

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

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

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

Функция "ИНДЕКС" в Excel иногда выдает значение «#ССЫЛ!». Чаще всего это происходит, если ячейка, расположенная на пересечении указанных строки и столбца, находится вне указанного диапазона.

Примеры применения

Рассмотрим несколько случаев использования функции "ИНДЕКС" на практике.

Предположим, имеется массив, состоящий из 4 столбцов и 4 строк (см. таблицу). Если ввести в одну из ячеек таблицы расположенное вне диапазона А1:Е5 выражение «=ИНДЕКС (В2:Е5, 2, 3)» (без кавычек) и нажать на «Ввод», то в ответ будет выдано значение «бегония».

Если требуется узнать, сколько учащихся Группы 2 получили оценку «неудовлетворительно», то в соответствующую ячейку следует ввести выражение: ИНДЕКС (С2:С5, 1).

Функция "ПОИСКПОЗ" в Excel

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

Рассмотрим случай, когда массив состоит из единственной строки.

Диапазон значений в этом случае В3:В6.

Выбираем ячейку в другой строке, например D1. Вводим в нее название фрукта, позицию которого хотим найти, в данном случае «апельсины». В ячейке (Е1), куда хотим записать номер соответствующей строки, вводим «= ПОИСКПОЗ(D1;В3:В6;0)» (см. таблицу). В результате там появляется число 3. Именно такой номер в диапазоне В3:В6 у выражения «апельсины».

Последний 0 означает, что требуется найти точное совпадение со значением D1.

Как найти все текстовые значения, удовлетворяющие некому критерию

В виде, представленном выше, функция "ПОИСКПОЗ" возвращает только одно значение (самое первое, т. е. верхнее). Но что делать, если в списке есть повторения. В таком случае помогают формулы массива. Для их использования следует выделить весь диапазон данных и использовать сочетание клавиш «Ctrl+Shift+Enter». Однако ее рассмотрение не является предметом данной статьи.

Функция "ИНДЕКС" и "ПОИСКПОЗ" в Excel: примеры

Представьте, что вам нужно выбрать из достаточно большого массива только определенные данные. Рассмотрим для простоты случай с небольшим числом элементов. Например, у вас есть отчет об успеваемости нескольких групп студентов и их оценки. Предположим, вы хотите, чтобы в ячейке H2 появилось число студентов, получивших оценку «неуд».

Для этого лучше всего совместно использовать обе функции. Чтобы узнать, что необходимо ввести в H2, сначала рассмотрим самое простое выражение, которое можно использовать для этой цели. В частности, искомое значение можно получить, если записать в эту ячейку «=ИНДЕКС(А2:Е5;1;2)». Здесь мы использовали вариант из предыдущих примеров, когда номер строки и столбца высчитывался вручную. Однако наша цель - автоматизировать этот процесс. Для этого следует вместо двойки и единицы, которые указывают на искомые строку и столбец, в массиве записать соответствующие функции "ПОИСКПОЗ", выдающие эти номера. Обратите внимание, что мы ищем выражение «уд», расположенное в ячейке G2 и «гр. 2» из H2. Кроме того, нам нужны точные совпадения, поэтому в качестве последнего, третьего, аргумента в обоих случаях указывается 0.

Тогда вместо 1 в формуле ИНДЕКС(А2:Е5;1;2) следует записать: ПОИСКПОЗ(G2;A2:A5;0), а вместо 2 — ПОИСКПОЗ(H2; А2:Е2;0).

После подстановки имеем: ИНДЕКС(А2:E5; ПОИСКПОЗ(G2;A2:A5;0); ПОИСКПОЗ(H2; А2:Е2;0)). В результате, нажав «Ввод», имеем в этой ячейке значение «10».

Как распространить действие полученной формулы на некий диапазон

Как известно, функция "ИНДЕКС" в Excel может быть «вытянута» на некий диапазон. В примере, рассматриваемом выше, это все 4 ячейки из H2:J3. В связи с этим необходимо выяснить, как сделать так, чтобы, «вытянув» эту формулы вправо и вниз, получить правильные значения.

Главная сложность заключается в том, что массив А2:Е5 имеет относительный адрес. Чтобы исправить это, следует превратить его в абсолютный. Для этого массив записывается в виде $А$2:$Е$5. То же следует сделать и для обеих встроенных функций, т. е. они должны выглядеть как ПОИСКПОЗ($G$2;$A$2:$A$5;0) и ПОИСКПОЗ($H$2; А$2:$Е2;0).

Окончательный вид формулы будет: ИНДЕКС($А$2:$Е$5; ПОИСКПОЗ($G$2;$А$2:А$5;0); ПОИСКПОЗ($H$2; $А$2:$Е$2;0)).

В результате будем иметь таблицу, изображенную ниже

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

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