LlosLab Software Development BlogPDF, Delphi 및 문서 소프트웨어 개발자를 위한 엔지니어링 노트.

기술 문서

델파이 HotXLS의 SUBTOTAL AGGREGATE 숨겨진 행

HotXLS Delphi Component

숨겨진 행을 포함한 워크북에서 SUBTOTAL(109, ...)와 SUBTOTAL(9, ...)가 같은 숫자를 반환한다면 둘 중 하나가 틀린 겁니다. 스프레드시트 계산 엔진을 만들 때 가장 빠지기 쉬운 함정 중 하나인데, Delphi와 C++Builder용 네이티브 Excel 스프레드시트 컴포넌트인 HotXLS는 계산 엔진이 워크시트에 특정 행이 숨겨져 있는지 물어보게 하는 방식으로 이 함정을 피합니다

이 증상은 수식 코드에 대한 질문으로는 좀처럼 도착하지 않습니다. 불일치로 도착합니다. 서버의 배치 작업이 합계를 계산하고, 사용자가 같은 파일을 Excel에서 필터를 걸어 열면 두 숫자는 걸러진 행들의 합만큼 어긋납니다. 아무도 집계 함수를 의심하지 않습니다. 셀 안의 수식 문자열은 양쪽에서 동일하기 때문입니다. 차이는 전적으로 평가기가 볼 수 있었던 것에 있습니다

SUBTOTAL 109는 왜 숨겨진 행을 포함할까

대부분의 엔진 설계에서는 수식을 평가하는 계층이 행 가시성을 알 방법이 없기 때문입니다. 무난한 설계는 lxCalc.pas의 TXLSGetValue 콜백처럼 값 콜백 하나로 셀 값을 가져옵니다. 이 콜백은 (시트, 행, 열) 트리플에 대해 값만 답하고 그 이상은 아무것도 주지 않습니다. 가시성은 행 레코드에 저장된 표현 속성이고, 호출 체인을 따라 다른 정보가 내려가지 않으면 엔진은 SUBTOTAL 함수 번호 테이블의 두 절반이 모두 귀결되는 집계 경로 하나만 갖게 됩니다. 반올림 오류 부류의 실수가 아닙니다. 숨겨진 행 정책이야말로 테이블의 후반이 존재하는 이유 그 자체이기 때문입니다. ISO/IEC 29500-1으로 발행된 ECMA-376 Part 1은 수식 함수 정의(§18.17.7)에서 SUBTOTAL을 정의하며, 첫 번째 인수가 내부 집계와 숨겨진 행 정책을 함께 선택합니다. 코드 1부터 11까지는 AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, VARP에 매핑되며 수동으로 숨겨진 행의 값도 포함합니다. 코드 101부터 111까지는 같은 열한 가지 집계를 선택하되 이를 제외합니다. 9 대신 109를 입력하는 사용자는 숨겨진 데이터에 대해 의도를 밝히는 것이고, 그 구분을 지워버리는 엔진은 그 의도를 조용히 무효로 만듭니다

Delphi용 HotXLS의 SUBTOTAL 함수 번호 테이블 다이어그램: 코드 1-11은 수동으로 숨겨진 행을 포함하고 코드 101-111은 제외한다. 서버와 Excel의 합계 불일치를 함께 보여준다
코드 1-11과 101-111은 반대되는 숨겨진 행 정책으로 같은 열한 가지 집계를 선택합니다. 셀 값만 보는 엔진은 이 구분을 하나의 집계 경로로 뭉개버립니다

함수 번호가 엔진 안에서 무엇에 매핑되는가

