Как сравнить значения из Excel с данными 1С и найти расхождения?

← На главную

В практике программиста 1С часто возникает задача сверки внешних данных (например, из файлов Excel) с текущим состоянием информационной базы. Типичный сценарий: отдел маркетинга или логистики предоставляет таблицу с актуальными «топами» (категориями) номенклатуры, и нам нужно выявить позиции, где данные в 1С устарели или вовсе отсутствуют. Рассмотрим, как реализовать такой механизм, избегая классических ошибок производительности и логики запросов.

Постановка задачи и анализ исходных данных

Представим ситуацию: у нас есть документ Excel, где в первой колонке указано наименование номенклатуры, а в девятой колонке — значение ее «Топ-прогноза». В 1С эти данные хранятся в регистре сведений ЗначенияСвойствОбъектов. Нам необходимо вывести в табличный документ только те строки из Excel, которые:

Отличаются от значений, уже записанных в базе.
Отсутствуют в базе (для этой номенклатуры еще не задано значение свойства).

Типичные ошибки при сопоставлении данных

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

1. Поиск в цикле. Использование методов НайтиПоНаименованию внутри цикла обхода строк Excel — это крайне ресурсоемкая операция. При большом объеме данных (более 1000 строк) обработка может затянуться на минуты.

2. Дублирование наименований свойств. Часто в базе существуют свойства с одинаковыми именами, но разными владельцами. Например, свойство «Рейтинг» может относиться и к контрагентам, и к номенклатуре. Если искать значение просто по строке, 1С может вернуть ссылку на значение «чужого» свойства.

3. Неявное сравнение с NULL. В языке запросов 1С результат сравнения ЗначениеИзБазы <> ЗначениеИзExcel всегда будет ложным, если ЗначениеИзБазы является NULL (т.е. запись в регистре отсутствует). Это приводит к тому, что новые позиции просто не попадают в отчет.

Шаг 1: Подготовка таблицы значений и правильный поиск ссылок

Для начала подготовим промежуточную таблицу значений (ТЗ), в которую загрузим данные из Excel, предварительно преобразовав их в ссылки 1С. Важно при поиске значения свойства (справочник ЗначенияСвойствОбъектов) всегда указывать владельца — конкретное свойство из плана видов характеристик.


ТЗНоменклатуры = Новый ТаблицаЗначений;
ТЗНоменклатуры.Колонки.Добавить("Номенклатура", Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
ТЗНоменклатуры.Колонки.Добавить("ЗначениеТопаПрогноз", Новый ОписаниеТипов("СправочникСсылка.ЗначенияСвойствОбъектов"));

// Определяем свойство-владелец один раз вне цикла
СвойствоТоп = ПланыВидовХарактеристик.СвойстваОбъектов.НайтиПоКоду("000000467");

Для Каждого Строка ИЗ ТабличноеПоле Цикл
    НоменклатураТекст = СокрЛП(Строка[0]);
    СсылкаНоменклатура = Справочники.Номенклатура.НайтиПоНаименованию(НоменклатураТекст);
    
    Если СсылкаНоменклатура.Пустая() Тогда
        Продолжить; // Пропускаем, если номенклатура не найдена в базе
    КонецЕсли;

    ЗначениеТекст = СокрЛП(Строка[8]);
    // Ищем значение ТОЛЬКО в контексте нашего свойства
    СсылкаЗначение = Справочники.ЗначенияСвойствОбъектов.НайтиПоНаименованию(ЗначениеТекст, Истина, , СвойствоТоп);

    НоваяСтрока = ТЗНоменклатуры.Добавить();
    НоваяСтрока.Номенклатура = СсылкаНоменклатура;
    НоваяСтрока.ЗначениеТопаПрогноз = СсылкаЗначение;
КонецЦикла;

Важный нюанс: метод НайтиПоНаименованию чувствителен к лишним пробелам. Рекомендуется использовать очистку строки не только через СокрЛП, но и удалять неразрывные пробелы, которые часто приходят из Excel (символ с кодом 160).

Шаг 2: Реализация логики сравнения в запросе

Чтобы эффективно найти расхождения, воспользуемся механизмом временных таблиц и левым соединением. Посмотрим на структуру запроса, который решит обе задачи: найдет и несовпадения, и отсутствие данных.


Запрос = Новый Запрос;
Запрос.УстановитьПараметр("ТЗНоменклатуры", ТЗНоменклатуры);
Запрос.Текст = 
"ВЫБРАТЬ
|	ТЗ.Номенклатура КАК НоменклатураДокумента,
|	ТЗ.ЗначениеТопаПрогноз КАК ЗначениеExcel
|ПОМЕСТИТЬ ВТ_ДанныеExcel
|ИЗ
|	&ТЗНоменклатуры КАК ТЗ
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
|	ВТ.НоменклатураДокумента,
|	ВТ.ЗначениеExcel,
|	ЕСТЬNULL(РС.Значение, ЗНАЧЕНИЕ(Справочник.ЗначенияСвойствОбъектов.ПустаяСсылка)) КАК ЗначениеВБазе
|ИЗ
|	ВТ_ДанныеExcel КАК ВТ
|		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК РС
|		ПО ВТ.НоменклатураДокумента = РС.Объект
|			И (РС.Свойство.Код = ""000000467"")
|ГДЕ
|	ВТ.ЗначениеExcel <> ЕСТЬNULL(РС.Значение, ЗНАЧЕНИЕ(Справочник.ЗначенияСвойствОбъектов.ПустаяСсылка))";

Результат = Запрос.Выполнить();

Проанализируем ситуацию с ЛЕВЫМ СОЕДИНЕНИЕМ. Мы берем все данные из нашей временной таблицы (Excel) и пытаемся найти соответствующие им записи в регистре. Если записи в регистре нет, поле РС.Значение примет состояние NULL. С помощью функции ЕСТЬNULL мы превращаем этот NULL в пустую ссылку. Таким образом, сравнение ВТ.ЗначениеExcel <> ЗначениеВБазе сработает корректно даже для новых позиций.

Шаг 3: Вывод результатов в отчет

Теперь разберем, как вывести полученные расхождения в табличный документ для пользователя. Пройдемся в цикле по выборке запроса и заполним области макета.


Выборка = Результат.Выбрать();

Пока Выборка.Следующий() Цикл
    ОбластьТело.Параметры.Номенклатура = Выборка.НоменклатураДокумента;
    
    Если Выборка.ЗначениеВБазе.Пустая() Тогда
        ОбластьТело.Параметры.Комментарий = "Запись в базе отсутствует";
    Иначе
        ОбластьТело.Параметры.Комментарий = "Несовпадение значений";
    КонецЕсли;
    
    ОбластьТело.Параметры.ЗначениеExcel = Выборка.ЗначениеExcel;
    ОбластьТело.Параметры.Значение1С = Выборка.ЗначениеВБазе;
    
    ТабДок.Вывести(ОбластьТело);
КонецЦикла;

ТабДок.Показать("Результат сравнения");