Formati frazione di Excel e frazioni continue in HotXLS

Formattate 3.14159 con la maschera di frazione # ??/?? ed Excel mostra 3 1/7, benché 3 14/99 sia più di sette volte più vicino, così un export Delphi che cerca la frazione più vicina stampa un valore che l'utente non vede mai. La ragione è che Excel accetta solo i convergenti della frazione continua; in HotXLS, TXLSXCell.FormattedText restituisce lo stesso 3 1/7, spazi di riempimento compresi

Excel si ferma ai convergenti della frazione continua

Ogni maschera di frazione per approssimare dà ad Excel lo stesso compito: trovare un numeratore e un denominatore per la parte dopo l'intero, col denominatore limitato dal numero di slot per cifre dopo la barra. Excel ci arriva espandendo il valore come frazione continua e tenendo solo i convergenti, le frazioni che si ottengono troncando quell'espansione dopo ciascun termine. Per 0.14159 l'espansione parte con [0; 7, 15, 1, ...], che dà 1/7, poi 15/106, poi 16/113. L'intero 3 non cambia nulla. Con lui 3.14159 passa per gli stessi 22/7, 333/106 e 355/113 che si trovano in ogni tabella delle approssimazioni di pi greco. Dato che 106 sborda già un denominatore a due cifre, Excel tiene 1/7. Il più vicino 14/99 è un semiconvergente, una delle frazioni intermedie che siedono tra due convergenti confinanti. Excel non visita mai quelle. Non serve codice su misura per inciamparci: i tipi Up to one digit e Up to two digits della finestra di dialogo Format Cells sono gli ID di formato predefiniti 12 e 13, # ?/? e # ??/??, nell'elenco dei formati numerici predefiniti di ECMA-376 Part 1 §18.8.30

Il divario non riguarda solo input alla pi greco. La migliore approssimazione, tipicamente un percorso Stern-Brocot o una scansione su ogni denominatore ammesso, chiede quale frazione sia la più vicina, mentre il ciclo dei convergenti chiede quale frazione l'espansione raggiunga per prima mentre il denominatore ci sta ancora. Su input ordinari a due decimali le due regole divergono abbastanza spesso da contare, e 0.27 nel codice qui sotto è uno di questi valori. Se il vostro codice scrive frazioni in una fattura PDF, un report HTML o un CSV che qualcuno metterà a confronto col foglio di lavoro, copiate lì la regola di Excel, anche se è la meno precisa. Un lettore che vede 3 14/99 sulla carta e 3 1/7 in Excel non conclude che il vostro export sia più esatto. Conclude che uno dei due è rotto. Non sarà Excel

Il ciclo in sé è breve. Contate i segnaposto per cifre dopo la barra, che siano ?, # o 0, e il tetto è 9, 99 o 999 per uno, due o tre di loro. Una maschera mista come # ??/?? stacca l'intero ed espande solo la parte frazionaria; una maschera impropria come ?/? espande l'intero valore, ecco perché 3.14159 sotto ?/? esce come 22/7. Ogni passo prende la parte intera A del valore corrente B e la ripiega nel convergente successivo. Il ciclo si ferma appena il resto B − A scende sotto 5e-8, così una frazione semplice più il rumore in floating point atterra comunque su quella frazione. Quando un passo spinge il denominatore oltre il tetto, Excel ripiega sul convergente precedente, e quel ripiego può atterrare su 1/1, che una maschera mista porta nell'intero, così 12.9999 sotto # ?/? mostra 13. Qui sotto il ciclo è scritto in Delphi puro accanto a una ricerca a forza bruta della frazione più vicina, così vedete dove le due divergono sui vostri numeri

uses
  SysUtils;

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

// La regola dei convergenti dietro le maschere # ?/?, # ??/?? e # ???/??? di Excel
// X non è negativo: la parte frazionaria per una maschera mista, il valore intero
// per una maschera impropria. MaxDen vale 9, 99 o 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;      // convergente k-2
  P1 := 1; Q1 := 0;      // convergente 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;               // il resto conta come zero
    B := 1 / R;
    P2 := P1; Q2 := Q1;
    P1 := P;  Q1 := Q;
  end;
  if Q > MaxDen then       // tetto superato: si ripiega di un convergente
    if Q1 > MaxDen then
    begin
      P := P2; Q := Q2;
    end
    else
    begin
      P := P1; Q := Q1;
    end;
  Result.Num := P;
  Result.Den := Q;
