今回は、VBAでPivotTable(ピボットテーブル)を扱う際の基礎知識を解説します。なお、ピボットテーブル事態の基礎知識については以下を参照ください。
また、過去にテーブル(ListObject)の扱い方に関する記事も書いていますので、ご参照ください。
ピボットテーブルの作成と設定
テーブルを活用してピボットテーブルを作成する
まず前提として、今回はテーブルからピボットテーブルを作成する方法を紹介します。普段テーブルを使い慣れていない人も多いかと思いますが、VBAでピボットテーブルを扱うときにテーブルが設定されてない通常のデータ範囲を使うと、都度参照範囲が変わったりして、非常に面倒です。可能な限りテーブルを設定して、任意の名前を付た上でVBA化することをお勧めします。
今回は、このテーブルが”元データ”という名前のシート上で”テーブル1″という名前で存在しています。ここから、VBAのコードでピボットテーブルを作成します。
元データ指定 => ピボットキャッシュ作成 => ピボットテーブル作成
VBAを使って新しいピボットテーブルを作成する流れは、元データの指定、ピボットキャッシュの作成、そしてピボットテーブルの作成という順になりますが、ここまで以下の1行で実現できます。
ActiveWorkbook.PivotCaches.Create(xlDatabase, "テーブル1").CreatePivotTable Sheets.Add.Range("A3")なおピボットキャッシュとは、Excelがピボットテーブルを作成する際に使用するデータの一時保存場所です。ピボットテーブルが参照するデータのスナップショットを保持することにより、元データを何度も読み込む必要がなくなります。例えば、同じデータソースから複数のピボットテーブルを作成する場合でも、それぞれのピボットテーブルが独自にデータを読み込む必要がなくなり効率的です。
またピボットキャッシュを使用することで、データの集計や計算が前もって行われ、ピボットテーブルの表示や操作が高速になります。キャッシュ(一時保存)されたデータに基づいて操作を行うため、ピボットテーブルの更新や再計算が迅速に行えるのです。
次に、作成したピボットテーブルに対して、行・列・値などを設定します。ここは後述の通り、必要に応じてカスタマイズする部分です。
Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables(1)
With pvt.PivotFields("担当者")
.Orientation = xlPageField
.Position = 1
.CurrentPage = "田中"
End With
With pvt
.PivotFields("商品名").Orientation = xlRowField
.PivotFields("商品名").Position = 1
End With
With pvt
.PivotFields("エリア").Orientation = xlColumnField
.PivotFields("エリア").Position = 1
End With
With pvt
.AddDataField pvt.PivotFields("販売数量"), "合計販売数量", xlSum
.AddDataField pvt.PivotFields("販売価格(合価)"), "合計販売価格", xlSum
End WithこのVBAの実行結果は以下の通りです。
ピボットテーブルのカスタマイズ
カスタマイズ例
以下に、カスタマイズの一例を紹介します。
“担当者”列をフィルターに設置し、”田中”のみ表示
With pvt.PivotFields("担当者")
.Orientation = xlPageField
.Position = 1
.CurrentPage = "田中"
End With行ラベルに”商品名”と”エリア”を設定
With pvt
.PivotFields("商品名").Orientation = xlRowField
.PivotFields("商品名").Position = 1
.PivotFields("エリア").Orientation = xlRowField
.PivotFields("エリア").Position = 2
End With列ラベルに”受注年”を設定し、昇順で並べ替え
With pvt.PivotFields("受注年")
.Orientation = xlColumnField
.Position = 1
.AutoSort xlAscending, "受注年"
End With
“販売数量”と”販売価格(合価)”をデータフィールドに追加し、それぞれの合計を表示
With pvt
.AddDataField pvt.PivotFields("販売数量"), "合計販売数量", xlSum
.AddDataField pvt.PivotFields("販売価格(合価)"), "合計販売価格", xlSum
End With更新方法
一度作成したピボットテーブルを更新するのは非常に簡単で、以下1行で実現できます。
pvt.RefreshTable
コメント