Pivot Show Values As w Delphi: ranking i suma narastająca

HotXLS ustawia tryby "Show Values As" tabeli przestawnej Excel z Delphi przez dwie właściwości na TXLSPivotDataField: ShowDataAs dla klasycznych trybów, jak suma narastająca, różnica i procent wiersza, oraz ExtendedShowDataAs dla rankingu, procentu rodzica i procentu sumy narastającej. TXLSXWorkbook.MaterializeNativePivotTable zapisuje potem policzoną siatkę tak, jak zrobiłby to Excel. Te dwie właściwości wykluczają się wzajemnie, a rozszerzone zależą od URI rozszerzenia, które Excel porównuje z rozróżnianiem wielkości liter

Ta ostatnia cecha to powód istnienia tego wpisu. Tabela przestawna z kolumną rankingu wygląda na drobiazg do wygenerowania i przy pierwszym otwarciu zwykle nim jest. Kłopoty zaczynają się, gdy użytkownik kliknie Odśwież w Excelu, a kolumna rankingu bez ostrzeżenia wraca do zwykłej sumy. Dobre Show Values As znaczy dobre dwa słownictwa XML plus garść reguł liczbowych, których Excel nigdy nie dokumentuje w UI: jak rankują remisy, co znaczy skumulowany procent średniej i która pusta komórka staje się pustką, a która błędem

Dlaczego jedno menu Excela ma dwie właściwości?

Excel pokazuje jedno menu "Show Values As", ale plik chowa je w dwóch różnych miejscach, więc HotXLS wystawia dwie właściwości. Oryginalne tryby z Excela 2007 mieszkają w atrybucie showDataAs elementu dataField wewnątrz pivotTableDefinition, w wyliczeniu ECMA-376 ST_ShowDataAs. Tryby dodane w Excelu 2010, ranking oraz procenty rodzica i skumulowany, nie mieściły się w tym wyliczeniu, więc trafiły do elementu x14:dataField z atrybutem pivotShowAs, opakowanego w blok rozszerzenia extLst na tym samym polu danych

TXLSPivotShowDataAs odwzorowuje pierwszą grupę: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal i xlpsdaIndex. TXLSPivotExtendedShowDataAs pokrywa drugą: xlpesdaPercentOfParent, xlpesdaPercentOfParentRow, xlpesdaPercentOfParentColumn, xlpesdaPercentOfRunningTotal, xlpesdaRankAscending i xlpesdaRankDescending, z xlpesdaNone jako wartością neutralną. Pole danych używa jednej grupy albo drugiej, nigdy obu. Jeśli zostawisz ShowDataAs na wartości innej niż normalna i dodatkowo ustawisz tryb rozszerzony, MaterializeNativePivotTable zwraca -1 i zostawia istniejące wyjście dokładnie takie, jakie było, komórki i granice przestawienia włącznie

BaseField i BaseItem niosą cel porównania. BaseField to indeks od zera w listę pól tabeli przestawnej, nie w nagłówki kolumn źródłowych, i steruje porównaniami, sumami narastającymi, procentem wybranego rodzica i rankingami. BaseItem to pozycja elementu od zera wewnątrz tego pola albo jedna ze stałych xlPivotBaseItemPrevious i xlPivotBaseItemNext dla "porównaj z sąsiadem". Procenty wiersza i kolumny rodzica ignorują BaseField zupełnie; wybierają najbardziej wewnętrzne pole na pasującej osi

Budowanie rankowanej tabeli przestawnej w Delphi

Najkrótsza działająca droga to zbudować przestawienie, ustawić tryb wyświetlania na polu danych i zmaterializować przed zapisem. Poniższy przykład rankuje kwoty regionalne per kategoria malejąco, więc największa kwota w każdym wierszu dostaje ranking 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';
    // ... wypełnij wiersze 2..7 ...
    Report := Book.Sheets.Add('Report');

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

    Measure.ShowDataAs := xlpsdaNormal;              // tryb podstawowy zostaje wyłączony
    Measure.ExtendedShowDataAs := xlpesdaRankDescending;
    Measure.BaseField := 1;                          // ranking wzdłuż 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;

Dwa nawyki się tu opłacają. Po pierwsze, ustawiaj ShowDataAs := xlpsdaNormal jawnie, gdy wybierasz tryb rozszerzony, nawet na świeżym polu, bo ten sam kod bywa reuse'owany przeciw importowanej tabeli przestawnej, której pole danych już niesie tryb podstawowy. Po drugie, sprawdzaj wartość zwracaną. Odrzucona materializacja jest atomowa, co jest dobre, ale znaczy też, że pominięte sprawdzenie wysyła w świat plik z poprzednią siatką w środku

