Индекс функция в эксель: ИНДЕКС (функция ИНДЕКС)

Содержание

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

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

Содержание

  • Описание функции ИНДЕКС
  • Функция ИНДЕКС для массивов
  • Функция ИНДЕКС для ссылок
  • Использование с оператором СУММ
  • Сочетание с функцией ПОИСКПОЗ
  • Заключение

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

Смотрите также: “Ссылки в Excel: абсолютные, относительные, смешанные”

ИНДЕКС входит в категорию операторов “Ссылки и массивы”, причем имеет два разных набора аргументов:

1. Для массивов

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

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

Может быть заполнен один из двух аргументов: “Номер_строки” или “Номер_столбца”. Все зависит от того, с каким массивом приходиться иметь дело. Если, например, массив занимает только одну строку, то аргумент “Номер_строки” не актуален, и заполняется только “Номер_столбца”. Для массива, занимающего всего лишь один столбец все аналогично. Здесь есть один нюанс – указывать нужно номера строк и столбцов, ориентируясь не на общие обозначения на панелях координат программы, а на порядковые номера внутри заданного массива.

2. Для ссылок

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

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

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

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

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

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

Наша задача – в заранее выбранной ячейке отобразить наименование 5-ой позиции в списке.

  1. Встаем в ячейку, куда планируем вывести требуемые данные. Затем жмем кнопку “Вставить функцию” (fx).
  2. В открывшемся окне вставки функции выбираем категорию “Ссылки и массивы” (или “Полный алфавитный перечень”), отмечаем строку “ИНДЕКС” и жмем OK.
  3. Далее программа предложит на выбор один из двух наборов аргументов, о которых мы писали выше (для массива или для ссылок). В рамках поставленной задачи выбираем первый вариант и жмем OK.
  4. Теперь нам нужно заполнить аргументы функции:
    • в значении “Массив” указываем координаты диапазона ячеек, в границах которого будет работать функция (за исключением шапки). Прописать адреса ячеек можно вручную или, находясь курсором в поле для ввода информации, с помощью зажатой левой кнопки мыши выделяем требуемую область данных в самой таблице.
    • в аргументе “Номер_строки” пишем цифру 5, так как согласно поставленной задаче требуется отобрать 5-ую позицию из списка.
    • в значении аргумента “Номер_столбца” пишем число 1, потому что наименования позиций находятся в первом столбце рассматриваемого массива.
    • по готовности жмем OK.
  5. В выбранной ячейке отобразится результат согласно заданным условиям отбора в аргументах функции. В нашем случае – это содержимое ячейки, находящейся в 1-ом столбце и 5-ой строке выделенного массива.

Как мы ранее отмечали, один из аргументов функции (“Номер_столбца” или “Номер_строки”) может оставаться незаполненным, если выделенный массив будет одномерным, т.е. занимать либо одну строку, либо один столбец. На практике это выглядит следующим образом.

  1. В окне аргументов функции в поле “Массив” выделяем только ячейки первого столбца. Указываем номер строки – 5, а номер столбца, соответственно, остается незаполненным, так как в выделенном нами массиве он только один.
  2. Нажав кнопку OK мы получим тот же результат в ячейке таблицы, хотя аргументы функции и ее формула будут отличаться от первоначального варианта.

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

Смотрите также: “Как поменять строки местами в Excel”

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

Допустим, у нас есть 4 таблицы. В каждой из них представлена информация по продажам за определенный период времени (1, 2, 3 и 4 кварталы).

Нам нужно узнать продажи 4-ой позиции (“Системный блок”) за второй квартал в штуках.

  1. Встаем в ячейку, куда планируем выводить итоговый результат и жмем кнопку “Вставить функцию” (fx).
  2. Выбираем функцию ИНДЕКС и жмем OK.
  3. Во вспомогательном окошке останавливаемся на втором варианте (для ссылки) и жмем кнопку OK.
  4. Отобразится окно с аргументами функции для заполнения:
    • поле “Ссылка” заполняется таким же образом, как и аргумент “Массив” в примере выше (вручную или методом выделения в самой таблице). Единственная разница заключается в том, что в данном случае нам нужно указать вместо одного сразу 4 диапазона ячеек, перечислив их через точку с запятой. Т.е. указываем первую область, ставим знак “;”, затем указываем вторую область и т. д. Когда все будет сделано, ставим открывающую и закрывающую скобки в начале и конце ссылки, соответственно.
    • в значении аргумента “Номер_строки” пишем число 4, так как нас интересуют данные по четвертой позиции.
    • в поле “Номер_столбца” пишем число 3, так как нам на нужны продажи в шт., а это третий по счету столбец в выделенных диапазонах.
    • в поле аргумента “Номер области” указываем число 2, так как требуется отобразить данные по второму кварталу, что соответствует второму отмеченному диапазону в аргументе “Ссылка”.
    • когда все готово, жмем OK.
  5. В выбранной ячейке с функцией отобразится требуемый результат согласно заданным условиям в аргументах.

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

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

