LБлог losLab по разработке программного обеспеченияИнженерные заметки для разработчиков PDF, Delphi и программного обеспечения для работы с документами.

Техническая статья

Precision as displayed в HotXLS: правила округления Excel

HotXLS Delphi Component

Excel precision as displayed округляет каждое сохраняемое число до знаков, которые показывает его числовой формат: берётся секция формата, совпадающая со знаком значения, по два лишних знака на каждый %, по три в минус на каждую запятую масштабирования до тысяч, а округление — от нуля на половине. HotXLS применяет то же правило в обоих своих Delphi-движках, когда TXLSXWorkbook.FullPrecision или TXLSWorkbook.UseFullPrecision равен False. Звучит как однострочник, пока клиент не пожалуется, что итоги вашего экспортированного счёта расходятся с Excel на цент, или что столбец длительностей в [ss].00 схлопнулся в нули. Случилось и то и другое, и оба случая тянутся к одной неправильно понятой из этих правил. С v2.384.57 оба движка делят одну реализацию, чьи ожидаемые значения вымерены в Excel 16 со включённым Workbook.PrecisionAsDisplayed

Что precision as displayed реально меняет в книге?

Precision as displayed — одиночный флаг уровня книги, который велит вычислительному движку хранить числа так, как они выглядят, а не как были вычислены. В UI Excel он сидит в File, Options, Advanced, «When calculating this workbook», под именем «Set precision as displayed». На диске это один бит. BIFF8-файл несёт его в записи CalcPrecision ($000E, [MS-XLS] §2.4.35), чьё поле fFullPrec равно 1 при нормальной полной точности и 0 при включённой опции. XLSX-пакет несёт его как атрибут fullPrecision элемента calcPr в workbook.xml, определённом в ECMA-376 Part 1, где дефолт true, а fullPrecision="0" включает округление

Флаг — не предпочтение отображения. Когда вы ставите галочку, Excel предупреждает, что данные навсегда потеряют точность, и не врёт: значения переписываются до показанной точности, а отрезанные цифры исчезают. Снять галочку позже старые цифры не вернёт. 0.1234, показанный как 12.3%, становится 0.123 навсегда

HotXLS читает и пишет флаг в обоих форматах и выставляет его в обоих движках:

  • TXLSXWorkbook.FullPrecision: Boolean на движке XLSX, читается из и сохраняется в calcPr/@fullPrecision
  • TXLSWorkbook.UseFullPrecision: Boolean на классическом движке (также на IXLSWorkbook), читается из и сохраняется в запись CalcPrecision
  • Оба по умолчанию True — безопасный, неразрушающий режим и экселевский дефолт

Где HotXLS применяет округление — важно. HotXLS округляет в точке вычисления значения: каждый результат формулы округляется до показанной точности, прежде чем лечь в кэш ячейки, — при Recalculate и при вычислении по требованию. Константы, присвоенные через Value, хранятся ровно как даны. Если вывод обязан воспроизводить то, что Excel хранит после установки галочки, округлите эти константы сами перед записью, например хелпером, показанным ниже

Как Excel решает, сколько знаков сохранить?

Excel выводит число сохраняемых знаков из конкретной секции формата, показывающей значение, а не из строки формата целиком. Правила ниже вымерены в Excel 16 и составляют то, что XlsApplyDisplayedPrecision в lxNumFormat реализует для обоих движков HotXLS

  1. Выбирайте секцию по знаку. Формат из двух секций берёт вторую для отрицательных значений. Формат из трёх и более секций берёт вторую для отрицательных и третью для точного нуля. Всё прочее берёт первую секцию
  2. Считайте заполнители дробной части. Каждый 0, # или ? после десятичной точки в той секции добавляет один сохраняемый знак
  3. Прибавляйте два на каждый знак процента. 0.0% показывает 0.1234 как 12.3%, так что сохраняемое значение — сотая часть видимого и держит три знака, а не один
  4. Вычитайте три на каждую запятую масштабирования. Запятая после последнего целочисленного заполнителя (0,, 0.0,, 0,.0) делит показ на 1000. 0.0, показывает 12345.678 как 12.3, так что Excel держит один знак минус три, то есть отрицательный счёт: значение округляется до сотен и сохраняется как 12300. Запятая между целочисленными заполнителями, как в #,##0, — простая группировка разрядов и ничего не меняет
  5. Не трогайте нечисловые секции. General, секции дат и времени (включая истекающие [h], [mm] и [ss]), научные, дробные и текстовые секции, а также секции без единого цифрового заполнителя сохраняют полную точность
