Функция ИНДЕКС в Excel - Примеры формул + БЕСПЛАТНОЕ видео

Функция ИНДЕКС в Excel (примеры + видео)

Когда использовать функцию ИНДЕКС в Excel

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

Что это возвращает

Он возвращает значение из таблицы для указанного номера строки и номера столбца.

Синтаксис

= ИНДЕКС (массив; номер_строки; [номер_столбца])
= ИНДЕКС (массив; номер_строки; [номер_столбца]; [номер_площади])

Функция ИНДЕКС имеет 2 синтаксиса. Первый используется в большинстве случаев, однако в случае трехстороннего поиска используется второй (описанный в примере 5).

Входные аргументы

• множество - а диапазон ячеек или константа массива.
• row_num - номер строки, из которой должно быть извлечено значение.
• [col_num] - номер столбца, из которого должно быть извлечено значение. Хотя это необязательный аргумент, но если row_num не указан, его необходимо указать.
• [area_num] - (Необязательно) Если аргумент массива состоит из нескольких диапазонов, это число будет использоваться для выбора ссылки из всех диапазонов.

Дополнительные примечания (скучный материал… но важно знать)

• Если номер строки или столбца равен 0, он возвращает значения всей строки или столбца соответственно.
• Если функция ИНДЕКС используется перед ссылкой на ячейку (например, A1 :), она возвращает ссылку на ячейку вместо значения (см. Примеры ниже).
• Наиболее широко используется вместе с функцией ПОИСКПОЗ.
• В отличие от ВПР, функция ИНДЕКС может возвращать значение слева от значения поиска.
• Функция ИНДЕКС имеет две формы - форма массива и справочная форма.
  • «Форма массива» - это место, где вы выбираете значение на основе номера строки и столбца из данной таблицы.
  • «Справочная форма» - это когда есть несколько таблиц, и вы используете аргумент area_num, чтобы выбрать таблицу, а затем получить значение в ней, используя номер строки и столбца (см. Живой пример ниже).

Функция ИНДЕКС в Excel - Примеры

Вот шесть примеров использования функции ИНДЕКС Excel.

Пример 1. Поиск оценок Тома по физике (двусторонний поиск)

Предположим, у вас есть набор данных, как показано ниже:

Чтобы узнать оценки Тома по физике, используйте следующую формулу:

= ИНДЕКС ($ B $ 3: $ E $ 10,3,2)

Эта формула ИНДЕКС определяет массив как $ B $ 3: $ E $ 10, в котором есть оценки для всех предметов. Затем он использует номер строки (3) и номер столбца (2) для получения оценок Тома по физике.

Пример 2 - Создание динамического значения LOOKUP с помощью функции MATCH

Не всегда возможно указать номер строки и номер столбца вручную. У вас может быть огромный набор данных или вы можете захотеть сделать его динамическим, чтобы он автоматически определял имя и / или тему, указанные в ячейках, и давал правильный результат.

Что-то вроде того, что показано ниже:

Это можно сделать с помощью комбинации ИНДЕКС и функции ПОИСКПОЗ.

Вот формула, которая сделает поисковые значения динамическими:

= ИНДЕКС ($ B $ 3: $ E $ 10, ПОИСКПОЗ ($ G $ 5, $ A $ 3: $ A $ 10,0), ПОИСКПОЗ ($ H $ 4, $ B $ 2: $ E $ 2,0))

В приведенной выше формуле вместо жесткого кодирования номера строки и номера столбца используется функция ПОИСКПОЗ, чтобы сделать его динамическим.

