ピボットグラフを作成するVBA

得られる効果

下のような家計簿のデータベースから、

下のようなピボットテーブルとグラフを作成します。

今回は特定の月のグラフを作成します。

作成手順

１　用意した表から、ピボットテーブルを作成。
２　ピボットテーブルに項目を代入。
３　ピボットグラフを作成（横棒グラフ）

モジュールの中身

Sub pibot_make()

Dim DataS As Worksheet 'データシート
Dim PCache As PivotCache 'ピボットキャッシュ格納用変数
 
Set DataS = ActiveWorkbook.Worksheets("食材単価DB")
 
'ピボットテーブルキャッシュに、「データ」シートのセル範囲をセット
Set PCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=DataS.Range("A1").CurrentRegion)


'『ピボットテーブル』シートを追加
Worksheets.Add
ActiveSheet.Name = "食費PBTテーブル"
Set PivotS = ThisWorkbook.Worksheets("食費PBTテーブル")


'『ピボットテーブル』シートにピボットテーブル作成
PCache.CreatePivotTable _
        TableDestination:=PivotS.Range("A1"), _
        TableName:="食費集計"
        
With Worksheets("食費PBTテーブル").PivotTables("食費集計")
    .ColumnGrand = True
    .HasAutoFormat = True
    .DisplayErrorString = False
    .DisplayNullString = True
    .EnableDrilldown = True
    .ErrorString = ""
    .MergeLabels = False
    .NullString = ""
    .PageFieldOrder = 2
    .PageFieldWrapCount = 0
    .PreserveFormatting = True
    .RowGrand = True
    .SaveData = True
    .PrintTitles = False
    .RepeatItemsOnEachPrintedPage = True
    .TotalsAnnotation = False
    .CompactRowIndent = 1
    .InGridDropZones = True
    .DisplayFieldCaptions = True
    .DisplayMemberPropertyTooltips = False
    .DisplayContextTooltips = True
    .ShowDrillIndicators = True
    .PrintDrillIndicators = False
    .AllowMultipleFilters = True
    .SortUsingCustomLists = True
    .FieldListSortAscending = False
    .ShowValuesRow = True
    .CalculatedMembersInFilters = False
    .RowAxisLayout xlTabularRow
End With


'項目設定
With Worksheets("食費PBTテーブル").PivotTables("食費集計").PivotFields("年")
    .Orientation = xlRowField
    .Position = 1
End With
With Worksheets("食費PBTテーブル").PivotTables("食費集計").PivotFields("月")
    .Orientation = xlRowField
    .Position = 2
End With
With Worksheets("食費PBTテーブル").PivotTables("食費集計").PivotFields("分類")
    .Orientation = xlRowField
    .Position = 3
End With

Worksheets("食費PBTテーブル").PivotTables("食費集計").AddDataField ActiveSheet.PivotTables( _
        "食費集計").PivotFields("合計単価"), "合計 / 合計単価", xlSum


'ピボットグラフ作成
Worksheets("食費PBTテーブル").Shapes.AddChart2(Style:=216, XlChartType:=xlBarClustered, Left:=200, Top:=20, Width:=400, Height:=400).Select

'データラベル
Worksheets("食費PBTテーブル").ChartObjects("グラフ 1").Chart.SetElement (msoElementDataLabelCenter)

'フィルター
With Worksheets("食費PBTテーブル").ChartObjects("グラフ 1").Chart.PivotLayout.PivotTable.PivotFields("月")
    .PivotItems("2").Visible = False
End With


End Sub

モジュールの解説

まず、ピボットテーブルを作成するにあたって、知っておくべきことは「ピボットキャッシュ（PivotCache）」を作成してから、「ピボットテーブル」を作成するということです。

ピボットキャッシュの作成とは、範囲指定のことです。どこからどこまでのデータを扱うのかを指定するということです。指定したピボットキャッシュから、ピボットテーブルを作成します。

PivotCaches.Create構文
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=データソースの範囲)

作成したモジュールでは、変数「PCache」に「PivotCaches.Create（～」、の構文を代入しています。
で、作成したピボットキャッシュから、ピボットテーブルを作成します。

CreatePivotTable構文
PivotCacheオブジェクト.CreatePivotTable TableDestination:=位置指定, TableName:=ピボットテーブル名

これでピボットテーブルが完成。
あとは、項目を配置する。

題目のピボットグラフを作成するのは、「AddChart2」を使います。

AddChart2メソッドの記述方法
Shapesオブジェクト.AddChart2(プロパティ)

作成したモジュールでは、データラベルを追加して月フィルターで３月のみ表示されるようにしています。
これで完成。

まとめると、PivotCache　→　CreatePivotTable　→　AddChart2。
ピボットテーブル作成からグラフ作成までの流れ。

以上です。

応用として、指定した月のグラフを作成するとかいろいろできそうです。
とりあえずのピボットグラフ作成のひな形として。