Примеры функции ИНДЕКС для создания массивов значений в Excel
Функция ИНДЕКС в Excel возвращает данные из таблицы или определенного диапазона ячеек на основе известных строки и столбца, значения которых передаются в качестве аргументов функции.
Примеры использования функции ИНДЕКС в Excel
Существует два варианта данной функции, отличающиеся синтаксической записью:
- Форма массива – функция ИНДЕКС используется для возврата одного или нескольких значений элементов массива (константы массивов в Excel, обозначаемые фигурными скобками «») или диапазона ячеек. Если в качестве первого аргумента функции была передана константа массива, функция должна быть выполнена как формула массива (Ctrl+Shift+Enter).
- Ссылочная форма – функция ИНДЕКС используется для возврата ссылки на определенную аргументами ячейку или несколько ячеек из одного либо нескольких диапазонов ячеек. Если эти диапазоны не являются смежными, можно явно указать диапазон, в котором будет произведен поиск требуемой ячейки. Формулы, принимающие в качестве аргументов не ссылочные данные, будут интерпретировать возвращенный функцией ИНДЕКС результат в качестве значения, хранящегося в ячейке, а не ссылки на нее. Например, функция =ФАКТР(ИНДЕКС(A4:B7;6;2)) вернет факториал числа, которое содержится в ячейке B6. Если результат выполнения функции ИНДЕКС используется в качестве аргумента функции, принимающей данные ссылочного типа, он будет определен в качестве ссылки на ячейку. Например, функция =СТРОКА(ИНДЕКС(A4:B7;6;2)) вернет число 4, то есть номер строки, в которой содержится ячейка B6.
Динамическая выборка из массива значений ячеек
Пример 1. В таблице Excel указаны данные о средней стоимости некоторых продуктов в определенные месяцы года. Найти стоимость молока в мае с использованием функции ИНДЕКС.
Для нахождения стоимости молока в мае используем формулу:
- B2:G8 – диапазон ячеек таблицы с числовыми значениями, в которых выполняется поиск;
- ПОИСКПОЗ(A10;A2:A8;0) – функция, возвращающая номер строки из диапазона A2:A8, в которой находится значение, содержащееся в ячейке A10 («Молоко, 1л»);
- ПОИСКПОЗ(B10;B1:G1;0) – функция, возвращающая номер столбца из диапазона B1:G1, в котором содержится значение, хранящееся в ячейке B10 («Май»).
Искомая ячейка находится в диапазоне B2:G8 на пересечении строки и столбца, найденных функциями ПОИСКПОЗ. В результате получим:
Стратегический анализ расходов в Excel
Пример 2. Определить, сколько денег потратил бы покупатель в феврале, если бы купил 2 л молока и 4 кг яблок на основании данных, содержащихся в таблице к первому примеру.
Функция СУММ возвращает сумму значений в ячейках, определенных функциями ИНДЕКС, умноженных на значения, содержащиеся в ячейках B12 и B13 (2 л, 4 кг) соответственно.
Формула выглядит громоздкой, однако она является универсальной, поскольку позволяет вычислить суммарные затраты на покупку двух любых продуктов в любых количествах за любой из указанных месяцев.
Пример формулы ИНДЕКС и ПОИСКПОЗ в Excel
Пример 3. Организовать фильтрацию данных по трем критериям из нескольких таблиц.
Для быстрого поиска оценки у любого ученика за любую контрольную в любой из представленных таблиц создадим три открывающихся списка, содержащих:
В ячейку, в которой будет выводится результат, запишем следующую формулу:
В качестве первого аргумента функции ИНДЕКС передана ссылка на несколько несмежных диапазонов. Для поиска номеров строки и столбца, на пересечении которых находится ячейка с искомым значением, используются функции ПОИСКПОЗ. Функции ЕСЛИ необходимы для указания порядкового номера диапазона из ссылки, определяемого номером четверти, которую можно выбрать из списка.
Пример поиска оценки ученика с фамилией Иванищев за контрольную №2, написанную во второй четверти:
Как в Excel сделать верхний индекс? Ответы на вопросы про обучение и работу
- Форма массива – функция ИНДЕКС используется для возврата одного или нескольких значений элементов массива (константы массивов в Excel, обозначаемые фигурными скобками «») или диапазона ячеек. Если в качестве первого аргумента функции была передана константа массива, функция должна быть выполнена как формула массива (Ctrl+Shift+Enter).
- Ссылочная форма – функция ИНДЕКС используется для возврата ссылки на определенную аргументами ячейку или несколько ячеек из одного либо нескольких диапазонов ячеек. Если эти диапазоны не являются смежными, можно явно указать диапазон, в котором будет произведен поиск требуемой ячейки. Формулы, принимающие в качестве аргументов не ссылочные данные, будут интерпретировать возвращенный функцией ИНДЕКС результат в качестве значения, хранящегося в ячейке, а не ссылки на нее. Например, функция =ФАКТР(ИНДЕКС(A4:B7;6;2)) вернет факториал числа, которое содержится в ячейке B6. Если результат выполнения функции ИНДЕКС используется в качестве аргумента функции, принимающей данные ссылочного типа, он будет определен в качестве ссылки на ячейку. Например, функция =СТРОКА(ИНДЕКС(A4:B7;6;2)) вернет число 4, то есть номер строки, в которой содержится ячейка B6.
Выделите знаки в ячейке или диапазоне ячеек, которые вы хотите отформатировать. На вкладке Главная в группе Шрифт нажмите кнопку вызова диалогового окна «параметры шрифта». Нажмите сочетание клавиш CTRL+1. В разделе эффектыустановите флажок надстрочный или подстрочный текст и нажмите кнопку ОК.
