기술 문서

HotXLS 표시 정밀도: Excel 반올림 규칙

Excel의 표시 정밀도(precision as displayed)는 저장된 숫자를 숫자 서식이 보여 주는 소수 자릿수로 반올림합니다: 값의 부호에 맞는 서식 구간 선택, %마다 소수 둘 추가, 천 단위 스케일링 쉼표마다 셋 감소, 0에서 멀어지는 쪽으로의 half-away-from-zero 반올림. HotXLS는 TXLSXWorkbook.FullPrecision이나 TXLSWorkbook.UseFullPrecision이 False일 때 두 Delphi 엔진 모두에서 같은 규칙을 적용합니다. 한 줄 요약처럼 들리지만, 고객이 내보낸 송장 합계가 Excel과 1센트 어긋난다거나 [ss].00 구간의 소요 시간 열이 0으로 무너졌다고 보고하는 순간 이야기가 달라집니다. 둘 다 규칙 하나를 잘못 적용한 데서 옵니다. 두 HotXLS 엔진은 하나의 구현을 공유하며, 그 기대 값들은 Workbook.PrecisionAsDisplayed를 켠 상태의 Excel 16에서 측정되었습니다

표시 정밀도는 통합 문서에서 실제로 무엇을 바꿀까?

표시 정밀도는 계산 엔진에게 숫자를 계산된 대로가 아니라 보이는 대로 저장하라고 말하는 통합 문서 수준 플래그 하나입니다. Excel UI에서는 파일, 옵션, 고급, "이 통합 문서를 계산할 때" 아래 "표시 정밀도로 설정"에 있습니다. 디스크에서는 비트 하나입니다. BIFF8 파일은 CalcPrecision 레코드($000E, [MS-XLS] §2.4.35)에 담아 옮기며, 그 fFullPrec 필드는 평소의 전체 정밀도에서는 1, 옵션이 켜지면 0입니다. XLSX 패키지는 workbook.xml의 calcPr 요소의 fullPrecision 속성으로 옮기는데, ECMA-376 Part 1에서 정의되며 기본값은 true이고 fullPrecision="0"이 반올림을 켭니다

이 플래그는 표시 기본 설정이 아닙니다. 상자에 체크하면 Excel은 데이터가 영구적으로 정확도를 잃는다고 경고하고, 진심입니다. 값이 표시 정밀도로 다시 쓰이고 잘려나간 자릿수는 사라집니다. 나중에 상자를 풀어도 옛 자릿수는 돌아오지 않습니다. 12.3%로 표시된 0.1234는 영원히 0.123이 됩니다

HotXLS는 두 형식 모두에서 플래그를 읽고 쓰며 두 엔진 모두에서 노출합니다:

  • XLSX 엔진의 TXLSXWorkbook.FullPrecision: Boolean, calcPr/@fullPrecision에서 읽고 저장
  • 클래식 엔진의 TXLSWorkbook.UseFullPrecision: Boolean(IXLSWorkbook에도 있음), CalcPrecision 레코드에서 읽고 저장
  • 둘 다 기본값은 True. 안전한 비파괴 모드이며 Excel의 기본값이기도 합니다

HotXLS가 반올림을 어디에 적용하는지가 중요합니다. HotXLS는 값을 계산하는 지점에서 반올림합니다. Recalculate 중과 요청 시 평가 중에, 각 수식 결과는 셀의 캐시 값으로 저장되기 전에 표시 정밀도로 반올림됩니다. Value로 대입하는 상수는 준 그대로 정확히 저장됩니다. 출력이 상자에 체크한 뒤 Excel이 저장하는 것을 재현해야 한다면, 그 상수들은 쓰기 전에 직접 반올림하세요. 나중에 보여 줄 헬퍼로 하면 됩니다

Excel은 몇 자리의 소수를 유지할지 어떻게 정할까?

