LlosLab Software Development BlogEngineering notes for PDF, Delphi, and document software developers

Teknisk artikkel

HotXLS Precision as Displayed: Excels avrundingsregler

HotXLS Delphi Component

Excel precision as displayed avrunder hvert lagret tall til desimalene tallformatet viser: formatseksjonen som matcher verdiens fortegn, to ekstra desimaler per %, tre færre per tusen-skalakomma, avrunding halv bort fra null. HotXLS bruker samme regel i begge Delphi-motorene når TXLSXWorkbook.FullPrecision eller TXLSWorkbook.UseFullPrecision er False. Det høres ut som en one-liner helt til en kunde rapporterer at de eksporterte fakturasummene dine avviker fra Excel med én øre, eller at en kolonne med varigheter i [ss].00 kollapset til null. Begge skjedde, og begge spores tilbake til å få én av disse reglene feil. Siden v2.384.57 deler de to motorene én enkelt implementasjon hvis forventede verdier ble målt i Excel 16 med Workbook.PrecisionAsDisplayed slått på

Hva endrer precision as displayed egentlig i en arbeidsbok?

Precision as displayed er ett enkelt flagg på arbeidsboknivå som ber beregningsmotoren lagre tall slik de ser ut, ikke slik de ble beregnet. I Excel-grensesnittet ligger den under File, Options, Advanced, «When calculating this workbook», som «Set precision as displayed». På disk er det én bit. En BIFF8-fil bærer den i CalcPrecision-posten ($000E, [MS-XLS] §2.4.35), hvis fFullPrec-felt er 1 for normal full presisjon og 0 når alternativet er på. En XLSX-pakke bærer den som fullPrecision-attributten til calcPr-elementet i workbook.xml, definert i ECMA-376 Part 1, der standarden er true og fullPrecision="0" slår på avrundingen

Flagget er ikke en visningsinnstilling. Når du haker av boksen, advarer Excel om at data vil miste nøyaktighet permanent, og det mener det: verdier omskrives til sin viste presisjon, og sifrene som ble kuttet, er borte. Å fjerne haken senere bringer ikke de gamle sifrene tilbake. En 0.1234 vist som 12.3% blir 0.123 for godt

HotXLS leser og skriver flagget i begge formater og eksponerer det i begge motorer:

  • TXLSXWorkbook.FullPrecision: Boolean på XLSX-motoren, lastet fra og lagret til calcPr/@fullPrecision
  • TXLSWorkbook.UseFullPrecision: Boolean på Classic-motoren (også på IXLSWorkbook), lastet fra og lagret til CalcPrecision-posten
  • Begge har True som standard, som er den trygge, ikke-destruktive modusen og Excels standard

Hvor HotXLS bruker avrundingen, betyr noe. HotXLS avrunder der den beregner en verdi: hvert formelresultat avrundes til sin viste presisjon før det lagres som cellens bufrede verdi, under Recalculate og under evaluering ved behov. Konstanter du tildeler gjennom Value, lagres nøyaktig slik de gis. Skal utdataene dine gjenskape det Excel lagrer etter at boksen er haket av, avrunder du de konstantene selv før du skriver dem, for eksempel med hjelperen vist senere

Hvordan avgjør Excel hvor mange desimaler som skal beholdes?

Excel utleder antallet beholdte desimaler fra den spesifikke formatseksjonen som viser verdien, ikke fra formatstrengen som helhet. Reglene under ble målt i Excel 16 og er det XlsApplyDisplayedPrecision i lxNumFormat implementerer for begge HotXLS-motorene

  1. Velg seksjonen etter fortegn. Et toseksjonsformat bruker den andre seksjonen for negative verdier. Et format med tre eller flere seksjoner bruker den andre for negative verdier og den tredje for nøyaktig null. Alt annet bruker den første seksjonen
  2. Tell desimalplassholdere. Hver 0, # eller ? etter desimaltegnet i den seksjonen legger til én beholdt desimal
  3. Legg til to per prosenttegn. 0.0% viser 0.1234 som 12.3%, så den lagrede verdien er en hundredel av det du ser og beholder tre desimaler, ikke én
  4. Trekk fra tre per skalakomma. Et komma etter siste heltallsplassholder (0,, 0.0,, 0,.0) deler visningen på 1000. 0.0, viser 12345.678 som 12.3, så Excel beholder én desimal minus tre, noe som er et negativt antall: verdien avrundes til hundrene og lagres som 12300. Et komma mellom heltallsplassholdere, som i #,##0, er ren siffergruppering og endrer ingenting
  5. La ikke-numeriske seksjoner være i fred. General-, dato- og tidsseksjoner (inkludert elapsed [h], [mm] og [ss]), vitenskapelige, brøk- og tekstseksjoner, og seksjoner uten noen sifferplassholder beholder full presisjon
