Что такое функция индекс в экселе. Функция индекс в excel и примеры ее работы.

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

1. При добавлении столбца в таблицу данных не нужно менять номер столбца в самой формуле (как в ВПР). Столбец подвинется автоматом
2. Можно сделать ВПР наоборот, т.е. сделать выбор из таблицы справа-налево. В ВПР первый столбец всегда должен быть для поиска.

Как все это сделать, читаем ниже:)

Вводная информация

Использовать буду пример из . Для того, чтобы лучше понять применение этой конструкции в сравнение с ВПР:

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

Сперва определитесь где таблица изначальная а где для получения данных! Поэтапно, что заполнять.

1. Вставляем формулу в нужную область таблицы
2. На место $G:$G поставьте те ячейки, в которых должно быть найдено значение и соответственно, должно появиться в результате. Мы ищем фамилию, значит в ищите столбец с фамилиями в начальной таблице.
3. Замените $J:$J на в зависимости от которых должны вернуться значения в ячейку. Нам нужны Фамилии в зависимости от ТС — вставляем те ТС около которых должны появиться значения.
4. Вместо $H:$H заполняем столбец с в котором нужно найти соответствующее значение. Т.е. мы ищем Фамилию по ТС, значит вставляем столбец с ТС в изначальной таблице.

Если вам интересно для чего в формуле знаки $ читайте

ИНДЕКС и ПОИСКПОЗ. Что это за функции?

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

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

Возвращает значение на пересечении указанной строки и столбца определенного диапазона. Т.е. изначально она работает с двумерными массивами.

Выберя массив данных J1:K4 и задав номер строки и столбца равным двум, мы получили соответствующее значение.

Посмотрев на изначальную формулу

ИНДЕКС($G:$G;ПОИСКПОЗ($J:$J;$H:$H;0);1)

Мы увидим, что вместо второго аргумента (номер строки) у нас формула ПОИСКПОЗ. Что тут она делает?

ПОИСКПОЗ — это поиск по значению. Функция ищет заданное значение в строке или в столбце и возвращает ее порядковый номер (от начала диапазона). Т.е. во втором аргументе функции ИНДЕКС мы находим нужный нам номер ТС, получаем его номер, например 2.

И уже в одномерном массиве $G:$G находим ячейку с номером строки = 2. Так это будет работать для каждой ячейки в столбце J.

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

Пишите комментарии, если вопросы остались.

На данный момент программа 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. Примеры ее совместного использования с "ПОИСКПОЗ" вам также известны, и вы сможете корректно применять их для решения многих практических задач.

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

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

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

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

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

Для начала создадим выпадающий список для поля АРТИКУЛ ТОВАРА, чтобы не вводить цифры с клавиатуры, а выбирать их. Для этого кликаем в соответствующую ячейку (у нас это F13), затем выбираем вкладку ДАННЫЕ – ПРОВЕРКА ДАННЫХ. В открывшемся окне в пункте ТИП ДАННЫХ выбираем СПИСОК. А в качестве источника выделяем столбец с артикулами, включая шапку. Так у нас получился выпадающий список артикулов, которые мы можем выбирать.

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

Массив. В данном случае это вся таблица заказов. Выделяем ее вместе с шапкой и фиксируем клавишей F4.

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

Записываем команду ПОИСКПОЗ и проставляем ее аргументы.

Искомое значение. В нашем случае это ячейка, в которой указывается артикул, т.е. F13. Фиксируем ее клавишей F4.

Просматриваемый массив. Т.к. мы ищем по артикулу, значит, выделяем столбец артикулов вместе с шапкой. Фиксируем F4.

Тип сопоставления. Excel предлагает три типа сопоставления: больше, меньше и точное совпадение. У нас конкретный артикул, поэтому выбираем точное совпадение. В программе оно значится как 0 (ноль). На этом аргументы ПОИСКПОЗ закончились.

Номер столбца. Опять же воспользуемся ПОИСКПОЗ. Искомым значением будет ячейка E14, где указано наименование параметра, который мы ищем (ТОВАР). Просматриваемый массив: шапка с наименованиями, потому что искать система будет по слову ТОВАР. Тип сопоставления: 0.

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

Поиск индекса максимального числа массива в Excel

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

Начнем с количества. В любой ячейке под этим столбцом пишем =ИНДЕКС.

Первым аргументом у нас будет не просто массив, а максимальное число из массива. Поэтому дополнительно используем команду МАКС и выделяем соответствующий массив.

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

Получили простейшую формулу, помогающую вывести максимальное значение из массива. Протянем ее вправо, получив аналогичную информацию по цене и сумме.

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

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

Функция ПОИСКПОЗ возвращает относительное расположение ячейки в заданном диапазоне Excel, содержимое которой соответствует искомому значению. Т.е. данная функция возвращает не само содержимое, а его местоположение в массиве данных.

Например, на рисунке ниже формула вернет число 5 , поскольку имя "Дарья" находится в пятой строке диапазона A1:A9.

В следующем примере формула вернет 3 , поскольку число 300 находится в третьем столбце диапазона B1:I1.

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

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

Функция ИНДЕКС в Excel

Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.

Стоит отметить, что номера строк и столбцов задаются относительно верхней левой ячейки диапазона. Например, если ту же таблицу расположить в другом диапазоне, то формула вернет тот же результат:

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

Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):

Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):

Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel

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

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

Пускай ячейка C15 содержит указанный нами месяц, например, Май . А ячейка C16 - тип товара, например, Овощи . Введем в ячейку C17 следующую формулу и нажмем Enter :

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

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

В данной формуле функция ИНДЕКС принимает все 3 аргумента:

Если подставить в исходную громоздкую формулу вместо функций ПОИСКПОЗ уже вычисленные данные из ячеек D15 и D16, то формула преобразится в более компактный и понятный вид:

=ИНДЕКС(B2:E13;D15;D16)

Как видите, все достаточно просто!

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

Здравствуйте друзья!

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

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

Хотя наибольшую эффективность проявляет в тандеме с другими функциями, такими как (написана отдельная статья с примерами, рекомендую к прочтению), , СУММПРОИЗВ и прочее. Эта функция в тандеме очень хорошая альтернатива (детально о функции в статье), она в некоторых моментах может то, что ей не доступно, например, поиск с левой стороны. Да в принципе можно много достоинств описывать, но всё же лучше приступить к практике, и как всегда начнём с синтаксиса функции ИНДЕКС.

Синтаксис, который имеет , следующий:

= ИНДЕКС (массив, номер строки, [номер столбика]) , где

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

=ИНДЕКС(C1:G12;6; 2)

вернет значение ячейки B4, то есть, значение с четвертой строки во втором столбике;

В более сложном исполнении мы вернем значение целого массива:

{=ИНДЕКС(C2:G12; ; 2)}

данная интерпретация формулы вернет вам ссылку на диапазон B1:B5, которую, в дальнейшем вы сможете использовать в вычислениях. Как видите, формула заключена в фигурные скобки, а это означает вводиться формула должна как формула массива, не просто кнопка Enter , а CTRL + Shift + Enter , которая введет значение как массив и сама поставит фигурные скобки, просто так, вручную это сделать невозможно!

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

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

Я не буду повторяться с примерами по функции ПОИСКПОЗ, так как, я детально и в разнообразных примерах рассмотрел эту функции в своей статье, и вам рекомендую пройти по и ознакомится с предоставленными материалами.

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

До новых встреч на страницах сайта!

"Они нуждаются, обладая богатством, — а это самый тяжелый вид нищеты.
"
Л.А. Сенека