HotXLS의 AGGREGATE 옵션과 중첩 게이트

네 개의 셀이면 =AGGREGATE(9,7,A1:A4)가 직접 만든 평가기에서는 20을 반환하고 Excel에서는 40을 보이는 상황이 재현됩니다. A1에는 10, 숨겨진 행인 A2에는 20, A3에는 =1/0, A4에는 값이 30인 그룹 소계 =SUBTOTAL(9,A1:A2)이 들어 있습니다. 옵션 7은 숨겨진 행을 건너뛰고, 오류를 건너뛰고, 중첩 소계는 값으로 세라는 뜻이므로 Excel은 10과 30을 더합니다. 20이 나오는 이유는 A4가 자기 값을 계산하는 동안 바깥 호출의 "숨겨진 행 건너뛰기" 설정이 아직 켜져 있어 A4가 10으로 줄어들기 때문입니다. 40을 얻으려면 두 가지가 갖춰져야 합니다. 세 개의 옵션 비트를 올바른 위치에서 디코딩하는 것, 그리고 참조된 수식이 자신을 참조한 수식의 옵션을 결코 보지 못하게 막는 것입니다

AGGREGATE 옵션 0부터 7은 실제로 무엇을 선택할까요?

옵션 인자는 3비트 행렬이고 비트끼리는 서로 독립적입니다. 비트 0(값 1)은 숨겨진 행을 건너뛰고, 비트 1(값 2)은 오류 값을 건너뛰며, 비트 2(값 4)는 자체 수식이 SUBTOTAL이나 AGGREGATE인 셀을 건너뛰는 것을 멈춥니다. 낮은 코드들에서는 그런 셀을 건너뛰는 것이 기본 동작이기 때문입니다. 여기에는 거꾸로 읽기 쉬운 부분이 두 군데 있습니다. 숨겨진 행 비트는 가운데 비트가 아니라 낮은 비트라서, AGGREGATE(9,1,...)이 필터링된 합계이고 AGGREGATE(9,2,...)가 오류 관용형입니다. 그리고 중첩 정책은 나머지 둘과 반대로 움직입니다. 중첩된 집계를 평범한 숫자로 취급하는 것은 코드 4부터 7뿐입니다. ECMA-376 Part 1 §18.17.7은 SUBTOTAL을 코드 1–11과 101–111에 걸쳐 같은 숨겨진 행 포함/제외 구분으로 정의하는데, OOXML에 _xlfn. 접두사로 저장되는 AGGREGATE는 그 구분을 옵션 인자 안으로 접어 넣습니다. 따라서 Microsoft가 이 함수에 대해 공개하는 표는 편의를 넘어 엔진이 지켜야 할 계약입니다

옵션	숨겨진 행	오류 값	중첩 SUBTOTAL / AGGREGATE
0	포함	전파	무시
1	무시	전파	무시
2	포함	무시	무시
3	무시	무시	무시
4	포함	전파	포함
5	무시	전파	포함
6	포함	무시	포함
7	무시	무시	포함

0부터 7 밖의 코드는 #VALUE!로 거부되는데, Excel이 하는 일도 정확히 이것입니다

디코딩이 틀리는 지점은 한 비트짜리 코드입니다

표 자체가 아니라 표를 풀어 쓴 요약에서 디코딩을 작성하면 대개 이런 모양이 됩니다. 코드 4 이상에서 오류를 무시하고, 2, 3, 6, 7에서 숨겨진 행을 건너뛰며, 중첩 정책은 아예 빠뜨리는 것입니다. 이 버전은 실전에서 한동안 잘 버텨 줍니다. 사람들이 실제로 가장 많이 쓰는 조합인 숨김+오류가 두 해석 모두에서 코드 3과 7에 떨어지므로, 뒤바뀜은 한 비트짜리 코드에서만 드러납니다. 이때 AGGREGATE(9,1,A1:A4)는 필터링되지 않은 합계를 반환하고, AGGREGATE(9,2,...)는 #DIV/0!을 전파하면서 숨겨진 행을 건너뜁니다. 실무 워크북이 코드 1과 2를 단독으로 쓰는 일은 드물고, 바로 그 때문에 고객 파일을 기다리는 것보다 공개된 표와 대조해 보는 리뷰 쪽이 더 많은 것을 잡아냅니다

HotXLS는 옵션을 표의 열마다 하나씩, 세 개의 집합 소속 검사로 디코딩하고, AGGREGATE 호출이 시작될 때 세 결과를 모두 대입한 뒤 끝날 때 이전 상태를 복원합니다. 대입이 무조건인 것은 일부러입니다. "옵션이 요구할 때만 플래그를 켜는" 식으로 쓴 디코딩에서는 SUBTOTAL(109, ...) 안에 중첩된 코드 4짜리 AGGREGATE가 바깥의 숨겨진 행 설정을 지우는 대신 물려받게 됩니다. 아래 스케치는 자체 평가기를 작성하거나 리뷰하는 사람을 위한 같은 규칙입니다