BaseField przeżywa zapis nawet, gdy nigdy nie wołasz MaterializeNativePivotTable. HotXLS zapisuje atrybut baseField dla procentu rodzica, procentu sumy narastającej i obu trybów rankingu od razu, gdy ustawiony jest tryb rozszerzony, więc skoroszyt zapisany prosto po konfiguracji wciąż mówi Excelowi, po którym polu rankować przy odświeżeniu

Co się psuje z URI rozszerzenia?

Blok rozszerzenia niosący pivotShowAs jest identyfikowany URI {E15A36E0-9728-4e99-A89B-3F7291B0FE68}, a Excel porównuje ten string z rozróżnianiem wielkości liter przy odświeżaniu. Zauważ małe e w 4e99. GUID-y są normalnie nierozróżniające wielkości liter i wielu writerów XML zapisuje je wielkimi, co jest dokładnie tym trybem awarii: plik z 4E99 otwiera się w Excelu z każdą zcache'owaną komórką poprawną, bo Excel po prostu maluje przechowane wartości. Po RefreshTable Excel już jednak nie rozpoznaje rozszerzenia, wyrzuca tryb rozszerzony i przelicza kolumnę jako zwykłą sumę. Bez okna naprawy, bez błędu, po prostu kolumna rankingu po cichu stała się sumami

HotXLS zapisuje literalne URI tak, jak Excel tego oczekuje, więc ugryzie cię to tylko wtedy, gdy coś downstream przepisuje pakiet, na przykład krok postprocessingu normalizujący wielkość liter GUID albo przebudowujący elementy extLst. Test, który to łapie, jest tani: otwórz wygenerowany plik w Excelu, odśwież przestawienie i potwierdź, że rozszerzona kolumna wciąż pokazuje rankingi albo procenty, a nie surowe agregaty. Test czytający tylko zcache'owane komórki przejdzie na zepsutym pliku za każdym razem

Jak Excel rankuje remisy i porządkuje sumy narastające?

Ranking przestawny Excela to dense rank i HotXLS go odwzorowuje. Przy malejących kwotach 30, 30 i 20 wynik to 1, 1 i 2, nie stylu konkursowego 1, 1 i 3, jaki daje RANK.EQ na arkuszu. Remis liczy się jako jedną odrębną wartość poprzedzającą. Jeśli raport potrzebuje rankingu konkursowego, policz go w danych źródłowych albo w formule arkuszowej obok przestawienia, bo żaden tryb wyświetlania go nie produkuje

Sumy narastające, ich procenty oraz porównania z poprzednim albo następnym idą po kolejności, w jakiej członowie faktycznie występują, nie po kolejności listy elementów pola. Ta różnica ma znaczenie, gdy tylko wołający zmieni sortowanie po zbudowaniu cache

// Wiersze posortowane malejąco: suma narastająca akumuluje się w kolejności wyświetlania
Pivot.Fields[0].SortType := xlpfstDescending;

Measure.ExtendedShowDataAs := xlpesdaNone;
Measure.ShowDataAs := xlpsdaPercentDiff;   // % różnicy od...
Measure.BaseField := 0;                    // ...pola Category
Measure.BaseItem := xlPivotBaseItemPrevious; // ...poprzedniego widocznego członka

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

Po zmianie SortType na xlpfstDescending "poprzedni" znaczy człon wyświetlony wyżej, a suma narastająca startuje od góry posortowanej listy. Implementacja, która idzie po przechowanej liście elementów, produkuje liczby wyglądające wiarygodnie i rozjeżdżające się z Excelem w chwili, gdy ktokolwiek odświeży. Skrajni członowie też potrzebują uwagi: przy pierwszym członie nie ma poprzedniego sąsiada, a tylko tryb procentu-od porównuje człon skrajny z nim samym; tryby różnicowe zostawiają tę komórkę własnemu zachowaniu skrajnemu Excela, zamiast wymyślać zero

Dlaczego procent skumulowany może przekroczyć 100 procent?

Procent sumy narastającej po średnich może legalnie przejść ponad 100 procent, bo Excel akumuluje wyświetlane agregaty członów i dzieli przez agregat, który nie jest ich sumą. Dla sum jedno i drugie to to samo, więc ostatni człon ląduje dokładnie na 100 procentach. Dla średnich zsumowanie trzech średnich kategorii i podzielenie przez średnią ogólną nie jest ułamkiem niczego, a ostatni wiersz może wylądować wyraźnie ponad 100 procentami. HotXLS celowo dorównuje tu Excelowi, zamiast obcinać wartość, bo obcięta liczba rozmijałaby się z odświeżonym plikiem

Dzielnik zależy też od tego, gdzie siedzi pole bazowe. Gdy pole bazowe jest najbardziej wewnętrznym polem na swojej osi, dzielnikiem jest oryginalny agregat dla tej pozycji osi. Gdy pole bazowe jest polem zewnętrznym w układzie zagnieżdżonym, dzielnikiem jest suma po pasujących członach pozostałej osi. Obie pozycje były sprawdzone niezależnie wobec wyjścia Excela, bo reguła, która działa na jednym poziomie zagnieżdżenia i po cichu pada na drugim, to klasyka błędów przestawnych

