Excel-breuknotatie en de kettingbreukregel in HotXLS

Formatteert u 3.14159 met het breukmasker # ??/??, dan toont Excel 3 1/7, terwijl 3 14/99 meer dan zeven keer zo dichtbij zit, dus een Delphi-export die naar de dichtstbijzijnde breuk zoekt drukt een waarde af die de gebruiker nooit te zien krijgt. De reden is dat Excel alleen kettingbreukconvergenten accepteert; in HotXLS geeft TXLSXCell.FormattedText dezelfde 3 1/7 terug, opvulspaties en al

Excel stopt bij kettingbreukconvergenten

Elk benaderend breukmasker geeft Excel dezelfde opdracht: vind een teller en een noemer voor het deel achter het geheel, met de noemer begrensd door het aantal cijferposities achter de slash. Excel doet dat door de waarde als kettingbreuk uit te werken en alleen de convergenten te houden, de breuken die u krijgt door die uitwerking na elke term af te kappen. Voor 0.14159 begint de uitwerking met [0; 7, 15, 1, ...], wat 1/7 oplevert, daarna 15/106, daarna 16/113. Het gehele getal 3 verandert niets. Daarmee loopt 3.14159 langs dezelfde 22/7, 333/106 en 355/113 die in elke tabel met benaderingen van pi staan. Omdat 106 al niet meer in een noemer van twee cijfers past, houdt Excel 1/7 over. De dichterbij zittende 14/99 is een semiconvergent, een van de tussengelegen breuken die tussen twee buurconvergenten in zitten. Excel bezoekt die nooit. U hebt daarvoor geen eigen code nodig: de types Up to one digit en Up to two digits in het Format Cells-dialoogvenster zijn de ingebouwde notatie-ID's 12 en 13, # ?/? en # ??/??, in de lijst met voorgedefinieerde getalnotaties van ECMA-376 Part 1 §18.8.30

De kloof blijft niet beperkt tot pi-achtige invoer. Beste benadering, doorgaans een Stern-Brocot-wandeling of een scan langs elke toegestane noemer, vraagt welke breuk het dichtstbij zit, terwijl de convergentlus vraagt welke breuk de uitwerking het eerst bereikt terwijl de noemer nog past. Op gewone invoer met twee decimalen gaan de twee regels vaak genoeg uit elkaar om te tellen, en 0.27 in de code hieronder is zo'n waarde. Schrijft uw code breuken weg in een PDF-factuur, een HTML-rapport of een CSV die iemand naast de werkmap zal leggen, kopieer dan de regel van Excel daarheen, ook al is dat de minder nauwkeurige. Een lezer die 3 14/99 op papier ziet en 3 1/7 in Excel concludeert niet dat uw export nauwkeuriger is. Hij concludeert dat een van de twee kapot is. Dat is Excel niet

De lus zelf is kort. Tel de cijferplaceholders achter de slash, of dat nu ?, # of 0 is, en de grens is 9, 99 of 999 bij één, twee of drie ervan. Een gemengd masker zoals # ??/?? splitst het geheel eraf en werkt alleen het breukdeel uit; een onecht masker zoals ?/? werkt de hele waarde uit, wat de reden is dat 3.14159 onder ?/? als 22/7 uitkomt. Elke stap neemt het gehele deel A van de actuele waarde B en vouwt het in de volgende convergent. De lus stopt zodra de rest B − A onder 5e-8 zakt, dus een zuivere breuk plus floating point-ruis landt nog steeds op die breuk. Duwt een stap de noemer voorbij de grens, dan valt Excel terug op de vorige convergent, en die terugval kan op 1/1 uitkomen, wat een gemengd masker in het geheel meeneemt, dus 12.9999 onder # ?/? toont 13. Hieronder staat de lus uitgeschreven in gewoon Delphi naast een brute-force zoektocht naar de dichtstbijzijnde breuk, zodat u kunt zien waar de twee op uw eigen getallen uiteenlopen

uses
  SysUtils;

type
  TFraction = record
    Num, Den: Int64;
  end;

