Условное форматирование Excel: как выделить просроченные задачи подготовки курса

Зафиксируйте смысл просрочки

Для редакционного примера договоримся: задача просрочена, если её плановая дата раньше даты проверки, а статус отличается от «Готово». Срок сегодня ещё не считается просроченным. Пустой срок требует уточнения, но не должен автоматически окрашиваться как прошедшая дата.

Используем фиксированную дату проверки — 4 октября 2026 года. Это позволяет воспроизвести пример в другой день. В F1 введите формулу =ДАТА(2026;10;4) и задайте ячейке формат даты. Рядом подпишите «Дата проверки», чтобы соавтор понимал назначение значения.

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

Соберите контрольные строки

В A1:C1 разместите заголовки «Материал», «Срок», «Статус». В строках 2–6 внесите пять условных задач:

• Сценарий — 02.10.2026 — В работе.

• Слайды — 04.10.2026 — В работе.

• Тест — 02.10.2026 — Готово.

• Тетрадь — пустая дата — В работе.

• Запись — 03.10.2026 — Проверка.

Даты должны быть значениями Excel, а не текстовыми подписями. Для воспроизводимой пробы можно вводить их через ДАТА, меняя день в формуле, затем выбрать привычный формат отображения. Ячейку срока тетради оставьте действительно пустой.

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

Задайте формулу для выделенного диапазона

Выделите A2:C6, начиная с A2. В настольном Excel для Windows откройте «Главная → Условное форматирование → Создать правило» и выберите использование формулы для определения форматируемых ячеек. Введите =И(ЕЧИСЛО($B2);$B2<$F$1;$C2<>"Готово").

Функция И объединяет три проверки: срок является числом, срок раньше даты проверки, статус не равен «Готово». Построение логических условий описано в справке Microsoft. Для английских имён функций запись будет =AND(ISNUMBER($B2),$B2<$F$1,$C2<>"Готово"); разделитель аргументов зависит от настроек Excel.

Назначьте умеренную заливку и заметную границу. В инструкции по условному форматированию правило на формуле применяется, когда результат истинен. Проверьте область применения: =$A$2:$C$6. Заголовки и дату проверки в неё не включайте.

Проверьте ссылки и ожидаемый результат

В $B2 и $C2 закреплены столбцы, но номер строки меняется. Поэтому каждая строка проверяет собственный срок и статус. Ссылка $F$1 полностью закреплена: для всех задач используется одна дата проверки. Если закрепить также строку в $B$2, можно получить окрашивание всего списка по первой задаче.

Сверьте результат с ожиданием: выделены строки 2 и 6. Затем измените статус сценария на «Готово» — должна остаться только запись. Верните статус и перенесите срок сценария на 5 октября: при прежней дате проверки строка также перестанет удовлетворять условию.

Наконец, замените F1 на =ДАТА(2026;10;5), предварительно восстановив исходные пять задач. Теперь незавершённые слайды со сроком 4 октября тоже просрочены. Ожидаются три строки: сценарий, слайды и запись. Такие изменения проверяют обе части правила, а не только один удачный случай.

Разберите пропущенные сроки отдельно

Строка без даты не окрашивается нашим правилом благодаря проверке ЕЧИСЛО. Это не означает, что с ней всё в порядке. На встрече команды просмотрите задачи без срока и назначьте ответственного за уточнение. Если вместо даты введён текст, исправьте тип значения после проверки исходной договорённости.

Статус также должен быть согласован. «Готово» и «Завершено» для этой формулы различаются. Выберите единый словарь, а при необходимости настройте проверку данных и список значений. Не меняйте слова только ради исчезновения заливки, если задача фактически не завершена.

Цвет используйте как вспомогательный сигнал. Срок и статус остаются видимыми текстом, а граница дополняет заливку. Так коллега сможет понять причину внимания к строке без запоминания оттенков и без предположения, что красный всегда означает одно и то же.

Сохраняйте правило при расширении плана

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

Если план уже ведётся как умная таблица Excel, всё равно проверьте добавление записи в своём файле. Обновите дату проверки осознанно и укажите её в отчёте, который отправляете команде.

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