Резюме
Чтобы фильтровать и извлекать данные на основе нескольких сложных критериев, вы можете использовать функцию ФИЛЬТР с цепочкой выражений, использующих логическую логику. В показанном примере формула в G5:
=FILTER(B5:E16,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4))
Эта формула возвращает данные, где:
счет начинается с "x" И регион "восток", а месяц НЕ апрель.
Объяснение
В этом примере нам нужно построить логику, которая фильтрует данные, чтобы включить:
счет начинается с "x" И регион "восток", а месяц НЕ апрель.
Логика фильтрации этой формулы (аргумент include) создается путем объединения трех выражений, которые используют логическую логику для массивов данных. Первое выражение использует функцию LEFT, чтобы проверить, начинается ли Account с "x":
LEFT(B5:B16)="x" // account begins with "x"
Результатом является массив ИСТИННЫХ ЛОЖНЫХ значений, подобных этому:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE)
Второе выражение проверяет, является ли Регион "восточным" с помощью оператора равенства (=):
C5:C16="east" // region is east
В результате получился еще один массив:
(FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE)
Третье выражение использует функцию МЕСЯЦ с функцией НЕ, чтобы проверить, не является ли месяц апрелем:
NOT(MONTH(D5:D16)=4) // month is not april
что дает:
(FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Обратите внимание, что функция НЕ меняет результат выражения МЕСЯЦ.
Все три массива перемножаются. Математическая операция приводит значения ИСТИНА и ЛОЖЬ к 1 и 0, поэтому на этом этапе мы можем визуализировать аргумент include следующим образом:
(1;0;1;1;1;0;0;0;1;1;0;1)* (0;0;1;1;1;0;1;0;0;1;0;1)* (0;0;0;1;1;1;1;1;1;1;1;1)
Логическое умножение соответствует логической функции И, поэтому конечный результат представляет собой один массив, подобный этому:
(0;0;0;1;1;0;0;0;0;1;0;1)
Функция FILTER использует этот массив для фильтрации данных и возвращает четыре строки, соответствующие единицам в массиве.
Расширяющие критерии
Выражения, используемые для создания аргумента включения в фильтре, могут быть расширены по мере необходимости для обработки еще более сложных фильтров. Например, чтобы дополнительно отфильтровать данные, чтобы включить только те строки, где сумма> 10000, вы можете использовать такую формулу:
=FILTER(B5:E16,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4)*(E5:E16>10000))
