Фильтрация данных с помощью FILTER

Функция FILTER возвращает только те строки диапазона, которые соответствуют заданному условию. Вместо того чтобы вручную скрывать строки или копировать совпадения, FILTER автоматически разливает подходящие строки.

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

FILTER принимает до трёх аргументов:

=FILTER(array, include, [if_empty])

• array — диапазон, который нужно вернуть.
• include — логическая проверка, возвращающая TRUE или FALSE для каждой строки.
• if_empty — необязательное значение, отображаемое, если совпадений нет.

Проверка include должна иметь ту же высоту, что и array, чтобы для каждой строки получалось значение TRUE или FALSE.

Предположим, в A2:A10 указаны имена продавцов, а в B2:B10 — их регионы. Чтобы вывести только имена из региона East:

=FILTER(A2:A10, B2:B10="East")

Проверка B2:B10="East" создаёт столбец со значениями TRUE и FALSE. FILTER оставляет строки, для которых результатом является TRUE, и разливает их.

Массив может состоять более чем из одного столбца. Чтобы вернуть имя и сумму продаж для региона East, укажите в качестве массива весь блок:

=FILTER(A2:C10, B2:B10="East")

FILTER возвращает все столбцы подходящих строк, разливая небольшую таблицу. Проверка include по-прежнему использует только один столбец с условием.

Условия не ограничиваются текстом. Чтобы вернуть все строки, в которых продажи в C2:C10 превышают 500:

=FILTER(A2:C10, C2:C10>500)

Операторы сравнения, такие как «больше», «меньше» и «не равно», работают внутри аргумента include так же, как и в обычной логической проверке.

Чтобы одновременно требовать выполнения двух условий, перемножьте проверки. Умножение действует как AND, поскольку TRUE равно 1, а FALSE равно 0: строка проходит только тогда, когда обе проверки дают 1.

=FILTER(A2:C10, (B2:B10="East")*(C2:C10>500))

Эта формула возвращает строки региона East, в которых продажи также превышают 500. Каждую проверку заключайте в круглые скобки.

Чтобы строка проходила, когда истинно любое из условий, сложите проверки. Сложение действует как OR, поскольку сумма равна как минимум 1, если хотя бы одна проверка даёт TRUE.

=FILTER(A2:C10, (B2:B10="East")+(B2:B10="West"))

Формула возвращает строки из региона East или West. Строка с результатом 1 или 2 сохраняется, а строка с результатом 0 удаляется.

Если ни одна строка не соответствует условию, FILTER по умолчанию возвращает ошибку #CALC!. Необязательный третий аргумент позволяет заменить её понятным сообщением:

=FILTER(A2:C10, B2:B10="South", "No matches found")

Если региона South нет, ячейка отображает текст вместо ошибки. В настоящих отчётах всегда добавляйте значение if_empty.

Для интерактивного отчёта сравнивайте данные с ячейкой, а не с жёстко заданным значением. Если в E1 указан выбранный пользователем регион:

=FILTER(A2:C10, B2:B10=E1, "No matches")

Измените E1 на West — и разлитый список мгновенно обновится, показывая строки региона West. Это основа панели мониторинга, управляемой раскрывающимся списком.

FILTER возвращает совпадения в исходном порядке. Чтобы отсортировать их, вложите FILTER в SORT. Чтобы вывести строки региона East, отсортированные по убыванию продаж:

=SORT(FILTER(A2:C10, B2:B10="East"), 3, -1)

SORT упорядочивает разлитую таблицу по третьему столбцу, а -1 означает сортировку по убыванию. Такое объединение функций разлива распространено и очень эффективно.

FILTER работает и в Excel 365, и в Google Таблицах, используя почти одинаковый синтаксис. В Google Таблицах можно передавать несколько условий отдельными аргументами, а не перемножать их:

=FILTER(A2:C10, B2:B10="East", C2:C10>500)

Таблицы обрабатывают каждый дополнительный аргумент как условие AND. В Excel в единственном аргументе include используются умножение для AND и сложение для OR.

Проверьте, насколько хорошо Вы поняли FILTER.

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

• Синтаксис: =FILTER(array, include, [if_empty]).
• Проверка include должна соответствовать высоте массива.
• Перемножайте проверки для AND и складывайте их для OR.
• Добавляйте сообщение if_empty, чтобы избежать ошибок #CALC!.
• Вкладывайте FILTER в SORT, чтобы упорядочить результаты.

Далее Вы научитесь извлекать список уникальных значений с помощью UNIQUE.