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

36

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

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

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

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

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

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

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

2. Для ссылок

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

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

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

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

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

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

Таблица Excel

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

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

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

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

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

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

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

Таблицы в Эксель

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

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

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

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

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

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

Сумма ячеек столбца в Эксель

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

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

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

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

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

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

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

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

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

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

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

Основная и вспомогательная таблицы в Excel

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

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

Заключение

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

ОСТАВЬТЕ ОТВЕТ

Введите свой комментарий
Пожалуйста, введите свое Имя