Ключевой столбец: сопоставляйте таблицы по коду, а не по названию

Выгрузка остатков из 1С и результаты пересчёта лежат рядом. Многие сопоставляют их по названию, надеясь, что рядом с «Мука, 50 кг» найдётся строка, записанная точно так же. Но если названия отличаются на один символ, где-то лишний пробел или слова стоят в другом порядке, формула не найдёт товар. Хуже того, иногда она находит чужой товар и прячет ошибку. Решение простое: сопоставляйте не названия, а уникальный код, один на товар, то есть ключевой столбец.

Что такое ключ

Ключ однозначно определяет строку. Для сопоставления подходят:

• код товара или артикул в 1С;
• внутренний SKU, если он присвоен каждому товару по одному;
• штрихкод, но только при соблюдении правила «один код — один товар».

Не подходят: название (меняется и повторяется), порядковый номер строки (при каждой выгрузке другой), артикул поставщика (у разных поставщиков он может совпасть). Ключ должен быть и в листе пересчёта, поэтому стройте лист из списка 1С и пусть столбец с кодом будет напечатан, а не вписан от руки.

Подготовьте ключ до сверки

1. В обеих таблицах переведите столбец кодов в текстовый формат, чтобы сохранились ведущие нули.
2. Уберите лишние пробелы функцией TRIM.
3. Проверьте уникальность кода: если результат COUNTIF(A:A;A2) больше 1, код повторяется. В таблице 1С повторов быть не должно. Если в таблице пересчёта один товар посчитан в двух местах, сначала сведите значения в одну строку через SUMIF.
4. Назовите заголовки столбцов в обоих файлах одинаково, чтобы потом не запутаться.

Связываем через VLOOKUP и XLOOKUP

Допустим, на листе 1С столбец A — код, столбец B — остаток, а на листе пересчёта A — код, B — факт. В столбец C листа 1С впишите формулу IFERROR(VLOOKUP(A2;Пересчёт!A:B;2;0);"не найдено"). В русском Excel эти функции называются ЕСЛИОШИБКА и ВПР. Последний аргумент 0 означает точное совпадение; без него Excel возьмёт ближайшее значение и молча выдаст неверное число. В новых версиях (Excel 2021 и Microsoft 365) удобнее XLOOKUP: он не зависит от порядка столбцов и сам принимает текст на случай, если значение не найдено.

В столбце D посчитайте разницу: факт минус остаток. Строки с надписью «не найдено» в этот расчёт не входят, их разбирают отдельно.

Если связка не работает: пять частых причин

• В одной таблице код записан числом, в другой текстом (в углу ячейки зелёный треугольник). Выглядят они одинаково, но Excel считает их разными.
• В конце стоит невидимый или неразрывный пробел.
• У кода пропал ведущий ноль: вместо 000451 стоит 451.
• В 1С код товара изменили, а лист пересчёта построен по старой выгрузке.
• В формуле неверно указан диапазон: столбец поиска не стоит первым в диапазоне.

Первые три причины видны за минуту через LEN и ISNUMBER, для четвёртой достаточно сравнить даты выгрузок.

Проверка в обе стороны

Односторонней связки недостаточно. В первом направлении коды из 1С ищутся в пересчёте: не найденные — это непосчитанные товары или строки, выпавшие из листа. Во втором направлении коды из пересчёта ищутся в 1С: не найденные — это новые, ошибочно записанные или чужие для этого склада коды.

Условный пример: в 1С 1 200 кодов, в пересчёте 1 150. Совпало 1 120. Строк «есть в 1С, нет в пересчёте» 80: часть из них карточки с нулевым остатком, часть — непосчитанный стеллаж. Строк «есть в пересчёте, нет в 1С» 30: чаще всего это ошибки при ручной записи кода. Без второй проверки эти 30 строк остались бы вообще незамеченными.

Что делать с ненайденными строками

Не оставляйте #Н/Д в таблице. Вынесите ненайденные строки на отдельный лист и напишите причину по каждой:

• ошибка в коде;
• непосчитанное место;
• другой склад;
• новая карточка, ещё не внесённая в 1С.

Пока причина не установлена, такие строки не входят в общую сумму расхождений. Если код ошибочный, исправьте его и повторите связку.

Вывод

Сверка по названию держится на случайных совпадениях, сверка по коду — это процесс, который можно проверить. Подготовьте ключ, связывайте по точному совпадению, проверяйте в обе стороны и отделяйте ненайденное с указанием причины. SkladSoft связывает таблицы 1С и пересчёта по коду и передаёт ненайденные строки с причинами; изменения в системе вносятся только с вашего письменного согласия.