Delphi 피벗 Show Values As: 순위와 누적 합계

HotXLS는 TXLSPivotDataField의 두 속성으로 Delphi에서 Excel 피벗 테이블 "Show Values As" 모드를 설정합니다: 누적 합계, 차이, 행 퍼센트 같은 클래식 모드용 ShowDataAs와 순위, 부모 퍼센트, 누적 합계 퍼센트용 ExtendedShowDataAs입니다. TXLSXWorkbook.MaterializeNativePivotTable은 이어서 계산된 그리드를 Excel이 그리는 방식으로 기록합니다. 두 속성은 상호 배타적이고, 확장 쪽은 Excel이 대소문자를 구분해 매칭하는 확장 URI에 의존합니다

마지막 디테일이 이 글이 존재하는 이유입니다. 순위 열이 달린 피벗 테이블은 생성하기에 사소해 보이고, 첫 열기에서는 보통 실제로 사소합니다. 문제는 사용자가 Excel에서 Refresh를 클릭하고 순위 열이 아무 경고 없이 평범한 합계로 돌아갈 때 시작됩니다. Show Values As를 제대로 하려면 두 개의 XML 어휘를 제대로 잡아야 하고, 거기에 Excel이 UI에 문서화하지 않는 숫자 규칙 한 줌이 더해집니다: 동률은 어떻게 순위를 매기는지, 평균의 누적 퍼센트는 무슨 의미인지, 어떤 빈 셀이 공백이 되고 어떤 셀이 에러가 되는지

Excel 메뉴 하나에 속성이 둘인 이유

Excel은 "Show Values As" 메뉴를 하나 보여 주지만, 파일에는 두 군데에 저장되므로 HotXLS는 속성을 둘 노출합니다. Excel 2007의 원조 모드들은 ECMA-376 ST_ShowDataAs 열거형을 쓰며 pivotTableDefinition 안의 dataField 요소의 showDataAs 속성에 삽니다. Excel 2010이 추가한 모드들, 즉 순위와 부모 퍼센트, 누적 퍼센트는 그 열거형에 안 맞았으므로 pivotShowAs 속성을 단 x14:dataField 요소로 들어가 같은 데이터 필드의 extLst 확장 블록에 싸입니다

TXLSPivotShowDataAs는 첫 그룹을 반영합니다: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal, xlpsdaIndex. TXLSPivotExtendedShowDataAs는 둘째를 다룹니다: xlpesdaPercentOfParent, xlpesdaPercentOfParentRow, xlpesdaPercentOfParentColumn, xlpesdaPercentOfRunningTotal, xlpesdaRankAscending, xlpesdaRankDescending, 그리고 중립 값인 xlpesdaNone. 데이터 필드는 두 그룹 중 하나만 쓰고, 둘 다는 결코 아닙니다. ShowDataAs를 non-normal 값으로 남겨 둔 채 확장 모드까지 설정하면 MaterializeNativePivotTable은 -1을 반환하고 기존 출력을 셀과 피벗 경계까지 포함해 그대로 둡니다

BaseField와 BaseItem은 비교 대상을 운반합니다. BaseField는 피벗 테이블의 필드 리스트에 대한 0부터 시작하는 인덱스이지 소스 열의 헤더 이름에 대한 것이 아니며, 비교, 누적 합계, 선택한 부모의 퍼센트, 순위를 이끕니다. BaseItem은 그 필드 안에서 0부터 시작하는 항목 위치이거나, "이웃과 비교"를 뜻하는 상수 xlPivotBaseItemPrevious와 xlPivotBaseItemNext 중 하나입니다. 부모 행과 부모 열 퍼센트는 BaseField를 완전히 무시합니다; 매칭되는 축에서 가장 안쪽 필드를 고릅니다

Delphi에서 순위 피벗 만들기

가장 짧은 동작 경로는 피벗을 만들고, 데이터 필드에 표시 모드를 설정하고, 저장 전에 머티리얼라이즈하는 것입니다. 아래 예제는 카테고리별 지역 금액을 내림차순으로 순위 매겨, 각 행에서 가장 큰 금액이 1위가 되게 합니다

uses
  SysUtils, lxHandleX, lxPivot;

