Функция ИНДЕКС в Excel используется для извлечения данных из таблицы, если вы знаете номер строки и столбца, в которых расположены данные.
Например, в таблице ниже вы можете использовать эту функцию, чтобы получить результаты экзамена по физике от Андрея, зная номера строк и столбцов, в которых находятся эти данные.
Что возвращает функция
Возвращает данные из определенной строки и столбца табличных данных.
Синтаксис
= ИНДЕКС (array, row_num, [col_num]) — английская версия
= ИНДЕКС (array, row_num, [col_num], [area_num]) — английская версия
= ИНДЕКС (массив; номер_строки; [номер_столбца]) — русская версия
= INDEX (link; line_number; [column_number]; [area_number]) — русская версия
Аргументы функции
- array (массив) — диапазон ячеек или массив данных для поиска;
- row_num (row_number) — номер строки, в которой находятся запрашиваемые данные;
- [col_num] ([column_number]) (необязательный аргумент) — номер столбца, в котором находятся запрашиваемые данные. Это необязательный аргумент. Но если аргументы функции не определяют критерии для line_num (line_number), вы должны указать аргумент column_num (column_number);
- [area_num] ([area_num]) — (необязательно) — если аргумент массива состоит из нескольких диапазонов, это число будет использоваться для выбора всех диапазонов.
Дополнительная информация
- Если номер строки или столбца равен «0», функция возвращает данные для всей строки или столбца;
- Если функция используется перед ссылкой на ячейку (например, A1), она возвращает ссылку на ячейку вместо значения (см. Примеры ниже);
- Чаще всего ИНДЕКС используется вместе с функцией ПОИСКПОЗ);
- В отличие от функции ВПР (VLOOKUP), функция ИНДЕКС может возвращать данные либо справа от желаемого значения, либо слева;
- Функция используется в двух формах — Массив данных и Форма ссылки на данные:
— Модуль «Массив» используется, когда вы хотите найти значения на основе определенного количества строк и столбцов таблицы;
— Модуль «Связи данных» используется при поиске значений в нескольких таблицах (используйте аргумент [area_num] ([area_number]), чтобы выбрать таблицу, и только затем сориентируйте функцию по номерам строк и столбцов.
Примеры использования функции ИНДЕКС в Excel
Пример 1. Ищем результаты экзамена по физике для Алексея
Допустим, у нас есть результаты экзаменов в табличной форме для нескольких студентов:
Чтобы найти результаты экзамена по физике для Андрея, нам понадобится формула:
= ИНДЕКС ($ B $ 3: $ E $ 9,3,2) — английская версия
= ИНДЕКС ($ B $ 3: $ E $ 9; 3; 2) — русская версия
В формуле мы определили аргумент диапазона данных, в котором будем искать данные $ B $ 3: $ E $ 9. Затем они указали строку номер «3», которая содержит результаты экзамена для Андрея, и номер столбца «2», в котором находятся результаты экзамена по физике.
Пример 2. Создаем динамический поиск значений с использованием функций ИНДЕКС и ПОИСКПОЗ
Не всегда можно вручную указать номера строк и столбцов. У вас может быть огромная таблица данных, в которой вы можете динамически отображать данные, чтобы функция автоматически определяла указанное имя или исследование в ячейках и выдавала правильный результат.
Ниже приведен пример визуализации динамических данных:
Для динамического отображения данных мы используем комбинацию функций ИНДЕКС и ПОИСКПОЗ).
Эта формула поможет нам добиться результата:
= ИНДЕКС ($ B $ 3: $ E $ 9, СРАВНИТЬ ($ G $ 4, $ A $ 3: $ A $ 9,0), СРАВНИТЬ ($ H $ 3, $ B $ 2: $ E $ 2,0)) — английская версия
= ИНДЕКС ($ B $ 3: $ E $ 9; ПОИСК ($ G $ 4; $ A $ 3: $ A $ 9; 0); ПОИСК ($ H $ 3; $ B $ 2: $ E $ 2; 0)) — Русская версия
В приведенной выше формуле без использования сложного программирования мы сделали визуализацию данных динамической с помощью функции СРАВНИТЬ.
Динамическое отображение строки определяется следующей частью формулы –
MATCH ($ G $ 4, $ A $ 3: $ A $ 9,0) — английская версия
ПОИСК ($ G $ 4; $ A $ 3: $ A $ 9; 0) — Русская версия
Просканируйте имена студентов и определите значение поиска (в нашем случае $ G $ 4). Затем он возвращает номер строки для поиска в наборе данных. Например, если значение поиска — Алексей, функция вернет «1», если это Максим, она вернет «4» и так далее.
Динамическое отображение данных столбца определяется следующей частью формулы –
MATCH ($ H $ 3, $ B $ 2: $ E $ 2.0) — английская версия
ПОИСК ($ H $ 3; $ B $ 2: $ E $ 2; 0) — Русская версия
Он сканирует имена объектов и определяет значение поиска (в нашем случае $ H $ 3). Затем он возвращает номер столбца для поиска в наборе данных. Например, если значение поиска — Math, функция вернет «1», если это Physics, функция вернет «2» и так далее.
Пример 3. Создаем динамический поиск значений с использованием функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ) и выпадающего списка
В приведенном выше примере мы вручную ввели имена учащихся и имена предметов. Вы можете сэкономить время на вводе данных, используя раскрывающиеся списки. Это верно, когда объем данных огромен.
Используя раскрывающиеся списки, просто выберите имя студента из списка, и функция автоматически найдет и заменит необходимые данные.
См. Пример ниже:
Используя такой подход, вы можете создать удобный дашборд, например, для учителя. При этом не нужно заниматься фильтрацией данных или прокруткой листа со студентами, чтобы найти результаты экзамена конкретного студента, просто выберите имя, и результаты будут динамически отражены в лаконичной и удобной форме.
Чтобы выполнить динамическую замену данных с помощью функций ИНДЕКС и ПОИСКПОЗ и раскрывающегося списка, мы используем ту же формулу, что и в Примере 2:
= ИНДЕКС ($ B $ 3: $ E $ 9, СРАВНИТЬ ($ G $ 4, $ A $ 3: $ A $ 9,0), СРАВНИТЬ ($ H $ 3, $ B $ 2: $ E $ 2,0)) — английская версия
= ИНДЕКС ($ B $ 3: $ E $ 9; ПОИСК ($ G $ 4; $ A $ 3: $ A $ 9; 0); ПОИСК ($ H $ 3; $ B $ 2: $ E $ 2; 0)) — Русская версия
Единственное отличие от Примера 2 в том, что мы создадим раскрывающиеся списки, в которые вводятся имя и тема:
- Выберите ячейку, в которой мы хотим отобразить раскрывающийся список с именами студентов;
- Щелкните вкладку «Данные» => Инструменты данных => Проверка данных;
- В окне «Проверка данных» вкладки «Настройки» в подразделе Разрешить выберите «Список”;
- В качестве источника мы должны выбрать диапазон ячеек, в котором указаны имена учеников;
- Нажмите ОК
Теперь у вас есть раскрывающийся список имен учащихся в ячейке G5. Точно так же вы можете создать раскрывающийся список с элементами.
Пример 4. Использование трехстороннего поиска с помощью INDEX (ИНДЕКС) / MATCH (ПОИСКПОЗ)
Функцию ИНДЕКС можно использовать для обработки трехсторонних запросов.
Что такое трехсторонний поиск?
В приведенных выше примерах мы использовали таблицу с оценками учащихся по разным предметам. Это пример двунаправленного поиска, поскольку мы используем две переменные для получения оценки (имя учащегося и предмет).
Теперь предположим, что к концу года студент сдал экзамены трех уровней: вступительный, семестр и выпускной».
Трехстороннее исследование — это возможность получить оценки учащегося по заданному предмету на определенном уровне экзамена.
Вот пример трехстороннего поиска:
В приведенном выше примере, помимо выбора имени студента и названия предмета, вы также можете выбрать уровень экзамена. В зависимости от уровня экзамена формула возвращает соответствующее значение из одной из трех таблиц.
Для таких расчетов нам поможет формула:
= ИНДЕКС (($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23), СРАВНИТЬ ($ G $ 4, $ A $ 3: $ A $ 7.0), MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0), IF ($ H $ 2 = «Вводный», 1, IF ($ H $ 2 = «Полугодовой», 2 , 3))) — английская версия
= ИНДЕКС (($ B $ 3: $ E $ 7; $ B $ 11: $ E $ 15; $ B $ 19: $ E $ 23); ПОИСК ($ G $ 4; $ A $ 3: $ A $ 7.0)); ПОИСК ($ H $ 3; $ B $ 2: $ E $ 2; 0); SE ($ H $ 2 = «Вводный»; 1; SE ($ H $ 2 = «Полугодовой»; 2; 3))) — русская версия
Давайте проанализируем эту формулу, чтобы понять, как она работает.
Эта формула принимает четыре аргумента. Функция ИНДЕКС — одна из тех функций Excel, которые имеют более одного синтаксиса.
= ИНДЕКС (array, row_num, [col_num]) — английская версия
= ИНДЕКС (array, row_num, [col_num], [area_num]) — английская версия
= ИНДЕКС (массив; номер_строки; [номер_столбца]) — русская версия
= INDEX (link; line_number; [column_number]; [area_number]) — русская версия
Во всех приведенных выше примерах мы использовали первый синтаксис, но для трехстороннего поиска вам необходимо использовать второй синтаксис.
Давайте посмотрим на каждую часть формулы на основе второго синтаксиса.
- array (массив) — ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): вместо использования одного массива в данном случае мы использовали три массива в скобках.
- row_num (row_number) — ПОИСКПОЗ ($ G $ 4, $ A $ 3: $ A $ 7.0): функция ПОИСКПОЗ используется для поиска имени учащегося для ячейки $ G $ 4 из списка всех учащихся.
- col_num (column_number) — ПОИСКПОЗ ($ H $ 3, $ B $ 2: $ E $ 2.0): функция ПОИСКПОЗ используется для поиска имени элемента для ячейки $ H $ 3 из списка всех элементов.
- [area_num] ([area_num]) — ЕСЛИ ($ H $ 2 = «Вводный», 1, IF ($ H $ 2 = «Полугодовой», 2,3)): значение номера площади указывает ИНДЕКС какой массив данных выбрать. В этом примере у нас есть три массива в первом аргументе. Если вы выберете «Вводный» из раскрывающегося меню, функция ЕСЛИ вернет «1», а функция ИНДЕКС выберет первый массив из трех массивов ($ B $ 3: $ E $ 7).
Я уверен, что вы подробно изучили, как работает функция ИНДЕКС в Excel!
