Excel: ИНДЕКС — удобный поиск данных

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

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

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

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

В функции используются следующие параметры:

• Массив — задается левой верхней и правой нижней ячейкой.
• Строка — в которой находится нужная ячейка
• Столбец — в котором находится нужная ячейка.

Массив всегда задается двумя ячейками, разделенными двоеточием. Если он содержит только один столбец или одну строку, достаточно одного дополнительного параметра. Excel автоматически поймет, имеете ли вы в виду соответствующую строку или столбец.

Вы можете ввести координаты непосредственно в виде числа или установить ссылку на ячейку. Тогда числа будут содержаться в указанных ячейках. Параметр всегда относится к выбранной области, а не к рабочему листу, например. Так, если массив начинается с ячейки C2, то столбец D будет иметь номер 2, а не номер 4.

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

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

Последовательность в ссылке здесь имеет ключевое значение: Область, которую вы введете первой, также будет иметь номер 1. Области считаются слева направо — независимо от того, где область расположена на рабочем листе. Если вы хотите отобразить значение в другом массиве, достаточно изменить область в последнем параметре. Если этот параметр не указан, Excel автоматически считает, что вы имеете в виду первую область.

Наконец, INDEX также можно использовать в качестве матричной формулы. В этом случае выделенная область, содержащая несколько ячеек, воспроизводится в таком же количестве ячеек. Строку или столбец можно отобразить следующим образом: Сначала выделите соответствующее количество пустых ячеек, а затем введите функцию ИНДЕКС как обычно. (Область должна оставаться выделенной!) Вместо подтверждения ввода клавишей [Enter] используйте комбинацию клавиш [Ctrl] + [Shift] + [Enter]. Теперь выделенная область появится в нужной позиции в таблице.

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

Мы ввели параметры столбца и строки в ячейки B12 и B13. В этом примере важно отметить, что область внутри формулы не включает внешние метки. Это также возможно, но при этом необходимо будет соответствующим образом скорректировать значения двух других параметров.

Формулу можно расширить, если у вас есть несколько таблиц с одинаковой структурой. Предположим, у нас есть четыре таблицы — по одной для месяцев каждого квартала. Однако структура таблиц одинакова: столбцы и строки расположены одинаково.

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

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

Функция MATCH ищет значение или термин и указывает, какая ячейка в столбце или строке соответствует критерию поиска. Преимущество этой комбинации в том, что MATCH может искать термин и отправлять результат в INDEX; затем он выступает в качестве параметра столбца или строки в функции INDEX.

Как видите, две функции MATCH обращаются только к первому массиву. Но это не является проблемой. В конце концов, имена строк и столбцов одинаковы во всех версиях таблицы.

В Excel можно также комбинировать INDEX с математическими или статистическими функциями, такими как MEAN. В этом случае INDEX (опять же в сочетании с MATCH) указывает область, для которой нужно рассчитать среднее значение. Вместо того чтобы вставлять его непосредственно в формулу, вы можете динамически изменять область выделения через расположение формул.

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