Мощная функция FILTER в Google Spreadsheets и Excel

Функция ФИЛЬТР / FILTER выводит значения из одного или нескольких столбцов, отфильтрованные по заданным нами условиями. Как обычный фильтр. Но это функция! То есть:

данные пересчитываются автоматически, если изменяются данные (и включен автоматический пересчет в случае Excel)
результат вычисления FILTER можно отправить в другие функции в качестве аргумента. Отфильтровали данные, а потом посчитали сумму (СУММ / SUM) или медиану (МЕДИАНА / MEDIAN), например. Или отфильтровали, а потом собрали в одну текстовую строку функцией ОБЪЕДИНИТЬ / TEXTJOIN.

Функция есть в Excel 2021 / 2024 / 365 / Online, Google Таблицах и Р7-Офис (но в последнем работает своеобразно, как «старые» формулы массива Excel, то есть диапазон для вывода результата надо выделять заранее — подробнее об этом можно прочитать в другой моей статье). В Excel называется по-разному при разных языках интерфейса, на русском будет ФИЛЬТР. В Google Таблицах всегда на английском — FILTER.

Google Таблица с примерами из статьи
Книга Excel с примерами из статьи

Синтаксис функции и массив условий
Синтаксис у функции простой: первым аргументом будет диапазон, а дальше — условия. И вся соль в них. В Google Таблицах условий может быть много — и одно, и несколько. Они перечисляются после диапазона как отдельные аргументы:
=FILTER (массив; условие 1; [условие 2]; …)

В Excel аргумент с условиями один. В любом случае мы можем применить несколько условий (И / ИЛИ) — об этом ниже. Третий аргумент «если_пусто» — на что заменить ошибку, которая возникнет, если возвращаемый массив будет пустым.
=ФИЛЬТР (массив; включить; [если_пусто])

Как задаются условия, что это вообще такое? Фактически нам нужно получить массив из логических значений ИСТИНА / TRUE и ЛОЖЬ / FALSE — такого же размера, как выводимый массив из первого аргумента. Те строки, для которых в этом массиве будет ИСТИНА, будут возвращаться функцией FILTER. Давайте посмотрим на пример с числовым условием — сделки больше 16 000:

Все работает! Более того, даже заголовки остались! Неужели функция такая умная и забирает их автоматически? Давайте попробуем условие «меньше 16 000»:

Тоже работает! Но заголовков больше нет. Потому что «Сумма» > 16 000 = ИСТИНА, а «Сумма» < 16 000 = ЛОЖЬ, иначе говоря, текстовая строка больше числа при сравнении в Excel. так что нет, заголовки функцией автоматически не возвращаются, может так только совпасть в случае с числовыми условиями. Как их добавить — обсудим ниже. Давайте выведем массив условий отдельно, чтобы наглядно его увидеть — в Google Таблицах для этого придется нажать Ctrl + Shift + Enter или ввести вручную функцию ARRAYFORMULA, в Excel можно просто ввести. Вариант в Google:

Вариант в Excel:

Вообще при написании сложных условий для FILTER можно вот так сначала проверить их отдельно, чтобы убедиться, что все работает. А потом уже отправлять в FILTER.

Давайте попробуем сделать массив условий меньше, чем фильтруемый массив. Например, так:
=FILTER (A1:G156;F1:F155<16 000)
Не вышло! Будет ошибка:

А если сместить диапазон условий, но оставить такого же размера?
=FILTER (A1:G156;F2:F157<16 000)

Тут все будет очень плохо: возвращаемый диапазон поедет. То есть FILTER не смотрит на какое-то пересечение. Просто выводит те строки, для которых во втором аргументе TRUE / ИСТИНА.

Текст в условии
Как во всех формулах Excel / Таблиц, здесь текстовое условие либо вводится в кавычках внутри формулы:
=FILTER (A1:G156;E1:E156="сайт")

Либо берется из ячейки — тогда уже кавычек не нужно:
=FILTER (A1:G156;E1:E156=I1)

