EXCEL VBA ピボットテーブルの作成・操作・更新・データ範囲の取得・集計フィールド設定（PivotTable）

2022年7月17日

EXCEL VBA ピボットテーブルの作成・操作・更新・データ範囲の取得・集計フィールド設定（PivotTable）

●はじめに

今回説明するのは、EXCEL VBAで作成・操作するピボットテーブル（PivotTable）の利用方法を説明いたします。ピボットテーブルは、一覧データから集計表を作成する時にとても便利な機能です。ピボットテーブルとVBAを組み合わせる事により、一瞬でピボットテーブルを作成するなど、様々な活用方法が増えると思います。それでは、サンプルプログラムを交えて順番に説明致します。

●【PivotTablesオブジェクト (Excel)、下記を参照して下さい】（Microsoft社　様）】
https://docs.microsoft.com/ja-jp/office/vba/api/excel.pivottables

●書式の説明

●　ピボットテーブル（PivotTable）を作成するには、下記の通りに設定を行います。

【ピボットテーブルの作成手順】

①ピボットキャッシュの作成
・PivotCaches.Add（SourceType,SourceData）

定数（SourceType)	値	内容・説明
xlDatabase	１	Excel のリスト/データベース
xlExternal	２	外部のアプリケーションのデータ
xlConsolidation	３	複数のワークシート範囲
xlScenario	４	データは、[シナリオの登録と管理] を使用して作成されたシナリオに基づきます。
xlPivotTable	-4148	既存のピボットテーブルレポート

②ピボットテーブルの作成
・CreatePivotTable（TableDestination,TableName,ReadData,DefaultVersion）

定数	必須・オプション	データ型	説明・内容
TableDestination	必須	Variant	ピボットテーブルレポートの移動先範囲の左上隅にあるセル (結果のピボットテーブルレポートが配置されるワークシートの範囲)。変換先の範囲は、式で指定された PivotCache オブジェクトを含むブック内のワークシート上にある必要があります。
TableName	省略可能	Variant	作成するピボットテーブルレポートの名前を指定します。
ReadData	省略可能	Variant	True を指定すると、外部データベースのすべてのレコードを含むピボットテーブルキャッシュが作成されます。このキャッシュは非常に大きい場合があります。 False を指定すると、データが実際に読み取る前に、一部のフィールドをサーバーベースのページフィールドとして設定できます。
DefaultVersion	省略可能	Variant	ピボットテーブルレポートの既定のバージョンを指定します。

③ピボットテーブルにフィールドを作成
・PivotFields（Index）

・Orientation ＝　既定値

定数	説明・内容
xlHidden	非表示（削除）
xlRowField	行ラベル
xlColumnField	列ラベル
xlPageField	レポートフィルター（ページ）
xlDataField	テータ（値）

【使用例】

【サンプルプログラム】

'
'
Sub Pivottable01() 'ピボットテーブルの作成


    Dim Setrng As Range
    
    Set Setrng = Range("A1").CurrentRegion 'セルA1を元にデータ範囲を指定

    ActiveWorkbook.PivotCaches.Add(xlDatabase, Setrng).CreatePivotTable Range("E1") 'データ範囲を元にセルE1ピボットキャッシュ（テーブル）を作成

    With ActiveSheet.PivotTables(1)
        .PivotFields("支店名").Orientation = xlRowField  'ピボットテーブルの行ラベルを指定「支店名」
        .PivotFields("商品").Orientation = xlColumnField  'ピボットテーブルの列ラベルを指定「商品」
        .PivotFields("金額").Orientation = xlDataField  'ピポットテーブルのデータ（値）を指定「金額」
    End With
    
End Sub
'

Sub Pivottable01() 'ピボットテーブルの作成

Dim Setrng As Range

Set Setrng = Range("A1").CurrentRegion 'セルA1を元にデータ範囲を指定

ActiveWorkbook.PivotCaches.Add(xlDatabase, Setrng).CreatePivotTable Range("E1") 'データ範囲を元にセルE1ピボットキャッシュ（テーブル）を作成

