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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  5. Результат обработки выводится в ячейку, которая была указана в первом пункте данной инструкции. Именно выведенная фамилия является третьей в списке в выделенном диапазоне данных.

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

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

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

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

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

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

Синтаксис оператора такой:

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

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

С помощью этого инструмента можно автоматизировать введение аргументов и в функцию .

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

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

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

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

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

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

  3. Результат количества заработка Парфенова Д. Ф. после обработки выводится в поле
  4. Теперь, если в поле мы изменим содержимое с , на, например, , то автоматически изменится и значение заработной платы в поле .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=СУММ(C4:C9)

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

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.
Помимо этой статьи, на сайте еще 13048 полезных инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам. Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Источник: starhit
Читайте также  Что делать, если не запускается игра GTA 4 на Windows 7

Домашние хитрости