// 자체 평가기용 예시 디코딩. TAggPolicy와
// DecodeAggregateOptions는 직접 정의하는 이름이지 HotXLS API가 아닙니다
type
  TAggPolicy = record
    IgnoreHidden, IgnoreErrors, SkipNested: Boolean;
  end;

function DecodeAggregateOptions(OptCode: Integer; out P: TAggPolicy): Boolean;
begin
  Result := (OptCode >= 0) and (OptCode <= 7);   // 그 외의 코드는 모두 #VALUE!
  if not Result then
    Exit;
  P.IgnoreHidden := OptCode in [1, 3, 5, 7];       // 비트 0
  P.IgnoreErrors := OptCode in [2, 3, 6, 7];       // 비트 1
  P.SkipNested   := OptCode in [0, 1, 2, 3];       // 비트 2 클리어
end;

같은 숨겨진 행 규칙의 SUBTOTAL 쪽, 필터링된 행과 수동으로 숨긴 행이 어떻게 비교되는지를 포함해서는 SUBTOTAL과 AGGREGATE 숨겨진 행 글에 정리되어 있습니다

바깥쪽 AGGREGATE는 참조하는 수식 안까지 왜 손을 뻗을까요?

"숨겨진 행 건너뛰기"를 셀 순회 루프들에게 전달하는 가장 자연스러운 방법은 계산기 객체의 필드 하나이고, 한 번의 재계산 패스에 평가되는 모든 수식을 하나의 계산기가 처리하기 때문입니다. 필드가 세워져 있는 동안 실행되는 모든 것이 그 필드를 세운 집계에 속한다면 이 설계는 문제없습니다. 깨지는 순간은 하나로 정해져 있습니다. 셀 가져오기입니다. 순회자가 셀 값을 요청했는데 그 셀이 캐시된 결과 없는 수식을 담고 있으면, 워크북은 그 자리에서 같은 계산기 위에, 바깥 설정이 그대로 켜진 채 그 수식을 컴파일하고 평가합니다

도입부 예제를 그런 엔진으로 돌려 봅시다. 코드 7의 AGGREGATE가 숨겨진 행 건너뛰기를 켜고 A1에서 출발합니다. A1은 10이고, A2는 숨겨져 있어 건너뛰고, A3은 오류라 건너뜁니다. 그다음 순회자가 아직 계산되지 않은 A4에 도달합니다. A4의 SUBTOTAL(9,A1:A2)가 실행되는데, 코드 101부터 111에만 숨겨진 행 건너뛰기를 켜는(전혀 합리적인 작성 방식입니다) SUBTOTAL 구현은 코드 9에 대해 그것을 끄지 않습니다. 설정이 이미 켜져 있는 것을 보고 A2를 건너뛰어 10을 반환합니다. 바깥 합계는 10 + 10 = 20이 됩니다. A4의 수식 어디에도 숨겨진 행은 언급되지 않습니다

중첩 집계 설정은 양방향으로 샙니다. 코드 0부터 3에서는 이 설정이 켜져 있으므로, 이를 따르는 일반 범위 순회자가 있다면 =SUM(B1:B3) 같은 참조 대상은 하필 B2에 SUBTOTAL이 들어 있을 때 B2를 떨어뜨립니다. 반대 방향으로는, 끝날 때 이전 상태를 복원하는 대신 중첩 건너뛰기를 꺼 버리는 SUBTOTAL이 나머지 순회 동안 바깥 AGGREGATE의 정책을 무력화합니다. 일시적인 필드로 재귀 순회에 문맥을 전달하는 평가기라면 모두 이 모양을 가집니다. 필드는 그것을 세운 프레임에는 맞고 그것을 물려받는 모든 프레임에는 틀립니다

가져오기 경계에서 정책을 끊는 방법

HotXLS는 AGGREGATE가 스스로 계산하지 않은 값을 읽는 모든 지점에 경계를 둡니다. 가져오기 전에 두 설정을 저장하고 지우고, 가져온 뒤 finally 블록에서 복원합니다. 바깥 집계는 방금 가져온 셀에는 여전히 자기 정책을 적용합니다. 숨겨진 행과 중첩 셀 검사는 가져오기를 둘러싼 순회자에서 일어나기 때문입니다. 참조된 수식은 아무 정책 없이 실행되며, Excel이 그렇게 합니다. 세 순회자 계열 모두 이 경계를 거칩니다. SUM 계열(SUM, AVERAGE, COUNT, COUNTA, MAX, MIN), 닫힌 형태로 줄여 계산하는 것들(PRODUCT, STDEV, VAR), k 인자를 받는 형태(MEDIAN, LARGE, SMALL, 백분위와 사분위)가 그 대상입니다

// 자체 평가기용 예시 워커. FPolicy, FetchCell,
// IsRowHidden, IsSubtotalCell은 직접 정의하는 멤버입니다
function TMyEvaluator.FetchForAggregate(Sheet, Row, Col: Integer;
  var V: Variant): Integer;
var
  Saved: TAggPolicy;
begin
  Saved := FPolicy;
  FPolicy := Default(TAggPolicy);     // 참조된 수식은 자기 정책을 스스로 소유합니다
  try
    Result := FetchCell(Sheet, Row, Col, V);
  finally
    FPolicy := Saved;
  end;
end;

// 범위 순회 내부: 바깥 정책은 가져오기 주위에서 적용됩니다
for R := R1 to R2 do
begin
  if FPolicy.IgnoreHidden and IsRowHidden(Sheet, R) then
    Continue;
  for C := C1 to C2 do
  begin
    if FPolicy.SkipNested and IsSubtotalCell(Sheet, R, C) then
      Continue;
    Code := FetchForAggregate(Sheet, R, C, V);
    // ... Code / V에 오류 정책을 적용한 뒤 누적합니다 ...
  end;
end;

평범한 범위가 아닌 인자에는 플래그를 지우는 것만으로 부족합니다. A1:A4/(B1:B4-20) 같은 인자는 계산된 배열이고, 옵션 6이 0으로 나눈 원소만 골라내려면 원소 모양이 살아남아야 합니다. HotXLS는 참조된 범위를 같은 경계를 통해 2차원 배열로 바꾸고, 실패한 셀은 중단하는 대신 오류 원소로 저장하며, 산술·비교 연산자를 원소 단위로 적용합니다. 여러 시트에 걸치거나 모서리가 뒤집힌 범위는 #VALUE!를 반환합니다. TXLSCalculator.FormulaArrayMemoryLimit(기본값 64 MB)에 비해 너무 큰 범위는 리소스 한계 결과를 반환하는데, 이 결과는 일부러 옵션 2, 3, 6, 7이 건너뛸 수 있는 오류 집합 밖에 둡니다. 수식이 #N/A 무시를 요청했다는 이유로 엔진 자신의 메모리 부족 신호를 삼키면 그럴듯하게 틀린 숫자가 나오므로, 대신 수식이 실패합니다

오류를 무시하지 않을 때 AGGREGATE는 어떤 오류를 반환할까요?

만나는 첫 번째 오류를 그대로입니다. 모든 오류를 #VALUE!로 뭉개는 것은 쉬운 지름길이지만, 그렇게 하면 #DIV/0! 셀에 대한 AGGREGATE(9,4,A1:A3)가 Excel과 엇나가게 됩니다. Excel은 #DIV/0!을 그대로 통과시키는데도 말입니다. HotXLS는 진짜 오류 Variant와 일곱 가지 오류 문자열(#NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A)을 각자의 코드로 되돌려 매핑하고, 각 순회자는 자신이 기록한 첫 코드를 반환합니다. 한 번도 계산되지 않은 셀은 오류를 캐시된 값이 아니라 가져오기의 반환 코드로 전달하는데, 캐시된 경우와 정확히 같은 방식으로 전파됩니다

두 개의 세는 함수는 SUM이 아니라 SUBTOTAL을 따릅니다. 함수 2인 COUNT는 숫자만 세므로, 옵션 코드와 무관하게 오류 셀을 세지도 전파하지도 않습니다. 함수 3인 COUNTA는 오류 셀을 비어 있지 않은 값으로 보므로, 옵션 코드가 오류를 건너뛰지 않는 한 1로 셉니다. 이 비대칭은 AGGREGATE 밖에서도 Excel이 COUNT와 COUNTA를 다루는 방식입니다. 무조건 "오류면 전파" 규칙을 깔아 버리면 둘 다 틀립니다

하나의 워크시트로 여덟 코드를 모두 검사하기