With ActiveSheet.PivotTables(1)

.PivotFields("支店名").Orientation = xlRowField 'ピボットテーブルの行ラベルを指定「支店名」

.PivotFields("商品").Orientation = xlColumnField 'ピボットテーブルの列ラベルを指定「商品」

.PivotFields("金額").Orientation = xlDataField 'ピポットテーブルのデータ（値）を指定「金額」

End With

End Sub

【実行前～実行後】　※プログラム実行後、左下のデータを元に、ピボットテーブルが作成されました。

【注意】既にピボットテーブルが作成されている状態で再度、上記プログラムを実行するとピボットテーブルが作成されているので、エラーが発生します。この場合、プログラムにピボットテーブルが作成されている場合の回避方法をプログラムに記述する必要があります。詳細は、下記サンプルプログラムを参照して下さい。

EXCEL VBA ピボットテーブルの作成・ピボットテーブルが作成されているか確認（ピポットテーブル罫線作成・3桁カンマ）

●プログラム説明　(サンプル①)

下記のサンプルプログラムは、ピポットテーブルを作成する時に、既にピポットテーブルが作成されているか確認し、ピボットテーブルが作成されていない場合は、ピボットテーブルを作成するサンプルプログラムです。既に同じピボットテーブルが作成されている場合は、エラーが発生するので、このエラー発生を回避する事もできます。それでは、サンプルプログラムを交えて順番に説明いたします。

A：ピボットテーブルが有るかの判定は、【PivotTables.Count】で判定する事が出来ます。（※Pivottables.Countで０を超えるとピボットテーブルが有り）
B：ピボットテーブルに罫線を引く場合は、通常のセルに罫線を引く方法と同じ様に、【 Range(“E1”).CurrentRegion.Borders.LineStyle = True】で引く事が出来ます。
C：ピボットテーブルに数値3桁カンマ表示するには、【.NumberFormat = “#,##0_ “】を利用する事で表示する事が出来ます。
※記述方法については、下記プログラムを参照して下さい。

【プログラムの流れ】
①　ピボットテーブルの元データとしてセルＡ１を起点にセル範囲を取得します。
②　既にワークシートにピボットテーブルが有るか判定します。
（有る）ピボットテーブルにピポットテーブルが有る場合は、・・・・ピポットテーブルを削除。
（無い）ピポットテーブルが無い場合は、・・・・何もしない。
③　①のデータを使いピポットテーブルをセルE1を起点に作成します。（ピポットキャッシュ）
④　作成したピボットテーブルの行ラベルを指定します。【支店名】
⑤　作成したピボットテーブルの列ラベルを指定します。【商品】
⑥　作成したピボットテーブルのデータ（値）を指定します。【金額】併せて数値の3桁カンマ区切りに設定します。
⑦　作成したピボットテーブルの範囲に罫線を引きます。

'
'
'
Sub Pivottable01() 'ピボットテーブルの存在を確認してピボットテーブルを作成


    Dim Setrng As Range
    
    Set Setrng = Range("A1").CurrentRegion 'セルA1を元にデータ範囲を指定

    
    With ActiveSheet
    
        If .PivotTables.Count > 0 Then 'A：ピボットテーブルの数をカウント[0]を超える場合は、以下の処理を実施（ピボットテールを削除）
            .PivotTables(1).TableRange1.Clear  'アクティブシートのピボットテーブルを削除します。
        End If
            
        ActiveWorkbook.PivotCaches.Add(xlDatabase, Setrng).CreatePivotTable Range("E1") 'データ範囲を元にセルE1ピボットキャッシュ（テーブル）を作成
    
        With ActiveSheet.PivotTables(1)
            .PivotFields("支店名").Orientation = xlRowField  'ピボットテーブルの行ラベルを指定「支店名」
            .PivotFields("商品").Orientation = xlColumnField  'ピボットテーブルの列ラベルを指定「商品」
            
            With .PivotFields("金額")
                 .Orientation = xlDataField  'ピポットテーブルのデータ（値）を指定「金額」
                 .NumberFormat = "#,##0_ " 'C：データフィールドを三桁カンマ区切り設定
            End With
        End With
        
        Range("E1").CurrentRegion.Borders.LineStyle = True 'B:ピボットテールの範囲に罫線を引く
    
    End With
    
