Как сделать, чтобы остаток в Excel считался сам

Основа — таблица на двух листах: «Движение» (каждый приход и расход отдельной строкой) и «Товары» (итоги и остаток). Как её собрать с нуля, разобрано в статье «Таблица приход-расход-остаток в Excel». Здесь — что добавить, чтобы она работала без присмотра.

Почему остаток перестаёт считаться

• Остаток правят руками. Было 20, продали 3 — стёрли и написали 17. Через неделю никто не помнит, откуда взялось число.
• Формула не дотянута. Новый товар добавили ниже последней строки с формулой — по нему остаток пустой или нулевой.
• Расход больше остатка. Excel спокойно уходит в минус, и ошибка всплывает только на пересчёте.
• Остаток виден только «сейчас». Нельзя сказать, сколько было после конкретной продажи, — сложно найти, где потерялись 5 штук.

Приём 1. Умная таблица — формулы протягиваются сами

Встаньте в любую ячейку листа «Товары» и нажмите Ctrl+T → «Таблица с заголовками». То же самое сделайте на листе «Движение». В конструкторе таблиц задайте имена: Товары и Движение.

Теперь формулы пишутся по названиям колонок, а не по адресам ячеек:

• Приход: =СУММЕСЛИМН(Движение[Приход];Движение[Товар];[@Название])
• Расход: =СУММЕСЛИМН(Движение[Расход];Движение[Товар];[@Название])
• Остаток: =[@[На начало]]+[@Приход]-[@Расход]

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

Приём 2. Остаток после каждой операции

Добавьте на лист «Движение» колонку «Остаток после». В ней накопительная сумма по этому товару — от первой строки до текущей. Если колонки идут так: A — «Дата», B — «Товар», C — «Приход», D — «Расход», E — «Остаток после», то в E2:

=ВПР(B2;Товары!A:B;2;0)+СУММЕСЛИМН($C$2:C2;$B$2:B2;B2)-СУММЕСЛИМН($D$2:D2;$B$2:B2;B2)

ВПР берёт начальный остаток товара с листа «Товары», а диапазоны $C$2:C2 при протягивании растут вниз. В каждой строке видно, сколько осталось сразу после этой продажи или поставки, — расхождение на пересчёте легко найти по дате.

Приём 3. Запрет ухода в минус

На листе «Движение» выделите колонку «Расход» → «Данные» → «Проверка данных» → тип «Другой» → формула (для строки 2):

=ВПР(B2;Товары!$A:$E;5;0)>=0

Здесь 5 — номер колонки «Остаток» на листе «Товары». Если введённый расход уводит остаток ниже нуля, Excel покажет ошибку и не примет число. На вкладке «Сообщение об ошибке» напишите понятный текст: «Столько на складе нет — проверьте остаток».

Приём 4. Пометка «Заказать»

На листе «Товары» рядом с «Мин. остатком» добавьте колонку «Статус»: =ЕСЛИ([@Остаток]<=[@[Мин. остаток]];"Заказать";""). Отфильтруйте по слову «Заказать» — получите список закупки на сегодня. Для наглядности добавьте условное форматирование с заливкой.

Если продаж много: отдельный лист продаж

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

Чего Excel не сделает, как ни настраивай

• Двое не работают в одном файле без конфликтов. Копии расходятся, а общий доступ в облаке легко ломает формулы.
• Со склада неудобно. С телефона таблицу можно открыть, но вносить приход и искать товар по штрихкоду — мучение.
• Никто не напомнит. Пометка «Заказать» видна, только если открыть файл.

В программе учёта прихода и расхода SkladControl всё это работает без формул: остаток пересчитывается после каждой операции любого сотрудника, расход больше остатка не проходит, товар находится сканом штрихкода камерой телефона, а о минимальном остатке пишет бот в MAX или Telegram. Таблицу Excel можно загрузить как есть — колонки система распознает сама.

Пока остаётесь в Excel — возьмите бесплатный шаблон учёта склада с готовыми формулами.

Частые вопросы

Почему остаток в Excel не пересчитывается для новых товаров?

Формулу протянули только до последней заполненной строки. Превратите диапазон в умную таблицу (Ctrl+T) — тогда формула сама появляется в каждой новой строке.

Как сделать, чтобы остаток сам уменьшался после продажи?

Записывайте каждую продажу отдельной строкой в журнал движения, а остаток на листе товаров считайте через СУММЕСЛИМН по этому журналу. Новая строка продажи — остаток уже другой, ничего не нужно править руками.

Как в Excel запретить расход больше остатка?

Через «Данные» → «Проверка данных» → «Другой» с формулой, которая проверяет, что остаток по товару после этой строки не меньше нуля. Excel не даст ввести расход, из-за которого остаток уйдёт в минус.

Как увидеть остаток после каждой операции?

Добавьте в журнал колонку с накопительным остатком: СУММЕСЛИМН с диапазоном от первой строки до текущей. В каждой строке будет остаток товара сразу после этой операции.