HotXLS Precision as Displayed: Excels Rundungsregeln

Excel Precision as displayed rundet jede gespeicherte Zahl auf die Dezimalstellen, die ihr Zahlenformat zeigt: die zur Wertvorzeichen passende Formatsektion, zwei zusätzliche Dezimalstellen pro %, drei weniger pro Tausender-Skalierungskomma, gerundet wird halb von null weg. HotXLS wendet dieselbe Regel in beiden Delphi-Engines an, wenn TXLSXWorkbook.FullPrecision oder TXLSWorkbook.UseFullPrecision auf False steht. Das klingt nach einer Einzeiler-Sache, bis ein Kunde meldet, dass Ihre exportierten Rechnungssummen um einen Cent von Excel abweichen, oder dass eine Spalte mit Dauern in [ss].00 auf null kollabiert ist. Beides ist passiert, und beides ließ sich auf eine der falsch behandelten Regeln zurückführen. Seit v2.384.57 teilen sich die zwei Engines eine einzige Implementierung, deren Erwartungswerte in Excel 16 mit eingeschaltetem Workbook.PrecisionAsDisplayed ausgemessen wurden

Was ändert Precision as displayed tatsächlich in einer Arbeitsmappe?

Precision as displayed ist ein einzelnes Flag auf Workbook-Ebene, das der Berechnungs-Engine sagt, Zahlen so zu speichern, wie sie aussehen, nicht wie sie berechnet wurden. In der Excel-UI sitzt es unter Datei, Optionen, Erweitert, „Beim Berechnen dieser Arbeitsmappe“, als „Genauigkeit wie dargestellt“. Auf der Disk ist es ein Bit. Eine BIFF8-Datei trägt es im CalcPrecision-Record ($000E, [MS-XLS] §2.4.35), dessen Feld fFullPrec 1 für normale volle Präzision ist und 0, wenn die Option an ist. Ein XLSX-Paket trägt es als fullPrecision-Attribut des calcPr-Elements in workbook.xml, definiert in ECMA-376 Part 1, mit dem Default true, fullPrecision="0" schaltet also das Runden ein

Das Flag ist keine Anzeige-Präferenz. Beim Anhaken warnt Excel, dass die Daten dauerhaft an Genauigkeit verlieren, und es meint es ernst: Die Werte werden auf ihre angezeigte Präzision umgeschrieben, und die abgeschnittenen Ziffern sind fort. Das spätere Ausschalten holt die alten Ziffern nicht zurück. Ein als 12.3% gezeigtes 0.1234 wird für immer zu 0.123

HotXLS liest und schreibt das Flag in beiden Formaten und legt es in beiden Engines offen:

• TXLSXWorkbook.FullPrecision: Boolean auf der XLSX-Engine, geladen aus und gesichert in calcPr/@fullPrecision
• TXLSWorkbook.UseFullPrecision: Boolean auf der klassischen Engine (auch auf IXLSWorkbook), geladen aus und gesichert in den CalcPrecision-Record
• Beide stehen auf Default True, dem sicheren, zerstörungsfreien Modus und dem Excel-Default

Wo HotXLS das Runden ansetzt, zählt. HotXLS rundet an der Stelle, an der es einen Wert berechnet: Jedes Formelergebnis wird auf seine angezeigte Präzision gerundet, bevor es als zwischengespeicherter Wert der Zelle abgelegt wird, während Recalculate und während der bedarfsgesteuerten Auswertung. Konstanten, die Sie über Value zuweisen, werden exakt wie gegeben gespeichert. Muss Ihre Ausgabe reproduzieren, was Excel nach dem Anhaken speichert, runden Sie diese Konstanten selbst, bevor Sie sie schreiben, zum Beispiel mit dem Helfer, der später gezeigt wird

Wie entscheidet Excel, wie viele Dezimalstellen bleiben?

Excel leitet die Anzahl der behaltenen Dezimalstellen aus der konkreten Formatsektion ab, die den Wert anzeigt, nicht aus dem Formatstring als Ganzem. Die folgenden Regeln wurden in Excel 16 ausgemessen und sind das, was XlsApplyDisplayedPrecision in lxNumFormat für beide HotXLS-Engines implementiert