End Sub
'

Sub Pivottable01() 'ピボットテーブルの存在を確認してピボットテーブルを作成

Dim Setrng As Range

Set Setrng = Range("A1").CurrentRegion 'セルA1を元にデータ範囲を指定

With ActiveSheet

If .PivotTables.Count > 0 Then 'A：ピボットテーブルの数をカウント[0]を超える場合は、以下の処理を実施（ピボットテールを削除）

.PivotTables(1).TableRange1.Clear 'アクティブシートのピボットテーブルを削除します。

End If

ActiveWorkbook.PivotCaches.Add(xlDatabase, Setrng).CreatePivotTable Range("E1") 'データ範囲を元にセルE1ピボットキャッシュ（テーブル）を作成

With ActiveSheet.PivotTables(1)

.PivotFields("支店名").Orientation = xlRowField 'ピボットテーブルの行ラベルを指定「支店名」

.PivotFields("商品").Orientation = xlColumnField 'ピボットテーブルの列ラベルを指定「商品」

With .PivotFields("金額")

.Orientation = xlDataField 'ピポットテーブルのデータ（値）を指定「金額」

.NumberFormat = "#,##0_ " 'C：データフィールドを三桁カンマ区切り設定

End With

Range("E1").CurrentRegion.Borders.LineStyle = True 'B:ピボットテールの範囲に罫線を引く

End With

End Sub

●実行前～実行後　※データを元に、ピボットテーブルが作成されました。今回のサンプルプログラムでは、ピボットテーブルが既に作成されているかの判定結果により、ピボットテーブルを作成します。
（画面クリックして拡大）

EXCEL VBA ピボットテーブル内の指定したデータをコピーする。（特定データの転記・データを抜き取る）

●プログラム説明　(サンプル②)

下記のサンプルプログラムは、ピボットテーブルを作成した後（集計）にピボットテーブル内の指定したデータをコピーするサンプルプログラムです。ピボットテーブルを作成した後に必要なデータのみを再利用する際にはとても便利です。それでは、サンプルプログラムを交えて説明いたします。

１．ピボットテーブルから指定した行データを指定したセル位置（場所）へコピーする。
指定条件：①支店名　②神戸支店　③セル（F16）

２．ピボットテーブルから指定した列データを指定したセル位置（場所）へコピーする。
指定条件：①支店名　②神戸支店　③セル（F16）

【プログラム実行条件】
①　アクティブシートにあるピボットテーブルが対象となります。
②　指定条件に無い項目を入力するとエラーが発生します。
③　アクティブシートにピボットテーブルが複数ある場合は、1つ目が対象となります。

'
'
Sub Pivottable02() '①指定した行データを転記します。
    
    With ActiveSheet.PivotTables(1)

        .RowFields("支店名").PivotItems("神戸支店").DataRange.Copy Range("F16")

    End With
End Sub
'
'
Sub Pivottable03() '②指定した列データを転記します。
    
    With ActiveSheet.PivotTables(1)

        .ColumnFields("商品").PivotItems("エアコン").DataRange.Copy Range("F12")

    End With
End Sub
'
'

Sub Pivottable02() '①指定した行データを転記します。

With ActiveSheet.PivotTables(1)

.RowFields("支店名").PivotItems("神戸支店").DataRange.Copy Range("F16")

End With

End Sub

Sub Pivottable03() '②指定した列データを転記します。

With ActiveSheet.PivotTables(1)

.ColumnFields("商品").PivotItems("エアコン").DataRange.Copy Range("F12")