HotXLS-diagram av reglene for vist presisjon: velg formatseksjonen etter verdiens fortegn, tell sifferplassholderne etter desimaltegnet, legg til to desimaler per prosenttegn, trekk fra tre per tusen-skalakomma slik at antallet kan bli negativt, hopp over General og dato-tid-seksjoner fullstendig, og avrunder så halv bort fra null
Sifferantallet kommer fra seksjonen som matcher fortegnet, pluss to per prosenttegn og minus tre per skalakomma, og et negativt antall avrunder til tiere eller hundrene; General- og datoseksjoner blir igjen i fred

Målt mot Excel 16 er dette verdiene begge HotXLS-motorene nå lagrer for et formelresultat i hvert format:

TallformatBeregnet verdiLagret verdiRegel som gjelder
0.0%0.12340.123Én desimal pluss to for prosenttegnet
02.53Halv bort fra null, ikke til partall
0-2.5-3Halv bort fra null også på den negative siden
0.00;(0.0)-1.2345-1.2Negativ seksjon viser én desimal
0.00;(0.0)1.23451.23Positiv seksjon viser to desimaler
#,##0.01234.56781234.6Grupperingskomma, ingen skalering
0.0,12345.67812300Én desimal minus tre: avrunder til hundrene
0.0%;(0.00%)-0.0125-0.0125Negativ seksjon beholder to pluss to desimaler
0.001.0051.01Toleranse for binær representasjonsfeil
0;-0;0.00.51Ikke null, så den positive seksjonen avgjør

Siste rad er en fin felle. Verdien 0.5 avrundes til et heltall, og nullseksjonen kommer aldri til syne, fordi Excel velger seksjon ut fra den beregnede verdien før avrundingen. Én ærlig begrensning på HotXLS-siden: seksjoner velges etter fortegn alene, så et format hvis seksjoner bærer egendefinerte klammebetingelser som [>=1000] splittes fortsatt etter fortegn. Sjekk slike formater mot Excel hvis de betyr noe for deg

Hvorfor avrundes 1.005 til 1.01 og ikke til 1.00?

Excel avrunder 1.005 i en 0.00-celle til 1.01 selv om dobbelen nærmest 1.005 ligger litt under halvveispunktet, og HotXLS matcher det med en få-ulps toleranse. Literalen 1.005 kan ikke representeres i binært flyttall. Den nærmeste IEEE 754 dobbelen er 1.00499999999999989341858963598497211933135986328125, og multiplisert med 100 gir det 100.49999999999999. En lærebok Floor(x * 100 + 0.5) / 100 returnerer derfor 1.00, som uenig med tallet brukeren tastet, med det Excel viser, og med det Excel lagrer

Delphi legger til sitt eget tvist. System.Round avrunder likheter til partall, så Round(2.5) er 2 og Round(3.5) er 4. Det er bankers avrunding, et fornuftig standardvalg for statistikk og feil regel her: Excel lagrer 3 for 2.5 i en 0-celle og -3 for -2.5. HotXLS-implementasjonen jobber på absoluttverdien, legger til 0.5 pluss en relativ toleranse på 2-51 ganger den skalerte verdien (noen få ulps på den størrelsen, aldri mindre enn to ulps av 1.0), trunkerer, skalerer tilbake og gjenoppretter fortegnet. Funksjonen under er en selvstendig illustrasjon av det prinsippet, ikke bibliotekskoden selv, og den håndterer negative sifferantall for skalakomma på samme måte:

HotXLS avrundingsdiagram: 2.5 avrundes halv bort fra null til 3 og -2.5 til -3, der Delphi System.Round gir bankersvarene 2 og -2, og siden nærmeste dobbel til 1.005 ligger like under halvveispunktet, er få-ulps toleransen det som gjør et floor-basert 1.00 til Excel-svaret 1.01
Excel avrunder likheter bort fra null og tilgir binær representasjonsfeil med en liten toleranse; begge detaljene er målbare, og hopper du over én av dem, lagres 2 for 2.5 eller 1.00 for 1.005, ett øre unna Excel
// Prinsippskisse: avrunder halv bort fra null til ADigits desimaler,
// med en få-ulps toleranse slik at 1.005 når 1.01.
// ADigits < 0 avrunder til tiere, hundrene, ... ("0.0," gir -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, to ulps av 1.0
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // utover dobbelpresisjon: la verdien være i fred
  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 gitt overflow
    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); // halv bort fra null, 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-basert: 1.00)
