Delphi에서 의도적인 순환 참조(Circular Reference)를 계산하기 위해, HotXLS는 XLSX 엔진에 반복 계산(Iterative calculation) 기능을 제공합니다: TXLSXWorkbook.Iterate를 True로 설정하면 TXLSXWorkbook.Recalculate는 감지된 각 참조 사이클이 고정 지점(Fixed point)에 수렴할 때까지 순회합니다. 이 연산은 최대 IterateCount 횟수만큼 반복되거나 모든 셀의 변동량이 IterateDelta 미만으로 떨어질 때까지 수행되며, #REF! 에러를 즉시 반환하고 포기하지 않습니다
이러한 구분은 하나의 boolean 프로퍼티가 암시하는 것보다 훨씬 더 중요합니다. 동일한 엔진이 참조 사이클을 감지하고 무한 루프를 방지하기 위해 거부하더라도, 프로퍼티 하나만 전환하면 의도적으로 사이클이 안정화될 때까지 계산을 수행하게 됩니다. 사이클이 보고해야 할 결함인지, 혹은 풀어야 할 모델인지에 관한 두 가지 동작 방식을 구분하는 것이 이 기사의 핵심 주제입니다
왜 순환 참조는 기본적으로 에러를 발생시키나요?
기본적으로 HotXLS는 모든 참조 사이클을 작성 오류로 취급하고 이를 계산하는 대신 보고합니다. TXLSXWorkbook.Recalculate는 수식 종속성 그래프를 작성하고 위상 정렬(Topological sort) 순서대로 모든 수식 셀을 평가하며, 사이클을 발견하는 즉시 — 위상 정렬 중에 결코 릴리즈할 수 없는 노드들 — lxErrorRef를 반환합니다. 사이클 멤버에 속한 셀들은 이전의 캐시된 값을 그대로 유지하며, 사이클 외부의 모든 수식은 여전히 정상적으로 평가됩니다. 그래프의 메커니즘과 사이클 멤버들이 루프를 돌지 않고 건너뛰어지는 이유는 증분 수식 재계산 및 종속성 그래프에 관한 설명에서 자세히 다룹니다
대부분의 사이클은 버그(실수로 합계 행이 자신의 SUM 범위에 포함되었거나, 복사-붙여넣기 시 참조가 자기 자신으로 밀린 경우 등)이기 때문에 기본 동작은 가장 안전한 방식입니다. 재계산 시점에 에러 코드를 명확히 리턴받는 것이 이에 대처하는 최선입니다. 그러나 일부 중요하고 구체적인 수식 모델 클래스는 의도적으로 순환 구조를 갖습니다. 원리합계(복리) 계획표, 부서 간의 순환 비용 및 간접비 배분, 잔액 기준 수수료 계산 등은 값이 적법하게 자신의 입력으로 피드백되는 구조를 띱니다. Excel은 사용자가 파일 → 옵션 → 수식 → 반복 계산 사용 옵션을 체크했을 때만 이들을 연산합니다
HotXLS에서 반복 계산을 활성화하려면 어떻게 해야 하나요?
HotXLS는 TXLSXWorkbook의 세 가지 프로퍼티로 이 Excel 체크박스를 구현합니다. Iterate는 모드를 켜며, 이것이 True이면 감지된 사이클은 lxErrorRef를 리턴하는 대신 반복 솔버(Solver)에 인계됩니다. 기말 잔액이 이자에 의존하고 이자가 잔액에 의존하는 복리 계산 셀 쌍의 정형적인 예시를 고려해 보겠습니다
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Model');
Sheet.Cells[1, 2].Value := 1000; // B1: 기초 원금
Sheet.Cells[2, 2].Value := 0.05; // B2: 기간 이율
Sheet.Cells[3, 2].Formula := 'B1+B4'; // B3: 잔액 = 원금 + 이자
Sheet.Cells[4, 2].Formula := 'B3*B2'; // B4: 이자 = 잔액 * 이율
Book.Iterate := True; // 반복 계산 적용
if Book.Recalculate = lxOk then
// B3은 1052.63...으로, B4는 52.63...으로 수렴합니다
Report(Sheet.Cells[3, 2].Value);
finally
Book.Free;
end;
end;B3은 B4를 참조하고 B4는 B3을 참조하므로, 종속성 그래프는 두 노드의 사이클로 이를 감지합니다. Iterate가 기본값인 False이면 두 셀 모두 lxErrorRef로 귀결되어 아무 값도 얻지 못합니다. Iterate가 True이면 Recalculate는 현재 캐시된 값을 사이클의 시드값으로 공급하고 패스(Pass)마다 셀들을 반복 평가하며, 이전 패스의 결과값을 다음 패스의 입력값으로 피드백하여 수치 변동이 멈출 때까지 루프를 돕니다. 이 예시의 수렴 공식은 원금 / (1 - 이율)이므로, 최종 잔액은 1052.63에 수렴하고 이자는 52.63에 수렴하게 되며, 이는 Excel의 반복 계산 활성화 시 결과와 완벽하게 일치합니다
반복은 무엇을 기준으로 멈추나요?
두 가지 독립적인 중단 조건이 솔버를 제한하며, 이 둘을 올바르게 이해해야 수식이 에러를 내거나 무한 루프에 빠지는 것을 방지할 수 있습니다. IterateCount는 사이클 멤버가 재평가되는 최대 횟수 제한(하드캡)으로, 기본값은 Excel과 동일하게 100입니다. IterateDelta는 수렴 임계값입니다: 매 패스(Pass)마다 솔버는 모든 사이클 셀에서 발생한 최대 수치 변동량을 측정하며, 이 변동량이 IterateDelta(기본값 0.001) 미만으로 떨어지는 즉시 패스 루프를 조기 종료합니다. 둘 중 먼저 달성되는 조건이 반복을 종료시킵니다
Book.Iterate := True;
Book.IterateCount := 1000; // 하드캡: 사이클에 대해 최대 1000회 반복
Book.IterateDelta := 0.0001; // 수렴: 모든 셀의 변동량이 0.0001 미만이 되면 중단합니다
case Book.Recalculate of
lxOk:
// 사이클이 수렴했거나, 1000회 하드캡에 도달하여
// 마지막 반복의 값을 유지합니다 -- Iterate가 True일 때 두 경로 모두 lxOk를 반환합니다
SaveWorkbook(Book);
lxErrorRef:
// Iterate가 False일 때만 도달 가능: 사이클이 해결되지 않고 에러로 보고되었습니다
LogWarning('Circular reference with iteration disabled');
end;
여기서 파생되는 한 가지 결과는 이 기능의 실제 물리적 한계이므로 명확하게 이해해야 합니다. 변동량이 IterateDelta 미만으로 감소하지 않은 채 최대 한계 횟수에 도달하더라도 SolveCycleIteratively는 에러를 던지지 않습니다. 대신 lxOk를 반환하고 수렴 없이 최대 반복 횟수에 도달했을 때 Excel이 마지막으로 계산된 수치를 쓰는 것과 똑같이 셀에 마지막 반복 결과 값을 남겨둡니다. 즉, 반복 계산 모드에서 Recalculate가 성공 코드(lxOk)를 반환한 것은 '솔버가 실행되었다'는 의미이지 '솔버가 수렴했다'는 뜻이 아닙니다. 피드백 루프가 발산하거나 진동하는 수식 모델은 조용히 IterateCount 횟수를 모두 소모하고 고정 지점(Fixed point)이 아닌 비정상적인 값을 반환하며, 어떠한 예외나 경고도 표시되지 않습니다
이 설정이 XLSX 및 XLS 파일에 어떻게 저장되나요?
반복 계산 설정은 두 스프레드시트 포맷에 모두 저장되므로, Excel에서 문서를 열었을 때 Delphi 코드가 구성한 대로 올바르게 동작합니다. XLSX의 경우, 작성기는 Iterate가 True일 때만 OOXML <calcPr> 요소를 출력하고, 출력을 최소화하기 위해 기본값 상태인 속성은 생략합니다: 즉, 기본값만 사용하는 통합 문서는 <calcPr iterate="1"/>만 작성하고, iterateCount는 100이 아닐 때만, iterateDelta는 0.001과 다를 때만 출력됩니다. 파일을 열 때 TXLSXWorkbook은 이 세 가지 속성을 다시 읽어들이므로 왕복 전송이 완벽하게 동기화됩니다
레거시 BIFF8(.xls) 엔진인 TXLSWorkbook은 다른 세 프로퍼티 쌍을 통해 이와 동등한 상태를 세 개의 독립된 레코드에 전달합니다. EnableIteration은 CalcIter 레코드($0011, [MS-XLS] §2.4.33)에, MaxIterations는 CalcCount 레코드($000C, [MS-XLS] §2.4.31)에, MaxIterationChange는 CalcDelta 레코드($0010, [MS-XLS] §2.4.32)에 매핑됩니다. 세터(Setter)는 스펙 범위를 강제합니다. CalcCount는 1~32767 사이여야 하므로 MaxIterations가 이 범위로 클램핑(Clamping)되고, 음수의 MaxIterationChange는 0.001 기본값으로 자동 전환됩니다. 로드된 .xls 통합 문서에 이를 설정하면 세 가지 계산 레코드가 저장 시 충실하게 기록됩니다
var
Book: TXLSWorkbook; // BIFF8 (.xls) 엔진
begin
Book := TXLSWorkbook.Create;
try
Book.Open('model.xls');
Book.EnableIteration := True; // CalcIter 레코드 $0011
Book.MaxIterations := 500; // CalcCount 레코드 $000C (1..32767 범위로 클램핑됨)
Book.MaxIterationChange := 0.0001; // CalcDelta 레코드 $0010
Book.SaveAs('model.xls'); // 세 가지 계산 레코드가 온전히 왕복 전송됩니다
finally
Book.Free;
end;
end;명명법의 의도적인 분리에 주목하십시오: XLSX 엔진은 Iterate / IterateCount / IterateDelta(OOXML 어휘)를 사용하고, BIFF8 엔진은 EnableIteration / MaxIterations / MaxIterationChange([MS-XLS] 레코드명)를 사용합니다. 두 트리플 모두 동일한 세 가지 놉(제어 장치)인 온/오프 스위치, 반복 한계 횟수, 수렴 임계값을 나타내며, 기본값 역시 비활성화, 100, 0.001로 동일합니다
순환 참조가 모델이 아닌 버그에 해당하는 경우는 언제인가요?
반복 계산 활성화가 순환 참조 경고를 강제로 없애기 위한 꼼수가 아니며, 그렇게 취급하는 것은 전형적인 함정입니다. 전역적으로 Iterate를 켜두면 기본 에러 코드로 잡아내어야 할 모든 실수에 의한 순환 참조 오류가 조용히 수렴하거나 수렴하지 않는 상태의 비정상적인 수치로 둔갑합니다. 올바른 원칙은 그 반대입니다: Iterate를 일반 모드인 False로 유지하여 실제 작성 실수가 lxErrorRef 에러로 감지되도록 하고, 순환 구조가 설계상 의도되고 규명된 통합 문서에서만 선별적으로 반복 계산을 활성화해야 합니다
순환 참조 사이클이 나타났는데 어떤 유형인지 명확하지 않은 경우, 수식 평가 추적기가 이를 구분하는 유용한 도구입니다: 의심스러운 수식을 추적하면 자신에게 되돌아오는 참조 체인이 단계별로 표시되므로, 이것이 실제 피드백 루프를 표현한 것인지 아니면 실수로 꼬인 자기 참조인지 판별할 수 있습니다. 또한 사이클 내부의 셀도 루프를 도는 과정에서 임의의 내장 함수를 호출할 수 있다는 점을 기억하십시오. 엔지니어링 또는 복소수 수식을 계산하는 계산기가 매 패스마다 사이클 멤버를 똑같이 평가하므로, 발산하는 수식 모델은 반복 계산 설정이 아닌 대개 루프 내부의 수식 구조 자체의 오류가 원인입니다
실제 점검 체크리스트는 간단합니다. 반복 계산에 의존하기 전에 루프가 실제 고정 지점을 갖는지 확인하십시오; 개발자의 수식 모델에서 요구하는 수렴 정밀도에 맞춰 IterateDelta를 충분히 좁게 유지하십시오; 그리고 수렴할 것으로 예상되는 Recalculate가 수행된 후, 반환되는 lxOk 상태만 믿지 말고 출력 결과를 직접 검증하십시오: 에러 코드는 완벽한 수렴과 한계 횟수 소모 후의 조기 중단 상태를 구분하지 못하기 때문입니다
반복적인 순환 참조 계산 기능은 증분 종속성 그래프 기반 재계산 시스템 및 생성하는 모든 파일에 설정을 주입하는 OOXML, BIFF8 지속성 구조와 함께 HotXLS Delphi Excel 컴포넌트의 XLSX 엔진에 포함되어 있습니다. 설계상 의도적으로 순환 구조를 갖는 재무 및 공학 수식 모델의 경우, 이 기능은 단순한 오류 메시지와 온전한 정답을 가르는 차이가 됩니다