End With

End Sub

●実行前～実行後　※プログラム実行後、ピボットテーブル内の指定した行列データより、指定したセルにデータがコピーされました。
（画面クリックして拡大）

EXCEL VBA ピボットテーブル内のフィールド（列・行）のアイテムを取得する（行・列項目の転記）

●プログラム説明　(サンプル③)

下記のサンプルプログラムは、ピボットテーブルで作成した行列のフィールドアイテムを取得します。サンプル②では、データに対する行列のデータを別セルに転記する方法でしたが、今回は、ピボットテーブル内の項目に位置する部分のフィードアイテムを取得します。それでは、サンプルプログラムを交えて順番に説明致します。

１．ピボットテーブルから指定したフィールドアイテム（行）を指定したセル位置（場所）へ転記する。
指定条件：①支店名　②セル（E13）

２．ピボットテーブルから指定したフィールドアイテム（列）を指定したセル位置（場所）へ転記する。
指定条件：①商品　②セル（E13）

'
'

Sub Pivottable04() '指定したフィールドアイテム（行）を転記します。

    Dim i As Long
    
    With ActiveSheet.PivotTables(1)
        
        For i = 1 To .PivotFields("支店名").PivotItems.Count
            
            Range("E13").Offset(0, i - 1).Value = .PivotFields("支店名").PivotItems(i)

        Next i
    End With

End Sub
'
'



Sub Pivottable05() '指定したフィールドアイテム（列）を転記します。

    Dim i As Long
    
    With ActiveSheet.PivotTables(1)
        
        For i = 1 To .PivotFields("商品").PivotItems.Count
        
            Range("E13").Offset(i - 1, 0).Value = .PivotFields("商品").PivotItems(i)
        Next i
    End With

End Sub

'
'

Sub Pivottable04() '指定したフィールドアイテム（行）を転記します。

Dim i As Long

With ActiveSheet.PivotTables(1)

For i = 1 To .PivotFields("支店名").PivotItems.Count

Range("E13").Offset(0, i - 1).Value = .PivotFields("支店名").PivotItems(i)

Next i

End With

End Sub

Sub Pivottable05() '指定したフィールドアイテム（列）を転記します。

Dim i As Long

With ActiveSheet.PivotTables(1)

For i = 1 To .PivotFields("商品").PivotItems.Count

Range("E13").Offset(i - 1, 0).Value = .PivotFields("商品").PivotItems(i)

Next i

End With

End Sub

●実行前～実行後　※プログラム実行後、ピボットテーブルから指定したフィールドアイテム（行・列）のでデータを指定したセル位置より転記されました。
（画面クリックして拡大）

EXCEL VBA ピボットテーブルの作成・ピボットテーブルの背景色・書式の設定・変更・数値マイナス赤字表示

●プログラム説明　(サンプル④)

下記のサンプルプログラムは、ピボットテーブルの書式の設定や変更方法を説明したいと思います。通常にピボットテーブルを作成するとシンプルなピボットテーブルが作成されるので、見やすくするためにピポットテーブル内の背景色・書式を設定します。背景色・書式を設定する事で視覚的に解りやすく表現する事が出来ます。それでは、サンプルプログラムを交えて順番に説明致します。

●既に作成しているピポットテーブルの各フィールドに背景色を設定する場合は、以下の通りに設定します。

①　アクティブシートのピポットテーブル全体に背景色を設定する場合。
②　アクティブシートのピポットテーブルの行ラベルに背景色を設定する場合。

③　アクティブシートのピポットテーブルの行ラベルに背景色を設定する場合。

④　アクティブシートのピポットテーブルのデータ（値）に背景色を設定する場合。

※　下記のサンプルプログラム（A）は、既に作成されているピボットテーブルに対して各フィールドに背景色を指定します。

【プログラム実行条件】
・ワークシート上にピボットテーブルが既に作成されている。

【サンプルプログラム】