1. Sektion nach Vorzeichen wählen. Ein Format mit zwei Sektionen nutzt die zweite für negative Werte. Ein Format mit drei oder mehr Sektionen nutzt die zweite für negative Werte und die dritte für exakt null. Alles andere nutzt die erste Sektion
2. Dezimal-Platzhalter zählen. Jedes 0, # oder ? nach dem Dezimalpunkt in dieser Sektion liefert eine behaltene Dezimalstelle
3. Zwei pro Prozentzeichen addieren. 0.0% zeigt 0.1234 als 12.3%, der gespeicherte Wert ist also ein Hundertstel dessen, was man sieht, und behält drei Dezimalstellen, nicht eine
4. Drei pro Skalierungskomma abziehen. Ein Komma nach dem letzten Ganzzahl-Platzhalter (0,, 0.0,, 0,.0) teilt die Anzeige durch 1000. 0.0, zeigt 12345.678 als 12.3, Excel behält also eine Dezimalstelle minus drei, eine negative Anzahl: Der Wert wird auf die Hunderter gerundet und als 12300 gespeichert. Ein Komma zwischen Ganzzahl-Platzhaltern, wie in #,##0, ist schlicht Zifferngruppierung und ändert nichts
5. Nicht-numerische Sektionen in Ruhe lassen. General-, Datums- und Zeitsktionen (einschließlich der verstrichenen [h], [mm] und [ss]), wissenschaftliche, Bruch- und Textsektionen sowie Sektionen ohne jeden Ziffern-Platzhalter behalten die volle Präzision

Gemessen gegen Excel 16 sind dies die Werte, die beide HotXLS-Engines jetzt für ein Formelergebnis in jedem dieser Formate speichern:

Zahlenformat	Berechneter Wert	Gespeicherter Wert	Angewandte Regel
0.0%	0.1234	0.123	Eine Dezimalstelle plus zwei für das Prozentzeichen
0	2.5	3	Halb von null weg, nicht auf gerade
0	-2.5	-3	Halb von null weg, auch auf der negativen Seite
0.00;(0.0)	-1.2345	-1.2	Die negative Sektion zeigt eine Dezimalstelle
0.00;(0.0)	1.2345	1.23	Die positive Sektion zeigt zwei Dezimalstellen
#,##0.0	1234.5678	1234.6	Gruppierungskomma, keine Skalierung
0.0,	12345.678	12300	Eine Dezimalstelle minus drei: auf Hunderter runden
0.0%;(0.00%)	-0.0125	-0.0125	Die negative Sektion behält zwei plus zwei Dezimalstellen
0.00	1.005	1.01	Toleranz für den Binärdarstellungsfehler
0;-0;0.0	0.5	1	Nicht null, die positive Sektion entscheidet also

Die letzte Zeile ist eine hübsche Falle. Der Wert 0.5 wird auf eine ganze Zahl gerundet, und die Null-Sektion kommt nie zum Zuge, denn Excel wählt die Sektion nach dem berechneten Wert, vor dem Runden. Eine ehrliche Grenze auf HotXLS-Seite: Sektionen werden allein nach Vorzeichen gewählt, ein Format, dessen Sektionen eigene Klammerbedingungen wie [>=1000] tragen, wird also weiterhin nach Vorzeichen gespalten. Prüfen Sie solche Formate gegen Excel, wenn sie Ihnen wichtig sind

Warum wird aus 1.005 die 1.01 und nicht die 1.00?

Excel rundet 1.005 in einer 0.00-Zelle zu 1.01, obwohl das Double, das 1.005 am nächsten liegt, knapp unter dem Halbwegpunkt sitzt, und HotXLS gleicht das mit einer Few-ULP-Toleranz aus. Das Literal 1.005 ist im binären Gleitkomma nicht darstellbar. Das nächste IEEE-754-Double ist 1.00499999999999989341858963598497211933135986328125, und die Multiplikation mit 100 ergibt 100.49999999999999. Ein Lehrbuch-Floor(x * 100 + 0.5) / 100 liefert deshalb 1.00, im Widerspruch zur Zahl, die der Nutzer getippt hat, zu dem, was Excel zeigt, und zu dem, was Excel speichert

Delphi legt eine eigene Volte nach. System.Round rundet Gleichstände auf gerade, Round(2.5) ist also 2 und Round(3.5) ist 4. Das ist Banker's Rounding, ein vernünftiger Default für Statistik und hier die falsche Regel: Excel speichert für 2.5 in einer 0-Zelle die 3 und für -2.5 die -3. Die HotXLS-Implementierung arbeitet auf dem Absolutwert, addiert 0.5 plus eine relative Toleranz von 2^-51 mal dem skalierten Wert (einige ULPs bei dieser Größenordnung, nie weniger als zwei ULPs von 1.0), schneidet ab, skaliert zurück und stellt das Vorzeichen wieder her. Die folgende Funktion ist eine in sich geschlossene Illustration dieses Prinzips, nicht der Bibliothekscode selbst, und sie behandelt negative Ziffernanzahlen für Skalierungskommas auf dieselbe Weise:

// Prinzip-Skizze: halb von null weg auf ADigits Dezimalstellen runden,
// mit einer Few-ULP-Toleranz, damit 1.005 zu 1.01 wird.
// ADigits < 0 rundet auf Zehner, Hunderter, ... ("0.0," liefert -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, zwei ulp von 1.0
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // jenseits der Double-Präzision: Wert in Ruhe lassen
  Scale := 1;
  for I := 1 to Abs(ADigits) do
    Scale := Scale * 10;
  if ADigits >= 0 then
  begin
    if Abs(AValue) > 1E300 / Scale then
      Exit; // Skalierung würde überlaufen
    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); // halb von null weg, nicht 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-basiert: 1.00)
// RoundAsDisplayed(2.5, 0)        = 3      (Round: 2)
// RoundAsDisplayed(-2.5, 0)       = -3
// RoundAsDisplayed(0.1234, 3)     = 0.123  ("0.0%": 1 + 2 Stellen)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3 Stellen)

Die Toleranz ist ein bewusster Trade-off. Ein Wert, der tatsächlich zwei ULPs unter einem Halbwegschritt liegt, rundet ebenfalls auf, aber bei diesem Abstand ist der Unterschied vom Darstellungsfehler nicht zu unterscheiden, und ihn als Halbwegschritt zu behandeln ist das, was getippte Dezimalzahlen so verhalten lässt, wie Nutzer es erwarten

Was ging vor v2.384.57 schief?

Vor v2.384.57 hatte die XLSX-Engine und die klassische Engine jeweils ihren eigenen Precision-as-displayed-Code, und jeder war auf seine Weise falsch. Erzeugen Sie Arbeitsmappen mit eingeschalteter Option, sind dies die Symptome, nach denen Sie in Dateien aus älteren Builds suchen sollten

XLSX-Engine: nur erste Sektion, kein Prozent, Banker's Rounding

Der alte XLSX-Pfad fragte die Dezimalstellenanzahl des Formatstrings als Ganzes ab, was nur in die erste Sektion schaute und % ignorierte, und rundete dann mit Round. Ein 0.1234 in 0.0% wurde als 0.1 gespeichert, also 10% statt der 12.3% auf dem Schirm. Ein 2.5 in 0 wurde als 2 statt 3 gespeichert. Negative Werte in einem Format wie 0.00;(0.0) wurden auf die zwei Dezimalstellen der positiven Sektion gerundet. Seit v2.384.57 ruft die XLSX-Engine dieselbe gemeinsame Routine auf wie die klassische Engine, die in derselben Version auch Skalierungskomma-Unterstützung bekam

Klassische Engine: TRUE wurde zu -1

Die klassische Engine sicherte ihr Runden mit VarIsNumeric ab, und VarIsNumeric liefert True für ein varBoolean-Variant. Die Konvertierung dieses Variants mit Double(V) ergibt -1, denn ein Boolean-True im COM-Stil wird als -1 gespeichert. Eine Formel wie =A1>0 in einer als 0.00 formatierten Zelle kam aus der Neuberechnung also als die Zahl -1 heraus. Seit v2.384.57 werden Boolean-Ergebnisse vor jedem numerischen Test ausgeschlossen, und ein logisches Ergebnis bleibt in beiden Engines ein logisches Ergebnis

Verstrichene-Zeit-Formate als Farben gelesen (v2.384.9)

Der dritte Bug saß im Zahlenformat-Modell statt im Runden. Der Parser klassifizierte jedes eingeklammerte Token, das keine Bedingung war, als Farbe, [h], [mm] und [ss] markierten ihre Sektion also nie als Datum/Zeit. Die Anzeige blieb unberührt, denn die Formatierung läuft auf einem separaten Pfad, aber Precision as displayed verlässt sich auf dieses Flag, um Zeitwerte zu überspringen. Eine Fünf-Sekunden-Dauer ist 5/86400 eines Tags, etwa 0.0000579, und ein Format wie [ss].00 sah aus wie eine gewöhnliche Zweidezimalstellen-Zahl, mit ausgeschaltetem FullPrecision wurde die Dauer also auf 0.00 Tage gerundet. Seit v2.384.9 wird ein eingeklammerter Lauf aus einem einzelnen h-, m- oder s-Buchstaben als Elapsed-Time-Token geparst, und die Sektion wird als Datum/Zeit behandelt. Dasselbe Release fixte die Minutenerkennung in h:mm, wo der Doppelpunkt zwischen den Tokens dem Parser zuvor die Stunde verbarg

Precision as displayed in HotXLS aus Delphi einschalten

Um Excel-äquivalente gespeicherte Werte zu bekommen, setzen Sie das Flag vor der Neuberechnung, die es beachten soll, und lesen dann die zwischengespeicherten Ergebnisse oder sichern. Auf der XLSX-Engine ist FullPrecision ein schlichtes Flag: Eine Änderung invalidiert keine Ergebnisse, die ein früheres Recalculate bereits gespeichert hat, setzen Sie es also direkt nach Create oder Open und vor dem ersten Recalculate. Das Beispiel benutzt Formeln, denn dort setzt HotXLS das Runden an:

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

    // Muss vor dem ersten Recalculate auf der XLSX-Engine gesetzt werden
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Die gecachten Ergebnisse matchen jetzt Excel 16: 0.123, 3 und 12300.
    // Die Konstanten in Spalte A behalten ihre volle Präzision.
    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'); // schreibt <calcPr fullPrecision="0"/>
  finally
    Wb.Free;
  end;
end;

Die klassische Engine verhält sich genauso, mit einer Bequemlichkeit: Die Zuweisung an TXLSWorkbook.UseFullPrecision markiert jede Formel im Abhängigkeitsgraphen als dirty, das nächste Recalculate bewertet also die ganze Arbeitsmappe unter der neuen Regel neu. Ein NumberFormat bei eingeschalteter Option zu ändern markiert die betroffenen Formelzellen ebenfalls als dirty, denn das Format entscheidet jetzt über den gespeicherten Wert. Beachten Sie, dass das klassische Recalculate die Anzahl der Formelzellen zurückgibt, die es nicht auswerten konnte, null bedeutet also Erfolg:

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; // markiert jede Formel als dirty
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: die negative Sektion "(0.0)" zeigt eine Dezimalstelle
    // C1 bleibt Boolean True (Builds vor v2.384.57 speicherten -1)
    Wb.SaveAs('report.xls'); // CalcPrecision-Record mit fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Beide Engines beachten auch das Flag, das mit einer Datei hereinkommt. Öffnen Sie eine mit eingeschalteter Option gesicherte Arbeitsmappe, steht FullPrecision oder UseFullPrecision bereits auf False, ein Recalculate nach dem Laden rundet also exakt so, wie Excel es täte. Müssen Sie nur die Zahlen lesen, die Excel bereits gespeichert hat, können Sie die Neuberechnung komplett überspringen, wie in zwischengespeicherte Formelwerte ohne Neuberechnung lesen beschrieben. Wie Seriennummern und Datumsformate mit dem Formatmodell interagieren, das die Datums-/Zeitprüfung treibt, siehe Excel-Datumsserien, das 1904-System und numFmt in Delphi

Wann schaltet man Precision as displayed ein, und wann nicht?

Schalten Sie Precision as displayed nur ein, wenn die gespeicherten Zahlen der Arbeitsmappe ihren angezeigten Zahlen entsprechen müssen, und Sie akzeptieren, die zusätzlichen Ziffern für immer zu verlieren. Der klassische legitime Fall ist ein Finanzplan, dessen Spalten gerundeter Beträge auf die gerundete Summe auf dem Schirm aufgehen müssen, ohne dass verborgene Cent-Bruchteile eine Summe produzieren, die an letzter Stelle um eins danebenliegt. Eine bestehende Arbeitsmappe eines Kunden nachzubilden, die die Option bereits gesetzt hat, ist der andere gute Grund, und HotXLS bewahrt das Flag beim Roundtrip, Sie schalten also niemanden stillschweigend zurück auf volle Präzision

Meiden Sie es in den meisten anderen Situationen:

• Engineering- und wissenschaftliche Daten. Eine Messung zu runden, weil jemand für einen Report ein Zweidezimalstellen-Format gewählt hat, zerstört Information, die keine spätere Formatänderung zurückholt
• Prozentsätze mit groben Formaten. Ein 0%-Format behält nur zwei Dezimalstellen des gespeicherten Verhältnisses, 0.1234 wird also zu 0.12, und jede nachgelagerte Formel, die die Zelle liest, arbeitet mit 0.12
• Skalierte Anzeigen. Ein 0,- oder 0.0,-Format, das Tausender zeigen soll, rundet den gespeicherten Wert auf Tausender oder Hunderter, was selten das ist, was die Person im Sinn hatte, die das Format wählte
• Geteilte Vorlagen. Das Flag gilt arbeitsmappenweit. Jeder, der später ein Blatt ergänzt, erbt das Verhalten, meist ohne zu wissen, dass es an ist

Wollen Sie in Wahrheit gerundete Ergebnisse in ein paar konkreten Zellen, schreiben Sie stattdessen ROUND in diese Formeln. ROUND ist explizit, lokal auf die Zelle beschränkt, für jeden sichtbar, der die Formel liest, und wird von der HotXLS-Formel-Engine wie jede andere Funktion ausgewertet, ohne arbeitsmappenweite Nebenwirkungen

Precision as displayed Kurzreferenz

• Datei-Flag: CalcPrecision $000E mit fFullPrec = 0 in BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" in XLSX (ECMA-376 Part 1)
• HotXLS-Schalter: TXLSXWorkbook.FullPrecision := False und TXLSWorkbook.UseFullPrecision := False, beide mit Default True
• Sektion: gewählt nach dem Vorzeichen des berechneten Werts; die dritte Sektion nur für exakt null
• Ziffern: Dezimal-Platzhalter, plus zwei pro %, minus drei pro Skalierungskomma; die Anzahl kann negativ sein
• Runden: halb von null weg mit einer Few-ULP-Toleranz, 2.5 ergibt also 3, -2.5 ergibt -3 und 1.005 ergibt 1.01
• Übersprungen: General, Datum/Zeit und Elapsed Time, wissenschaftlich, Bruch, Text, Boolean und Fehlerwerte
• Geltungsbereich in HotXLS: Formelergebnisse, während sie berechnet werden; Konstanten werden wie zugewiesen gespeichert
• XLSX-Engine: FullPrecision vor dem ersten Recalculate setzen; der klassische Setter macht alle Formeln selbst wieder dirty
• Versionen: an Excel 16 angeglichen in beiden Engines seit v2.384.57; Elapsed-Time-Formate geschützt seit v2.384.9

HotXLS liest, schreibt und berechnet XLS- und XLSX-Arbeitsmappen nativ aus Delphi und C++Builder, eingeschlossen die hier behandelten Berechnungsoptionen der Arbeitsmappe. Details, Editionen und den Testdownload finden Sie auf der HotXLS Delphi spreadsheet component page