도입부의 네 셀이 모든 코드를 아우르는 컴팩트한 검사가 됩니다. 코드 0부터 7까지 각각에 대해 A1:A4에 걸친 SUM 형태와 MEDIAN 형태를 평가하고 손으로 계산한 값과 비교해 보세요. 코드 0, 1, 4, 5는 오류를 건너뛰지 않으므로 A3의 #DIV/0!을 전파해야 합니다. 코드 2는 중첩된 A4를 건너뛴 10과 20에서 SUM 30, MEDIAN 15를 줍니다. 코드 3은 10과 10입니다. 코드 6은 A4의 30이 이제 세지므로 60과 20입니다. 코드 7은 40과 20이고, 가져오기 경계 없는 엔진이 20으로 보고하는 바로 그 사례입니다. 이 검사는 AGGREGATE가 실행될 때 A4가 캐시되지 않았을 때만 의미가 있으므로, 참조 대상을 미리 계산해 둔 경우와 그렇지 않은 경우를 각각 한 번씩 평가해 보세요

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 10;
    Sheet.Cells[2, 1].Value := 20;
    Sheet.Cells[3, 1].Formula := '=1/0';
    Sheet.Cells[4, 1].Formula := '=SUBTOTAL(9,A1:A2)';    // 그룹 소계 = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  숨김 건너뜀, 오류 전파
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       숨김, 오류, 중첩 모두 건너뜀
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       오류만 건너뜀
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       20이면 설정이 A4로 샜다는 뜻
    Sheet.Cells[10, 1].Formula := '=AGGREGATE(12,6,A1:A4)'; // 20     10, 20, 30의 MEDIAN
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

저장된 파일을 Excel에서 열고 Ctrl+Alt+F9를 누르면 같은 셀에 대한 두 번째 의견을 얻습니다. 더 넓은 범위를 보려면 함수 번호 1부터 19까지를 여덟 코드 전부와 조합해 돌려 보세요

계산기를 직접 만들 때 콜백 연결하기

두 설정 모두 두 개의 콜백에 의존합니다. TXLSCalculator.Create는 값 게터 뒤에 TXLSIsRowHidden과 TXLSIsSubtotalCell을 받고, 둘 다 기본값은 nil입니다. 하나라도 빠지면 표의 해당 열이 적용되지 않으므로, 모든 옵션 코드가 숨겨진 행·중첩 집계·그 둘 다를 포함하게 됩니다. XLSX와 클래식 XLS 워크북 객체는 생성자에서 자기 구현을 넘기고, 자체 데이터 소스 위에 TXLSCalculator를 직접 만드는 코드는 둘 다 공급해야 합니다. 워크북들은 SUBTOTAL(, AGGREGATE(, _xlfn.AGGREGATE(로 시작하는 수식을 선행 등호의 유무와 관계없이 중첩 집계로 인식하므로, 커스텀 소스도 같은 규칙을 쓰는 것이 좋습니다

// TMySheetSource와 FormulaAt은 자체 타입입니다. 세 메서드
// 시그니처는 TXLSGetValue, TXLSIsRowHidden, TXLSIsSubtotalCell과 같습니다
// (워크북이 넘기는 기준으로 SheetIndex는 1부터, Row와 Col은 0부터입니다)
function TMySheetSource.IsSubtotalCell(SheetIndex, Row, Col: Integer): Boolean;
var
  F: string;
begin
  F := FormulaAt(SheetIndex, Row, Col);
  if (F <> '') and (F[1] = '=') then
    Delete(F, 1, 1);
  Result := SameText(Copy(F, 1, 9), 'SUBTOTAL(') or
            SameText(Copy(F, 1, 10), 'AGGREGATE(') or
            SameText(Copy(F, 1, 16), '_xlfn.AGGREGATE(');
end;

// Extern은 호스트가 이미 갖고 있는 시트 이름 테이블입니다
Calc := TXLSCalculator.Create(Extern, Source.GetCellValue,
  Source.IsRowHidden, Source.IsSubtotalCell);

이 접두사 규칙은 생성되는 워크북에도 영향을 줍니다. =ROUND(SUBTOTAL(9,B2:B9),2)를 써 넣는 생성기는 접두사 검사가 인식하지 못하는 셀을 만들므로, 코드 0부터 3이 그 셀을 세고 그룹이 전체 합계에 두 번 들어갑니다. SUBTOTAL이나 AGGREGATE 호출을 가장 바깥에 두고 반올림은 숫자 형식으로 처리하세요. 전체 Recalculate는 참조 대상을 의존 대상보다 먼저 평가하므로 집계는 보통 캐시된 값을 읽고, 의존성 그래프에 대한 증분 재계산에서도 같은 순서가 유지됩니다. 합계가 여전히 이상한데 수식 텍스트는 멀쩡해 보인다면, 평가를 단계별로 추적해 보세요. 참조 대상이 순회 도중 on-demand로 실행된 것인지, 콜백이 아예 연결되지 않은 것인지가 드러납니다

여기서 설명한 옵션 디코더와 가져오기 경계, 콜백 훅은 HotXLS Delphi 스프레드시트 컴포넌트에 함께 제공됩니다. 이 컴포넌트는 머신에 Excel을 설치하지 않고 Delphi와 C++Builder 코드에서 XLS, XLSX, ODS 워크북을 읽고, 쓰고, 재계산합니다