Как сопоставить прайс поставщика с каталогом без 1С

Сопоставление прайса поставщика с каталогом означает, что для каждой строки прайса вы находите свою позицию или помечаете, что её нет. Без 1С это делают в Excel или CSV: по артикулу через ВПР, по названию через ПОИСКПОЗ, а остальное сверяют глазами по характеристикам. Ниже разберём, где формулы работают, где ломаются и как вести таблицу соответствий.

Что нужно на входе

Две таблицы на соседних листах одной книги.

• Прайс поставщика: его артикул, наименование, единица, цена.
• Ваш каталог: код, наименование, артикул, если он заполнен, единица.

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

Ручной способ: ВПР и ПОИСКПОЗ

Точное совпадение по артикулу

Если у поставщика и у вас один и тот же артикул, хватит одной формулы. Пусть артикул поставщика в столбце A листа «Прайс», а каталог на листе «Каталог»: артикул в столбце A, ваш код в столбце B.

=ВПР(A2;Каталог!A:B;2;ЛОЖЬ)

Формула вернёт ваш код или ошибку #Н/Д, если артикул не найден. В новых версиях Excel то же делает ПРОСМОТРX, а связка ИНДЕКС и ПОИСКПОЗ работает в любой версии и умеет брать значение из столбца левее искомого. Сверка двух списков по ключу разобрана в статье про два прайса в Excel.

Поиск по части названия

Когда артикулов нет, ищут по фрагменту наименования. ПОИСКПОЗ понимает подстановочный знак «*»:

=ПОИСКПОЗ("*"&C2&"*";Каталог!C:C;0)

Здесь в C2 стоит ключевой фрагмент из строки поставщика, например «6205». Формула вернёт номер первой строки каталога, где он встречается.

Где формулы перестают помогать

• Находят только первое совпадение. Если в каталоге есть «6205-2RS», «6205-2Z» и открытый «6205», ПОИСКПОЗ вернёт тот, что выше.
• Сравнивают символы. «Ду80» с латинской «y» и «Ду80» с русской для Excel разные строки. То же с «ВВГнг(A)» и «ВВГнг(А)».
• Не знают синонимов. Старое обозначение подшипника 180205 и международное 6205-2RS у формулы ничего общего не имеют.
• Не видят характеристик. Фрагмент «Задвижка 30с41нж Ду80» одинаково подойдёт к задвижке Ру16 и Ру25.

В Power Query есть нечёткое объединение таблиц с порогом сходства. Оно находит больше пар, но страдает тем же: строки, похожие по буквам, оно считает одним товаром. «6205-2RS» и «6205-2Z» для него почти одинаковы.

Когда артикул подводит

Артикул надёжен, если поставщик указывает артикул производителя, а у вас в каталоге тот же. На практике так бывает редко. Три способа сопоставления, от артикула до смысла, сравниваются в отдельной статье.

• Поставщик ставит свой внутренний артикул, который в вашем каталоге не встречается.
• Один артикул записан с пробелами, дефисами или без них, и ВПР его не находит.
• В каталоге МТР артикул часто вообще не заполнен.
• Один артикул у поставщика соответствует разным фасовкам: штуке и упаковке.

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

Как сверять по смыслу и характеристикам

Приведите записи к одному виду

Сделайте в обеих таблицах вспомогательный столбец. В нём уберите лишние пробелы функцией СЖПРОБЕЛЫ, переведите текст в один регистр функцией СТРОЧН и замените латинские буквы на русские через вложенные ПОДСТАВИТЬ. Туда же сведите «х», «x» и «*» в размерах к одному знаку. Сравнивайте уже эти столбцы.

Выделите ключевые характеристики

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

Сверьте единицу измерения

Поставщик может продавать кабель бухтами, а вы ведёте его в метрах. Крепёж в прайсе идёт упаковками по 100 штук, а у вас поштучно. Если единицы разные, запишите коэффициент пересчёта в отдельный столбец. Иначе цены поставщиков нельзя будет сравнить.

Пример таблицы соответствий

Результат удобно держать в одной таблице. На каждую строку прайса одна строка соответствия.

Коды в примере условные. Добавьте столбцы с датой проверки и с тем, кто проверял. Тогда при следующем прайсе вы увидите, какие пары уже подтверждены, и будете разбирать только новые строки.

Что делать со строками без пары

Не подбирайте ближайшую позицию, если точного соответствия нет. Ошибочная пара хуже пустой: по ней закупят не тот подшипник. Оставьте у строки статус «нет в каталоге» и решите отдельно: заводить новую позицию или считать её аналогом существующей. Второе решает тот, кто отвечает за каталог.

Предполагаю, что у вас не один поставщик и прайсы обновляются регулярно. Так ли это? Тогда ручная сверка повторяется каждый раз, и на тысячах строк она занимает дни. Мы делаем ту же работу для каждой строки прайса: сверяем смысл, ключевые характеристики и единицу, ставим уверенность и выносим спорное с причиной. Результат приходит таблицей соответствий в Excel. Подробнее на странице о сопоставлении номенклатуры поставщика, а порядок работы описан в разделе как мы проверяем строки. Начать можно с пробы на одном прайсе.

Вопросы про сверку прайса

Можно ли сделать то же в Google Таблицах?

Да. ВПР, ПОИСКПОЗ, ИНДЕКС и ПРОСМОТРX там работают так же, подстановочный знак «*» тоже. Нечёткого объединения, как в Power Query, в Google Таблицах нет.

Почему ВПР выдаёт #Н/Д, хотя артикул на месте?

Чаще всего в одной таблице артикул хранится числом, а в другой текстом, или в нём есть пробел в конце. Приведите оба столбца к тексту и уберите пробелы функцией СЖПРОБЕЛЫ.

Как не сопоставлять заново при каждом новом прайсе?

Храните таблицу соответствий отдельно от прайса. Новый прайс сначала сверяйте с ней по строке поставщика. Вручную разбирайте только строки, которых в таблице ещё нет или у которых изменилось наименование.

Что делать, если у поставщика одна строка, а у вас две похожие позиции?

Скорее всего, у вас в каталоге дубль. Сначала решите, какая карточка основная, и только потом ставьте соответствие. Иначе поставки будут уходить то на одну карточку, то на другую. Как искать такие дубли прямо в таблице, описано здесь.