'
'
Sub Pivottable06()  '背景色を設定

        With ActiveSheet.PivotTables(1)
        
            .TableRange1.Interior.ColorIndex = 17  'ピポットテーブル全体の背景色を指定
           
             .PivotFields("支店名").DataRange.Interior.ColorIndex = 28  '支店名の背景色を指定
         
            .PivotFields("担当").DataRange.Interior.ColorIndex = 37  '担当の背景色を指定
            
            .PivotFields("合計 / 利益").DataRange.Interior.ColorIndex = 20  'データ全体の背景色を指定
             
            
        End With

End Sub
'

Sub Pivottable06() '背景色を設定

With ActiveSheet.PivotTables(1)

.TableRange1.Interior.ColorIndex = 17 'ピポットテーブル全体の背景色を指定

.PivotFields("支店名").DataRange.Interior.ColorIndex = 28 '支店名の背景色を指定

.PivotFields("担当").DataRange.Interior.ColorIndex = 37 '担当の背景色を指定

.PivotFields("合計 / 利益").DataRange.Interior.ColorIndex = 20 'データ全体の背景色を指定

End With

End Sub

★Interior.ColorIndexのカラーインデックス番号は以下の通りです。

※　下記のサンプルプログラム（B）は、データを元にピボットテーブルを作成して各フィールドに背景色を指定して、データフィールド部分がマイナス表示の場合は、赤字で表示するサンプルプログラムです。

【注意事項】
データフィールドにマイナス表記を赤文字として表示する場合は、以下の通りに設定します。
.NumberFormat = “#,##0;[Red]▲#,##0″ 　　　　「Red」・・・・正常に赤文字表示されます。
.NumberFormat = “#,##0;[赤]▲#,##0″　　　　　「赤」・・・・赤文字として表示されません。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
●　Pivottable04（サンプルプログラム）

'
Sub Pivottable08()    'ピボットテーブルの存在を確認してピボットテーブルを作成・各フィールドに背景色を設定

    Dim Setrng As Range
    
    Set Setrng = Range("A1").CurrentRegion 'セルA1を元にデータ範囲を指定

    
      With ActiveSheet
      
          If .PivotTables.Count > 0 Then 'ピボットテーブルの数をカウント[0]を超える場合は、以下の処理を実施（ピボットテールを削除）
              .PivotTables(1).TableRange1.Clear  'アクティブシートのピボットテーブルを削除します。
          End If
              
          ActiveWorkbook.PivotCaches.Add(xlDatabase, Setrng).CreatePivotTable Range("E1") 'データ範囲を元にセルE1ピボットキャッシュ（テーブル）を作成
      
          With ActiveSheet.PivotTables(1)
              .PivotFields("支店名").Orientation = xlRowField  'ピボットテーブルの行ラベルを指定「支店名」
              
              
              .PivotFields("担当").Orientation = xlColumnField  'ピボットテーブルの列ラベルを指定「商品」
    
              
              With .PivotFields("利益")
                   .Orientation = xlDataField  'ピポットテーブルのデータ（値）を指定「金額」
                   .NumberFormat = "#,##0;[Red]▲#,##0" 'データフィールドを三桁カンマ区切り設定/マイナスは、赤字に設定
          
              End With
        
              .TableRange1.Interior.ColorIndex = 17  'ピポットテーブル全体の背景色を指定
             
              .PivotFields("支店名").DataRange.Interior.ColorIndex = 28  '支店名の背景色を指定
              
              .PivotFields("担当").DataRange.Interior.ColorIndex = 37  '担当の背景色を指定
              
              .PivotFields("合計 / 利益").DataRange.Interior.ColorIndex = 20  'データ全体の背景色を指定
              
          End With
          
         
          Range("E1").CurrentRegion.Borders.LineStyle = True 'ピボットテールの範囲に罫線を引く
      
      End With
    
    End Sub
'

Sub Pivottable08() 'ピボットテーブルの存在を確認してピボットテーブルを作成・各フィールドに背景色を設定

Dim Setrng As Range

Set Setrng = Range("A1").CurrentRegion 'セルA1を元にデータ範囲を指定

With ActiveSheet

If .PivotTables.Count > 0 Then 'ピボットテーブルの数をカウント[0]を超える場合は、以下の処理を実施（ピボットテールを削除）

.PivotTables(1).TableRange1.Clear 'アクティブシートのピボットテーブルを削除します。

End If

ActiveWorkbook.PivotCaches.Add(xlDatabase, Setrng).CreatePivotTable Range("E1") 'データ範囲を元にセルE1ピボットキャッシュ（テーブル）を作成

With ActiveSheet.PivotTables(1)

.PivotFields("支店名").Orientation = xlRowField 'ピボットテーブルの行ラベルを指定「支店名」

.PivotFields("担当").Orientation = xlColumnField 'ピボットテーブルの列ラベルを指定「商品」

With .PivotFields("利益")

.Orientation = xlDataField 'ピポットテーブルのデータ（値）を指定「金額」

.NumberFormat = "#,##0;[Red]▲#,##0" 'データフィールドを三桁カンマ区切り設定/マイナスは、赤字に設定

End With

.TableRange1.Interior.ColorIndex = 17 'ピポットテーブル全体の背景色を指定

.PivotFields("支店名").DataRange.Interior.ColorIndex = 28 '支店名の背景色を指定

.PivotFields("担当").DataRange.Interior.ColorIndex = 37 '担当の背景色を指定

.PivotFields("合計 / 利益").DataRange.Interior.ColorIndex = 20 'データ全体の背景色を指定

End With

Range("E1").CurrentRegion.Borders.LineStyle = True 'ピボットテールの範囲に罫線を引く

End With

End Sub

●実行前～実行後　※プログラム実行後、データを元にピボットテーブルを作成して、各フィールドには指定した背景色が設定されて、データフィールド内にマイナス表記が有る場合は、赤文字に変換されました。
（画面クリックして拡大）

EXCEL VBA ピボットテーブルの作成・ピボットテーブルのデータ範囲変更・更新

●プログラム説明　(サンプル⑤)

下記のサンプルプログラムは、通常ピポットテーブルを作成すると、参照するデータ範囲を設定して作成してしまいます。データを追加すると再度、ピポットテーブルのデータ参照範囲を変更する必要があります。データを追加するたびに参照範囲を変更するのは、大変なので、ここではデータ範囲が変更されてもデータ範囲を自動取得し、ピポットテーブルを作成する方法を説明いたします。なお、ここで説明する例はあくまでも一例です。

【プログラムの流れ】
①　ワークシート「元データ」・「ピボットテーブル」を設定します。

【プログラム実行条件】
・　ワークシート名「元データ」・「ピボットテーブル」を設定します。
・　ワークシート名「元データ」は、ピボットテーブルを作成するためのデータを登録します。
・　ワークシート名「ピボットテーブル」にピボットテーブルを作成している必要があります。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
●　Pivottable05（サンプルプログラム）

'
'
Sub Pivottable08()    'ピボットテーブルを更新します。
    Dim ws01, ws02 As Worksheet
    Dim Setrng As Range
    
    
    Set ws01 = Worksheets("元データ")
    Set ws02 = Worksheets("ピボットテーブル")
    
    
    Set Setrng = ws01.Range("A1").CurrentRegion 'ワークシート（元データ）セルA1を起点にデータ範囲を取得
    
    With ws02
    
        .PivotTables(1).ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, Setrng)
        'ピポットキャッシュに対してデータ範囲を設定します。（再取得したデータを設定）
                            
        .Range("A1").CurrentRegion.Borders.LineStyle = True 'ピボットテールの範囲に罫線を引く
        
        .Activate  'ワークシート「ピボットテーブル」をアクティブにします。
        
      End With
      
    ActiveWorkbook.RefreshAll 'アクティブブックに対して全ての更新します。
    
    End Sub
'