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

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

Що precision as displayed насправді змінює в workbook?

Precision as displayed — один workbook-рівневий прапорець, який каже calculation engine зберігати числа так, як вони виглядають, а не так, як були пораховані. В Excel UI він сидить під 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 читає і пише прапорець в обох форматах і відкриває його в обох engines:

• TXLSXWorkbook.FullPrecision: Boolean на XLSX engine, завантажується з і зберігається в calcPr/@fullPrecision
• TXLSWorkbook.UseFullPrecision: Boolean на Classic engine (також на IXLSWorkbook), завантажується з і зберігається в записі CalcPrecision
• Обидва усталено True — це безпечний, неруйнівний режим і Excel-івська усталена

Де HotXLS застосовує заокруглення — має значення. HotXLS заокруглює в точці, де обчислює значення: кожен результат формули заокруглюється до своєї показаної точності, перш ніж зберегтися як кешоване значення клітинки, — під час Recalculate і під час обчислення на вимогу. Константи, які ви присвоюєте через Value, зберігаються точно як задані. Якщо ваш вивід мусить відтворювати те, що Excel зберігає після встановлення галочки, заокругліть ті константи самі, перед записом, — наприклад, хелпером, показаним далі

Як Excel вирішує, скільки десяткових зберегти?

Excel виводить кількість збережених десяткових із конкретної секції формату, яка показує значення, а не з рядка формату як цілого. Правила нижче виміряні в Excel 16, і саме їх імплементує XlsApplyDisplayedPrecision у lxNumFormat для обох engines 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, дати й часу (включно з elapsed [h], [mm] і [ss]), scientific, fraction і текстові секції, а також секції без жодного цифрового заповнювача тримають повну точність

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

Number format	Обчислене значення	Збережене значення	Правило, що застосовується
0.0%	0.1234	0.123	Один десятковий плюс два за знак процента
0	2.5	3	Половина від нуля, не до парного
0	-2.5	-3	Половина від нуля і на від’ємному боці
0.00;(0.0)	-1.2345	-1.2	Від’ємна секція показує один десятковий
0.00;(0.0)	1.2345	1.23	Додатна секція показує два десяткові
#,##0.0	1234.5678	1234.6	Кома групування, без масштабування
0.0,	12345.678	12300	Один десятковий мінус три: до сотень
0.0%;(0.00%)	-0.0125	-0.0125	Від’ємна секція тримає два плюс два десяткові
0.00	1.005	1.01	Толерантність до помилки двійкового представлення
0;-0;0.0	0.5	1	Не нуль, тож вирішує додатна секція

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

Чому 1.005 заокруглюється до 1.01, а не до 1.00?

Excel заокруглює 1.005 у клітинці 0.00 до 1.01, хоч double, найближчий до 1.005, сидить трохи нижче півточки, і HotXLS збігається з цим через толерантність у кілька ulp. Літерал 1.005 неможливо представити в двійковому floating point. Найближчий 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. Це banker’s rounding, розумна усталена для статистики і неправильне правило тут: Excel зберігає 3 для 2.5 у клітинці 0 і -3 для -2.5. Імплементація HotXLS працює над абсолютним значенням, додає 0.5 плюс відносну толерантність 2^-51 масштабованого значення (кілька ulp на тій величині, ніколи не менше двох ulp від 1.0), відсікає, масштабує назад і відновлює знак. Наступна функція — самодостатня ілюстрація того принципу, а не код бібліотеки, і вона опрацьовує від’ємні рахунки цифр для масштабувальних ком так само:

// Принципова замальовка: заокруглення половини від нуля до 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 engine і Classic engine мали кожен власний precision-as-displayed код, і кожен помилявся по-своєму. Якщо ви продукуєте workbooks з увімкненою опцією, ось симптоми, які шукати у файлах, згенерованих старішими збірками

XLSX engine: лише перша секція, без процентів, banker’s rounding

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

Classic engine: TRUE ставав -1

Classic engine охороняв своє заокруглення через VarIsNumeric, а VarIsNumeric повертає True для Variant з varBoolean. Конвертація того Variant через Double(V) дає -1, бо COM-style Boolean True зберігається як -1. Формула на кшталт =A1>0 у клітинці, відформатованій 0.00, виходила з перерахунку числом -1. Від v2.384.57 Boolean результати виключаються перед будь-яким числовим тестом, і логічний результат лишається логічним в обох engines

Elapsed-time формати читалися як кольори (v2.384.9)

Третій bug сидів у моделі number format, а не в заокругленні. Parser класифікував кожен дужковий токен, що не був умовою, як колір, тож [h], [mm] і [ss] ніколи не мітили свою секцію як date/time. Відображення не страждало, бо форматування йде окремим шляхом, але precision as displayed покладається на той прапорець, щоб пропускати часові значення. П’ятисекундна тривалість — це 5/86400 доби, близько 0.0000579, і формат на кшталт [ss].00 виглядав як звичайне число з двома десятковими, тож із вимкненим FullPrecision тривалість заокруглювалася до 0.00 доби. Від v2.384.9 дужковий запуск однієї літери h, m чи s парситься як elapsed-time токен, а секція трактується як date/time. Той самий реліз виправив детект хвилин у h:mm, де двокрапка між токенами ховала годину від parser

Увімкнення precision as displayed у HotXLS з Delphi

Щоб отримати еквівалентні Excel збережені значення, поставте прапорець перед тим перерахунком, який має його шанувати, потім прочитайте кешовані результати чи збережіть файл. На XLSX engine 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 engine
    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;

Classic engine поводиться так само, з однією зручністю: присвоєння TXLSWorkbook.UseFullPrecision мітає dirty кожну формулу в графі залежностей, тож наступний Recalculate переоцінює весь workbook за новим правилом. Зміна NumberFormat, коли опція увімкнена, теж мітить dirty уражені формульні клітинки, бо формат тепер вирішує збережене значення. Зауважте, що Classic 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; // мітить dirty кожну формулу
    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;

Обидва engines також шанують прапорець, який приходить із файлом. Відкрийте workbook, збережений з увімкненою опцією, — і FullPrecision чи UseFullPrecision вже False, тож Recalculate після завантаження заокруглює рівно так, як би Excel. Якщо вам треба лише прочитати числа, які Excel уже зберіг, можна оминути перерахунок повністю, як описано в читанні кешованих значень формул без перерахунку. Про те, як серійні номери та формати дати взаємодіють з моделлю форматів, що рухає перевірку date/time, — у Excel date serials, системі 1904 та numFmt у Delphi

Коли вмикати precision as displayed, а коли ні?

Увімкніть precision as displayed лише коли збережені числа workbook мусять дорівнювати його показаним числам, і ви приймаєте втрату зайвих цифр назавжди. Класичний легітимний випадок — фінансовий розклад, де стовпці заокруглених сум мусять складатися в заокруглений підсумок на екрані, без прихованих часток цента, що дають підсумок, збіжний на одиницю в останньому розряді. Зрівняння з наявним workbook клієнта, де опція вже стоїть, — інша добра причина, і HotXLS зберігає прапорець у round-trip, тож ви не перемкнете їх тихо назад на повну точність

Оминайте його в більшості інших ситуацій:

• Інженерні та наукові дані. Заокруглення вимірювання через те, що хтось обрав дводесятковий формат для звіту, нищить інформацію, яку жодна пізніша зміна формату не відновить
• Відсотки з грубими форматами. Формат 0% тримає лише два десяткові збереженого співвідношення, тож 0.1234 стає 0.12, і кожна формула нижче за течією, що читає клітинку, працює з 0.12
• Масштабовані покази. Формат 0, чи 0.0,, узятий для показу тисяч, заокруглює збережене значення до тисяч чи сотень, що рідко входило в наміри того, хто обрав формат
• Спільні шаблони. Прапорець — на весь workbook. Будь-хто, хто пізніше додасть sheet, успадкує поведінку, зазвичай не знаючи, що вона увімкнена

Якщо вам насправді треба заокруглених результатів у кількох конкретних клітинках, напишіть натомість ROUND у ті формули. ROUND — явний, локальний для клітинки, видимий кожному, хто читає формулу, і обчислюється formula engine HotXLS як будь-яка інша функція, без workbook-широких побічних ефектів

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, date/time та elapsed time, scientific, fraction, текст, Boolean та error значення
• Охоплення в HotXLS: результати формул у момент обчислення; константи зберігаються як присвоєні
• XLSX engine: ставте FullPrecision перед першим Recalculate; Classic setter сам знову мітить усі формули dirty
• Версії: зрівняно з Excel 16 в обох engines від v2.384.57; elapsed-time формати захищені від v2.384.9

HotXLS читає, пише й обчислює XLS та XLSX workbooks нативно з Delphi і C++Builder, включно з опціями обчислення workbook, покритими тут. Деталі, видання та пробне завантаження — на сторінці HotXLS Delphi spreadsheet component page