end;

// Cosa restituisce una routine di migliore approssimazione: la N/D più vicina con 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  // severo: la forma ridotta vince i pareggi
    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;

// Atteso:
// 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

La riga 0.27 è nell'elenco perché anche un input ordinario a due decimali diverge, 10/37 contro 17/63, senza nulla di esotico nel valore. Quanto spesso le regole divergono dipende dai vostri dati: le frazioni semplici vere come 0.5 o 0.375 escono uguali in entrambi i modi, le grandezze misurate e i rapporti calcolati molto meno affidabilmente. Trunc fa le veci di Floor perché l'input non è mai negativo. Excel tratta il segno fuori dalla frazione. Anche il vostro codice dovrebbe. E l'argomento deve essere il Double che la cella conserva davvero, per un motivo che la sezione successiva rende concreto

Cosa succede a 0.99 in una frazione a due cifre?

Excel mostra 0.99 sotto # ??/?? come ·98/99 (ogni · in questo articolo sta per un carattere spazio). La causa è il floating point binario. Il letterale 0.99 non ha un Double esatto, e il valore conservato siede un pelo più in basso. Così 1/(1/0.99 − 1) restituisce circa 98.9999999999994 invece di 99, il secondo termine dell'espansione diventa 98, e il convergente 98/99 ci sta sotto il tetto. Fate la stessa espansione con decimali esatti e ottenete [0; 1, 99]: quel 99 spinge il denominatore a 100, il ciclo ripiega su 1/1, e la cella leggerebbe 1······. Qui, essere più precisi di Excel è il bug. Un generatore di report che tiene gli importi come Currency o TBcd, o riparsa la stringa decimale dal database e la espande con aritmetica esatta, stampa un bel 1 intero dove Excel stampa 98/99. Date al formattatore il Double conservato. Tenete ogni intermedio in floating point. La frazione resta una questione di visualizzazione, anche: le formule vedono ancora 0.99, e HotXLS mantiene le sezioni di frazione a piena precisione persino in un foglio di lavoro con la precision as displayed attiva

Come Excel riempie e allinea l'area della frazione

Excel dispone le frazioni come viene disposta una colonna di decimali: il numeratore è allineato a destra nei suoi slot, il denominatore a sinistra, così la barra atterra nella stessa posizione su ogni riga. Ogni slot inutilizzato viene riempito secondo il suo segnaposto. Un ? lascia uno spazio, uno 0 scrive uno zero, un # non scrive niente. Ecco perché 3.14159 sotto # ??/?? legge 3··1/7·: uno spazio è il separatore della maschera, uno riempie lo slot vuoto del numeratore davanti all'1, e uno riempie lo slot vuoto del denominatore dopo il 7. La tabella elenca il testo che Excel visualizza per un insieme di input che copre le regole di layout

Codice di formato	Valore	Excel mostra
# ?/?	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·

I numeri interi sono dove i formattatori scritti a mano deviano di più da Excel. Sotto una maschera mista il cui numeratore usa ?, Excel conserva le cifre dell'intero, zero compreso, e scambia separatore, numeratore, barra e denominatore per uno spazio ciascuno, così 12 sotto # ?/? diventa 12···· e sotto # ??/100 è seguito da sette spazi. Cambiate il segnaposto del numeratore in # e il riempimento sparisce. Un intero zero funziona al contrario: lo slot # non stampa niente per 0.5 e il separatore sopravvive solo come spazio, mentre 0 ?/? forza la cifra, e le maschere improprie non buttano mai via la frazione

