В эксель функция индекс - Учим Эксель

Функция ИНДЕКС() в EXCEL

Функция ИНДЕКС() в EXCEL

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

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

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

Значения аргументов «номер_строки» и «номер_столбца» должны указывать на ячейку снутри данного массива; в неприятном случае функция ИНДЕКС() возвращает значение ошибки #ССЫЛКА! К примеру, формула =ИНДЕКС(A2:A13;22) возвратит ошибку, т.к. в спектре А2:А13 лишь 12 строк.

Значение из данной строчки спектра

Пусть имеется одностолбцовый спектр А6:А9.

Выведем значение из 2-й строчки спектра, т.е. значение Груши . Это можно создать при помощи формулы =ИНДЕКС(A6:A9;2)

Если спектр горизонтальный (размещен в одной строке, к примеру, А6:D6 ), то формула для вывода значения из 2-го столбца будет смотреться так =ИНДЕКС(A6:D6;;2)

Значение из данной строчки и столбца таблицы

Пусть имеется таблица в спектре А6:B9.

Выведем значение, расположенное в 3-й строке и 2-м столбце таблицы, т.е. значение 200 . Это можно создать при помощи формулы =ИНДЕКС(A6:B9;3;2)

Внедрение функции в формулах массива

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

Пусть имеется одностолбцовый спектр А6:А9. Выведем 3 первых значения из этого спектра, т.е. на А6 , А7 , А8 . Для этого выделите 3 ячейки ( А21 , А22 , А23 ), в Строчку формул введите формулу =ИНДЕКС(A6:A9;0) , потом нажмите CTRL+SHIFT+ENTER .

Для чего это необходимо? Сейчас удалить по отдельности значения из ячеек А21 , А22 , А23 не получится, мы получим предупреждение «недозволено изменять часть массива».

Хотя можно просто ввести в этих 3-х ячейках ссылки на спектр А6:А8. Выделите 3 ячейки и введите формулу =A6:A8. Потом нажмите CTRL+SHIFT+ENTER и получим этот же итог.

Внедрение массива констант

Заместо ссылки на спектр можно применять массив констант :

ПОИСКПОЗ() + ИНДЕКС()

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

Формула =ВПР(«яблоки»;A35:B38;2;0) подобна формуле =ИНДЕКС(B35:B38;ПОИСКПОЗ(«яблоки»;A35:A38;0)) которая извлекает стоимость продукта Яблоки из таблицы, размещенную в спектре A35:B38

Связка ПОИСКПОЗ() + ИНДЕКС() даже гибче, чем функция ВПР() , т.к. при помощи ее можно, к примеру, найти продукт с данной ценой (оборотная задачка, так именуемый «левый ВПР()»). Формула =ИНДЕКС(A35:A38;ПОИСКПОЗ(200;B35:B38;0)) описывает продукт с ценой 200. Если продуктов с таковой ценой несколько, то будет выведен 1-ый сверху.

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

Функция ИНДЕКС() дозволяет применять так именуемую ссылочную форму. Поясним на примере.

Пусть имеется спектр с числами ( А2:А10 ) Нужно отыскать сумму первых 2-х, 3-х, . 9 значений. Естественно, можно написать несколько формул =СУММ(А2:А3) , =СУММ(А2:А4) и т.д. Но, записав формулу ввиде:

получим всепригодное решение, в каком требуется изменять лишь крайний аргумент (если в формуле выше заместо 4 ввести 5, то будет подсчитана сумма первых 5-и значений).

Внедрение функции ИНДЕКС() в этом примере принципно различается от примеров рассмотренных выше, т.к. функция возвращает не само значение, а ссылку (адресок ячейки) на значение. Вышеуказанная формула =СУММ(A2:ИНДЕКС(A2:A10;4)) эквивалентна формуле =СУММ(A2:A5)

Аналогичный итог можно получить используя функцию СМЕЩ()

Сейчас наиболее непростой пример, с областями.

Пусть имеется таблица продаж нескольких продуктов по полугодиям.

Задав Продукт , год и полугодие , можно вывести соответственный размер продаж при помощи формулы =ИНДЕКС((B9:C12;D9:E12;F9:G12);B15;A19;B17)

Вся таблица вроде бы разбита на 3 подтаблицы (области), надлежащие отдельным годам: B9:C12 ; D9:E12 ; F9:G12 . Задавая номер строчки, столбца (в подтаблице) и номер области, можно вывести соответственный размер продаж. В файле примера , избранные строчка и столбец выделены цветом при помощи Условного форматирования .

