Одной из самых полезных функций программы Эксель является оператор ИНДЕКС. Он производит поиск данных в диапазоне на пересечении указанных строки и столбца, возвращая результат в заранее обозначенную ячейку. Но полностью возможности этой функции раскрываются при использовании её в сложных формулах в комбинации с другими операторами. Давайте рассмотрим различные варианты её применения.
Использование функции ИНДЕКС
Оператор относится к группе функций из категории . Он имеет две разновидности: для массивов и для ссылок.
Вариант для массивов имеет следующий синтаксис:
=ИНДЕКС(массив;номер_строки;номер_столбца)
При этом два последних аргумента в формуле можно использовать, как вместе, так и любой один из них, если массив одномерный. При многомерном диапазоне следует применять оба значения. Нужно также учесть, что под номером строки и столбца понимается не номер на координатах листа, а порядок внутри самого указанного массива.
Синтаксис для ссылочного варианта выглядит так:
=ИНДЕКС(ссылка;номер_строки;номер_столбца;[номер_области])
Тут точно так же можно использовать только один аргумент из двух: или . Аргумент вообще является необязательным и он применяется только тогда, когда в операции участвуют несколько диапазонов.
Таким образом, оператор ищет данные в установленном диапазоне при указании строки или столбца. Данная функция своими возможностями очень похожа на оператора ВПР, но в отличие от него может производить поиск практически везде, а не только в крайнем левом столбце таблицы.
Способ 1: использование оператора ИНДЕКС для массивов
Давайте, прежде всего, разберем на простейшем примере алгоритм использования оператора для массивов.
Имеем таблицу зарплат. В первом её столбце отображены фамилии работников, во втором – дата выплаты, а в третьем – величина суммы заработка. Нам нужно вывести имя работника в третьей строке.
- Выделяем ячейку, в которой будет выводиться результат обработки. Кликаем по значку , который размещен сразу слева от строки формул.
- Происходит процедура активации . В категории данного инструмента или ищем наименование . После того, как нашли этого оператора, выделяем его и щелкаем по кнопке , которая размещается в нижней части окна.
- Открывается небольшое окошко, в котором нужно выбрать один из типов функции: или . Нужный нам вариант . Он расположен первым и по умолчанию выделен. Поэтому нам остается просто нажать на кнопку .
- Открывается окно аргументов функции . Как выше говорилось, у неё имеется три аргумента, а соответственно и три поля для заполнения.
В поле нужно указать адрес обрабатываемого диапазона данных. Его можно вбить вручную. Но для облегчения задачи мы поступим иначе. Ставим курсор в соответствующее поле, а затем обводим весь диапазон табличных данных на листе. После этого адрес диапазона тут же отобразится в поле.
В поле ставим цифру , так как по условию нам нужно определить третье имя в списке. В поле устанавливаем число , так как колонка с именами является первой в выделенном диапазоне.
После того, как все указанные настройки совершены, щелкаем по кнопке .
- Результат обработки выводится в ячейку, которая была указана в первом пункте данной инструкции. Именно выведенная фамилия является третьей в списке в выделенном диапазоне данных.
Мы разобрали применение функции в многомерном массиве (несколько столбцов и строк). Если бы диапазон был одномерным, то заполнение данных в окне аргументов было бы ещё проще. В поле тем же методом, что и выше, мы указываем его адрес. В данном случае диапазон данных состоит только из значений в одной колонке . В поле указываем значение , так как нужно узнать данные из третьей строки. Поле вообще можно оставить пустым, так как у нас одномерный диапазон, в котором используется только один столбец. Жмем на кнопку .
Результат будет точно такой же, что и выше.
Это был простейший пример, чтобы вы увидели, как работает данная функция, но на практике подобный вариант её использования применяется все-таки редко.
Способ 2: применение в комплексе с оператором ПОИСКПОЗ
На практике функция чаще всего применяется вместе с аргументом . Связка – является мощнейшим инструментом при работе в Эксель, который по своему функционалу более гибок, чем его ближайший аналог – оператор .
Основной задачей функции является указание номера по порядку определенного значения в выделенном диапазоне.
Синтаксис оператора такой:
=ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])
- – это значение, позицию которого в диапазоне мы ищем;
- – это диапазон, в котором находится это значение;
- – это необязательный параметр, который определяет, точно или приблизительно искать значения. Мы будем искать точные значения, поэтому данный аргумент не используется.
С помощью этого инструмента можно автоматизировать введение аргументов и в функцию .
Посмотрим, как это можно сделать на конкретном примере. Работаем все с той же таблицей, о которой шла речь выше. Отдельно у нас имеется два дополнительных поля – и . Нужно сделать так, что при введении имени работника автоматически отображалась сумма заработанных им денег. Посмотрим, как это можно воплотить на практике, применив функции и .
- Прежде всего, узнаем, какую заработную плату получает работник Парфенов Д. Ф. Вписываем его имя в соответствующее поле.
- Выделяем ячейку в поле , в которой будет выводиться итоговый результат. Запускаем окно аргументов функции для массивов.
В поле вносим координаты столбца, в котором находятся суммы заработных плат работников.
Поле оставляем пустым, так как мы используем для примера одномерный диапазон.
А вот в поле нам как раз нужно будет записать функцию . Для её записи придерживаемся того синтаксиса, о котором шла речь выше. Сразу в поле вписываем наименование самого оператора без кавычек. Затем сразу же открываем скобку и указываем координаты искомого значения. Это координаты той ячейки, в которую мы отдельно записали фамилию работника Парфенова. Ставим точку с запятой и указываем координаты просматриваемого диапазона. В нашем случае это адрес столбца с именами сотрудников. После этого закрываем скобку.
После того, как все значения внесены, жмем на кнопку .
- Результат количества заработка Парфенова Д. Ф. после обработки выводится в поле
- Теперь, если в поле мы изменим содержимое с , на, например, , то автоматически изменится и значение заработной платы в поле .
Способ 3: обработка нескольких таблиц
Теперь посмотрим, как с помощью оператора можно обработать несколько таблиц. Для этих целей будет применяться дополнительный аргумент .
Имеем три таблицы. В каждой таблице отображена заработная плата работников за отдельный месяц. Нашей задачей является узнать заработную плату (третий столбец) второго работника (вторая строка) за третий месяц (третья область).
- Выделяем ячейку, в которой будет производиться вывод результата и обычным способом открываем , но при выборе типа оператора выбираем ссылочный вид. Это нам нужно потому, что именно этот тип поддерживает работу с аргументом .
- Открывается окно аргументов. В поле нам нужно указать адреса всех трех диапазонов. Для этого устанавливаем курсор в поле и выделяем первый диапазон с зажатой левой кнопкой мыши. Затем ставим точку с запятой. Это очень важно, так как если вы сразу перейдете к выделению следующего массива, то его адрес просто заменит координаты предыдущего. Итак, после введения точки с запятой выделяем следующий диапазон. Затем опять ставим точку с запятой и выделяем последний массив. Все выражение, которое находится в поле берем в скобки.
В поле указываем цифру , так как ищем вторую фамилию в списке.
В поле указываем цифру , так как колонка с зарплатой является третьей по счету в каждой таблице.
В поле ставим цифру , так как нам нужно найти данные в третьей таблице, в которой содержится информация о заработной плате за третий месяц.
После того, как все данные введены, щелкаем по кнопке .
- После этого в предварительно выделенную ячейку выводятся результаты вычисления. Там отображается сумма заработной платы второго по счету работника (Сафронова В. М.) за третий месяц.
Способ 4: вычисление суммы
Ссылочная форма не так часто применяется, как форма массива, но её можно использовать не только при работе с несколькими диапазонами, но и для других нужд. Например, её можно применять для расчета суммы в комбинации с оператором .
При сложении суммы имеет следующий синтаксис:
=СУММ(адрес_массива)
В нашем конкретном случае сумму заработка всех работников за месяц можно вычислить при помощи следующей формулы:
=СУММ(C4:C9)
Но можно её немного модифицировать, использовав функцию . Тогда она будет иметь следующий вид:
=СУММ(C4:ИНДЕКС(C4:C9;6))
В этом случае в координатах начала массива указывается ячейка, с которой он начинается. А вот в координатах указания окончания массива используется оператор . В данном случае первый аргумент оператора указывает на диапазон, а второй – на последнюю его ячейку – шестую.
Как видим, функцию можно использовать в Экселе для решения довольно разноплановых задач. Хотя мы рассмотрели далеко не все возможные варианты её применения, а только самые востребованные. Существует два типа этой функции: ссылочный и для массивов. Наиболее эффективно её можно применять в комбинации с другими операторами. Созданные таким способом формулы смогут решать самые сложные задачи.
Мы рады, что смогли помочь Вам в решении проблемы.
Помимо этой статьи, на сайте еще 13048 полезных инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам. Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.