Excel 分數格式為何停在連分數漸近分數

用分數格式代碼 # ??/?? 格式化 3.14159，Excel 顯示 3 1/7，儘管 3 14/99 近了七倍以上——於是搜尋最近分數的 Delphi 匯出功能，印出一個使用者永遠看不到的值。原因在於 Excel 只接受連分數漸近分數；在 HotXLS 裡，TXLSXCell.FormattedText 傳回的也是同一個 3 1/7，連補位的空格都一模一樣

Excel 停在連分數漸近分數

每一個近似分數格式代碼交給 Excel 的都是同一件事：為整數之後的部分找一個分子與一個分母，分母的上限由斜線後的位數槽位數決定。Excel 的做法是把值展開成連分數，只留下漸近分數——每取一項就切斷展開所得到的分數。0.14159 的展開從 [0; 7, 15, 1, ...] 開始，先得到 1/7，再 15/106，再 16/113。整數 3 不帶來任何改變。加上它，3.14159 走過的正是每份圓周率近似值表都找得到的 22/7、333/106 與 355/113。106 已經塞不進兩位數的分母，Excel 便留下 1/7。更近的 14/99 是半漸近分數，落在兩個相鄰漸近分數之間的中介分數之一。Excel 從不走訪那些。不必自寫程式碼也會撞上這件事：儲存格格式對話方塊裡的 Up to one digit 與 Up to two digits 類型，就是 ECMA-376 第一部 §18.8.30 預先定義數字格式清單中的內建格式 ID 12 與 13，也就是 # ?/? 與 # ??/??

這個落差不只發生在圓周率之類的輸入。最佳逼近——通常是 Stern-Brocot 走訪或對每個允許分母的掃描——問的是哪個分數最近；漸近分數迴圈問的則是，展開在分母還裝得下時最先抵達哪個分數。一般兩位小數的輸入上，兩種規則分歧的頻率足以造成影響，下面程式碼裡的 0.27 就是這樣一個值。如果您的程式碼要把分數寫進 PDF 發票、HTML 報表，或之後會有人拿來對照活頁簿的 CSV，就把 Excel 的規則複製過去，即使它是較不準的那個。在紙上看到 3 14/99、在 Excel 裡看到 3 1/7 的讀者，不會因此認定您的匯出更精準，他們會認定兩邊有一邊壞了。壞的不會是 Excel

迴圈本身不長。數一數斜線後的預留位置有幾個——?、# 或 0——一、二、三個的上限分別是 9、99、999。像 # ??/?? 這樣的帶分數式格式代碼會把整數切開，只展開分數部分；像 ?/? 這樣的假分數式格式代碼則展開整個值，3.14159 在 ?/? 下得出 22/7 就是這個原因。每一步取當前值 B 的整數部分 A，折進下一個漸近分數。餘數 B − A 一旦低於 5e-8，迴圈就停，所以單純分數加上浮點雜訊仍然落在那個分數上。當某一步把分母推過上限，Excel 退回上一個漸近分數，而這個退回可能落在 1/1；帶分數式格式代碼把它收進整數，於是 12.9999 在 # ?/? 下顯示 13。下面把這個迴圈以平實的 Delphi 寫出，旁邊配上暴力掃描的最近分數搜尋，您可以在自己的數字上看出兩者從哪裡分道揚鑣

uses
  SysUtils;

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

// Excel 的 # ?/?、# ??/?? 與 # ???/??? 格式代碼背後的漸近分數規則。
// X 非負：帶分數式代碼傳入分數部分，假分數式代碼傳入整個值。
// MaxDen 是 9、99 或 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;      // 漸近分數 k-2
  P1 := 1; Q1 := 0;      // 漸近分數 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;               // 餘數視為零
    B := 1 / R;
    P2 := P1; Q2 := Q1;
    P1 := P;  Q1 := Q;
  end;
  if Q > MaxDen then       // 超出上限：退回一個漸近分數
    if Q1 > MaxDen then
    begin
      P := P2; Q := Q2;
    end
    else
    begin
      P := P1; Q := Q1;
    end;
  Result.Num := P;
  Result.Den := Q;
end;

// 「最佳逼近」常式傳回者：D <= MaxDen 時最近的 N/D
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  // 嚴格比較：平手時由最簡形式勝出
    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;

// 預期結果：
// 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

0.27 那行會在清單裡，是因為平凡的兩位小數輸入照樣分歧：10/37 對上 17/63，這個值毫無奇特之處。規則分歧得多頻繁，取決於您的資料：0.5 或 0.375 這類真正的單純分數，兩種走法結果相同；實測量與算出來的比值就可靠得多。用 Trunc 頂替 Floor，是因為輸入永不為負。Excel 在分數之外處理正負號，您的程式碼也該如此。而參數必須是儲存格實際儲存的那個 Double，下一節會給出具體理由

0.99 在兩位數分數裡會怎樣？

Excel 在 # ??/?? 下把 0.99 顯示成 ·98/99（本文中每個 · 都代表一個空格字元）。肇因是二進位浮點數。字面值 0.99 沒有精確的 Double，儲存的值比它低了那麼一絲。於是 1/(1/0.99 − 1) 算出來約是 98.9999999999994 而非 99，展開的第二項變成 98，漸近分數 98/99 裝得進上限。用精確小數做同樣的展開，得到 [0; 1, 99]：那個 99 把分母推到 100，迴圈退回 1/1，儲存格會顯示成 1······。在這裡，比 Excel 更講究正是 bug。把金額用 Currency 或 TBcd 保存的報表產生器，或從資料庫重新解析小數字串、用精確算術展開的做法，會在 Excel 印出 98/99 的地方印出整數 1。餵給格式器的就是儲存的 Double，每個中間值都留在浮點數裡。分數也始終只是顯示層面的事：公式看到的仍是 0.99，而且即使在開啟以顯示精度為準的活頁簿裡，HotXLS 也讓分數區段保持完整精度

Excel 如何填補與對齊分數區

Excel 排版分數的方式，跟一欄小數的排版一樣：分子在其槽位內靠右對齊，分母靠左對齊，斜線於是落在每一列的同一位置。每個用不到的槽位按其預留位置填補。? 留一個空格，0 寫一個零，# 什麼都不寫。這就是 3.14159 在 # ??/?? 下讀作 3··1/7· 的原因：一個空格是格式代碼的分隔符，一個填補 1 前面的空分子槽位，一個填補 7 後面的空分母槽位。表格列出一組涵蓋排版規則的輸入在 Excel 顯示的文字

格式代碼	值	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·

整數正是手寫格式器與 Excel 走樣最遠的地方。在分子用 ? 的帶分數式格式代碼下，Excel 保留整數位數——零也算——並把分隔符、分子、斜線與分母各換成一個空格，於是 12 在 # ?/? 下變成 12····，在 # ??/100 下後面跟著七個空格。把分子的預留位置換成 #，填補就消失了。整數為零則走另一個方向：# 槽位對 0.5 什麼都不印，分隔符只以一個空格存活，而 0 ?/? 會強制印出那個位數；假分數式格式代碼更從不丟掉分數

這些填補怎麼處理，取決於文字要去哪裡。等寬文字報表與右對齊的 PDF 表格儲存格保留它，對齊斜線正是那裡的重點。CSV 與 JSON 則修剪掉——尾端空格會把 12 變成一個再也解析不成數字的字串。HTML 也需要做個決定，因為瀏覽器會折疊連續空格，除非儲存格用了 white-space: pre；設定它或修剪，別留下半折疊的填補，讓某些列對齊、另一些列不對齊

# ?/8 這類固定分母

以 1 到 9 開頭的數字寫成的分母是固定值，不是上限。算術整個改變：Excel 把值乘上分母，四捨五入到最接近的整數分子，分數不約分。在 0/8 下，0.375 顯示 3/8、0.64 顯示 5/8，因為 0.64 × 8 是 5.12。內建的 As sixteenths 類型把 0.5 顯示成 8/16——看圖施工的機械技工預期的正是這個，而對每個結果都約分的格式器會把它變成 1/2。四捨五入後的分子可能抵達分母。這時帶分數式格式代碼把它收進整數：2.97 在 # ?/8 下預期讀作 3····，因為 0.97 × 8 = 7.76，進位成一整個八。兩個家族怎麼選，看分母對讀者意味著什麼。固定分母適合一切以固定刻度報價的東西：英制長度、鑽頭規格、以八分之一報價的老式股價。讀木料尺寸的人不需要 3/7 英寸。近似式格式代碼適合比值與實測值——那裡沒有任何特定分母承載意義

在 Delphi 裡用 FormattedText 讀出 Excel 的分數文字

在 HotXLS 裡您很少需要自己寫那個迴圈。TXLSXCell.FormattedText 透過儲存格的 NumberFormat、用 lxStandard.pas 裡的 TXLSFormater.GetDisplayValue 渲染儲存的值，漸近分數的選擇、填補與任何文字常量一起抵達同一個字串。下面的程序把成對的值與格式代碼指派給儲存格，並把每個結果印在方括號之間，讓尾端空格維持可見。新的 TXLSXWorkbook 起始時沒有工作表，所以程式碼在碰儲存格之前先呼叫 Sheets.Add；Sheets.Items 與 Cells[Row, Col] 都以 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');   // 新活頁簿還沒有任何工作表
    for I := Low(Cases) to High(Cases) do
    begin
      Cell := Sheet.Cells[I + 1, 1];          // 列與欄從 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;

// 預期結果：
// # ??/??            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)]

最後兩個案例展示分數周遭的東西。引號文字與反斜線逸出按普通字元印出，而整個值的填補落在後綴之前，所以 "x"# ?/?" y" 把 -12 變成 -x12·····y。負號放在所有東西的最前面——前綴也算——除非像 (# ?/?) 這樣明確的負數區段自己寫出正負號；分數遵循與其他 Excel 數字格式相同的區段與負號規則。值來自資料集而非儲存格時，跳過活頁簿：TXLSFormater 有無參數建構函式，數值走的是 FormattedText 內部呼叫的 GetDisplayValue(Value, NumberFormat)

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, '# ?/?')                預期結果：' 1/2'
// FractionText(1234.75, '#,##0 ?/?')        預期結果：'1,234 3/4'
// Trim(FractionText(12, '# ?/?'))           預期結果：'12'（適合 CSV）

您的 Delphi 程式碼在 Excel 之外印出的任何分數，都應該來自漸近分數迴圈，或直接來自 FormattedText 並用儲存格自己的 Double 餵入。紙上的 3 1/7 於是就是格線裡的 3 1/7，而有了HotXLS Delphi spreadsheet component，連迴圈都不必寫就能拿到那段文字