Чтобы при помощи Excel отслеживать состояние запасов нужно доработать модель товарных запасов по дням.
Курсы от Skillbox со скидкой до 65% по ссылке.
От себя лично, как автор канала про excel, я рекомендую курс "Excel + «Google Таблицы» с нуля до PRO" очень содержательный курс, замечательный лектор. Как бы хорошо вы не знали Excel, в этом курсе вы найдете много полезной информации для работы с электронными таблицами в Excel или Google. Каждый урок небольшое открытие.
Теперь к доработке модели. Добавляем лист с нормативами (для упрощения в примере нормативы по двум товарам одинаковые).
В данном примере будет 4 интервала:
- меньше 200 - "дефицит";
- равно или больше 200 и меньше 220 - "заказ";
- равно или больше 220 и меньше или равно 300 - "норматив";
- больше 300 - "много" (излишние запасы)
Дальше будем работать с синим блоком исходной таблицы. Для Товара 1 добавим итоговый столбец, в данном примере важно общее наличие товара, без привязки к кодировке номенклатуры в группе.
Добавляем в таблицу два новых столбца с оценкой состояния запаса.
Для оценки запаса используются функции "ЕСЛИ" и "И"
Формула оценки запаса для Товара 1 выглядит так
=ЕСЛИ(S14<нормативы!$F$3;"дефицит";ЕСЛИ(И(S14>=нормативы!$B$3;S14<=нормативы!$C$3);"норматив";ЕСЛИ(И(S14>=нормативы!$D$3;S14<нормативы!$E$3);"заказ";"много")))
Формула длинная, ее лучше заносить частями:
Сначала заносим условие для дефицита и проверяем.
=ЕСЛИ(S14<нормативы!$F$3;"дефицит";0)
Далее вместо 0, вносим второе условие, проверяем и так далее пока все условия не будут описаны и проверены.
=ЕСЛИ(S14<нормативы!$F$3;"дефицит";ЕСЛИ(И(S14>=нормативы!$B$3;S14<=нормативы!$C$3);"норматив";0))
Функция "И" для описания интервалов, знак $, чтобы можно было копировать формулу вниз и вверх по столбцу.
После того как формула для Товара 1 будет занесена, ее можно скопировать в соседний столбец для Товара 2, при этом в формуле нужно поменять ссылку на номер строки на листе "нормативы" с 3 на 4.
Формулу ЕСЛИ можно использовать и без ссылок на лист с нормативами, а заносить нормативы в виде цифр. В данном случае если возникнет необходимость изменить нормативы придется их менять в формуле, а это сделать сложнее (высокая вероятность ошибки, так как формула длинная).
Для наглядности работы с запасами можно использовать условное форматирование, то есть выделять то или иное состояние запаса определенным цветом.
Для этого в Excel есть функция "условное форматирование", она находится на панели инструментов
В данном примере для условного форматирования были использованы результаты оценки запаса