Zwykłe procenty zachowują się inaczej niż skumulowany. Procent wiersza, kolumny i sumy głównej dzieli przez oryginalny agregat w odpowiednim zakresie, także dla agregacji nienaddytywnych, jak średnie, więc procent od sumy na średnich to średnia komórki podzielona przez średnią ogólną, nie udział w zsumowanej puli

Pustki, #NULL! i #DIV/0! w trybach porównań

Porównania procentu-od-bazy rozróżniają trzy puste sytuacje i każda produkuje inną komórkę. Gdy przecięcie licznika nie ma wierszy źródłowych, komórka pokazuje #NULL!. Gdy element bazowy nie ma danych na tym przecięciu, komórka jest pusta. Gdy element bazowy istnieje, a jego agregat jest zerem, komórka pokazuje #DIV/0!. Kod czytający zmaterializowaną siatkę powinien testować przez VarIsEmpty i VarIsError, zamiast zakładać, że każda komórka wartości trzyma liczbę

Zwykłe procenty są pobłaśliwsze: brakujący licznik liczy się tam jako zero. Procenty wiersza i kolumny rodzica dodają jeden przypadek, bo przestawienie bez pola na pasującej osi nie ma rodzica, przez którego można dzielić, więc te wyniki są puste. Sumy główne też trzymają semantykę każdego trybu, co obejmuje puste sumy wzdłuż osi, po której idzie porównanie albo suma narastająca; wiersz sumy pełen pustek pod sumą narastającą jest poprawny, a nie porażką materializacji

Zmiana trybów w importowanej tabeli przestawnej

Zmiana trybu wyświetlania przestawienia, które przyszło z istniejącego skoroszytu, działa tak samo, z jedną dodatkową flagą. Przekaż True jako AAdoptExisting, żeby MaterializeNativePivotTable mogło przejąć komórki, które Excel już zapisał dla tego przestawienia

Pivot := Book.Sheets.SheetByName('Sales').PivotTables[0];
with Pivot.DataFields[0] do
begin
  ShowDataAs := xlpsdaNormal;                    // wyczyść tryb podstawowy
  ExtendedShowDataAs := xlpesdaPercentOfParentRow; // BaseField niewymagany
end;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
  raise Exception.Create('Could not re-materialize the pivot');

Gdy tryb się zmienia, HotXLS usuwa przestarzałe atrybuty showDataAs, pivotShowAs i baseItem ze zstage'owanej definicji, więc zapisany plik nigdy nie niesie starego trybu podstawowego obok nowego rozszerzonego. Ten sam krok stagingu trzyma flagi compact i outline na polach danych i nieaktywnych w kroku z layoutem raportu, co ma znaczenie, bo Excel wyprowadza odświeżone podpisy danych z tych flag, a nie tylko z pól wiersza i kolumny. Niepoprawne wartości enum są też odrzucane: rzutowanie bajtu 255 na któreś wyliczenie może się rozszerzyć ze znakiem do -1 w Delphi, więc HotXLS sprawdza i dolną, i górną granicę i zwraca -1 bez dotykania siatki

Jeśli dodajesz do tego samego raportu też pola wyliczane albo elementy wyliczane, przeczytaj najpierw budowanie i odświeżanie tabel przestawnych z polami wyliczanymi w HotXLS, a dla reguł XML na poziomie pola, które chronią przed naprawą pliku przez Excela, zobacz poprawność schematu pola przestawnego XLSX w Delphi

FAQ

Czy pole danych HotXLS może używać ShowDataAs i ExtendedShowDataAs naraz?

Nie. TXLSPivotDataField.ShowDataAs i ExtendedShowDataAs wykluczają się wzajemnie; przy obu ustawionych MaterializeNativePivotTable zwraca -1 i zostawia poprzednie wyjście nietknięte

Dlaczego mój ranking przestawny po odświeżeniu w Excelu stał się sumą?

URI rozszerzenia został zapewne przepisany z inną wielkością liter. Excel dopasowuje {E15A36E0-9728-4e99-A89B-3F7291B0FE68} z rozróżnianiem wielkości liter i cofa się do zwykłej agregacji po RefreshTable, gdy nie ma dopasowania

Czy ranking przestawny HotXLS pomija numery po remisie?

Nie. HotXLS trzyma się gęstego rankingu przestawnego Excela, więc 30, 30 i 20 rankuje jako 1, 1 i 2

Wsparcie Show Values As, natywna materializacja przestawień i reszta zestawu funkcji XLSX i XLS są w HotXLS Delphi spreadsheet component dla Delphi i C++Builder