FILTER

Функция FILTER Таблиц Гугл отображает только те строки или столбцы в диапазоне, которые соответствуют заданным условиям. Отличительной особенностью этой формулы является то, что она работает как со строками, так и с столбцами.

Примечание Это большой и подробный указатель на возможности формулы FILTER на русском языке. Все примеры доступны в Таблице, см. раздел Ссылки .

Описание

Функция FILTER универсальная и очень гибкая. Она очень быстрая и легко читается. Используя такие функции как REGEXMATCH и MATCH, можно имитировать работу SQL-подобных запросов типа like и выбора на основе списка. Это действительно удобно, когда требуется отобрать большое количество данных, не зная конечного числа аргументов в уловии отбора.

Для всех примеров будет использоваться следующий набор данных

Примеры

Колонка соответсвует конкретному значению

=FILTER('Данные'!A2:F18;'Данные'!F2:F18="A")

Данные содержат заданную подстроку

Вывести все имена, в которых есть буквосочетание ль

=FILTER('Данные'!A2:F;REGEXMATCH('Данные'!B2:B; "ль"))

Данные начинаются с определенной строки

Вывести имена, которые начинаются на А

=FILTER('Данные'!A2:F;REGEXMATCH('Данные'!B2:B; "^А"))

Данные не начинаются с определенной строки

Вывести имена не начинающиеся на А

=FILTER('Данные'!A2:F;REGEXMATCH('Данные'!B2:B; "^[^А]"))

Сравнение чисел

Вывести данные, числа которых меньше 2500

=FILTER('Данные'!A2:F;'Данные'!D2:D<2500)

Числа содержат определенную цифру

Номер содержит цифру 2

=FILTER('Данные'!A2:F;REGEXMATCH(TEXT('Данные'!A2:A; "0"); "2"))

Числа оканчиваются на определенную цифру

Номер оканчивается на цифру 2

=FILTER('Данные'!A2:F;REGEXMATCH(TEXT('Данные'!A2:A; "0"); "2$"))

Сравнение дат

Выбрать всё до даты 31.12.2017

=FILTER('Данные'!A2:F;'Данные'!C2:C<=DATE(2017;12;31))

Даты только определенного года

Только 2017 год

=FILTER('Данные'!A2:F;REGEXMATCH(TEXT('Данные'!C2:C; "yyyyMMdd"); "2017"))

Даты только определенного месяца

Только август 2019

=FILTER('Данные'!A2:F;EOMONTH('Данные'!C2:C;1)=EOMONTH(DATE(2019;8;1);1))

Любой август

=FILTER('Данные'!A2:F;MONTH('Данные'!C2:C)=8)

Отбор по счету (по очереди)

Выбрать каждую шестую строку

=FILTER('Данные'!A2:F; NOT(MOD(ROW('Данные'!A2:A)-1; 6)))

Выбрать каждую нечетную строку

=FILTER('Данные'!A2:F; ISODD(ROW('Данные'!A2:A)-1))

Данные, которые находятся в заранее заданном списке

Выбрать данные, имена которых находятся в списке {"Георгий";"Ермак";"Юлиус"}

=FILTER('Данные'!A2:F;IFERROR(MATCH('Данные'!B2:B;{"Георгий";"Ермак";"Юлиус"};0);))

Данные, которые соответствуют списку

Выбрать имена, которые находятся в списке. Список в I8:I12

=FILTER('Данные'!A2:F;MATCH('Данные'!B2:B;I8:I12;0))

Данные, которые отсутствуют в списке

Выбрать имена, которых нет в списке. Список в I15:I19

=FILTER('Данные'!A2:F;ISNA(MATCH('Данные'!B2:B;I15:I19;0)))

Данные, которые соответствуют “нечеткому” списку значений

Выбрать все, в поле Строка которых, есть последовательность символов “x” и, любая цифра или тире “-”

=FILTER('Данные'!A2:F;REGEXMATCH('Данные'!E2:E;"x[\d-]"))

Вывести все данные, где поле Имя начинается с буквы из списка

Список находится в I9:I12

=FILTER('Данные'!A2:F;REGEXMATCH('Данные'!B2:B; "(?i)^(" & TEXTJOIN("|";1; I9:I12) & ")"))

Пример выбора женских имен

=FILTER('Данные'!A2:F;REGEXMATCH('Данные'!B2:B; "(?i)(" & TEXTJOIN("|";1; I15:I19) & ")"))

или

=FILTER('Данные'!A2:F;REGEXMATCH('Данные'!B2:B; "(?i)(" & JOIN("|"; {"а$";"я$"}) & ")"))

Пример выбора мужских имен

=FILTER('Данные'!A2:F;NOT(REGEXMATCH('Данные'!B2:B; "(?i)(" & TEXTJOIN("|";1;I23:I27) & ")")))

Выборка с условием ИЛИ

Требуется отфильтровать данные в Дате которых указан 2019 год или Группа которых равна "B"

=FILTER('Данные'!A2:F;(YEAR('Данные'!C2:C)=2019) + ('Данные'!F2:F="B"))

Бесконечные диапазоны

Бесконечные диапазоны в FILTER могут создавать некоторые неудобства, особенно, если в выборку попадает достаточно много пустых строк. Они просто занимают место в результирующей Таблице. Если на это место внести данные, то получим ошибку

Ошибка Массив не выведен, поскольку это привело бы к перезаписи данных в диапазоне.

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

В примере ниже на листе Данные 1000 строк, из которых только 20 заполнены значениями. Вот результаты фильтров

Сравните

=FILTER('Данные'!A2:F;YEAR('Данные'!C2:C)<2018;'Данные'!A2:A<>"")

=FILTER('Данные'!A2:F;YEAR('Данные'!C2:C)<2018)

В первой формуле учитываются и не выводятся пустые строки 'Данные'!A2:A<>""

Фильтр по колонкам

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

Жестко заданные колонки по номерам. Только вторая и шестая колонка

=FILTER('Данные'!A1:F;{0\1\0\0\0\1})

Колонки по определенному условию. Если имя колонки равно “Имя” или “Группа”

=FILTER('Данные'!A1:F;ARRAYFORMULA(REGEXMATCH('Данные'!A1:F1;"(?i)(имя|группа)")))

Динамический фильтр колонок

В диапазоне F2:F7 находятся условия для фильтра

=FILTER('Данные'!A1:F;TRANSPOSE(F2:F7))

Ссылки

• FILTER - Cправка - Редакторы Документов
• Примеры формул в Таблице