Даты в условиях
Даты в Excel и Google Таблицах — это числа, так что вполне можно сравнивать, как и числа, соответственно и задавать условия в FILTER. Только дата нужна именно как число, то есть такой вариант не сработает:
=ФИЛЬТР (A1:G156;D1:D156>"01.04.2025")

А такой сработает. ДАТАЗНАЧ / DATEVALUE превращает дату в текстовом формате в число:
=ФИЛЬТР (A1:G156;D1:D156>ДАТАЗНАЧ ("01.04.2025"))
Кстати, обратите внимание, что даты в Excel (но не в Google Таблицах) теряют форматирование. FILTER возвращает значения, а не форматы, в случае с датами «истинные» значения — это числа, вот они там и есть:

Другой вариант — использовать функцию ДАТА / DATE:
=ФИЛЬТР (A1:G156;D1:D156>ДАТА (2025;4;1))

Флажки (чекбоксы) в условиях
А если у вас столбец с флажками и вы хотите фильтровать по нему — выбирать выполненные задачи, например? В ячейках с флажками всегда будет TRUE (включен) или FALSE (выключен). А значит, не нужно никаких дополнительных манипуляций — просто ссылаемся на диапазон с флажками:

Обратите внимание, что мы выводим столбцы A и B, фильтруя по C. То есть можно не включать диапазон с условием в выводимый диапазон. Здесь это не нужно — там априори будет TRUE / ИСТИНА.

А если нам нужен список невыполненных задач? Применим функцию НЕ / NOT - она превратит выключенные флажки в TRUE (а включенные в FALSE) и только они и будут отфильтрованы:
=FILTER(A2:B6;НЕ(C2:C6))

Несколько условий в Google.
Что если условий у нас несколько? Тут будут отличия в Google Таблицах и Excel, потому что в Таблицах аргументов с условиями несколько, а в Excel он один. Так что разбираем отдельно.

Допустим, мы хотим отфильтровать по сумме сделки (больше 15 тыс.) и каналу продаж (пусть будет рассылка). В Google перечислим два этих условия через точку с запятой — это два отдельных аргумента:
=FILTER (A:G; E: E="рассылка"; F: F>15 000)

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

А если нам нужны два канала продаж? Рассылка и сайт? То есть условия на один и тот же столбец. Тут вариант «И» не подойдет. Не может одновременно в ячейке быть «Рассылка» и «Сайт» (если мы говорим про полное содержимое ячейки, конечно). В такой ситуации укажем оба условия в одном аргументе со знаком «+», сами выражения возьмем в скобки:
=FILTER (A:G; (E:E="рассылка")+(E:E="сайт"))

Если хотя бы одно из двух условий будет выполняться, сумма двух будет возвращать ИСТИНА / TRUE и мы получим нужную строку.

А если к этому добавится условие на другой столбец? То есть «рассылка ИЛИ сайт И сумма больше N», например? Добавим это условие отдельным аргументом:
=FILTER (A:G; (E:E="рассылка")+(E:E="сайт"); F: F>16 000)

Несколько условий в Excel
В Excel аргумент с условием один. То есть в любом случае мы оперируем скобками и плюсами. А для логического умножения будем использовать соответствующий символ.
Иначе говоря, вариант с двумя каналами продаж будет идентичен гуглотабличному:

А в случае с условием «И» добавляем знак умножения. То есть формула, которая в Google Таблицах выглядела так:
=FILTER (A:G; E: E="рассылка"; F: F>15 000)

В Excel будет такой:
=FILTER (A:G; (E:E="рассылка") *(F:F>15 000))

А если нужно сочетание И-ИЛИ? Один из двух каналов продаж и сумма выше N? Тогда нам надо умножать два условия, одно из которых — сумма двух:
=ФИЛЬТР (A:G; ((E:E="сайт") + (E:E="рассылка")) * (F:F>16 000))

