Delphi'de Pivot Show Values As: Sıralama ve Kümülatif Toplam

HotXLS, Excel pivot table "Show Values As" modlarını, TXLSPivotDataField üzerindeki iki özellik üzerinden Delphi'den ayarlar: kümülatif toplam, fark ve satır yüzdesi gibi klasik modlar için ShowDataAs; sıralama, percent of parent ve kümülatif toplam yüzdesi için ExtendedShowDataAs. TXLSXWorkbook.MaterializeNativePivotTable ise hesaplanan ızgarayı Excel'in yapacağı şekilde yazar. İki özellik karşılıklı olarak dışlayıcıdır ve genişletilmiş olanlar, Excel'in büyük/küçük harfe duyarlı eşleştirdiği bir uzantı URI'sine bağlıdır

Son ayrıntı, bu yazının var olma nedenidir. Sıralama sütunlu bir pivot table, üretilecek küçük bir şey gibi görünür ve ilk açılışta genellikle öyledir. Sorun, bir kullanıcı Excel'de Refresh'e tıkladığında ve sıralama sütunu hiçbir uyarı olmadan düz bir toplama geri döndüğünde başlar. Show Values As'ı doğru yapmak, iki XML sözcük dağarcığını doğru yapmak demektir; artı olarak Excel'in arayüzde hiç belgelemediği bir tutam sayısal kural: eşitlikler nasıl sıralanır, bir ortalamanın kümülatif yüzdesi ne anlama gelir ve hangi boş hücre hataya karşı boş olur

Tek bir Excel menüsü için neden iki özellik var?

Excel tek bir "Show Values As" menüsü gösterir ama dosya onu iki farklı yerde saklar; bu yüzden HotXLS iki özellik sunar. Excel 2007'den gelen özgün modlar, pivotTableDefinition içindeki dataField elemanının showDataAs özniteliğinde yaşar ve ECMA-376 ST_ShowDataAs numaralandırmasını kullanır. Excel 2010'un eklediği modlar — sıralama ile parent ve kümülatif yüzdeler — o numaralandırmaya sığmadı; bu yüzden pivotShowAs özniteliğine sahip bir x14:dataField elemanına gittiler ve aynı data field üzerinde bir extLst uzantı bloğuna sarıldılar

TXLSPivotShowDataAs ilk grubu yansıtır: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal ve xlpsdaIndex. TXLSPivotExtendedShowDataAs ikinciyi kapsar: xlpesdaPercentOfParent, xlpesdaPercentOfParentRow, xlpesdaPercentOfParentColumn, xlpesdaPercentOfRunningTotal, xlpesdaRankAscending ve xlpesdaRankDescending; nötr değer olarak da xlpesdaNone vardır. Bir data field ya bir grubu ya öbürünü kullanır, asla ikisini birden kullanmaz. ShowDataAs'i normal dışı bir değerde bırakıp ayrıca bir genişletilmiş mod ayarlarsanız MaterializeNativePivotTable -1 döndürür ve mevcut çıktıyı, hücreler ve pivot sınırları dâhil, tam olarak olduğu gibi bırakır

BaseField ile BaseItem karşılaştırma hedefini taşır. BaseField, pivot table'ın field list'ine sıfır tabanlı bir indekstir; kaynak sütunların başlık adlarına değil ve karşılaştırmaları, kümülatif toplamları, seçilmiş bir parent yüzdesini ve sıralamaları o yönetir. BaseItem, o field içindeki sıfır tabanlı bir item konumudur ya da "komşuyla karşılaştırma" için xlPivotBaseItemPrevious ve xlPivotBaseItemNext sabitlerinden biridir. Parent satır ve parent sütun yüzdeleri BaseField'i tamamen yok sayar; eşleşen eksendeki en iç field'i seçerler

Delphi'de sıralamalı bir pivot kurmak

En kısa çalışan yol, pivotu kurmak, display modunu data field üzerinde ayarlamak ve kaydetmeden önce materialize etmektir. Aşağıdaki örnek, bölgesel tutarları kategori başına azalan sırayla sıralar; böylece her satırdaki en büyük tutar 1. sıradadır

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';
    // ... satır 2..7'yi doldurun ...
    Report := Book.Sheets.Add('Report');

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

    Measure.ShowDataAs := xlpsdaNormal;              // temel mod kapalı kalır
    Measure.ExtendedShowDataAs := xlpesdaRankDescending;
    Measure.BaseField := 1;                          // Region üzerinden sıralama

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

Burada iki alışkanlık kar getirir. Birincisi, genişletilmiş bir mod seçtiğinizde ShowDataAs := xlpsdaNormal değerini açıkça ayarlayın; taze bir field üzerinde bile, çünkü aynı kod sık sık, data field'ında hâlâ bir temel mod taşıyan içe aktarılmış bir pivota karşı yeniden kullanılır. İkincisi, dönüş değerini denetleyin. Reddedilen bir materialization atomiktir, bu iyidir; ama aynı zamanda gözden kaçan bir denetimin, içinde önceki ızgara olan bir dosyayı gönderdiği anlamına da gelir

