Format fraction Excel : 3 1/7 pour 3.14159, pas 3 14/99

Formatez 3.14159 avec le masque de fraction # ??/?? et Excel affiche 3 1/7, alors que 3 14/99 est plus de sept fois plus proche, si bien qu'un export Delphi qui cherche la fraction la plus proche imprime une valeur que l'utilisateur ne voit jamais. La raison : Excel n'accepte que les convergentes de fraction continue ; dans HotXLS, TXLSXCell.FormattedText renvoie le même 3 1/7, espaces de remplissage compris

Excel s'arrête aux convergentes de fraction continue

Chaque masque de fraction approchante donne à Excel le même travail : trouver un numérateur et un dénominateur pour la partie après l'entier, avec le dénominateur plafonné par le nombre d'emplacements de chiffres après la barre oblique. Excel le fait en développant la valeur en fraction continue et en ne gardant que les convergentes, les fractions obtenues en coupant ce développement après chaque terme. Pour 0.14159 le développement commence [0; 7, 15, 1, ...], qui donne 1/7, puis 15/106, puis 16/113. L'entier 3 ne change rien. Avec lui, 3.14159 traverse les mêmes 22/7, 333/106 et 355/113 que l'on trouve dans toute table d'approximations de pi. Puisque 106 déborde déjà d'un dénominateur à deux chiffres, Excel garde 1/7. La 14/99 plus proche est une semi-convergente, une des fractions intermédiaires qui siègent entre deux convergentes voisines. Excel ne les visite jamais. Pas besoin de code personnalisé pour tomber dessus : les types Jusqu'à un chiffre et Jusqu'à deux chiffres de la boîte de dialogue Format de cellule sont les ID de format intégrés 12 et 13, # ?/? et # ??/??, dans la liste des formats de nombres prédéfinis de l'ECMA-376 Partie 1 §18.8.30

L'écart ne se limite pas aux entrées façon pi. La meilleure approximation, typiquement une marche de Stern-Brocot ou un balayage de tous les dénominateurs permis, demande quelle fraction est la plus proche, tandis que la boucle des convergentes demande quelle fraction le développement atteint en premier tant que le dénominateur tient encore. Sur des entrées ordinaires à deux décimales, les deux règles se séparent assez souvent pour compter, et le 0.27 du code ci-dessous est une telle valeur. Si votre code écrit des fractions dans une facture PDF, un rapport HTML ou un CSV que quelqu'un confrontera au classeur, copiez-y la règle d'Excel, même si c'est la moins précise. Un lecteur qui voit 3 14/99 sur le papier et 3 1/7 dans Excel ne conclut pas que votre export est plus précis. Il conclut que l'un des deux est cassé. Ce ne sera pas Excel

La boucle elle-même est courte. Comptez les emplacements de chiffres après la barre oblique, que ce soit ?, # ou 0, et le plafond vaut 9, 99 ou 999 pour un, deux ou trois d'entre eux. Un masque mixte tel que # ??/?? sépare l'entier et ne développe que la partie fractionnaire ; un masque impropre tel que ?/? développe la valeur entière, c'est pourquoi 3.14159 sous ?/? ressort en 22/7. Chaque étape prend la partie entière A de la valeur courante B et la replie dans la convergente suivante. La boucle s'arrête une fois que le reste B − A descend sous 5e-8, si bien qu'une fraction simple plus le bruit en virgule flottante atterrit quand même sur cette fraction. Quand une étape pousse le dénominateur au-delà du plafond, Excel retombe sur la convergente précédente, et cette retombée peut atterrir sur 1/1, qu'un masque mixte transporte dans l'entier, si bien que 12.9999 sous # ?/? affiche 13. Ci-dessous, la boucle est écrite en Delphi ordinaire à côté d'une recherche par force brute de la fraction la plus proche, pour que vous voyiez où les deux divergent sur vos propres nombres

uses
  SysUtils;

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

