Инвентаризация закончилась: кладовщики два дня пересчитывали четыре тысячи позиций, выгрузка со склада лежит в Excel, а 1С по-прежнему показывает свои цифры. Теперь главный вопрос — где факт не сошёлся с учётом. Сверять глазами распечатку против экрана при таком объёме нереально: неделя работы и гарантированные пропуски. Разберём, как свести инвентаризацию к двум файлам и одной формуле — и получить ведомость расхождений за полчаса.
Два файла — две правды
Сверка инвентаризации — это сравнение двух таблиц:
- Файл факта — фактический подсчёт со склада: выгрузка с терминала сбора данных или Excel, куда кладовщики заносили количество по ходу обхода. Обычно это штрихкод или артикул плюс посчитанное количество.
- Файл учёта — остатки из учётной системы: отчёт «Остатки товаров на складах» из 1С или аналогичная выгрузка из МойСклад, Битрикса, ERP. Здесь колонок больше: наименование, склад, резерв, цена.
Задача подготовки одна для обоих: привести каждый файл к виду «ключ + количество». Ключ — то, по чему строки сопоставляются: артикул или штрихкод. Важно, чтобы в обоих файлах это была одна и та же сущность: сверять факт по штрихкодам с учётом по артикулам поставщика нельзя — половина позиций уедет в ложные расхождения.
Готовим файл фактического подсчёта
Складской файл почти всегда «сырой»: один товар посчитан на трёх полках и встречается тремя строками, кто-то вписал «12 шт» текстом, кто-то отсканировал одну коробку дважды.
- Суммируйте повторы. Один артикул на разных зонах хранения — норма: перед сверкой сверните дубли с суммой количества. Быстрее всего сводной таблицей: строки — артикул, значения — сумма количества. Или через «Данные → Консолидация».
- Отличайте повторы от ошибок. Если дубль — результат двойного сканирования одной упаковки, суммировать его нельзя: такие строки нужно находить и убирать, техника разобрана в статье про удаление дублей в Excel.
- Почистите числа. «12 шт», «1 5» с пробелом, количество текстом — всё это ломает итоги. Оставьте в колонке количества только цифры.
На выходе — две колонки: «Артикул» (или «Штрихкод») и «Количество факт». Больше в этом файле ничего не нужно.
Готовим выгрузку из учётной системы
Выгрузка остатков — обычно «красивый отчёт», а не таблица: многострочная шапка с реквизитами, группировки по складам и категориям, строки «Итого» по каждой группе, отдельные колонки «Резерв» и «Доступно». Как получить из 1С именно плоскую таблицу, описано в статье про выгрузку из 1С в плоский вид; если отчёт уже есть как есть, быстрее привести его к нужному виду конвертером:
- Загрузите выгрузку в конвертер для подготовки файлов к инвентаризации — .xls, .xlsx и .csv до 2 МБ обрабатываются бесплатно и без регистрации.
- На шаге «Структура» укажите строку, с которой начинается таблица, схлопните многострочную шапку и уберите строки-разделы с названиями складов и групп.
- На шаге «Колонки» оставьте артикул и количество и переименуйте их в «Артикул» и «Количество учёт» — в той же логике, что и файл факта.
- На колонку количества добавьте действие «Убрать пробелы»: «1 240» станет числом 1240. Формулы на экспорте заменяются значениями — сверяться будет то, что реально стояло в учёте.
- На шаге «Фильтры» отсеките служебные строки условием «не содержит Итого».
Важный нюанс: заранее решите, что считать учётным остатком — колонку «Остаток» или «Доступно» (остаток минус резерв). Если на складе пересчитывали и зарезервированный под заказы товар, сравнивать нужно с полным остатком; если резерв лежит в отдельной зоне и в подсчёт не попал — с доступным. Неправильный выбор этой колонки — самый массовый источник ложных расхождений.
Сверка по ключу: ВПР или XLOOKUP
Механика та же, что при сравнении двух прайсов, только вместо цен — количества. В файл факта добавьте колонку и подтяните учётный остаток по ключу: =ВПР(A2;[uchet.xlsx]Лист1!A:B;2;0) — или XLOOKUP, если Excel свежий. Рядом — разница: факт минус учёт.
Результат разваливается на три списка:
- Разница не равна нулю — расхождения: недостачи (факт меньше учёта) и излишки (факт больше).
- #Н/Д при подтягивании учёта в факт — товар есть на складе, но в системе не числится: неоприходованные излишки или чужой резерв.
- #Н/Д при обратном ВПР (из учёта в факт) — числится в системе, но на складе не найдено: классическая недостача.
Обратный ВПР обязателен: односторонняя сверка не увидит позиции, которых нет в файле факта.
Типовые источники расхождений
Прежде чем подписывать ведомость, прогоните расхождения по чек-листу — большинство «недостач» живут в данных, а не на складе:
- Единицы измерения. Учёт ведёт в коробах по двенадцать, кладовщики считали в штуках — расхождение ровно в двенадцать раз. Коэффициенты упаковок проверяйте первым делом.
- Дубли в учётном файле. Один товар числится на двух складах или двумя партиями — сверните остатки до уровня артикула, иначе факт 100 сравнится с учётом 60 и 40 по отдельности.
- Резервы и непроведённые документы. Отгрузка собрана, но документ не проведён; возврат пришёл вчера и ещё не оприходован.
- Формат ключа. Штрихкод текстом в одном файле и числом в другом, потерянный ведущий ноль EAN-13 — ВПР честно не находит совпадений, и позиции уезжают в излишки и недостачи одновременно.
- Разный момент среза. Учёт выгружен на утро, подсчёт шёл до вечера — все продажи дня сидят в «расхождениях».
Оформление результата для учётной системы
Итог сверки — ведомость расхождений: пять колонок «Артикул — Наименование — Учёт — Факт — Разница» и только строки, где разница не равна нулю, плюс оба списка #Н/Д. Отсортируйте по модулю разницы и начинайте разбор с крупнейших сумм.
Дальше ведомость уходит в учётную систему. В 1С для этого служит документ инвентаризации: он заполняется по остаткам учёта, а фактическое количество вносится отдельной колонкой — из вашего файла, вручную или загрузкой из Excel, если конфигурация это умеет. Точный шаблон загрузки проверьте в своей конфигурации: состав и порядок колонок различаются. На основании документа проводятся оприходование излишков и списание недостач, а чистый файл остаётся приложением к инвентаризационной описи.
Если инвентаризация у вас регулярная, сохраните настройки конвертации шаблоном — это доступно после регистрации. Следующая подготовка выгрузки сведётся к «загрузить → применить шаблон → скачать», и вся сверка уложится в час вместо недели.
Частые вопросы
Один товар посчитали в нескольких зонах склада — как сверять?
Суммируйте количество по ключу до сверки: сводной таблицей (строки — артикул, значения — сумма количества) или через «Данные → Консолидация». Если сверять построчно, каждый повтор даст ложное расхождение с учётным остатком.
ВПР не находит штрихкод, хотя он есть в обоих файлах. Что не так?
Разный тип данных: штрихкод текстом в одном файле и числом в другом, лишние пробелы или потерянный при открытии CSV ведущий ноль. Приведите ключ к одному виду в обоих файлах: уберите пробелы и задайте столбцу текстовый формат при импорте, чтобы нули не отрезались, — совпадения найдутся.
Расхождений оказались сотни — с чего начинать разбор?
Отсортируйте ведомость по модулю разницы в деньгах и идите сверху вниз. Первым делом проверяйте единицы измерения (короба против штук), дубли артикула в учётном файле и непроведённые документы — обычно там закрывается большая часть суммы расхождений.
Можно ли сверять сразу с колонкой «Доступно» вместо полного остатка?
Можно, если зарезервированный товар физически не попал в подсчёт — лежит в зоне сборки и не считался. Если же кладовщики считали всё подряд, включая резерв, сравнивать нужно с полным остатком, иначе каждая открытая заявка даст фантомную недостачу.