BaseField, MaterializeNativePivotTable hiç çağırmasanız bile kaydetmeyi atlatır. HotXLS, genişletilmiş mod ayarlanır ayarlanmaz percent of parent, kümülatif toplam yüzdesi ve her iki sıralama modu için baseField özniteliğini yazar; böylece yapılandırmadan hemen sonra kaydedilen bir workbook, yenilediğinde Excel'e hangi field üzerinden sıralama yapacağını yine söyler

Uzantı URI'sinde ne ters gider?

pivotShowAs taşıyan uzantı bloğu, {E15A36E0-9728-4e99-A89B-3F7291B0FE68} URI'si ile tanımlanır ve Excel o metni yenileme sırasında büyük/küçük harfe duyarlı karşılaştırır. 4e99 içindeki küçük harfli eye dikkat. GUID'ler normalde büyük/küçük harfe duyarsızdır ve birçok XML yazıcısı onları büyük harfe çevirir; tam da bu başarısız biçimidir: 4E99lu bir dosya Excel'de, önbelleklenmiş her hücre doğru olacak şekilde açılır; çünkü Excel yalnızca saklanan değerleri boyar. Ama RefreshTable sonrası Excel uzantıyı artık tanımaz, genişletilmiş modu düşürür ve sütunu sıradan bir toplam olarak yeniden hesaplar. Onarım istemi yok, hata yok; yalnızca sessizce toplama dönüşmüş bir sıralama sütunu

HotXLS URI'yi Excel'in beklediği haliyle yazar; bu yüzden bu sorun, yalnızca paketi akışta bir şeyin yeniden yazdığında ısırır; örneğin GUID harf durumunu normalize eden ya da extLst elemanlarını yeniden kuran bir post-processing adımı. Onu yakalayan test ucuzdur: üretilen dosyayı Excel'de açın, pivotu yenileyin ve genişletilmiş sütunun ham agregalar yerine hâlâ sıralamaları ya da yüzdeleri gösterdiğini teyit edin. Yalnızca önbelleklenmiş hücreleri okuyan bir test, bozuk bir dosyada her seferinde geçer

Excel eşitlikteki değerleri nasıl sıralar, kümülatif toplamları hangi sırayla alır?

Excel'in pivot sıralaması bir dense rank'tır ve HotXLS aynısını üretir. Azalan 30, 30 ve 20 tutarlarında sonuç 1, 1 ve 2'dir; bir çalışma sayfasındaki RANK.EQden alacağınız yarışma tarzı 1, 1 ve 3 değil. Bir eşitlik, tek farklı önceki değer olarak sayılır. Bir rapor yarışma sıralaması istiyorsa bunu kaynak veride ya da pivotun yanındaki bir çalışma sayfası formülünde hesaplayın; çünkü hiçbir pivot display modu onu üretmez

Kümülatif toplamlar, kümülatif toplam yüzdeleri ve önceki ya da sonraki karşılaştırmaları, üyelerin gerçekte göründüğü sırayı izler; field'ın item listesi sırasını değil. O ayrım, bir çağıran önbellek kurulduktan sonra sıralamayı değiştirdiğinde önem kazanır

// Satırlar azalan sıralı: kümülatif toplam görüntü sırasında birikir
Pivot.Fields[0].SortType := xlpfstDescending;

Measure.ExtendedShowDataAs := xlpesdaNone;
Measure.ShowDataAs := xlpsdaPercentDiff;   // % fark, şuradan...
Measure.BaseField := 0;                    // ...Category field'ından
Measure.BaseItem := xlPivotBaseItemPrevious; // ...önceki görünür üyeden

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

SortType değeri xlpfstDescending olduğunda "previous", yukarıda görüntülenen üye demektir ve kümülatif toplam sıralı listenin tepesinden başlar. Bunun yerine saklanan item listesini gezen bir uygulama, makul görünen ama herkes yenilediği anda Excel'le ters düşen sayılar üretir. Kenar üyeler de özen ister: ilk üyede önceki bir komşu yoktur ve kenar üyeyi kendisiyle yalnızca percent-of modu karşılaştırır; fark modları o hücreyi sıfır uydurmak yerine Excel'in kendi kenar davranışına bırakır

Bir kümülatif yüzde neden yüzde 100'ü aşabilir?

Ortalamalar üzerinden bir kümülatif toplam yüzdesi meşru biçimde yüzde 100'ü geçebilir; çünkü Excel görüntülenen üye agregalarını biriktirir ve bunların toplamı olmayan bir agregaya böler. Toplamlar için ikisi aynı şeydir; bu yüzden son üye tam yüzde 100'e oturur. Ortalamalar için üç kategori ortalamasını toplayıp genel ortalamaya bölmek hiçbir şeyin kesri değildir ve son satır yüzde 100'ün epey üstüne oturabilir. HotXLS burada değeri kırpmak yerine bilerek Excel'le eşleşir; çünkü kırpılmış bir sayı, yenilenmiş dosyayla ters düşerdi

