Дата публикации: 14.05.2026

Условное форматирование для товаров с истекающим сроком годности

Хочу себе такие же кнопки
2a75c51f

Условное форматирование для товаров с истекающим сроком годности

Что вы получите после урока

  • Понимание, как быстро визуально определять товары, которые скоро «съедят» срок годности.
  • Умение построить автоматическую подсветку в Excel/Google Sheets без написания кода.
  • Навык создания гибких правил, которые работают даже при изменении данных.

1. Почему условное форматирование – ваш «золотой помощник»

Представьте, что вы отвечаете за склад, где каждый день приходят новые партии, а срок годности у продуктов разный. Если проверять каждую ячейку вручную, вы упустите шанс продать товар до того, как он станет непригодным. Условное форматирование (англ. Conditional Formatting) – это как сигналы светофора в таблице: зелёный – всё в порядке, жёлтый – внимание, красный – срочно действовать.

2. Основные понятия

Термин Описание Пример
Условие Логическое выражение, которое проверяется для каждой ячейки. =TODAY()>B2-7
Формат Визуальное оформление, которое применяется, если условие истинно. Красный фон, жирный шрифт
Диапазон Область ячеек, к которой привязывается правило. A2:A500
Ссылка Относительная (B2) или абсолютная ($B$2). При копировании правила меняется только относительная часть.

3. Подготовка данных

  1. Колонки (рекомендовано):

    • A – Наименование товара
    • B – Дата производства (формат дд.мм.ггг)
    • C – Срок годности, дней (число)
    • D – Дата окончания (формула =B2+C2)

  2. Формула даты окончания (вставьте в D2 и протяните вниз): 

    =B2 + C2

    Зачем? Все дальнейшие правила будут сравнивать текущую дату с этой колонкой, а не рассчитывать каждый раз «на лету».

  3. Проверьте формат даты – в Excel: Главная → Формат ячеек → Дата. В Google Sheets: Формат → Число → Дата.

4. Создание правил условного форматирования