Условия с функциями
В условиях можно использовать самые разные функции. Главное, чтобы на выходе были TRUE / FALSE. Допустим, нам нужны только понедельники. Данных о дне недели в таблице у нас нет, но что нам мешает в самой функции FILTER трансформировать столбец с датой с помощью функции ДЕНЬНЕД / WEEKDAY (второй аргумент этой функции, равный двойке, отвечает за тип нумерации, по умолчанию неделя начинается с воскресенья, а при двойке — с понедельника) :
=ФИЛЬТР (Продажи;ДЕНЬНЕД (Продажи[Дата];2)=1)

В этом примере я превратил диапазон Excel в "умную" таблицу с именем "Продажи", поэтому ссылки выглядят иначе. Даты выглядят как даты только потому, что я применил к ним формат. ФИЛЬТР, повторюсь, приносит только данные без форматирования, то есть даты изначально выглядят как числа

По аналогии можно брать год с помощью одноименной функции ГОД / YEAR, месяц (МЕСЯЦ / MONTH), номер недели (НОМНЕДЕЛИ.ISO / ISOWEEKNUM).

Как быть, если нас интересует вхождение какого-то текста? Допустим, нужны не ячейки, в точности равные «Рассылка», а ячейки, в которых в названии товара присутствует что-то. Можно использовать какую-нибудь функцию для поиска. НАЙТИ / FIND или ПОИСК / SEARCH, например. Первая будет искать с учетом регистра, а вторая без. Такая функция будет возвращать ошибку, если значение не найдено, и число, если оно найдено (это будет положение фрагмента в тексте):

Можно эту конструкцию отправлять в FILTER. Числа сойдут за ИСТИНУ, все отфильтруется:
=FILTER (диапазон; ПОИСК ("фрагмент";столбец с текстовыми значениями))

Для более тяжелых случаев можно использовать регулярные выражения, а именно функцию REGEXMATCH (РЕГТЕСТ / REGEXTEST в Excel). Она возвращает ИСТИНУ и ЛОЖЬ — то, что нужно.
Нужны нам все товары с 3 и 4 портами HDMI, допустим:
=ФИЛЬТР (Остатки;РЕГТЕСТ (Остатки[Товар];"[34]xHDMI"))

Вот видеоурок про регулярные выражения:

Фильтрация по списку
Еще одна стандартная история: нужно отфильтровать по списку, то есть условий у нас много и они в ячейках. Во многих ситуациях в Excel для этого подойдет расширенный фильтр (Advanced Filter), но его нет в Google, да и в Excel это статичная история, а мы про формулы сейчас.

В такой ситуации используем функцию ПОИСКПОЗ / MATCH или ее новую версию ПОИСКПОЗX / XMATCH (отличаются они в том числе тем, что XMATCH по умолчанию ведет точный поиск, а у MATCH третий аргумент должен быть равен нулю для этого)

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

