← Блог

Условное форматирование в Excel: светофор по срокам за 10 минут

Реестр поручений, журнал писем, список договоров: везде есть сроки, и везде их кто-то должен отслеживать. Глазами это делать мучительно: двадцать строк ещё терпимо, двести уже нет. Решение простое: пусть Excel сам красит строки. Просрочено красным, горит жёлтым, всё в порядке зелёным. Ниже пошаговая инструкция, как сделать такой светофор по срокам за десять минут, используя только обычные функции СЕГОДНЯ и РАБДЕНЬ и условное форматирование.

Подготовка: какие колонки нужны

Минимальный набор для светофора:

• Документ или поручение (что контролируем);
• Дата поступления;
• Срок в днях (например, 5 рабочих или 30 календарных);
• Плановая дата исполнения (считаем формулой);
• Фактическая дата исполнения (заполняется, когда дело сделано);
• Статус (формула).

Пусть данные лежат так: A это название, B дата поступления, C срок в днях, D плановая дата, E фактическая дата, F статус. Первая строка данных вторая.

Шаг 1. Посчитайте плановую дату

Если срок в рабочих днях, используйте РАБДЕНЬ, которая пропускает выходные и праздники.

• Шаг 1. На отдельном листе «Праздники» запишите в столбец A все нерабочие праздничные дни года, включая перенесённые выходные. Для 2026 и 2027 годов сверьте список с производственным календарём, утверждённым постановлением Правительства (актуальную версию проверяйте).
• Шаг 2. В D2 напишите формулу РАБДЕНЬ(B2;C2;Праздники!$A$2:$A$40).
• Шаг 3. Протяните формулу вниз и задайте столбцу формат даты.

Если срок календарный (например, ответ на обращение в течение тридцати дней), формула проще: B2+C2. Какой срок у вашей категории дел, определяется нормативным документом или регламентом организации, а не таблицей. Проверьте нужную норму в актуальной редакции.

Шаг 2. Сделайте столбец статуса

Статус нужен и людям, и для условного форматирования.

• Шаг 1. В F2 напишите: ЕСЛИ(E2="";ЕСЛИ(ЗНАК(СЕГОДНЯ()-D2)=1;"просрочено";ЕСЛИ(ЗНАК(D2-СЕГОДНЯ()-4)=-1;"горит";"в срок"));"исполнено").
• Шаг 2. Протяните вниз.
• Шаг 3. Проверьте: поставьте плановую дату вчерашней, и статус должен стать «просрочено».

Функция ЗНАК здесь заменяет сравнение: она возвращает 1, если число положительное (сегодня позже плановой даты), и минус 1, если отрицательное. Функция СЕГОДНЯ пересчитывается при каждом открытии файла, поэтому светофор всегда актуален. Значение «3 дня» для статуса «горит» можно вынести в отдельную ячейку и менять по необходимости.

Шаг 3. Настройте условное форматирование

Теперь самое интересное: цвет.

• Шаг 1. Выделите всю область данных, например A2:F500.
• Шаг 2. На вкладке «Главная» нажмите «Условное форматирование», затем «Создать правило».
• Шаг 3. Выберите «Использовать формулу для определения форматируемых ячеек».
• Шаг 4. Для красного напишите формулу $F2="просрочено" и нажмите «Формат», вкладка «Заливка», выберите красный цвет.
• Шаг 5. Повторите для жёлтого: $F2="горит".
• Шаг 6. Для зелёного: $F2="исполнено".

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

Порядок правил важен: «Управление правилами» позволяет менять приоритет. Правило «исполнено» поставьте первым, чтобы закрытые дела не красились красным.

Шаг 4. Добавьте счётчик дней

Цвет это хорошо, но иногда важно знать, сколько дней осталось.

• Шаг 1. В G2 напишите ЕСЛИ(E2="";ЧИСТРАБДНИ(СЕГОДНЯ();D2;Праздники!$A$2:$A$40);"").
• Шаг 2. Отрицательное число означает просрочку в рабочих днях.
• Шаг 3. Отсортируйте или отфильтруйте по этому столбцу, и самые срочные дела окажутся вверху.

Мини-пример для строки:

• Поручение поступило 12.10.2026, срок 5 рабочих дней.
• Плановая дата считается с учётом выходных: 19.10.2026.
• За три дня до этой даты строка станет жёлтой, после неё красной, если нет фактической даты.

Шаг 5. Защитите формулы и проверьте

Готовое решение с уже настроенными правилами лежит здесь: светофор сроков. Если собираете сами, не пропустите защиту.

• Шаг 1. Разблокируйте ячейки, куда вносятся данные (поступление, срок, факт): «Формат ячеек», «Защита».
• Шаг 2. Включите «Защитить лист», чтобы формулы случайно не удалили.
• Шаг 3. Проверьте крайние случаи: пустые строки, фактическая дата раньше плановой, срок ноль.
• Шаг 4. Сохраните чистую копию шаблона без данных.

Пустые строки лучше сделать «молчащими», добавив условие ЕСЛИ(B2="";"";...) в формулы, иначе по ним будут появляться странные даты.

Где ещё пригодится светофор

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

Частые ошибки

• Праздники не внесены. РАБДЕНЬ без списка пропускает только выходные и ошибается в праздничные недели.
• Список праздников устарел, например, на этот год и не на следующий. Обновляйте его в конце года.
• Забыли доллар в формуле правила, цвет попадает не в ту строку.
• Календарные дни вместо рабочих (или наоборот): сначала выясните, как считается ваш срок.
• Даты записаны как текст. Формулы выдают ошибку или неверный результат.
• Правило «исполнено» стоит ниже «просрочено», и закрытые дела остаются красными.

Если не хочется собирать самому

Описанная схема работает, но сборка занимает время, а на проверку уходит ещё больше. Если нужен готовый файл, возьмите бесплатную таблицу «Контроль сроков: светофор»: там срок считается через РАБДЕНЬ с праздниками, строки подсвечиваются автоматически, а вам остаётся вносить поручения. Другие таблицы для контроля собраны в разделе «Контроль».

Таблица из статьи

Читайте также