// De convergentregel achter de Excel-maskers # ?/?, # ??/?? en # ???/???.
// X is niet-negatief: het breukdeel bij een gemengd masker, de hele waarde
// bij een onechte. MaxDen is 9, 99 of 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;      // convergent k-2
  P1 := 1; Q1 := 0;      // convergent 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;               // de rest telt als nul
    B := 1 / R;
    P2 := P1; Q2 := Q1;
    P1 := P;  Q1 := Q;
  end;
  if Q > MaxDen then       // voorbij de grens: één convergent terug
    if Q1 > MaxDen then
    begin
      P := P2; Q := Q2;
    end
    else
    begin
      P := P1; Q := Q1;
    end;
  Result.Num := P;
  Result.Den := Q;
end;

// Wat een routine voor "beste benadering" teruggeeft: de dichtstbijzijnde N/D met 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: bij gelijkspel wint de gereduceerde vorm
    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;

// Verwacht:
// 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/2

De regel met 0.27 staat in de lijst omdat ook gewone invoer met twee decimalen uiteenloopt, 10/37 tegenover 17/63, zonder dat er iets exotisch aan de waarde is. Hoe vaak de regels uiteenlopen hangt van uw data af: echte zuivere breuken zoals 0.5 of 0.375 komen beide kanten op hetzelfde uit, gemeten grootheden en berekende verhoudingen een stuk minder betrouwbaar. Trunc staat in voor Floor omdat de invoer nooit negatief is. Excel regelt het teken buiten de breuk om. Dat zou uw code ook moeten doen. En het argument moet de Double zijn die de cel echt bewaart, om een reden die de volgende sectie concreet maakt

Wat gebeurt er met 0.99 in een breuk van twee cijfers?

Excel toont 0.99 onder # ??/?? als ·98/99 (elke · in dit artikel staat voor één spatieteken). De oorzaak is binary floating point. Het literal 0.99 heeft geen exacte Double, en de bewaarde waarde zit er een haartje onder. 1/(1/0.99 − 1) evalueert daardoor op ruwweg 98.9999999999994 in plaats van 99, de tweede term van de uitwerking wordt 98, en de convergent 98/99 past onder de grens. Doet u dezelfde uitwerking met exacte decimalen, dan krijgt u [0; 1, 99]: die 99 duwt de noemer naar 100, de lus valt terug op 1/1, en de cel zou 1······ tonen. Hier is zorgvuldiger zijn dan Excel de bug. Een rapportgenerator die bedragen als Currency of TBcd bewaart, of de decimale string uit de database opnieuw parst en met exacte rekenkunde uitwerkt, drukt een heel 1 af waar Excel 98/99 drukt. Geef de formatter de bewaarde Double. Houd elke tussenstap in floating point. De breuk blijft ook een weergavezaak: formules zien nog steeds 0.99, en HotXLS houdt breuksecties op volledige precisie, zelfs in een werkmap met precision as displayed aangezet

Hoe Excel het breukgebied opvult en uitlijnt

Excel zet breuken neer zoals een kolom decimalen wordt neergezet: de teller staat rechtsuitgelijnd in zijn posities, de noemer linksuitgelijnd, zodat de slash op elke rij op dezelfde plek landt. Elke ongebruikte positie wordt volgens zijn placeholder gevuld. Een ? laat een spatie na, een 0 schrijft een nul, een # schrijft niets. Daarom leest 3.14159 onder # ??/?? als 3··1/7·: één spatie is de scheider uit het masker, één vult de lege tellerpositie vóór de 1, en één vult de lege noemerpositie achter de 7. De tabel somt de tekst op die Excel toont voor een set invoer die de opmaakregels dekt

Notatiecode	Waarde	Excel toont
# ?/?	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·

Hele getallen zijn waar handgeschreven formatters het verst van Excel afdwalen. Onder een gemengd masker waarvan de teller ? gebruikt houdt Excel de cijfers van het geheel aan, nul inbegrepen, en wisselt hij de scheider, de teller, de slash en de noemer elk voor één spatie om, dus 12 onder # ?/? wordt 12···· en onder # ??/100 volgen er zeven spaties. Zet de placeholder van de teller op # en de opvulling verdwijnt. Een geheel van nul werkt andersom: de #-positie drukt voor 0.5 niets af en de scheider overleeft het alleen als spatie, terwijl 0 ?/? het cijfer afdwingt, en onechte maskers laten de breuk helemaal nooit vallen

