HotXLS Precision as Displayed: de afrondregels van Excel

Excel precision as displayed rondt elk opgeslagen getal af op de decimalen die zijn getalnotatie toont: de formatsectie die matcht met het teken van de waarde, twee extra decimalen per %, drie minder per duizendtallen-komma, afrondend op halve af van nul. HotXLS past dezelfde regel toe in beide Delphi-engines wanneer TXLSXWorkbook.FullPrecision of TXLSWorkbook.UseFullPrecision False is. Dat klinkt als één regel code totdat een klant meldt dat uw geëxporteerde factuurtotalen met een cent afwijken van Excel, of dat een kolom duren in [ss].00 naar nul is geslonken. Beide zijn gebeurd, en beide gaan terug op het verkeerd toepassen van een van die regels. Sinds v2.384.57 delen de twee engines één implementatie waarvan de verwachte waarden in Excel 16 zijn gemeten met Workbook.PrecisionAsDisplayed ingeschakeld

Wat verandert precision as displayed werkelijk in een workbook?

Precision as displayed is één vlag op workbookniveau die de rekenengine opdraagt getallen op te slaan zoals ze eruitzien, niet zoals ze zijn berekend. In de UI van Excel zit hij onder Bestand, Opties, Geavanceerd, bij het berekenen van dit workbook, als zet precisie zoals weergegeven. Op schijf is hij één bit. Een BIFF8-bestand draagt hem in het CalcPrecision-record ($000E, [MS-XLS] §2.4.35), waarvan het veld fFullPrec 1 is voor normale volledige precisie en 0 wanneer de optie aanstaat. Een XLSX-package draagt hem als het attribuut fullPrecision van het element calcPr in workbook.xml, gedefinieerd in ECMA-376 Part 1, waar de default true is en fullPrecision="0" de afronding inschakelt

De vlag is geen weergavevoorkeur. Als u het vakje aanvinkt, waarschuwt Excel dat data permanent aan nauwkeurigheid zal inboeten, en dat meent hij: waarden worden herschreven naar hun weergegeven precisie, en de cijfers die zijn afgekapt zijn weg. Het vakje later weer uitzetten haalt de oude cijfers niet terug. Een 0.1234 getoond als 12.3% wordt voorgoed 0.123

HotXLS leest en schrijft de vlag in beide formaten en stelt hem in beide engines bloot:

• TXLSXWorkbook.FullPrecision: Boolean op de XLSX-engine, geladen uit en opgeslagen in calcPr/@fullPrecision
• TXLSWorkbook.UseFullPrecision: Boolean op de klassieke engine (ook op IXLSWorkbook), geladen uit en opgeslagen in het CalcPrecision-record
• Beide staan by default op True, de veilige, niet-destructieve modus en tevens de default van Excel

Waar HotXLS de afronding toepast doet ertoe. HotXLS rondt af op het punt waar hij een waarde berekent: elk formuleresultaat wordt afgerond naar zijn weergegeven precisie voordat het als gecachte waarde van de cel wordt opgeslagen, tijdens Recalculate en tijdens evaluatie op verzoek. Constanten die u via Value toewijst worden exact zoals gegeven opgeslagen. Moet uw output reproduceren wat Excel opslaat nadat het vakje is aangevinkt, rond die constanten dan zelf af voordat u ze wegschrijft, bijvoorbeeld met de helper die later volgt

Hoe beslist Excel hoeveel decimalen er worden gehouden?

Excel leidt het aantal gehouden decimalen af uit de specifieke formatsectie die de waarde toont, niet uit de formatstring als geheel. De regels hieronder zijn gemeten in Excel 16 en zijn wat XlsApplyDisplayedPrecision in lxNumFormat voor beide HotXLS-engines implementeert

1. Kies de sectie op teken. Een formaat met twee secties gebruikt de tweede sectie voor negatieve waarden. Een formaat met drie of meer secties gebruikt de tweede voor negatieve waarden en de derde voor exact nul. Al het andere gebruikt de eerste sectie
2. Tel de decimal-placeholders. Elke 0, # of ? na de decimale punt in die sectie levert één gehouden decimaal op
3. Voeg er twee toe per procentsign. 0.0% toont 0.1234 als 12.3%, dus de opgeslagen waarde is een honderdste van wat u ziet en houdt drie decimalen, niet één
4. Trek er drie af per schaalkomma. Een komma na de laatste integer-placeholder (0,, 0.0,, 0,.0) deelt de weergave door 1000. 0.0, toont 12345.678 als 12.3, dus Excel houdt één decimaal minus drie, een negatieve telling: de waarde wordt afgerond op honderdtallen en opgeslagen als 12300. Een komma tussen integer-placeholders, zoals in #,##0, is gewone cijfergroepering en verandert niets
5. Laat niet-numerieke secties met rust. Secties General, datum en tijd (inbegrepen verstreken [h], [mm] en [ss]), wetenschappelijk, breuk en tekst, en secties zonder enige cijfer-placeholder houden de volledige precisie

