Таблица учёта товара в Excel: как вести складской учёт пошагово
Если остатки ведутся в блокноте или в разрозненных файлах, рано или поздно приходит ревизия — и выясняется, что «всё примерно одинаково». Ниже — пошаговая инструкция, как собрать рабочую таблицу учёта товара в Excel: какие листы нужны, какие формулы в них живут и как проверить, что счёт сошёлся.
- Какую задачу решает таблица учёта товара
- Шаг 1. Справочник товаров
- Шаг 2. Приход: лист «Поступление»
- Шаг 3. Расход и продажи
- Шаг 4. Остатки: считаем формулами
- Шаг 5. Себестоимость, маржа и деньги на складе
- Шаг 6. Минимальный запас и статус позиции
- Шаг 7. Инвентаризация
- Частые ошибки и как их избежать
- Что делать, если времени нет
Какую задачу решает таблица учёта товара
Таблица учёта товара отвечает на четыре вопроса, и именно эти четыре вопроса чаще всего задают клиенты:
- Сколько чего есть — по каждой позиции и по складу в целом, в штуках и в деньгах.
- Что пришло и что ушло — с датой, количеством, ценой и, если есть, поставщиком или причиной списания.
- Сколько зарабатываем на каждой позиции — себестоимость, маржа в рублях и процентах.
- Что закупать — минимальный остаток по каждой позиции и список, где он нарушен.
Если таблица отвечает хотя бы на первые два, это уже больше, чем у большинства. Если не отвечает ни на один — это не учёт, а записная книжка, и оцифровка её ничего не меняет.
Всё, что ниже, — обычный Excel без макросов и без подписок. Файл открывается на любом компьютере, где есть Excel, и работает годами.
Шаг 1. Справочник товаров
Справочник — это лист «Товары», который заполняется один раз и потом только дополняется. Всё остальное ссылается на него, поэтому названия не расходятся и в приходе, и в остатках.
| Столбец | Что в нём | Пример |
|---|---|---|
| Артикул | Короткий код, по которому товар ищется в остальных листах | КР-1042 |
| Наименование | Человеческое название | Кроссовки серые, р. 42 |
| Категория | Группа для отчётов и закупок | Обувь |
| Ед. изм. | шт, пар, кг, упаковка | шт |
| Закупочная цена | Цена, по которой товар пришёл | 2 400 ₽ |
| Цена продажи | Отпускная | 4 900 ₽ |
| Мин. запас | Ниже этого уровня — пора закупать | 10 |
| Поставщик | Кто поставляет — пригодится для заказа | ООО «Обувь-Опт» |
Технически справочник оформляется как таблица Excel (Ctrl+T), а не просто заполненный диапазон. Это даёт три вещи: при добавлении строки формулы подхватывают новые позиции автоматически, по колонкам можно фильтровать, а вместо артикула в приходе ставится выпадающий список из справочника — опечатку сделать уже нельзя.
Выпадающий список делается через «Данные → Проверка данных → Список», источник: =Справочник[Артикул]. Имя артикула подставляется формулой =ВПР(артикул;Справочник;2;0), и дальше наименование, категория и цена тянутся автоматически.
Шаг 2. Приход: лист «Поступление»
Приход — это единственный источник увеличения остатка. Каждая строка — одна поставка одной позиции.
| Столбец | Содержимое | Пример |
|---|---|---|
| Дата | Дата прихода на склад | 12.10.2026 |
| Артикул | Выбор из справочника | КР-1042 |
| Количество | Сколько пришло | 50 |
| Цена закупки | Цена за штуку в этой поставке | 2 400 ₽ |
| Сумма | =Количество*Цена | 120 000 ₽ |
| Поставщик | Кто привёз | ООО «Обувь-Опт» |
| Документ | Номер накладной | УТ-118 |
Цену закупки тяните из справочника формулой =ВПР(артикул;Справочник;5;0), но в самом листе прихода оставьте столбец «Цена закупки» — потому что закупочная цена меняется: тот же кроссовок в октябре стоил 2 400 ₽, а в декабре уже 2 700 ₽. Если брать цену только из справочника, себестоимость за past период уедет.
Отдельный случай — возврат поставщику. Это не приход, а расход: строка в листе «Расход» с отрицательным количеством или с типом операции «Возврат». Если разделить типы операций в одном столбце, потом не придётся гадать, почему остаток ушёл вверх.
Шаг 3. Расход и продажи
Лист «Расход» устроен похоже, но с дополнительными столбцами: канал продажи, цена продажи, скидка и покупатель.
- Продажа — уходит со склада и приносит деньги.
- Списание — уходит без денег: брак, порча, срок годности.
- Возврат покупателя — уходит обратно на склад, количество возвращается в остаток.
- Перемещение — уходит с одной точки и приходит на другую; на общем остатке ничего не меняется.
В столбце «Сумма продажи» считается =Количество*Цена продажи*(1−Скидка). Для списаний сумма продажи равна нулю, но себестоимость списания всё равно нужно списывать — иначе склад в маржинальном отчёте будет выглядеть прибыльнее, чем есть.
Шаг 4. Остатки: считаем формулами
Это ключевой шаг: остатки не пересчитываются вручную, а собираются формулами из «Прихода» и «Расхода». Основной инструмент — СУММЕСЛИ.
Допустим, в «Приходе» количество в столбце C, артикул в столбце B, а в «Расходе» количество в столбце C, артикул в столбце B. Тогда в листе «Остатки» по строке 2:
| Показатель | Формула | Смысл |
|---|---|---|
| Приход | =СУММЕСЛИ(Приход!C:C;$A2;Приход!B:B) | Сумма количеств по этому артикулу |
| Расход | =СУММЕСЛИ(Расход!C:C;$A2;Расход!B:B) | Сумма количеств, ушедших по этому артикулу |
| Остаток | =B2-C2 | Приход минус расход |
| Сумма на складе | =D2*Закупочная цена | Остаток в штуках, умноженный на цену |
Формулы протягиваются один раз по столбцу — и всё. Добавили приход, и остаток пересчитался сам, без F9 и без «протянуть формулу ещё на 20 строк».
Чтобы итог не разъезжался, сверьте сумму остатков с суммой приходов и расходов: =СУММ(Приход!C:C)-СУММ(Расход!C:C) должно совпадать с =СУММ(Остатки!D2:D1000). Если не совпадает — почти всегда виноват артикул, написанный в приходе с пробелом или в другом регистре. Лечится формулой СИМВОЛ(160) в конце или сравнением без учёта регистра.
Главное правило остатков: считать их можно только из операций. Если остаток где-то вводится руками — рано или поздно он разойдётся с приходом и расходом, и вы не найдёте, где именно ошибка.
Шаг 5. Себестоимость, маржа и деньги на складе
Остаток в штуках ничего не говорит о деньгах. Чтобы понять, сколько лежит на складе, нужна средняя закупочная цена по позиции:
=СУММЕСЛИ(Приход!C:C;$A2;Приход!B:B) по количеству, делённое на количество — проще всего через два дополнительных столбца: СУММЕСЛИ(Приход!D:D;артикул;Приход!B:B) (сумма денег по приходу) и СУММЕСЛИ(Приход!C:C;артикул;Приход!B:B) (количество), затем их отношение. Для продаж с несколькими ценами используется СУММЕСЛИМН с двумя условиями — по артикулу и по дате.
| Показатель | Формула | Пример |
|---|---|---|
| Выручка за период | =СУММЕСЛИ(Расход!E:E;диапазон дат) | 120 000 ₽ |
| Себестоимость проданного | остаток × средняя закупочная | 72 000 ₽ |
| Валовая прибыль | =Выручка−Себестоимость | 48 000 ₽ |
| Маржа | =Прибыль/Выручка | 40% |
| Средняя маржа позиции | (Цена продажи − Закупочная)/Цена продажи | (4 900−2 400)/4 900 = 51% |
Если в ассортименте есть позиции, которые продаются «в ноль», они видны именно здесь: закупочная 2 400 ₽, продажа 2 500 ₽, маржа 4% — и эта позиция съедает место на складе, время продавца и внимание. Такие позиции проще всего вывести или поднять цену, и таблица показывает, о каких именно идёт речь.
Шаг 6. Минимальный запас и статус позиции
Смысл учёта в том, чтобы вы узнавали о проблеме раньше, чем она случилась. Добавьте в лист «Остатки» столбец «Статус»:
=ЕСЛИ(D2=0;"НЕТ В НАЛИЧИИ";ЕСЛИ(D2<Минимальный запас;"КРИТИЧНО";ЕСЛИ(D2<Минимальный запас*1,5;"МАЛО";"OK")))
Дальше — условное форматирование: «КРИТИЧНО» красится в красный, «МАЛО» — в жёлтый, «OK» — в зелёный. Открываете файл и сразу видите, что закупать, ничего не листая. Если добавить рядом формулу =ЕСЛИ(D2<Мин;"Заказать "&ЕСЛИ(Средний расход в день>0;ОКРУГЛ(Мин/Средний расход в день;0);""); " шт";""), таблица подскажет и объём заказа.
Средний расход в день считается за последние 30 дней: =ЕСЛИ(Средний расход=0;0;Остаток/Средний расход) — это и есть дней покрытия. Если остаток покрывает меньше 10 дней, позицию пора заказывать.
Шаг 7. Инвентаризация
Инвентаризация — это сверка факта с учётом, и её тоже стоит делать в том же файле, а не в блокноте.
- Создайте лист «Инвентаризация» с двумя столбцами: «Артикул» и «Факт».
- В столбце «Факт» вводите пересчитанное количество.
- Столбец «По учёту» подтяните из листа «Остатки» формулой
=ВПР($A2;Остатки;4;0). - Столбец «Расхождение» =
Факт−По учёту. - Столбец «Сумма расхождения» =
=Расхождение*Закупочная цена— так видно, сколько стоит ошибка в деньгах. - Столбец «Статус» —
=ЕСЛИ(ABS(Расхождение)<=0;"OK";ЕСЛИ(ABS(Расхождение)<=3;"Проверить";"Расхождение")).
Важно: расхождение в единицах и расхождение в деньгах — разные вещи. Партия, где нет паллетной маркировки, даёт расхождение в 1–2 штуки на сотни позиций — это нормально. А расхождение в деньгах показывает реальную потерю склада.
Частые ошибки и как их избежать
- Ручные остатки. Остаток всегда считается из прихода и расхода. Это единственное правило, которое держит файл живым.
- Названия вместо артикулов. «Кроссовки серые» и «Кроссовки серые.» — разные товары для Excel. Связь только по коду.
- Разные единицы измерения. «Упаковка по 6 шт» и «шт» в одной таблице. Вводите всё в одной единице — той, в которой считаете остаток.
- Пробелы и другой регистр в артикулах. Чистятся формулой
=СЖАТЬ(А2)и=ПРОПИСН(). - Отсутствие контроля ввода. Без проверки данных в приходе неизбежно появится артикул, которого нет в справочнике, и такой товар просто выпадет из остатков.
- Слишком много листов. Шесть листов (справочник, приход, расход, остатки, инвентаризация, отчёт) — достаточно для большинства магазинов и складов. Седьмой и восьмой листы в Excel уже тормозят пересчёт.
Что делать, если времени нет
Собрать такую таблицу с нуля — работа на один-два дня, если вы делаете это впервые, и на несколько часов, если у вас уже есть опыт. Если времени нет совсем, есть три разумных варианта:
- Починить существующий файл. Часто у клиентов таблица уже есть, но в ней не сходятся остатки или потерялись формулы. Разбор и починка стоит от 1 000 ₽ и занимает от нескольких часов.
- Собрать базовый контур и дорастить позже. Справочник, приход, расход и остатки — это 80% пользы. Маржа и аналитика добавляются вторым шагом.
- Взять готовый файл и наполнить вашими данными. У меня есть рабочие примеры: учёт склада с автоподсчётом остатков и подсветкой минимального запаса, юнит-экономика по маркетплейсам и долговая книга. Можно открыть, посмотреть логику и перенести на свои товары.
Отдельно про два контура: если к складу добавить учёт денег — продажа списывает товар и приносит деньги одним действием, — получается связка «склад + деньги», которая стоит дешевле, чем два отдельных заказа, и даёт прибыль, а не только остаток. Подробнее про второй контур — в статье про долговую книгу и учёт расходов.