Функция ФИЛЬТР (FILTER) в Excel и Google Sheets

Кратко

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

Где работает:
✅ Excel Microsoft 365 (Office 365)
✅ Excel 2021 (и новее)
✅ Google Sheets (все версии)
❌ Excel 2019, 2016 и старше — функция недоступна (вместо неё придётся использовать сложные формулы массива с ИНДЕКС и НАИМЕНЬШИЙ)

Возвращает:
Динамический массив строк (или столбцов), соответствующих условиям.

Сложность:
★☆☆☆☆ — Для начинающих (одно условие)
★★☆☆☆ — Средняя (несколько условий, вложенные FILTER)

Для чего нужна функция

FILTER — это главный инструмент для динамической фильтрации данных в Excel и Google Sheets. Вот основные сценарии:

1. Извлечение данных по условию — показать только те строки, где значение в столбце соответствует критерию .
2. Создание динамических отчётов — отчёт, который автоматически обновляется при изменении данных или выборе параметра в выпадающем списке .
3. Поиск всех совпадений — в отличие от ВПР (VLOOKUP), который находит только первое совпадение, FILTER возвращает ВСЕ подходящие строки .
4. Сложные условия — комбинирование нескольких условий с логикой И (AND) и ИЛИ (OR) .
5. Создание зависимых выпадающих списков — список городов, который меняется в зависимости от выбранной страны .
6. Горизонтальная фильтрация — отбор нужных столбцов, а не строк (это умеет только FILTER, встроенные инструменты Excel не могут) .

Совместимость

Это критически важный момент — синтаксис FILTER принципиально различается в Excel и Google Sheets.

Платформа	Синтаксис	Поддержка
Excel Microsoft 365	=FILTER(array, include, [if_empty])	✅ Да
Excel 2021	=FILTER(array, include, [if_empty])	✅ Да
Excel 2019 и старше	Недоступна	❌ Нет
Google Sheets	=FILTER(range, condition1, [condition2, ...])	✅ Да
Google Sheets (офлайн)	То же	✅ Да

Главное отличие

Характеристика	Excel	Google Sheets
Второй аргумент	Условие-фильтр (include)	Условие 1 (condition1)
Третий аргумент	Значение, если ничего не найдено (if_empty)	Дополнительное условие (condition2)
Если совпадений нет	Возвращает значение из if_empty или #CALC!	Возвращает #N/A

Это самая частая причина ошибок при переносе формул между платформами. Одна и та же формула =FILTER(A2:B10, A2:A10>5, C2:C10<10):

• В Excel: вернёт строки где A>5, а если таких нет — покажет массив C2:C10<10 (бессмыслица, но не ошибка)
• В Google Sheets: вернёт строки где A>5 И C<10 (два условия)

Синтаксис

В Microsoft Excel

=ФИЛЬТР(массив; включить; [если_пусто])

или

=FILTER(array; include; [if_empty])

В Google Sheets

=FILTER(диапазон; условие1; [условие2; ...])

или

=FILTER(range; condition1; [condition2; ...])

Разбор аргументов

Для Excel:

Аргумент	Обязательный	Описание
массив (array)	✅ Да	Диапазон ячеек, который нужно отфильтровать. Может быть одним столбцом, строкой или таблицей.
включить (include)	✅ Да	Массив логических значений (ИСТИНА/ЛОЖЬ). Определяет, какие строки/столбцы включать в результат. Обычно это условие вида A2:A100="Товар".
если_пусто (if_empty)	❌ Нет	Значение, которое будет показано, если ни одна строка не соответствует условию. Если опущен — возвращается ошибка #CALC!.

Для Google Sheets:

Аргумент	Обязательный	Описание
диапазон (range)	✅ Да	Фильтруемые данные .
условие1 (condition1)	✅ Да	Столбец или строка с логическими значениями ИСТИНА/ЛОЖЬ (или формула, которая их возвращает). Определяет, пройдёт ли строка/столбец через фильтр .
условие2, … (condition2, …)	❌ Нет	Дополнительные условия. Все условия должны быть одного типа (только столбцы или только строки). Смешивать нельзя .

