LlosLabソフトウェア開発ブログPDF、Delphi、およびドキュメントソフトウェア開発者向けのエンジニアリングノート。

技術記事

Delphi のピボット Show Values As: ランクと累計

HotXLS Delphi Component

HotXLS は、Excel ピボットテーブルの「Show Values As」モードを、TXLSPivotDataField の 2 つのプロパティで Delphi から設定します。累計、差分、行の割合といったクラシックモードの ShowDataAs と、ランク、親の割合、累計割合のための ExtendedShowDataAs です。TXLSXWorkbook.MaterializeNativePivotTable が、Excel がやるのと同じように計算済みグリッドを書き出します。2 つのプロパティは相互排他であり、拡張のほうは Excel が大文字小文字を区別して照合する拡張 URI に依存します

最後の細部こそ、この記事が存在する理由です。ランク列付きのピボットテーブルは生成するには小さな題材に見え、最初の 1 回のオープンではたいていその通りです。問題が始まるのは、ユーザーが Excel で Refresh をクリックし、警告もなくランク列が素の合計に戻るときです。Show Values As を正しくやるとは、2 つの XML 語彙を正しくすることに加えて、Excel が UI に決して文書化しない数値ルールの一揃いを正しくすることです。同点がどうランクされるか、平均の累計割合が何を意味するか、どの空セルが空白になり、どれがエラーになるか

1 つの Excel メニューにプロパティが 2 つあるのはなぜか

Excel が見せる「Show Values As」メニューは 1 つですが、ファイルはそれを 2 つの場所に格納します。だから HotXLS はプロパティを 2 つ公開します。Excel 2007 からのオリジナルモードは pivotTableDefinition 内の dataField 要素の showDataAs 属性に住み、ECMA-376 の ST_ShowDataAs 列挙を使います。Excel 2010 が追加したモード、ランクと親・累計の割合は、その列挙に収まらなかったため、pivotShowAs 属性を持つ x14:dataField 要素へ行き、同じデータフィールドの extLst 拡張ブロックに包まれています

TXLSPivotShowDataAs が最初のグループを写します。xlpsdaNormal、xlpsdaDifference、xlpsdaPercent、xlpsdaPercentDiff、xlpsdaRunTotal、xlpsdaPercentOfRow、xlpsdaPercentOfCol、xlpsdaPercentOfTotal、xlpsdaIndex です。TXLSPivotExtendedShowDataAs が 2 つ目をカバーします。xlpesdaPercentOfParent、xlpesdaPercentOfParentRow、xlpesdaPercentOfParentColumn、xlpesdaPercentOfRunningTotal、xlpesdaRankAscending、xlpesdaRankDescending であり、中立値として xlpesdaNone を持ちます。データフィールドはどちらか一方のグループを使い、両方には決してしません。ShowDataAs を 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';
    // ... rows 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;

ここで効く習慣が 2 つあります。第 1 に、拡張モードを選ぶとき、新鮮なフィールドでも ShowDataAs := xlpsdaNormal を明示的に設定すること。同じコードは、データフィールドが既に基本モードを運んでいるインポート済みピボットに対して再利用されがちです。第 2 に、戻り値を確認すること。拒否されたマテリアライズはアトミックで、それは良いことですが、見逃したチェックは前のグリッド入ったままのファイルを出荷することにもなります

MaterializeNativePivotTable を呼ばなくても、BaseField は保存を生き延びます。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 のピボットランクは密ランクであり、HotXLS はこれを再現します。降順の金額 30、30、20 に対する結果は 1、1、2 であって、ワークシートの RANK.EQ が返す競技スタイルの 1、1、3 ではありません。同点は 1 つの異なる先行値として数えます。レポートに競技ランキングが必要なら、ソースデータか、ピボットの横のワークシート数式で計算してください。これを生むピボット表示モードは存在しないので

累計、累計割合、前後との比較は、フィールドのアイテムリストの順序ではなく、メンバーが実際に現れる順序に従います。キャッシュ構築後に呼び出し側がソートを変えた途端、この区別が効いてきます

// 行は降順ソート済み: 累計は表示順に蓄積される
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 に変わった後は、「previous」は上に表示されたメンバーを意味し、累計はソート済みリストの先頭から始まります。代わりに格納されたアイテムリストをなぞる実装は、もっともらしく見えて誰かが更新した瞬間に Excel と食い違う数字を生みます。端のメンバーにも注意が要ります。最初のメンバーには前の隣人がなく、端のメンバーを自分自身と比較するのは percent-of モードだけです。差分モードはゼロをでっち上げる代わりに、そのセルを Excel 自身の端の挙動に任せます

累計割合が 100% を超えられるのはなぜか

平均に対する累計割合は、正当に 100% を超えられます。Excel は表示されるメンバーの集計を蓄積し、それらの和ではない集計で割るからです。合計なら両者は同じものなので、最後のメンバーはちょうど 100% に着地します。平均では、3 つのカテゴリ平均を足して全体の平均で割っても、何かの分数にはなりません。最終行は 100% を大きく超え得ます。HotXLS はここで値をクランプする代わりに、意図的に Excel に合わせます。クランプされた数値は更新後のファイルと食い違うからです

除数はベースフィールドがどこに座るかにも依存します。ベースフィールドがその軸の最も内側のフィールドのとき、除数はその軸位置のオリジナルの集計です。ネストしたレイアウトでベースフィールドが外側のフィールドのとき、除数は残りの軸の一致するメンバーを横断した和です。両方の位置は Excel 出力に対して独立に確認されました。1 つのネストレベルでは動いて他では静かに壊れる規則こそ、古典的なピボットバグだからです

単純な割合は累計のものとは違う振る舞いをします。行、列、総計の割合は、平均のような非加算的な集計を含めて、関連するスコープのオリジナルの集計で割ります。つまり平均に対する総計割合は、セルの平均を全体の平均で割ったものであって、合算プールの取り分ではありません

比較モードにおける空白、#NULL!、#DIV/0!

ベースに対する割合比較は 3 つの空の状況を区別し、それぞれが違うセルを生みます。分子の交差にソース行がないとき、セルは #NULL! を示します。ベースアイテムにその交差でデータがないとき、セルは空白です。ベースアイテムが存在し、その集計がゼロのとき、セルは #DIV/0! を示します。マテリアライズされたグリッドを読むコードは、すべての値セルが数値を保持すると仮定する代わりに、VarIsEmpty と VarIsError で判定すべきです

単純な割合はより寛容です。欠けた分子はゼロとして数えられます。親行と親列の割合はもう 1 つケースを加えます。対応する軸にフィールドのないピボットには割るべき親がないため、それらの結果は空白です。総計も各モードの固有のセマンティクスに従います。比較や累計がたどる軸に沿った空白の総計も含まれます。累計の下の総計行が空白だらけなのは正しく、マテリアライズの失敗ではありません

インポート済みピボットでモードを切り替える

既存のワークブックから来たピボットの表示モードの変更も同じように動き、フラグが 1 つ増えます。MaterializeNativePivotTable が Excel が既に書いたそのピボットのセルを引き継げるよう、AAdoptExisting に True を渡します

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 スプレッドシートコンポーネントに搭載されています