var
  Book: TXLSXWorkbook;
  Data, Report: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
  Measure: TXLSPivotDataField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Data.Cells[1, 1].Value := 'Category';
    Data.Cells[1, 2].Value := 'Region';
    Data.Cells[1, 3].Value := 'Amount';
    // ... 2..7행을 채웁니다 ...
    Report := Book.Sheets.Add('Report');

    Pivot := Report.AddPivotTable('Data!A1:C7', 4, 3, 'RegionRank');
    Pivot.AddRowField('Category');     // 피벗 필드 0
    Pivot.AddColumnField('Region');    // 피벗 필드 1
    Measure := Pivot.AddDataFieldByName('Amount', xlpaSum);
    Measure.DisplayName := 'Rank by Region';

    Measure.ShowDataAs := xlpsdaNormal;              // 기본 모드는 끔
    Measure.ExtendedShowDataAs := xlpesdaRankDescending;
    Measure.BaseField := 1;                          // Region 전체에서 순위

    if Book.MaterializeNativePivotTable(Pivot) <> 1 then
      raise Exception.Create('Pivot display mode was rejected');
    Book.SaveAs('region-rank.xlsx');
  finally
    Book.Free;
  end;
end;

여기서 두 가지 습관이 값어치를 합니다. 첫째, 확장 모드를 고를 때 ShowDataAs := xlpsdaNormal을 명시적으로 설정하세요. 새 필드에서조차 그렇습니다. 같은 코드가 데이터 필드에 이미 기본 모드를 달고 있는 임포트된 피벗에 재활용되는 일이 잦기 때문입니다. 둘째, 반환값을 확인하세요. 거부된 머티리얼라이제이션은 원자적이라 좋지만, 확인을 빠뜨리면 이전 그리드가 그대로 든 파일이 나갑니다

BaseField는 MaterializeNativePivotTable을 한 번도 호출하지 않아도 저장을 살아남습니다. HotXLS는 확장 모드가 설정되는 즉시 부모 퍼센트, 누적 합계 퍼센트, 두 순위 모드에 대해 baseField 속성을 기록하므로, 설정 직후 저장된 워크북도 새로고침 시 어느 필드를 걸쳐 순위를 매길지 Excel에게 알려 줍니다

확장 URI에서는 무엇이 잘못될까요?

pivotShowAs를 운반하는 확장 블록은 URI {E15A36E0-9728-4e99-A89B-3F7291B0FE68}로 식별되며, Excel은 새로고침 중 그 문자열을 대소문자 구분해서 비교합니다. 4e99의 소문자 e를 주목하세요. GUID는 보통 대소문자를 구분하지 않고 많은 XML 작성기가 대문자로 올리는데, 그것이 바로 실패 모드입니다: 4E99가 든 파일은 캐시된 셀이 모두 옳은 채로 Excel에서 열립니다. Excel은 저장된 값을 그저 칠할 뿐이니까요. 하지만 RefreshTable 후에는 Excel이 확장을 더 이상 인식하지 못하고, 확장 모드를 버리고, 열을 평범한 합계로 다시 계산합니다. 복구 프롬프트도 에러도 없이, 순위 열이 조용히 합계가 되었을 뿐입니다

HotXLS는 URI를 Excel이 기대하는 문자 그대로 기록하므로, 이것이 물리는 경우는 하류의 무언가가 패키지를 다시 쓸 때입니다. GUID 케이싱을 정규화하거나 extLst 요소를 재조립하는 후처리 단계 같은 것이요. 이것을 잡는 테스트는 싸게 만들 수 있습니다: 생성된 파일을 Excel에서 열고, 피벗을 새로고침하고, 확장 열이 날 집계가 아니라 여전히 순위나 퍼센트를 보여 주는지 확인하세요. 캐시된 셀만 읽는 테스트는 깨진 파일에서도 언제나 통과합니다

Excel은 동률 순위와 누적 합계 순서를 어떻게 매기나요?