Частые задачи

Задача	Excel	Google Sheets
Одно условие (строки, где продавец = «Иванов»)	=ФИЛЬТР(A2:C100; B2:B100="Иванов")	=FILTER(A2:C100; B2:B100="Иванов")
Два условия (И)	=ФИЛЬТР(A2:C100; (B2:B100="Иванов")*(C2:C100>1000))	=FILTER(A2:C100; B2:B100="Иванов"; C2:C100>1000)
Два условия (ИЛИ)	=ФИЛЬТР(A2:C100; (B2:B100="Иванов")+(B2:B100="Петров"))	=FILTER(A2:C100; (B2:B100="Иванов")+(B2:B100="Петров"))
Если ничего не найдено	=ФИЛЬТР(A2:C100; B2:B100="Иванов"; "Нет данных")	=IFNA(FILTER(A2:C100; B2:B100="Иванов"); "Нет данных")
Уникальные + отфильтрованные	=УНИК(ФИЛЬТР(A2:A100; B2:B100="Овощи"))	=UNIQUE(FILTER(A2:A100; B2:B100="Овощи"))
Отфильтрованные + отсортированные	=СОРТ(ФИЛЬТР(A2:C100; B2:B100="Иванов"); 2; -1)	=SORT(FILTER(A2:C100; B2:B100="Иванов"); 2; FALSE)

Простые примеры

Пример 1. Базовый фильтр — все продажи конкретного продавца

Исходные данные (A2:C11):

Дата	Продавец	Сумма
01.01	Иванов	100
02.01	Петров	200
03.01	Иванов	150
04.01	Сидоров	300
05.01	Иванов	250

Задача: показать все строки, где продавец = «Иванов».

Формула (Excel и Google Sheets одинаково): =FILTER(A2:C6; B2:B6="Иванов")

Результат:

Дата	Продавец	Сумма
01.01	Иванов	100
03.01	Иванов	150
05.01	Иванов	250

Пример 2. Два условия (И) — Excel

Задача: продажи Иванова на сумму больше 120.

Excel: =ФИЛЬТР(A2:C6; (B2:B6="Иванов")*(C2:C6>120))

Результат:

Дата	Продавец	Сумма
03.01	Иванов	150
05.01	Иванов	250

---

Пример 3. Два условия (И) — Google Sheets

Задача: та же самая — продажи Иванова на сумму больше 120.

Google Sheets: =FILTER(A2:C6; B2:B6="Иванов"; C2:C6>120)

Результат: тот же.

Обратите внимание: в Google Sheets условия просто перечисляются через запятую .

Пример 4. Условие ИЛИ (обе платформы)

Задача: продажи Иванова или Сидорова.

Формула (обе платформы): =FILTER(A2:C6; (B2:B6="Иванов")+(B2:B6="Сидоров"))

Результат:

Дата	Продавец	Сумма
01.01	Иванов	100
03.01	Иванов	150
04.01	Сидоров	300
05.01	Иванов	250

---

Пример 5. Обработка пустого результата — Excel

Задача: найти продажи продавца «Смирнов» (такого нет в списке).

Excel: =ФИЛЬТР(A2:C6; B2:B6="Смирнов"; "Продавец не найден")

Результат: Продавец не найден

Пример 6. Обработка пустого результата — Google Sheets

Google Sheets: =IFNA(FILTER(A2:C6; B2:B6="Смирнов"); "Продавец не найден")

Результат: Продавец не найден

В Google Sheets нет встроенного аргумента «если пусто», поэтому используется обёртка IFNA .

Практические сценарии использования

1. Финансы: отчёт по операциям за период

Задача: показать все транзакции за выбранный месяц.

Исходные данные: столбцы A — дата, B — описание, C — сумма.

В ячейке E1 выбираем месяц (например, «Январь»).

Формула (Excel): =ФИЛЬТР(A2:C1000; МЕСЯЦ(A2:A1000)=МЕСЯЦ(E1&"1"); "Нет операций")