HotXLS는 CalcSubtotalFunc에서 SUBTOTAL의 첫 번째 인수를 해석합니다. 이 함수는 코드 101부터 111까지를 코드 1부터 11과 같은 내부 함수 식별자로 정규화한 뒤 집계 자체를 기준으로 디스패치합니다. 이 집합 대부분은 SUM, COUNT, COUNTA, MIN, MAX, AVERAGE를 담당하는 점진적 ExcelSum 누산기를 통과합니다. 다섯 가지는 그럴 수 없습니다. STDEV, VAR, STDEVP, VARP, PRODUCT는 데이터에 대한 닫힌 형태의 한 차례 순회가 필요하므로, CalcSubtotalFunc는 내부 코드 12, 46, 193, 194, 183을 별도의 리듀서인 SubtotalReduceVariance로 보냅니다. 손대기 전에 가장 먼저 파악할 부분이 바로 이 분기입니다. 독립적인 집계 경로가 둘이면 독립적인 셀 순회 루프도 둘이고, 그중 하나에만 숨겨진 행 검사를 넣으면 최악의 결과가 나옵니다. 같은 범위에서 SUBTOTAL(109, ...)는 필터를 존중하는데 SUBTOTAL(107, ...)는 그렇지 않게 됩니다. AGGREGATE까지 포함하면 HotXLS에는 여섯 개의 순회 경로가 있으며, 범위 평가, 단순 범위 수집, 세 개의 개별 리듀서에 걸쳐 분포합니다

여섯 개의 새 시그니처 대신 스크래치 필드를 쓰는 이유

불리언 하나를 위해 새 매개변수를 여섯 개의 셀 순회 함수와 그 호출부 전부에 관통시키는 건, 뜨거운 코드 경로에 가하는 너무 넓은 변경이기 때문입니다. HotXLS에는 대안의 전례가 있습니다. 계산기의 임시 필드입니다. GetRangeInfo가 3D 참조가 외부 워크북으로 해석됐음을 기록할 때 쓰는 스크래치 필드가 같은 정신입니다. 숨겨진 행 게이트는 그 두 번째입니다. (SheetIndex, row)를 받아 Boolean을 반환하는 함수로 선언된 콜백 타입 TXLSIsRowHidden을 FIsRowHidden에 저장하고, 여기에 임시 플래그 FIgnoreHiddenRows를 더한 것입니다. 플래그는 함수 코드가 101에서 111 사이일 때 CalcSubtotalFunc 진입점에서, 그리고 숨겨진 행 제외를 선택하는 AGGREGATE 옵션 코드에 대해 CalcAggregateFunc 진입점에서 켜집니다. 이후 모든 셀 순회 루프는 이 플래그를 검사해 켜져 있으면 해당 행 하나를 건너뛰며, 비용은 루프당 한 줄입니다

HotXLS Delphi 계산 엔진의 숨겨진 행 게이트 다이어그램: CalcSubtotalFunc와 CalcAggregateFunc가 FIgnoreHiddenRows를 켜고, 여섯 개의 셀 순회 루프가 FIsRowHidden을 조회해 숨겨진 행을 건너뛴다
하나의 스크래치 필드와 하나의 콜백이 여섯 개의 셀 순회 루프 전부를 관문으로 묶습니다. 이전 플래그 값은 모든 종료 경로의 finally 블록에서 복원됩니다
// 여섯 개의 셀 순회 루프 모두에서 반복되는 형태
for sh := s1 to s2 do
  for rr := r1 to r2 do
  begin
    if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
      Continue;
    for cc := c1 to c2 do
    begin
      // ... Cells[rr, cc]를 누산기에 합산합니다 ...
    end;
  end;

무장 코드의 두 가지 세부 사항이 전체 방식의 정확성을 떠받칩니다. 플래그는 단순히 설정하고 해제하는 대신 저장하고 복원합니다. SUBTOTAL 인수 안의 표현식이 바깥쪽 집계가 아직 스택에 있는 동안 자체 평가를 실행할 수 있고, 그 중첩 작업이 바깥쪽 게이트를 물려받거나 망가뜨려서는 안 되기 때문입니다. 그리고 복원은 finally 블록에 둡니다. CalcSubtotalFunc에는 오류 코드로 빠져나가는 조기 종료가 여럿 있어서, 오류 반환 후 켜진 채로 남은 플래그는 재계산 순서상 다른 무관한 수식을 조용히 오염시킬 것입니다

prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
  FIgnoreHiddenRows := True;
try
  // Item.Child[2] .. Item.Child[ChildCount]에 대해 집계
  // 아래의 모든 Exit 경로는 finally가 뒤덮습니다
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
end;

Assigned 검사가 계산기 API의 호환성을 지켜주는 장치입니다. TXLSCalculator 생성자는 기본값이 nil인 세 번째 매개변수를 받으므로, 두 인수 호출로 계산기를 만드는 코드는 그대로 컴파일되며 숨겨진 행 포함 동작을 얻습니다