// RoundAsDisplayed(2.5, 0)        = 3      (Round: 2)
// RoundAsDisplayed(-2.5, 0)       = -3
// RoundAsDisplayed(0.1234, 3)     = 0.123  ("0.0%": 1 + 2 siffer)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3 siffer)

Toleransen er en bevisst avveining. En verdi som virkelig ligger to ulps under et halvt trinn, avrundes også oppover, men på den avstanden er forskjellen umulig å skille fra representasjonsfeil, og å behandle den som et halvt trinn er det som får tastete desimaler til å oppføre seg slik brukerne forventer

Hva var galt før v2.384.57?

Før v2.384.57 hadde XLSX-motoren og Classic-motoren hver sin precision-as-displayed-kode, og hver var feil på sin måte. Produserer du arbeidsbøker med alternativet på, er dette symptomene du ser etter i filer generert av eldre bygg

XLSX-motoren: bare første seksjon, ingen prosent, bankers avrunding

Den gamle XLSX-stien spurte etter desimalantallet til formatstrengen som helhet, noe som så bare på første seksjon og ignorerte %, og avrundet så med Round. En 0.1234 i 0.0% ble lagret som 0.1, noe som er 10% i stedet for de 12.3% på skjermen. En 2.5 i 0 ble lagret som 2 i stedet for 3. Negative verdier i et format som 0.00;(0.0) ble avrundet til den positive seksjonens to desimaler. Siden v2.384.57 kaller XLSX-motoren samme delte rutine som Classic-motoren, som også fikk støtte for skalakomma i den utgivelsen

Classic-motoren: TRUE ble -1

Classic-motoren voktet avrundingen sin med VarIsNumeric, og VarIsNumeric returnerer True for en varBoolean-Variant. Å konvertere den Varianten med Double(V) gir -1, fordi en COM-aktig Boolean True lagres som -1. En formel som =A1>0 i en celle formatert 0.00 kom derfor ut av rekalkuleringen som tallet -1. Siden v2.384.57 ekskluderes boolske resultater før enhver numerisk test, og et logisk resultat forblir et logisk resultat i begge motorer

Elapsed-time-formater lest som farger (v2.384.9)

Den tredje buggen satt i tallformat-modellen snarere enn i avrundingen. Parseren klassifiserte hvert klammet token som ikke var en betingelse som en farge, så [h], [mm] og [ss] markerte aldri seksjonen sin som dato/tid. Visningen var upåvirket, fordi formatering kjører på en egen sti, men precision as displayed er avhengig av det flagget for å hoppe over tidsverdier. En varighet på fem sekunder er 5/86400 av et døgn, omtrent 0.0000579, og et format som [ss].00 så ut som et vanligt todessimaltall, så med FullPrecision av ble varigheten avrundet til 0.00 dager. Siden v2.384.9 parses en klammet kjørmengde av en enkelt h-, m- eller s-bokstav som et elapsed-time-token, og seksjonen behandles som dato/tid. Samme utgivelse rettet minuttgjenkjenning i h:mm, der kolonet mellom tokenene pleide å skjule timen for parseren

HotXLS-diagram av en elapsed time-feiltolkning: fem sekunder lagret som en liten dagsbrøk i en celle formatert med det klammete ss-tokenet, som den gamle parseren leste som en farge og flagget som et vanligt todessimaltall, slik at precision as displayed avrundet varigheten til 0.00 til den ble parset som en elapsed time-seksjon
Formateringen kjørte på sin egen sti, så cellen så riktig ut mens den lagrede verdien avrundes til null; et klammet enkelttegn h, m eller s er et elapsed time-token, ikke en farge, og seksjonen beholder full presisjon

Å slå på precision as displayed i HotXLS fra Delphi