Схема HotXLS правил показанной точности: выбрать секцию формата по знаку значения, посчитать заполнители цифр после десятичной точки, прибавить два знака на каждый знак процента, вычесть три на каждую запятую масштабирования до тысяч, так что счёт может стать отрицательным, целиком пропустить секции General и даты-времени, затем округлять от нуля на половине
Счёт цифр берётся из секции, совпадающей со знаком, плюс два на процент и минус три на запятую масштабирования, а отрицательный счёт округляет до десятков или сотен; секции General и дат остаются в покое

Вмерено против Excel 16 — вот значения, которые оба движка HotXLS теперь хранят для результата формулы в каждом формате:

Числовой форматВычисленное значениеСохраняемое значениеПрименимое правило
0.0%0.12340.123Один знак плюс два за знак процента
02.53От нуля на половине, не к чётному
0-2.5-3От нуля на половине и на отрицательной стороне тоже
0.00;(0.0)-1.2345-1.2Отрицательная секция показывает один знак
0.00;(0.0)1.23451.23Положительная секция показывает два знака
#,##0.01234.56781234.6Запятая группировки, без масштабирования
0.0,12345.67812300Один знак минус три: округление до сотен
0.0%;(0.00%)-0.0125-0.0125Отрицательная секция держит два плюс два знака
0.001.0051.01Допуск на ошибку двоичного представления
0;-0;0.00.51Не ноль, так что решает положительная секция

Последняя строка — славная ловушка. Значение 0.5 округляется до целого, а нулевая секция и не вступает в игру, потому что Excel выбирает секцию по вычисленному значению до округления. Одно честное ограничение со стороны HotXLS: секции выбираются только по знаку, так что формат, чьи секции несут пользовательские скобочные условия вроде [>=1000], всё равно делится по знаку. Сверяйте такие форматы с Excel, если они вам важны

Почему 1.005 округляется в 1.01, а не в 1.00?

Excel округляет 1.005 в ячейке 0.00 в 1.01, хотя ближайший к 1.005 double чуть ниже середины, и HotXLS повторяет это допуском в несколько ulp. Литерал 1.005 непредставим в двоичной плавающей точке. Ближайший IEEE 754 double — 1.00499999999999989341858963598497211933135986328125, а умножение на 100 даёт 100.49999999999999. Учебниковый Floor(x * 100 + 0.5) / 100 потому возвращает 1.00 — вопреки числу, набранному пользователем, показываемому Excel и хранимому Excel

Delphi добавляет свой подвох. System.Round округляет половинки к чётному, так что Round(2.5) — это 2, а Round(3.5) — 4. Это банковское округление, разумный дефолт для статистики и неверное правило здесь: Excel хранит 3 для 2.5 в ячейке 0 и -3 для -2.5. Реализация HotXLS работает с абсолютным значением, прибавляет 0.5 плюс относительный допуск в 2-51 масштабированного значения (несколько ulp на том порядке, никогда не меньше двух ulp от 1.0), отсекает, масштабирует назад и возвращает знак. Следующая функция — самодостаточная иллюстрация того принципа, а не библиотечный код, и с отрицательными счётами цифр для запятых масштабирования она поступает так же:

Схема округления HotXLS: 2.5 округляется от нуля на половине в 3, а -2.5 в -3, тогда как Delphi System.Round даёт банковские ответы 2 и -2; а поскольку ближайший к 1.005 double сидит чуть ниже середины, именно допуск в несколько ulp превращает основанный на floor 1.00 в экселевский ответ 1.01
Excel округляет половинки от нуля и прощает ошибку двоичного представления малым допуском; обе детали вымеряемы, и пропуск любой из них сохранит 2 для 2.5 или 1.00 для 1.005 — в центе от Excel
// Принципиальный набросок: округление от нуля на половине до ADigits знаков,
// с допуском в несколько ulp, чтобы 1.005 доходило до 1.01.
// ADigits < 0 округляет до десятков, сотен, ... («0.0,» даёт -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, два ulp от 1.0
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // за пределами точности double: оставляем значение как есть
  Scale := 1;
  for I := 1 to Abs(ADigits) do
    Scale := Scale * 10;
  if ADigits >= 0 then
  begin
    if Abs(AValue) > 1E300 / Scale then
      Exit; // масштабирование переполнило бы
    Scaled := Abs(AValue) * Scale;
  end
  else
    Scaled := Abs(AValue) / Scale;
  Eps := Scaled * Tolerance;
  if Eps < Tolerance then
    Eps := Tolerance;
  Scaled := Int(Scaled + 0.5 + Eps); // от нуля на половине, не Round()
  if ADigits >= 0 then
    Result := Scaled / Scale
  else
    Result := Scaled * Scale;
  if AValue < 0 then
    Result := -Result;
end;

// RoundAsDisplayed(1.005, 2)      = 1.01   (через Floor: 1.00)
// RoundAsDisplayed(2.5, 0)        = 3      (Round: 2)
// RoundAsDisplayed(-2.5, 0)       = -3
// RoundAsDisplayed(0.1234, 3)     = 0.123  («0.0%»: 1 + 2 цифры)
// RoundAsDisplayed(12345.678, -2) = 12300  («0.0,»: 1 - 3 цифры)

Допуск — нарочитый компромисс. Значение, честно сидящее на два ulp ниже полуцелого шага, тоже округлится вверх, но на том расстоянии разница неотличима от ошибки представления, и трактовка её как полуцелого шага — то, что заставляет набранные десятичные вести себя, как ждут пользователи

Что было не так до v2.384.57?

До v2.384.57 движок XLSX и классический движок имели каждый собственный код precision-as-displayed, и каждый ошибался по-своему. Если выпускаете книги с включённой опцией, вот симптомы, которые стоит искать в файлах от старых сборок

Движок XLSX: только первая секция, без процента, банковское округление

Старый XLSX-путь спрашивал число десятичных знаков строки формата целиком, что смотрело только в первую секцию и игнорировало %, а затем округляло через Round. 0.1234 в 0.0% сохранялся как 0.1 — это 10% вместо 12.3% на экране. 2.5 в 0 сохранялся как 2 вместо 3. Отрицательные значения в формате вроде 0.00;(0.0) округлялись до двух знаков положительной секции. С v2.384.57 движок XLSX зовёт ту же общую подпрограмму, что и классический движок, — она же в том релизе обзавелась поддержкой запятой масштабирования

Классический движок: TRUE становился -1

Классический движок ограждал своё округление через VarIsNumeric, а VarIsNumeric возвращает True для Variant с varBoolean. Конверсия того Variant через Double(V) даёт -1, потому что COM-стиль Boolean True хранится как -1. Формула вроде =A1>0 в ячейке с форматом 0.00 потому выходила из пересчёта числом -1. С v2.384.57 Boolean-результаты отсекаются до всякой числовой проверки, и логический результат остаётся логическим в обоих движках

Форматы истёкшего времени читались как цвета (v2.384.9)

