LlosLab Software Development BlogИнженерни бележки за разработчици на PDF, Delphi и софтуер за документи.

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

HotXLS precision as displayed: закръглянето на Excel

HotXLS Delphi Component

Excel precision as displayed закръглява всяко записано число до десетичните знаци, които number форматът му показва: секцията на формата, съответстваща на знака на стойността, два допълнителни знака на всеки %, три по-малко на всяка мащабираща запетая за хилядите, закръгляне half away from zero. HotXLS прилага същото правило и в двата си Delphi двигателя, когато TXLSXWorkbook.FullPrecision или TXLSWorkbook.UseFullPrecision е False. Звучи като работа за един ред, докато клиент не докладва, че експортираните ви фактурни суми се разминават с Excel с един стотинк, или че колона продължителности в [ss].00 се сринала до нула. И двете се случиха, и двете водеха до едно объркано правило. От v2.384.57 насам двата двигателя споделят една имплементация, чиито очаквани стойности бяха измерени в Excel 16 с включен Workbook.PrecisionAsDisplayed

Какво реално променя precision as displayed в една работна книга?

Precision as displayed е един-единствен флаг на ниво работна книга, който казва на изчислителния двигател да записва числата така, както изглеждат, а не както са били пресметнати. В 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, където default е true, а fullPrecision="0" включва закръглянето

Флагът не е предпочитание за показване. Когато отметнете кутийката, Excel предупреждава, че данните трайно губят точност, и го мисли сериозно: стойностите се презаписват до показаната си точност, а изрязаните цифри изчезват. Размахването на кутийката по-късно не връща старите цифри. 0.1234, показвано като 12.3%, става 0.123 завинаги

HotXLS чете и записва флага и в двата формата и го излага и в двата двигателя:

  • TXLSXWorkbook.FullPrecision: Boolean в XLSX двигателя, зареждан от и записван в calcPr/@fullPrecision
  • TXLSWorkbook.UseFullPrecision: Boolean в Classic двигателя (също и на IXLSWorkbook), зареждан от и записван в CalcPrecision записа
  • И двете по подразбиране са True — безопасният, неразрушителен режим и Excel default-ът

Къде HotXLS прилага закръглянето има значение. HotXLS закръгля в момента, в който преизчислява стойност: всеки резултат от формула се закръглява до показаната си точност, преди да бъде записан като кеширана стойност на клетката — при Recalculate и при изчисление по поискване. Константите, които задавате през Value, се записват точно както са дадени. Ако изходът ви трябва да възпроизвежда това, което Excel записва след отметването, закръглете тези константи сами, преди да ги запишете — например с помощната функция, показана по-долу

Как Excel решава колко десетични знака да запази?

Excel извежда броя запазени десетични знаци от конкретната секция на формата, която показва стойността, а не от целия format низ. Правилата по-долу са измерени в 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, секциите за дата и час (включително elapsed [h], [mm] и [ss]), научните, дробните и текстовите секции, и секциите без нито едно запазено място за цифра пазят пълната точност
Диаграма на правилата за показана точност в HotXLS: избери секцията на формата по знака на стойността, преброй запазените места за цифри след десетичната точка, добави два знака на всеки знак за процент, отнеми три на всяка мащабираща за хилядите запетая, така че броят може да стане отрицателен, прескачи изцяло General и секциите за дата и час, после закръгля half away from zero
Броят цифри идва от секцията, съответстваща на знака, плюс две на процент и минус три на мащабираща запетая, а отрицателен брой закръглява към десетици или стотици; General и секциите за дата остават непипнати

Смерени срещу Excel 16, това са стойностите, които и двата HotXLS двигателя вече записват за резултат от формула във всеки формат:

Number форматПресметната стойностЗаписаната стойностПриложеното правило
0.0%0.12340.123Един знак плюс две за знака за процент
02.53Half away from zero, не към четно
0-2.5-3Half away from zero и на отрицателната страна
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.01Tolerance за грешката на двоичното представяне
0;-0;0.00.51Не е нула, така че решава положителната секция

Последният ред е хубав капан. Стойността 0.5 се закръглява до цяло число, а секцията за нула изобщо не влиза в игра, защото Excel избира секцията по пресметнатата стойност преди закръглянето. Едно честно ограничение от страната на HotXLS: секциите се избират само по знак, така че формат, чиито секции носят собствени скоби условия като [>=1000], пак се разделя по знак. Сверете такива формати с Excel, ако са важни за вас

Защо 1.005 се закръглява на 1.01, а не на 1.00?

