Почему при загрузке данных из Excel в 1С число 5.39 преобразуется в 5.38999999?

← На главную

Проанализируем распространенную проблему, с которой сталкиваются разработчики 1С при импорте данных из таблиц Microsoft Excel через COM-соединение. На экране в ячейке отображается точное десятичное число, например 5.39, однако при обращении к свойству Value в 1С попадает значение с двоичной погрешностью — 5.38999999999. Выясним причину такого поведения и разберем по шагам способы решения этой задачи.

Причина расхождения: специфика хранения чисел с плавающей запятой

Выясним подробно, почему возникает подобное искажение при передаче данных.

В платформе 1С тип данных Число реализован как десятичное число с фиксированной запятой (Fixed-Point Decimal). Это позволяет обрабатывать финансовые и учетные показатели с абсолютной точностью без потери разрядов.

В Microsoft Excel вещественные числа хранятся в формате с плавающей запятой двойной точности (стандарт IEEE 754, тип double / 64-битный Float). Число в памяти компьютера представляется в виде знака, мантиссы и показателя степени двойки.

Многие десятичные дроби (включая 5.39 или 4.39) невозможно точно представить в виде конечной двоичной дроби. При переводе десятичного числа в двоичную систему счисления оно превращается в бесконечную периодическую дробь и округляется до ближайшего значения, вмещающегося в 64 бита памяти.

Посмотрим на пример кода на VBA, который наглядно демонстрирует накапливаемую погрешность двоичной арифметики:


s = 0
For i = 0 To 100
    s = s + 0.01
    Debug.Print s
Next

В процессе выполнения данного цикла при очередном прибавлении 0.01 значение в определенный момент превращается из 0.8 в 0.810000000000001.

Когда 1С считывает значение ячейки через COM-интерфейс с помощью обращения к xl.Cells(row, col).Value, происходит маршалинг OLE-объекта (тип VT_R8) и конвертация двоичного числа double в десятичное число 1С. В этот момент и проявляется реальное внутреннее значение, записанное в памяти Excel.

Различие между свойствами Value, Value2 и Text

Рассмотрим, почему пользователь видит на экране 5.39, а программный код считывает длинную дробь:

Свойство Value — возвращает внутреннее значение ячейки в виде числа с плавающей запятой.
Свойство Value2 — работает аналогично Value, но пропускает внутренние форматирования дат и валют Excel, что ускоряет обработку.
Свойство Text — возвращает форматированное строковое представление ячейки точно в том виде, в котором оно отображается пользователю на экране (например, "5,39").

Способ 1. Явное округление чисел в коде 1С

Рассмотрим наиболее простой и эффективный метод устранения погрешности при работе через COM-соединение. Для получения точного значения достаточно применить функцию 1С Окр() сразу после считывания данных из ячейки.

Разберем пример кода с явным округлением:


ЧислоИзExcel = Ячейка.Value;
Если ТипЗнч(ЧислоИзExcel) = Тип("Число") Тогда
    ТочноеЗначение = Окр(ЧислоИзExcel, 2);
КонецЕсли;

Если для оптимизации скорости работы данные считываются сразу всем диапазоном в массив, округление выполняется при обходе элементов массива:


МассивДанных = Лист.UsedRange.Value2;
Для ИндексСтроки = 1 По МассивДанных.Количество() Цикл
    ИсходноеЗначение = МассивДанных[ИндексСтроки][7];
    Если ТипЗнч(ИсходноеЗначение) = Тип("Число") Тогда
        ОбработанноеЗначение = Окр(ИсходноеЗначение, 2);
    КонецЕсли;
КонецЦикла;

Способ 2. Использование свойства Text и преобразование строки

Проанализируем вариант с получением текста ячейки. Свойство Text позволяет прочитать строку именно в том виде, в котором она отображается в Excel.

При использовании свойства Text необходимо учитывать следующие особенности:

Если ширина колонки в файле недостаточно велика, свойство Text вернет строку из символов решетки ("###").
Возвращаемое значение зависит от региональных настроек операционной системы и содержит разделители групп разрядов или неразрывные пробелы.

Посмотрим на пример обработки строкового значения из ячейки:


ТекстЯчейки = Ячейка.Text;
ТекстЯчейки = СтрЗаменить(ТекстЯчейки, Символы.НПП, "");
ТекстЯчейки = СтрЗаменить(ТекстЯчейки, ".", ",");
ЧисловоеЗначение = Число(ТекстЯчейки);

Способ 3. Чтение файла через объект ТабличныйДокумент (без COM)

Рассмотрим самый надежный и кроссплатформенный способ чтения файлов Excel в 1С. Встроенный объект ТабличныйДокумент считывает файлы .xlsx напрямую, исключая работу с COM-интерфейсом и преобразование в тип double.

Преимущества данного подхода:

Высокая скорость обработки больших файлов.
Полная кроссплатформенность (работает на серверах под управлением Linux).
Отсутствие необходимости устанавливать Microsoft Excel на компьютер.
Сохранение точности десятичных чисел.

Разберем пример чтения файла Excel с помощью табличного документа:


ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлу, ТипФайлаТабличногоДокумента.XLSX);

КоличествоСтрок = ТабДок.ВысотаТаблицы;
Для НомерСтроки = 1 По КоличествоСтрок Цикл
    ЗначениеТекстом = ТабДок.Область(НомерСтроки, 7).Текст;
    // Приведение строки к числу
    ЧисловоеЗначение = Число(СтрЗаменить(ЗначениеТекстом, Символы.НПП, ""));
КонецЦикла;

Подводя итог: при работе через COM-соединение следует применять явное округление с помощью Окр(), а для быстрой и универсальной загрузки файлов лучше всего использовать встроенный объект ТабличныйДокумент.