LlosLab Software Development BlogTekniske noter til PDF-, Delphi- og dokumentsoftwareudviklere.

Teknisk artikel

HotXLS Precision as Displayed: Excels afrundingsregler

HotXLS Delphi Component

Excel precision as displayed afrunder hvert gemt tal til de decimaler, dets talformat viser: den formatsektion, der matcher værdiens fortegn, to ekstra decimaler pr. %, tre færre pr. tusinde-skaleringskomma, afrundet half away from zero. HotXLS anvender samme regel i begge sine Delphi-motorer, når TXLSXWorkbook.FullPrecision eller TXLSWorkbook.UseFullPrecision er False. Det lyder som en one-liner, indtil en kunde rapporterer, at dine eksporterede fakturatotaler afviger fra Excel med en cent, eller at en kolonne af varigheder i [ss].00 kollapsede til nul. Begge ting skete, og begge sporede tilbage til en af reglerne taget forkert. Siden v2.384.57 deler de to motorer én implementering, hvis forventede værdier blev målt i Excel 16 med Workbook.PrecisionAsDisplayed slået til

Hvad ændrer precision as displayed egentlig i en workbook?

Precision as displayed er ét workbook-niveau-flag, der beder beregningsmotoren om at gemme tal, som de ser ud, ikke som de blev beregnet. I Excel UI ligger den under File, Options, Advanced, "When calculating this workbook", som "Set precision as displayed". På disken er det én bit. En BIFF8-fil bærer den i CalcPrecision-recorden ($000E, [MS-XLS] §2.4.35), hvis fFullPrec-felt er 1 for normal fuld præcision og 0, når optionen er slået til. En XLSX-pakke bærer den som attributten fullPrecision på calcPr-elementet i workbook.xml, defineret i ECMA-376 Part 1, hvor default er true, og fullPrecision="0" tænder for afrunding

Flaget er ikke en visningsindstilling. Når du sætter fluebenet, advarer Excel om, at data permanent mister nøjagtighed, og det mener den: værdier omskrives til deres viste præcision, og de cifre, der blev skåret af, er væk. At fjerne fluebenet senere bringer ikke de gamle cifre tilbage. En 0.1234 vist som 12.3% bliver til 0.123 for altid

HotXLS læser og skriver flaget i begge formater og eksponerer det i begge motorer:

  • TXLSXWorkbook.FullPrecision: Boolean på XLSX-motoren, indlæst fra og gemt til calcPr/@fullPrecision
  • TXLSWorkbook.UseFullPrecision: Boolean på Classic-motoren (også på IXLSWorkbook), indlæst fra og gemt til CalcPrecision-recorden
  • Begge default'er til True, som er den sikre, ikke-destruktive tilstand og Excels default

Hvor HotXLS anvender afrunding, tæller. HotXLS afrunder på det punkt, hvor den beregner en værdi: hvert formelresultat afrundes til sin viste præcision, før det gemmes som cellens cachede værdi, både under Recalculate og ved on-demand-evaluering. Konstanter, du tildeler gennem Value, gemmes præcis som givet. Skal dit output gengive, hvad Excel gemmer efter fluebenet er sat, så afrund selv disse konstanter, før du skriver dem, for eksempel med hjælpefunktionen vist senere

Hvordan beslutter Excel, hvor mange decimaler der skal beholdes?

Excel udleder antallet af beholdte decimaler fra den specifikke formatsektion, der viser værdien, ikke fra formatstrengen som helhed. Reglerne nedenfor blev målt i Excel 16 og er det, XlsApplyDisplayedPrecision i lxNumFormat implementerer for begge HotXLS-motorer

  1. Vælg sektionen efter fortegn. Et to-sektions-format bruger den anden sektion til negative værdier. Et format med tre eller flere sektioner bruger den anden til negative værdier og den tredje til præcis nul. Alt andet bruger den første sektion
  2. Tæl decimalpladsholderne. Hver 0, # eller ? efter decimalpunktet i den sektion tilføjer én beholdt decimal
  3. Tilføj to pr. procenttegn. 0.0% viser 0.1234 som 12.3%, så den gemte værdi er en hundrededel af, hvad du ser, og beholder tre decimaler, ikke én
  4. Træk tre fra pr. skaleringskomma. Et komma efter den sidste heltalspladsholder (0,, 0.0,, 0,.0) deler visningen med 1000. 0.0, viser 12345.678 som 12.3, så Excel beholder én decimal minus tre, hvilket er en negativ optælling: værdien afrundes til hundrederne og gemmes som 12300. Et komma mellem heltalspladsholdere, som i #,##0, er ren ciffergruppering og ændrer ingenting
  5. Rør ikke ikke-numeriske sektioner. General-, dato- og tidssektioner (inklusive elapsed [h], [mm] og [ss]), videnskabelige, brøk- og tekstsektioner samt sektioner uden nogen cifferpladsholder beholder fuld præcision