Excel은 유지할 소수 자릿수를 서식 문자열 전체가 아니라 값을 표시하는 특정 서식 구간에서 유도합니다. 아래 규칙은 Excel 16에서 측정되었으며, 두 HotXLS 엔진을 위해 lxNumFormat의 XlsApplyDisplayedPrecision이 구현하는 바로 그것입니다

  1. 부호로 구간을 고른다. 구간 두 개인 서식은 음수 값에 두 번째 구간을 씁니다. 구간이 셋 이상인 서식은 음수에 두 번째, 정확히 0에 세 번째를 씁니다. 그 외에는 첫 구간을 씁니다
  2. 소수 자리 표시자를 센다. 해당 구간에서 소수점 뒤의 0, #, ? 하나하나가 유지할 소수 하나를 더합니다
  3. 퍼센트 기호마다 둘을 더한다. 0.0%는 0.1234를 12.3%로 보여 주므로 저장 값은 보이는 것의 백분의 일이며 소수 하나가 아니라 셋을 유지합니다
  4. 스케일링 쉼표마다 셋을 뺀다. 마지막 정수 자리 표시자 뒤의 쉼표(0,, 0.0,, 0,.0)는 표시를 1000으로 나눕니다. 0.0,은 12345.678을 12.3으로 보여 주므로 Excel은 소수 하나 빼기 셋, 즉 음수 개수를 유지합니다. 값은 백의 자리로 반올림되어 12300으로 저장됩니다. #,##0에서처럼 정수 자리 표시자 사이의 쉼표는 단순 자릿수 묶음이며 아무것도 바꾸지 않습니다
  5. 비수치 구간은 그대로 둔다. General, 날짜·시간 구간(경과 시간 [h], [mm], [ss] 포함), 지수, 분수, 텍스트 구간, 그리고 숫자 자리 표시자가 전혀 없는 구간은 전체 정밀도를 유지합니다
HotXLS 표시 정밀도 규칙 다이어그램: 값의 부호로 서식 구간을 고르고, 소수점 뒤의 자릿수 표시자를 세고, 백분율 기호마다 소수 둘을 더하고, 천 단위 스케일링 쉼표마다 셋을 빼서 개수가 음수가 될 수 있게 하며, General과 날짜 시간 구간은 완전히 건너뛴 뒤 0에서 멀어지는 쪽으로 반올림함
자릿수 개수는 부호에 맞는 구간에서 나오며 백분율마다 둘을 더하고 스케일링 쉼표마다 셋을 빼므로, 음수 개수는 십이나 백 단위로 반올림합니다. General과 날짜 구간은 그대로 둡니다

Excel 16과 비교해 측정한 결과, 다음은 각 서식에서 수식 결과에 대해 두 HotXLS 엔진 모두가 저장하는 값입니다:

숫자 서식계산 값저장 값적용 규칙
0.0%0.12340.123소수 하나 + 퍼센트 기호분 둘
02.530에서 멀어지는 쪽, 짝수 쪽이 아님
0-2.5-3음수 쪽에서도 0에서 멀어지는 쪽
0.00;(0.0)-1.2345-1.2음수 구간은 소수 하나 표시
0.00;(0.0)1.23451.23양수 구간은 소수 둘 표시
#,##0.01234.56781234.6묶음 쉼표, 스케일링 없음
0.0,12345.67812300소수 하나 빼기 셋: 백의 자리로 반올림
0.0%;(0.00%)-0.0125-0.0125음수 구간은 둘 + 퍼센트분 둘을 유지
0.001.0051.01이진 표현 오차에 대한 허용 오차
0;-0;0.00.510이 아니므로 양수 구간이 결정

마지막 행은 제법 좋은 함정입니다. 0.5는 정수로 반올림되고 0 구간은 전혀 개입하지 않는데, Excel이 반올림 전의 계산 값으로 구간을 고르기 때문입니다. HotXLS는 부호로 구간을 고르므로, [>=1000]처럼 구간이 대괄호 조건을 담고 있는 서식이라면 저장 값이 중요할 때 Excel과 비교해 보십시오

1.005는 왜 1.00이 아니라 1.01로 반올림될까?

Excel은 0.00 셀의 1.005를 1.01로 반올림합니다. 1.005에 가장 가까운 double이 중간점보다 살짝 아래에 있음에도요. HotXLS는 몇 ulp 허용 오차로 그것에 맞춥니다. 리터럴 1.005는 이진 부동 소수점으로 표현될 수 없습니다. 가장 가까운 IEEE 754 double은 1.00499999999999989341858963598497211933135986328125이고, 100을 곱하면 100.49999999999999가 됩니다. 교과서식 Floor(x * 100 + 0.5) / 100는 그래서 1.00을 돌려주는데, 이는 사용자가 입력한 숫자와도, Excel이 표시하는 것과도, Excel이 저장하는 것과도 어긋납니다

Delphi는 자기 방식을 더합니다. System.Round는 동률을 짝수 쪽으로 반올림하므로 Round(2.5)는 2이고 Round(3.5)는 4입니다. 은행가 반올림이죠. 통계에는 합리적인 기본값이지만 여기서는 잘못된 규칙입니다. Excel은 0 셀의 2.5에 대해 3을, -2.5에 대해 -3을 저장합니다. HotXLS 구현은 절댓값으로 동작하고, 스케일된 값의 2-51 배에 해당하는 상대 허용 오차(그 크기에서 몇 ulp, 1.0의 ulp 둘보다 작지 않음)를 더한 0.5를 더한 뒤, 버리고, 다시 스케일하고, 부호를 되살립니다. 다음 함수는 그 원리의 자기 완결적인 삽화이지 라이브러리 코드 자체는 아니며, 스케일링 쉼표의 음수 자릿수 개수도 같은 방식으로 다룹니다:

HotXLS 반올림 다이어그램: 2.5는 0에서 멀어지는 쪽으로 3에, -2.5는 -3에 반올림되는데 Delphi System.Round는 은행가 답인 2와 -2를 주고, 1.005에 가장 가까운 double이 중간점 바로 아래에 있으므로 몇 ulp 허용 오차가 floor 기반의 1.00을 Excel 답인 1.01로 바꿈
Excel은 동률을 0에서 멀어지는 쪽으로 반올림하고 작은 허용 오차로 이진 표현 오류를 용서합니다. 둘 다 측정 가능한 세부이며, 어느 하나를 빼뜨리면 2.5에 대해 2를, 1.005에 대해 1.00을 저장해 Excel과 1센트 어긋납니다
// 원리 스케치: 0에서 멀어지는 쪽으로 ADigits 자릿수에 반올림,
// 몇 ulp 허용 오차로 1.005가 1.01에 닿게 함.
// ADigits < 0이면 십, 백 단위로 반올림, ... ("0.0,"은 -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, 1.0의 ulp 둘
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // double 정밀도 한계 초과: 값을 그대로 둠
  Scale := 1;
  for I := 1 to Abs(ADigits) do
    Scale := Scale * 10;
  if ADigits >= 0 then
  begin
    if Abs(AValue) > 1E300 / Scale then
      Exit; // 스케일링하면 오버플로
    Scaled := Abs(AValue) * Scale;
  end
  else
    Scaled := Abs(AValue) / Scale;
  Eps := Scaled * Tolerance;
  if Eps < Tolerance then
    Eps := Tolerance;
  Scaled := Int(Scaled + 0.5 + Eps); // 0에서 멀어지는 쪽, Round() 아님
  if ADigits >= 0 then
    Result := Scaled / Scale
  else
    Result := Scaled * Scale;
  if AValue < 0 then
    Result := -Result;
end;

// RoundAsDisplayed(1.005, 2)      = 1.01   (Floor 기반: 1.00)
// RoundAsDisplayed(2.5, 0)        = 3      (Round: 2)
// RoundAsDisplayed(-2.5, 0)       = -3
// RoundAsDisplayed(0.1234, 3)     = 0.123  ("0.0%": 1 + 2자리)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3자리)