Excel의 피벗 순위는 밀집 순위(dense rank)이고 HotXLS는 그것을 재현합니다. 내림차순 금액이 30, 30, 20이면 결과는 1, 1, 2이지 워크시트에서 RANK.EQ가 주는 경기식 1, 1, 3이 아닙니다. 동률은 하나의 서로 다른 선행 값으로 셉니다. 리포트가 경기식 순위를 필요로 하면 소스 데이터나 피벗 옆의 워크시트 수식에서 계산하세요. 그걸 만들어 내는 피벗 표시 모드는 존재하지 않습니다

누적 합계, 누적 합계 퍼센트, 이전 또는 다음 비교는 필드의 항목 리스트 순서가 아니라 멤버가 실제로 나타나는 순서를 따릅니다. 캐시가 만들어진 뒤 호출자가 정렬을 바꾸는 순간 이 구분이 중요해집니다

// 행을 내림차순 정렬: 누적 합계는 표시 순서로 쌓입니다
Pivot.Fields[0].SortType := xlpfstDescending;

Measure.ExtendedShowDataAs := xlpesdaNone;
Measure.ShowDataAs := xlpsdaPercentDiff;   // ... 기준 대비 % 차이
Measure.BaseField := 0;                    // ... Category 필드 기준
Measure.BaseItem := xlPivotBaseItemPrevious; // ... 이전 표시 멤버 기준

if Book.MaterializeNativePivotTable(Pivot) <> 1 then
  raise Exception.Create('Pivot display mode was rejected');

SortType이 xlpfstDescending으로 바뀐 뒤에는 "이전"이 위에 표시된 멤버를 뜻하고, 누적 합계는 정렬된 리스트의 꼭대기에서 시작합니다. 대신 저장된 항목 리스트를 훑는 구현은 그럴듯해 보이지만 누군가 새로고침하는 순간 Excel과 어긋나는 숫자를 만듭니다. 가장자리 멤버도 주의가 필요합니다: 첫 멤버에는 이전 이웃이 없고, 가장자리 멤버를 자기 자신과 비교하는 것은 percent-of 모드뿐입니다; 차이 모드들은 0을 발명하는 대신 그 셀을 Excel 자체의 가장자리 동작에 맡깁니다

누적 퍼센트가 100퍼센트를 넘을 수 있는 이유

평균 위의 누적 합계 퍼센트는 정당하게 100퍼센트를 넘을 수 있습니다. Excel은 표시된 멤버 집계를 쌓고 그 합이 아닌 어떤 집계로 나누기 때문입니다. 합계에서는 둘이 같은 것이므로 마지막 멤버가 정확히 100퍼센트에 착지합니다. 평균에서는 세 카테고리 평균을 더해 전체 평균으로 나누는 것은 아무것의 비율도 아니고, 마지막 행은 100퍼센트를 한참 넘을 수 있습니다. HotXLS는 값 집계를 자르는 대신 여기서 일부러 Excel에 맞춥니다. 잘린 숫자는 새로고침한 파일과 어긋날 테니까요

제수는 베이스 필드가 어디 앉아 있는지에도 달렸습니다. 베이스 필드가 자기 축의 가장 안쪽 필드면 제수는 그 축 위치의 원래 집계입니다. 중첩 레이아웃에서 베이스 필드가 바깥 필드면 제수는 남은 축의 매칭 멤버들에 걸친 합입니다. 두 위치는 Excel 출력과 독립적으로 각각 검증되었습니다. 한 중첩 수준에서는 통하고 다른 수준에서 조용히 실패하는 규칙이야말로 전형적인 피벗 버그이기 때문입니다

단순 퍼센트는 누적과 다르게 동작합니다. 행, 열, 총합 퍼센트는 평균 같은 비가산 집계를 포함해 관련 범위의 원래 집계로 나누므로, 평균에 대한 총합 퍼센트는 셀 평균을 전체 평균으로 나눈 것입니다. 합쳐진 풀의 몫이 아니라요

비교 모드의 공백, #NULL!, #DIV/0!

기준 대비 퍼센트 비교는 세 가지 빈 상황을 구분하고, 각각 다른 셀을 만듭니다. 분자 교차점에 소스 행이 없으면 셀은 #NULL!을 보여 줍니다. 베이스 항목이 그 교차점에 데이터가 전혀 없으면 셀은 공백입니다. 베이스 항목이 존재하고 그 집계가 0이면 셀은 #DIV/0!을 보여 줍니다. 머티리얼라이즈된 그리드를 읽는 코드는 모든 값 셀이 숫자라고 가정하는 대신 VarIsEmpty와 VarIsError로 검사해야 합니다

단순 퍼센트는 더 관대합니다: 없는 분자는 거기서 0으로 셉니다. 부모 행과 부모 열 퍼센트는 경우가 하나 더 있습니다. 해당 축에 필드가 없는 피벗은 나눌 부모가 없으므로 그 결과는 공백입니다. 총합은 각 모드 자신의 의미론도 따릅니다. 여기에는 비교나 누적 합계가 걸어 가는 축을 따라 공백 총합이 포함됩니다; 누적 합계 아래의 공백뿐인 총합 행은 옳은 것입니다. 머티리얼라이제이션 실패가 아니라요

임포트된 피벗에서 모드 전환하기

기존 워크북에서 온 피벗의 표시 모드를 바꾸는 것도 같은 방식으로 동작합니다. 플래그 하나만 더할 뿐입니다. AAdoptExisting에 True를 넘겨서 MaterializeNativePivotTable이 Excel이 그 피벗을 위해 이미 쓴 셀들을 넘볼 수 있게 하세요

Pivot := Book.Sheets.SheetByName('Sales').PivotTables[0];
with Pivot.DataFields[0] do
begin
  ShowDataAs := xlpsdaNormal;                    // 기본 모드 해제
  ExtendedShowDataAs := xlpesdaPercentOfParentRow; // BaseField 불필요
end;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
  raise Exception.Create('Could not re-materialize the pivot');

모드가 바뀔 때 HotXLS는 스테이징된 정의에서 낡은 showDataAs, pivotShowAs, baseItem 속성을 제거하므로, 저장된 파일이 새 확장 모드 옆에 낡은 기본 모드를 달고 다니는 일은 없습니다. 같은 스테이징 단계는 데이터 필드와 비활성 필드의 compact 및 outline 플래그를 리포트 레이아웃과 맞춰 둡니다. Excel이 새로고침된 데이터 캡션을 행·열 필드에서만이 아니라 그 플래그들에서 유도하므로 중요합니다. 잘못된 열거형 값도 거부됩니다: 255 바이트를 열거형으로 캐스팅하면 Delphi에서 -1로 부호 확장될 수 있으므로, HotXLS는 하한과 상한을 모두 검사하고 그리드를 건드리지 않고 -1을 반환합니다

같은 리포트에 계산 필드나 계산 항목까지 추가할 거라면 HotXLS에서 계산 필드로 피벗 테이블 만들고 새로고침하기를 먼저 읽고, Excel이 파일을 복구하지 못하게 막는 필드 수준 XML 규칙은 Delphi의 XLSX 피벗 필드 스키마 유효성을 참고하세요

FAQ

HotXLS 데이터 필드는 ShowDataAs와 ExtendedShowDataAs를 함께 쓸 수 있나요?

아니요. TXLSPivotDataField.ShowDataAs와 ExtendedShowDataAs는 상호 배타적입니다; 둘 다 설정하면 MaterializeNativePivotTable이 -1을 반환하고 이전 출력을 그대로 둡니다

Excel에서 새로고침 후 피벗 순위가 합계로 변한 이유는 무엇인가요?

확장 URI가 다른 케이싱으로 다시 쓰였을 가능성이 높습니다. Excel은 {E15A36E0-9728-4e99-A89B-3F7291B0FE68}를 대소문자 구분해서 매칭하고, 맞지 않으면 RefreshTable 후 평범한 집계로 폴백합니다

HotXLS 피벗 순위는 동률 뒤의 숫자를 건너뛰나요?

아니요. HotXLS는 Excel의 밀집 피벗 순위를 따르므로 30, 30, 20은 1, 1, 2로 순위 매겨집니다

Show Values As 지원, 네이티브 피벗 머티리얼라이제이션, 그리고 XLSX와 XLS 기능 세트의 나머지는 Delphi와 C++Builder용 HotXLS Delphi 스프레드시트 컴포넌트에 들어 있습니다