Technischer Artikel

HotXLS Precision as Displayed: Excels Rundungsregeln

Excel Precision as displayed rundet jede gespeicherte Zahl auf die Dezimalstellen, die ihr Zahlenformat zeigt: die zum Vorzeichen des Werts passende Formatsektion, zwei zusätzliche Dezimalstellen pro %, drei weniger pro Tausender-Skalierungskomma, gerundet wird halb von null weg. HotXLS wendet dieselbe Regel in beiden seiner Delphi-Engines an, wenn TXLSXWorkbook.FullPrecision bzw. TXLSWorkbook.UseFullPrecision auf False steht. Das klingt nach einem Einzeiler, bis ein Kunde meldet, dass Ihre exportierten Rechnungssummen um einen Cent neben Excel liegen, oder dass eine Spalte von Dauern im Format [ss].00 auf null zusammenfiel. Beides geht auf eine dieser Regeln zurück, die falsch umgesetzt wurde. Die beiden HotXLS-Engines teilen sich 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
HotXLS-Diagramm der Regeln für die angezeigte Präzision: Die Formatsektion nach dem Vorzeichen des Werts wählen, die Ziffern-Platzhalter nach dem Dezimalpunkt zählen, zwei Dezimalstellen pro Prozentzeichen addieren, drei pro Tausender-Skalierungskomma abziehen, sodass die Anzahl negativ werden kann, General- und Datums-Zeit-Sektionen komplett überspringen, dann halb von null weg runden
Die Ziffernanzahl kommt aus der Sektion, die zum Vorzeichen passt, plus zwei pro Prozentzeichen und minus drei pro Skalierungskomma, und eine negative Anzahl rundet auf Zehner oder Hunderter; General- und Datumssktionen bleiben unangetastet

Gemessen an Excel 16 sind das die Werte, die beide HotXLS-Engines für ein Formelergebnis in jedem Format speichern:

ZahlenformatBerechneter WertGespeicherter WertAngewandte Regel
0.0%0.12340.123Eine Dezimalstelle plus zwei für das Prozentzeichen
02.53Halb von null weg, nicht auf gerade
0-2.5-3Halb von null weg, auch auf der negativen Seite
0.00;(0.0)-1.2345-1.2Die negative Sektion zeigt eine Dezimalstelle
0.00;(0.0)1.23451.23Die positive Sektion zeigt zwei Dezimalstellen
#,##0.01234.56781234.6Gruppierungskomma, keine Skalierung
0.0,12345.67812300Eine Dezimalstelle minus drei: auf Hunderter runden
0.0%;(0.00%)-0.0125-0.0125Die negative Sektion behält zwei plus zwei Dezimalstellen
0.001.0051.01Toleranz für den Binärdarstellungsfehler
0;-0;0.00.51Nicht null, die positive Sektion entscheidet also

Die letzte Zeile ist eine hübsche Falle. Der Wert 0.5 rundet auf eine ganze Zahl, und die Null-Sektion kommt nie zum Zug, weil Excel die Sektion vor dem Runden aus dem berechneten Wert wählt. HotXLS wählt Sektionen nach Vorzeichen; bei einem Format, dessen Sektionen eigene Klammerbedingungen tragen wie [>=1000], vergleichen Sie die gespeicherten Werte also mit Excel, wenn es Ihnen auf sie ankommt

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:

HotXLS-Rundungsdiagramm: 2.5 wird halb von null weg zu 3 gerundet und -2.5 zu -3, während Delphi System.Round die Banker-Antworten 2 und -2 liefert, und weil das 1.005 nächste Double knapp unter dem Halbwegpunkt sitzt, ist die Few-ULP-Toleranz das, was aus einem Floor-basierten 1.00 die Excel-Antwort 1.01 macht
Excel rundet Gleichstände von null weg und verzeiht den Binärdarstellungsfehler mit einer kleinen Toleranz; beide Details sind messbar, und überspringt man eines davon, werden 2 für 2.5 oder 1.00 für 1.005 gespeichert, ein Cent neben Excel
// 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

Welche Implementierungsfallen brechen Precision as displayed?

Drei Fallen erklären die meisten Abweichungen bei Precision as displayed, und jede speichert einen Wert, der nicht zum Bild auf dem Schirm passt. Wer die Regel selbst implementiert oder Arbeitsmappen prüft, die mit der Option erzeugt wurden, sucht nach diesen Symptomen

Nur die erste Sektion, kein Prozent, Banker's Rounding

Der verlockende Shortcut fragt nach der Dezimalstellenzahl des Formatstrings als Ganzes, was nur in die erste Sektion schaut und % ignoriert, und rundet dann mit Round. Eine 0.1234 in 0.0% wird dann als 0.1 gespeichert — 10% statt der 12.3% auf dem Schirm. Eine 2.5 in 0 wird als 2 statt als 3 gespeichert. Negative Werte in einem Format wie 0.00;(0.0) werden auf die zwei Dezimalstellen der positiven Sektion gerundet. HotXLS führt beide Engines durch eine gemeinsame Routine, die die Sektion nach Vorzeichen wählt, Prozentzeichen und Skalierungskommas zählt und halb von null weg rundet

TRUE wird zu -1

Die Rundung mit VarIsNumeric zu bewachen ist die zweite Falle, denn VarIsNumeric liefert für einen varBoolean-Variant True. Konvertiert man diesen Variant mit Double(V), kommt -1 heraus, denn ein COM-style Boolean True wird als -1 gespeichert. Eine Formel wie =A1>0 in einer als 0.00 formatierten Zelle käme aus der Neuberechnung also als die Zahl -1 heraus. HotXLS schließt Boolean-Ergebnisse vor jedem numerischen Test aus, sodass ein logisches Ergebnis in beiden Engines ein logisches Ergebnis bleibt

Verstrichene Zeiten werden als Farben gelesen

Die dritte Falle sitzt im Zahlenformat-Modell, nicht in der Rundung. Ein Parser, der jedes eingeklammerte Token, das keine Bedingung ist, als Farbe einstuft, markiert [h]-, [mm]- und [ss]-Sektionen nie als Datum/Zeit. Die Anzeige bleibt unberührt, denn das Formatieren läuft auf einem eigenen Pfad, aber Precision as displayed verlässt sich auf dieses Flag, um Zeitwerte zu überspringen. Eine Fünf-Sekunden-Dauer ist 5/86400 eines Tages, etwa 0.0000579, und ein Format wie [ss].00 sieht dann wie eine gewöhnliche Zahl mit zwei Dezimalstellen aus; mit ausgeschaltetem FullPrecision rundet die Dauer also auf 0.00 Tage. HotXLS parst eine eingeklammerte Folge des einzelnen Buchstabens h, m oder s als Verstrichene-Zeit-Token und behandelt die Sektion als Datum/Zeit. Die Minutenerkennung in h:mm braucht dieselbe Sorgfalt, weil der Doppelpunkt zwischen den Tokens die Stunde vor einem Parser verbergen kann

HotXLS-Diagramm eines Fehlparsens verstrichener Zeit: Fünf Sekunden als winziger Tagesbruchteil in einer Zelle mit dem eingeklammerten ss-Token gespeichert, das ein Parser, der jede Nicht-Bedingungs-Klammer als Farbe behandelt, als schlichte Zahl mit zwei Dezimalstellen einstuft, sodass Precision as displayed die Dauer auf 0.00 runden würde, es sei denn, die Sektion wird als verstrichene Zeit geparst
Das Formatieren läuft auf eigenem Pfad, die Zelle kann also richtig aussehen, während der gespeicherte Wert auf null rundet; eine eingeklammerte Einzelbuchstaben-Folge h, m oder s ist ein Verstrichene-Zeit-Token, keine Farbe, und die Sektion behält volle Präzision

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, nicht die Zahl -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
  • Dezimalstellen: Dezimal-Platzhalter, plus zwei pro %, minus drei pro Skalierungskomma; die Anzahl kann negativ sein
  • Rundung: halb von null weg mit einer Few-ULP-Toleranz, sodass 2.5 zu 3 wird, -2.5 zu -3 und 1.005 zu 1.01
  • Übersprungen: General, Datum/Zeit und verstrichene Zeit, wissenschaftlich, Bruch, Text, Boolean und Fehlerwerte
  • Geltungsbereich in HotXLS: Formelergebnisse beim Berechnen; Konstanten werden wie zugewiesen gespeichert
  • XLSX-Engine: FullPrecision vor dem ersten Recalculate setzen; der Classic-Setter markiert alle Formeln selbst erneut als dirty
  • Referenz: Erwartungswerte in Excel 16 ausgemessen, von beiden Engines geteilt

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