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

В руководстве показано, как использовать Excel XLOOKUP с несколькими критериями, а также объясняются преимущества и ограничения этого метода.

В Excel есть замечательная функция XLOOKUP, которая позволяет легко находить определенные значения в таблицах. И угадай что? Он не просто ищет что-то одно, он также может искать, используя разные условия. В этой статье мы покажем вам, как комбинировать различные критерии, чтобы найти идеальное соответствие вашим данным. Вы будете поражены тем, как много можно сделать с помощью этой функции!

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

Прежде чем углубляться в множество критериев, давайте быстро рассмотрим синтаксис XLOOKUP, сосредоточив внимание на самом главном:

XLOOKUP(искомое_значение, искомый_массив, возвращаемый_массив, [if_not_found], [match_mode], [search_mode])

Для наших целей нас особенно интересуют первые три аргумента:

искомое_значение — значение, которое вы ищете.
искомый_массив — диапазон, в котором вы хотите искать искомое значение.
return_array — диапазон, из которого следует вернуть соответствующее значение.

Для более глубокого понимания вы можете изучить более подробную информацию в статье: Функция Excel XLOOKUP – синтаксис и использование.

Хотя XLOOKUP предназначен для обработки только одного значения поиска, у нас есть хитрости, позволяющие преодолеть это ограничение 🙂

Формула 1. Булева логика

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

XLOOKUP(1, (искомый_массив1 = искомое_значение1) * (искомый_массив2 = искомое_значение2) * (…), return_array)

Вот суть: XLOOKUP ищет число 1, создавая временный массив поиска, заполненный 0 (нет совпадения) и 1 (совпадение). Сначала вы проверяете каждое искомое значение на соответствие всем значениям в соответствующем массиве поиска, создавая массив значений TRUE и FALSE. А затем вы умножаете эти массивы, превращая ИСТИНА и ЛОЖЬ в 1 и 0 и формируя единый массив поиска. Этот окончательный массив имеет 1 для элементов, соответствующих всем критериям, а XLOOKUP возвращает первое найденное совпадение.

Например, чтобы найти поставщика целевого товара в целевом регионе, общая формула будет такой:

=XLOOKUP(1, (Items=Target_Item) * (Regions=Target_Region), Поставщики)

Формула 2. Конкатенация

Другой подход предполагает объединение всех целевых значений (условий) в одно искомое_значение с помощью оператора конкатенации (&). Затем найдите это значение в объединенном Lookup_array:

XLOOKUP(искомое_значение1 & искомое_значение2 &…, искомый_массив1 & искомый_массив2 &…, return_array)

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

=XLOOKUP(Целевой_предмет и Целевой_регион, Предметы и регионы, Поставщики)

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

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

Как использовать XLOOKUP с несколькими критериями

Теперь, когда мы рассмотрели основную формулу, давайте углубимся в практическое применение. Представьте, что вам нужно найти поставщика по трем критериям: название товара, регион и тип доставки. Эту задачу можно решить с помощью двух разных формул, подробно описанных ниже. Хотя обе формулы приводят к одному и тому же результату, они идут разными путями.

Формула XLOOKUP с несколькими критериями: булева логика

Для нашего образца набора данных используйте следующую формулу, чтобы получить поставщика на основе трех критериев в ячейках G4, G5 и G6:

=XLOOKUP(1, (A3:A22=G4) * (B3:B22=G5) * (C3:C22=G6), D3:D22)

Вот описание того, как работает эта формула:

Тестирование индивидуальных условий
Сначала формула сравнивает целевой элемент в ячейке G4 со всеми элементами в диапазоне A3:A22. Аналогичным образом он проверяет регион в G5 по всем регионам в B3:B22 и тип доставки в G6 по всем службам доставки в C3:C22. Эти сравнения генерируют три массива значений TRUE и FALSE, например:
{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE;…} * {FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE;… } * {FALSE; TRUE; FALSE ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;…}
Операция умножения
Операция умножения преобразует значения TRUE и FALSE в 1 и 0 соответственно, образуя единый массив поиска. Умножение на 0 гарантирует, что 1 будут представлены только элементы, соответствующие всем критериям.
{0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0}
XLOOKUP в действии
Этот массив становится массивом поиска для XLOOKUP, где он ищет число 1. 10-е значение в массиве, равное 1, соответствует 10-й записи в наборе данных. XLOOKUP находит его и возвращает 10-е значение в return_array (D3:D22), то есть «Илия».

Формула XLOOKUP с несколькими критериями: конкатенация

