Таблица приход-расход-остаток в Excel: формула и готовый шаблон

Формула остатка

Вся таблица учёта товаров держится на одной формуле:

Конечный остаток = Начальный остаток + Приход − Расход

Пример: на начало месяца было 40 пакетов молока, привезли 20, продали 52. Остаток: 40 + 20 − 52 = 8. Сложность не в формуле, а в том, чтобы приход и расход по каждому товару собирались сами, а не пересчитывались вручную.

Простой вариант: одна строка на товар

Колонки: «Товар», «На начало», «Приход», «Расход», «Остаток». В «Остатке» формула =B2+C2-D2, протянутая вниз. Приход и расход дописываются в ячейку сложением: было 20, привезли ещё 10 — пишете 30.

Так можно вести склад на два-три десятка позиций. Минус — не видно, когда и что приходило: в ячейке только итоговая сумма, ошибку не найти.

Правильный вариант: таблица на двух листах

Движение записывается отдельно, а остатки считаются из него. Так история сохраняется, а итог всегда верный.

1. Лист «Движение»

Колонки: A — «Дата», B — «Товар», C — «Приход», D — «Расход», E — «Комментарий». Каждая поставка или продажа — новая строка. Ничего не стирается и не перезаписывается.

2. Лист «Товары»

Колонки: A — «Название», B — «На начало», C — «Приход», D — «Расход», E — «Остаток», F — «Мин. остаток». Формулы во второй строке, дальше протягиваете вниз:

• Приход: =СУММЕСЛИМН(Движение!C:C;Движение!B:B;A2)
• Расход: =СУММЕСЛИМН(Движение!D:D;Движение!B:B;A2)
• Остаток: =B2+C2-D2

3. Выпадающий список товаров

Главный источник ошибок — опечатки: «Молоко 3,2%» и «молоко 3.2%» для формулы разные товары. На листе «Движение» выделите колонку B → «Данные» → «Проверка данных» → «Список» → источник =Товары!$A$2:$A$500. Теперь товар выбирается из списка.

4. Подсветка «пора заказать»

Выделите колонку «Остаток» → «Условное форматирование» → «Создать правило» → «Использовать формулу» → =E2<=F2 → оранжевая заливка. Товары, которые дошли до минимума, будут видны сразу.

Остаток на дату

Если нужен остаток не на сегодня, а на конкретный день, добавьте в СУММЕСЛИМН условие по дате. Например, дата в ячейке H1:

=B2+СУММЕСЛИМН(Движение!C:C;Движение!B:B;A2;Движение!A:A;"<="&$H$1)-СУММЕСЛИМН(Движение!D:D;Движение!B:B;A2;Движение!A:A;"<="&$H$1)

Готовый шаблон

Всё описанное выше уже собрано в шаблоне: два листа, формулы, выпадающий список и подсветка «Заказать». Скачать таблицу приход-расход-остаток бесплатно.

Типичные ошибки

• Формулу протянули не до конца. Новый товар добавили ниже — остаток по нему не считается.
• Строку движения забыли внести. Таблица не знает о продаже, недостача всплывает на пересчёте.
• Файл правят двое. У каждого своя копия — через неделю остатки в них разные.
• Остаток ушёл в минус. Excel не запрещает расход больше остатка — ошибку видно только по факту.

Когда таблицы становится мало

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

Таблицу не нужно переделывать: загрузите её как есть — колонки распознаются автоматически, а остаток, посчитанный формулой, перенесётся. Подробнее — в статье «Складской учёт вместо Excel».

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

Как посчитать остаток товара в Excel?

Конечный остаток = начальный остаток + приход − расход. Если приход и расход записываются построчно на отдельном листе, суммы по каждому товару собирает формула СУММЕСЛИМН, а остаток считается в соседней колонке.

Какая формула нужна для прихода и расхода по товару?

=СУММЕСЛИМН(Движение!C:C;Движение!B:B;A2) — сумма колонки «Приход» по строкам, где товар совпадает с названием в A2. Для расхода то же самое, только с колонкой D.

Работают ли эти формулы в Google Таблицах?

Да. В Google Таблицах функция называется так же — СУММЕСЛИМН (SUMIFS). Разделитель аргументов зависит от языка таблицы: точка с запятой или запятая.

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

Подсветите колонку «Остаток» условным форматированием: правило «меньше 0» — красная заливка. Запретить расход больше остатка формулами Excel надёжно нельзя — это умеют только программы учёта.