Третий баг сидел в модели числовых форматов, а не в округлении. Парсер классифицировал всякий скобочный токен, не являющийся условием, как цвет, так что [h], [mm] и [ss] никогда не помечали свою секцию как дату/время. Отображение не страдало — форматирование идёт по отдельному пути, — но precision as displayed полагается на тот флаг, чтобы пропускать временные значения. Длительность в пять секунд — это 5/86400 суток, около 0.0000579, и формат вроде [ss].00 выглядел как обычное число с двумя знаками, так что с выключенным FullPrecision длительность округлялась в 0.00 суток. С v2.384.9 скобочный прогон из единственной буквы h, m или s разбирается как токен истёкшего времени, а секция трактуется как дата/время. Тот же релиз починил распознавание минут в h:mm, где двоеточие между токенами раньше прятало час от парсера

Схема HotXLS неверного прочтения истёкшего времени: пять секунд сохранены как крошечная дробь суток в ячейке, отформатированной скобочным токеном ss, который старый парсер читал как цвет и метил как обычное число с двумя знаками, так что precision as displayed округлял длительность в 0.00, пока токен не стали разбирать как секцию истёкшего времени
Форматирование шло по собственному пути, так что ячейка выглядела верно, пока сохраняемое значение округлялось в ноль; скобочная одиночная буква h, m или s — токен истёкшего времени, а не цвет, и секция сохраняет полную точность

Включение precision as displayed в HotXLS из Delphi

Чтобы получить сохраняемые значения, эквивалентные Excel, выставьте флаг перед тем пересчётом, который должен его учесть, затем читайте закэшированные результаты или сохраняйте. На движке XLSX FullPrecision — простой флаг: смена не инвалидирует результаты, которые ранний Recalculate уже сохранил, так что ставьте его сразу после Create или Open и перед первым Recalculate. Пример пользуется формулами, потому что именно там HotXLS применяет округление:

var
  Wb: TXLSXWorkbook;
  Sh: TXLSXWorksheet;
begin
  Wb := TXLSXWorkbook.Create;
  try
    Sh := Wb.Sheets.Add('Totals');
    Sh.Cells[1, 1].Value := 0.1234;
    Sh.Cells[2, 1].Value := 2.5;
    Sh.Cells[3, 1].Value := 12345.678;

    Sh.Cells[1, 2].Formula := '=A1';
    Sh.Cells[1, 2].NumberFormat := '0.0%';   // показывает 12.3%
    Sh.Cells[2, 2].Formula := '=A2';
    Sh.Cells[2, 2].NumberFormat := '0';      // показывает 3
    Sh.Cells[3, 2].Formula := '=A3';
    Sh.Cells[3, 2].NumberFormat := '0.0,';   // показывает 12.3 (тысячи)

    // Должен быть выставлен до первого Recalculate на движке XLSX
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Кэшированные результаты теперь совпадают с Excel 16: 0.123, 3 и 12300.
    // Константы в столбце A хранят полную точность.
    Assert(Abs(Double(Sh.Cells[1, 2].Value) - 0.123) < 1E-12);
    Assert(Double(Sh.Cells[2, 2].Value) = 3);
    Assert(Double(Sh.Cells[3, 2].Value) = 12300);

    Wb.SaveAs('totals.xlsx'); // пишет <calcPr fullPrecision="0"/>
  finally
    Wb.Free;
  end;
end;

Классический движок ведёт себя так же, с одним удобством: присвоение TXLSWorkbook.UseFullPrecision помечает грязной каждую формулу в графе зависимостей, так что следующий Recalculate пересчитает всю книгу по новому правилу. Смена NumberFormat при включённой опции тоже помечает грязной задетую формульную ячейку, потому что формат теперь решает сохраняемое значение. Учтите, что классический Recalculate возвращает число формульных ячеек, которые не смог вычислить, так что ноль значит успех:

