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

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

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

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

Оператор INDEX относится к группе функций в категории "Ссылки и массивы" . Есть две разновидности: для массивов и для ссылок.

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

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

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

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

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

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

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

Метод 1: используйте оператор INDEX для массивов

Сначала рассмотрим простейший пример алгоритма использования оператора массива INDEX.

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

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

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

  5. Откроется небольшое окно, в котором вы выбираете один из типов функций: "Массив" или "Ссылка" . Нам нужен вариант "Массив" . Он расположен первым и помечен по умолчанию. Так что вам просто нужно нажать на кнопку "в порядке" .
  6. Функция ИНДЕКС в Excel

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

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

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

    После того, как вы выполнили все настройки, нажмите кнопку "в порядке" .

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

  9. Результат обработки отображается в поле, указанном в первом абзаце данного руководства. Это случайное имя, которое занимает третье место в списке в выбранном диапазоне данных.
Читать также:  Auslogics Boostspeed 10 0 5 0 скачать бесплатно

Метод 2: приложение в сочетании с оператором MATCH

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

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

Синтаксис выражения POSITION:

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

  • Желаемое значение — это значение, положение которого в диапазоне мы ищем;
  • Отсканированный массив — это диапазон, в котором находится это значение;
  • Тип сопоставления — это необязательный параметр, который определяет, нужно ли его точно или приблизительно найти. Мы будем искать точные значения, поэтому этот аргумент не используется.

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

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

  1. Первым делом выясняем, какую зарплату получает сотрудник ДП. "Парфенёв". Вводим его имя в соответствующее поле.
  2. Функция ИНДЕКС в Excel

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

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

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

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

    После ввода всех значений нажмите на кнопку "в порядке" .

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

  5. Результат суммы выручки Парфенов Д.Ф. после обработки отображается в поле "Количество".
  6. Функция ИНДЕКС в Excel

  7. Теперь, если в поле "имя" изменить контент с "Парфенов Д.Ф." , например "Попова М.Д." , то величина заработной платы в отрасли "Сумма" изменится автоматически.
Читать также:  Скачать драйвера для Intel WiMax Link 5150

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

Теперь посмотрим, как можно обработать несколько таблиц с помощью оператора INDEX. Для этих целей будет применяться дополнительный аргумент. "Номер зоны" .

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

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

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

    В поле "Номер строки" мы показываем номер "2" поскольку мы ищем вторую фамилию в списке.

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

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

    После ввода всех данных нажмите на кнопку "в порядке" .

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

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

Метод 4: Рассчитайте сумму

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

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

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

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

= СУММ (C4: C9)

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

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

= СУММ (C4: ИНДЕКС (C4: C9; 6))

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

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

Урок: Полезные функции Excel

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

Понравилась статья? Поделиться с друзьями:
Что нужно знать пользователю?