허용 오차는 의도된 절충입니다. half 단계보다 정말로 2ulp 아래인 값도 올림됩니다. 하지만 그 정도 거리라면 차이는 표현 오차와 구분이 불가능하고, 그것을 half 단계로 다루는 것이 입력된 소수가 사용자가 기대하는 방식으로 동작하게 만드는 길입니다

어떤 구현 함정이 표시 정밀도를 깨뜨리는가?

세 가지 함정이 표시 정밀도 불일치의 대부분을 설명하며, 각각은 화면과 어긋난 값을 저장합니다. 규칙을 직접 구현하거나 옵션을 켠 채 생성된 통합 문서를 감사한다면 다음 증상들을 찾으십시오

첫 구간만 보고, 퍼센트 무시, 은행가 반올림

유혹적인 지름길은 서식 문자열 전체의 소수 자릿수를 묻는 것입니다. 첫 구간만 보고 %를 무시한 뒤 Round로 반올림합니다. 그러면 0.0%의 0.1234는 0.1로 저장되는데, 화면의 12.3%가 아니라 10%입니다. 0의 2.5는 3이 아니라 2로 저장됩니다. 0.00;(0.0) 같은 서식의 음수 값은 양수 구간의 소수 둘로 반올림됩니다. HotXLS는 두 엔진을 부호로 구간을 고르고, 퍼센트 기호와 스케일링 쉼표를 세고, 0에서 멀어지는 쪽으로 반올림하는 하나의 공유 루틴으로 돌립니다

TRUE가 -1이 되는 문제

반올림을 VarIsNumeric으로 가드하는 것이 두 번째 함정입니다. VarIsNumeric은 varBoolean Variant에 대해서도 True를 돌려줍니다. 그 Variant를 Double(V)로 변환하면 -1이 나옵니다. COM 방식의 Boolean True는 -1로 저장되기 때문입니다. 0.00 서식 셀의 =A1>0 수식은 재계산에서 숫자 -1로 나옵니다. HotXLS는 어떤 수치 검사보다 먼저 Boolean 결과를 걸러 내므로, 논리 결과는 두 엔진 모두에서 논리 결과로 남습니다

경과 시간 서식이 색으로 읽히는 문제

세 번째 함정은 반올림이 아니라 숫자 서식 모델에 있습니다. 조건이 아닌 대괄호 토큰을 전부 색으로 분류하는 파서는 [h], [mm], [ss] 구간을 날짜/시간으로 표시하지 않습니다. 표시는 영향받지 않습니다. 서식은 별도 경로로 돌기 때문입니다. 하지만 표시 정밀도는 그 플래그에 의존해 시간 값을 건너뜁니다. 5초짜리 소요 시간은 하루의 5/86400, 약 0.0000579이고, [ss].00 같은 서식은 그때 평범한 소수 둘짜리 숫자처럼 보이므로 FullPrecision이 꺼지면 소요 시간은 0.00일로 반올림됩니다. HotXLS는 h, m, s 글자 하나짜리 대괄호 실행을 경과 시간 토큰으로 파싱하고 해당 구간을 날짜/시간으로 취급합니다. h:mm에서의 분 감지도 같은 주의가 필요합니다. 토큰 사이의 콜론이 파서에게 시(hour)를 가려 버릴 수 있기 때문입니다

HotXLS 경과 시간 오파싱 다이어그램: 대괄호 ss 토큰으로 서식된 셀에 5초가 아주 작은 날짜 분수로 저장되고, 조건이 아닌 대괄호를 전부 색으로 취급하는 파서는 이를 평범한 소수 둘짜리 숫자로 분류하여, 구간이 경과 시간으로 파싱되지 않으면 표시 정밀도가 소요 시간을 0.00으로 반올림하는 모습
서식은 자기 경로로 돌기 때문에 저장 값이 0으로 반올림되는 동안에도 셀은 옳아 보일 수 있습니다. h, m, s 글자 하나짜리 대괄호는 색이 아니라 경과 시간 토큰이며, 해당 구간은 전체 정밀도를 유지합니다

Delphi에서 HotXLS의 표시 정밀도 켜기

