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 gaan terug op het verkeerd toepassen van een van die regels. De twee HotXLS-engines delen éé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: Booleanop de XLSX-engine, geladen uit en opgeslagen incalcPr/@fullPrecisionTXLSWorkbook.UseFullPrecision: Booleanop de klassieke engine (ook opIXLSWorkbook), 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
- 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
- Tel de decimal-placeholders. Elke
0,#of?na de decimale punt in die sectie levert één gehouden decimaal op - 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 - 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 - 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 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. HotXLS kiest secties op teken, dus voor een formaat waarvan de secties eigen tussen-haak-condities dragen zoals [>=1000]: vergelijk de opgeslagen waarden met Excel als dat voor u ertoe doet
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
Welke implementatievalkuilen breken precision as displayed?
Drie valkuilen verklaren de meeste precision-as-displayed-mismatches, en elk slaat een waarde op die niet met het scherm overeenkomt. Implementeert u de regel zelf, of auditeert u workbooks die met de optie aan zijn gegenereerd, dan zijn dit de symptomen om naar te zoeken
Alleen de eerste sectie, geen procent, bankers rounding
De verleidelijke shortcut vraagt het aantal decimalen van de formatstring als geheel, dat alleen naar de eerste sectie kijkt en % negeert, en rondt daarna af met Round. Een 0.1234 in 0.0% wordt dan opgeslagen als 0.1, dus 10% in plaats van de 12.3% op het scherm. Een 2.5 in 0 wordt opgeslagen als 2 in plaats van 3. Negatieve waarden in een formaat als 0.00;(0.0) worden afgerond op de twee decimalen van de positieve sectie. HotXLS leidt beide engines door één gedeelde routine die de sectie op teken kiest, procenttekens en schaalkomma's telt, en op halve af van nul afrondt
TRUE wordt -1
De afronding beveiligen met VarIsNumeric is de tweede valkuil, want 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 zou uit de herberekening komen als het getal -1. HotXLS sluit Boolean-resultaten vóór elke numerieke test uit, dus een logisch resultaat blijft een logisch resultaat in beide engines
Verstreken-tijd-formaten gelezen als kleuren
De derde valkuil zit in het getalnotatiemodel in plaats van in de afronding. Een parser die elk tussen haken geplaatst token dat geen conditie is als een kleur classificeert, markeert [h]-, [mm]- en [ss]-secties nooit als datum/tijd. De weergave merkt 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 ziet er dan uit als een gewoon getal met twee decimalen, dus met FullPrecision uit wordt de duur afgerond op 0.00 dagen. HotXLS parseert een tussen haken geplaatste reeks van één letter h, m of s als een verstreken-tijd-token en behandelt de sectie als datum/tijd. Minutendetectie in h:mm vraagt dezelfde zorg, want de dubbele punt tussen de tokens kan het uur voor een parser verbergen
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, niet het getal -1
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,of0.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
$000EmetfFullPrec= 0 in BIFF8 ([MS-XLS] §2.4.35),calcPr fullPrecision="0"in XLSX (ECMA-376 Part 1) - HotXLS-schakelaars:
TXLSXWorkbook.FullPrecision := FalseenTXLSWorkbook.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
FullPrecisionvóór de eersteRecalculate; de klassieke setter maakt zelf alle formules opnieuw dirty - Referentie: verwachte waarden gemeten in Excel 16, gedeeld door beide engines
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