숨겨진 행을 포함한 워크북에서 SUBTOTAL(109, ...)와 SUBTOTAL(9, ...)가 같은 숫자를 반환한다면, 둘 중 하나는 틀린 것입니다. 델파이와 C++Builder용 네이티브 Excel 스프레드시트 컴포넌트인 HotXLS는 버전 2.197.0까지 정확히 그렇게 동작했습니다. 계산 엔진이 워크시트에게 특정 행이 숨겨져 있는지 물어볼 방법이 전혀 없었기 때문입니다
이 증상은 수식 코드에 관한 버그 리포트로는 좀처럼 도착하지 않습니다. 불일치로 도착합니다: 서버에서 배치 작업이 합계를 계산하고, 사용자는 필터가 적용된 같은 파일을 Excel에서 열며, 두 숫자는 필터링된 행들이 합산했을 값만큼 차이가 납니다. 아무도 집계 함수를 의심하지 않습니다. 셀 안의 수식 문자열은 양쪽에서 동일하기 때문입니다. 차이는 전적으로 평가기가 무엇을 볼 수 있도록 허용되었는가에 있습니다
SUBTOTAL 109가 숨겨진 행을 포함하는 이유는 무엇인가
대부분의 엔진 설계에서 수식을 평가하는 계층은 행 가시성에 대해 결코 알지 못하기 때문입니다. HotXLS는 교과서적인 사례였습니다: lxCalc.pas의 계산 엔진은 (시트, 행, 열) 삼중항에 대한 값을 응답하는 단일 TXLSGetValue 콜백만을 통해 셀 값에 도달했으며 그 외에는 아무것도 없었습니다. 가시성은 행 레코드에 저장된 프레젠테이션 속성이며, 그 레코드의 어떤 부분도 호출 체인을 따라 내려가지 않았습니다. 그래서 엔진은 하나의 집계 경로만 가졌고, SUBTOTAL 함수 번호 테이블의 양쪽 절반 모두 그것으로 귀결되었습니다. 이것은 반올림 오차급의 결함이 아닙니다: 이것이 바로 테이블 뒤쪽 절반이 존재하는 이유 전체입니다. ISO/IEC 29500-1로 발행된 ECMA-376 파트 1은 수식 함수 정의(§18.17.7)에서 SUBTOTAL을 정의하며, 첫 번째 인수가 내부 집계와 숨겨진 행 정책을 모두 선택합니다. 코드 1부터 11까지는 AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, VARP에 매핑되며 수동으로 숨긴 행의 값을 포함합니다. 코드 101부터 111까지는 같은 열한 개 집계를 선택하고 그것들을 제외합니다. 9 대신 109를 입력한 사용자는 숨겨진 데이터에 관한 의도적인 진술을 하고 있는 것이며, 그 구분을 무너뜨리는 엔진은 조용히 그 진술을 뒤집습니다
함수 번호가 엔진 내부에서 무엇에 매핑되는가
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는 이미 대안에 대한 선례를 갖고 있었습니다: 3D 참조가 외부 워크북으로 해석되었을 때 기록하기 위해 GetRangeInfo가 사용하는 스크래치 필드와 같은 정신을 가진, 계산기에 대한 임시 필드입니다. 버전 2.197.0은 두 번째 것을 추가했습니다. 엔진은 (SheetIndex, row)를 받아 Boolean을 반환하는 함수로 선언된 콜백 타입 TXLSIsRowHidden을 얻었고, 이는 FIsRowHidden에 저장되며, 여기에 임시 플래그 FIgnoreHiddenRows가 추가되었습니다. 이 플래그는 함수 코드가 101부터 111 사이일 때 CalcSubtotalFunc의 진입점에서, 그리고 숨겨진 행 제외를 선택하는 AGGREGATE 옵션 코드에 대해서는 CalcAggregateFunc의 진입점에서 무장됩니다. 그런 다음 모든 셀 순회 루프가 그것을 검사하고 세팅되어 있으면 한 행을 건너뛰며, 각각 한 줄만 추가됩니다
// The shape repeated in all six cell-walk loops
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
// ... fold Cells[rr, cc] into the accumulator ...
end;
end;무장 코드의 두 가지 세부 사항이 전체 방식의 정확성을 지탱합니다. 플래그는 단순히 세팅되고 해제되는 것이 아니라 저장되고 복원됩니다. SUBTOTAL 인수는 바깥쪽 집계가 아직 스택에 있는 동안 자체 평가를 실행하는 표현식을 포함할 수 있으며, 그 중첩된 작업은 바깥쪽 게이트를 물려받거나 파괴해서는 안 되기 때문입니다. 그리고 복원은 finally 블록 안에 있습니다. CalcSubtotalFunc는 오류 코드에 대해 여러 조기 종료 지점을 갖고 있기 때문입니다. 오류 반환 후에도 무장된 채로 남은 플래그는 재계산 순서상 다음의 무관한 수식을 조용히 손상시킬 것입니다
prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
FIgnoreHiddenRows := True;
try
// aggregate over Item.Child[2] .. Item.Child[ChildCount]
// every Exit path below is covered by the finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;Assigned 검사가 바로 이 변경을 호환 가능하게 유지하는 요소입니다. HotXLS는 계산기 생성자를 기본값이 nil인 세 번째 매개변수로 확장했으므로, 예전의 두 인수 호출로 TXLSCalculator를 만드는 코드는 여전히 컴파일되고 여전히 레거시의 숨김 포함 동작을 얻습니다. 기존 API의 형태는 아무것도 변하지 않았습니다
숨겨진 행 비트는 실제로 어디서 오는가
워크시트로부터, 두 개의 서로 다른 소스를 거쳐 옵니다. HotXLS가 두 개의 워크북 엔진을 갖고 있기 때문입니다. 레거시 BIFF 쪽은 TXLSWorkbook.GetRowHidden을 통해 도달하는 TXLSRowInfoList.GetHidden에서 응답합니다. OOXML 쪽은 TXLSXWorkbook.GetCalcRowHidden을 통해 도달하는 TXLSXWorksheet.GetRowHidden에서 응답합니다. 둘 다 생성 시점에 계산기에 연결되며, 그것이 반영하는 셀 값 콜백과 나란히 배선됩니다. 이런 종류의 다리는 보통 행 관례에서 잘못되므로, 명시적으로 짚어둘 가치가 있습니다. 계산기는 콜백에게 0 기반 행을 건넵니다. 이는 TXLSGetValue가 이미 사용하는 좌표와 일치합니다. XLSX 워크시트는 Excel이 행 번호를 매기는 것과 정확히 같은 1 기반 행 번호로 행 숨김 맵의 키를 정하며, 이는 공개 RowHidden[ARow] 속성이 노출하는 것과도 같습니다. 그래서 XLSX 브리지는 조회 전에 1을 더하고, BIFF 브리지는 그렇게 하지 않습니다. TXLSRowInfoList가 이미 0 기반이기 때문입니다. 두 브리지 모두 유효 범위를 벗어난 시트 인덱스나 행을 가시적인 것으로 취급하므로, 범위를 벗어난 질의는 데이터를 누락시키는 대신 예전의 숨김 포함 응답으로 저하됩니다
필터링된 워크북에서 무엇이 바뀌는가
이것이 지원 티켓을 만들어내는 경우입니다. HotXLS에서 ApplyAutoFilter를 통해 자동 필터를 적용하면 열 기준을 평가하고 일치하지 않는 모든 데이터 행을 숨깁니다. 이는 사용자가 필터 드롭다운을 클릭했을 때 Excel이 정확히 하는 일입니다. v2.197.0 이전에는 그 숨겨진 행들이 사용자에게는 보이지 않으면서 계산 엔진에게는 완전히 보였으므로, 서버 측 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; // hides the non-matching rows
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// The cell value now agrees with what Excel shows for the same filter,
// and VisibleRows tells you how many rows fed into it
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;수동 숨기기도 같은 방식으로 작동합니다. RowHidden[ARow] := True가 필터가 쓰는 것과 같은 상태이기 때문입니다. 그 동등성은 Excel에서 의도된 것이며 이제 HotXLS에서도 성립합니다. 결과 하나는 생성된 워크북과 함께 제공되는 문서 어디엔가 메모해 둘 가치가 있습니다: 코드 109로 계산된 합계는 뷰에 의존하는 숫자이므로, 필터를 해제하는 수신자는 그것을 바꿉니다. 보고서가 독자가 뷰에 무엇을 하든 상관없이 고정된 수치를 진술해야 할 때는, 코드 9가 올바른 선택이며 언제나 그래왔습니다. 필터, 검증, 표는 데이터 검증, 자동 필터, 표에 관한 글에서 함께 다룹니다. 행을 숨기는 것은 어떤 수식도 건드리지 않으므로, 그 자체로 의존성 그래프를 더럽히지도 않습니다. 더러워진 서브그래프에 대한 증분 재계산에 의존해 대형 워크북을 반응성 있게 유지하고 있다면 알아둘 가치가 있습니다
AGGREGATE 옵션 코드와 아직 남아 있는 한 가지 한계
AGGREGATE는 두 번째 정책 인수를 가진 SUBTOTAL이며, HotXLS는 이를 CalcAggregateFunc에서 처리합니다. 옵션 인수는 독립적인 스위치들을 인코딩합니다: 범위 내부의 중첩된 SUBTOTAL과 AGGREGATE 호출을 건너뛸지, 숨겨진 행의 값을 건너뛸지, 오류 값을 전파하는 대신 억제할지 여부입니다. HotXLS는 옵션 코드 2, 3, 6, 7에 대해 공유된 숨겨진 행 게이트를 무장하고, 옵션 코드 4부터 7까지에 대해 오류 값을 억제합니다. 그런 다음 함수 번호 인수는 SUBTOTAL과 똑같이 집계를 선택하며, 분산, 표준편차, 곱의 자체 리듀서로의 라우팅도 포함합니다. 문서화된 한 가지 간극이 남아 있으며, 이는 실전에서 발견되는 것보다 여기서 언급하는 편이 낫습니다: 낮은 옵션 코드에 연관된 중첩 SUBTOTAL 무시 의미론은 HotXLS에 구현되어 있지 않습니다. 참조된 범위 안의 중첩 SUBTOTAL을 감지하려면 평가기의 재귀 상태를 표시해서 내부 집계가 자신을 바깥쪽에 알릴 수 있게 해야 하는데, 이는 숨겨진 행 게이트보다 더 큰 변경입니다. 실전에서 그 노출은 작습니다. 실제 워크북은 거의 항상 SUBTOTAL 수식을 다른 SUBTOTAL 수식이 집계하는 범위 밖에 배치하기 때문입니다. 여러분의 생성기가 겹치는 집계 범위를 실제로 만든다면, 그것들을 중복 제거하기 위해 낮은 옵션 코드에 의존하지 마십시오
함께 출시된 인수 개수 가드
버전 2.197.0은 같은 디스패처에서 검증 간극 하나도 함께 닫았으며, 그 설계 이유는 스크래치 필드를 동기부여했던 것과 같습니다: 한 번만 작성될 수 있는 곳에 검사를 두는 것입니다. 대략 280개의 내장 함수 본문이 각각 자신의 인수 개수를 Item.ChildCount에 대해 스스로 검증했으며, 인수가 너무 많은 경우에 대한 일관된 경계는 없었습니다. =SIN(1,2) 같은 호출은 첫 번째 인수만 검사하고 나머지는 무시하는 함수 본문에 도달해, Excel이 #VALUE!를 반환할 자리에 그럴듯한 숫자를 반환했습니다. HotXLS는 이미 모든 내장 함수의 선언된 인수 개수를 함수 레지스트리에 저장하고 있었으며, 이는 THashFunc.ArgsCnt로 노출되고 -1은 SUM, IF, CONCAT 같은 가변 인수 함수를 표시합니다. 버전 2.197.0은 그것을 새로운 TXLSFormula.FuncArgsCntByPtg 속성을 통해 전달했고 주 디스패처인 GetValueItemFunc의 맨 위에 게이트 하나를 추가했습니다
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] is the function node
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) now yields #VALUE!
Exit;
end;
end;이 가드는 너무 많은 인수를 거부하며, 인수가 너무 적은 경우에 대해서는 의도적으로 아무 말도 하지 않습니다. 후행 선택적 인수를 생략하는 것은 VLOOKUP, SUBSTITUTE 등 긴 목록의 함수에서 Excel에서 합법이므로, 대칭적인 검사는 틀린 수식을 잡기 위해 올바른 수식을 깨뜨렸을 것입니다. 알려지지 않은 식별자는 가변 인수로 보고되어 게이트를 완전히 건너뜁니다. 이것이 사용자 정의 함수를 그 방해에서 벗어나게 해줍니다. 여러분 자신의 함수를 등록한다면, 수식 엔진과 사용자 정의 함수 안내서에서 설명하는 동작은 영향받지 않습니다. 인수가 너무 적은 경우를 중앙화하는 것은 별개의 작업입니다. 그 280개 본문 각각이 자신만의 오류 코드 의미론을 갖고 있어서 하나씩 개별적으로 검토해야지 그냥 가정할 수 없기 때문입니다
여기서 설명한 계산 엔진, 두 워크북 파사드, 그리고 이를 공급하는 AutoFilter와 행 가시성 API는 HotXLS 델파이 스프레드시트 컴포넌트의 일부이며, 델파이와 C++Builder용 전체 소스와 함께 제공되고 이를 실행하는 머신에 Excel 설치가 필요하지 않습니다