// La règle des convergentes derrière les masques Excel # ?/?, # ??/?? et # ???/???.
// X est non négatif : la partie fractionnaire pour un masque mixte, la valeur entière
// pour un masque impropre. MaxDen vaut 9, 99 ou 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;               // le reste compte pour zéro
    B := 1 / R;
    P2 := P1; Q2 := Q1;
    P1 := P;  Q1 := Q;
  end;
  if Q > MaxDen then       // plafond dépassé : on recule d'une 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;

// Ce que renvoie une routine de "meilleure approximation" : la N/D la plus proche avec 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  // strict : la forme réduite gagne les égalités
    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;

// Attendu :
// 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 ligne 0.27 est dans la liste parce qu'une entrée ordinaire à deux décimales diverge elle aussi, 10/37 contre 17/63, sans rien d'exotique dans la valeur. La fréquence à laquelle les règles se séparent dépend de vos données : les vraies fractions simples telles que 0.5 ou 0.375 ressortent identiques dans les deux cas, les quantités mesurées et les rapports calculés bien moins sûrement. Trunc tient lieu de Floor parce que l'entrée n'est jamais négative. Excel gère le signe en dehors de la fraction. Votre code devrait faire de même. Et l'argument doit être le Double que la cellule stocke réellement, pour une raison que la section suivante rend concrète

Que devient 0.99 dans une fraction à deux chiffres ?

Excel affiche 0.99 sous # ??/?? comme ·98/99 (chaque · dans cet article représente un caractère espace). La cause est la virgule flottante binaire. Le littéral 0.99 n'a pas de Double exact, et la valeur stockée siège un cheveu en dessous. Donc 1/(1/0.99 − 1) s'évalue à peu près à 98.9999999999994 au lieu de 99, le second terme du développement devient 98, et la convergente 98/99 tient sous le plafond. Faites le même développement avec des décimales exactes et vous obtenez [0; 1, 99] : ce 99 pousse le dénominateur à 100, la boucle retombe sur 1/1, et la cellule lirait 1······. Ici, être plus soigneux qu'Excel est le bug. Un générateur de rapports qui détient les montants en Currency ou TBcd, ou qui réanalyse la chaîne décimale depuis la base pour la développer en arithmétique exacte, imprime un 1 entier là où Excel imprime 98/99. Nourrissez le formateur avec le Double stocké. Gardez chaque intermédiaire en virgule flottante. La fraction reste aussi une affaire d'affichage : les formules voient toujours 0.99, et HotXLS garde les sections de fraction à pleine précision même dans un classeur avec la précision telle qu'affichée activée

Comment Excel remplit et aligne la zone de fraction

Excel dispose les fractions comme est disposée une colonne de décimales : le numérateur est aligné à droite dans ses emplacements, le dénominateur aligné à gauche, si bien que la barre oblique atterrit à la même position sur chaque ligne. Chaque emplacement inutilisé est rempli selon son espace réservé. Un ? laisse un espace, un 0 écrit un zéro, un # n'écrit rien. C'est pourquoi 3.14159 sous # ??/?? se lit 3··1/7· : un espace est le séparateur du masque, un remplit l'emplacement de numérateur vide devant le 1, et un remplit l'emplacement de dénominateur vide après le 7. Le tableau liste le texte qu'Excel affiche pour un ensemble d'entrées couvrant les règles de disposition

Code de format	Valeur	Excel affiche
# ?/?	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·

Les nombres entiers sont là où les formateurs écrits à la main dérivent le plus d'Excel. Sous un masque mixte dont le numérateur utilise ?, Excel garde les chiffres de l'entier, zéro compris, et échange le séparateur, le numérateur, la barre oblique et le dénominateur contre un espace chacun, si bien que 12 sous # ?/? devient 12···· et que sous # ??/100 il est suivi de sept espaces. Passez l'espace réservé du numérateur à # et le remplissage disparaît. Un entier zéro marche dans l'autre sens : l'emplacement # n'imprime rien pour 0.5 et le séparateur survit seulement comme espace, tandis que 0 ?/? force le chiffre, et les masques impropres n'abandonnent jamais la fraction du tout

