Как сделать индекс в экселе в формуле

Обновлено: 08.07.2024

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

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

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

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

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

Синтаксис

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ошибки

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

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

Заключение

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

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

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

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

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

Синтаксис, который имеет функция ИНДЕКС в Excel, следующий:

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

массив – это обязательный аргумент, который содержит в себе константу на массив или диапазон ячеек;
номер строки – является обязательным аргументом, который указывает, из какого номера строки нам нужно вернуть результаты;
номер столбика – этот аргумент не является обязательным при условии, когда в аргументе массив указан один конкретный столбик, но становится обязательным, когда в массиве, два и более столбика, тогда нужно указать столбик, из которого нужно будет вернуть значение.

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

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

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

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

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

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

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

Microsoft Office Excel имеет в своем арсенале формулу, которая позволяет определять координаты ячейки и возвращать ее значение в любую точку на рабочем листе. Такая опция полезна при работе с объемными базами данных, содержащих большое количество информации. Сегодня разберемся, как работает функция индекс в excel.

Описание

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

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

Выбираете произвольную ячейку для отображения результата.
В строке формул записываете =ИНДЕКС(B2:E8;5;3)

где B2:E8 — диапазон значений, внутри которого необходимо найти требуемую позицию. Два последующих числа обозначают номер строки и столбца соответственно.

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

Результат формулы соответствует значению в таблице, значит все сделано правильно.

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

Примеры

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

Запишем формулу, при этом блок диапазонов записывается через точку с запятой и заключается в круглые скобки. Далее указываете номера строки, столбца и диапазона для поиска.

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

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

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

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

Форма массива

ИНДЕКС(массив; номер_строки; [номер_столбца])
Возвращает значение элемента таблицы или массива на пересечении конкретных строки и столбца, в данном диапазоне.

Массив(обязательный аргумент) — диапазон ячеек;
Номер строки(обязательный аргумент) — выбирает строку в массиве из которой будет возвращаться значение;
Номер столбца(необязательный аргумент) — выбирает столбец в массиве из которой будет возвращаться значение.

Ссылочная форма

ИНДЕКС(ссылка; номер_строки; [номер_столбца]; [номер_области])
Возвращает ссылку на ячейку на пересечении конкретных строки и столбца, в данном диапазоне.

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

Предположим у нас есть таблица данных с продуктами:

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

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

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

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

Искомое значение. Это значение, которое мы будем искать в списке. Значение должно быть уникальным, иначе Excel найдет только первое!
Диапазон списка. Может состоять только из одного столбца или одной сроки. Это важно! Если попробовать выделить две колонки или две строки, программа выдаст ошибку!
Способ поиска. Тут надо указать вариант, согласно которому приложение выполнит поиск позиции для нашего искомого значения. Тут возможны следующие типы.

1 Будет найдено ближайшее к нашему значение, которое не превышает его, то есть НЕ БОЛЬШЕ нужного. Равным может быть, но не больше. Список должен располагаться ПО ВОЗРАСТАНИЮ.

-1 Excel найдет значение, которое самое близкое к заданному нами, но НЕ МЕНЬШЕ ЕГО. Список должен быть ПО УБЫВАНИЮ.

0 Запустится поиск ТОЧНОГО СООТВЕТСТВИЯ значения из списка заданному нами. Сортировка тут НЕ ТРЕБУЕТСЯ.

Наглядное применение ПОИСКПОЗ.

Посмотрите на скриншот.

Особое внимание обратите вот на что. Во второй и третьей формуле задан тип поиска равный 1 (единице). Однако если в первой из них Excel искал число, которое действительно присутствует в списке, то есть число 50, то он его порядковый номер и указал. А вот числа 68 в списке нет, поэтому он указал порядковый номер значения из списка, которое находится ближе всего к нужному нам, то есть к 68, но его не превышает. А это – число 60.

И еще одна тонкость. Если использовать тип поиска, или, как пишется в справке Excel по этой функции, тип сопоставления, единицу, и в качестве искомого задать значение, которое будет явно больше любого в списке, то Excel покажет номер самого последнего элемента. Таким образом можно узнать, какая строка является последней в таблице. Вот пример:

Функция ИНДЕКС в Excel и ее особенности.

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

Отметим, что функция индекс, включенная в состав другой функции, начинает срабатывать как часть адреса. В следующем примере Excel рассчитывает сумму за первые 6 месяцев. Информацию о номере последнего месяца он берет из ячейки R4, закрашенной зеленым цветом.

Стоит поменять в ней значение, и результат функции СУММ поменяется.

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

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

Результат работы может выглядеть так, как на рисунке.

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

Функция СМЕЩ в Excel и т онкости ее применения.

. Функция СМЕЩ показывает значение ячейки или диапазона, которые смещен относительно указанного адреса на нужное количество строк и столбцов. Ее синтаксис следующий.

СМЕЩ(1;2;3;4;5)

Ссылка, от которой отсчитывается перемещение.
На сколько строк надо переместиться.
Сколько столбцов надо отсчитать для перемещения.
Объем строк в диапазоне, на которые перемещаемся. Указывать не обязательно.
Количество столбцов, на которые перемещаемся. Тоже указывать не обязательно.

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

В примере выше происходит перемещение от ячейки G11 сначала на 2 строчки вверх, затем на 4 колонки влево. В найденной ячейки находится число 10, которое и является результатом работы функции.

Вариант же, когда указывается диапазон, на который переходим, чаще используется как исходные данные для другой функции. В примере ниже с помощью функции СМЕЩ вычисляется диапазон, по которому затем рассчитывается сумма. Если быть честным, то данный вариант на практике бесполезен, проще было бы сразу указать столбец Е для вычисления. Однако он хорошо показывает возможности СМЕЩ.

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

Первый вариант создания “левого ВПР”. Комбинируем ИНДЕКС и ПОИСКПОЗ.

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

Данные формулы конечно можно объединить в одну. Если в последней формуле вместо адреса К8 указать ее содержимое, то есть записать ПОИСКПОЗ(K6;C7:C86;0), то результат работы не изменится. Итоговая формула будет уже такой:

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

Второй вариант создания “левого ВПР”. Комбинируем СМЕЩ и ПОИСКПОЗ.

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

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

Особенности связки СМЕЩ и ПОИСКПОЗ

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

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

На этом наше занятие можно считать законченным. Попробуйте сами применить полученные знания. Вначале поработайте на учебных материалах, а уже потом и в рабочих документах. Всем удачи и успехов в использовании Excel !

Читайте также: