Excel。マクロ043。ピボットテーブルをマクロでつくってみる
＜Excel VBA：ピボットテーブル＞
集計するのに便利な「ピボットテーブル」をマクロで作ることはできますか？と聞かれることがありますので、次のようなピボットテーブルをExcel VBAで作ってみましょう。
【マクロの記録だとわかりにくい】
「マクロの記録」を使った場合を確認してみましょう。
開発タブのマクロの記録をつかい、マクロを作成したものを確認してみましょう。
Sub ピボットのマクロ()
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"R1C1:R451C6", Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:="Sheet3!R3C1", TableName:="ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ2" _
, DefaultVersion:=xlPivotTableVersion15
Sheets("Sheet3").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ2").PivotFields("商品名")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ2").PivotFields("曜日")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ2").AddDataField ActiveSheet.PivotTables( _
"ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ2").PivotFields("金額"), "合計 / 金額", xlSum
End Sub
このように、マクロの記録で、ピボットテーブルを作るマクロを作成はできるのですが、範囲も固定されているし、シート名が固定されていたり、長い構文で、わかりにくいのも欠点ですね。
そこで、Excel VBAでつくってみると、とてもわかりやすくなります。
【Excel VBAでピボットテーブル】
まず、次のようなExcel VBA構文をつくってみます。
Sub ピボットのvba()
Dim hani As Range
Dim pvc As PivotCache
Set hani = ActiveCell.CurrentRegion
Sheets.Add
Set pvc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=hani, Version:=xlPivotTableVersion15)
pvc.CreatePivotTable Range("a3")
With ActiveSheet.PivotTables(1)
.PivotFields("商品名").Orientation = xlRowField
.PivotFields("曜日").Orientation = xlColumnField
.PivotFields("金額").Orientation = xlDataField
End With
Range("a3").Activate
End Sub
では、実行してみましょう。
このように、ピボットテーブルを使って集計することができました。
それでは、確認してみましょう。
最初は、変数宣言です。
Dim hani As Range
この変数は、データの範囲で使います。
Dim pvc As PivotCache
このpvcという変数に、ピボットキャッシュを格納するので、オブジェクト型のPivotCacheで宣言します。
Set hani = ActiveCell.CurrentRegion
データの範囲を変数に格納します。
Sheets.Add
新しいシートにピボットテーブルを作成しますので、シートを追加します。
ここまでが、準備ですね。ピボットテーブルを作る時のダイアログボックスとほぼ同じ内容です。
【ピボットテーブルキャッシュを作成】
Set pvc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=hani, Version:=xlPivotTableVersion15)
Createメソッドで、PivotCacheオブジェクトを取得して、pvcに代入させます。
SourceType:=xlDatabase は、どのデータからピボットテーブルを作るのかを教える必要があります。xlDatabaseは、「Exccelの表」のことです。
SourceData:=hani は、xlDatabaseのデータ元となるセル範囲を指定します。
今回は、
Set hani = ActiveCell.CurrentRegion
で、範囲選択させています。
Version:=xlPivotTableVersion15 は、ピボットテーブルのバージョンを設定します。
バージョン？と思うかもしれませんが、Excelのバージョンでピボットテーブルは変わっています。
指定しないと、Excel2003のピボットテーブルをつくるデザインになります。
Excel2003は、xlPivotTableVersion11で値は2。
Excel2010は、xlPivotTableVersion14で値は4。
Excel2013は、xlPivotTableVersion15で値は5。
と設定されています。
今回は、Excel2013で作成していますので、xlPivotTableVersion15を使用しております。なお、Excel2016は、値6で設定します。
【ピボットテーブルの作成】
pvc.CreatePivotTable Range("a3")
ピボットテーブルキャッシュを格納したオブジェクト（pvc）から、CreatePivotTableメソッドを使用して、ピボットテーブルを作成します。
Range(“a3”)は、A3を起点に作ります。省略していますが、
TableDestination:=Range("a3")と記述します。
【ピボットテーブルにフィールドを追加】
With ActiveSheet.PivotTables(1)
.PivotFields("商品名").Orientation = xlRowField
.PivotFields("曜日").Orientation = xlColumnField
.PivotFields("金額").Orientation = xlDataField
End With
ActiveSheet.PivotTables(1)を行フィールド・列フィールド・値フィールドごとに記載するのは面倒なので、With～End Withで省略させます。
PivotFields("商品名").Orientation = xlRowField は、参照したいフィールド("商品名")をPivotFieldsメソッドで指定して、Orientationプロパティで、行（xlRowField）などを設定していきます。
xlRowFieldは、ROWなので行ですね。
xlColumnFieldは、Columnなので列ですね。
xlDataFieldは、Dataなので値ですね。
これで、ピボットテーブルが完成しました。
ピボットテーブルを作る時の流れですが、
最初は、ピボットテーブルキャッシュを作成
次に、ピボットテーブルの作成
最後に、ピボットテーブルにフィールドを追加
と大きく、3つの行程で作っていきます。