Excel закръглява 1.005 в клетка с 0.00 на 1.01, въпреки че double-ът, най-близък до 1.005, е леко под средата, и HotXLS го възпроизвежда с tolerance от няколко 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 rounding — разумен default за статистика, но тук грешното правило: Excel записва 3 за 2.5 в клетка с 0 и -3 за -2.5. Имплементацията в HotXLS работи върху абсолютната стойност, добавя 0.5 плюс относителен tolerance от 2-51 от мащабираната стойност (няколко ulp при тази величина, никога под два ulp на 1.0), отрязва, връща мащаба и възстановява знака. Функцията по-долу е самостоятелна илюстрация на принципа, не самият библиотечен код, и обработва отрицателните бройки знаци за мащабиращите запетаи по същия начин:

Диаграма на закръглянето в HotXLS: 2.5 се закръглява half away from zero на 3, а -2.5 на -3, докато Delphi System.Round дава banker отговорите 2 и -2, и понеже най-близкият double до 1.005 стои малко под средата, tolerance от няколко ulp превръща floor-базираното 1.00 в Excel отговора 1.01
Excel закръглява равните случаи навън от нулата и прощава грешката на двоичното представяне с малък tolerance; и двата детайла се мерят, и като прескачате някой от тях записвате 2 за 2.5 или 1.00 за 1.005 — на един стотинк от Excel
// Скица на принципа: закръгляване half away from zero до ADigits знака,
// с tolerance от няколко 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); // half away from zero, не 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 знака)

Tolerance-ът е съзнателен компромис. Стойност, която реално е с два ulp под половин стъпка, също се закръглява нагоре, но на това разстояние разликата не се различава от грешка на представянето, а третирането ѝ като половин стъпка е това, което кара написаните на ръка десетични числа да се държат така, както потребителите очакват

Какво грешеше преди v2.384.57?

Преди v2.384.57 XLSX двигателят и Classic двигателят всеки имаха свой код за precision as displayed, и всеки грешеше по различен начин. Ако произвеждате работни книги с включена опцията, това са симптомите, по които да ги търсите във файлове от по-стари версии

XLSX двигатель: само първа секция, без проценти, banker rounding

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

Classic двигатель: TRUE ставаше -1

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

Elapsed-time форматите се четяха като цветове (v2.384.9)

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

Диаграма на погрешния разбор на elapsed време в HotXLS: пет секунди, съхранени като дребна дневна фракция в клетка, форматирана със скобувания ss токен, който старият парсер четеше като цвят и маркираше като обикновено число с два знака, така че precision as displayed закръгляваше продължителността до 0.00, докато не започне да се парсва като elapsed time секция
Форматирането вървеше по собствен път, така че клетката изглеждаше правилно, докато записаната стойност се закръгляваше до нула; скобувана единична буква h, m или s е elapsed time токен, не цвят, а секцията пази пълната точност

Включване на 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;

Classic двигателят се държи по същия начин, с едно удобство: задаването на TXLSWorkbook.UseFullPrecision маркира всяка формула в графа на зависимостите като dirty, така че следващият Recalculate преизчислява цялата книга под новото правило. Смяната на 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;

И двата двигателя уважават и флага, който идва с файла. Отворете книга, записана с включена опция, и FullPrecision или UseFullPrecision вече е False, така че Recalculate след зареждането закръглява точно както би направил Excel. Ако трябва само да прочетете числата, които Excel вече е записал, можете да прескочите преизчислението изцяло — както е описано в четенето на кеширани стойности на формули без преизчисление. За взаимодействието на серийните числа и date форматите с форматния модел, който движи проверката дата/час, вижте Excel date serial, 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
  • Секция: избира се по знака на пресметнатата стойност; третата секция само за точно нула
  • Знаци: десетични запазени места, плюс две на %, минус три на мащабираща запетая; броят може да е отрицателен
  • Закръгляне: half away from zero с tolerance от няколко ulp, така че 2.5 дава 3, -2.5 дава -3 и 1.005 дава 1.01
  • Прескачат се: General, дата/час и elapsed време, научни, дробни, текстови, Boolean и грешкови стойности
  • Обхват в HotXLS: резултати от формули по време на изчислението; константите се записват както са зададени
  • XLSX двигатель: задайте FullPrecision преди първия Recalculate; Classic setter-ът сам презарежда всички формули като dirty
  • Версии: съвпадение с Excel 16 и в двата двигателя от v2.384.57 насам; elapsed-time форматите са защитени от v2.384.9 насам

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