Gemeten tegen Excel 16 zijn dit de waarden die beide HotXLS-engines nu opslaan voor een formuleresultaat per formaat:

Getalnotatie	Berekende waarde	Opgeslagen waarde	Toepasselijke regel
0.0%	0.1234	0.123	Eén decimaal plus twee voor het procentsign
0	2.5	3	Halve af van nul, niet naar even
0	-2.5	-3	Halve af van nul, ook aan de negatieve kant
0.00;(0.0)	-1.2345	-1.2	Negatieve sectie toont één decimaal
0.00;(0.0)	1.2345	1.23	Positieve sectie toont twee decimalen
#,##0.0	1234.5678	1234.6	Groeperingskomma, geen schaling
0.0,	12345.678	12300	Eén decimaal minus drie: afronden op honderdtallen
0.0%;(0.00%)	-0.0125	-0.0125	Negatieve sectie houdt twee plus twee decimalen
0.00	1.005	1.01	Tolerantie voor binaire representatiefout
0;-0;0.0	0.5	1	Niet nul, dus de positieve sectie beslist

De laatste rij is een mooie val. De waarde 0.5 rondt af op een heel getal, en de nulsectie komt nooit in beeld, omdat Excel de sectie uit de berekende waarde kiest vóór het afronden. Eén eerlijke beperking aan de HotXLS-kant: secties worden alleen op teken gekozen, dus een formaat waarvan de secties eigen tussen-haak-condities dragen zoals [>=1000] wordt nog steeds op teken gesplitst. Check zulke formaten tegen Excel als ze voor u ertoe doen

Waarom rondt 1.005 af op 1.01 en niet op 1.00?

Excel rondt 1.005 in een cel 0.00 af op 1.01, hoewel de double die het dichtst bij 1.005 ligt net onder het halve punt zit, en HotXLS matcht dat met een tolerantie van enkele ulps. Het getal 1.005 is niet representeerbaar in binair floating point. De dichtstbijzijnde IEEE 754-double is 1.00499999999999989341858963598497211933135986328125, en maal 100 geeft 100.49999999999999. Een standard Floor(x * 100 + 0.5) / 100 geeft daardoor 1.00 terug, wat afwijkt van het getal dat de gebruiker intypte, van wat Excel toont en van wat Excel opslaat

Delphi voegt er nog een eigen draai aan toe. System.Round rondt gelijkspel naar even af, dus Round(2.5) is 2 en Round(3.5) is 4. Dat is bankers rounding, een zinnige default voor statistiek en hier de verkeerde regel: Excel slaat voor 2.5 in een cel 0 een 3 op en voor -2.5 een -3. De HotXLS-implementatie werkt op de absolute waarde, telt 0.5 plus een relatieve tolerantie van 2^-51 maal de geschaalde waarde op (enkele ulps op die grootte, nooit minder dan twee ulps van 1.0), kappt af, schaalt terug en herstelt het teken. De volgende functie is een zelfstandige illustratie van dat principe, niet de librarycode zelf, en hij behandelt negatieve cijfertellingen voor schaalkomma's op dezelfde manier:

// Principeschets: rond halve af van nul af op ADigits decimalen,
// met een tolerantie van enkele ulps zodat 1.005 op 1.01 uitkomt.
// ADigits < 0 rondt af op tientallen, honderdtallen, ... ("0.0," geeft -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, twee ulps van 1.0
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // voorbij double-precisie: laat de waarde met rust
  Scale := 1;
  for I := 1 to Abs(ADigits) do
    Scale := Scale * 10;
  if ADigits >= 0 then
  begin
    if Abs(AValue) > 1E300 / Scale then
      Exit; // schalen zou overflowen
    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); // halve af van nul, niet 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-gebaseerd: 1.00)
// RoundAsDisplayed(2.5, 0)        = 3      (Round: 2)
// RoundAsDisplayed(-2.5, 0)       = -3
// RoundAsDisplayed(0.1234, 3)     = 0.123  ("0.0%": 1 + 2 cijfers)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3 cijfers)