• Динамический номер строки задается следующей частью формулы - ПОИСКПОЗ ($ G $ 5, $ A $ 3: $ A $ 10,0). Он сканирует имена студентов и определяет значение поиска (в данном случае $ G $ 5). Затем он возвращает номер строки искомого значения в наборе данных. Например, если значение поиска - Мэтт, оно вернет 1, если это Боб, оно вернет 2 и так далее.
• Номер динамического столбца задается следующей частью формулы - ПОИСКПОЗ ($ H $ 4, $ B $ 2: $ E $ 2,0). Он сканирует имена субъектов и определяет значение поиска (в данном случае $ H $ 4). Затем он возвращает номер столбца искомого значения в наборе данных. Например, если значение поиска Math, оно вернет 1, если это Physics, оно вернет 2 и так далее.

Пример 3 - Использование раскрывающихся списков в качестве значений поиска

В приведенном выше примере нам нужно вручную ввести данные. Это может занять много времени и привести к ошибкам, особенно если у вас есть огромный список значений поиска.

В таких случаях хорошей идеей является создание раскрывающегося списка значений поиска (в данном случае это могут быть имена учащихся и предметы), а затем просто выбирать из списка. На основе выбора формула автоматически обновит результат.

Что-то вроде того, что показано ниже:

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

Как это сделать:

Формула, использованная в этом случае, такая же, как в Примере 2.

= ИНДЕКС ($ B $ 3: $ E $ 10, ПОИСКПОЗ ($ G $ 5, $ A $ 3: $ A $ 10,0), ПОИСКПОЗ ($ H $ 4, $ B $ 2: $ E $ 2,0))

Значения поиска преобразованы в раскрывающиеся списки.

Вот шаги для создания раскрывающегося списка Excel:

• Выберите ячейку, в которой вы хотите открыть раскрывающийся список. В этом примере в G4 нам нужны имена учеников.
• Перейдите в Data -> Data Tools -> Data Validation.
• В диалоговом окне «Проверка данных» на вкладке настроек выберите «Список» в раскрывающемся списке «Разрешить».
• В источнике выберите $ A $ 3: $ A $ 10
• Щелкните ОК.

Теперь в ячейке G5 появится раскрывающийся список. Точно так же вы можете создать его в H4 для предметов.

Пример 4 - Возвращаемые значения из всей строки / столбца

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

А что, если вы хотите получить все оценки студента. Это может позволить вам найти максимальный / минимальный балл этого ученика или общее количество баллов, набранных по всем предметам.

Говоря простым языком, вы хотите сначала получить всю строку баллов для ученика (скажем, Боба), а затем в рамках этих значений определить наивысший балл или общую сумму всех баллов.

Вот уловка.

В функции ИНДЕКС Excel при вводе номер столбца как 0, он вернет значения всей этой строки.

Итак, формула для этого будет:

= ИНДЕКС ($ B $ 3: $ E $ 10, ПОИСКПОЗ ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)

Теперь это формула. если использовать как есть, вернет #VALUE! ошибка. В то время как он отображает ошибку, в бэкэнде он возвращает массив, в котором есть все оценки для Тома - {57,77,91,91}.

Если вы выберете формулу в режиме редактирования и нажмете F9, вы сможете увидеть массив, который она возвращает (как показано ниже):

Точно так же, в зависимости от того, что такое значение поиска, когда номер столбца указан как 0 (или оставлен пустым), он возвращает все значения в строке для значения поиска.

Теперь, чтобы подсчитать общий балл, полученный Томом, мы можем просто использовать приведенную выше формулу в функции СУММ.

= СУММ (ИНДЕКС ($ B $ 3: $ E $ 10, ПОИСКПОЗ ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))

Аналогично, для вычисления наивысшего балла мы можем использовать MAX / LARGE, а для вычисления минимума - MIN / SMALL.

Пример 5 - Трехсторонний поиск с использованием INDEX / MATCH

Функция ИНДЕКС Excel предназначена для обработки трехстороннего поиска.

Что такое трехсторонний поиск?

В приведенных выше примерах мы использовали одну таблицу с оценками учащихся по разным предметам. Это пример двустороннего поиска, поскольку мы используем две переменные для получения оценки (имя учащегося и предмет).

Теперь предположим, что за год у студента есть экзамены трех разных уровней: модульный тест, промежуточный экзамен и итоговый экзамен (это то, что я сдавал, когда был студентом).

Трехсторонний поиск - это возможность получить оценки учащегося по указанному предмету на заданном уровне экзамена. Это сделало бы поиск трехсторонним, поскольку есть три переменных (имя студента, имя предмета и уровень экзамена).

Вот пример трехстороннего поиска:

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

Вот формула, используемая в ячейке H4:

= ИНДЕКС (($ 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), IF ($ H $ 2 = «Модульный тест», 1, IF ($ H $ 2 = «Среднесрочный период», 2,3)))

Давайте разберемся с этой формулой, чтобы понять, как она работает.

Эта формула принимает четыре аргумента. ИНДЕКС - одна из тех функций в Excel, которые имеют более одного синтаксиса.

= ИНДЕКС (массив; номер_строки; [номер_столбца])
= ИНДЕКС (массив; номер_строки; [номер_столбца]; [номер_площади])

До сих пор во всех приведенных выше примерах мы использовали первый синтаксис, но для трехстороннего поиска нам нужно использовать второй синтаксис.

Теперь давайте посмотрим на каждую часть формулы на основе второго синтаксиса.

• array - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): в данном случае вместо использования одного массива мы использовали три массива в скобках.
• row_num - ПОИСКПОЗ ($ G $ 4, $ A $ 3: $ A $ 7,0): функция ПОИСКПОЗ используется для поиска позиции имени учащегося в ячейке $ G $ 4 в списке имен учащихся.
• col_num - ПОИСКПОЗ ($ H $ 3, $ B $ 2: $ E $ 2,0): функция ПОИСКПОЗ используется для поиска позиции имени субъекта в ячейке $ H $ 3 в списке имен субъектов.
• [area_num] - IF ($ H $ 2 = «Unit Test», 1, IF ($ H $ 2 = «Midterm», 2,3)): значение номера области сообщает функции INDEX, какой массив выбрать. В этом примере у нас есть три массива в первом аргументе. Если вы выбираете Unit Test из раскрывающегося списка, функция IF возвращает 1, а функции INDEX выбирают 1-й массив из трех массивов (это $ B $ 3: $ E $ 7).

Пример 6 - Создание ссылки с помощью функции ИНДЕКС (динамические именованные диапазоны)

Это одно из безумных применений функции ИНДЕКС в Excel.

Возьмем простой пример.

У меня есть список имен, как показано ниже:

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

Вот формула:

= ИНДЕКС ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))

Эта функция просто подсчитывает количество непустых ячеек и возвращает последний элемент из этого списка (работает только тогда, когда в списке нет пробелов).

Итак, что здесь происходит с магией.

Если вы поместите формулу перед ссылкой на ячейку, формула вернет ссылку на ячейку с совпадающим значением (вместо самого значения).

= A2: ИНДЕКС ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))

Вы ожидаете, что приведенная выше формула вернет = A2: «Джош» (где Джош - последнее значение в списке). Однако он возвращает = A2: A9 и, следовательно, вы получаете массив имен, как показано ниже:

Одним из практических примеров, где может быть полезна эта техника, является создание динамических именованных диапазонов.

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

Примечание. Я изо всех сил старался проверить это руководство, но если вы обнаружите какие-либо ошибки или орфографические ошибки, сообщите мне об этом 🙂

Функция ИНДЕКС в Excel - видеоурок

• Функция ВПР в Excel.
• Функция Excel HLOOKUP.
• Функция НЕПРЯМАЯ в Excel.
• Функция ПОИСКПОЗ в Excel.
• Функция смещения Excel.

Вам также могут понравиться следующие руководства по Excel:

• VLOOKUP Vs. ИНДЕКС / МАТЧ
• Соответствие индекса Excel
• Поиск и возврат значений во всей строке / столбце.