ФИЛЬТР в Excel: рабочая выборка, которая обновляется вместе с таблицей

Руководителю нужен список открытых заявок своего отдела. Копировать строки на отдельный лист неудобно: после обновления остаётся старый список. ФИЛЬТР позволяет создать выборку, которая зависит от исходной таблицы и параметра отдела. Важно оставить место для результата и заранее решить, что показывать при отсутствии строк.

Сделайте источник умной таблицей

Пусть таблица Заявки содержит ID, Отдел, Статус и Сумма. При добавлении новых строк структурированные ссылки расширятся вместе с таблицей. Для параметра отдела используйте отдельную ячейку B1. Подпишите её и по возможности ограничьте выбор допустимым списком.

Если данные приходят через Power Query, сначала обновляется источник, затем пересчитывается выборка. ФИЛЬТР работает с уже загруженными строками: он сам не читает новый файл. В процедуре обновления это два связанных действия.

Задайте условия и пустой результат

Условия для отдела и статуса перемножаются, то есть должны выполняться одновременно. Третий аргумент задаёт результат, если подходящих строк нет. Формулу разместите вне умной таблицы, на свободном участке листа: динамический массив должен развернуться в соседние ячейки.

Если в области результата уже стоят значения или объединённые ячейки, может появиться ошибка разлива. Не переносите формулу вниз построчно: одна формула должна сформировать весь массив. Заголовки можно разместить отдельной строкой над областью.

=ФИЛЬТР(Заявки;(Заявки[Отдел]=B1)*(Заявки[Статус]="Открыта");"Нет открытых заявок")

Отделите рабочую выборку от ввода

Ячейки результата вычисляются формулой. Исправления заявки вносите в источник или специально предназначенную таблицу ввода. Ручная запись в область разлива либо мешает формуле, либо исчезает после обновления. Пользователю полезно показать, где именно можно менять данные.

Текст «Нет открытых заявок» удобен для человека, но последующая формула не должна считать его строкой заявки. Для машинной обработки лучше опираться на источник или отдельный счётчик условий. Не извлекайте число заявок простым подсчётом непустых ячеек всей области.

Примите выборку после изменения данных

В учебной таблице две открытые заявки отдела A и одна закрытая. После выбора A должны остаться только две открытые. Затем закройте одну из них и добавьте новую строку: список должен измениться без копирования формулы. Для отдела без заявок проверьте понятный пустой результат.

Если нужен список отделов для параметра, можно подготовить его через УНИК и СОРТ. Решите, включать ли отделы без текущих заявок: список из справочника и список из факта имеют разный смысл. Это влияет на доступный пользователю выбор.

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

После передачи книги проверьте её в целевой версии Excel. Пример рассчитан на Microsoft 365 с поддержкой динамических массивов. Сохраните небольшой контрольный набор и инструкцию обновления, чтобы коллега мог отличить пустую очередь от не загруженного нового файла.

Соберите обновляемый отчёт на практике

На курсе вы создадите динамические выборки через ФИЛЬТР, УНИК и СОРТ. Затем соберёте отчёт, который обновляется на новых файлах. Это помогает связать удобный рабочий список с подготовкой исходных данных и проверять, что следующий месяц действительно попал в результат.

Откройте программу курса «Продвинутый Excel: Power Query, Power Pivot и автоматизация отчётов».

Начните с пробного урока о рабочей книге и первом запросе Power Query.

Для следующего этапа: объединение файлов из папки и обновление отчёта.