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

Определите правило отбора

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

Создайте три столбца с заголовками Code, Status и Approved. Внесите шесть записей: L01 — Ready — Yes; L02 — Draft — Yes; L03 — Ready — No; L04 — Ready — Yes; L05 — Draft — No; L06 — Review — Yes. Каждая строка описывает один урок, коды написаны латинскими буквами.

Ожидаемый результат можно назвать заранее: L01 и L04. Короткие английские значения здесь нужны для однозначного воспроизведения формулы. В рабочей книге вы можете использовать свои названия, но должны согласовать, что означает каждый статус. Функция проверяет значения ячеек и не устанавливает фактическую готовность файлов.

Подготовьте источник и свободное место вывода

Оформите A1:C7 как таблицу Excel с заголовками и назовите её Lessons. Если эта настройка незнакома, сначала выполните пример умной таблицы Excel. Убедитесь, что все шесть записей входят в границы таблицы и рядом нет дополнительного текста, ошибочно включённого в данные.

На том же листе напишите в E1:G1 такие же заголовки. Ячейка E2 станет началом результата; область ниже и справа оставьте свободной. Саму формулу разместите вне объекта Lessons и вне другой таблицы Excel. По документации Microsoft о динамических массивах, такой вывод занимает соседние ячейки и не поддерживается внутри объекта таблицы.

Для примера используйте Excel с поддержкой FILTER: Microsoft 365, Excel 2021 или Excel 2024. Не путайте функцию с обычной кнопкой фильтра в заголовке столбца. Если имя не распознаётся, проверьте версию и язык функций до изменения исходных данных.

Запишите формулу с двумя условиями

В английской локализации формула для E2 выглядит так: =FILTER(Lessons,(Lessons[Status]="Ready")*(Lessons[Approved]="Yes"),"Нет готовых уроков"). В русской локализации используется ФИЛЬТР; разделителем аргументов часто служит точка с запятой: =ФИЛЬТР(Lessons;(Lessons[Status]="Ready")*(Lessons[Approved]="Yes");"Нет готовых уроков"). Фактический разделитель зависит от региональных настроек приложения.

Первый аргумент задаёт выводимые данные. Два сравнения отбирают строки, где одновременно выполняются оба требования. В справке Microsoft по FILTER для сочетания условий И используется умножение логических массивов. Последний аргумент задаёт сообщение для случая без совпадений.

После ввода ожидайте две строки: L01 — Ready — Yes и L04 — Ready — Yes. Заголовки мы написали отдельно, поэтому формула выводит только записи. Сравните все три столбца результата с источником. Проверка одних кодов может скрыть ошибку в выбранном диапазоне вывода.

Испытайте обновление без повторного копирования

Измените Approved у L01 на No. В режиме автоматического вычисления в результате должна остаться только L04. Если данные не изменились, проверьте режим вычисления и выполните пересчёт. Не начинайте исправление с ручного удаления строки результата: источник и формула должны объяснять весь вывод.

Теперь добавьте L07 — Ready — Yes внутрь таблицы Lessons. Убедитесь, что её граница расширилась. Ожидаемый результат после предыдущего изменения — L04 и L07. Структурированные ссылки учитывают строки таблицы; запись под ней, не включённая в её область, не становится частью источника только потому, что стоит рядом.

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

Проверьте пустую выдачу и препятствие разливу

В учебной копии временно измените Approved у L04 и L07 на No. Теперь совпадений нет, и E2 должна показать «Нет готовых уроков». Это сообщение означает отсутствие строк по двум условиям в данном источнике. Оно не доказывает, что вообще ни один урок курса не подготовлен: реестр может быть неполным.

Верните Yes у L04 и L07. Если область вывода занята заметкой, Excel может показать #SPILL! — ошибку размещения массива. Посмотрите, какая ячейка мешает результату. Перенесите нужную заметку в безопасное место, затем повторите проверку. Не очищайте весь лист ради исчезновения ошибки.

Не редактируйте отдельные значения внутри полученного массива. Для изменения статуса вернитесь к Lessons; для изменения правила выберите начальную ячейку E2. Мы держим источник и результат в одной книге, чтобы учебный пример не зависел от доступности внешнего файла и его состояния.

Передайте редактору правило и контрольные ожидания

Подпишите область: «Уроки со Status=Ready и Approved=Yes». Добавьте пояснение, где изменять исходные записи и почему нельзя писать комментарии вплотную под результатом. Число подходящих уроков будет меняться, поэтому маленький свободный участок сегодня может оказаться недостаточным после следующего выпуска.

Сохраните три контрольных ожидания: первоначально L01 и L04; после снятия согласования L01 — только L04; после добавления L07 — L04 и L07. Отдельно укажите ожидаемое сообщение при отсутствии совпадений. Эти проверки удобнее повторить после переименования столбца или передачи книги другому редактору, чем искать причину в большом рабочем реестре.

Полученная выборка помогает выбрать записи для дальнейшей приёмки. Перед публикацией всё равно откройте соответствующие материалы и проверьте их содержимое. Значение Yes — решение, занесённое человеком, а не автоматическая проверка качества урока. Функция поддерживает актуальный список по установленному правилу; ответственность за исходные статусы остаётся у команды.