Результат: все транзакции за январь. При смене месяца в E1 список обновляется автоматически.

2. Аналитика: топ-10 по выручке

Задача: найти 10 лучших клиентов по сумме заказов.

Шаг 1: получить уникальных клиентов и суммы (с помощью УНИК + СУММЕСЛИ).
Шаг 2: отфильтровать тех, кто входит в топ-10 по сумме.

Но проще: использовать FILTER + СОРТ с ограничением:

=СОРТ(УНИК(A2:A100); СУММЕСЛИ(A:A; УНИК(A2:A100); B:B); -1) — не совсем FILTER, но показывает подход.

3. Продажи: динамический отчёт с выпадающим списком

Задача: пользователь выбирает менеджера из выпадающего списка, и отчёт показывает только его продажи .

В ячейке G5 создаётся выпадающий список с именами менеджеров.

Формула (Excel): =ФИЛЬТР(Таблица_продаж[[Товар]:[Сумма]]; Таблица_продаж[Менеджер]=G5; "Нет продаж")

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

4. HR: список сотрудников без дубликатов в проектах

Задача: найти сотрудников, которые ещё не назначены ни на один проект на текущую неделю .

Формула (Excel):

=СОРТ(
  ФИЛЬТР(
    Workers[Имя];
    СЧЁТЕСЛИМН(Tasks[На_неделе]; Workers[Имя])=0;
    "Все назначены"
  )
)

Результат: список свободных сотрудников. После назначения на проект они автоматически исчезают из списка.

5. Логистика: заказы по городу с сортировкой

Задача: все заказы в конкретный город, отсортированные по сумме (от больших к малым).

Формула (Excel): =СОРТ(ФИЛЬТР(A2:D1000; C2:C1000="Москва"); 4; -1)
(СОРТ по 4-му столбцу — сумма, в порядке убывания)

6. Горизонтальная фильтрация — только в Excel

Сценарий: таблица, где данные расположены горизонтально (столбцы — это объекты, строки — атрибуты). Встроенная фильтрация в Excel работает только по строкам, но FILTER умеет фильтровать и столбцы .

Исходные данные: строка с заголовками, строка с условиями (ИСТИНА/ЛОЖЬ) над таблицей.

Формула: =ФИЛЬТР(Таблица!A4:Z14; Таблица!A2:Z2)

Результат: остаются только те столбцы, над которыми в строке условий стоит ИСТИНА .

Частые ошибки пользователей

1. Путают синтаксис Excel и Google Sheets

Ситуация: пользователь переносит формулу из Excel в Google Sheets (или наоборот) и получает неожиданный результат или ошибку.

Пример: в Excel формула с третьим аргументом "Нет данных":
=FILTER(A2:B10; A2:A10>5; "Нет данных")

В Google Sheets это интерпретируется как два условия:
=FILTER(A2:B10; A2:A10>5; "Нет данных") — второй условием становится текст, что вызывает ошибку #ЗНАЧ! .

Решение: проверяйте синтаксис для конкретной платформы.

2. Используют FILTER вместо удаления данных

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

Решение: FILTER не изменяет исходные данные — он только показывает отфильтрованную копию. Если нужно физически удалить строки — используйте встроенную фильтрацию на вкладке «Данные».

3. Неправильное комбинирование условий (И/ИЛИ)

Ситуация: пользователь пишет =FILTER(A2:C100; B2:B100="Иванов"; C2:C100>1000) в Google Sheets, думая, что это ИЛИ, а получает И (оба условия должны выполняться).

Решение:

• И (AND) — в Excel: умножение (условие1)*(условие2); в Google Sheets: перечисление через запятую.
• ИЛИ (OR) — в обеих платформах: сложение (условие1)+(условие2) .

4. Забывают про пустые ячейки и невидимые символы

Ситуация: в ячейках есть пробелы или пустые строки, условие A2:A100="Иванов" не срабатывает.

Решение: очистите данные с помощью СЖПРОБЕЛЫ (TRIM):