숨겨진 행 비트는 실제로 어디서 오는가

워크시트로부터, 두 가지 서로 다른 경로를 거쳐 옵니다. HotXLS가 워크북 엔진 두 개를 갖고 있기 때문입니다. 레거시 BIFF 쪽은 TXLSWorkbook.GetRowHidden을 통해 닿는 TXLSRowInfoList.GetHidden이 답하고, OOXML 쪽은 TXLSXWorkbook.GetCalcRowHidden을 통해 닿는 TXLSXWorksheet.GetRowHidden이 답합니다. 둘 다 자신이 짝지은 셀 값 콜백과 함께 생성 시점에 계산기에 연결됩니다. 이런 브리지가 보통 잘못되는 지점이 행 번호 규약이므로 명시적으로 적어둘 가치가 있습니다. 계산기는 콜백에 0-based 행을 넘깁니다. TXLSGetValue가 이미 쓰는 좌표와 맞추기 위함입니다. XLSX 워크시트는 행 숨김 맵을 1-based 행 번호로 키잉합니다. Excel이 행을 매기는 방식 그대로이며, 공개 RowHidden[ARow] 속성이 노출하는 것과 같습니다. 그래서 XLSX 브리지는 조회 전에 하나를 더하고, TXLSRowInfoList가 이미 0-based인 BIFF 브리지는 더하지 않습니다. 두 브리지 모두 유효 범위를 벗어난 시트 인덱스나 행은 보이는 것으로 취급하므로, 범위 밖 질의는 데이터를 떨어뜨리는 대신 숨겨진 행 포함 답으로 완화됩니다

두 개의 HotXLS Delphi 워크북 엔진이 행 숨김 상태를 계산기로 브리징하는 다이어그램. BIFF 브리지는 행 시프트가 필요 없고 XLSX 브리지는 1-based 맵을 위해 하나를 더한다
BIFF 행 목록은 이미 0-based이고 XLSX 행 숨김 맵은 1-based이므로, 조회 전에 행을 밀어주는 브리지는 XLSX 쪽 하나뿐입니다. 범위 밖 질의는 보임으로 답합니다

필터링된 워크북에서는 무엇이 달라지나

숨겨진 행 정책이 가장 중요해지는 경우가 바로 이것입니다. HotXLS에서 ApplyAutoFilter로 자동 필터를 적용하면 열 기준을 평가해 일치하지 않는 모든 데이터 행을 숨깁니다. 사용자가 필터 드롭다운을 클릭할 때 Excel이 하는 것과 정확히 같은 동작입니다. 이 행들은 사용자에게 보이지 않고, 계산 엔진이 같은 행 상태를 조회하므로 서버 쪽 SUBTOTAL(109, ...)는 필터링되지 않은 합계가 아니라 필터링된 합계를 보고합니다

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  VisibleRows: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
    VisibleRows := Sheet.ApplyAutoFilter;   // 일치하지 않는 행을 숨깁니다

    Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
    Book.Recalculate;
    // 셀 값은 Excel이 같은 필터에서 보여주는 것과 일치하고,
    // VisibleRows는 그 값에 들어간 행 수를 알려줍니다

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

수동 숨김도 같은 방식으로 동작합니다. RowHidden[ARow] := True가 필터가 쓰는 것과 같은 상태이기 때문입니다. 이 동등성은 Excel에서 의도된 것이며 HotXLS에서도 유지됩니다. 생성한 워크북에 함께 실리는 문서에는 한 가지 결과를 적어둘 만합니다. 코드 109로 계산한 합계는 보기(view)에 의존하는 숫자이므로, 필터를 해제하는 수신자가 그 값을 바꿉니다. 보고서가 읽는이가 보기를 어떻게 만지든 고정된 수치를 말해야 한다면 코드 9가 올바른 선택이며 항상 그래왔습니다. 필터, 데이터 유효성, 테이블은 데이터 유효성, AutoFilter, 테이블을 다룬 글에서 함께 정리했습니다. 행 숨김은 어떤 수식도 건드리지 않으므로 그 자체로는 의존성 그래프를 더럽히지 않는데, 더러워진 하위 그래프에 대한 점진적 재계산으로 큰 워크북의 반응성을 유지한다면 알아둘 가치가 있습니다