Ту же задачу можно решить с помощью этой формулы:

=XLOOKUP(G4 и G5 и G6, A3:A22 и B3:B22 и C3:C22, D3:D22)

Вот разбивка этого подхода:

Объединение значений поиска
Объедините все три искомых значения (G4, G5 и G6) в одно искомое_значение с помощью оператора конкатенации. Проще говоря, мы создаем комбинированную строку для поиска: «OrangesWestExpedited».
Объединение массивов поиска
Объедините соответствующие диапазоны A3:A22, B3:B22 и C3:C22, чтобы создать один искомый_массив, например:
{“Apples EastStandard”; “Apples EastExpedited”; “Apples EastOvernight”; “ApplesWestStandard”; “ApplesWestExpedited”; “ApplesWestOvernight”; “Oranges EastStandard”; “Oranges EastExpedited”; “OrangesWestStandard”; “OrangesWestExpedited”; …}
XLOOKUP к вашим услугам
XLOOKUP ищет объединенное искомое значение в объединенном искомом массиве. Когда он идентифицирует соответствующую строку, он возвращает соответствующее значение из возвращаемого массива (D3:D22).

Кончик. Чтобы получить представление о ваших формулах Excel, вы можете использовать клавишу F9 для оценки формулы и видеть все промежуточные результаты в строке формул.

Несколько критериев XLOOKUP с логическими операторами

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

Например, рассмотрим сценарий получения поставщика товара в G4, региона, не соответствующего G5, и скидки, превышающей G6. Формула достижения этого выглядит следующим образом:

=XLOOKUP(1, (A3:A22=G4) * (B3:B22<>G5) * (C3:C22>G6), D3:D22)

Приблизительное совпадение по нескольким критериям XLOOKUP

Базовая формула XLOOKUP может искать точное или приблизительное совпадение, управляемое пятым аргументом match_mode. При работе с несколькими условиями возникает проблема поиска значения, которое приблизительно соответствует одному из критериев.

Решение включает в себя первую фильтрацию записей, которые не соответствуют условию точного соответствия, что достигается с помощью функции ЕСЛИ или ФИЛЬТР. Затем отфильтрованный массив передается в XLOOKUP, предлагая приблизительное совпадение — вы выбираете между ближайшим меньшим элементом (match_mode установлен в -1) или ближайшим большим элементом (match_mode установлен в 1).

В примере сценария с названиями товаров в столбце A, количествами в столбце B и скидками в столбце C, с целью найти скидку для конкретного товара в ячейке F4 и количества в F5, формула строится следующим образом:

=XLOOKUP(F5, ЕСЛИ(A3:A22=F4, B3:B22), C3:C22,, -1)

Разбивая его, внутренняя логика фильтрует элементы, соответствующие F4, и соответствующие им количества:

ЕСЛИ(A3:A22=F4, B3:B22)

В результате получается массив, состоящий из чисел количества для совпадающих товаров и FALSE для несовпадающих:

{…;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;20;50;100;150;200;250;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;…}

При целевом количестве 75 в F5 функция XLOOKUP с параметром match_mode, установленным в -1, ищет следующий меньший элемент в приведенном выше массиве, находит 50 и возвращает соответствующую скидку из столбца C (3%).

Альтернативно вы можете выполнить фильтрацию с помощью функции ФИЛЬТР:

=XLOOKUP(F5, ФИЛЬТР(B3:B22, A3:A22=F4), ФИЛЬТР(C3:C22, A3:A22=F4),, -1)

В этой версии вы фильтруете количества (B3:B22) на основе целевого товара (A3:A22=F4) для массива поиска, а для возвращаемого массива вы фильтруете скидки (C3:C22) для того же целевого товара.

XLOOKUP с несколькими условиями (логика ИЛИ)

В наших предыдущих примерах мы углубились в логику AND, находя значение, соответствующее всем указанным критериям. Теперь давайте рассмотрим, как использовать XLOOKUP с логикой ИЛИ, находя значения, соответствующие хотя бы одному из условий.

В зависимости от того, находятся ли ваши критерии в одном столбце или в разных столбцах, существует два варианта формулы.

Формула XLOOKUP для нескольких критериев ИЛИ в одном столбце

В этой формуле используется булева логика с операцией сложения (+), представляющей логику ИЛИ:

XLOOKUP(1, (просматриваемый_массив = искомое_значение1) + (просматриваемый_массив = искомое_значение2) + (…), return_array)

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

