Операции · Практическое руководство

Excel: как подсветить просроченные строки по дате и статусу

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

3 мин чтения
Иллюстрация к статье: Excel: как подсветить просроченные строки по дате и статусу
Если у вас одна минута

Коротко о главном

Чтобы выделить просроченные задачи в Excel, создайте правило условного форматирования для всей строки: дата срока раньше сегодня, а статус ещё не завершён. Форматирование только меняет вид ячейки — оно не рассылает уведомления и не проверяет, правильно ли заполнены дата и статус.

  • Разделите в таблице срок и статус задачи по отдельным столбцам.
  • Применяйте формулу ко всему диапазону строк, фиксируя столбцы даты и статуса.
  • Проверьте правило на пустой дате, просроченной и будущей задаче.
  • Подсветка — визуальная проверка списка, а не автоматическое оповещение исполнителя.

1. Подготовьте данные для правила

Сделайте отдельные столбцы для задачи, срока и статуса. Например, в A находится задача, в B — срок, в C — статус, а D содержит ответственного. Укажите дату как дату Excel, а не как текстовую строку: иначе условие сравнения может не сработать так, как вы ожидаете.

Заранее определите, что считать просрочкой. Ниже задача считается просроченной, если срок прошёл до сегодняшней даты и статус не равен «Завершено». Задача со сроком сегодня не подсвечивается; если в вашем процессе она должна выделяться, замените строгое сравнение на условие «меньше или равно».

2. Создайте правило для строк таблицы

Выделите диапазон строк, например A2:D200, не включая заголовок. Откройте «Главная» → «Условное форматирование» → «Создать правило» и выберите правило на основе формулы. Для английского интерфейса формула может выглядеть так: =AND($B2<>"",$C2<>"Завершено",$B2<TODAY()). В русской локализации функции и разделители аргументов могут отображаться иначе; формула должна проверять те же три условия.

Задайте заметную, но спокойную заливку и сохраните правило. Знак $ закрепляет столбцы B и C, а номер строки остаётся относительным: при применении к строке 3 Excel проверит B3 и C3. Убедитесь в менеджере правил, что диапазон «Применяется к» включает нужные строки и не захватывает заголовок.

3. Проверьте граничные случаи

Перед использованием протестируйте несколько строк: просроченный срок и незавершённая задача должны выделиться; завершённая задача с прошлой датой и задача с будущим сроком — нет. Пустая дата также не должна выделяться. Если результат иной, проверьте адрес первой строки в формуле и порядок столбцов.

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

4. Обслуживайте правило после изменений

Когда диапазон задач расширился, проверьте поле «Применяется к» в менеджере правил и включите новые строки. При копировании листа или вставке из другой книги правила форматирования могут копироваться или менять порядок; Microsoft описывает управление и приоритет правил отдельно.

Если задача считается просроченной по рабочему календарю, а не по календарным дням, одной формулы с TODAY() недостаточно: понадобится согласованный календарь рабочих дней и отдельное правило. Не выдавайте цвет за SLA, прогноз или автоматическое уведомление.

5. Используйте цвет как очередь на разбор

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

Для обзора изменений по магазину можно начать с доступных показателей в HelpStat «Рука на пульсе». Таблица задач и сигналы инструмента решают разные задачи: эта настройка Excel сама по себе не подключает магазин и не отправляет сообщения.

Источники и методика

Источники ниже помогают проверить определения и возможности отчётов. Методика разбора и учебные расчёты подготовлены редакцией HelpStat. Доступность отчётов и условия работы проверяйте в своём кабинете. Расчётные примеры в статье — учебные, а не результаты клиентов.

Как устроены материалы HelpStat

Все материалы блога
Следующий шаг — за вами

У каждого товара
есть точка роста.

Найдите свою с аналитикой HelpStat.

Команда HelpStat

Связаться с HelpStat