Excel INDEX MATCH с несколькими критериями

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

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

Excel INDEX MATCH с несколькими критериями

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

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

{=ИНДЕКС(диапазон_возвратаПОИСКПОЗ(1, (критерии1знак равнодиапазон1) * (критерии2знак равнодиапазон2) * (…), 0))}

Где:

  • Диапазон_возврата это диапазон, из которого возвращается значение.
  • Критерии1, критерии2… – условия, которые необходимо выполнить.
  • Диапазон1, диапазон2… — диапазоны, на которых должны проверяться соответствующие критерии.

Важная заметка! Это формула массива, и она должна быть завершена с помощью Ctrl + Shift + Enter. Это заключит вашу формулу в {фигурные скобки}, что является визуальным признаком формулы массива в Excel. Не пытайтесь вводить фигурные скобки вручную, это не сработает!

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

INDEX MATCH с несколькими критериями – пример формулы

В этом примере мы будем использовать таблицу в так называемом формате «плоского файла» с каждой отдельной комбинацией критериев (в нашем случае регион-месяц-элемент) в отдельной строке. Наша цель — получить данные о продажах определенного товара в определенном регионе и месяце.

С исходными данными и критериями в следующих ячейках:

  • Диапазон_возврата (продажи) – Д2:Д13
  • Критерии1 (целевой регион) – G1
  • Критерии2 (целевой месяц) – G2
  • Критерии3 (целевой предмет) – G3
  • Диапазон1 (регионы) – A2:A13
  • Диапазон2 (месяцев) – B2:B13
  • Диапазон3 (предметы) – C2:C13

Формула принимает следующий вид:

