【Excel VBA】ピボットテーブルの作成例

今回は、私がよく使用する、実用的（だと思っている）ピボットテーブル作成用プログラムの一例をご紹介します。

ピボットテーブルの作成例

ピボットテーブルについて細々とご紹介すると非常に長くなってしまうのですが、

今回は日頃の事務作業などにおいて実用的だと思われる、私が日頃よく使うサンプルに絞ってご紹介します。

今回は、下記画像の表が「データ」という名前のシートに存在するものとしてご紹介します。

「データ」シートのサンプル

ページ下部のサンプル１のプログラムを元にご紹介しますと、全体的な流れとしては、まずピボットテーブル作成用の「ピボット」シートを用意したいのですが、すでに「ピボット」シートが存在していた場合はエラーが発生してしまいますので、

For Each ws In Worksheets
If ws.Name = “ピボット” Then flag = True
Next ws
If flag = True Then

Set pivot = Worksheets(“ピボット”)

Application.DisplayAlerts = False
pivot.Delete
Application.DisplayAlerts = True

End If

で、既に「ピボット」シートが存在していた場合は削除します。

その後、

Set PCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=data.Range(data.Columns(1), data.Columns(3)))

で、ピボットテーブル作成用のデータとなるキャッシュを作成した後、

Worksheets.Add After:=data
ActiveSheet.Name = “ピボット”

Set pivot = Worksheets(“ピボット”)

で「データ」シートの後ろに改めて「ピボット」シートを作成し、変数pivotに代入しています。

そして「ピボット」シートにピボットテーブルを作成しますが、まず、

PCache.CreatePivotTable _
TableDestination:=pivot.Range(“A3”), _
TableName:=”集計テーブル”

で作成するピボットテーブルの左上端のセルはA３、ピボットテーブルの名前は「集計テーブル」としています。

ピボットテーブルの設定はそれぞれ、

・グリッド内のドロップゾーンを表示

InGridDropZones = True

・従来のピボットテーブルレイアウト

RowAxisLayout xlTabularRow

・列の総計を非表示

ColumnGrand = False

・行の総計を非表示

RowGrand = False

・アイテムのラベルをすべて繰り返す

RepeatAllLabels xlRepeatLabels

・小計を非表示にする

For Each pv_fld In .PivotFields
pv_fld.Subtotals(1) = True
pv_fld.Subtotals(1) = False
Next pv_fld

としています。

ピボットテーブルの行の設定は、

PivotFields(“種別”).Orientation = xlRowField

で「種別」をピボットテーブルの行に設定し、

PivotFields(“種別”).PivotItems(“(blank)”).Visible = False

で「種別」の空白を非表示にしています。

また今回は一例として、

For Each pv_itm In .PivotFields(“種別”).PivotItems
Select Case pv_itm.Caption
Case “野菜”
pv_itm.Visible = True
Case Else
pv_itm.Visible = False
End Select

で、「種別」のフィルタで「野菜」のみにチェックを入れる設定をしています。

この部分の考えかたとしては、

Case “野菜”
pv_itm.Visible = True

でピボットアイテムが野菜の場合はチェックを入れ、

Case Else
pv_itm.Visible = False

で野菜以外のチェックを外すようにしています。

最後に、

.AddDataField Field:=pivot.PivotTables(“集計テーブル”).PivotFields(“価格”), Function:=xlSum

で、「価格」をピボットテーブルの値に設定し、値フィールドの集計を「合計」にしています。

・サンプル１

Sub ピボット()

Dim data As Worksheet ‘元データシート用変数
Dim pivot As Worksheet ‘ピボットテーブル作成シート用変数

Dim PCache As PivotCache ‘ピボットキャッシュ用変数
Dim pv_fld As PivotField ‘ピボットフィールド用変数
Dim pvt As PivotTable ‘ピボットテーブル用変数
Dim pv_itm As PivotItem ‘ピボットアイテム用変数

Dim ws As Worksheet
Dim flag As Boolean

‘『ピボット』シートが存在していた場合、いったん削除して新たに作成
For Each ws In Worksheets
If ws.Name = “ピボット” Then flag = True
Next ws
If flag = True Then

Set pivot = Worksheets(“ピボット”)

Application.DisplayAlerts = False
pivot.Delete
Application.DisplayAlerts = True

End If

Set data = Worksheets(“データ”)

‘『データ』シートからピボットテーブル作成用のキャッシュを作成
Set PCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=data.Range(data.Columns(1), data.Columns(3)))

‘ピボットテーブル作成用の『ピボット』シートを追加
Worksheets.Add After:=data
ActiveSheet.Name = “ピボット”

Set pivot = Worksheets(“ピボット”)

‘『ピボット』シートにピボットテーブル作成
‘作成するピボットテーブルの左上端のセルを指定
‘作成するピボットテーブルの名前を指定
PCache.CreatePivotTable _
TableDestination:=pivot.Range(“A3”), _
TableName:=”集計テーブル”

With ActiveSheet.PivotTables(“集計テーブル”)
.InGridDropZones = True ‘グリッド内のドロップゾーンを表示
.RowAxisLayout xlTabularRow ‘従来のピボットテーブルレイアウト
.ColumnGrand = False ‘列の総計を非表示
.RowGrand = False ‘行の総計を非表示
.RepeatAllLabels xlRepeatLabels ‘アイテムのラベルをすべて繰り返す

‘小計を非表示にする
For Each pv_fld In .PivotFields
pv_fld.Subtotals(1) = True
pv_fld.Subtotals(1) = False
Next pv_fld

.PivotFields(“種別”).Orientation = xlRowField ‘「種別」をピボットテーブルの行に設定
.PivotFields(“種別”).PivotItems(“(blank)”).Visible = False ‘「種別」の空白を非表示

‘「種別」のフィルタで「野菜」のみにチェックを入れる
For Each pv_itm In .PivotFields(“種別”).PivotItems
Select Case pv_itm.Caption
Case “野菜”
pv_itm.Visible = True
Case Else
pv_itm.Visible = False
End Select
Next

.AddDataField Field:=pivot.PivotTables(“集計テーブル”).PivotFields(“価格”), Function:=xlSum ‘「価格」をピボットテーブルの値に設定し、値フィールドの集計を「合計」にする

End With

End Sub

ピボットテーブルの作成結果