В эксель функция индекс

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

Браузер не поддерживает видео.

Функцию ИНДЕКС можно применять 2-мя методами:

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

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

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

Описание

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

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

Синтаксис

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

Аргументы формы массива функции ИНДЕКС последующую:

Массива — неотклонимый аргумент. Спектр ячеек либо константа массива.

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

Если массив имеет несколько строк и несколько столбцов и употребляется лишь row_num либо column_num, индекс возвращает массив всей строчки либо столбца в массиве.

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

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

Замечания

Если употребляются row_num и column_num, индекс возвращает значение ячейки на пересечении row_num и column_num.

Интересно почитать:  Функция сегодня в excel примеры

row_num и column_num должны указать на ячейку в массиве; в неприятном случае индекс возвращает #REF! ошибку «#ВЫЧИС!».

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

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

Примеры

Пример 1

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

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

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

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

Пример 2

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

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

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

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

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

Описание

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

Синтаксис

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

Аргументы функции ИНДЕКС могут быть последующими:

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

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

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

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

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

area_num Необязательный. Выбирает спектр в ссылке, из которого будут ворачиваться пересечения row_num и column_num. 1-ая избранная либо введенная область имеет номер 1, 2-ая — 2 и так дальше. Если area_num опущен, в индексе употребляется область 1. Перечисленные тут области должны быть размещены на одном листе. Если указать области, которые не находятся на одном листе вместе, это приведет к #VALUE! ошибку «#ВЫЧИС!». Если для вас необходимо применять спектры, расположенные на различных листах друг от друга, рекомендуется применять форму массива функции ИНДЕКС и применять другую функцию для вычисления спектра, который составляет массив. К примеру, при помощи функции ВЫБОР можно вычислить спектр, который будет употребляться.

К примеру, если в справочнике описаны ячейки (A1:B4;D1:E4,G1:H4), area_num 1 — спектр A1:B4, area_num 2 — спектр D1:E4, а area_num 3 — спектр G1:H4.

Замечания

Опосля ссылки и area_num вы избрали определенный спектр, row_num и column_num выберем определенную ячейку: row_num 1 — первую строчку спектра, column_num 1 — 1-ый столбец и так дальше. Ссылка, возвращаемая индексом, является пересечением row_num и column_num.

Если для row_num либо column_num 0, индекс возвращает ссылку на весь столбец либо строчку соответственно.

row_num, column_num и area_num должны указать на ячейку в ссылке; в неприятном случае индекс возвращает #REF! ошибку «#ВЫЧИС!». Если row_num и column_num опущены, индекс возвращает область в ссылке, обозначенной area_num.

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

Примеры

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

Интересно почитать:  Функция впр в эксель с несколькими условиями примеры

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

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

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

Внедрение функции ИНДЕКС

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

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

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

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

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

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

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

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

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

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

Переход в Мастер функций в Microsoft Excel

Мастер функций в Microsoft Excel

Выбор типа функции ИНДЕКС в Microsoft Excel

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

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

Окно аргументов функции ИНДЕКС в Microsoft Excel

Результат обработки функции ИНДЕКС в Microsoft Excel

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

Окно аргументов функции ИНДЕКС для одномерного массива в Microsoft Excel

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

Результат обработки функции ИНДЕКС для одномерного массива в Microsoft Excel

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

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

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

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

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

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

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

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

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

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

Имя вписано в поле в Microsoft Excel

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

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

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

Интересно почитать:  Excel функция подставить

Окно аргументов функции ИНДЕКС в комбинации с оператором ПОИСКПОЗ в Microsoft Excel

Результат обработки функции ИНДЕКС в комбинации с оператором ПОИСКПОЗ в Microsoft Excel

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

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

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

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

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

Выбор ссылочного вида функции ИНДЕКС в Microsoft Excel

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

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

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

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

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

Метод 4: вычисление суммы

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

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

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

Результат функции СУММ в Microsoft Excel

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

Результат комбинации функции СУММ и ИНДЕКС в Microsoft Excel

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

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

Мы рады, что смогли посодействовать Для вас в решении трудности.

Кроме данной нам статьи, на веб-сайте еще 12327 инструкций.
Добавьте веб-сайт Lumpics.ru в закладки (CTRL+D) и мы буквально еще пригодимся для вас.

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

Опишите, что у вас не вышло. Наши спецы постараются ответить очень стремительно.

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

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

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

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

База данных.

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

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

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

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

Выборка значения по индексу.

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

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

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

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

Ссылка на основную публикацию
Adblock
detector