Cosa fare con quel riempimento dipende da dove va il testo. Tenetelo per i report di testo a spaziatura fissa e per le celle di tabelle PDF allineate a destra. Allineare le barre è tutto il punto, lì. Tagliatelo per CSV e JSON, dove uno spazio finale trasforma 12 in una stringa che non si lascia più interpretare come numero. Anche HTML richiede una decisione, perché il browser collassa le sequenze di spazi a meno che la cella usi white-space: pre; impostatelo o tagliate, ma evitate un riempimento mezzo collassato che disallinea alcune righe e altre no

Denominatori fissi come # ?/8

Un denominatore scritto come cifre da 1 a 9 è un valore fisso invece che un tetto. L'aritmetica cambia alla radice: Excel moltiplica il valore per il denominatore, arrotonda al numeratore intero più vicino e lascia la frazione non ridotta. Sotto 0/8, 0.375 mostra 3/8 e 0.64 mostra 5/8, perché 0.64 × 8 fa 5.12. Il tipo di serie As sixteenths mostra 0.5 come 8/16, che è ciò che si attende un meccanico che legge un disegno tecnico e ciò che un formattatore che riduce ogni risultato trasformerebbe in 1/2. Un numeratore arrotondato può raggiungere il denominatore. Allora una maschera mista lo porta nell'intero: 2.97 sotto # ?/8 deve leggere 3····, visto che 0.97 × 8 = 7.76 si arrotonda a un otto pieno. Scegliete tra le due famiglie in base a cosa il denominatore significa per il lettore. I denominatori fissi stanno bene a tutto ciò che si cita a passi fissi, come lunghezze imperiali, punte di trapano o vecchi prezzi di azioni in ottavi. Nessuno che legge dimensioni di legname vuole 3/7 di pollice. Le maschere approssimanti stanno bene a rapporti e grandezze misurate dove nessun denominatore particolare porta significato

Leggere il testo frazione di Excel in Delphi con FormattedText

In HotXLS di rado vi serve il ciclo per conto vostro. TXLSXCell.FormattedText renderizza il valore conservato attraverso il NumberFormat della cella usando TXLSFormater.GetDisplayValue da lxStandard.pas, così la scelta del convergente, il riempimento e ogni testo letterale arrivano insieme in una sola stringa. La procedura sotto assegna coppie di valore e maschera alle celle e stampa ogni risultato tra parentesi quadre, così gli spazi finali restano visibili. Un nuovo TXLSXWorkbook parte senza fogli di lavoro, ecco perché il codice chiama Sheets.Add prima di toccare una cella; Sheets.Items e Cells[Row, Col] sono entrambi a base 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');   // un nuovo workbook non ha ancora fogli
    for I := Low(Cases) to High(Cases) do
    begin
      Cell := Sheet.Cells[I + 1, 1];          // righe e colonne partono da 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;

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

Gli ultimi due casi mostrano cosa circonda la frazione. Il testo tra virgolette e le escape con backslash stampano come caratteri semplici, e il riempimento di un valore intero atterra prima del suffisso, così "x"# ?/?" y" trasforma -12 in -x12·····y. Il segno meno va davanti a tutto, prefisso compreso, a meno che una sezione negativa esplicita come (# ?/?) non scriva il segno per conto suo; le frazioni seguono le stesse regole di sezioni e di segno meno di ogni altro formato numerico di Excel. Quando il valore arriva da un dataset anziché da una cella, saltate il workbook: TXLSFormater ha un costruttore senza parametri, e GetDisplayValue(Value, NumberFormat) è la chiamata che FormattedText fa per i valori numerici

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, # ?/?)                Atteso: uno spazio poi 1/2
// FractionText(1234.75, #,##0 ?/?)        Atteso: 1,234 3/4
// Trim(FractionText(12, # ?/?))           Atteso: 12 senza spazi, adatto a CSV

Ogni frazione che il vostro codice Delphi stampa fuori da Excel dovrebbe venire dal ciclo dei convergenti o direttamente da FormattedText, nutrita col Double della cella stessa. Così il 3 1/7 sulla carta è il 3 1/7 nella griglia, e con lo strumento fogli di calcolo HotXLS per Delphi quel testo lo avete senza scrivere il ciclo affatto