HotXLS: dlaczego Excel pokazuje 3 1/7 zamiast 3 14/99

Sformatuj 3.14159 maską ułamkową # ??/??, a Excel pokaże 3 1/7, choć 3 14/99 jest ponad siedem razy bliżej, więc eksport z Delphi, który szuka najbliższego ułamka, wypisuje wartość, której użytkownik nigdy nie zobaczy. Powód: Excel przyjmuje wyłącznie konwergenty ułamka łańcuchowego; w HotXLS TXLSXCell.FormattedText zwraca te same 3 1/7, razem ze spacjami dopełnienia

Excel zatrzymuje się na konwergentach ułamka łańcuchowego

Każda przybliżająca maska ułamkowa daje Excelowi tę samą robotę: znajdź licznik i mianownik dla części po liczbie całkowitej, z mianownikiem ograniczonym liczbą miejsc na cyfry po ukośniku. Excel robi to, rozwijając wartość w ułamek łańcuchowy i trzymając wyłącznie konwergenty — ułamki, które dostajesz, ucinając to rozwinięcie po każdym wyrazie. Dla 0.14159 rozwinięcie zaczyna się od [0; 7, 15, 1, ...], co daje 1/7, potem 15/106, potem 16/113. Liczba całkowita 3 nic nie zmienia. Z nią 3.14159 przechodzi przez te same 22/7, 333/106 i 355/113, które znajdziesz w każdej tabeli przybliżeń pi. Ponieważ 106 już przelewa dwucyfrowy mianownik, Excel zostawia 1/7. Bliższe 14/99 to konwergent pośredni, jeden z ułamków pośrednich leżących między dwoma sąsiednimi konwergentami. Excel nigdy tam nie zagląda. Nie potrzebujesz własnego kodu, żeby na to wpaść: typy Up to one digit i Up to two digits w oknie Format Cells to wbudowane identyfikatory formatów 12 i 13, # ?/? i # ??/??, z listy predefiniowanych formatów liczbowych ECMA-376 Part 1 §18.8.30

Rozjazd nie ogranicza się do danych w stylu pi. Najlepsze przybliżenie — zwykle przejście po drzewie Stern-Brocota albo przegląd każdego dozwolonego mianownika — pyta, który ułamek jest najbliższy, a pętla konwergentów pyta, który ułamek rozwinięcie osiągnie pierwsze, póki mianownik jeszcze mieści się w limicie. Na zwyczajnych danych z dwoma miejscami po przecinku obie reguły rozjeżdżają się wystarczająco często, żeby to miało znaczenie, i 0.27 w kodzie poniżej to jedna z takich wartości. Jeśli twój kod zapisuje ułamki do faktury PDF, raportu HTML albo CSV, z którym ktoś będzie zestawiał arkusz, przepisz tam regułę Excela, choć jest mniej dokładna. Czytelnik, który widzi 3 14/99 na papierze i 3 1/7 w Excelu, nie stwierdza, że twój eksport jest bardziej precyzyjny. Stwierdza, że jedno z dwojga jest popsute. Nie będzie to Excel

Sama pętla jest krótka. Policz znaczniki cyfr po ukośniku — czy to ?, #, czy 0 — i limitem jest 9, 99 albo 999 dla jednego, dwóch lub trzech z nich. Maska mieszana, jak # ??/??, odcina część całkowitą i rozwija tylko część ułamkową; maska niewłaściwa, jak ?/?, rozwija całą wartość, dlatego 3.14159 pod ?/? wychodzi jako 22/7. Każdy krok bierze część całkowitą A bieżącej wartości B i wplata ją w następny konwergent. Pętla zatrzymuje się, gdy reszta B − A spadnie poniżej 5e-8, więc prosty ułamek plus szum zmiennoprzecinkowy i tak ląduje na tym ułamku. Gdy krok wypchnie mianownik za limit, Excel cofa się do poprzedniego konwergenta, a ten fallback może wylądować na 1/1, co maska mieszana wciąga w część całkowitą, więc 12.9999 pod # ?/? pokazuje 13. Poniżej pętla jest zapisana w czystym Delphi obok przeszukiwania brute force najbliższego ułamka, więc widzisz, gdzie obie drogi rozjeżdżają się na twoich własnych liczbach

uses
  SysUtils;

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

// Reguła konwergentów za maskami Excela # ?/?, # ??/?? i # ???/???.
// X jest nieujemne: część ułamkowa dla maski mieszanej, cała wartość
// dla niewłaściwej. MaxDen to 9, 99 albo 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;      // konwergent k-2
  P1 := 1; Q1 := 0;      // konwergent 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;               // reszta liczy się jako zero
    B := 1 / R;
    P2 := P1; Q2 := Q1;
    P1 := P;  Q1 := Q;
  end;
  if Q > MaxDen then       // przelew limitu: krok wstecz o jeden konwergent
    if Q1 > MaxDen then
    begin
      P := P2; Q := Q2;
    end
    else
    begin
      P := P1; Q := Q1;
    end;
  Result.Num := P;
  Result.Den := Q;
end;

// Co zwraca procedura "najlepszego przybliżenia": najbliższe N/D z 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  // ścisłe: przy remisie wygrywa postać skrócona
    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;

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

Wiersz 0.27 jest na liście, bo zwyczajne dane z dwoma miejscami też się rozjeżdżają — 10/37 kontra 17/63 — bez niczego egzotycznego w tej wartości. Jak często reguły się rozjeżdżają, zależy od twoich danych: prawdziwe proste ułamki, jak 0.5 albo 0.375, wychodzą tak samo w obie strony, wielkości zmierzone i wyliczone proporcje już znacznie mniej pewnie. Trunc występuje w roli Floor, bo dane wejściowe nigdy nie są ujemne. Excel traktuje znak poza ułamkiem. Twój kod też powinien. A argumentem musi być ten Double, który komórka faktycznie trzyma — powód następna sekcja zrobi konkretnym

Co się dzieje z 0.99 w ułamku dwucyfrowym?

Excel pokazuje 0.99 pod # ??/?? jako ·98/99 (każde · w tym artykule stoi za jeden znak spacji). Przyczyną jest binarna arytmetyka zmiennoprzecinkowa. Literał 0.99 nie ma dokładnego Double, a wartość zapisana siedzi o włos niżej. Dlatego 1/(1/0.99 − 1) wychodzi z grubsza 98.9999999999994 zamiast 99, drugi wyraz rozwinięcia staje się 98, a konwergent 98/99 mieści się pod limitem. Zrób to samo rozwinięcie na dokładnych dziesiętnych i dostaniesz [0; 1, 99]: ta 99 wypycha mianownik do 100, pętla cofa się do 1/1, a komórka czytałaby się 1······. Tutaj bycie dokładniejszym niż Excel jest błędem. Generator raportów, który trzyma kwoty jako Currency albo TBcd, albo ponownie parsuje ciąg dziesiętny z bazy i rozwija go arytmetyką dokładną, wypisze pełną jedynkę tam, gdzie Excel wypisuje 98/99. Podawaj formaterowi zapisany Double. Trzymaj każdy krok pośredni w zmiennoprzecinku. Ułamek dalej jest sprawą wyświetlania: formuły nadal widzą 0.99, a HotXLS trzyma sekcje ułamkowe w pełnej precyzji nawet w skoroszycie z włączoną dokładnością jak na ekranie

Jak Excel dopełnia i wyrównuje obszar ułamka

Excel układa ułamki tak, jak układa się kolumnę liczb dziesiętnych: licznik jest wyrównany do prawej w swoich miejscach, mianownik do lewej, więc ukośnik ląduje w tej samej pozycji w każdym wierszu. Każde nieużywane miejsce jest wypełniane według swojego znacznika. ? zostawia spację, 0 pisze zero, # nie pisze niczego. Dlatego 3.14159 pod # ??/?? czyta się 3··1/7·: jedna spacja to separator z maski, jedna wypełnia puste miejsce licznika przed jedynką, a jedna wypełnia puste miejsce mianownika po siódemce. Tabela zbiera tekst, który Excel pokazuje dla serii danych wejściowych pokrywających reguły układu

Kod formatu	Wartość	Pokazuje Excel
# ?/?	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·

Całkowite liczby to miejsce, gdzie formatery pisane ręcznie odjeżdżają od Excela najdalej. Pod maską mieszaną, której licznik używa ?, Excel zachowuje cyfry całkowite, razem z zerem, i zamienia separator, licznik, ukośnik i mianownik na po jednej spacji, więc 12 pod # ?/? staje się 12····, a pod # ??/100 następuje po tym siedem spacji. Przełącz znacznik licznika na #, a dopełnienie znika. Zerowa część całkowita działa w drugą stronę: miejsce # nie wypisuje niczego dla 0.5 i separator przeżywa tylko jako spacja, podczas gdy 0 ?/? wymusza cyfrę, a maski niewłaściwe nigdy nie wyrzucają ułamka w ogóle

