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 Part 1 §18.8.30의 미리 정의된 표시 형식 목록에서 내장 서식 ID 12와 13, 곧 # ?/?와 # ??/??입니다

이 간격은 원주율 같은 입력에만 한정되지 않습니다. 최적 근사(best approximation), 보통 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;               // 나머지를 0으로 칩니다
    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)은 99 대신 대략 98.9999999999994로 계산되고, 전개의 두 번째 항은 98이 되며, 수렴분수 98/99는 상한 안에 들어옵니다. 같은 전개를 정확한 십진수로 하면 [0; 1, 99]가 나옵니다. 그 99가 분모를 100으로 밀고, 루프는 1/1로 물러서고, 셀은 1······을 읽게 됩니다. 여기서 Excel보다 더 신중한 것이 바로 버그입니다. 금액을 Currency나 TBcd로 쥐고 있거나 데이터베이스의 십진수 문자열을 다시 해석해 정확한 산술로 전개하는 리포트 생성기는 Excel이 98/99를 인쇄하는 자리에 온전한 1을 인쇄합니다. 포매터에는 저장된 Double을 먹이십시오. 중간값은 모두 부동 소수점에 두십시오. 분수는 표시 문제로 남습니다. 수식은 여전히 0.99를 보고, HotXLS는 표시된 대로 정밀도가 켜진 통합 문서에서도 분수 구역을 온전한 정밀도로 지킵니다

Excel은 분수 영역을 어떻게 패딩하고 정렬하는가

Excel은 분수를 십진수 열을 배치하는 방식으로 배치합니다. 분자는 자신의 자리에서 오른쪽 정렬, 분모는 왼쪽 정렬이라 슬래시가 모든 행에서 같은 위치에 떨어집니다. 쓰이지 않은 자리는 자리표시자에 따라 채워집니다. ?는 공백을 남기고, 0은 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은 0을 포함한 정수 자릿수를 지키고 구분자와 분자, 슬래시, 분모를 각각 공백 하나로 바꿉니다. 그래서 # ?/? 아래의 12는 12····가 되고 # ??/100 아래에서는 공백 일곱 개가 뒤따릅니다. 분자 자리표시자를 #로 바꾸면 패딩이 사라집니다. 0인 정수는 반대로 갑니다. # 자리는 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로 만들어 버립니다. 반올림된 분자는 분모에 닿을 수 있습니다. 그러면 대분수 마스크가 그것을 정수로 흡수합니다. # ?/8 아래의 2.97은 3····로 읽히는 것이 기대되는데, 0.97 × 8 = 7.76이 온전한 여덟으로 올림되기 때문입니다. 두 계열 사이의 선택은 분모가 독자에게 무슨 의미인지로 합니다. 고정 분모는 야드파운드 길이나 드릴 규격, 8분의 1 단위의 옛 주식 가격처럼 고정 단계로 언급되는 무엇이든 맞습니다. 목재 치수를 읽는 사람은 3/7인치를 원하지 않습니다. 근사 마스크는 어느 특정 분모도 의미를 지니지 않는 비율과 측정값에 어울립니다

FormattedText로 Delphi에서 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 밖에서 인쇄하는 모든 분수는 수렴분수 루프에서 오거나 셀 자신의 Double을 먹인 FormattedText에서 곧장 와야 합니다. 그래야 종이의 3 1/7이 격자의 3 1/7이고, HotXLS Delphi spreadsheet component가 있으면 루프를 짜지 않고도 그 텍스트를 얻습니다