Как создать систему учета инвентаря в Excel: пошаговое руководство

Чтобы создать систему учета запасов в Excel, постройте две таблицы. В первой перечислите ваши товары с колонками для ID товара, названия, стоимости единицы, начального остатка и точки перезаказа. Во второй фиксируйте каждое движение: дату, ID товара, тип (Приход или Расход) и количество. Затем используйте функцию SUMIFS, чтобы суммировать приход и расход, и вычтите их, чтобы получить текущий остаток.
Добавьте колонку статуса, которая помечает товары на уровне или ниже точки перезаказа, и выделите эти строки с помощью условного форматирования. Краткая сводка вверху покажет общую стоимость запасов и количество позиций, требующих дозаказа.
Это руководство охватывает каждый шаг: от структуры таблиц до формул и оповещений. Оно также включает полный пример — небольшой склад офисных принадлежностей — и более быстрый способ создания трекера на основе экспорта данных. Шаги основаны на страницах поддержки Microsoft, изученных 18 ноября 2026 года.
Что делает трекер запасов
Трекер запасов в любой момент отвечает на три вопроса:
- Сколько единиц каждого товара у нас есть? Это текущий остаток.
- Сколько это стоит? Это стоимость запасов, основанная на цене за единицу.
- Что нам нужно заказать? Это товары, уровень которых достиг точки перезаказа или опустился ниже неё.
Хороший трекер также хранит историю. Если количество кажется неверным, вы можете прокрутить журнал движений и найти, когда товар пришел или ушел.
Система учета запасов в Excel хорошо подходит для малых и средних объемов. Она идеальна для кладовых, небольших магазинов, офисных принадлежностей, запчастей и инвентаря для мероприятий. Если вам нужно отслеживать тысячи позиций на нескольких объектах, обычно лучше подходит специализированная система управления запасами.
Планирование трекера: две таблицы
Прежде чем вводить формулы, решите, как будет структурирован трекер. Многие простые трекеры используют одну таблицу с единственной колонкой количества, которую люди перезаписывают. Это быстро приводит к ошибкам. Никто не знает, когда и почему изменилось число.
Более правильный дизайн разделяет список товаров и движения:
| Таблица | Одна строка на | Основные колонки |
|---|---|---|
| Товары | Продукт или деталь | ID товара, Название, Категория, Стоимость единицы, Начальный остаток, Точка перезаказа |
| Движения | Изменение запаса | Дата, ID товара, Тип (Приход или Расход), Количество, Примечание |
Таблица «Товары» содержит факты, которые меняются редко. Таблица «Движения» растет каждый день. Формулы в таблице «Товары» считывают данные из таблицы «Движения» и вычисляют текущий остаток.
У такого дизайна три преимущества. Вы никогда не перезаписываете числа. У каждого изменения есть дата. И вы сможете позже ответить на вопросы, например, сколько товара ушло в марте.
Это также делает трекер более безопасным для совместного использования. Сотрудники только добавляют строки в таблицу «Движения». Таблица «Товары» с её формулами может оставаться заблокированной.
Разместите каждую таблицу на отдельном листе. Назовите листы «Товары» и «Движения», чтобы формулы было легко читать.
Создание списка товаров в виде таблицы
Начните с листа «Товары». Введите заголовки в первой строке: ID товара, Название, Категория, Стоимость единицы, Начальный остаток и Точка перезаказа. Затем введите по одной строке для каждого товара.
Затем превратите этот диапазон в таблицу Excel. Microsoft объясняет зачем: «Чтобы упростить управление группой связанных данных и их анализ, можно преобразовать диапазон ячеек в таблицу Excel». Чтобы создать её:
- Выберите любую ячейку в данных.
- Выберите Главная > Форматировать как таблицу.
- Выберите стиль таблицы.
- Убедитесь, что флажок «Таблица с заголовками» установлен, и нажмите ОК.
Назовите таблицу «Items» на вкладке Конструктор таблиц. Сделайте то же самое для журнала движений на другом листе и назовите его «Movements».
Таблицы помогают двумя способами. Они автоматически расширяются при добавлении строк. А формулы в колонке таблицы копируются вниз сами собой. Microsoft утверждает, что формула, введенная в одну ячейку, «мгновенно применяется ко всем остальным ячейкам в этой колонке таблицы».
Используйте короткие и уникальные ID товаров, например A-101. Используйте их везде вместо названий, так как названия могут быть написаны по-разному.
Добавление выпадающих списков в журнал движений
Ошибки при вводе — основная причина расхождений в учете. Выпадающий список предотвращает большинство из них.
Microsoft объясняет цель одной фразой: «Используйте проверку данных, чтобы ограничить тип данных или значения, которые пользователи вводят в ячейку, например, с помощью раскрывающегося списка». Для колонки «Тип»:
- Выберите колонку «Тип» в таблице «Движения».
- Выберите Данные > Проверка данных.
- На вкладке Параметры в поле Тип данных выберите Список.
- В поле Источник введите Приход,Расход.
- Убедитесь, что флажок Список допустимых значений установлен, и нажмите ОК.
Сделайте то же самое для колонки «ID товара», но в качестве источника укажите колонку «ID товара» из таблицы «Товары». Теперь пользователи смогут выбирать только существующие товары.
Выпадающие списки также ускоряют ввод. Кладовщик может зафиксировать поставку за несколько секунд, выбрав товар и тип, а затем введя количество.
Вы также можете ограничить колонку «Количество». В поле Тип данных выберите Целое число и установите минимум 1. Это заблокирует пустые и отрицательные значения.
Расчет текущего остатка с помощью SUMIFS
Теперь добавьте расчетные колонки в таблицу «Товары». Функция SUMIFS суммирует числа, соответствующие нескольким условиям. Синтаксис Microsoft выглядит так:
SUMIFS(диапазон_суммирования, диапазон_условия1, условие1, [диапазон_условия2, условие2], ...)
Добавьте колонку «Приход всего». В её первую ячейку введите:
=SUMIFS(Movements[Количество],Movements[ID товара],[@[ID товара]],Movements[Тип],"Приход")
Это суммирует все количества в таблице «Движения», где ID товара совпадает со строкой, а тип — «Приход». Таблица сама применит формулу для каждого товара.
Добавьте колонку «Расход всего» с той же формулой, заменив «Приход» на «Расход».
Затем добавьте «В наличии»:
=[@[Начальный остаток]]+[@[Приход всего]]-[@[Расход всего]]
Наконец, добавьте «Стоимость запасов»:
=[@[В наличии]]*[@[Стоимость единицы]]
Проверьте один товар вручную после настройки формул. Отфильтруйте таблицу «Движения» по этому товару, сложите строки прихода и расхода и сравните результат с колонкой «В наличии».
Маркировка товаров для дозаказа
Трекер запасов наиболее полезен, когда он говорит вам, что делать. Добавьте колонку «Статус» с такой формулой:
=IF([@[В наличии]]<=[@[Точка перезаказа]],"Заказать","ОК")
Теперь в каждой строке будет написано «Заказать» или «ОК». Устанавливайте точки перезаказа исходя из того, сколько времени занимает доставка и сколько товара вы расходуете за этот период.
Выделите строки «Заказать» с помощью условного форматирования:
- Выберите колонку «Статус».
- Выберите Главная > Условное форматирование > Правила выделения ячеек > Текст содержит.
- Введите Заказать и выберите цвет заливки, например светло-красный.
- Нажмите ОК.
Microsoft отмечает, что когда появляется диалоговое окно «Текст содержит», вы «вводите нужный вариант форматирования и нажимаете ОК». Выделение будет обновляться каждый раз при изменении запасов.
Вы также можете отсортировать или отфильтровать колонку «Статус», чтобы видеть только строки «Заказать» перед оформлением закупок.
Поиск данных о товаре с помощью XLOOKUP
В журнале движений видны ID товаров, которые трудно читать. Колонка поиска добавит название товара рядом с каждым движением.
Microsoft описывает XLOOKUP как способ «находить данные в таблице или диапазоне по строкам». Её синтаксис:
XLOOKUP(искомое_значение, просматриваемый_массив, возвращаемый_массив, [если_не_найдено], [режим_сопоставления], [режим_поиска])
Добавьте колонку «Название» в таблицу «Движения» и введите:
=XLOOKUP([@[ID товара]],Items[ID товара],Items[Название],"Неизвестный ID")
Если кто-то введет несуществующий ID, в ячейке отобразится «Неизвестный ID». Это позволяет легко замечать ошибки.
Microsoft отмечает, что «XLOOKUP недоступна в Excel 2016 и Excel 2019». В этих версиях используйте связку ИНДЕКС и ПОИСКПОЗ или ВПР (VLOOKUP), разместив ID товара в первой колонке.
Добавление сводки вверху
Последний элемент трекера — это сводка. Краткий отчет помогает любому увидеть состояние склада без прокрутки. Добавьте несколько ячеек над таблицей «Товары» или на отдельном листе «Сводка».
Используйте эти формулы:
- Общая стоимость запасов:
=SUM(Items[Стоимость запасов]) - Товаров к заказу:
=COUNTIF(Items[Статус],"Заказать") - Единиц отгружено в этом месяце:
=SUMIFS(Movements[Количество],Movements[Тип],"Расход",Movements[Дата],">="&DATE(2026,11,1))
COUNTIF следует простому шаблону. По словам Microsoft, она спрашивает: «Где вы хотите искать?» и «Что вы хотите найти?»
Вы также можете добавить гистограмму стоимости запасов по категориям. Выберите диапазон сводки и выберите Вставка > Диаграмма. Сводка должна быть небольшой, с тремя-пятью важными показателями.
Практический пример: склад офисных принадлежностей
Вот полный пример работающего трекера. Небольшой офис отслеживает пять позиций. Таблица «Товары» перед ноябрем выглядит так:
| ID товара | Название | Стоимость ед. | Начальный остаток | Точка перезаказа |
|---|---|---|---|---|
| A-101 | Блокнот A5 | $2.50 | 120 | 50 |
| A-102 | Шариковые ручки (кор.) | $6.00 | 40 | 20 |
| B-201 | Бумага для принтера (пачка) | $4.20 | 80 | 30 |
| B-202 | Картридж с тонером | $45.00 | 12 | 5 |
| C-301 | Коробка для доставки (сред.) | $0.80 | 300 | 100 |
В течение ноября в таблице «Движения» зафиксированы следующие изменения. Блокноты: 85 расход и 50 приход. Ручки: 25 расход. Бумага: 60 расход и 40 приход. Тонер: 9 расход. Коробки: 180 расход.
После выполнения формул расчетные колонки показывают:
| ID товара | Приход всего | Расход всего | В наличии | Стоимость запасов | Статус |
|---|---|---|---|---|---|
| A-101 | 50 | 85 | 85 | $212.50 | ОК |
| A-102 | 0 | 25 | 15 | $90.00 | Заказать |
| B-201 | 40 | 60 | 60 | $252.00 | ОК |
| B-202 | 0 | 9 | 3 | $135.00 | Заказать |
| C-301 | 0 | 180 | 120 | $96.00 | ОК |
Пример также показывает, почему важен журнал движений. С бумагой всё кажется нормальным (60 пачек), но журнал показывает, что 60 ушло за один месяц. Если такой темп сохранится, бумага достигнет точки перезаказа через пару недель. Одна колонка количества скрыла бы этот тренд.
Проверьте одну строку вручную. Блокнотов было 120, пришло 50, ушло 85, итого 120 + 50 − 85 = 85. Это совпадает с колонкой «В наличии».
Сводка показывает общую стоимость запасов $785.50 и 2 позиции к заказу. Ручки и тонер находятся ниже точек перезаказа, поэтому они попадают в заказ на этой неделе.
Создание трекера запасов в Excel с помощью AI
Создание трекера вручную занимает час или два. Самая медленная часть — это данные: очистка экспорта из кассовой системы, сопоставление названий товаров и исправление дубликатов ID.
Инструмент AI может выполнить эту очистку и анализ за вас. Вот метод из трех шагов с использованием Powerdrill Bloom.
Шаг 1: Загрузите файл Excel, CSV или PDF
Загрузите ваш экспорт остатков, список поставщиков или ведомость инвентаризации за прошлый месяц. Excel AI Assistant работает с файлами электронных таблиц напрямую.
Включите и список товаров, и движения, если они у вас есть, даже в разных файлах.
Шаг 2: Запросите трекер на естественном языке
Опишите, что вам нужно. Например: «Создай трекер запасов с текущим остатком, стоимостью запасов и флагом перезаказа для товаров, которых осталось 20 единиц или меньше». Попросите его составить список товаров, которые нужно заказать на этой неделе.
Вы также можете задавать уточняющие вопросы, например, какая категория товаров замораживает больше всего наличных.
Шаг 3: Проверьте результаты и экспортируйте их
Сверьте количество в наличии для нескольких позиций с вашей последней проверкой. Затем экспортируйте очищенную таблицу обратно в Excel или превратите выводы в краткий отчет или слайды для вашей команды.
Это полезно, когда трекер запасов запущен. Ежемесячный экспорт из вашей системы продаж может показать, что реально ушло, что вы сможете сравнить с журналом.
Сохраняйте дизайн с двумя таблицами для ежедневных обновлений. Используйте AI, когда вам нужна быстрая очистка данных или ежемесячный обзор.
Распространенные ошибки, которых следует избегать
Большинство проблем с трекером запасов возникает из-за того, как люди вводят данные, а не из-за формул. Вот проблемы, которые чаще всего встречаются при использовании трекера в Excel.
- Перезапись количеств. Вместо этого записывайте каждое изменение как новое движение.
- Названия вместо ID. «Ручки» и «Коробка ручек» станут двумя разными товарами. Используйте уникальные ID.
- Отсутствие начального остатка. Без начального счета любая цифра остатка будет неверной.
- Смешанные единицы измерения. Отслеживайте либо коробки, либо штуки, но не то и другое одновременно для одного товара.
- Отсутствие регулярной инвентаризации. Сравнивайте данные трекера с физическим наличием каждый месяц или квартал.
- Точки перезаказа не пересматриваются. Обновляйте их при изменении расхода или сроков поставки.
- Удаление строк журнала. Никогда не удаляйте движение, чтобы исправить ошибку. Вместо этого добавьте корректирующую запись с примечанием.
- Затирание формул. Защитите лист «Товары», чтобы никто не заменил формулу числом.
Чтобы найти товары, которые слишком долго лежат на полке, ознакомьтесь с нашим руководством как выявить неходовые товары. Для прогнозирования спроса посмотрите наш список AI-инструментов для инвентаризации и прогнозирования спроса.
Если ваши данные о запасах хранятся в запутанном экспорте, попробуйте Powerdrill Bloom, чтобы превратить их в готовый трекер.
Часто задаваемые вопросы
Как создать простой трекер запасов в Excel?
Создайте таблицу «Товары» с ID товара, названием, стоимостью единицы, начальным остатком и точкой перезаказа. Добавьте таблицу «Движения», где фиксируется каждое изменение. Затем используйте SUMIFS для расчета прихода, расхода и текущего остатка.
Какая формула вычисляет остаток на складе в Excel?
Остаток на складе равен начальному остатку плюс приход минус расход. Используйте SUMIFS, чтобы суммировать количества «Приход» и «Расход» для каждого товара из журнала движений.
Как заставить Excel помечать низкий уровень запасов?
Добавьте колонку «Статус» с формулой IF, которая возвращает «Заказать», когда остаток на складе равен или ниже точки перезаказа. Затем выделите эти ячейки с помощью условного форматирования.
Как часто нужно обновлять трекер запасов?
Фиксируйте каждое движение в момент его совершения или хотя бы раз в день. Затем сравнивайте трекер с физическим наличием каждый месяц или квартал и исправляйте любые расхождения корректирующей записью.
Можно ли отслеживать запасы в Excel для нескольких локаций?
Да. Добавьте колонку «Локация» в таблицу «Движения» и добавьте «Локацию» как второе условие в SUMIFS. Тогда каждый товар получит цифру остатка для каждой локации.
Источники: Поддержка Microsoft, Обзор таблиц Excel · Поддержка Microsoft, Применение проверки данных к ячейкам · Поддержка Microsoft, функция SUMIFS · Поддержка Microsoft, функция XLOOKUP · Поддержка Microsoft, Использование условного форматирования для выделения информации в Excel · Поддержка Microsoft, Использование функции COUNTIF в Microsoft Excel.