С функцией FILTER будет так:
=FILTER(диапазон; XMATCH(Столбец, по которому фильтруем; список значений)

ФИЛЬТРация с СОРТировкой

Если вы хотите совместить фильтрацию с сортировкой, нужно совместить соответствующие функции — добавить к ФИЛЬТРу функцию СОРТ / SORT.
=СОРТ (ФИЛЬТР (диапазон; условие); номер столбца для сортировки; порядок сортировки)

Например, по столбцу «Сумма» по убыванию:
=СОРТ (ФИЛЬТР (Данные;Данные[Продукт]="штуки");6;-1)

Добавляем к результату фильтрации заголовки
Все это время мы страдали без заголовков, которые только один раз прицепились к результату фильтрации — потому что текст больше числа. А если надо с гарантией, используем функцию VSTACK / ВСТОЛБИК, которая объединяет массивы вертикально:
=VSTACK (ссылка на заголовки; FILTER (…))

Если у вас умная таблица, можно ссылаться на ее заголовки:
=VSTACK(Сделки[#HEADERS];FILTER(Сделки;Сделки[Город]="Москва"))

Фильтруем не все столбцы
Что если нам нужны первый, третий и седьмой столбцы, а другие не нужны? Отфильтруем как обычно, а потом добавим функцию CHOOSECOLS (В Excel на русском ВЫБОРСТОЛБЦ). Ее первый аргумент — массив (у нас его, отфильтрованный, уже будет возвращать функцию FILTER), а второй и последующие — номера столбцов:
=CHOOSECOLS (FILTER (…); номер первого столбца; номер второго; …)

В следующем примере берем первые 6 столбцов, но без третьего (а зачем он нам? Мы по нему фильтруем и априори понимаем, что в нем будет только выбранный тип товара — «штуки»):

Фильтруем горизонтальные диапазоны
А почему бы и нет? Можно выбирать не строки, а столбцы. Допустим, у нас есть такие данные (в реальной жизни шапка с заголовками будет многоуровневой, скорее всего, например, будут идти разные показатели по каждому месяцу):

И нам надо только деньги и только за определенный квартал. Если бы там были даты или нам нужен был один месяц — справились бы просто функцией SUMIFS / СУММЕСЛИМН. Был бы диапазон вертикальным, табличного вида, справились бы функцией БДСУММ / DSUM. А тут можно отфильтровать с регулярным выражением и получить все суммы:
=FILTER (B3:BU8;regexmatch (B2:BU2; "Деньги (Январь|Февраль|Март) 2025"))

На скриншоте функция выводит данные со второй строки — для наглядности, чтобы с заголовками. В реальности нам скорее всего нужны будут только суммы для дальнешей обработки, так что в формуле выше диапазон с третьей строки.

FILTER в качестве аргументов других функций
Ну, а нам ведь, скорее всего, не табличка такая нужна, а сумма денег в первом квартале 2025? Тогда мы просто добавляем функцию СУММ / SUM.
=СУММ (FILTER (B2:BU8;regexmatch (B2:BU2; "Деньги (Январь|Февраль|Март) 2025")))
И получаем на выходе уже одно число, а не массив.

А можем, конечно, и в другие функции результат фильтрации отправлять. Вот нужна нам, допустим, ссылка на отправку письма всем сотрудникам определенного отдела:

Что нам мешает добавить в любую ячейку проверку данных по списку, чтобы выбирать отдел в выпадающем списке? И в Excel, и в Google ничто не мешает. И далее фильтровать по этому отделу:

Но возвращается массив. Сейчас три ячейки, добавятся сотрудники или мы выберем другой отдел — будет меньше или больше. А нам надо в одной ячейке, да в виде ссылки, чтобы все имейлы шли через запятую. Так что добавляем функцию ОБЪЕДИНИТЬ / TEXTJOIN. Только в качестве массива объединяемых значений даем не диапазон, а нашу функцию ФИЛЬТР.

Второй аргумент функции пропускаем. В данной ситуации он значения не имеет - это пропуск пустых ячеек. Но у нас не ячейки, а ФИЛЬТР, пустых априори не будет.

Ну, а далее добавляем к этому делу обработчик mailto (при желании можно и тело письма body, и тему subject, тут уже насколько фантазии хватит) и все это засовываем в функцию ГИПЕРССЫЛКА / HYPERLINK:

=ГИПЕРССЫЛКА("mailto:"&ОБЪЕДИНИТЬ(",";;ФИЛЬТР(Сотрудники[Электропочта];Сотрудники[Отдел]=F2)))

Для большей красоты можно задействовать второй аргумент функции ГИПЕРССЫЛКА — то, как ссылка выглядит в ячейке. Например, брать название отдела из ячейки F2 или просто ввести текст «Рассылка»:

---

Магия табличных формул: от A1 до LAMBDA — мой курс по подписке на Sponsr.ru. Присоединяйтесь! Новые подробные уроки с примерами — каждую неделю.

Функция FILTER в Google Spreadsheets и Excel