Excel과 동등한 저장 값을 얻으려면, 그것을 존중할 재계산 전에 플래그를 설정한 뒤 캐시된 결과를 읽거나 저장하세요. XLSX 엔진에서 FullPrecision은 평범한 플래그입니다. 바꿔도 이전 Recalculate가 이미 저장한 결과를 무효화하지 않으므로, Create나 Open 직후, 첫 Recalculate 전에 설정하세요. 예제는 수식을 씁니다. HotXLS가 반올림을 적용하는 지점이 거기이기 때문입니다:

var
  Wb: TXLSXWorkbook;
  Sh: TXLSXWorksheet;
begin
  Wb := TXLSXWorkbook.Create;
  try
    Sh := Wb.Sheets.Add('Totals');
    Sh.Cells[1, 1].Value := 0.1234;
    Sh.Cells[2, 1].Value := 2.5;
    Sh.Cells[3, 1].Value := 12345.678;

    Sh.Cells[1, 2].Formula := '=A1';
    Sh.Cells[1, 2].NumberFormat := '0.0%';   // 12.3% 표시
    Sh.Cells[2, 2].Formula := '=A2';
    Sh.Cells[2, 2].NumberFormat := '0';      // 3 표시
    Sh.Cells[3, 2].Formula := '=A3';
    Sh.Cells[3, 2].NumberFormat := '0.0,';   // 12.3 표시(천 단위)

    // XLSX 엔진에서 첫 Recalculate 전에 설정해야 함
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // 캐시된 결과가 이제 Excel 16과 일치: 0.123, 3, 12300.
    // A열의 상수는 전체 정밀도를 유지합니다.
    Assert(Abs(Double(Sh.Cells[1, 2].Value) - 0.123) < 1E-12);
    Assert(Double(Sh.Cells[2, 2].Value) = 3);
    Assert(Double(Sh.Cells[3, 2].Value) = 12300);

    Wb.SaveAs('totals.xlsx'); // <calcPr fullPrecision="0"/>를 씀
  finally
    Wb.Free;
  end;
end;

클래식 엔진은 같게 동작하되 편의가 하나 있습니다. TXLSWorkbook.UseFullPrecision을 대입하면 의존성 그래프의 모든 수식이 dirty로 표시되어, 다음 Recalculate가 새 규칙 아래에서 통합 문서 전체를 다시 평가합니다. 옵션이 켜진 동안 NumberFormat을 바꾸면 영향받는 수식 셀도 dirty로 표시됩니다. 이제 서식이 저장 값을 결정하기 때문입니다. 클래식 Recalculate는 평가하지 못한 수식 셀 개수를 돌려주므로 0이 성공을 뜻한다는 점에 유의하세요:

var
  Wb: TXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  try
    Sh := Wb.Sheets.Add;
    Sh.Range['A1', 'A1'].Value := -1.2345;
    Sh.Range['B1', 'B1'].Formula := '=A1';
    Sh.Range['B1', 'B1'].NumberFormat := '0.00;(0.0)';
    Sh.Range['C1', 'C1'].Formula := '=A1<0';
    Sh.Range['C1', 'C1'].NumberFormat := '0.00';

    Wb.UseFullPrecision := False; // 모든 수식을 dirty로 표시
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: 음수 구간 "(0.0)"은 소수 하나를 표시한다
    // C1은 숫자 -1이 아니라 Boolean True로 남는다
    Wb.SaveAs('report.xls'); // fFullPrec = 0인 CalcPrecision 레코드
  finally
    Wb.Free;
  end;
end;

두 엔진 모두 파일과 함께 들어온 플래그도 존중합니다. 옵션을 켠 채 저장된 통합 문서를 열면 FullPrecision이나 UseFullPrecision이 이미 False이므로, 불러온 뒤의 Recalculate는 Excel이 그러하듯 정확히 반올림합니다. Excel이 이미 저장한 숫자를 읽기만 하면 되는 경우라면 재계산을 완전히 건너뛸 수 있습니다. 재계산 없이 캐시된 수식 값 읽기에서 기술하듯이요. 일련 번호와 날짜 서식이 날짜/시간 검사를 움직이는 서식 모델과 어떻게 상호작용하는지는 Delphi의 Excel 날짜 일련 번호, 1904 시스템, numFmt를 참조하세요

