Резюме
Чтобы извлечь данные с несколькими условиями ИЛИ, вы можете использовать функцию ФИЛЬТР вместе с функцией ПОИСКПОЗ. В показанном примере формула в F9:
=FILTER(B5:D16, ISNUMBER(MATCH(items,F5:F6,0))* ISNUMBER(MATCH(colors,G5:G6,0))* ISNUMBER(MATCH(cities,H5:H6,0)))
где элементы (B3: B16), цвета (C3: C16) и города (D3: D16) являются именованными диапазонами.
Эта формула возвращает данные, в которых элемент (футболки ИЛИ толстовка) И цвет (красный ИЛИ синий) И город (Денвер ИЛИ Сиэтл).
Объяснение
В этом примере критерии вводятся в диапазоне F5: H6. Логика формулы:
товар (футболка ИЛИ толстовка) И цвет (красный ИЛИ синий) И город (Денвер ИЛИ Сиэтл)
Логика фильтрации этой формулы (аргумент include) применяется с функциями ISNUMBER и MATCH вместе с логической логикой, применяемой в операции с массивом.
ПОИСКПОЗ настраивается «в обратном порядке», при этом значения поиска поступают из данных, а критерии используются для массива поиска. Например, первое условие - одежда должна быть либо футболкой, либо худи. Чтобы применить это условие, ПОИСКПОЗ настраивается следующим образом:
MATCH(items,F5:F6,0) // check for tshirt or hoodie
Поскольку в данных 12 значений, в результате мы получаем массив с 12 значениями, подобными этому:
(1;#N/A;#N/A;2;#N/A;2;2;#N/A;1;#N/A;2;1)
Этот массив содержит либо # N / A ошибок (нет совпадений), либо числа (совпадения). Номера уведомлений соответствуют элементам футболки или худи. Чтобы преобразовать этот массив в значения ИСТИНА и ЛОЖЬ, функция ПОИСКПОЗ заключена в функцию ЕЧИСЛО:
ISNUMBER(MATCH(items,F5:F6,0))
что дает такой массив:
(TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE)
В этом массиве ИСТИННЫЕ значения соответствуют футболке или худи.
Полная формула содержит три выражения, подобных приведенному выше, используемому для аргумента include функции FILTER:
ISNUMBER(MATCH(items,F5:F6,0))* // tshirt or hoodie ISNUMBER(MATCH(colors,G5:G6,0))* // red or blue ISNUMBER(MATCH(cities,H5:H6,0))) // denver or seattle
После оценки MATCH и ISNUMBER у нас есть три массива, содержащих значения TRUE и FALSE. Математическая операция умножения этих массивов переводит значения ИСТИНА и ЛОЖЬ в единицы и нули, поэтому мы можем визуализировать массивы в этой точке следующим образом:
(1;0;0;1;0;1;1;0;1;0;1;1)* (1;0;1;1;0;1;0;0;0;0;0;1)* (1;0;1;0;0;1;0;1;1;0;0;1)
Результатом, согласно правилам логической арифметики, является единственный массив:
(1;0;0;0;0;1;0;0;0;0;0;1)
который становится аргументом включения в функции ФИЛЬТР:
=FILTER(B5:D16,(1;0;0;0;0;1;0;0;0;0;0;1))
Конечный результат - три строки данных, показанные в F9: H11.
С жестко запрограммированными значениями
Хотя в формуле в примере используются критерии, введенные непосредственно на листе, критерии могут быть жестко заданы как константы массива, а не следующим образом:
=FILTER(B5:D16, ISNUMBER(MATCH(items,("Tshirt";"Hoodie"),0))* ISNUMBER(MATCH(colors,("Red";"Blue"),0))* ISNUMBER(MATCH(cities,("Denver";"Seattle"),0)))