De tolerantie is een bewuste afweging. Een waarde die werkelijk twee ulps onder een halve stap zit rondt ook omhoog af, maar op die afstand is het verschil niet te onderscheiden van een representatiefout, en het als halve stap behandelen is wat getypte decimalen laat gedragen zoals gebruikers verwachten

Wat ging er mis vóór v2.384.57?

Vóór v2.384.57 hadden de XLSX-engine en de klassieke engine elk hun eigen precision-as-displayed-code, en elk zat op een eigen manier fout. Produceert u workbooks met de optie aan, dan zijn dit de symptomen om in bestanden van oudere builds te zoeken

XLSX-engine: alleen de eerste sectie, geen procent, bankers rounding

Het oude XLSX-pad vroeg het aantal decimalen van de formatstring als geheel, wat alleen naar de eerste sectie keek en % negeerde, en rondde daarna af met Round. Een 0.1234 in 0.0% werd opgeslagen als 0.1, dus 10% in plaats van de 12.3% op het scherm. Een 2.5 in 0 werd opgeslagen als 2 in plaats van 3. Negatieve waarden in een formaat als 0.00;(0.0) werden afgerond op de twee decimalen van de positieve sectie. Sinds v2.384.57 roept de XLSX-engine dezelfde gedeelde routine aan als de klassieke engine, die in die release ook schaalkomma-ondersteuning kreeg

Klassieke engine: TRUE werd -1

De klassieke engine beveiligde zijn afronding met VarIsNumeric, en VarIsNumeric geeft True terug voor een Variant varBoolean. Die Variant met Double(V) converteren levert -1 op, want een Boolean True in COM-stijl wordt als -1 opgeslagen. Een formule als =A1>0 in een cel geformatteerd als 0.00 kwam uit de herberekening dus als het getal -1. Sinds v2.384.57 worden Boolean-resultaten vóór elke numerieke test uitgesloten, en blijft een logisch resultaat een logisch resultaat in beide engines

Verstreken-tijd-formaten gelezen als kleuren (v2.384.9)

De derde bug zat in het getalnotatiemodel in plaats van in de afronding. De parser classificeerde elk tussen haken geplaatst token dat geen conditie was als een kleur, dus [h], [mm] en [ss] markeerden hun sectie nooit als datum/tijd. De weergave merkte er niets van, want opmaak draait op een apart pad, maar precision as displayed is op die vlag aangewezen om tijdwaarden over te slaan. Een duur van vijf seconden is 5/86400 van een dag, zo'n 0.0000579, en een formaat als [ss].00 zag eruit als een gewoon getal met twee decimalen, dus met FullPrecision uit werd de duur afgerond op 0.00 dagen. Sinds v2.384.9 wordt een tussen haken geplaatste reeks van één letter h, m of s geparseerd als een verstreken-tijd-token en wordt de sectie behandeld als datum/tijd. Dezelfde release fixte de minutendetectie in h:mm, waar de dubbele punt tussen de tokens het uur eerder voor de parser verborg

Precision as displayed in HotXLS aanzetten vanuit Delphi

Om opgeslagen waarden te krijgen die aan Excel gelijk zijn, zet u de vlag vóór de herberekening die haar moet respecteren, en leest daarna de gecachte resultaten of slaat op. Op de XLSX-engine is FullPrecision een kale vlag: haar veranderen maakt resultaten die een eerdere Recalculate al heeft opgeslagen niet ongeldig, dus zet haar direct na Create of Open en vóór de eerste Recalculate. Het voorbeeld gebruikt formules omdat dat het punt is waar HotXLS afrondt:

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

    // Moet vóór de eerste Recalculate op de XLSX-engine worden gezet
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Gecachte resultaten matchen nu Excel 16: 0.123, 3 en 12300.
    // De constanten in kolom A houden hun volledige precisie.
    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'); // schrijft <calcPr fullPrecision="0"/>
  finally
    Wb.Free;
  end;
end;