=СУММ(Адрес_массива).

Если применить функцию суммирования к нашей таблице, например, к столбцу с суммой, формула будет выглядет так: =СУММ(D2:D9).

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

  1. В качестве первого аргумента функции СУММ оставляем координаты ячейки, которая является началом диапазона суммирования.
  2. Второй аргумент (крайняя нижняя ячейка диапазона) будем задавать с помощью оператора ИНДЕКС. Перейдя в режим редактирования формулы (двойным щелчком мыши по ячейке с формулой или просто кликнув по строке формул, предварительно выбрав нужную ячейку), приводим ее к следующему виду с учетом нашей таблицы:
    =СУММ(D2:ИНДЕКС(D2:D9;8)).Цифра 8 означает, что мы ограничиваем указанный диапазон между ячейками D2 и D9 позицией под номером 8. Мы можем задать любую цифру (больше или равно 0), не превышающую общее количество всех позиций в выбранном массиве.
  3. Нажав Enter мы получим результат в выбранной ячейке.

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

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

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

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

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

Давайте “пробежимся” по аргементам функции:

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

Использование ПОИСКПОЗ позволяет автоматически заполнить аргументы “Номер строки” и/или “Номер столбца” функции ИНДЕКС.

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

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

  1. Для начала, заполняем ячейку с наименованием. Можно просто скопировать и вставить значение из основной таблицы.
  2. Теперь встаем в ячейку, в которой планируем отображать результат, и жмем кнопку “Вставить функцию” (fx).
  3. Выбираем функцию ИНДЕКС из списка операторов.
  4. Выбираем список аргументов для массива и жмем OK.
  5. Приступаем к заполнению аргументов:
    • в значении “Массива” указываем ячейки столбца, содержащего цены позиций. Сделать это можно вручную или с помощью выделения ячеек в самой таблице.
    • в поле аргумента “Номер_строки” требуется добавить оператор ПОИСКПОЗ. Для этого делаем следующее:
      • перейдя в поле для заполнения данного аргумента щелкаем по небольшой стрелке вниз в поле с именем ячейки (в котором будет отображаться название текущей функции), расположенным слева от кнопки “Вставить функцию”.
      • в раскрывшемся перечне выбираем пункт “Другие функции”.
      • в очередном окне Мастера функций выбираем категорию “Ссылки и массивы”, находим оператор “ПОИСКПОЗ”, отмечаем его и жмем OK.
      • в аргументе “Искомое_значение” указываем адрес ячейки, по содержимому которого будет выполняться поиск в основном массиве (в нашем случае – это F2). В “Просматриваемом_массиве” указываем вручную или с помощью выделения в самой таблице диапазон ячеек, в котором будет производиться поиск искомого значения. В аргумент “Тип_сопоставления” указываем цифру 0.
    • Обращаем внимание на строку формул. Здесь мы левой кнопкой мыши щелкаем по слову “ИНДЕКС”.
    • появится снова список аргументов функции ИНДЕКС. Мы можем заметить, что в результате проделанных выше действий, поле “Номер_строки” заполнилось автоматически. Так как выделенный массив одномерный и содержит только один столбец, последний аргумент оставляем незаполненным и жмем кнопку OK. Примечание: заполнить аргумент “Номер_строки” можно и вручную, ориентируясь на синтаксис функции ПОИСКПОЗ.
  6. Таким образом, мы получаем в выбранной ячейке нужный результат, а именно цену указанной рядом позиции.
  7. Так как информация “подтягивается” с помощью формулы, изменение цены соответствующей позиции в основной таблице немедленно отразится в данной ячейке.
  8. Также, если мы изменим наименование позиции во вспомогательной таблице, ее цена автоматически будет заполнена из основной.

Заключение

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

ИНДЕКС Функция Excel — voxt.ru

Что такое функция индексации в Excel?

Функция индекса в excel — это полезная функция, которая используется либо для отображения значения ячейки, когда мы предоставляем ей позицию из массива таблицы, когда она используется индивидуально, когда функция индекса используется с функцией сопоставления, объединенной, она становится заменой для vlookup функция, когда нам нужно посмотреть значения слева в таблице.

Функция ИНДЕКС в Excel относится к категории «Формула поиска и ссылки».

Функция INDEX возвращает значение / позицию ячейки в данной таблице или диапазоне. Индексная функция полезна, когда у нас есть несколько данных, и каждый знает позицию, откуда нужно получить точку данных.

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

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