4.1. Сигнал «свежий» – зелёный фон (срок более 30 дней)

  • Диапазон: D2:D500 (можно добавить A2:C500, если хотите подсвечивать всю строку).

  • Условие: 

    =D2 - TODAY() > 30

  • Формат: зелёный заливка (#C6EFCE), чёрный текст.

4.2. Сигнал «внимание» – жёлтый фон (от 7 до 30 дней)

  • Условие: 

    =AND(D2 - TODAY() <= 30, D2 - TODAY() > 7)

  • Формат: жёлтый заливка (#FFEB9C).

4.3. Сигнал «срочно» – красный фон (до 7 дней включительно)

  • Условие: 

    =D2 - TODAY() <= 7

  • Формат: красный заливка (#FFC7CE), жирный шрифт.

Подсказка: В Google Sheets правила идут в порядке их создания. Чтобы «красный» имел приоритет, разместите его выше в списке правил.

4.4. Особый случай – «просрочено» (дата уже прошла)

  • Условие: 

    =D2 < TODAY()

  • Формат: тёмно‑красный фон, белый текст, зачеркивание.

5. Тонкая настройка: динамический порог

Иногда компания меняет политику: вместо 7 дней – 5 дней. Чтобы не править каждое правило вручную, вынесем порог в отдельную ячейку, например F1.

  • В F1 пишем 5 (можно добавить подпись «Порог в днях»).

  • Правило «срочно» меняем на: 

    =D2 - TODAY() <= $F$1

  • Правило «внимание» меняем на: 

    =AND(D2 - TODAY() > $F$1, D2 - TODAY() <= 30)

Теперь достаточно изменить значение в F1, и всё форматирование обновится автоматически.

6. Как проверить, что всё работает

  1. Тестовый набор: Добавьте несколько строк с датой окончания в прошлом, в ближайших 5 днях, в 15 днях и в 45 днях.
  2. Смоделируйте «завтрашний день»: В Excel можно переопределить функцию TODAY() через Параметры → Формулы → Параметры расчёта → Текущая дата (для теста). В Google Sheets проще – в отдельной ячейке G1 задайте «текущую дату» и замените TODAY() на $G$1 в формулах.

Если цвета соответствуют ожиданиям, правило построено правильно.

7. Расширенные возможности

Возможность Как реализовать Когда полезно
Подсветка всей строки Выберите диапазон A2:D500, в условии используйте $D2 (абсолютный столбец). Чтобы сразу видеть название товара и остальные параметры.
Иконки‑индикаторы В Excel → Условное форматирование → Правила с набором иконок. Выберите три иконки (зеленый, желтый, красный). Когда пространство таблицы ограничено, а визуальный сигнал нужен.
Автоматическое копирование формата Сохраните правило в шаблоне, а затем используйте Формат по образцу (Ctrl+Shift+C). При работе с новыми листами, где структура одинаковая.
Событийный триггер (Google Sheets) Скрипт Apps Script, который каждый вечер применяет clearFormat() и setConditionalFormatRules(). Для больших таблиц, где правила могут «залипать» после массового импорта.

8. Ошибки, которые часто встречаются

Ошибка Причина Как исправить
Формула не меняет цвет Условие возвращает FALSE из‑за неверного формата даты. Убедитесь, что колонка D действительно дата, а не текст.
Смещение ссылок при копировании В условии использованы относительные ссылки (D2), а диапазон расширяется. Примените абсолютную ссылку $D2 (стробец фиксирован).
Конфликт правил Два правила «красный» и «жёлтый» активны одновременно. Установите порядок приоритетов в списке правил.
Порог в ячейке не учитывается Ссылка $F$1 забыта в одной из формул. Проверьте каждое правило, замените F1 на $F$1.

9. Практика для закрепления

  1. Создайте таблицу из 20 товаров, задайте даты производства и срок годности (разные значения). Добавьте колонку «Дата окончания» и сформируйте правила, описанные выше. Оцените, насколько быстро вы находите товары, требующие продажи.

  2. Измените порог в ячейке F1 с 7 до 3 дней. Как изменятся цвета? Сравните визуальный результат с ожидаемым.

  3. Добавьте иконки‑индикаторы (зеленый, желтый, красный) вместо заливки. Сохраните правило и проверьте, что при изменении даты окончания цвет меняется автоматически.

  4. Смоделируйте просроченный товар: задайте дату окончания в прошлом и убедитесь, что применяется правило «просрочено» (тёмно‑красный, зачеркивание).

  5. Автоматизация (по желанию): напишите простой скрипт Apps Script, который каждый день в полночь обновляет правила, используя текущую дату из ячейки G1.

10. Итоги

  • Условное форматирование превращает сухие цифры в понятные сигналы.
  • Правила построены на логических выражениях (=TODAY()>..., =AND(...)).
  • Гибкость достигается через абсолютные ссылки и вынесенные пороги.
  • Тестирование и проверка формата даты – обязательный шаг перед запуском в продакшн.

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


Почему Excel без VBA быстрее внедрить в малой логистике
Структура исходных данных: что должно быть в таблице «Остатки на складах»
Структура исходных данных: таблица «Поступления товара»
Структура исходных данных: таблица «Отгрузки и продажи»
Создание умной таблицы (Ctrl+T) для автоматического расширения диапазонов
Правила именования складов и ячеек хранения
Формула СУММЕСЛИ для подсчёта остатков по конкретному товару
Формула СУММЕСЛИМН для расчёта остатков по товару + складу
Расчёт свободного объёма склада в паллето-местах
Создание справочника «Товары» с габаритами и весом единицы
Формула ВПР для подстановки габаритов из справочника в таблицу заказов
Автоматический расчёт веса партии по ВПР + умножение
Автоматический расчёт объёма партии (длинаширинавысотаколичество)
Условное форматирование для подсветки отрицательных остатков (красным)
Условное форматирование для подсветки нулевых остатков (серым)
Условное форматирование для подсветки страхового запаса (жёлтым)
Создание выпадающего списка складов через «Проверку данных»
Создание выпадающего списка товаров через «Проверку данных»
Динамическая таблица остатков с выбором склада через выпадающий список
Формула ЕСЛИ для сигнала «Срочный заказ» при остатке ниже нормы
Создание простого дашборда остатков на одном листе
Таблица «Заявки на перевозку»: номер, дата, маршрут, вес, объём
Справочник автомобилей: тип, грузоподъёмность, объём кузова, расход топлива
Формула ВПР для подбора авто по грузоподъёмности (ближайшая сверху)
Дополнительная проверка: объём груза < объёма кузова
Формула И / ИЛИ для одновременной проверки веса и объёма
Условное форматирование для авто, которые НЕ подходят (зачёркивание)
Автоматическая маркировка «Газель» / «Фура» по весу
Расчёт минимального количества автомобилей для сборного груза
Расчёт коэффициента загрузки фуры (факт/максимум) в процентах
Сводная таблица для анализа частоты заказов по каждому маршруту
Гистограмма в сводной таблице для визуализации загрузки авто
Расчёт удельной стоимости перевозки на 1 кг или 1 куб. м
Расчёт остатков на завтра: остаток сегодня минус плановая отгрузка
Формула СЕГОДНЯ для контроля сроков годности (если скоропорт)
Условное форматирование для товаров с истекающим сроком годности
Создание журнала перемещений между складами
Расчёт транзитных остатков (товар в пути) формулой СУММЕСЛИМН
Общий доступный остаток = складской + транзитный
Расчёт точки заказа (мин. остаток под заказ новой партии)
Автоматический текст: «Заказать N единиц» через ЕСЛИ и округление
Проверка дубликатов в накладных через СЧЁТЕСЛИ
Создание понятной шапки с фильтрами (Автофильтр)
Защита листов от случайного изменения формул (без пароля на старте)
Настройка вывода процентов загрузки склада в условных значках (зелёный/жёлтый/красный)
Расчёт оборачиваемости товара (отношение продаж к среднему остатку)
Создание шаблона «Заявка водителю» с автоподстановкой данных из подобранного авто
Сохранение файла как шаблона (.xltx) для ежедневного копирования
Чек-лист из 5 шагов на каждый день: проверить отрицательные остатки, загрузку авто, подсветку страхового
Бонусный пункт: как не сломать формулы при вставке новых строк
АПТЕЧКА ДЛЯ СОБАКИ С ПОМОЩЬЮ МЕДИЦИНЫ
Часы на весь экран в высоком разрешении
Чат рулетка 2026: чаты с элементом неопределенности
Чат рулетка бесплатно
Чатрулетка: чат с новой личностью
Чай и кофе: два мира вкуса
Диагностика шин: Проверка давления и износа
Excel для логистики: бесплатный курс учёта остатков и подбора авто
Фототехника в цифровую эпоху
Игрушки для развития навыков самостоятельности
Инновационные подходы в управлении грузовыми перевозками на линиях Валберис
Немецкие авто: легковые и внедорожники
Общение с Аней в чате
Онлайн генератор паролей с символами
Оптимизация динамического контента GEO
Политика конфиденциальности и пользовательское соглашение
Сервер для affiliate-маркетинга: Безопасность, Скорость, Изоляция
Смешные телевизоры
Современные тенденции в российском автопроме
Средства IP видеонаблюдения