Excel индекс

ИНДЕКС (функция ИНДЕКС)

Функция ИНДЕКС возвращает значение или ссылку на значение из таблицы или диапазона.

Функцию ИНДЕКС можно использовать двумя способами:

Если вы хотите возвращать значение указанной ячейки или массива ячеек, см. раздел Форма массива.

Если требуется возвращать ссылку на указанные ячейки, см. раздел Ссылочная форма.

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

Возвращает значение элемента в таблице или массиве, выбранном с помощью индексов строк и столбцов.

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

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

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

IsArray — обязательный аргумент. Диапазон ячеек или константа массива.

Если массив содержит только одну строку или один столбец, соответствующий аргумент «номер_строки» или «номер_столбца» является необязательным.

Если массив содержит более одной строки и несколько столбцов и используется только аргумент «номер_строки» или «номер_столбца», функция индекс возвращает массив всей строки или столбца в массиве.

Номер_строки — обязательный аргумент. Выбирает строку в массиве, из которой требуется возвратить значение. Если аргумент «номер_строки» опущен, требуется номер_столбца.

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

Если используются оба аргумента «номер_строки» и «номер_столбца», функция индекс возвращает значение в ячейке на пересечении номер_строки и номер_столбца.

аргумент «номер_строки» и «номер_столбца» должен указывать на ячейку в массиве; в противном случае функция индекс возвращает значение #REF! ошибка #ЧИСЛО!.

Если для аргумента «номер_строки» или «номер_столбца» установлено значение 0 (ноль), функция индекс возвращает массив значений для всего столбца или строки соответственно. Чтобы использовать значения, возвращаемые в виде массива, введите в качестве формулы массива функцию индекс.

Примечание: Если у вас установлена текущая версия Office 365, вы можете ввести формулу в левую верхнюю ячейку диапазона вывода, а затем нажмите клавишу Ввод , чтобы подтвердить формулу как формулу динамических массивов. В противном случае необходимо ввести формулу в качестве устаревшей формулы массива, сначала выделив диапазон вывода, введите формулу в верхнюю левую ячейку выходного диапазона, а затем нажмите клавиши CTRL + SHIFT + ВВОД , чтобы подтвердить его. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

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

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — ВВОД.

Значение ячейки на пересечении второй строки и второго столбца в диапазоне A2:B3.

Значение ячейки на пересечении второй строки и первого столбца в диапазоне A2:B3.

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

Примечание: Если у вас установлена текущая версия Office 365, вы можете ввести формулу в левую верхнюю ячейку диапазона вывода, а затем нажмите клавишу Ввод , чтобы подтвердить формулу как формулу динамических массивов. В противном случае формула должна быть введена как устаревшая формула массива, если сначала выбрать две пустые ячейки, ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать клавиши CTRL + SHIFT + ВВОД , чтобы подтвердить его. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

Значение ячейки на пересечении первой строки и второго столбца в массиве. Массив содержит значения 1 и 2 в первой строке и значения 3 и 4 во второй строке.

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

Справочная форма

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

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

Ссылочная форма функции индекс имеет следующие аргументы:

справки Обязательный. Ссылка на один или несколько диапазонов ячеек.

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

Если каждая область в ссылке содержит только одну строку или один столбец, аргумент «номер_строки» или «номер_столбца» соответственно является обязательным. Например, для ссылки на единственную строку нужно использовать формулу ИНДЕКС(ссылка,,номер_столбца).

Номер_строки — обязательный аргумент. Номер строки в диапазоне, заданном аргументом «ссылка», из которого требуется возвратить ссылку.

Номер_столбца — необязательный аргумент. Номер столбца в диапазоне, заданном аргументом «ссылка», из которого требуется возвратить ссылку.

номер_области — необязательный аргумент. Выделяет диапазон в ссылке, из которого нужно вернуть пересечение номер_строки и номер_столбца. Первая выделенная или введенная область имеет номер 1, второй — 2 и т. д. Если аргумент номер_области опущен, то функция индекс использует область 1. Все перечисленные здесь области должны располагаться на одном листе. Если вы указали области, которые не находятся на одном листе, это вызовет #VALUE! ошибка #ЧИСЛО!. Если необходимо использовать диапазоны, расположенные на разных листах, рекомендуется использовать форму массива функции индекс и использовать другую функцию для вычисления диапазона, который составляет массив. Например, чтобы вычислить диапазон, который будет использоваться, можно использовать функцию выбор.