HotXLS-diagram over reglerne for vist præcision: vælg formatsektionen efter værdiens fortegn, tæl cifferpladsholderne efter decimalpunktet, tilføj to decimaler pr. procenttegn, træk tre fra pr. tusinde-skaleringskomma, så optællingen kan blive negativ, spring General- og dato-tidssektioner helt over, og afrund derefter half away from zero
Cifferoptællingen kommer fra sektionen, der matcher fortegnet, plus to pr. procent og minus tre pr. skaleringskomma, og en negativ optælling afrunder til tiereder eller hundreder; General- og datesektioner får lov at være

Målt mod Excel 16 er disse de værdier, begge HotXLS-motorer nu gemmer for et formelresultat i hvert format:

TalformatBeregnet værdiGemt værdiRegel, der gælder
0.0%0.12340.123Én decimal plus to for procenttegnet
02.53Half away from zero, ikke til lige
0-2.5-3Half away from zero på den negative side også
0.00;(0.0)-1.2345-1.2Negativ sektion viser én decimal
0.00;(0.0)1.23451.23Positiv sektion viser to decimaler
#,##0.01234.56781234.6Grupperingskomma, ingen skalering
0.0,12345.67812300Én decimal minus tre: afrund til hundreder
0.0%;(0.00%)-0.0125-0.0125Negativ sektion beholder to plus to decimaler
0.001.0051.01Tolerance for binær repræsentationsfejl
0;-0;0.00.51Ikke nul, så den positive sektion afgør

Den sidste række er en fin fælde. Værdien 0.5 afrunder til et helt tal, og nulsektionen kommer aldrig i spil, fordi Excel vælger sektionen ud fra den beregnede værdi før afrunding. Én ærlig begrænsning på HotXLS-siden: sektioner vælges kun efter fortegn, så et format, hvis sektioner bærer tilpassede klammebetingelser som [>=1000], deles stadig efter fortegn. Tjek sådanne formater mod Excel, hvis de betyder noget for dig

Hvorfor afrundes 1.005 til 1.01 og ikke til 1.00?

Excel afrunder 1.005 i en 0.00-celle til 1.01, selv om den double, der ligger nærmest 1.005, ligger en anelse under halvvejspunktet, og HotXLS matcher det med en tolerance på et par ulp. Literalen 1.005 kan ikke repræsenteres i binær flydende punkt. Den nærmeste IEEE 754 double er 1.00499999999999989341858963598497211933135986328125, og gang med 100 giver 100.49999999999999. En lærebogs-Floor(x * 100 + 0.5) / 100 returnerer derfor 1.00, hvilket afviger fra det tal, brugeren tastede, fra det, Excel viser, og fra det, Excel gemmer

Delphi tilføjer sin egen drejning. System.Round afrunder lige til lige, så Round(2.5) er 2 og Round(3.5) er 4. Det er bankers afrunding, en fornuftig default til statistik og den forkerte regel her: Excel gemmer 3 for 2.5 i en 0-celle og -3 for -2.5. HotXLS-implementeringen arbejder på absolutværdien, lægger 0.5 plus en relativ tolerance på 2-51 gange den skalerede værdi til (et par ulp ved den størrelse, aldrig mindre end to ulp af 1.0), trunkerer, skalerer tilbage og gendanner fortegnet. Følgende funktion er en selvstændig illustration af princippet, ikke selve bibliotekskoden, og den håndterer negative cifferantaler for skaleringskommaer på samme måde:

HotXLS-afrundingsdiagram: 2.5 afrundes half away from zero til 3 og -2.5 til -3, hvor Delphi System.Round giver bankersvarene 2 og -2, og da den double, der ligger nærmest 1.005, ligger lige under halvvejspunktet, er toleranceen på et par ulp det, der forvandler et floor-baseret 1.00 til Excels svar 1.01
Excel afrunder lige væk fra nul og tilgiver binær repræsentationsfejl med en lille tolerance; begge detaljer kan måles, og springer man en af dem over, gemmes 2 for 2.5 eller 1.00 for 1.005, én cent fra Excel
// Principskitse: afrund half away from zero til ADigits decimaler,
// med en tolerance på et par ulp, så 1.005 når 1.01.
// ADigits < 0 afrunder til tiered, hundreder, ... ("0.0," giver -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, to ulp af 1.0
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // ud over double-præcision: lad værdien være
  Scale := 1;
  for I := 1 to Abs(ADigits) do
    Scale := Scale * 10;
  if ADigits >= 0 then
  begin
    if Abs(AValue) > 1E300 / Scale then
      Exit; // skalering ville overflowe
    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, ikke 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-baseret: 1.00)