Bölen, base field'ın nerede durduğuna da bağlıdır. Base field kendi eksenindeki en iç field olduğunda bölen, o eksen konumu için özgün agregadır. Base field, iç içe bir yerleşimde bir dış field olduğunda bölen, kalan eksende eşleşen üyeler üzerinden toplatır. Her iki konum da Excel çıktısına karşı bağımsız denetlendi; çünkü bir yuvalama seviyesinde çalışan ve öbüründe sessizce başarısız olan kural, klasik pivot hatasıdır

Basit yüzdeler kümülatif olandan farklı davranır. Satır, sütun ve grand total yüzdeleri, ilgili kapsamdaki özgün agregaya böler; ortalamalar gibi toplanamaz agregasyonlar için de. Bu yüzden ortalamalar üzerinde bir percent of total, hücre ortalamasının genel ortalamaya bölümüdür; toplanmış bir havuzun payı değil

Karşılaştırma modlarında boşluklar, #NULL! ve #DIV/0!

Percent-of-base karşılaştırmaları üç boş durumu ayırt eder ve her biri farklı bir hücre üretir. Pay kesişiminin kaynak satırı olmadığında hücre #NULL! gösterir. Base item'in o kesişimde verisi olmadığında hücre boştur. Base item var olduğunda ve agregası sıfır olduğunda hücre #DIV/0! gösterir. Materialize edilmiş ızgarayı okuyan kod, her değer hücresinin sayı tuttuğunu varsaymak yerine VarIsEmpty ve VarIsError ile test etmelidir

Basit yüzdeler daha affedicidir: olmayan bir pay orada sıfır sayılır. Parent satır ve parent sütun yüzdeleri bir durum daha ekler; karşılık gelen eksende field'ı olmayan bir pivotun bölecek bir parent'ı yoktur ve o sonuçlar boştur. Grand total'ler de her modun kendi semantiğini izler; bu, bir karşılaştırmanın ya da kümülatif toplamın gezdiği eksen boyunca boş toplamları kapsar. Bir kümülatif toplam altında boşluklarla dolu bir toplam satır doğrudur, materialization başarısızlığı değil

İçe aktarılmış bir pivotta mod değiştirmek

Mevcut bir workbook'tan gelen bir pivotun display modunu değiştirmek aynı şekilde çalışır; tek bir ek bayrakla. AAdoptExisting olarak True geçirin ki MaterializeNativePivotTable, Excel'in o pivot için çoktan yazdığı hücreleri devralabilsin

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

Mod değiştiğinde HotXLS, bayat kalmış showDataAs, pivotShowAs ve baseItem özniteliklerini hazırlanan tanımdan kaldırır; böylece kaydedilen dosya yeni bir genişletilmiş modun yanında eski bir temel mod taşımaz. Aynı hazırlık adımı, data ve inactive field'lar üzerindeki compact ile outline bayraklarını rapor yerleşimiyle uyumlu tutar; bu önemlidir, çünkü Excel yenilenen data başlıklarını o bayraklardan türetir, yalnızca satır ve sütun field'larından değil. Geçersiz enum değerleri de reddedilir: 255 değerinde bir byte'ı herhangi bir enum'a dönüştürmek Delphi'de -1'e işaret uzatabilir; HotXLS hem alt hem üst sınırı denetler ve ızgaraya dokunmadan -1 döndürür

Aynı rapora ayrıca calculated field'lar ya da calculated item'lar ekleyecekseniz önce HotXLS'te calculated field'larla pivot table kurma ve yenileme yazısını okuyun; Excel'in dosyayı onarmasını engelleyen field seviyesindeki XML kuralları için Delphi'de XLSX pivot field schema geçerliliği yazısına bakın

FAQ

Bir HotXLS data field, ShowDataAs ile ExtendedShowDataAs'ı birlikte kullanabilir mi?

Hayır. TXLSPivotDataField.ShowDataAs ile ExtendedShowDataAs karşılıklı olarak dışlayıcıdır; ikisi de ayarlıyken MaterializeNativePivotTable -1 döndürür ve önceki çıktıyı olduğu gibi bırakır

Pivot sıralamam Excel'de yenileme sonrası neden toplama dönüştü?

Uzantı URI'si muhtemelen farklı harf durumuyla yeniden yazıldı. Excel, {E15A36E0-9728-4e99-A89B-3F7291B0FE68} değerini büyük/küçük harfe duyarlı eşleştirir ve eşleşmediğinde RefreshTable sonrası düz agregasyona döner

HotXLS pivot sıralaması bir eşitlikten sonraki sayıları atlar mı?

Hayır. HotXLS, Excel'in dense pivot sıralamasını izler; 30, 30 ve 20 değerleri 1, 1 ve 2 olarak sıralanır

Show Values As desteği, yerel pivot materialization ve XLSX ile XLS özellik setinin geri kalanı, Delphi ve C++Builder için HotXLS Delphi spreadsheet component içinde gelir