Например, если в ссылке описаны ячейки (a1: B4, D1: E4, G1: H4), то функция номер_области 1 — это диапазон a1: B4, номер_области 2 — диапазон D1: E4, а номер_области 3 — диапазон G1: H4.

После того, как ссылка и номер_области выделены определенный диапазон, выбери ячейку и номер_столбца, выделив одну из них: номер_строки 1 — это первая строка диапазона, номер_столбца 1 — первый столбец, и т. д. Ссылка, возвращаемая функцией индекс, — это пересечение с номер_строки и номер_столбца.

Если для аргумента «номер_строки» или «номер_столбца» установлено значение 0 (ноль), функция индекс возвращает ссылку на весь столбец или строку соответственно.

номер_строки, номер_столбца и номер_области должны указывать на ячейку в ссылке. в противном случае функция индекс возвращает значение #REF! ошибка #ЧИСЛО!. Если аргумент «номер_строки» и «номер_столбца» опущен, функция индекс возвращает область в ссылке, указанную с помощью номер_области.

Результатом вычисления функции ИНДЕКС является ссылка, которая интерпретируется в качестве таковой другими функциями. В зависимости от формулы значение, возвращаемое функцией ИНДЕКС, может использоваться как ссылка или как значение. Например, формула ЯЧЕЙКА(«ширина»;ИНДЕКС(A1:B2;1;2)) эквивалентна формуле ЯЧЕЙКА(«ширина»;B1). Функция ЯЧЕЙКА использует значение, возвращаемое функцией ИНДЕКС, как ссылку. С другой стороны, такая формула, как 2*ИНДЕКС(A1:B2;1;2), преобразует значение, возвращаемое функцией ИНДЕКС, в число в ячейке B1.

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД.

Функция ИНДЕКС в Excel и примеры ее работы с массивами данных

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

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

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

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

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

1. Ниже таблицы данный создайте небольшую вспомогательную табличку для управления поиском значений:
2. В ячейке B12 введите номер необходимого отдела, который потом выступит в качестве критерия для поискового запроса. Например, 3.
3. В ячейке B13 введите номер статьи. Например, 7.
4. В ячейку B14 введите следующую формулу:

В результате получаем значение на пересечении столбца 3 и строки 7:

Как видно значение 40 имеет координаты Отдел №3 и Статья №7. При этом функцией ИНДЕКС не учитываются номера строк листа Excel, а только строки и столбцы таблицы в диапазоне B2:G10.

Описание примера как работает функция ИНДЕКС

В первом аргументе функции указывается диапазон ячеек таблицы, по которому будет выполнен поиск значений на пересечении строк и столбцов. Во втором аргументе сначала указываем номер строки, а потом в третьем аргументе – номер столбца. На основе этой информации функция ищет соответствующее значение в таблице.

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

Функция ИНДЕКС в программе Microsoft Excel

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

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

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

Вариант для массивов имеет следующий синтаксис:

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

Синтаксис для ссылочного варианта выглядит так:

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

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

Способ 1: использование оператора ИНДЕКС для массивов

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

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

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

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

Открывается небольшое окошко, в котором нужно выбрать один из типов функции: «Массив» или «Ссылка». Нужный нам вариант «Массив». Он расположен первым и по умолчанию выделен. Поэтому нам остается просто нажать на кнопку «OK».

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

В поле «Массив» нужно указать адрес обрабатываемого диапазона данных. Его можно вбить вручную. Но для облегчения задачи мы поступим иначе. Ставим курсор в соответствующее поле, а затем обводим весь диапазон табличных данных на листе. После этого адрес диапазона тут же отобразится в поле.

В поле «Номер строки» ставим цифру «3», так как по условию нам нужно определить третье имя в списке. В поле «Номер столбца» устанавливаем число «1», так как колонка с именами является первой в выделенном диапазоне.

После того, как все указанные настройки совершены, щелкаем по кнопке «OK».

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

Результат будет точно такой же, что и выше.

Это был простейший пример, чтобы вы увидели, как работает данная функция, но на практике подобный вариант её использования применяется все-таки редко.

Способ 2: применение в комплексе с оператором ПОИСКПОЗ

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

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

Синтаксис оператора ПОИСКПОЗ такой:

=ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])

• Искомое значение – это значение, позицию которого в диапазоне мы ищем;
• Просматриваемый массив – это диапазон, в котором находится это значение;
• Тип сопоставления – это необязательный параметр, который определяет, точно или приблизительно искать значения. Мы будем искать точные значения, поэтому данный аргумент не используется.

С помощью этого инструмента можно автоматизировать введение аргументов «Номер строки» и «Номер столбца» в функцию ИНДЕКС.

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

Прежде всего, узнаем, какую заработную плату получает работник Парфенов Д. Ф. Вписываем его имя в соответствующее поле.

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

В поле «Массив» вносим координаты столбца, в котором находятся суммы заработных плат работников.

Поле «Номер столбца» оставляем пустым, так как мы используем для примера одномерный диапазон.

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

После того, как все значения внесены, жмем на кнопку «OK».

Результат количества заработка Парфенова Д. Ф. после обработки выводится в поле «Сумма».

Способ 3: обработка нескольких таблиц

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

Имеем три таблицы. В каждой таблице отображена заработная плата работников за отдельный месяц. Нашей задачей является узнать заработную плату (третий столбец) второго работника (вторая строка) за третий месяц (третья область).

Выделяем ячейку, в которой будет производиться вывод результата и обычным способом открываем Мастер функций, но при выборе типа оператора выбираем ссылочный вид. Это нам нужно потому, что именно этот тип поддерживает работу с аргументом «Номер области».

Открывается окно аргументов. В поле «Ссылка» нам нужно указать адреса всех трех диапазонов. Для этого устанавливаем курсор в поле и выделяем первый диапазон с зажатой левой кнопкой мыши. Затем ставим точку с запятой. Это очень важно, так как если вы сразу перейдете к выделению следующего массива, то его адрес просто заменит координаты предыдущего. Итак, после введения точки с запятой выделяем следующий диапазон. Затем опять ставим точку с запятой и выделяем последний массив. Все выражение, которое находится в поле «Ссылка» берем в скобки.

В поле «Номер строки» указываем цифру «2», так как ищем вторую фамилию в списке.

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

В поле «Номер области» ставим цифру «3», так как нам нужно найти данные в третьей таблице, в которой содержится информация о заработной плате за третий месяц.

После того, как все данные введены, щелкаем по кнопке «OK».

Способ 4: вычисление суммы

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

При сложении суммы СУММ имеет следующий синтаксис:

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

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

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

5 вариантов использования функции ИНДЕКС (INDEX)

Бывает у вас такое: смотришь на человека и думаешь «что за @#$%)(*?» А потом при близком знакомстве оказывается, что он знает пять языков, прыгает с парашютом, имеет семеро детей и черный пояс в шахматах, да и, вообще, добрейшей души человек и умница?

Так и в Microsoft Excel: есть несколько похожих функций, про которых фраза «внешность обманчива» работает на 100%. Одна из наиболее многогранных и полезных — функция ИНДЕКС (INDEX) . Далеко не все пользователи Excel про нее знают, и еще меньше используют все её возможности. Давайте разберем варианты ее применения, ибо их аж целых пять.

Вариант 1. Извлечение данных из столбца по номеру ячейки

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

=ИНДЕКС( Диапазон_столбец ; Порядковый_номер_ячейки )

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

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

Вариант 2. Извлечение данных из двумерного диапазона

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

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

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

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

Вариант 3. Несколько таблиц

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

=ИНДЕКС( (Диапазон1;Диапазон2;Диапазон3) ; Номер_строки ; Номер_столбца ; Номер_диапазона )

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

Вариант 4. Ссылка на столбец / строку

Если во втором варианте использования функции ИНДЕКС номер строки или столбца задать равным нулю (или просто не указать), то функция будет выдавать уже не значение, а ссылку на диапазон-столбец или диапазон-строку соответственно:

Обратите внимание, что поскольку ИНДЕКС выдает в этом варианте не конкретное значение ячейки, а ссылку на диапазон, то для подсчета потребуется заключить ее в дополнительную функцию, например СУММ (SUM) , СРЗНАЧ (AVERAGE) и т.п.

Вариант 5. Ссылка на ячейку

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

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

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

Функция ИНДЕКС (англ. INDEX) в Excel с примерами

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

Давайте вспомним как работает эта функция, а после этого рассмотрим работу данной функции совместно с функцией ПОИСКПОЗ (англ. MATCH)

Посмотрите на вот этот пример

Есть таблица с продажами различных фруктов в разных магазинах — это область A2:F10

Напомню синтаксис функции ИНДЕКС:

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

где массив — это наша таблица A2:F10

номер_строки — как следует из описания — это номер строки нашего массива (таблицы) , Обратите внимание! Номер строки необходимо считать именно по нашему указанному массиву , а не вообще с первой строки. Наша таблица начинается со второй строки.

