Складской учёт в Excel: приход, расход, остатки и формулы

1. Как устроить складской учёт, чтобы остаток можно было проверить

Рабочий складской файл должен отвечать не только на вопрос «сколько товара сейчас». По каждой позиции важно видеть, откуда появился остаток: начальное количество, приходы, продажи, списания, возвраты, перемещения и корректировки инвентаризации. Если итог меняют вручную после каждой операции, история исчезает, а расхождение уже невозможно объяснить.

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

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

2. Рекомендуемая структура книги: пять листов вместо одной таблицы

Практичную книгу удобно разделить на пять листов. «Товары» — справочник SKU, названия, категории, единицы измерения, закупочной цены и минимального запаса. «Движения» — журнал всех операций. «Остатки» — расчёт по SKU и складам. «Инвентаризация» — фактические пересчёты и корректировки. «Контроль» — дефицит, отрицательные остатки, товары без движения и стоимость запасов.

В журнале «Движения» минимальный набор колонок такой: Дата, Документ, SKU, Склад, Тип операции, Количество, Цена или себестоимость, Контрагент и Комментарий. Одна строка — одна операция по одному SKU. Не записывайте в одной ячейке «приход 10, расход 3»: такие данные невозможно нормально фильтровать и суммировать.

Исходные диапазоны превратите в таблицы Excel через Ctrl+T. Тогда структурированные ссылки автоматически охватывают новые строки. SKU выбирайте из справочника через проверку данных, а тип операции — из закрытого списка: Приход, Продажа, Списание, Возврат, Перемещение и Корректировка. Это предотвращает варианты вроде «расход», «Расход » и «списано», которые формулы воспримут как разные значения.

3. Реальные формулы прихода, расхода и текущего остатка

Пусть таблица «Движения» содержит столбцы SKU, Тип и Количество. На листе «Остатки» приход можно собрать формулой =СУММЕСЛИМН(Движения[Количество];Движения[SKU];[@SKU];Движения[Тип];"Приход"). Продажи аналогично: =СУММЕСЛИМН(Движения[Количество];Движения[SKU];[@SKU];Движения[Тип];"Продажа"). Для списаний используйте отдельный критерий, чтобы не терять причину уменьшения запаса.

Если B2 — начальный остаток, C2 — приход, D2 — продажи, E2 — списания, F2 — возвраты покупателей, текущий остаток можно считать как =B2+C2-D2-E2+F2. В рабочем файле лучше не держать новый ручной начальный остаток каждый месяц: зафиксируйте стартовую дату учёта и дальше стройте остаток из непрерывной истории движений.

Чтобы получить остаток на конкретную дату, добавьте условие по дате. Например, приход до даты в H1: =СУММЕСЛИМН(Движения[Количество];Движения[SKU];$A2;Движения[Тип];"Приход";Движения[Дата];"<="&H$1). Аналогично считается расход. Так можно строить историю остатков без отдельных файлов «Склад май», «Склад июнь» и так далее.

4. Несколько складов, перемещения, резерв и доступный остаток

Если складов несколько, поле «Склад» должно участвовать в каждой формуле. Например, приход SKU на склад из B1: =СУММЕСЛИМН(Движения[Количество];Движения[SKU];$A2;Движения[Склад];B$1;Движения[Тип];"Приход"). Так одна книга показывает и общий запас компании, и распределение по точкам.

Перемещение между складами нельзя записывать одной строкой, которая просто меняет название склада. Используйте две связанные операции с одним номером документа: уменьшение на складе-источнике и увеличение на складе-получателе. Общий запас компании не меняется, но остатки по точкам меняются корректно.

Для интернет-магазина полезно отделить физический остаток от доступного. Если G2 — физический остаток, H2 — резерв под уже принятые заказы, доступный остаток =G2-H2. Товар в пути показывайте отдельно и добавляйте в прогнозный остаток только для закупочного решения. Иначе менеджер может продать то, чего физически ещё нет.

5. Практический пример: 100 единиц на старте и 52 доступно

Рассмотрим SKU-101. На старте было 100 единиц по себестоимости 800 ₽. За неделю пришло 60 единиц, покупателям продано 95, возвращено 4, ещё 2 списано как брак и 15 единиц зарезервировано под подтверждённые заказы. Физический остаток равен 100+60−95+4−2=67 единиц.