표시 정밀도는 언제 켜고 언제 끄나?

표시 정밀도는 통합 문서의 저장 숫자가 표시 숫자와 반드시 같아야 하고, 남는 자릿수를 영원히 잃는 것을 받아들일 때만 켜세요. 고전적인 정당한 사례는 반올림된 금액 열이 화면의 반올림된 합계와 맞아야 하고, 숨은 센트 소수가 마지막 자리에서 하나 어긋난 합계를 만들지 않아야 하는 재무 일정입니다. 이미 옵션이 켜진 고객의 기존 통합 문서와 맞추는 것이 또 하나의 좋은 이유이고, HotXLS는 왕복에서 플래그를 보존하므로 조용히 전체 정밀도로 되돌리지 않습니다

그 밖의 대부분 상황에서는 피하세요:

  • 공학·과학 데이터. 보고서용으로 소수 둘 서식을 골랐다는 이유로 측정값을 반올림하면, 이후 어떤 서식 변경으로도 복구할 수 없는 정보가 파괴됩니다
  • 거친 서식의 백분율. 0% 서식은 저장된 비율의 소수 둘만 유지하므로 0.1234는 0.12가 되고, 그 셀을 읽는 이후 모든 수식은 0.12로 동작합니다
  • 스케일된 표시. 천 단위 표시용 0,이나 0.0, 서식은 저장 값을 천·백의 자리로 반올림하는데, 서식을 고른 사람이 의도한 것은 거의 아닙니다
  • 공유 템플릿. 플래그는 통합 문서 전체입니다. 나중에 시트를 추가하는 사람은 대개 켜져 있다는 사실도 모른 채 그 동작을 물려받습니다

정말 원하는 것이 몇 개 특정 셀의 반올림된 결과라면, 대신 그 수식에 ROUND를 쓰세요. ROUND는 명시적이고, 셀에 국한되며, 수식을 읽는 누구에게나 보이고, HotXLS 수식 엔진이 다른 함수처럼 평가하며, 통합 문서 전체 부작용이 없습니다

표시 정밀도 빠른 참조

  • 파일 플래그: BIFF8의 CalcPrecision $000E에 fFullPrec = 0([MS-XLS] §2.4.35), XLSX의 calcPr fullPrecision="0"(ECMA-376 Part 1)
  • HotXLS 스위치: TXLSXWorkbook.FullPrecision := False와 TXLSWorkbook.UseFullPrecision := False, 둘 다 기본값 True
  • 구간: 계산 값의 부호로 고른다. 세 번째 구간은 정확히 0일 때만
  • 자릿수: 소수 자리 표시자, %마다 둘 더하고 스케일링 쉼표마다 셋 뺀다. 개수는 음수가 될 수 있다
  • 반올림: 몇 ulp 허용 오차와 함께 0에서 멀어지는 쪽. 2.5는 3, -2.5는 -3, 1.005는 1.01
  • 건너뜀: General, 날짜/시간과 경과 시간, 지수, 분수, 텍스트, Boolean, 오류 값
  • HotXLS 범위: 계산되는 시점의 수식 결과. 상수는 대입된 그대로 저장
  • XLSX 엔진: 첫 Recalculate 전에 FullPrecision을 설정한다. 클래식 세터는 모든 수식을 스스로 다시 dirty로 만든다
  • 참고: 기대 값은 Excel 16에서 측정되었으며 두 엔진이 공유한다

HotXLS는 Delphi와 C++Builder에서 XLS와 XLSX 통합 문서를 네이티브로 읽고, 쓰고, 계산하며, 여기서 다룬 통합 문서 계산 옵션도 포함입니다. 세부, 에디션, 평가판 다운로드는 HotXLS Delphi 스프레드시트 컴포넌트 페이지에 있습니다