Wat u met die opvulling doet hangt af van waar de tekst heen gaat. Houd hem aan voor rapporten in monospace en voor rechtsuitgelijnde PDF-tabelcellen. De schuine strepen op één lijn krijgen is daar het hele doel. Knip hem weg voor CSV en JSON, waar een achterliggende spatie 12 in een string verandert die niet langer als getal parst. HTML vraagt ook om een keuze, want de browser schrapt reeksen spaties tenzij de cel white-space: pre gebruikt; zet dat aan of knip, maar vermijd half ingeklapte opvulling die sommige rijen verkeerd uitlijnt en andere niet

Vaste noemers zoals # ?/8

Een noemer die als cijfers vanaf 1 tot 9 is geschreven is een vaste waarde in plaats van een grens. De rekenkunde verandert volledig: Excel vermenigvuldigt de waarde met de noemer, rondt af op de dichtstbijzijnde hele teller en laat de breuk ongereduceerd. Onder 0/8 toont 0.375 3/8 en 0.64 toont 5/8, want 0.64 × 8 is 5.12. Het standaardtype As sixteenths toont 0.5 als 8/16, precies wat een machinist die een tekening leest verwacht en wat een formatter die elk resultaat reduceert tot 1/2 zou terugbrengen. Een afgeronde teller kan de noemer bereiken. Dan neemt een gemengd masker hem mee in het geheel: van 2.97 onder # ?/8 wordt verwacht dat hij 3···· leest, want 0.97 × 8 = 7.76 rondt naar boven af op een volle acht. Kies tussen de twee families op grond van wat de noemer voor de lezer betekent. Vaste noemers passen bij alles dat in vaste stappen wordt genoteerd, zoals imperiale lengtematen, boordiameters of oude koersen in achtsten. Niemand die houtmaten leest wil 3/7 inch. De benaderende maskers passen bij verhoudingen en gemeten waarden waar geen specifieke noemer betekenis draagt

De breuktekst van Excel in Delphi lezen met FormattedText

In HotXLS hebt u de lus zelf zelden nodig. TXLSXCell.FormattedText rendert de bewaarde waarde door de NumberFormat van de cel met TXLSFormater.GetDisplayValue uit lxStandard.pas, dus de convergentkeuze, de opvulling en eventuele letterlijke tekst komen samen in één string aan. De procedure hieronder koppelt paren van waarde en masker aan cellen en drukt elk resultaat tussen haakjes af, zodat achterliggende spaties zichtbaar blijven. Een nieuwe TXLSXWorkbook begint zonder werkbladen, wat de reden is dat de code Sheets.Add aanroept voordat hij een cel aanraakt; Sheets.Items en Cells[Row, Col] tellen allebei vanaf 1

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');   // een nieuwe werkmap heeft nog geen werkbladen
    for I := Low(Cases) to High(Cases) do
    begin
      Cell := Sheet.Cells[I + 1, 1];          // rijen en kolommen beginnen bij 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;

// Verwacht:
// # ??/??            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)]

De laatste twee gevallen tonen wat de breuk omringt. Gequote tekst en backslash-escapes drukken als gewone tekens af, en de opvulling van een hele waarde landt vóór het achtervoegsel, dus "x"# ?/?" y" maakt van -12 -x12·····y. Het minteken gaat vóór alles, prefix inbegrepen, tenzij een expliciete negatiesectie zoals (# ?/?) het teken zelf uitspelt; breuken volgen dezelfde sectie- en mintekenregels als elke andere Excel-getalnotatie. Komt de waarde uit een dataset in plaats van uit een cel, sla dan de werkmap over: TXLSFormater heeft een constructor zonder parameters, en GetDisplayValue(Value, NumberFormat) is de aanroep die FormattedText voor numerieke waarden doet

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, '# ?/?')                Verwacht: ' 1/2'
// FractionText(1234.75, '#,##0 ?/?')        Verwacht: '1,234 3/4'
// Trim(FractionText(12, '# ?/?'))           Verwacht: '12'  (CSV-veilig)

Elke breuk die uw Delphi-code buiten Excel afdrukt zou uit de convergentlus moeten komen of rechtstreeks uit FormattedText, gevoed met de eigen Double van de cel. Dan is de 3 1/7 op papier de 3 1/7 in het raster, en met de HotXLS Delphi spreadsheet component krijgt u die tekst zonder de lus ook maar te schrijven