// RoundAsDisplayed(2.5, 0)        = 3      (Round: 2)
// RoundAsDisplayed(-2.5, 0)       = -3
// RoundAsDisplayed(0.1234, 3)     = 0.123  ("0.0%": 1 + 2 cifre)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3 cifre)

Tolerancen er en bevidst afvejning. En værdi, der reelt ligger to ulp under et halvt trin, afrundes også opad, men på den afstand er forskellen uadskillelig fra repræsentationsfejl, og at behandle den som et halvt trin er det, der får tastede decimaler til at opføre sig, som brugerne forventer

Hvad gik galt før v2.384.57?

Før v2.384.57 havde XLSX-motoren og Classic-motoren hver deres egen precision-as-displayed-kode, og hver tog fejl på hver sin måde. Producerer du workbooks med optionen slået til, er disse symptomerne, du skal lede efter i filer genereret af ældre builds

XLSX-motor: kun første sektion, ingen procent, bankers afrunding

Den gamle XLSX-vej bad om decimalantallet for formatstrengen som helhed, hvilket kun kiggede på den første sektion og ignorerede %, og afrundede derefter med Round. En 0.1234 i 0.0% blev gemt som 0.1, altså 10% i stedet for de 12.3% på skærmen. En 2.5 i 0 blev gemt som 2 i stedet for 3. Negative værdier i et format som 0.00;(0.0) blev afrundet til den positive sektions to decimaler. Siden v2.384.57 kalder XLSX-motoren den samme delte rutine som Classic-motoren, som også fik understøttelse af skaleringskomma i den udgivelse

Classic-motor: TRUE blev til -1

Classic-motoren vogtede sin afrunding med VarIsNumeric, og VarIsNumeric returnerer True for en varBoolean-Variant. Konverteres den Variant med Double(V), kommer der -1 ud, fordi en COM-agtig Boolean True gemmes som -1. En formel som =A1>0 i en celle formatteret 0.00 kom derfor ud af genberegningen som tallet -1. Siden v2.384.57 udelukkes Boolean-resultater før enhver numerisk test, og et logisk resultat forbliver et logisk resultat i begge motorer

Elapsed-time-formater læst som farver (v2.384.9)

Den tredje bug sad i talformat-modellen frem for i afrunding. Parseren klassificerede hver klamret token, der ikke var en betingelse, som en farve, så [h], [mm] og [ss] markerede aldrig deres sektion som dato/tid. Visningen var upåvirket, fordi formatering kører på en separat vej, men precision as displayed er afhængig af det flag for at springe tidsværdier over. En varighed på fem sekunder er 5/86400 af en dag, omkring 0.0000579, og et format som [ss].00 så ud som et almindelig tocifret tal, så med FullPrecision slået fra blev varigheden afrundet til 0.00 dage. Siden v2.384.9 parses en klamret sekvens af ét h-, m- eller s-bogstav som en elapsed-time-token, og sektionen behandles som dato/tid. Samme udgivelse rettede minutdetektion i h:mm, hvor kolon mellem tokens plejede at skjule timen for parseren

HotXLS-diagram over en elapsed time-mislæsning: fem sekunder gemt som en lille dagsbrøk i en celle formatteret med den klamrede ss-token, som den gamle parser læste som en farve og flaggede som et almindelig tocifret tal, så precision as displayed afrundede varigheden til 0.00, indtil den blev parset som en elapsed time-sektion
Formatering kørte på sin egen vej, så cellen så rigtig ud, mens den gemte værdi afrundedes til nul; en klamret enkelt bogstav h, m eller s er en elapsed time-token, ikke en farve, og sektionen beholder fuld præcision

Slå precision as displayed til i HotXLS fra Delphi

For at få Excel-ækvivalente gemte værdier, sæt flaget før den genberegning, der skal ære det, og læs derefter de cachede resultater, eller gem. På XLSX-motoren er FullPrecision et almindeligt flag: at ændre det invaliderer ikke resultater, en tidligere Recalculate allerede har gemt, så sæt det lige efter Create eller Open og før den første Recalculate. Eksemplet bruger formler, for det er dér, HotXLS anvender afrunding:

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%';   // viser 12.3%
    Sh.Cells[2, 2].Formula := '=A2';
    Sh.Cells[2, 2].NumberFormat := '0';      // viser 3
    Sh.Cells[3, 2].Formula := '=A3';
    Sh.Cells[3, 2].NumberFormat := '0.0,';   // viser 12.3 (tusinder)

    // Skal sættes før den første Recalculate i XLSX-motoren
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Cachede resultater matcher nu Excel 16: 0.123, 3 og 12300.
    // Konstanterne i kolonne A beholder deres fulde præcision.
    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'); // skriver <calcPr fullPrecision="0"/>
  finally
    Wb.Free;
  end;
