Функция ФИЛЬТР / FILTER выводит значения из одного или нескольких столбцов, отфильтрованные по заданным нами условиями. Как обычный фильтр. Но это функция! То есть:
данные пересчитываются автоматически, если изменяются данные (и включен автоматический пересчет в случае Excel)
результат вычисления FILTER можно отправить в другие функции в качестве аргумента. Отфильтровали данные, а потом посчитали сумму (СУММ / SUM) или медиану (МЕДИАНА / MEDIAN), например. Или отфильтровали, а потом собрали в одну текстовую строку функцией ОБЪЕДИНИТЬ / TEXTJOIN.
Функция есть в Excel 2021 / 2024 / 365 / Online, Google Таблицах и Р7-Офис (но в последнем работает своеобразно, как «старые» формулы массива Excel, то есть диапазон для вывода результата надо выделять заранее — подробнее об этом можно прочитать в другой моей статье). В Excel называется по-разному при разных языках интерфейса, на русском будет ФИЛЬТР. В Google Таблицах всегда на английском — FILTER.
Синтаксис функции и массив условий Синтаксис у функции простой: первым аргументом будет диапазон, а дальше — условия. И вся соль в них. В 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)
По аналогии можно брать год с помощью одноименной функции ГОД / 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:
Для большей красоты можно задействовать второй аргумент функции ГИПЕРССЫЛКА — то, как ссылка выглядит в ячейке. Например, брать название отдела из ячейки F2 или просто ввести текст «Рассылка»: