Функция FILTER в Google Sheets — подробное руководство с практическими примерами

Коротко о назначении

Функция FILTER берёт массив (диапазон) и возвращает все элементы, для которых логическое условие равно TRUE. Условий может быть несколько — они объединяются логическим И (AND). Если вы хотите объединить условия логическим ИЛИ (OR), комбинируйте массивы или используйте выражения с плюсом (+) и функцией N().

Синтаксис и понятия

Синтаксис:

FILTER(range, condition1, [condition2, …])

Определения в одной строке:

• range — массив (диапазон) ячеек, из которого нужно вернуть строки/столбцы.
• condition1 — логическое выражение (массив TRUE/FALSE) сопоставленное по размеру с range.
• condition2… — дополнительные логические массивы, можно указывать сколько угодно.

Важно: все логические массивы должны совпадать по размеру (высота/ширина) с первой частью диапазона, иначе функция вернёт ошибку.

Пример данных и задача

У нас есть таблица продаж по продуктам и штатам. Задача — выбрать только те строки, которые относятся к выбранному штату, и вывести три столбца: товар, продажи, прибыль.

Шаг 1. Создаём выпадающий список для выбора штата

1. Щёлкните правой кнопкой по ячейке, где будет список.

2. Откройте Раздел «Проверка данных» (Data validation).

3. В критериях выберите «Список из диапазона». Это позволит подставлять значения из колонки со штатами.

4. Укажите диапазон со штатами (например A2:A13) с помощью выбора в окне критерия.

5. Сохраните настройки.

После этого в выбранной ячейке появится выпадающий список с уникальными значениями из диапазона.

Шаг 2. Пример формулы FILTER

Возьмём диапазон данных B2:D13 (товар, продажи, прибыль) и отфильтруем по колонке штатов A2:A13, сравнивая со значением в ячейке G5, где находится выпадающий список.

Формула (вводится в F8 или в любую пустую ячейку):

=FILTER(B2:D, A2:A = G5)

Совет: если вы планируете добавлять строки, используйте открытые диапазоны без номеров конца, например B2:D, A2:A. Тогда FILTER автоматически захватит новые строки.

Убедитесь, что высота диапазонов совпадает. Если нажать Enter, результат «вылется» в соседние ячейки и покажет строки, соответствующие выбранному штату.

Если тестовая логика возвращает FALSE, соответствующая строка пропускается. Например, при выборе Utah вы увидите данные только по этому штату.

Фильтрация по нескольким условиям

Добавим второе условие: прибыль больше 5000. Формула станет:

1. Откройте ячейку с текущей формулой (например F8).

2. Добавьте второе условие, указывая диапазон прибыли D2:D и сравнение >5000:

=FILTER(B2:D, A2:A = G5, D2:D > 5000)

Нажмите Enter, и таблица покажет только те строки, которые удовлетворяют обоим условиям.

Если значение прибыли не больше 5000, соответствующая строка будет отброшена.

Частые ошибки и когда FILTER не сработает

Важно знать самые распространённые причины ошибок:

• Несовпадение размеров диапазонов. Условия и основной диапазон должны иметь одинаковую высоту/ширину.
• Сравнение текстовых значений и чисел. Числа в текстовых ячейках неравны числам (“5000” ≠ 5000).
• Пустые строки в ключевой колонке при использовании открытого диапазона могут приводить к неожиданным результатам.
• Использование относительных ссылок там, где нужны абсолютные — при автозаполнении формул это меняет логику.
• Нечёткая логика OR — FILTER по умолчанию объединяет условия через И. Для OR используйте выражения вида (A2:A=G5)+(B2:B=H5)>0 или функцию REGEXMATCH.

Пример неудачной формулы:

=FILTER(B2:D, A2:A = G5, D2:D)

Здесь второе условие D2:D ничего не проверяет и вернёт ошибку, потому что нужно сравнение или логическое выражение.

Альтернативные подходы и когда их использовать

• QUERY — мощная текстовая функция для SQL-подобных выборок. Удобна для сложных группировок и агрегаций.
• FILTER + SORT — если нужно отсортировать результат фильтрации.
• UNIQUE + FILTER — чтобы сначала убрать дубликаты в списке значений для выпадающего меню.
• Filter Views — для интерактивного фильтра в интерфейсе без изменения формул.
• Pivot Table — для сводных отчётов и быстрой агрегации.
• Apps Script — если нужна автоматизация, условная логика или массовая трансформация.

Когда использовать FILTER: если нужна динамическая таблица-отбор с обновлением при изменении данных и простыми логическими условиями.

Практические приёмы и шаблоны

1. Фильтрация с OR (любое условие верно):

=FILTER(B2:D, (A2:A=G5) + (C2:C=H5))

Здесь знак + действует как логическое ИЛИ (любая из частей даёт TRUE).

2. Фильтрация с нечувствительностью к регистру (текст):

=FILTER(B2:D, UPPER(A2:A) = UPPER(G5))

3. Возврат замещающего значения, если ничего не найдено:

=IFERROR( FILTER(B2:D, A2:A=G5), {"Нет данных"} )

4. Фильтрация по части текста с REGEXMATCH:

=FILTER(B2:D, REGEXMATCH(A2:A, "(?i)utah|ut") )

Совет по производительности

• Для больших диапазонов лучше ограничивать поиск по колонке-ключу, а не по всему массиву.
• Избегайте сложных вычислений внутри самого условия — предварительно приведение типов и создание вспомогательных колонок ускорит расчёт.

Контроль качества: тест-кейсы и критерии приёмки

Критерии приёмки:

• Результат содержит только строки, где условие TRUE.
• Количество строк совпадает с ожидаемым подмножеством (вручную или через COUNTIF).
• Функция корректно обновляется при добавлении/удалении строк в исходном диапазоне.
• Для комбинированных условий логика AND/OR работает согласно ТЗ.

Примеры тест-кейсов:

• Выбрать штат с одним совпадением — проверить, что возвращается ровно одна строка.
• Выбрать штат с нулевым совпадением — ожидать «Нет данных» через IFERROR.
• Добавить новую строку с соответствующим штатом — проверка, что она появляется в результате при открытом диапазоне.

Отладка и восстановление (runbook)

1. Если возникает ошибка “FILTER has mismatched range sizes”, проверьте высоту/ширину всех условий и диапазона.
2. Если результат пустой, проверьте типы данных и пробелы в ячейках.
3. Для восстановления предыдущей версии листа используйте «Историю версий» в Google Sheets.

Важно: при совместной работе изменения формул могут быть перезаписаны — создавайте резервную копию перед массовыми правками.

Ментальные модели и хакометрики

• Модель «маска»: думайте о condition как о маске TRUE/FALSE, которая накладывается на строки диапазона.
• Модель «первичный ключ»: выбирайте колонку-ключ, по которой вы делаете основной отбор; чем меньше уникальных значений — тем проще фильтровать.
• Хак: используйте вспомогательные столбцы, если условия сложные — это упростит чтение формул для коллег.

Уровни зрелости использования FILTER

• Начальный: простая фильтрация по одной колонке с выбором из выпадающего списка.
• Средний: несколько условий, сортировка результата, IFERROR и открытые диапазоны.
• Продвинутый: динамические OR-выражения, REGEXMATCH, объединение с QUERY и Apps Script для автозапуска.

Рекомендации по безопасности и приватности

Работайте с ограниченными доступами к листам, если таблицы содержат персональные или финансовые данные. Если вы экспорируете результаты FILTER, убедитесь, что у получателей есть права доступа к исходным данным в рамках политики компании.

Быстрое резюме

• FILTER возвращает подмножество строк/столбцов из массива по логическим условиям.
• Условия нужно составлять таким образом, чтобы их размер совпадал с основным диапазоном.
• Используйте открытые диапазоны, IFERROR для пустых результатов и вспомогательные столбцы для сложных логик.

Примечание: FILTER в Google Sheets похожа на Excel-функцию, но в Sheets логика работы с массивами и выливание результатов отличается.

Краткий чеклист для роли

Аналитик:

• Убедиться в консистентности типов данных.
• Тестировать формулы с граничными случаями.

Владелец малого бизнеса:

• Создать выпадающие списки для удобства фильтрации.
• Настроить IFERROR для информативных сообщений пользователям.

Разработчик/автоматизатор:

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

Заключение

Функция FILTER — простой и мощный инструмент для динамической работы с подмножествами данных в Google Sheets. Она удобна для создания интерактивных таблиц и дашбордов. При правильном подходе (контроль размеров диапазонов, корректные типы данных, использование вспомогательных колонок) FILTER становится основной функцией в наборе аналитика.

Summary:

• Используйте открытые диапазоны для автоматического учёта новых строк.
• Проверяйте совпадение размеров диапазонов.
• Комбинируйте FILTER с QUERY, SORT и REGEXMATCH для гибкости.

Важно: при работе с личными или конфиденциальными данными проверьте настройки доступа к листу и истории версий.