номер_столбца — это номер столбца указанного массива. В нашем случае первый столбец совпадает с первым столбцом нашего массива.

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

=ИНДЕКС( A2:F10 ; 5 ; 3 ) — смотрите рисунок выше

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

Функция ИНДЕКС в Excel с функцией ПОИСКПОЗ

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

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

Конечно, в данном случае можно использовать функцию ВПР, при этом номер столбца нам нужно будет считать вручную. Перекресток это 3-й столбец, а Лента это 4-й столбец. Но представим, что количество магазинов будет очень много, к тому же данный отчет нам присылают каждый месяц и магазины могут быть в разных столбцах, кроме того, могут добавляться новые магазины. В данном случае нам будет неудобно использовать ВПР, так как номер столбца нам в каждом случае придется находить заново.

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

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

=ИНДЕКС( A2:F10 ; 3 ; 3 ) — груша находится в третьей строке таблицы A2:F10, а Перекресток в третьем столбце . Отлично, а теперь пропишем формулу, чтобы номер строки и номер столбца считался автоматически.

Чтобы найти номер строки используем функцию ПОИСКПОЗ — поиск позиции. Синтаксис функции:

=ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])

искомое_значение — нашем случае, в ячейке L4 мы ищем груши, поэтому искомое значение у нас будет K4

просматриваемый массив — нашем примере нам необходимо найти груши с столбце с фруктами — это диапазон A2:A10

тип_сопоставления — указываем 0, так как мы ищем полное совпадение.

Формула будет иметь следующий вид:

=ПОИСКПОЗ(K4;A2:A10;0) — итогом данной формулы будет позиция 3 в диапазоне A2:A10

аналогично, только в горизонтальном виде находим номер столбца.

искомое_значение — магазин перекресток или ячейка L3

просматриваемый массив — магазин мы находим в строке с магазинами — это диапазон A2:F2

тип_сопоставления — указываем 0, так как мы ищем точное совпадение.

в итоге получаем формулу:

=ПОИСКПОЗ(L3;A2:F2;0) — итогом данной формулы будет позиция 3 в диапазоне A2:F2

Теперь в нашу формулу =ИНДЕКС( A2:F10 ; 3 , 3 ) вместо номера строки и номера столбца пропишем раноценные значения, но в виде формул:

=ИНДЕКС( A2:F10 ; ПОИСКПОЗ(K4;A2:A10;0) ; ПОИСКПОЗ(L3;A2:F2;0) )

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

наш массив A2:F10 имеет относительный адрес, поэтому при протягивании формулы вниз и право диапазон так же будет сдвигаться, а он у нас постоянный, поэтому пропишем преобразуем его в абсолютный адрес, для этого пропишем знаки долларов перед столбцами и строки (можно выделить данный диапазон в формуле и нажать клавишу F4).

Далее идет номер строки с формулой ПОИСКПОЗ(K4;A2:A10 ;0) , при протягивании вниз у нас автоматически K4 (Груши) поменяется на K5 (Сливы), что нам и требуется, но диапазон, А2:A10 у нас постоянный, поэтому пропишем его в абсолютном виде А2:A10 → $А$2:$A$10

Все отлично, но когда мы будем протягивать формулу вправо, то K4 (Груши) автоматически поменяется на L4, нам же необходимо, чтобы при протягивании право K4 не менялось. Но мы помним, что в то же время нам необходимо, чтобы K4 менялось при протягивании вниз. Поэтому нам необходимо закрепить только столбец (K), а строка должна меняться. Пропишем знак доллар только перед столбцом К4 → $ K4

В итоге формула поиска номера строки будет выглядеть ПОИСКПОЗ($K4;$A$2:$A$10 ;0)

Аналогично с номером столбца, диапазон должен быть полностью закреплен, при протягивании вправо столбец должен меняться, а при протягивании вниз номер строки (строка с магазинами) не должен меняться. Для этого пропишем знак доллара только перед номером строки L3 → L$3

В итоге формула поиска номера столбца будет выглядеть ПОИСКПОЗ(L$3;$A$2:$F$2;0)

Итоговая формула, которую вы можете протянуть вниз и вправо

=ИНДЕКС( $ A$2:$F$10 ; ПОИСКПОЗ($K4;$A$2:$A$10;0) ; ПОИСКПОЗ(L$3;$A$2:$F$2;0) )

=INDEX ($A$2:$F$10;MATCH ($K4;$A$2:$A$10;0);MATCH (L$3;$A$2:$F$2;0)) — англ. версии

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

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

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