AGGREGATE 옵션 코드

AGGREGATE는 두 번째 정책 인수를 가진 SUBTOTAL이며, HotXLS는 이를 CalcAggregateFunc에서 처리합니다. 옵션 인수는 독립적인 스위치들을 인코딩합니다. 범위 안에 중첩된 SUBTOTAL과 AGGREGATE 호출을 건너뛸지, 숨겨진 행의 값을 건너뛸지, 오류 값을 전파하는 대신 억제할지입니다. HotXLS는 옵션 코드 2, 3, 6, 7에 대해 공유 숨겨진 행 게이트를 켜고, 옵션 코드 4부터 7까지에 대해 오류 값을 억제합니다. 함수 번호 인수는 그다음 분산, 표준편차, 곱이 자체 리듀서로 라우팅되는 것까지 포함해 SUBTOTAL과 정확히 같은 방식으로 집계를 선택합니다. 실제 워크북은 거의 항상 SUBTOTAL 수식을 다른 SUBTOTAL 수식이 집계하는 범위 바깥에 둡니다. 생성기가 겹치는 집계 범위를 만든다면 옵션 코드에 중복 제거를 맡기지 말고 안쪽 합계를 바깥 범위에서 빼두는 것이 안전합니다

같은 디스패처에 있는 인수 개수 가드

같은 디스패처에는 인수 개수 검사도 들어 있고, 설계 이유는 스크래치 필드 뒤에 있던 것과 같습니다. 검사를 한 번만 쓸 수 있는 곳에 두는 것입니다. 빌트인 함수 본문이 약 280개에 이르는 상황에서 각 본문이 Item.ChildCount와 비교해 자기 인수 개수를 검증하게 두면, 인수가 너무 많은 경우에 일관된 경계가 생기지 않고 함정도 놓치기 쉽습니다. =SIN(1,2) 같은 호출은 첫 번째 인수만 살펴보고 잉여분을 무시한 뒤 그럴듯한 숫자를 반환하는 함수 본문에 도달하지만, Excel은 #VALUE!를 반환합니다. HotXLS는 모든 빌트인의 선언 인수 개수를 함수 레지스트리에 저장해 THashFunc.ArgsCnt로 노출합니다(-1은 SUM, IF, CONCAT 같은 가변 인수 함수 표시). 이를 TXLSFormula.FuncArgsCntByPtg 속성으로 전달하고, 메인 디스패처인 GetValueItemFunc 맨 위의 게이트 하나에서 검사합니다

lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
  lProvidedArgs := Item.ChildCount - 1;   // Child[0]은 함수 노드입니다
  if lProvidedArgs > lDeclaredArgs then
  begin
    Result := lxErrorValue;               // =SIN(1,2)는 #VALUE!를 반환합니다
    Exit;
  end;
end;

이 가드는 인수가 너무 많은 경우를 거부하고, 너무 적은 경우에는 의도적으로 아무 말도 하지 않습니다. 후행 선택적 인수를 생략하는 것은 VLOOKUP, SUBSTITUTE 등 긴 목록의 함수에서 Excel이 허용하는 동작이라, 대칭적인 검사는 잘못된 수식을 잡겠다고 올바른 수식을 깨뜨립니다. 알 수 없는 식별자는 가변 인수로 보고되어 게이트를 완전히 건너뛰며, 사용자 정의 함수가 이 검사의 방해를 받지 않는 이유가 바로 이것입니다. 직접 함수를 등록해도 수식 엔진과 커스텀 함수 가이드에서 설명한 동작은 그대로입니다. 인수가 너무 적은 경우는 각 함수 본문의 몫으로 남습니다. 각 본문마다 오류 코드 의미 체계가 다르기 때문입니다

여기서 설명한 계산 엔진, 두 워크북 파사드, 그리고 이를 공급하는 AutoFilter와 행 가시성 API는 HotXLS Delphi 스프레드시트 컴포넌트의 일부입니다. Delphi와 C++Builder용 전체 소스와 함께 제공되며 실행 머신에 Excel 설치를 요구하지 않습니다