Остаток на дату в Excel: начальный баланс и движения товаров

Для отчёта по закупкам нужен остаток товара на конец выбранного дня. В выгрузке есть начальный баланс и журнал приходов и расходов. Сумма только сегодняшних движений покажет изменение за день, а не состояние склада. Остаток восстанавливается от известной начальной точки с учётом всех последующих движений.

Зафиксируйте момент начального баланса

Пусть остаток 100 штук подтверждён на начало 1 ноября. Движения начиная с этого момента включаются в расчёт. Если баланс снят на конец 31 октября, это эквивалентная граница. Если он снят после части операций 1 ноября, понадобится более точное время, иначе движения посчитаются повторно.

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

Приведите движения к знаку

Приход увеличивает остаток, расход уменьшает. Удобно подготовить числовой столбец QtySigned: приход+20, расход−15. Возврат покупателя и возврат поставщику имеют разные знаки. Правило должно опираться на тип операции из источника.

В B1 находится дата выбранного дня без времени, B2 — дата начального баланса, B3 — его количество, A2 — SKU, C2 — склад. Формула добавляет движения от B2 до начала следующего дня, не включая его. Это сохраняет вечерние операции выбранной даты.

=B3+СУММЕСЛИМН(Движения[QtySigned];Движения[SKU];A2;Движения[Склад];C2;Движения[ДатаВремя];">="&B2;Движения[ДатаВремя];"<"&B1+1)

Проверьте на последовательности

Начальный остаток 100, затем приход 20 и расход 15 в выбранный день: конец дня 105. Приход 30 на следующее утро в этот результат не входит. Для даты до начального баланса формулу применять нельзя: исходная точка не позволяет восстановить прошлое без более ранней истории.

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

Проверьте полноту истории

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

Остаток на конец дня нельзя складывать по датам как продажи. Сумма ежедневных остатков измеряет другую величину. В сводном отчёте по месяцу обычно нужен остаток на последнюю дату периода или средний запас по отдельно согласованному правилу.

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

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

Соберите обновляемый отчёт на практике

В платном курсе есть практикум «Остаток на дату: начальный баланс и движения». На подготовленных файлах вы проверите контрольные числа и обновите книгу на новых данных. Затем этот расчёт используется в работе с закупкой по нормативу покрытия, где корректный остаток особенно важен.

Откройте программу курса «Продвинутый Excel: Power Query, Power Pivot и автоматизация отчётов».

Начните с пробного урока о рабочей книге и первом запросе Power Query.

Для следующего этапа: объединение файлов из папки и обновление отчёта.