Que faire de ce remplissage dépend de où va le texte. Gardez-le pour les rapports en texte à chasse fixe et pour les cellules de tableaux PDF alignées à droite. Y aligner les barres obliques est tout l'enjeu. Rognez-le pour le CSV et le JSON, où un espace final transforme 12 en une chaîne qui ne s'analyse plus comme un nombre. Le HTML demande lui aussi une décision, parce que le navigateur réduit les suites d'espaces sauf si la cellule utilise white-space: pre ; posez cela ou roguez, mais évitez un remplissage à moitié réduit qui désaligne certaines lignes et pas d'autres

Les dénominateurs fixes tels que # ?/8

Un dénominateur écrit en chiffres de 1 à 9 est une valeur fixe au lieu d'un plafond. L'arithmétique change complètement : Excel multiplie la valeur par le dénominateur, arrondit au numérateur entier le plus proche et laisse la fraction irréduite. Sous 0/8, 0.375 affiche 3/8 et 0.64 affiche 5/8, parce que 0.64 × 8 fait 5.12. Le type intégré As sixteenths affiche 0.5 en 8/16, ce à quoi s'attend un machiniste qui lit un plan, et ce qu'un formateur qui réduirait chaque résultat transformerait en 1/2. Un numérateur arrondi peut atteindre le dénominateur. Alors un masque mixte le transporte dans l'entier : 2.97 sous # ?/8 doit se lire 3····, puisque 0.97 × 8 = 7.76 s'arrondit à un huit plein. Choisissez entre les deux familles selon ce que le dénominateur signifie pour le lecteur. Les dénominateurs fixes conviennent à tout ce qui se cote en pas fixes, tels que les longueurs impériales, les tailles de forets ou les vieux cours d'actions en huitièmes. Personne qui lit des dimensions de bois ne veut 3/7 de pouce. Les masques approchants conviennent aux rapports et aux valeurs mesurées, où aucun dénominateur particulier ne porte de sens

Lire le texte de fraction d'Excel en Delphi avec FormattedText

Dans HotXLS vous avez rarement besoin de la boucle vous-même. TXLSXCell.FormattedText rend la valeur stockée à travers le NumberFormat de la cellule en utilisant TXLSFormater.GetDisplayValue de lxStandard.pas, si bien que le choix de la convergente, le remplissage et tout texte littéral arrivent ensemble dans une seule chaîne. La procédure ci-dessous assigne des paires valeur-masque à des cellules et imprime chaque résultat entre crochets pour que les espaces finaux restent visibles. Un nouveau TXLSXWorkbook démarre sans feuilles de calcul, c'est pourquoi le code appelle Sheets.Add avant de toucher à une cellule ; Sheets.Items et Cells[Row, Col] sont tous deux 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 nouveau classeur n'a pas encore de feuilles
    for I := Low(Cases) to High(Cases) do
    begin
      Cell := Sheet.Cells[I + 1, 1];          // les lignes et les colonnes commencent à 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;

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

Les deux derniers cas montrent ce qui entoure la fraction. Le texte entre guillemets et les échappements par barre oblique inverse s'impriment comme caractères simples, et le remplissage d'une valeur entière atterrit avant le suffixe, si bien que "x"# ?/?" y" tourne -12 en -x12·····y. Le signe moins va devant tout, préfixe compris, sauf si une section négative explicite telle que (# ?/?) épelle elle-même le signe ; les fractions suivent les mêmes règles de sections et de signe moins que tout autre format de nombre Excel. Quand la valeur vient d'un jeu de données plutôt que d'une cellule, sautez le classeur : TXLSFormater a un constructeur sans paramètre, et GetDisplayValue(Value, NumberFormat) est l'appel que FormattedText fait pour les valeurs numériques

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

Toute fraction que votre code Delphi imprime en dehors d'Excel devrait venir de la boucle des convergentes ou tout droit de FormattedText, nourri avec le Double propre à la cellule. Alors le 3 1/7 sur le papier est le 3 1/7 dans la grille, et avec le HotXLS Delphi spreadsheet component vous obtenez ce texte sans écrire la boucle du tout