Excel 표시 정밀도는 저장된 각 숫자를 숫자 서식이 보여 주는 소수 자릿수로 반올림합니다. 값의 부호에 맞는 서식 구간, %마다 소수 둘 추가, 천 단위 스케일링 쉼표마다 셋 감소, 0에서 멀어지는 쪽 반올림입니다. HotXLS는 TXLSXWorkbook.FullPrecision이나 TXLSWorkbook.UseFullPrecision이 False일 때 두 Delphi 엔진 모두에서 같은 규칙을 적용합니다. 한 줄짜리 이야기처럼 들리지만, 고객이 내보낸 송장 합계가 Excel과 1센트 어긋난다거나 [ss].00 서식의 소요 시간 열이 0으로 무너졌다고 리포트하면 이야기가 달라집니다. 둘 다 실제로 일어났고, 둘 다 규칙 하나를 틀린 데서 거슬러 올라갑니다. v2.384.57부터 두 엔진은 기대값을 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이 구현하는 바입니다
- 부호로 구간을 고릅니다. 구간 둘인 서식은 음수에 두 번째 구간을 씁니다. 구간 셋 이상인 서식은 음수에 두 번째, 정확히 0에 세 번째를 씁니다. 나머지는 모두 첫 구간을 씁니다
- 소수 자리 표시자를 셉니다. 그 구간에서 소수점 뒤의
0,#,?하나하나가 유지할 소수 하나를 더합니다 - 백분율 기호마다 둘을 더합니다.
0.0%는 0.1234를 12.3%로 표시하므로, 저장 값은 보이는 것의 백분의 일이며 소수 하나가 아니라 셋을 유지합니다 - 스케일링 쉼표마다 셋을 뺍니다. 마지막 정수 표시자 뒤의 쉼표(
0,,0.0,,0,.0)는 표시를 1000으로 나눕니다.0.0,는 12345.678을 12.3으로 표시하므로, Excel은 소수 하나 빼기 셋, 즉 음수 개수를 유지합니다. 값은 백 단위로 반올림되어 12300으로 저장됩니다.#,##0처럼 정수 표시자 사이의 쉼표는 단순 자릿수 묶음이며 아무것도 바꾸지 않습니다 - 비숫자 구간은 그대로 둡니다. General, 날짜와 시간 구간(경과 시간
[h],[mm],[ss]포함), 과학, 분수, 텍스트 구간, 자릿수 표시자가 전혀 없는 구간은 전체 정밀도를 유지합니다
Excel 16에 대해 측정한 결과, 다음은 두 HotXLS 엔진이 이제 각 서식의 수식 결과에 대해 저장하는 값입니다:
| 숫자 서식 | 계산된 값 | 저장된 값 | 적용되는 규칙 |
|---|---|---|---|
0.0% | 0.1234 | 0.123 | 소수 한 자리에 백분율 기호로 둘을 더함 |
0 | 2.5 | 3 | 0에서 멀어지는 쪽, 짝수로가 아님 |
0 | -2.5 | -3 | 음수 쪽에서도 0에서 멀어지는 쪽 |
0.00;(0.0) | -1.2345 | -1.2 | 음수 구간은 소수 한 자리 표시 |
0.00;(0.0) | 1.2345 | 1.23 | 양수 구간은 소수 둘 표시 |
#,##0.0 | 1234.5678 | 1234.6 | 묶음 쉼표, 스케일링 없음 |
0.0, | 12345.678 | 12300 | 소수 하나 빼기 셋: 백 단위로 반올림 |
0.0%;(0.00%) | -0.0125 | -0.0125 | 음수 구간은 둘 더하기 둘 소수 유지 |
0.00 | 1.005 | 1.01 | 이진 표현 오류에 대한 허용 오차 |
0;-0;0.0 | 0.5 | 1 | 0이 아니므로 양수 구간이 결정 |
마지막 행은 좋은 함정입니다. 값 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를 더한 뒤, 버리고, 다시 스케일하고, 부호를 되살립니다. 다음 함수는 그 원리의 자기 완결적인 삽화이지 라이브러리 코드 자체는 아니며, 스케일링 쉼표의 음수 자릿수 개수도 같은 방식으로 다룹니다:
// 원리 스케치: 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자리)허용 오차는 의도된 트레이드오프입니다. 반 단계 아래 두 ulp에 진짜로 있는 값도 올림되지만, 그 거리에서는 차이가 표현 오류와 구별되지 않고, 반 단계로 다루는 것이 입력된 소수가 사용자가 기대하는 대로 동작하게 만드는 장치입니다
v2.384.57 전에는 무엇이 잘못됐을까?
v2.384.57 전에는 XLSX 엔진과 클래식 엔진이 각자의 표시 정밀도 코드를 가졌고, 각자 다른 방식으로 틀렸습니다. 옵션을 켠 채 통합 문서를 생산한다면, 구형 빌드가 만든 파일에서 찾아야 할 증상들은 이것들입니다
XLSX 엔진: 첫 구간만, 백분율 없음, 은행가 반올림
옛 XLSX 경로는 서식 문자열 전체의 소수 개수를 물었는데, 그것은 첫 구간만 보고 %를 무시했고, 그다음 Round로 반올림했습니다. 0.0%의 0.1234는 0.1로 저장됐습니다. 화면의 12.3%가 아니라 10%인 셈이죠. 0의 2.5는 3 대신 2로 저장됐습니다. 0.00;(0.0) 같은 서식의 음수는 양수 구간의 소수 둘로 반올림됐습니다. v2.384.57부터 XLSX 엔진은 클래식 엔진과 같은 공유 루틴을 호출하며, 그 릴리스에서 클래식 엔진은 스케일링 쉼표 지원도 얻었습니다
클래식 엔진: TRUE가 -1이 됐다
클래식 엔진은 반올림을 VarIsNumeric으로 가드했는데, VarIsNumeric은 varBoolean Variant에 대해 True를 돌려줍니다. 그 Variant를 Double(V)로 변환하면 -1이 나옵니다. COM식 Boolean True가 -1로 저장되기 때문입니다. 0.00 서식 셀의 =A1>0 같은 수식은 그래서 재계산에서 숫자 -1로 나왔습니다. v2.384.57부터 Boolean 결과는 어떤 숫자 검사보다 먼저 제외되며, 논리 결과는 두 엔진 모두에서 논리 결과로 남습니다
경과 시간 서식이 색으로 읽혔다(v2.384.9)
세 번째 버그는 반올림이 아니라 숫자 서식 모델에 앉아 있었습니다. 파서는 조건이 아닌 모든 대괄호 토큰을 색으로 분류했으므로, [h], [mm], [ss]는 자기 구간을 날짜/시간으로 표시하지 못했습니다. 표시는 영향을 받지 않았습니다. 서식은 별도 경로에서 돌기 때문입니다. 하지만 표시 정밀도는 시간 값을 건너뛰는 데 그 플래그에 의존합니다. 5초짜리 소요 시간은 하루의 5/86400, 약 0.0000579이고, [ss].00 같은 서식은 평범한 소수 둘 숫자처럼 보였으므로, FullPrecision이 꺼져 있으면 소요 시간은 0.00일로 반올림됐습니다. v2.384.9부터 h, m, s 글자 하나짜리 대괄호 런은 경과 시간 토큰으로 파싱되고 구간은 날짜/시간으로 다뤄집니다. 같은 릴리스는 h:mm의 분 검출도 고쳤는데, 토큰 사이의 콜론이 파서에게 시(hour)를 숨기곤 했습니다
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은 Boolean True 유지(v2.384.57 전 빌드는 -1 저장)
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에서는
fFullPrec= 0인 CalcPrecision$000E([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로 만듦 - 버전: v2.384.57부터 두 엔진 모두에서 Excel 16과 일치. 경과 시간 서식은 v2.384.9부터 보호
HotXLS는 Delphi와 C++Builder에서 XLS와 XLSX 통합 문서를 네이티브로 읽고, 쓰고, 계산하며, 여기서 다룬 통합 문서 계산 옵션도 포함입니다. 세부, 에디션, 평가판 다운로드는 HotXLS Delphi 스프레드시트 컴포넌트 페이지에 있습니다