Например, чтобы получить первую запись в приведенном ниже наборе данных, где регион имеет значение G4 или I4, используется формула:

=XLOOKUP(1, (B3:B22=G4) + (B3:B22=I4), A3:D22)

Примечание. Если есть две или более записи, соответствующие какому-либо условию, формула возвращает первое найденное совпадение.

Формула XLOOKUP для нескольких критериев ИЛИ в разных столбцах

При работе с несколькими критериями ИЛИ в одном столбце результаты теста однозначны — только один тест может вернуть TRUE. Эта простота позволяет складывать элементы результирующих массивов, получая окончательный массив только с 0 (ни один из критериев верен) и 1 (один из критериев верен), что идеально соответствует искомому значению 1.

Однако при тестировании нескольких столбцов все становится сложнее. Тесты не являются взаимоисключающими, поскольку критериям может соответствовать более одного столбца, в результате чего несколько логических тестов возвращают TRUE. Следовательно, конечный массив может содержать значения больше 1.

Чтобы решить эту проблему, измените формулу следующим образом:

XLOOKUP(1, –((искомый_массив1 =искомое_значение1) + (искомый_массив2 =искомое_значение2) + (…) > 0), return_array)

В этой адаптации вы складываете промежуточные массивы, а затем проверяете, больше ли значения в полученном массиве 0. Это дает нам новый массив, состоящий только из значений TRUE и FALSE. Двойное отрицание (–) меняет эти значения ИСТИНА и ЛОЖЬ на 1 и 0, гарантируя, что искомое значение 1 по-прежнему выполняет свою работу без сбоев.

Например, чтобы получить первую запись из A3:B22, у которой есть «Да» в столбце C или D или в обоих столбцах, вы можете использовать такую формулу:

=XLOOKUP(1, –((C3:C22 = “Да”) + (D3:D22 = “Да”) >0), A3:B22)

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

Сложный сценарий: объединение логики И и ИЛИ

В более сложных случаях вам может понадобиться сочетание логики «И» и «ИЛИ». Например, чтобы получить поставщика товара в G4 и региона в G5 или I5, используйте следующую формулу:

=XLOOKUP(1, (A3:A22=G4) * ((B3:B22=G5) + (B3:B22=I5)), D3:D22)

Где:

(A3:A22=G4) проверяет, соответствует ли элемент в диапазоне поиска имени целевого элемента в ячейке G4.
((B3:B22=G5) + (B3:B22=I5)) реализует логику ИЛИ, проверяя, является ли регион G5 или I5.
(A3:A22=G4) * ((B3:B22=G5) + (B3:B22=I5)) реализует логику AND для имени элемента и региона.
D3:D22 возвращает соответствующего поставщика из этого диапазона.

Общая формула успешно находит первое совпадение, в котором удовлетворяются критерии как элемента, так и региона, применяя логику И и ИЛИ к различным критериям.

Преимущества и ограничения использования нескольких критериев XLOOKUP

Использование XLOOKUP с несколькими критериями имеет как преимущества, так и ограничения, которые стоит учитывать.

Преимущества

Преимущества использования нескольких критериев XLOOKUP:

Легко найти конкретные детали. С помощью XLOOKUP легче найти в данных именно то, что вы ищете, особенно если у вас несколько условий. Это означает, что вы можете быть очень конкретными в отношении информации, которую хотите получить.
Гибкость в критериях. Вы можете использовать столько условий, сколько вам нужно. Просто убедитесь, что все массивы поиска имеют одинаковый размер.
Динамические массивы. Вы можете использовать XLOOKUP с динамическими массивами, а это означает, что вы можете распределять результаты по нескольким ячейкам без использования старомодных формул массива Ctrl + Shift + Enter.
Легко понять. Формулы XLOOKUP написаны так, чтобы их было легко читать и понимать. Это полезно не только для вас, но и для других, кто может работать с вашими листами Excel.

Ограничения

Ограничения XLOOKUP с несколькими критериями:

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

В заключение, освоение функции XLOOKUP в Excel с несколькими критериями открывает мир возможностей для эффективного анализа данных. Это позволяет вам быть более конкретным в поиске, делая его более организованным и эффективным. Просто помните: лучше всего это работает, когда каждая вещь, которую вы ищете, имеет свою уникальную комбинацию, и все данные, которые вы ищете, организованы одинаково. Итак, используйте эти приемы и сделайте свои приключения с Excel более продуктивными и увлекательными!

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

Несколько критериев XLOOKUP – примеры формул (файл .xlsx)

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