Система учета сроков годности в Excel

Для автоматического контроля сроков годности в Excel создайте таблицу с колонками «Дата производства», «Срок хранения (дни/мес)» и «Дата истечения», а затем используйте формулу =C2+D2 или =EDATE(C2; D2) для расчета конечной даты . Добавьте столбец со статусом на основе функции ЕСЛИ и СЕГОДНЯ(), чтобы система сама помечала товары как «Свежие», «Требуют внимания» или «Просроченные». Визуализируйте данные через условное форматирование: красный цвет для просрочки, желтый — для товаров, у которых осталось менее 7 дней .

Структура идеальной таблицы учета

Эффективный учет начинается с правильной структуры данных. Хаотичное заполнение ячеек приведет к ошибкам в расчетах, поэтому важно сразу задать жесткий формат таблицы.

Рекомендуемый набор столбцов:

1. Наименование товара — текстовое поле.
2. Партия / Артикул — для точной идентификации.
3. Дата поступления/производства — обязательный формат даты (ДД.ММ.ГГГГ).
4. Срок годности — числовое значение (количество дней или месяцев).
5. Единица измерения срока — опционально (дни/месяцы), если используете разные единицы.
6. Дата истечения — расчетное поле (формула).
7. Осталось дней — количество дней до конца срока.
8. Статус — текстовый индикатор состояния.

Автоматический расчет даты истечения

Главная задача — исключить ручной подсчет дат. В зависимости от того, в чем указан срок годности (днях или месяцах), используются разные формулы.

Если срок задан в днях

Это самый простой вариант. Excel хранит даты как числа, поэтому к дате производства можно просто прибавить количество дней срока годности.

• Формула: =Ячейка_даты + Ячейка_дней
• Пример: Если дата производства в C2, а срок в днях в D2, то в ячейке даты истечения (E2) пишем: =C2+D2 .

Если срок задан в месяцах

Для продуктов со сроком в месяцах (например, «12 месяцев») простая арифметика не подойдет, так как месяцы имеют разное количество дней. Используйте функцию ДАТАМЕСЯЦ (или EDATE в англ. версии).

• Формула: =ДАТАМЕСЯЦ(Ячейка_даты; Ячейка_месяцев)
• Пример: =ДАТАМЕСЯЦ(C2; D2) — эта функция автоматически добавит нужное количество месяцев к исходной дате, корректно обрабатывая високосные годы и переходы через конец месяца .

Настройка автоматического статуса и уведомлений

Чтобы таблица «говорила» сама за себя, настройте автоматическое определение статуса товара. Это избавит от необходимости вручную проверять каждую дату.

Используйте вложенную функцию ЕСЛИ в сочетании с функцией СЕГОДНЯ() (возвращает текущую дату системы) .

Логика формулы для столбца «Статус»:

1. Если дата истечения меньше сегодняшней → «Просрочен».
2. Если дата истечения наступает в ближайшие 7 дней (или иной порог) → «Внимание».
3. В остальных случаях → «Норма».

Готовая формула для ячейки F2 (при дате истечения в E2):

=ЕСЛИ(E2<СЕГОДНЯ(); "Просрочен"; ЕСЛИ(E2<=СЕГОДНЯ()+7; "Внимание"; "Норма"))

Для более точного подсчета оставшихся дней используйте функцию РАЗНДАТ (или DATEDIF): =РАЗНДАТ(СЕГОДНЯ(); E2; "d") — покажет точное количество полных дней до истечения . Если результат отрицательный, товар уже просрочен.

Визуализация: цветовая индикация рисков

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

1. Выделите столбец со статусом или датами истечения.
2. Перейдите: Главная → Условное форматирование → Создать правило.
3. Выберите тип: «Использовать формулу для определения форматируемых ячеек».

Правила подсветки:

• Красный фон (Просрочено): Формула =$E2<СЕГОДНЯ() .
• Желтый/Оранжевый фон (Скоро истекает): Формула =И($E2>=СЕГОДНЯ(); $E2<=СЕГОДНЯ()+7).
• Зеленый фон (Все хорошо): Формула =$E2>СЕГОДНЯ()+7.

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

Частые ошибки при ведении учета

Даже простые таблицы могут давать сбои из-за типовых ошибок пользователей. Избегайте их, чтобы данные оставались достоверными.

FAQ: Вопросы по автоматизации

Можно ли сделать так, чтобы просроченные товары удалялись сами? Автоматическое удаление строк стандартными формулами невозможно. Для этого требуется использование макросов (VBA) или сторонних надстроек. Безопаснее просто фильтровать таблицу по статусу «Просрочен» и скрывать эти строки.

Как отправить уведомление о просрочке на почту? Стандартный Excel не отправляет письма сам. Это реализуется через макрос VBA, который проверяет даты при открытии файла и запускает Outlook. Альтернатива — выгрузить данные в Google Таблицы и настроить триггер через Google Apps Script.

Что делать, если срок годности указан в часах? В Excel сутки равны 1. Чтобы добавить часы, разделите их количество на 24. Формула: =Дата_производства + (Часы_срока / 24) . Не забудьте установить формат ячейки результата как «Дата и время».

Как защитить файл от случайного удаления формул? Перейдите во вкладку Рецензирование → Защитить лист. Снимите галочку с пункта «Изменение заблокированных ячеек». Предварительно убедитесь, что ячейки с формулами заблокированы (это настройка по умолчанию), а ячейки для ввода данных — разблокированы.