• Excel: =ФИЛЬТР(A2:C100; СЖПРОБЕЛЫ(B2:B100)="Иванов")
• Google Sheets: =FILTER(A2:C100; TRIM(B2:B100)="Иванов")

5. Пытаются использовать FILTER в Excel 2019

Ситуация: пользователь вводит =FILTER(...) и видит #ИМЯ?.

Решение: FILTER недоступен в Excel 2019 и старше . Используйте альтернативу с ИНДЕКС + НАИМЕНЬШИЙ + ЕСЛИ как формулу массива , или перейдите на Microsoft 365 / Google Sheets.

6. Не оставляют место для растекания (spill) — Excel

Ситуация: результат FILTER занимает 10 строк, но в соседних ячейках есть данные. Excel выдаёт #СПИЛ!.

Решение: очистите ячейки снизу и справа от формулы, куда может попасть результат.

Типичные ошибки (технические)

1. #CALC! — только Excel

Причина: результат фильтрации пуст, а аргумент if_empty не указан .

Решение: добавьте третий аргумент: =ФИЛЬТР(...; "Нет данных").

2. #N/A — Google Sheets (и Excel при ошибках)

Причина: в Google Sheets — пустой результат фильтрации. В Excel — ошибка в условии .

Решение:

• Google Sheets: оберните в IFNA: =IFNA(FILTER(...); "Нет данных").
• Excel: проверьте условие, возможно, ссылаетесь на неправильный диапазон.

3. #ЗНАЧ! (#VALUE!)

Причина:

• В Excel: массивы разных размеров (array и include).
• В Google Sheets: условия разного типа (одно для строк, другое для столбцов) .

Решение: убедитесь, что все аргументы имеют одинаковую размерность.

4. #ССЫЛКА! (#REF!)

Причина: исходный диапазон удалён.

Решение: проверьте ссылки, используйте именованные диапазоны.

5. #ИМЯ? (#NAME?)

Причина: FILTER недоступна в вашей версии Excel.

Решение: обновитесь до Microsoft 365 / Excel 2021 или используйте Google Sheets.

Полезные комбинации с другими функциями

1. FILTER + SORT (ФИЛЬТР + СОРТ) — сортировка результата

Пример: продажи Иванова, отсортированные по сумме (от больших к малым).

Excel: =СОРТ(ФИЛЬТР(A2:C100; B2:B100="Иванов"); 3; -1)

Google Sheets: =SORT(FILTER(A2:C100; B2:B100="Иванов"); 3; FALSE)

2. FILTER + UNIQUE (ФИЛЬТР + УНИК) — уникальные значения по условию

Пример: уникальные товары только из категории «Овощи».

Excel: =УНИК(ФИЛЬТР(A2:A100; B2:B100="Овощи"))

3. FILTER + SUM (ФИЛЬТР + СУММ) — сумма по условию

Пример: общая сумма продаж Иванова.

Excel: =СУММ(ФИЛЬТР(C2:C100; B2:B100="Иванов"; 0))

С if_empty=0 сумма не ломается, если совпадений нет .

4. FILTER + COUNT (ФИЛЬТР + СЧЁТ) — количество по условию

Пример: сколько продаж у Иванова.

Excel: =СЧЁТ(ФИЛЬТР(B2:B100; B2:B100="Иванов"))

5. FILTER + XLOOKUP (ФИЛЬТР + ПРОСМОТРX) — поиск в отфильтрованных данных

Пример: найти цену товара, но только в категории «Овощи».

Excel: =XLOOKUP("Яблоки"; ФИЛЬТР(A2:A100; B2:B100="Овощи"); ФИЛЬТР(C2:C100; B2:B100="Овощи"); "Не найден")

6. Вложенные FILTER — фильтрация по строкам и столбцам (Google Sheets)

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

Google Sheets: =FILTER(FILTER(A2:D100; B2:B100="Иванов"); {TRUE; FALSE; TRUE; TRUE})

Сначала фильтруем строки по условию, затем из результата оставляем только нужные столбцы (второй FILTER фильтрует по столбцам) .

7. FILTER + ARRAYFORMULA — сложные вычисления в условии (Google Sheets)