De klassieke engine gedraagt zich hetzelfde, met één gemak: toewijzing van TXLSWorkbook.UseFullPrecision markeert elke formule in de dependencygraaf als dirty, dus de volgende Recalculate evalueert het hele workbook opnieuw onder de nieuwe regel. Een NumberFormat veranderen terwijl de optie aanstaat markeert de betrokken formulecellen ook als dirty, want het formaat beslist nu over de opgeslagen waarde. Merk op dat de klassieke Recalculate het aantal formulecellen teruggeeft dat hij niet kon evalueren, dus nul betekent 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; // markeert elke formule als dirty
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: de negatieve sectie "(0.0)" toont één decimaal
    // C1 blijft Boolean True (builds vóór v2.384.57 sloegen -1 op)
    Wb.SaveAs('report.xls'); // CalcPrecision-record met fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Beide engines respecteren ook de vlag die met een bestand meekomt. Open een workbook dat met de optie aan is opgeslagen en FullPrecision of UseFullPrecision staat al op False, dus een Recalculate na het laden rondt precies af zoals Excel dat zou doen. Hoeft u alleen de getallen te lezen die Excel al heeft opgeslagen, dan kunt u de herberekening helemaal overslaan, zoals beschreven in gecache formulewaarden lezen zonder herberekening. Voor hoe serienummers en datumnotaties samenspelen met het notatiemodel dat de datum/tijd-controle aandrijft, zie Excel-datumserienummers, het 1904-systeem en numFmt in Delphi

Wanneer zet u precision as displayed aan, en wanneer niet?

Zet precision as displayed alleen aan wanneer de opgeslagen getallen van het workbook gelijk moeten zijn aan zijn weergegeven getallen, en u het verlies van de extra cijfers voorgoed accepteert. Het klassieke legitieme geval is een financieel schema waarin kolommen afgeronde bedragen moeten optellen tot het afgeronde totaal op het scherm, zonder verborgen centfracties die een totaal opleveren dat op de laatste plaats één afwijkt. Aansluiten bij een bestaand workbook van een klant waarin de optie al staat is de andere goede reden, en HotXLS behoudt de vlag bij de round trip zodat u ze niet stilletjes terugzet naar volledige precisie

Vermijd hem in de meeste andere situaties:

• Engineering- en wetenschappelijke data. Een meting afronden omdat iemand voor een rapport een formaat met twee decimalen koos vernietigt informatie die geen latere formaatwijziging kan herstellen
• Percentages met grove formaten. Een formaat 0% houdt slechts twee decimalen van de opgeslagen verhouding over, dus 0.1234 wordt 0.12, en elke stroomafwaartse formule die de cel leest rekent met 0.12
• Geschaalde weergaven. Een formaat 0, of 0.0, gebruikt om duizendtallen te tonen rondt de opgeslagen waarde af op duizendtallen of honderdtallen, en dat is zelden wat degene die het formaat koos voor ogen had
• Gedeelde templates. De vlag geldt voor het hele workbook. Iedereen die later een werkblad toevoegt erft het gedrag, meestal zonder te weten dat hij aanstaat

Wilt u eigenlijk alleen afgeronde resultaten in een paar specifieke cellen, schrijf dan ROUND in die formules. ROUND is expliciet, lokaal voor de cel, zichtbaar voor iedereen die de formule leest, en wordt door de HotXLS-formule-engine geëvalueerd als elke andere functie, zonder workbookbrede neveneffecten

Snelnaslag precision as displayed

• Bestandsvlag: CalcPrecision $000E met fFullPrec = 0 in BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" in XLSX (ECMA-376 Part 1)
• HotXLS-schakelaars: TXLSXWorkbook.FullPrecision := False en TXLSWorkbook.UseFullPrecision := False, beide by default True
• Sectie: gekozen op het teken van de berekende waarde; derde sectie alleen voor exact nul
• Cijfers: decimal-placeholders, plus twee per %, minus drie per schaalkomma; de telling kan negatief zijn
• Afronding: halve af van nul met een tolerantie van enkele ulps, dus 2.5 geeft 3, -2.5 geeft -3 en 1.005 geeft 1.01
• Overgeslagen: General, datum/tijd en verstreken tijd, wetenschappelijk, breuk, tekst, Boolean en foutwaarden
• Bereik in HotXLS: formuleresultaten op het moment van berekenen; constanten worden opgeslagen zoals toegewezen
• XLSX-engine: zet FullPrecision vóór de eerste Recalculate; de klassieke setter maakt zelf alle formules opnieuw dirty
• Versies: gelijkgetrokken met Excel 16 in beide engines sinds v2.384.57; verstreken-tijd-formaten beschermd sinds v2.384.9

HotXLS leest, schrijft en berekent XLS- en XLSX-workbooks native vanuit Delphi en C++Builder, inclusief de hier behandelde berekeningsopties op workbookniveau. Details, edities en de proefdownload staan op de HotXLS Delphi spreadsheet component-pagina