1) найдите значение, которое находится на пересечении строки и столбца.

2) найдите определенную таблицу, а затем в этой конкретной таблице найдите значение ячейки, которое находится при объединении строки и столбца.

Формула ИНДЕКС в Excel

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

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

Параметры формулы ИНДЕКС в Excel

  • Массив: массив определяется как определенный диапазон ячеек
  • row_num: обозначает позицию строки в указанном массиве.
  • [column_num]: Обозначает позицию столбца в указанном массиве.

Примечание. Обязательным является любой номер строки / столбца; это даст # ЗНАЧЕНИЕ! Ошибка, если оба значения пусты / ноль.

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

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

Вы можете скачать этот шаблон Excel для функции ИНДЕКС здесь — Шаблон для функции ИНДЕКС в Excel

Пример # 1

Результат:

В приведенном выше примере функция Index имеет только один диапазон и возвращает позицию в строке 5 диапазона C3: C7, которая является ячейкой C7. Имеет значение 4

Пример # 2

Результат:

В приведенном выше примере Index вернет ссылку на ячейку номер 4 и строку 3 из диапазона B3: F7, который является ячейкой E5. Это значение 629

Пример вернет #VALUE, если обе строки №, столбец № равны нулю.

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

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

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

  1. Массив: массив определяется как определенный диапазон ячеек / диапазон. В случае нескольких диапазонов отдельные области разделяются запятыми и закрываются скобками — например. (A1: C2, C4: D7).
  2. row_num: обозначает позицию строки в указанном массиве.
  3. [column_num]: Обозначает позицию столбца в указанном массиве.
  4. Area_num: Номер области выбирает диапазон в ссылке, из которого возвращается пересечение Column_num и Row_num.

Заметка: Если поле Area_num оставлено пустым, функция INDEX в Excel по умолчанию использует область 1.

Функция индекса возвращает #VALUE! Ошибка, если область, указанная в формуле ИНДЕКС в Excel, находится на любом другом листе. Области, указанные в INDEX Formula excel, должны быть расположены на одном листе.

Например:

Пример # 3

В приведенном выше примере у нас есть 3 разных диапазона ячеек; следовательно, массив для вышеупомянутого будет упоминаться как (B3: E7, D10: F12, C15: E18)

Результат:

В приведенном выше примере функция Index возвращает ссылку на столбец номер 4 и строку номер 3 второй области {D10: F12}, которая относится к ячейке E11.

Это значение 665

Что нужно помнить

  • Если номер столбца или номер строки равен 0 (ноль), он вернет полные значения конкретной строки или столбца соответственно.
  • Функция ИНДЕКС вернет ссылку на ячейку вместо значения ячейки, если она используется перед ссылкой на ячейку, например, A1: ИНДЕКС (A2: C6, 2, 3).
  • Функция ИНДЕКС широко используется с функцией ПОИСКПОЗ в Excel.
  • В отличие от ВПР, ИНДЕКС также может возвращать значение из левой позиции искомого значения в массиве.
  • Все параметры, используемые в формуле ИНДЕКС в excel, такие как Row_num, Column_num и Area_num, должны относиться к ячейке в пределах определенного массива; в противном случае функция ИНДЕКС в Excel вернет # ССЫЛКА! значение ошибки.
  • Если Row_num или Column_num пустое или нулевое значение, по умолчанию будут использоваться все строки или столбцы в указанном массиве.

УЗНАТЬ БОЛЬШЕ >>


Post Views: 252

Похожие записи

Прокрутить вверхФункция ИНДЕКС

в Excel — примеры, формула, как использовать?

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

Например, рабочий лист содержит названия континентов и соответствующих стран. Записи, набранные в Excel (без двойных кавычек), перечислены следующим образом:

Столбец A

  • Ячейка A1 содержит «Азия».
  • Ячейка A2 содержит «Африка».
  • Ячейка A3 содержит «Европа».
  • В ячейке A4 указано «Северная Америка».

Столбец B

  • Ячейка B1 содержит «Индия».
  • Ячейка B2 содержит «Нигерия».
  • Ячейка B3 содержит «Франция».
  • Ячейка B4 содержит «Канада».

Введите формулу «=ИНДЕКС(A1:B4,3,2)» в ячейку C1. Нажмите клавишу «Ввод», и на выходе будет «Франция» (без двойных кавычек).

Сначала функция Excel ИНДЕКС переходит к диапазону A1:B4. Из этого диапазона извлекается значение ячейки, находящейся на пересечении третьей строки (строка 3) и второго столбца (столбец B). Эта ячейка — B3, и ее значение — «Франция».

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

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

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

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