end;

Classic-motoren opfører sig ens, med én bekvemmelighed: at tildele TXLSWorkbook.UseFullPrecision markerer hver formel i afhængighedsgrafen som dirty, så næste Recalculate gen-evaluerer hele workbooken under den nye regel. At ændre en NumberFormat, mens optionen er slået til, markerer også de berørte formulaceller som dirty, fordi formatet nu afgør den gemte værdi. Bemærk, at Classic-Recalculate returnerer antallet af formulaceller, den ikke kunne evaluere, så nul betyder succes:

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; // markerer hver formel som dirty
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: den negative sektion "(0.0)" viser én decimal
    // C1 forbliver Boolean True (builds før v2.384.57 gemte -1)
    Wb.SaveAs('report.xls'); // CalcPrecision-record med fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Begge motorer ærer også flaget, der kommer ind med en fil. Åbn en workbook gemt med optionen slået til, og FullPrecision eller UseFullPrecision er allerede False, så en Recalculate efter indlæsning afrunder præcis, som Excel ville. Behøver du kun at læse de tal, Excel allerede har gemt, kan du springe genberegningen helt over, som beskrevet i læsning af cachede formelværdier uden genberegning. For hvordan serienumre og datoformater interagerer med formatmodellen, der driver dato/tid-tjekket, se Excel-datoserienumre, 1904-systemet og numFmt i Delphi

Hvornår bør du slå precision as displayed til, og hvornår ikke?

Slå precision as displayed til, kun når workbookens gemte tal skal være lig dens viste tal, og du accepterer at miste de ekstra cifre for altid. Det klassiske legitime tilfælde er en finansiel skema, hvor kolonner af afrundede beløb skal lægge sammen til den afrundede total på skærmen, uden skjulte brøkdele af en cent, der producerer en total, der afviger med én på sidste plads. At matche en kundes eksisterende workbook, der allerede har optionen sat, er den anden gode grund, og HotXLS bevarer flaget ved round-trip, så du ikke lydløst skifter dem tilbage til fuld præcision

Undgå det i de fleste andre situationer:

  • Ingeniør- og videnskabelige data. At afrunde en måling, fordi nogen valgte et to-decimalers format til en rapport, ødelægger information, som ingen senere formatændring kan genskabe
  • Procenter med grove formater. Et 0%-format beholder kun to decimaler af den gemte ratio, så 0.1234 bliver til 0.12, og hver formel nedstrøms, der læser cellen, arbejder med 0.12
  • Skalerede visninger. Et 0, eller 0.0,-format brugt til at vise tusinder afrunder den gemte værdi til tusinder eller hundreder, hvilket sjældent er, hvad personen, der valgte formatet, mente
  • Delte skabeloner. Flaget gælder hele workbooken. Alle, der senere tilføjer et ark, arver opførslen, som regel uden at vide, at den er slået til

Er det, du reelt ønsker, afrundede resultater i et par specifikke celler, så skriv ROUND ind i de formler i stedet. ROUND er eksplicit, lokal for cellen, synlig for enhver, der læser formlen, og evalueres af HotXLS formelmotor som enhver anden funktion, uden workbook-bredsideeffekter

Precision as displayed hurtig reference

  • Filflag: CalcPrecision $000E med fFullPrec = 0 i BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" i XLSX (ECMA-376 Part 1)
  • HotXLS-switches: TXLSXWorkbook.FullPrecision := False og TXLSWorkbook.UseFullPrecision := False, begge default True
  • Sektion: valgt efter den beregnede værdis fortegn; tredje sektion kun til præcis nul
  • Cifre: decimalpladsholdere, plus to pr. %, minus tre pr. skaleringskomma; optællingen kan være negativ
  • Afrunding: half away from zero med en tolerance på et par ulp, så 2.5 giver 3, -2.5 giver -3 og 1.005 giver 1.01
  • Springes over: General, dato/tid og elapsed time, videnskabelige, brøker, tekst, Boolean og fejlværdier
  • Omfang i HotXLS: formelresultater, efterhånden som de beregnes; konstanter gemmes som tildelt
  • XLSX-motor: sæt FullPrecision før den første Recalculate; Classic-sætteren re-dirty'er selv alle formler
  • Versioner: matcher Excel 16 i begge motorer siden v2.384.57; elapsed-time-formater beskyttet siden v2.384.9

HotXLS læser, skriver og beregner XLS- og XLSX-workbooks nativt fra Delphi og C++Builder, inklusive de beregningsoptioner for workbooks, der er dækket her. Detaljer, udgaver og trial-download står på siden om HotXLS Delphi spreadsheet-komponenten