Skal du ha Excel-ekvivalente lagrede verdier, setter du flagget før rekalkuleringen som skal ære det, og leser så de bufrede resultatene eller lagrer. På XLSX-motoren er FullPrecision et bart flagg: å endre det ugyldiggjør ikke resultater en tidligere Recalculate allerede har lagret, så sett det rett etter Create eller Open og før den første Recalculate. Eksemplet bruker formler fordi det er der HotXLS bruker avrundingen:

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 (tusener)

    // Må settes før den første Recalculate på XLSX-motoren
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Bufrede resultater matcher nå Excel 16: 0.123, 3 og 12300.
    // Konstantene i kolonne A beholder sin fulle presisjon.
    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 oppfører seg likt, med én bekvemmelighet: å tildele TXLSWorkbook.UseFullPrecision markerer hver formel i avhengighetsgrafen som skitten, slik at neste Recalculate evaluerer hele arbeidsboken på nytt under den nye regelen. Å endre en NumberFormat mens alternativet er på, markerer også de berørte formelcellene som skitne, fordi formatet nå avgjør den lagrede verdien. Merk at Classic Recalculate returnerer antallet formelceller den ikke kunne evaluere, så null betyr suksess:

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 skitten
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: den negative seksjonen "(0.0)" viser én desimal
    // C1 forblir boolsk True (bygg før v2.384.57 lagret -1)
    Wb.SaveAs('report.xls'); // CalcPrecision-post med fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Begge motorer ærer også flagget som kommer inn med en fil. Åpner du en arbeidsbok lagret med alternativet på, er FullPrecision eller UseFullPrecision allerede False, så en Recalculate etter lasting avrunder nøyaktig slik Excel ville gjort. Trenger du bare å lese tallene Excel allerede har lagret, kan du hoppe over rekalkuleringen fullstendig, som beskrevet i lesing av bufrede formelverdier uten rekalkulering. For hvordan serienumre og datoformater samspiller med formatmodellen som driver dato/tid-sjekken, se Excel-datoserier, 1904-systemet og numFmt i Delphi

Når bør du slå på precision as displayed, og når ikke?

Slå på precision as displayed bare når arbeidsbokens lagrede tall må være lik de viste tallene, og du aksepterer å miste de ekstra sifrene for alltid. Det klassiske legitime tilfellet er en finansiell planlegging der kolonner med avrundede beløp må summere til den avrundede totalen på skjermen, uten skjulte brøkdeler av en øre som gir en total som avviker med én i siste siffer. Å matche en kundes eksisterende arbeidsbok som allerede har alternativet satt, er den andre gode grunnen, og HotXLS bevarer flagget på rundtur slik at du ikke stille bytter dem tilbake til full presisjon

Unngå det i de fleste andre situasjoner:

  • Ingeniør- og vitenskapelige data. Å avrunde en måling fordi noen valgte et todessimalt format for en rapport, ødelegger informasjon ingen senere formatendring kan gjenopprette
  • Prosentandeler med grove formater. Et 0%-format beholder bare to desimaler av den lagrede andelen, så 0.1234 blir 0.12, og hver formel nedstrøms som leser cellen, jobber med 0.12
  • Skalerte visninger. Et 0, eller 0.0,-format brukt til å vise tusener avrunder den lagrede verdien til tusener eller hundrene, noe som sjelden er det personen som valgte formatet, mente
  • Delte maler. Flagget er arbeidsbokomfattende. Enhver som senere legger til et ark, arver atferden, vanligvis uten å vite at den er på

Er det du egentlig vil ha, avrundede resultater i noen spesifikke celler, skriv ROUND inn i de formulene i stedet. ROUND er eksplisitt, lokal for cellen, synlig for enhver som leser formelen, og evalueres av HotXLS formelmotor som enhver annen funksjon, uten arbeidsbokomfattende sideeffekter

Precision as displayed hurtigreferanse

  • Filflagg: CalcPrecision $000E med fFullPrec = 0 i BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" i XLSX (ECMA-376 Part 1)
  • HotXLS-brytere: TXLSXWorkbook.FullPrecision := False og TXLSWorkbook.UseFullPrecision := False, begge har True som standard
  • Seksjon: valgt etter fortegnet til den beregnede verdien; tredje seksjon bare for nøyaktig null
  • Siffer: desimalplassholdere, pluss to per %, minus tre per skalakomma; antallet kan være negativt
  • Avrunding: halv bort fra null med en få-ulps toleranse, så 2.5 gir 3, -2.5 gir -3 og 1.005 gir 1.01
  • Hoppes over: General, dato/tid og elapsed time, vitenskapelige, brøk, tekst, boolske og feilverdier
  • Omfang i HotXLS: formelresultater slik de beregnes; konstanter lagres som tildelt
  • XLSX-motoren: sett FullPrecision før den første Recalculate; Classic-setteren re-markerer selv alle formler som skitne
  • Versjoner: matchet mot Excel 16 i begge motorer siden v2.384.57; elapsed-time-formater beskyttet siden v2.384.9

HotXLS leser, skriver og beregner XLS- og XLSX-arbeidsbøker nativt fra Delphi og C++Builder, inkludert arbeidsbokens beregningsvalg dekket her. Detaljer, utgaver og prøveversjonen finner du på siden for HotXLS Delphi regnearkkomponent