Пример: фильтрация строк, где разница между столбцами больше 10.

Google Sheets: =FILTER(A2:C100; ARRAYFORMULA(C2:C100 - B2:B100 > 10))

Аналоги и альтернативы

Инструмент	Описание	Когда использовать
Встроенный фильтр (Данные → Фильтр)	Стандартный инструмент Excel/Sheets для ручной фильтрации.	Когда нужно быстро посмотреть данные, а не создавать динамический отчёт.
Расширенный фильтр	Классический инструмент для фильтрации с копированием результата в другое место.	В старых версиях Excel без FILTER.
Сводная таблица	Агрегирует данные по категориям.	Когда нужны не просто строки, а суммы, средние, количества по группам.
QUERY (только Google Sheets)	SQL-подобный язык запросов: =QUERY(A:C; "SELECT * WHERE B='Иванов'").	Для сложных запросов, включающих группировку, агрегацию, сортировку в одной формуле.
ИНДЕКС + НАИМЕНЬШИЙ + ЕСЛИ (Excel 2019 и старше)	Формула массива для эмуляции FILTER: =ЕСЛИОШИБКА(ИНДЕКС(...); "").	В устаревших версиях Excel, где нет FILTER .

Сравнение FILTER и VLOOKUP

Критерий	VLOOKUP	FILTER
Возвращает	Только первое совпадение	Все совпадения
Сложность	Простая (но ограничена)	Простая (но мощнее)
Направление	Только слева направо	Любое направление
Несколько условий	Только через вспомогательные столбцы	Встроенная поддержка
Динамичность	Обновляется, но не расширяется	Динамический массив + растекание
Обработка пустого результата	Только через IFERROR	Встроенная (Excel) или IFNA (Sheets)

Часто задаваемые вопросы

Вопрос: В каких версиях Excel работает FILTER?
Ответ: Excel Microsoft 365 и Excel 2021. В Excel 2019 и старше — нет . В Google Sheets — работает у всех.

Вопрос: Как сделать несколько условий И (AND) в Excel?
Ответ: Умножьте условия: =ФИЛЬТР(A2:C100; (B2:B100="Иванов")*(C2:C100>1000)).

Вопрос: Как сделать несколько условий И (AND) в Google Sheets?
Ответ: Перечислите их через запятую: =FILTER(A2:C100; B2:B100="Иванов"; C2:C100>1000) .

Вопрос: Как сделать условия ИЛИ (OR) в обеих платформах?
Ответ: Сложите условия: =ФИЛЬТР(A2:C100; (B2:B100="Иванов")+(B2:B100="Петров")) .

Вопрос: Что делать, если FILTER возвращает пустой результат?
Ответ:

• Excel: добавьте третий аргумент =ФИЛЬТР(..., "Нет данных").
• Google Sheets: оберните в IFNA: =IFNA(FILTER(...); "Нет данных") .

Вопрос: Можно ли отфильтровать столбцы, а не строки?
Ответ: Да, в Excel — с помощью FILTER и горизонтального массива условий . В Google Sheets — через вложенный FILTER, где первый фильтрует строки, второй — столбцы .

Вопрос: Почему FILTER не находит значения с датами?
Ответ: Возможно, даты записаны как текст. Используйте преобразование или проверьте формат ячеек.

Заключение

FILTER (ФИЛЬТР) — это одна из самых мощных и полезных функций в современных таблицах. Она позволяет создавать динамические отчёты, которые обновляются автоматически, избавляет от необходимости вручную перебирать тысячи строк и открывает возможности для создания интерактивных дашбордов.

Главная сложность — различия в синтаксисе между Excel и Google Sheets. Третий аргумент в Excel — это «если пусто», а в Google Sheets — дополнительное условие. Если вы работаете в обеих платформах, всегда проверяйте синтаксис.

Освойте FILTER, и вы сможете за секунды извлекать из больших массивов данных именно то, что нужно. В сочетании с UNIQUE, SORT и XLOOKUP эта функция превращает Excel и Google Sheets в полноценные инструменты для работы с данными.