Доступный для новых заказов остаток равен 67−15=52 единицы. Если показывать клиентам все 67, можно принять заказ на товар, который уже обещан другим покупателям. Стоимость физического запаса по себестоимости 800 ₽ составляет 67×800=53 600 ₽, а доступного — 41 600 ₽.

Средний расход SKU-101 — 8 единиц в день, поставщик везёт товар 5 дней, страховой запас — 20 единиц. Точка заказа =8×5+20=60. Физически на складе 67, но доступно 52, поэтому позиция уже ниже точки заказа. Проверка =ЕСЛИ(J2<=K2;"Заказать";"Норма") должна использовать именно тот вид остатка, который принят в закупочной логике.

Если целевой уровень после поставки — 15 дней спроса плюс страховка, он равен 8×15+20=140 единиц. При доступном остатке 52 базовое пополнение до цели составляет 88 единиц. Если минимальная партия поставщика 100, округление должно быть отдельным прозрачным правилом, а не скрытой ручной правкой.

6. Минимальный запас, точка заказа и список закупки

Фиксированный минимальный остаток подходит для стабильных позиций, но лучше связать закупку с расходом и сроком поставки. Точка заказа = средний дневной расход × срок поставки + страховой запас. В Excel, если L2 — расход в день, M2 — срок поставки, N2 — страховка: =L2*M2+N2.

Количество к заказу не равно точке заказа. Если O2 — целевой уровень, J2 — доступный остаток, а P2 — подтверждённый товар в пути, базовая формула =МАКС(0;O2-J2-P2). Для поставщиков с минимальной партией добавьте отдельную колонку и округление вверх по бизнес-правилу.

На листе «Контроль» выведите SKU, доступный остаток, точку заказа, товар в пути, рекомендуемое количество и сумму закупки. Сортировка по сумме помогает увидеть потребность в деньгах, а не только длинный список красных позиций. Для оценки скорости запасов используйте соседнее руководство по оборачиваемости.

7. Инвентаризация: как исправить расхождение и не уничтожить историю

При инвентаризации сравнивайте расчётный и фактический остаток. Если система показывает 67 единиц, а пересчёт дал 64, расхождение равно −3. Не меняйте старый приход или продажу только для того, чтобы итог стал 64. Создайте корректирующую операцию −3 с датой инвентаризации и причиной.

На листе «Инвентаризация» храните дату, склад, SKU, расчётный остаток, фактический остаток, отклонение, стоимость отклонения и комментарий. Стоимость расхождения при себестоимости 800 ₽ в нашем примере равна −3×800=−2 400 ₽. Повторяющиеся отклонения по одной категории или складу — уже управленческий сигнал.

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

8. Типичные ошибки и контрольные проверки

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

Добавьте проверки: неизвестных SKU нет; тип операции входит в справочник; количество положительное, а направление задаётся типом; дата заполнена; склад существует; дубли Документ+SKU+Тип помечены; доступный остаток не уходит в минус без объяснимого резерва. Отдельно выводите товары с отрицательным физическим остатком — это почти всегда ошибка данных или пропущенный приход.

Контрольная стоимость склада считается независимо как сумма Остаток×Себестоимость по всем SKU. Если складов несколько, сумма по точкам должна совпадать с общим итогом компании с учётом перемещений. Условное форматирование используйте как сигнал, но сам статус храните отдельной формулой, чтобы его можно было фильтровать.

9. Как превратить файл в регулярный складской отчёт

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

Не копируйте книгу каждый месяц. Храните историю движений непрерывно и используйте даты в формулах, фильтрах и сводных таблицах. Тогда один файл отвечает и на текущий вопрос «что есть», и на исторический «почему остаток изменился». Когда объём операций станет слишком большим для ручной дисциплины, это будет понятным сигналом переходить на специализированную систему.

Если нужен готовый XLSX, в SmartXLSX можно описать товары, склады, типы движений, резерв и правила закупки обычными словами. Для следующего уровня контроля свяжите складской учёт с оборачиваемостью запасов, ABC/XYZ-анализом и анализом продаж — тогда закупка будет опираться не только на остаток, но и на скорость и ценность ассортимента.