Co zrobić z tym dopełnieniem, zależy od tego, dokąd idzie tekst. Zachowaj go dla raportów tekstowych w czcionce o stałej szerokości i dla komórek tabel PDF wyrównanych do prawej. Tam ustawianie ukośników w linii to cały sens. Przytnij go dla CSV i JSON, gdzie spacja na końcu zamienia 12 w ciąg, który już nie parsuje się jako liczba. HTML też wymaga decyzji, bo przeglądarka zwija biegi spacji, chyba że komórka używa white-space: pre; ustaw to albo przytnij, ale unikaj półzwiniętego dopełnienia, które rozjeżdża niektóre wiersze, a inne nie

Sztywne mianowniki, takie jak # ?/8

Mianownik zapisany cyframi od 1 do 9 to wartość sztywna zamiast limitu. Arytmetyka zmienia się kompletnie: Excel mnoży wartość przez mianownik, zaokrągla do najbliższego całkowitego licznika i zostawia ułamek nieskrócony. Pod 0/8 0.375 pokazuje 3/8, a 0.64 pokazuje 5/8, bo 0.64 × 8 to 5.12. Wbudowany typ As sixteenths pokazuje 0.5 jako 8/16 — dokładnie to, czego oczekuje fachowiec czytający rysunek techniczny i co formater skracający każdy wynik zamieniłby na 1/2. Zaokrąglony licznik potrafi dojść do mianownika. Wtedy maska mieszana wciąga go w część całkowitą: od 2.97 pod # ?/8 oczekuje się odczytu 3····, bo 0.97 × 8 = 7.76 zaokrągla się w górę do pełnej ósemki. Między tymi dwiema rodzinami wybieraj według tego, co mianownik znaczy dla czytelnika. Sztywne mianowniki pasują do wszystkiego kwotowanego w sztywnych krokach — długości imperialne, rozmiary wierteł albo stare ceny akcji w ósmych. Nikt czytający wymiary tarcicy nie chce 3/7 cala. Maski przybliżające pasują do proporcji i wartości zmierzonych, gdzie żaden konkretny mianownik nie niesie znaczenia

Odczyt tekstu ułamka Excela w Delphi przez FormattedText

W HotXLS sam rzadko potrzebujesz pętli. TXLSXCell.FormattedText renderuje zapisaną wartość przez NumberFormat komórki za pomocą TXLSFormater.GetDisplayValue z lxStandard.pas, więc wybór konwergenta, dopełnienie i każdy tekst literałowy przychodzą razem w jednym ciągu. Poniższa procedura przypisuje do komórek pary wartość-maska i wypisuje każdy wynik między nawiasami, żeby końcowe spacje zostały widoczne. Nowy TXLSXWorkbook startuje bez arkuszy, dlatego kod woła Sheets.Add, zanim dotknie komórki; Sheets.Items i Cells[Row, Col] liczą oba od 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');   // nowy skoroszyt nie ma jeszcze arkuszy
    for I := Low(Cases) to High(Cases) do
    begin
      Cell := Sheet.Cells[I + 1, 1];          // wiersze i kolumny startują od 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;

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

Ostatnie dwa przypadki pokazują, co otacza ułamek. Tekst w cudzysłowach i ukośnikowe sekwencje escape wypisują się jako zwykłe znaki, a dopełnienie całkowitej wartości ląduje przed sufiksem, więc "x"# ?/?" y" zamienia -12 na -x12·····y. Znak minus idzie przed wszystkim, prefiks wliczony, chyba że jawna sekcja ujemna, jak (# ?/?), pisze znak sama; ułamki trzymają te same reguły sekcji i znaku minus co każdy inny format liczbowy Excela. Gdy wartość pochodzi ze zbioru danych, a nie z komórki, pomiń skoroszyt: TXLSFormater ma konstruktor bezparametrowy, a GetDisplayValue(Value, NumberFormat) to wywołanie, które FormattedText robi dla wartości liczbowych

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

Każdy ułamek, który twój kod w Delphi wypisuje poza Excelem, powinien pochodzić z pętli konwergentów albo prosto z FormattedText, karmionego własnym Double komórki. Wtedy 3 1/7 na papierze to 3 1/7 w siatce, a z komponentem arkuszy kalkulacyjnych HotXLS dla Delphi dostajesz ten tekst bez pisania pętli w ogóle