=ИНДЕКС(D2:D13, ПОИСКПОЗ(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

Введите формулу, скажем, в G4, завершите ее, нажав Ctrl+Shift+Enter, и вы получите следующий результат:

Формула Excel INDEX MATCH с несколькими критериями

Как работает эта формула

Самая сложная часть — это функция ПОИСКПОЗ, так что давайте сначала разберемся с ней:

ПОИСКПОЗ(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

Как вы помните, ПОИСКПОЗ(искомое_значение, искомый_массив, [match_type]) ищет значение поиска в массиве поиска и возвращает относительное положение этого значения в массиве.

В нашей формуле аргументы следующие:

  • Искомое_значение: 1
  • Lookup_array: (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13)
  • Match_type: 0

1-й аргумент предельно ясен – функция ищет число 1. 3-й аргумент, установленный в 0, означает “точное совпадение”, т.е. формула возвращает первое найденное значение, которое точно равно искомому значению.

Вопрос в том, почему мы ищем «1»? Чтобы получить ответ, давайте подробнее рассмотрим массив поиска, где мы сравниваем каждый критерий с соответствующим диапазоном: целевой регион в G1 со всеми регионами (A2:A13), целевой месяц в G2 со всеми месяцами (B2:B13). ) и целевой элемент в G3 против всех элементов (C2:C13). Промежуточный результат — это 3 массива ИСТИНА и ЛОЖЬ, где ИСТИНА представляет значения, соответствующие проверенному условию. Чтобы визуализировать это, вы можете выбрать отдельные выражения в формуле и нажать клавишу F9, чтобы увидеть, что оценивает каждое выражение:

Как работает INDEX MATCH с несколькими критериями

Операция умножения преобразует значения TRUE и FALSE в 1 и 0 соответственно:

{1;1;1;1;1;1;0;0;0;0;0;0} * {0;0;1;1;0;0;0;0;1;1;0;0 } * {1;0;1;0;1;0;1;0;1;0;1;0}

А поскольку умножение на 0 всегда дает 0, результирующий массив содержит 1 только в тех строках, которые соответствуют всем критериям:

{0;0;1;0;0;0;0;0;0;0;0;0}

Приведенный выше массив переходит в lookup_array аргумент ПОИСКПОЗ. С искомое_значение = 1, функция возвращает относительное положение строки, для которой все критерии ИСТИНА (строка 3 в нашем случае). Если в массиве несколько единиц, возвращается позиция первой.

Число, возвращенное ПОИСКПОЗОМ, идет непосредственно в row_num аргумент ИНДЕКС (массив, row_num, [column_num]) функция:

=ИНДЕКС(D2:D13, 3)

И это дает результат $115, что является третьим значением в массиве D2:D13.

Формула INDEX MATCH без массива с несколькими критериями

Формула массива, рассмотренная в предыдущем примере, хорошо работает для опытных пользователей. Но если вы строите формулу для кого-то другого, и этот кто-то не знает функций массива, он может непреднамеренно ее сломать. Например, пользователь может щелкнуть вашу формулу, чтобы проверить ее, а затем нажать Enter вместо Ctrl + Shift + Enter. В таких случаях было бы разумно избегать массивов и использовать обычную формулу, которая является более надежной:

ИНДЕКС(диапазон_возвратаПОИСКПОЗ(1, ИНДЕКС((критерии1знак равнодиапазон1) * (критерии2знак равнодиапазон2) * (..), 0, 1), 0))

Для нашего примера набора данных формула выглядит следующим образом:

=ИНДЕКС(D2:D13, ПОИСКПОЗ(1, ИНДЕКС((G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0, 1), 0))

Формула INDEX MATCH без массива с несколькими критериями

Как работает эта формула

Поскольку функция ИНДЕКС может обрабатывать массивы изначально, мы добавляем еще один ИНДЕКС для обработки массива 1 и 0, созданного путем умножения двух или более массивов ИСТИНА/ЛОЖЬ. Второй ИНДЕКС настроен на 0 row_num аргумент формулы для возврата всего массива столбцов, а не одного значения. Так как в любом случае это массив из одного столбца, мы можем безопасно поставить 1 для номер_столбца:

ИНДЕКС({0;0;1;0;0;0;0;0;0;0;0;0}, 0, 1) возвращает {0;0;1;0;0;0;0;0; 0;0;0;0}

Этот массив передается в функцию ПОИСКПОЗ:

ПОИСКПОЗ(1, {0;0;1;0;0;0;0;0;0;0;0;0}, 0)

ПОИСКПОЗ находит номер строки, для которого все критерии ИСТИННЫ (точнее, относительное положение этой строки в указанном массиве), и передает это число в row_num аргумент первого ИНДЕКСА:

=ИНДЕКС(D2:D13, 3)

INDEX MATCH с несколькими критериями в строках и столбцах

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

Вот общая формула INDEX MATCH с несколькими критериями в строках и столбцах:

{=ИНДЕКС(таблица_массивСООТВЕТСТВИЕ(vlookup_value, lookup_column0), ПОИСКПОЗ(hlookup_value1 & hlookup_value2, lookup_row1 & lookup_row20))}

Где:

Таблица_массив – карта или область для поиска, т. е. все значения данных, кроме заголовков столбцов и строк.

ВПР_значение – значение, которое вы ищете по вертикали в столбце.

Lookup_column – диапазон столбцов для поиска, обычно это заголовки строк.

hlookup_value1, hlookup_value2, … – значения, которые вы ищете по горизонтали в строках.

Искомая_строка1, искомая_строка2, … – диапазоны строк для поиска, обычно это заголовки столбцов.

Важная заметка! Чтобы формула работала корректно, ее нужно вводить как формулу массива с помощью Ctrl+Shift+Enter.

Это разновидность классической формулы двустороннего поиска, которая ищет значение на пересечении определенной строки и столбца. Разница в том, что вы объединяете несколько значений и диапазонов hlookup для оценки нескольких заголовков столбцов. Чтобы лучше понять логику, рассмотрим следующий пример.

Поиск по матрице с несколькими критериями — пример формулы

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

  • Таблица_массив – В3:Е4
  • ВПР_значение (целевой элемент) – H1
  • Lookup_column (Заголовки строк: элементы) – A3:A4
  • Hlookup_value1 (целевой регион) – H2
  • Hlookup_value2 (целевой поставщик) — H3
  • Lookup_row1 (Заголовки столбцов 1: регионы) — B1:E1
  • Lookup_row2 (Заголовки столбцов 2: поставщики) — B2:E2

А теперь введите аргументы в общую формулу, описанную выше, и вы получите такой результат:

=ИНДЕКС(B3:E5, ПОИСКПОЗ(H1,A3:A5,0), ПОИСКПОЗ(H2&H3,B1:E1&B2:E2,0))

Не забудьте завершить формулу, нажав сочетание клавиш Ctrl + Shift + Enter, и ваш поиск по матрице с несколькими критериями будет выполнен успешно:

INDEX MATCH с несколькими критериями в строках и столбцах

Как работает эта формула

Поскольку мы ищем по вертикали и по горизонтали, нам нужно указать номера строк и столбцов для функции ИНДЕКС (массив, номер_строки, номер_столбца).

Row_num доставляется функцией MATCH(H1, A3:A5, 0), которая сравнивает целевой элемент (яблоки) в H1 с заголовками строк в A3:A5. Это дает результат 1, потому что «Яблоки» — это первый элемент в указанном диапазоне.

Столбец_номер вычисляется путем объединения 2 поисковых значений и 2 поисковых массивов: ПОИСКПОЗ(H2&H3, B1:E1&B2:E2, 0))

Ключевым фактором успеха является то, что значения поиска должны точно соответствовать заголовкам столбцов и быть объединены в том же порядке. Чтобы визуализировать это, выберите первые два аргумента в формуле ПОИСКПОЗ, нажмите F9, и вы увидите, что оценивает каждый аргумент:

ПОИСКПОЗ(“Северный поставщик 2”, {“Северный поставщик 1”, “Северный поставщик 2”, “Южный поставщик 1”, “Южный поставщик 2”}, 0)

Поскольку «NorthVendor 2» является вторым элементом в массиве, функция возвращает 2.

В этот момент наша длинная двумерная формула ПОИСКПОЗ ИНДЕКС трансформируется в эту простую:

=ИНДЕКС(B3:E5, 1, 2)

И возвращает значение на пересечении 1-й строки и 2-го столбца в диапазоне B3:E5, что является значением в ячейке C3.

Вот как можно найти несколько критериев в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Практическая рабочая тетрадь для скачивания

Excel ИНДЕКС СООТВЕТСТВУЕТ нескольким критериям (файл .xlsx)

Как найти несколько условий в Excel

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

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

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