Индекс соответствия множественным критериям

Индексирование строк и столбцов с несколькими критериями

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

Индекс соответствия множественным критериям

Как использовать формулу INDEX + MATCH для соответствия нескольким критериям?

Здесь мы объясняем, как использовать формулу индекс + соответствие для соответствия нескольким критериям для строк и столбцов с примерами.

Вы можете скачать этот шаблон Excel для соответствия индексов по нескольким критериям здесь — шаблон Excel для соответствия индексов по нескольким критериям

Пример №1 — Формула ИНДЕКС + ПОИСКПОЗ

Не большинство функций поиска пользователей Excel, кроме ВПР, причин может быть очень много. В любом случае, давайте сделаем простое введение в эту формулу, прежде чем мы перейдем к продвинутому уровню.

Например, посмотрите на приведенную ниже структуру данных в Excel.

ИНДЕКС + МАТЧ Формула 1

У нас есть названия торговых представителей и соответствующие им значения продаж. С другой стороны, у нас есть раскрывающийся список «Торговый представитель» в ячейке D2.

ИНДЕКС + МАТЧ Формула 1-1

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

Проблема в том, что мы не можем применить формулу VLOOKUP, потому что значение поиска «Торговый представитель» находится справа от столбца результатов «Продажи», поэтому в этих случаях мы можем использовать формулу комбинированного значения поиска INDEX + MATCH.

ИНДЕКС + МАТЧ Формула 1-2

INDEX ищет указанное значение номера строки в диапазоне A2: A11, и в этом диапазоне нам необходимо указать, из какой строки нам нужно получить значение продаж. Это значение строки основано на имени «Торговый представитель», выбранном в раскрывающемся списке в Excel, поэтому функция ПОИСКПОЗ ищет номер строки «Торговый представитель» в диапазоне B2: B11 и возвращает номер строки сопоставленного значения. .

Пример # 2 — Несколько критериев в формуле INDEX + MATCH

Теперь у нас есть структура данных, подобная приведенной ниже.

Множественные критерии в INDEX + MATCH Формула 1

У нас есть ежемесячные значения продаж «Торговый представитель». Из этой таблицы нам нужны динамические результаты, например, в ячейке A15, я создал раскрывающийся список «Торговый представитель», а в ячейке B14 я создал раскрывающийся список «Месяц».

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

Например, если я выберу «Rep 8» и «Apr», тогда он должен показать продажную стоимость «Rep 8» за месяц «Apr».

Несколько критериев в формуле INDEX + MATCH 1-1

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

Шаг 1: Откройте функцию ИНДЕКС в ячейке B15.

Несколько критериев в формуле INDEX + MATCH 1-2

Шаг 2: Первым аргументом функции ИНДЕКС является «Массив», т. Е. Из какого диапазона ячеек нам нужен результат. Итак, в этом случае нам нужны значения продаж, поэтому выберите диапазон ячеек от B2 до G11.

Несколько критериев в формуле INDEX + MATCH 1-3

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

Несколько критериев в INDEX + MATCH Formula 1-4

Шаг 4: ЗНАЧЕНИЕ ПРОСМОТРА функции ПОИСКПОЗ — «Торговый представитель», поэтому выберите ячейку A15 в качестве ссылки.

Множественные критерии в INDEX + MATCH Формула 1-5

Шаг 5: Массив поиска — это диапазон имен «Торговый представитель» в основной таблице. Поэтому выберите диапазон от A2 до A11.

Пример 1-6

Шаг 6: Тип соответствия функции ПОИСКПОЗ будет точным, поэтому введите ноль в качестве значения аргумента.

Пример 1-7

Шаг 7: Следующим аргументом функции ИНДЕКС является «Номер столбца», т. Е. Из выбранного диапазона ячеек, из которого нам нужен результат. Это зависит от месяца, который мы выбираем из раскрывающегося списка ячейки B14. Итак, чтобы получить номер столбца автоматически, откройте другую функцию ПОИСКПОЗ.

Пример 1-8

Шаг 8: На этот раз поисковым значением будет название месяца, поэтому выберите ячейку B14 в качестве ссылки.

Пример 1-9

Шаг 9: Подстановочный массив будет представлять собой месячный диапазон ячеек в основной таблице, то есть от B1 до G1.

Пример 1-10

Шаг 10: Последний аргумент — тип соответствия; выберите «Точное соответствие» в качестве критерия. Закройте две скобки и нажмите клавишу ввода, чтобы получить результат.

Пример 1-11

Как мы видим выше, мы выбрали «Rep 6» и «Apr» в качестве месяца, и наша формула вернула значение продаж за месяц «Apr» для «Rep 6».

Заметка: Ячейка желтого цвета — это ссылка для вас.

То, что нужно запомнить

  • Комбинация ИНДЕКС + ПОИСКПОЗ может быть более действенной, чем формула ВПР.
  • INDEX & MATCH может сопоставлять заголовки строк и столбцов и возвращать результат из средней таблицы.
  • MATCH может возвращать номер строки и номер столбца заголовков таблицы как для строк, так и для столбцов.

УЗНАТЬ БОЛЬШЕ >>

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *