Formatieren Sie 3.14159 mit der Bruchmaske # ??/??, zeigt Excel 3 1/7, obwohl 3 14/99 mehr als siebenmal näher liegt, also druckt ein Delphi-Export, der nach dem nächsten Bruch sucht, einen Wert, den der Nutzer nie zu sehen bekommt. Der Grund: Excel akzeptiert nur Konvergenten des Kettenbruchs; in HotXLS liefert TXLSXCell.FormattedText dieselben 3 1/7, Leerzeichen-Auffüllung inklusive
Excel hält bei den Kettenbruch-Konvergenten an
Jede annähernde Bruchmaske gibt Excel denselben Auftrag: einen Zähler und einen Nenner für den Teil hinter der Ganzzahl finden, mit dem Nenner begrenzt durch die Anzahl der Ziffern-Slots hinter dem Schrägstrich. Excel erledigt das, indem es den Wert als Kettenbruch entwickelt und nur die Konvergenten behält, die Brüche also, die man bekommt, wenn man diese Entwicklung nach jedem Glied abschneidet. Für 0.14159 beginnt die Entwicklung mit [0; 7, 15, 1, ...], was 1/7 liefert, dann 15/106, dann 16/113. Die Ganzzahl 3 ändert nichts. Mit ihr läuft 3.14159 dieselben 22/7, 333/106 und 355/113 durch, die in jeder Tabelle der Pi-Annäherungen stehen. Da 106 einen zweistelligen Nenner schon sprengt, behält Excel 1/7. Der nähere 14/99 ist ein semikonvergenter Bruch, einer der Zwischenbrüche, die zwischen zwei benachbarten Konvergenten sitzen. Excel besucht sie nie. Dafür braucht man keinen eigenen Code: Die Typen „Up to one digit“ und „Up to two digits“ im Dialog „Format Cells“ sind die eingebauten Format-IDs 12 und 13, # ?/? und # ??/??, in der vordefinierten Zahlenformat-Liste von ECMA-376 Part 1 §18.8.30
Die Lücke beschränkt sich nicht auf pi-artige Eingaben. Beste Approximation, typischerweise ein Stern-Brocot-Durchlauf oder ein Scan über jeden erlaubten Nenner, fragt, welcher Bruch am nächsten liegt, während die Konvergenten-Schleife fragt, welchen Bruch die Entwicklung zuerst erreicht, solange der Nenner noch passt. Bei gewöhnlichen Eingaben mit zwei Dezimalstellen gehen die beiden Regeln oft genug auseinander, um zu zählen, und 0.27 im Code unten ist so ein Wert. Schreibt Ihr Code Brüche in eine PDF-Rechnung, einen HTML-Bericht oder eine CSV, die jemand mit der Arbeitsmappe abgleichen wird, kopieren Sie dort Excels Regel, und zwar obwohl sie die ungenauere ist. Ein Leser, der auf dem Papier 3 14/99 sieht und in Excel 3 1/7, schließt nicht, dass Ihr Export präziser ist. Er schließt, dass eine der beiden Seiten kaputt ist. Es wird nicht Excel sein
Die Schleife selbst ist kurz. Zählen Sie die Ziffern-Platzhalter hinter dem Schrägstrich, ob ?, # oder 0, und die Obergrenze ist 9, 99 oder 999 für ein, zwei oder drei davon. Eine gemischte Maske wie # ??/?? spaltet die Ganzzahl ab und entwickelt nur den Bruchteil; eine unechte Maske wie ?/? entwickelt den ganzen Wert, weshalb 3.14159 unter ?/? als 22/7 herauskommt. Jeder Schritt nimmt den Ganzzahlteil A des laufenden Werts B und faltet ihn in den nächsten Konvergenten. Die Schleife stoppt, sobald der Rest B − A unter 5e-8 fällt, also landet auch ein simpler Bruch plus Gleitkomma-Rauschen auf genau jenem Bruch. Schiebt ein Schritt den Nenner über die Obergrenze, fällt Excel auf den vorherigen Konvergenten zurück, und dieser Rückfall kann auf 1/1 landen, was eine gemischte Maske in die Ganzzahl überträgt, also zeigt 12.9999 unter # ?/? 13. Unten steht die Schleife in plain Delphi neben einer Brute-Force-Suche nach dem nächsten Bruch, damit Sie sehen, wo die beiden auf Ihren eigenen Zahlen auseinanderlaufen
uses
SysUtils;
type
TFraction = record
Num, Den: Int64;
end;
// Die Konvergenten-Regel hinter Excels Masken # ?/?, # ??/?? und # ???/???.
// X ist nicht negativ: der Bruchteil für eine gemischte Maske, der ganze Wert
// für eine unechte. MaxDen ist 9, 99 oder 999.
function ExcelFraction(X: Double; MaxDen: Integer): TFraction;
var
B, R: Double;
A, P, Q, P1, Q1, P2, Q2: Int64;
begin
B := X;
P2 := 0; Q2 := 1; // Konvergent k-2
P1 := 1; Q1 := 0; // Konvergent k-1
P := 0; Q := 1;
while Q1 < MaxDen do
begin
A := Trunc(B);
P := A * P1 + P2;
Q := A * Q1 + Q2;
R := B - A;
if R < 5E-8 then
Break; // der Rest gilt als null
B := 1 / R;
P2 := P1; Q2 := Q1;
P1 := P; Q1 := Q;
end;
if Q > MaxDen then // Obergrenze überschritten: einen Konvergenten zurück
if Q1 > MaxDen then
begin
P := P2; Q := Q2;
end
else
begin
P := P1; Q := Q1;
end;
Result.Num := P;
Result.Den := Q;
end;
// Was eine "Best Approximation"-Routine liefert: den nächsten N/D mit D <= MaxDen
function NearestFraction(X: Double; MaxDen: Integer): TFraction;
var
D: Integer;
N: Int64;
Err, BestErr: Double;
begin
Result.Num := Round(X);
Result.Den := 1;
BestErr := Abs(X - Result.Num);
for D := 2 to MaxDen do
begin
N := Round(X * D);
Err := Abs(X - N / D);
if Err < BestErr then // streng: die gekürzte Form gewinnt bei Gleichstand
begin
BestErr := Err;
Result.Num := N;
Result.Den := D;
end;
end;
end;
procedure CompareFractionRules;
const
Samples: array[0..3] of Double = (0.14159, 0.99, 0.27, 0.5);
var
I: Integer;
E, N: TFraction;
begin
for I := Low(Samples) to High(Samples) do
begin
E := ExcelFraction(Samples[I], 99);
N := NearestFraction(Samples[I], 99);
Writeln(Format('%-8g Excel %d/%d nearest %d/%d',
[Samples[I], E.Num, E.Den, N.Num, N.Den]));
end;
end;
// Erwartet:
// 0.14159 Excel 1/7 nearest 14/99
// 0.99 Excel 98/99 nearest 98/99
// 0.27 Excel 10/37 nearest 17/63
// 0.5 Excel 1/2 nearest 1/2Die 0.27-Zeile steht in der Liste, weil auch eine gewöhnliche Eingabe mit zwei Dezimalstellen auseinanderläuft, 10/37 gegen 17/63, ohne dass an dem Wert etwas Exotisches wäre. Wie oft die Regeln auseinandergehen, hängt von Ihren Daten ab: echte einfache Brüche wie 0.5 oder 0.375 kommen beide Male gleich heraus, Messwerte und berechnete Verhältnisse weit unzuverlässiger. Trunc vertritt Floor, weil die Eingabe nie negativ ist. Excel behandelt das Vorzeichen außerhalb des Bruchs. Ihr Code sollte es genauso halten. Und das Argument muss das Double sein, das die Zelle tatsächlich speichert, aus einem Grund, den der nächste Abschnitt konkret macht
Was passiert mit 0.99 in einem zweistelligen Bruch?
Excel zeigt 0.99 unter # ??/?? als ·98/99 (jedes · steht in diesem Artikel für ein Leerzeichen). Die Ursache ist binäres Gleitkomma. Das Literal 0.99 hat kein exaktes Double, und der gespeicherte Wert sitzt ein Haar darunter. Also wertet 1/(1/0.99 − 1) rund 98.9999999999994 statt 99 aus, das zweite Glied der Entwicklung wird 98, und der Konvergent 98/99 passt unter die Obergrenze. Dieselbe Entwicklung mit exakten Dezimalzahlen ergibt [0; 1, 99]: Diese 99 schiebt den Nenner auf 100, die Schleife fällt auf 1/1 zurück, und die Zelle läse sich 1······. Hier ist es der Bug, sorgfältiger zu sein als Excel. Ein Reportgenerator, der Beträge als Currency oder TBcd hält, oder den Dezimal-String aus der Datenbank neu parst und mit exakter Arithmetik entwickelt, druckt eine runde 1, wo Excel 98/99 druckt. Füttern Sie den Formatierer mit dem gespeicherten Double. Halten Sie jedes Zwischenergebnis in Gleitkomma. Der Bruch bleibt auch eine reine Anzeigesache: Formeln sehen weiterhin 0.99, und HotXLS hält Bruch-Abschnitte in voller Präzision, selbst in einer Arbeitsmappe mit eingeschalteter Genauigkeit wie angezeigt
Wie Excel den Bruchbereich auffüllt und ausrichtet
Excel setzt Brüche so, wie eine Spalte von Dezimalzahlen gesetzt wird: Der Zähler ist rechtsbündig in seinen Slots, der Nenner linksbündig, also landet der Schrägstrich in jeder Zeile an derselben Position. Jeden ungenutzten Slot füllt sein Platzhalter. Ein ? lässt ein Leerzeichen, ein 0 schreibt eine Null, ein # schreibt nichts. Deshalb liest sich 3.14159 unter # ??/?? als 3··1/7·: Ein Leerzeichen ist der Trenner aus der Maske, eines füllt den leeren Zähler-Slot vor der 1, und eines füllt den leeren Nenner-Slot hinter der 7. Die Tabelle listet den Text, den Excel für eine Reihe von Eingaben zeigt, die die Layout-Regeln abdecken
| Formatcode | Wert | Excel zeigt |
|---|---|---|
# ?/? | 0.5 | ·1/2 |
# ?/? | 4/3 | 1·1/3 |
# ??/?? | 3.14159 | 3··1/7· |
# ??/?? | 0.99 | ·98/99 |
# ?/? | 12 | 12···· |
# ?/? | 0 | 0···· |
# ?/? | 12.9999 | 13···· |
# #/# | 12 | 12 |
0 ?/? | 0.5 | 0·1/2 |
#,##0 ?/? | 1234.75 | 1,234·3/4 |
?/? | 3.14159 | 22/7 |
?/? | 0 | 0/1 |
??/?? | 12 | 12/1· |
Ganze Zahlen sind der Punkt, an dem handgeschriebene Formatierer am weitesten von Excel abdriften. Unter einer gemischten Maske mit ? als Zähler-Platzhalter behält Excel die Ganzzahl-Ziffern, Nullen eingeschlossen, und tauscht Trenner, Zähler, Schrägstrich und Nenner gegen je ein Leerzeichen, also wird 12 unter # ?/? zu 12···· und unter # ??/100 folgen ihm sieben Leerzeichen. Schaltet man den Zähler-Platzhalter auf #, verschwindet die Auffüllung. Eine Null als Ganzzahl funktioniert umgekehrt: Der #-Slot druckt für 0.5 nichts, und der Trenner überlebt nur als Leerzeichen, während 0 ?/? die Ziffer erzwingt, und unechte Masken lassen den Bruch niemals ganz weg
Was man mit dieser Auffüllung tut, hängt davon ab, wohin der Text geht. Behalten Sie sie für Monospace-Textberichte und rechtsbündige PDF-Tabellenzellen. Die Schrägstriche in eine Linie zu bringen ist dort der ganze Sinn. Entfernen Sie sie für CSV und JSON, wo ein abschließendes Leerzeichen 12 in einen String verwandelt, der nicht mehr als Zahl parst. Auch HTML braucht eine Entscheidung, denn der Browser schrumpft Leerzeichen-Gruppen, sofern die Zelle nicht white-space: pre nutzt; setzen Sie das oder kürzen Sie, aber vermeiden Sie halb geschrumpfte Auffüllung, die einige Zeilen verschiebt und andere nicht
Feste Nenner wie # ?/8
Ein als Ziffern geschriebener Nenner beginnend mit 1 bis 9 ist ein fester Wert statt einer Obergrenze. Die Arithmetik ändert sich komplett: Excel multipliziert den Wert mit dem Nenner, rundet auf den nächsten ganzen Zähler und lässt den Bruch ungekürzt. Unter 0/8 zeigt 0.375 3/8 und 0.64 zeigt 5/8, denn 0.64 × 8 ist 5.12. Der eingebaute Typ As sixteenths zeigt 0.5 als 8/16, was ein Maschinenbauer beim Zeichnungslesen erwartet und was ein Formatierer, der jedes Ergebnis kürzt, zu 1/2 machen würde. Ein gerundeter Zähler kann den Nenner erreichen. Dann trägt eine gemischte Maske ihn in die Ganzzahl: 2.97 unter # ?/8 soll als 3···· erscheinen, denn 0.97 × 8 = 7.76 rundet auf ein volles Achtel hoch. Wählen Sie zwischen den beiden Familien danach, was der Nenner für den Leser bedeutet. Feste Nenner passen zu allem, was in festen Schritten angegeben wird, etwa imperialen Längen, Bohrer-Größen oder alten Aktienkursen in Achteln. Niemand, der Holzmaße liest, will 3/7 Zoll. Die annähernden Masken passen zu Verhältnissen und Messwerten, bei denen kein bestimmter Nenner eine Bedeutung trägt
Excels Bruchtext in Delphi mit FormattedText lesen
In HotXLS brauchen Sie die Schleife selten selbst. TXLSXCell.FormattedText rendert den gespeicherten Wert durch das NumberFormat der Zelle mit TXLSFormater.GetDisplayValue aus lxStandard.pas, also kommen Konvergenten-Wahl, Auffüllung und jeder Literaltext zusammen in einem String an. Die Prozedur unten weist Zellen Paare aus Wert und Maske zu und druckt jedes Ergebnis zwischen Klammern, damit abschließende Leerzeichen sichtbar bleiben. Eine neue TXLSXWorkbook startet ohne Arbeitsblätter, weshalb der Code Sheets.Add aufruft, bevor er eine Zelle anfasst; Sheets.Items und Cells[Row, Col] sind beide 1-basiert
uses
SysUtils, lxHandleX;
type
TFractionCase = record
Mask: string;
Value: Double;
end;
const
Cases: array[0..8] of TFractionCase = (
(Mask: '# ??/??'; Value: 3.14159),
(Mask: '# ??/??'; Value: 0.99),
(Mask: '# ?/?'; Value: 12),
(Mask: '# ?/?'; Value: 12.9999),
(Mask: '# #/#'; Value: 12),
(Mask: '?/?'; Value: 3.14159),
(Mask: '0/8'; Value: 0.64),
(Mask: '"x"# ?/?" y"'; Value: -12),
(Mask: '# ?/?;(# ?/?)'; Value: -1.5));
procedure ShowFractionText;
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Cell: TXLSXCell;
I: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Fractions'); // eine neue Arbeitsmappe hat noch keine Blätter
for I := Low(Cases) to High(Cases) do
begin
Cell := Sheet.Cells[I + 1, 1]; // Zeilen und Spalten beginnen bei 1
Cell.Value := Cases[I].Value;
Cell.NumberFormat := Cases[I].Mask;
Writeln(Format('%-16s %9g [%s]',
[Cases[I].Mask, Cases[I].Value, Cell.FormattedText]));
end;
finally
Book.Free;
end;
end;
// Erwartet:
// # ??/?? 3.14159 [3 1/7 ]
// # ??/?? 0.99 [ 98/99]
// # ?/? 12 [12 ]
// # ?/? 12.9999 [13 ]
// # #/# 12 [12]
// ?/? 3.14159 [22/7]
// 0/8 0.64 [5/8]
// "x"# ?/?" y" -12 [-x12 y]
// # ?/?;(# ?/?) -1.5 [(1 1/2)]Die letzten beiden Fälle zeigen, was den Bruch umgibt. Text in Anführungszeichen und Backslash-Escapes erscheinen als schlichte Zeichen, und die Auffüllung eines ganzen Werts landet vor dem Suffix, also verwandelt "x"# ?/?" y" -12 in -x12·····y. Das Minuszeichen rückt vor alles, Präfix eingeschlossen, sofern nicht ein expliziter negativer Abschnitt wie (# ?/?) das Vorzeichen selbst ausschreibt; Brüche folgen denselben Abschnitts- und Minuszeichen-Regeln wie jedes andere Excel-Zahlenformat. Kommt der Wert aus einem Dataset statt aus einer Zelle, lassen Sie die Arbeitsmappe weg: TXLSFormater hat einen parameterlosen Konstruktor, und GetDisplayValue(Value, NumberFormat) ist der Aufruf, den FormattedText für numerische Werte macht
uses
SysUtils, lxStandard;
function FractionText(const AValue: Double; const AMask: WideString): WideString;
var
Fmt: TXLSFormater;
begin
Fmt := TXLSFormater.Create;
try
Result := Fmt.GetDisplayValue(AValue, AMask);
finally
Fmt.Free;
end;
end;
// FractionText(0.5, '# ?/?') Erwartet: ' 1/2'
// FractionText(1234.75, '#,##0 ?/?') Erwartet: '1,234 3/4'
// Trim(FractionText(12, '# ?/?')) Erwartet: '12' (CSV-tauglich)Jeder Bruch, den Ihr Delphi-Code außerhalb von Excel druckt, sollte aus der Konvergenten-Schleife kommen oder direkt aus FormattedText, gefüttert mit dem eigenen Double der Zelle. Dann sind die 3 1/7 auf dem Papier dieselben 3 1/7 im Grid, und mit der HotXLS-Delphi-Spreadsheet-Komponente bekommen Sie diesen Text, ohne die Schleife überhaupt zu schreiben