Как вести учет прихода и расхода товаров в Excel
Таблица прихода и расхода в Excel закрывает базовую задачу склада: видно, сколько товара поступило, сколько ушло и какой остаток лежит на полке прямо сейчас. Для магазина, небольшого производства или пункта выдачи такой файл часто становится первой учетной системой. Ниже разберем, из каких листов состоит рабочая таблица, какие формулы считают остаток без ручного пересчета, и дадим готовый шаблон, который можно скачать и заполнить своими позициями.
Структура таблицы: три листа вместо одного
Частая ошибка при ведении прихода и расхода в экселе состоит в том, что все пишут на один лист: дата, товар, пришло, ушло, остаток. Пока позиций десять, это работает. Когда номенклатура вырастает до сотни строк, найти историю движения конкретного товара становится невозможно, а случайное удаление строки ломает остатки.
Рабочая схема выглядит иначе. В файле три листа:
телефоне
- Справочник. Список товаров с артикулами, единицами измерения и закупочной ценой. Каждая позиция вводится один раз.
- Приход. Журнал поступлений: дата, номер накладной, артикул, количество, цена, поставщик.
- Расход. Журнал отгрузок и списаний: дата, артикул, количество, основание (продажа, брак, внутреннее перемещение).
Остаток при такой структуре не хранится нигде. Он вычисляется формулой: сумма прихода по артикулу минус сумма расхода по нему же. Это ключевой момент, потому что остаток, который считается автоматически, невозможно испортить опечаткой.
Формулы для расчета остатка
Колонку с текущим остатком удобно вывести прямо в справочнике. Для этого подойдет СУММЕСЛИ или ее расширенная версия СУММЕСЛИМН.
-
87%
видят результат в первый месяц -
31%
среднее сокращение издержек -
42%рост скорости операций
-
18%увеличение оборота
Пример. Артикулы в справочнике лежат в столбце A, на листе «Приход» артикулы в столбце C, количество в столбце D. Тогда остаток для товара из строки 2 считается так:
=СУММЕСЛИ(Приход!C:C;A2;Приход!D:D)-СУММЕСЛИ(Расход!C:C;A2;Расход!D:D)
Формула протягивается вниз по всему справочнику и пересчитывается сама при каждой новой записи в журналах. Если нужен остаток на конкретную дату, добавьте условие по дате через СУММЕСЛИМН:
=СУММЕСЛИМН(Приход!D:D;Приход!C:C;A2;Приход!A:A;"<="&$F$1)-СУММЕСЛИМН(Расход!D:D;Расход!C:C;A2;Расход!A:A;"<="&$F$1)
где в ячейке F1 стоит дата, на которую нужен отчет. Так из журналов собирается оборотная ведомость за любой период без сводных таблиц.
Чтобы при вводе накладной не набирать название товара руками, используйте ВПР: оператор вводит артикул, а наименование и цена подтягиваются из справочника. Это убирает главный источник расхождений, когда один и тот же товар записан тремя разными способами.
Как заполнять журналы, чтобы остатки сходились
Сама по себе таблица порядок не наводит, его задают правила ввода. Несколько практических требований, проверенных на реальных складах:
- Одна строка равна одной позиции в накладной. Не суммируйте несколько поступлений в одну запись, иначе потеряете историю.
- Записывайте движение в день операции. Приход, внесенный задним числом через неделю, почти всегда означает, что часть товара уже продана «мимо таблицы».
- Списание брака и недостачи оформляйте отдельным типом расхода, а не удалением строк прихода.
- Закройте справочник и ячейки с формулами от редактирования через защиту листа. Оставьте открытыми только поля ввода.
- Раз в месяц сверяйте расчетный остаток с фактическим пересчетом. Расхождения фиксируйте актом и корректировочной записью в журнале расхода.
Готовый шаблон: что внутри
Чтобы не собирать файл с нуля, скачайте готовый шаблон учета прихода и расхода товара. В нем уже настроены:
- справочник на 500 позиций с выпадающими списками единиц измерения;
- журналы прихода и расхода с проверкой ввода дат и количества;
- автоматический расчет остатков по каждому артикулу;
- лист «Оборотка» с ведомостью за выбранный период;
- условное форматирование: позиции с остатком ниже минимального подсвечиваются красным.
Шаблон работает в Excel начиная с версии 2010 и в бесплатных редакторах вроде LibreOffice Calc. Макросов в файле нет, поэтому он открывается без предупреждений безопасности и его можно спокойно пересылать сотрудникам.
Ограничения таблицы и когда их стоит учитывать
Файл с приходом и расходом честно работает, пока с ним работает один человек и объем операций укладывается в 30-50 записей в день. Дальше начинаются известные проблемы: двое сотрудников не могут вносить накладные одновременно, история изменений не ведется, а поиск ошибки в остатках превращается в ручную сверку журналов за месяц.
Если заявки уже обрабатывают несколько человек, посмотрите разбор признаков, что таблиц перестало хватать, в статье почему Excel перестает справляться со складом. А если нужен не только приход и расход, но и полноценное ведение склада с инвентаризацией и адресным хранением, начните с общего руководства как вести склад в Excel.