var
  Wb: TXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  try
    Sh := Wb.Sheets.Add;
    Sh.Range['A1', 'A1'].Value := -1.2345;
    Sh.Range['B1', 'B1'].Formula := '=A1';
    Sh.Range['B1', 'B1'].NumberFormat := '0.00;(0.0)';
    Sh.Range['C1', 'C1'].Formula := '=A1<0';
    Sh.Range['C1', 'C1'].NumberFormat := '0.00';

    Wb.UseFullPrecision := False; // помечает каждую формулу грязной
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: отрицательная секция "(0.0)" показывает один знак
    // C1 остаётся Boolean True (сборки до v2.384.57 хранили -1)
    Wb.SaveAs('report.xls'); // запись CalcPrecision с fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Оба движка также чтут флаг, пришедший вместе с файлом. Откройте книгу, сохранённую с включённой опцией, — и FullPrecision или UseFullPrecision уже False, так что Recalculate после загрузки округлит в точности как Excel. Если нужно лишь прочитать числа, которые Excel уже сохранил, пересчёт можно пропустить целиком — описано в статье о чтении кэшированных значений формул без пересчёта. О том, как серийные номера дат и форматы дат взаимодействуют с моделью форматов, питающей проверку дата/время, — в статье о серийных датах Excel, системе 1904 и numFmt в Delphi

Когда precision as displayed стоит включать, а когда нет?

Включайте precision as displayed, только когда сохраняемые числа книги обязаны равняться показываемым, и вы готовы навсегда лишиться лишних цифр. Классический легитимный случай — финансовая ведомость, где столбцы округлённых сумм обязаны сходиться в округлённый итог на экране, без скрытых долей цента, дающих итог, разошедшийся на единицу в последнем разряде. Совпадение с существующей книгой клиента, у которой опция уже стоит, — второй добрый повод, а HotXLS хранит флаг при round-trip, так что вы не переключите их молча обратно на полную точность

В большинстве прочих ситуаций избегайте её:

  • Инженерные и научные данные. Округлить измерение потому, что кто-то выбрал формат с двумя знаками для отчёта, — уничтожить информацию, которую не вернёт ни одна смена формата после
  • Проценты с грубыми форматами. Формат 0% держит лишь два знака сохраняемой доли, так что 0.1234 становится 0.12, и каждая формула ниже по течению, читающая ячейку, работает с 0.12
  • Масштабированные показы. Формат 0, или 0.0,, выбранный ради показа тысяч, округляет сохраняемое значение до тысяч или сотен — редко то, чего хотел выбравший формат человек
  • Общие шаблоны. Флаг действует на всю книгу. Всякий, кто позже добавит лист, унаследует поведение — обычно не зная, что оно включено

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

Шпаргалка по precision as displayed

  • Флаг файла: CalcPrecision $000E с fFullPrec = 0 в BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" в XLSX (ECMA-376 Part 1)
  • Переключатели HotXLS: TXLSXWorkbook.FullPrecision := False и TXLSWorkbook.UseFullPrecision := False, оба по умолчанию True
  • Секция: выбирается по знаку вычисленного значения; третья секция только для точного нуля
  • Цифры: заполнители дробной части, плюс два на каждый %, минус три на запятую масштабирования; счёт может быть отрицательным
  • Округление: от нуля на половине с допуском в несколько ulp, так что 2.5 даёт 3, -2.5 даёт -3, а 1.005 даёт 1.01
  • Пропускаются: General, дата/время и истёкшее время, научные, дробные, текстовые, Boolean и ошибочные значения
  • Охват в HotXLS: результаты формул в момент вычисления; константы хранятся как присвоены
  • Движок XLSX: ставьте FullPrecision до первого Recalculate; классический сеттер сам пере-грязнит все формулы
  • Версии: совпадение с Excel 16 в обоих движках с v2.384.57; форматы истёкшего времени защищены с v2.384.9

HotXLS читает, пишет и вычисляет книги XLS и XLSX нативно из Delphi и C++Builder, включая расчётные опции книги, разобранные здесь. Детали, издания и пробная загрузка — на странице HotXLS Delphi spreadsheet component page