Как я научил 1С автоматически сопоставлять колонки Excel
Открываю очередной прайс от нового поставщика. Колонки называются «SKU товара», «Название позиции», «Ед. изм.», «Цена (с НДС)». В 1С реквизиты справочника номенклатуры — «Артикул», «Наименование», «ЕдиницаИзмерения», «Цена». Задача выглядит простой: понять, что «SKU товара» это «Артикул», «Ед. изм.» — это «ЕдиницаИзмерения», и так далее.
Когда делаешь это руками — очевидно за тридцать секунд. Когда пытаешься заставить программу делать это автоматически — начинаются вопросы, и первый из них звучит так: а откуда программа вообще должна знать, что «SKU товара» и «Артикул» — одно и то же?
Почему нельзя просто сравнить строки
Первое, что приходит в голову — сравнить название колонки в файле с названием реквизита в 1С. Совпадают — сопоставляем. Не совпадают — пропускаем. Реализуется за двадцать строк кода.
Я попробовал. Сработало примерно в трети случаев.
Потому что в реальных файлах колонки называются так, как придумал конкретный человек в конкретный день. Я собрал несколько десятков прайсов от разных поставщиков и просто выписал, как у них называются одни и те же поля. Никакого стандарта не обнаружилось.
«Артикул» приходит как SKU, Article, Арт., Код товара, Артикул поставщика, Артикул производителя. «Наименование» — как Название, Название товара, Товар, Name, Наименование позиции. «ЕдиницаИзмерения» — как Ед., Ед. изм., Единица, Unit. Это не экзотика, это обычная рабочая жизнь. Каждый называет колонки как хочет, и формально все правы.
Попалась одна выгрузка из старой самописной системы, где заголовки выглядели как «Колонка 1», «Колонка 2», «Колонка 3» — и так и оставались годами, потому что никто не чинит то, что формально работает.
Точное совпадение в таких условиях работает только тогда, когда файл сформировала та же система, которая потом его читает. Во всех остальных случаях — это лотерея.
Сначала был словарь
Следующая идея выглядела практичнее. Раз уж я выписал все варианты названий из реальных прайсов — можно просто зафиксировать их в коде. Если люди используют ограниченный набор слов, словарь закроет большинство случаев.
Так и сделал. Для каждого реквизита — список того, как он ещё может называться. Не придуманный, а собранный из реальных файлов:
"Артикул": ["sku", "артикул поставщика", "код товара", "article", "арт", "art"]
"Наименование": ["название", "название товара", "наименование позиции", "name", "товар"]
"ЕдиницаИзмерения": ["ед", "ед.", "ед. изм.", "единица", "unit"]
Это только верхушка. В итоговом словаре — контрагенты, банковские реквизиты, табличные части документов, склады, характеристики. Каждый вариант туда попал потому, что однажды встретился в реальном файле и не сопоставился автоматически.
Логика простая: берём заголовок из файла, приводим к нижнему регистру, убираем лишние пробелы — ищем в словаре. Нашли — сопоставляем, статус «Авто». Процент автоматически угаданных колонок вырос раза в два по сравнению с точным совпадением.
Но словарь живёт своей жизнью, и жизнь эта не очень приятная. Приходит новый поставщик, у него «Артикул производителя» — не в словаре. Другой пишет «Artikul» через латиницу — не в словаре. Третий допускает опечатку «Артикл» — тоже мимо. Добавляешь один вариант, через неделю находишь три новых.
Главная проблема словаря даже не в этом. Его кто-то должен поддерживать. Пока это я — значит, каждый новый вариант названия колонки, который не сопоставился, это задача для меня лично. Масштабироваться так невозможно.
Добавил нечёткое сравнение
Стал смотреть в сторону алгоритмов сравнения строк. Не потому что хотелось усложнить, а потому что словарь явно не закроет все варианты никогда — их просто слишком много.
Остановился на расстоянии Левенштейна. Объяснять его проще всего через редактуру текста. Представьте, что у вас есть слово, и вы хотите получить из него другое слово. Каждое изменение — это операция: вставить букву, удалить букву, заменить одну букву на другую. Расстояние Левенштейна — это минимальное количество таких операций.
«Кот» → «Код»: одна операция, заменить «т» на «д». Расстояние = 1, строки очень похожи.
Пример из нашей задачи: «Наименование» → «Наименование товара». Нужно вставить 8 символов — пробел и слово «товара». Расстояние = 8. Само по себе число ничего не говорит, поэтому считаем относительное сходство: 1 − 8/21 ≈ 0,62, то есть 62%. Достаточно, чтобы предложить пользователю — но уже не с полной уверенностью, а со статусом «Предложено». Пусть глянет и подтвердит.
Опечатки перестали быть проблемой. «Артикл» вместо «Артикул» — одна пропущенная буква, алгоритм справляется. Вариации написания тоже перестали ломать загрузку.
Где алгоритм ошибается
Казалось бы — всё, задача решена. Но алгоритм Левенштейна не понимает смысл, он работает с буквами. И это периодически выходит боком.
Самый запомнившийся случай — «ШтрихКод» и реквизит «Код». Система упорно предлагала их сопоставить. С точки зрения алгоритма логика железная: «Код» полностью входит в «ШтрихКод» как подстрока, расстояние небольшое. Для человека это очевидно разные вещи, для алгоритма — очень похожие строки.
Та же история с «Ценой» и «Ценой закупки», с «Количеством» и «КоличествомМест».
Вылечил штрафом за разницу в длине: если строки отличаются по длине в два раза и больше — итоговая уверенность снижается, даже если сами символы похожи. «Код» (3 символа) и «ШтрихКод» (9 символов) — разница в три раза, значит скидываем 20-30% от рассчитанного сходства. Не самое изящное решение, но ложных срабатываний стало заметно меньше.
Есть ещё одно ограничение, которое не лечится никак: алгоритм не знает английский. «SKU» и «Артикул» — ноль общих символов, расстояние максимальное. Здесь спасает только словарь. Поэтому словарь и нечёткое сравнение дополняют друг друга, а не заменяют.
Как три уровня работают вместе
Итоговый порядок для каждой колонки из файла:
- Точное совпадение (без учёта регистра) — статус «Авто», дальше не смотрим
- Поиск в словаре синонимов — статус «Авто»
- Нечёткое сравнение по всем реквизитам объекта-приёмника — если есть достаточно похожий вариант, статус «Предложено»; если нет — «НеНайдено»
В таблице сопоставления это видно цветом: зелёный для «Авто», жёлтый для «Предложено», розовый для «НеНайдено». Пользователь сразу понимает, где алгоритм уверен, а где лучше проверить.
Честно про ограничения
Процент автоматически сопоставленных колонок сильно зависит от файла. Хорошо структурированный прайс от аккуратного поставщика — 80-90% без участия пользователя. Выгрузка из старой системы с непонятными заголовками — 30-40%, и это нормально, потому что там просто нечего угадывать.
Пользователь всё равно иногда корректирует руками. Это не недостаток — это честная работа системы. Цель не убрать человека из процесса, а убрать тупую механическую работу. Вместо пяти минут перетыкивания каждой колонки заново — тридцать секунд проверки предложений.
Шаблоны решают отдельный сценарий: один и тот же файл грузится каждую неделю. Настроил один раз, сохранил шаблон, в следующий раз загрузил — сопоставление применилось автоматически. Алгоритм в этом случае вообще не нужен.
Главное, что понял в процессе: алгоритм не должен угадывать любой ценой. Если не уверен — лучше сказать об этом явно, чем молча записать не туда. «НеНайдено» это не ошибка, это честный ответ.
Всё это реализовано в обработке на Infostart: //infostart.ru/marketplace/2715613/. Если грузите Excel в 1С регулярно — посмотрите, возможно пригодится.
Вступайте в нашу телеграмм-группу Инфостарт