위의 셀 값을 계속 증폭하는 수식 10,000개가 한 열에 늘어선 모델은 스프레드시트가 담을 수 있는 가장 값싼 모델에 가깝습니다. 참조하는 수식을 만날 때마다 그 자리에서 계산해 버리는 평가기라면 합계는 여전히 맞게 나오지만 산술 대금이 문제입니다. 마지막 셀은 조상 9,999개를 평가에 끌어들이고, 그 위의 셀은 9,998개를 끌어들이며, 한 번의 전체 패스는 10,000회의 곱셈을 위해 대략 5천만 번의 평가로 불어납니다. 꼭대기의 성장률 하나를 바꾸면 그 평가기는 전체 대금을 다시 치러야 합니다. 어떤 셀이 어떤 셀을 읽는지 기록을 전혀 갖고 있지 않기 때문입니다
셀 하나의 편집이 왜 통합 문서 전체 패스로 번지는가
그런 동작을 낳는 지름길은 두 가지이고, 손으로 만든 대부분의 수식 엔진은 둘 중 하나를 택합니다. 재귀 지름길은 참조하는 수식을 매번 새로 평가하고 이미 캐시된 값을 무시하므로, 사슬은 이차 작업이 되고 순환 참조는 스택 오버플로가 됩니다. 전체 패스 지름길은 더 안전합니다. 모든 수식을 어떤 고정된 순서로, 아무것도 바뀌지 않을 때까지 몇 번이고 평가하는 것입니다. 올바르지만 비용은 편집의 크기가 아니라 통합 문서의 크기에 비례합니다
Excel은 계산 체인으로 두 가지를 모두 피합니다. 수식 셀들의 순서표를 두어 편집이 작은 셀 집합만 플래그하고 영향받는 꼬리만 걷게 하는 것입니다. HotXLS의 XLSX 엔진은 TXLSXWorkbook.Recalculate 뒤에 명시적인 의존성 그래프 형태로 같은 아이디어를 씁니다. 첫 호출은 모든 시트의 모든 수식으로 그래프를 만들어 전부 평가하고, 그다음 호출부터는 지난 패스 이후 값이 바뀐 셀의 하류 수식들과, 매번 실행되어야 하는 짧은 수식 목록만 평가합니다
Recalculate가 편집을 한 번의 정렬된 패스로 바꾸는 방법
수식 셀마다 노드 하나가 되고, 수식이 읽는 모든 수식 셀에서 그 수식으로 변이 뻗어 나갑니다. SUM(Model!B2:B500) 같은 참조는 그 사각형 안의 모든 수식 셀에서 변을 만들고, 여러 시트에 걸친 3D 참조는 그 행의 각 시트와 대조됩니다. 단순 상수는 노드가 아니며, 편집이 시작되는 출발점으로서만 의미가 있습니다
셀에 Value를 쓰는 것은 쓰는 순간에는 거의 아무 일도 하지 않습니다. 통합 문서는 셀의 주소를 큐에 넣을 뿐입니다. Recalculate가 시작되면 참조가 큐에 담긴 주소를 덮는 모든 수식이 dirty 표시를 받고, 그 표시는 변을 따라 그 수식의 의존자들, 그리고 그들의 의존자들로 흘러갑니다. Recalculate가 한 번도 불리지 않은 통합 문서는 그래프를 갖지 않고 쓰기 비용은 nil 검사 하나에 그치므로, 값만 읽고 쓰는 코드는 이 기능에 아무것도 지불하지 않습니다
그다음 그래프의 dirty 부분을 카으의 알고리즘으로 정렬합니다. 이 패스를 값싸게 만드는 디테일은 진입 차수를 세는 방법에 있습니다. dirty인 선행자만 셉니다. 깨끗한 선행자는 이미 유효한 캐시 값을 갖고 있으므로 어떤 수식도 그것을 기다리지 않습니다. 패스 도중 다른 수식 셀을 참조하는 수식은 그 셀을 다시 평가하지 않고 캐시 값을 읽으며, 정렬이 캐시가 이미 신선하다는 것을 보장합니다. dirty 수식은 정확히 한 번만 평가됩니다
아래 코드는 서두의 사슬을 만들고 유일한 입력을 편집합니다. HotXLS의 시트 인덱스는 1부터 시작하므로 Inputs는 시트 1, Model은 시트 2이고, Cells의 행과 열도 1부터 시작합니다
uses
SysUtils, lxCalc, lxHandleX; // lxOk는 lxCalc에 선언되어 있습니다
var
Book: TXLSXWorkbook;
Inputs, Model: TXLSXWorksheet;
Row: Integer;
Last: Variant;
begin
Book := TXLSXWorkbook.Create;
try
Inputs := Book.Sheets.Add('Inputs'); // Sheets[1]
Model := Book.Sheets.Add('Model'); // Sheets[2]
Inputs.Cells[2, 2].Value := 0.05; // 성장률 가정은 Inputs!B2에
Model.Cells[2, 2].Formula := 'Inputs!B2*1000';
for Row := 3 to 10000 do
Model.Cells[Row, 2].Formula :=
'B' + IntToStr(Row - 1) + '*(1+Inputs!$B$2)';
if Book.Recalculate <> lxOk then // 그래프를 만들고 9,999개 수식을 평가합니다
Exit;
Inputs.Cells[2, 2].Value := 0.07; // 주소 하나를 큐에 넣음
if Book.Recalculate = lxOk then // Inputs!B2를 먹이로 삼는 수식들이 다시 실행됩니다(여기서는 9,999개 모두)
Last := Model.Cells[10000, 2].Value; // 캐시된 결과를 값처럼 읽음
// 일회성 읽기: RecalcCell은 Recalculate를 실행한 뒤 그 셀 값을 돌려줍니다
// (시트 2 = Model, 행 10000, 열 2)
Book.RecalcCell(2, 10000, 2, Last);
finally
Book.Free;
end;
end;
이 모델에서는 모든 수식이 Inputs!$B$2를 읽으므로 두 번째 패스도 9,999개를 모두 건드립니다. 실제로 모든 것에 영향을 주는 편집이라면 그래프도 줄여 줄 수 없습니다. 절약이 드러나는 곳은 입력이 많은 모델입니다. 시나리오 루프가 셋이나 넷의 셀을 쓰고 통합 문서의 나머지는 캐시 값을 유지하는 형태에서 말입니다
값 쓰기는 값싸고, 수식 쓰기는 그래프를 다시 만든다
증분 상태를 유지하는 것은 값 편집뿐입니다. 노드나 변의 집합을 바꿀 수 있는 것은 무엇이든 그래프 전체를 낡게 표시하고, 다음 Recalculate는 처음부터 다시 만들어 모든 수식을 평가합니다. 여기에는 Formula 대입, 수식 셀에 Null이나 빈 값을 써서 지우기, 시트 추가·이동·삭제, 행과 열 삽입·삭제, 정의된 이름 편집, what-if 데이터 테이블 변경이 속합니다. 사용자 함수를 등록하거나 등록 해제하거나 OnUserFunction을 대입하는 경우 그래프는 유지되지만 모든 노드가 dirty 표시됩니다
보고서 생성기에 실질적으로 요구되는 것은 루프의 모양입니다. 수식을 한 번 배치하고 Recalculate를 부른 뒤로는 값만 만지세요. 고객마다 수식 템플릿을 다시 쓰고 재계산하는 루프는 매 반마다 전체 재구축을 치르는데, 그래프가 존재하는 이유 자체가 바로 그 비용을 피하는 것입니다. 템플릿 변경이 정말 필요하다면 모든 수식 편집을 한 번의 Recalculate 앞에 몰아 넣어 재구축이 한 번만 일어나게 하고, 그 뒤의 시나리오 루프는 값만 씁니다
// 모델은 한 번 만들고 Recalculate는 이미 호출된 상태
for I := 0 to High(Scenarios) do
begin
Inputs.Cells[2, 2].Value := Scenarios[I].Growth; // 값 쓰기만 함
Inputs.Cells[3, 2].Value := Scenarios[I].Discount;
if Book.RecalcNeeded then // 큐에 있거나 dirty인 셀이 있으면 True
if Book.Recalculate <> lxOk then
Break;
Results[I] := Model.Cells[10000, 2].Value;
end;
// 아직 밀린 것이 있을 때만 SaveAs가 재계산하도록 함
Book.RecalcBeforeSave := xrmIfDirty;
Book.SaveAs('scenarios.xlsx');
RecalcBeforeSave의 기본값은 xrmManual이며 저장 중에는 결코 재계산하지 않습니다. 생성하는 파일에는 xrmIfDirty가 맞습니다. 그래프가 낡았거나 dirty 수식이 있을 때만 재계산하므로, 루프에서 이미 재계산한 통합 문서는 그대로 기록되고 편집을 잊은 통합 문서가 낡은 캐시 값으로 저장되는 일은 없습니다
매 패스마다 재계산을 강제하는 Excel 함수는 무엇인가?
NOW, TODAY, RAND, RANDBETWEEN, RANDARRAY, OFFSET, INDIRECT, CELL, INFO, GETPIVOTDATA를 부르는 수식은 HotXLS에서 휘발성입니다. 상류가 바뀌었는지와 무관하게 매 패스 시작 시 dirty 표시되고, 의존자들도 모두 그렇습니다. 시계와 난수 함수가 휘발성인 이유는 자명합니다. OFFSET과 INDIRECT가 휘발성인 이유는 읽는 셀들이 실행 시점의 인수 값으로 계산되어, 수식의 정적 훑기로는 어떤 변을 그려야 하는지 말할 수 없기 때문입니다
같은 규칙이 그래프 작성기가 구체적 사각형으로 못 박을 수 없는 참조, 다른 이름들을 거쳐 자기 자신을 가리키는 정의된 이름, 동적 배열 스필 참조, what-if 데이터 테이블에 적용됩니다. 정의된 이름 자체는 문제가 아닙니다. 작성기는 이름이 가리키는 범위로 전개하므로 통합 문서나 시트 범위 이름을 거치는 수식은 실제 변을 얻습니다. 그런 이름들이 어떻게 해석되는지는 정의된 이름과 시트 간 수식 글이 다룹니다
여기서는 보수적인 방향이 옳습니다. 불필요한 평가는 시간을 조금 쓰지만, 빠진 변은 완성돼 보이는 보고서에 낡은 숫자를 남깁니다. 큰 모델의 핵심 경로에서 OFFSET과 INDIRECT를 떼어 놓고 계산된 주소가 정말 필요한 몇몇 셀로 국한하세요. 사슬 꼭대기 근처의 휘발성 셀 하나는 사실상 사슬 전체를 휘발성으로 만듭니다
OnUserFunction이나 RegisterUserFunction으로 공급되는 함수는 정반대 끝에 있습니다. 휘발성이 아니므로 핸들러는 내장 함수와 마찬가지로 인수를 먹이는 셀이 바뀔 때만 다시 불립니다. 데이터베이스에서 환율을 읽는 핸들러는 그래서 패스가 몇 번을 지나도 마지막 답을 되풀이해서 돌려줍니다. 환율을 입력 셀에 쓰고 그 셀을 인수로 넘기세요. 그러면 그래프가 의존성을 보고 환율이 바뀐 바로 그때 수식이 다시 실행됩니다. 핸들러 인수가 어떻게 도착하는지는 HotXLS 수식 엔진의 사용자 정의 함수 글이 설명합니다
순환 참조: lxErrorRef 또는 반복 계산
기본값인 Iterate = False 상태에서 정렬이 순환을 발견하면 TXLSXWorkbook.Recalculate는 lxErrorRef를 돌려줍니다. 순환 멤버는 카으 알고리즘이 끝내 놓아주지 못하는 dirty 노드들이고, 그 아래류의 dirty 수식도 같은 이유로 막힙니다. 그 수식들은 이전 캐시 값을 유지하는 한편, dirty 집합의 나머지는 순서대로 평가됩니다. 실패한 패스는 그래프를 낡게 표시하므로, 다음 호출은 그래프를 다시 만들어 모든 수식을 한 번 더 평가합니다
case Book.Recalculate of
lxOk:
Book.SaveAs(OutputFile);
lxErrorRef:
// 순환과 그 아래류 수식은 여전히 옛 캐시를 유지합니다.
// LogWarning은 여러분의 루틴입니다
LogWarning('Circular reference in model, report not written');
else
LogWarning('Recalculation did not complete');
end;
Iterate := True는 Excel이 제공하는 것과 같은 반복 계산을 켭니다. 엔진은 순환과 막힌 의존자들을 IterateCount(기본 100) 패스까지, 어떤 값도 IterateDelta(기본 0.001) 이상 움직이지 않을 때까지 되풀이 평가합니다. 비어 있던 순환 멤버는 0에서 출발합니다. 패스 한도에 다다르면 마지막 결과를 유지한 채 여전히 lxOk를 돌려줍니다. 그래서 생성하는 보고서에는 반복 계산을 꺼 두어야 합니다. 실제 보고서 모델의 순환은 거의 항상 실수이며, 예컨대 합계 행이 자신의 SUM 범위 안에 갇힌 경우입니다. 그런 경우 돌아오기 바라는 것은 확정적인 오류 코드입니다. 이자가 그 이자를 포함한 평균 잔액에 붙는 모델처럼 의도된 루프를 중심으로 만들어진 모델에만 켜세요
순환처럼 보이지만 순환으로 보고되지 않는 케이스가 하나 있습니다. VLOOKUP, HLOOKUP, LOOKUP, MATCH, XLOOKUP, XMATCH의 룩업 배열 인수는 그 범위의 셀이 바뀌면 수식을 dirty 표시하지만 순환 검출에는 세지 않습니다. 그래서 B열에 쓴 VLOOKUP(A1,B:B,1)은 평범하게 평가됩니다. 진짜 순환이라면 수식 평가 트레이서가 자기 자신으로 접어드는 참조 사슬을 한 단계씩 보여 줍니다
배열 수식과 멀티스레드 패스
레거시 CSE 배열 수식은 고정된 사각형 전체를 한 노드로 삼습니다. 루트 수식은 패스마다 한 번 평가되고, 그 행렬이 각 멤버 셀에 기록되며(스칼라 결과는 사각형 전체에 브로드캐스트됨), 사각형의 왼쪽 위 앵커뿐 아니라 그 안의 어떤 셀을 참조하는 수식도 루트에서 오는 변을 갖습니다. 동적 배열 수식은 더 조심스럽게 다뤄집니다. 매 패스 실행되고, 스필의 크기가 바뀌면 Recalculate는 그래프를 다시 만들고 스필 모양이 안정될 때까지 패스를 되풀이합니다. 32회 안에 안정되지 않으면 lxErrorCalc를 돌려줍니다
RecalcThreads를 1보다 크게 하면 독립적인 수식들의 각 위상 레벨을 워커 스레드에 걸쳐 평가하고, 스레드마다 별도의 계산기를 씁니다. 데이터 테이블은 여전히 직렬로 돌고, 동적 배열을 포함한 통합 문서는 직렬 경로에 머뭅니다. 이 값을 올리기 전에 사용자 함수 핸들러는 반드시 스레드 안전해야 합니다. 공유 상태를 만지는 핸들러라면 RecalcThreads를 기본값 0에 두는 쪽이 안전합니다
의존성 그래프와 휘발성 처리, 반복 계산은 HotXLS Delphi Excel Component XLSX 엔진의 일부이며, 같은 엔진이 수식 계산기, 정의된 이름, 그 주변의 읽기·쓰기 파이프라인까지 커버합니다. 가격표나 결산 통합 문서를 하루 종일 열어 두고 새 입력을 계속 먹이는 Delphi와 C++Builder 애플리케이션이라면, 편집당 비용이 그 편집이 실제로 닿는 셀들만 따라갑니다