【ExcelVBA】ピポットテーブル作成方法・データ範囲の変更方法を解説!

こんにちは、フリーランスエンジニア兼ライターのワキザカサンシロウです。

皆さんは、VBAでピポットテーブルを作ったことがありますか？

ピポットテーブルは大量のデータを効率的に集計・分析する時によく使います。

そこで今回は、

・ピポットテーブルとは？

・ピポットテーブルを作る時の考え方

・ピポットテーブルを作る方法

といった基礎的なことから、

・ピポットテーブルを作る具体的なサンプルコード

・ピポットテーブル作成後にデータ追加する方法

・マクロの記録を使った実践的なテクニック

といった応用的な方法まで、徹底的に解説します！

この記事の目次

1 ピポットテーブルとは
2 ピポットテーブルを作るときの考え方
3 ピポットテーブルの作り方
4 サンプルコード
5 データ追加時の対処方法
6 マクロの記録を使った実践的なテクニック
7 まとめ

ピポットテーブルとは

ピポットテーブルとは、データ集計・分析をしやすくするためのExcelの機能です。

次のような一覧データがあったとします。

データ一覧：

ピポットテーブルを使えば、言語別の学習時間を確認しやすい形式に変換することができます。

ピポットテーブル化したデータ：

このように一覧データから見やすい形式に変えることができるのが、ピポットテーブルです。

この例では数十行でしたが、数千行・数万行でもすぐに集計結果を確認できるので、データ分析にとても便利な機能ですね。

ピポットテーブルを作るときの考え方

次に、ピポットテーブルを作るときの考え方について簡単に解説します。

ピポットテーブルは以下のような流れでデータを作ります。

セル範囲を指定してピポットテーブル用のキャッシュデータを作成
↓
キャッシュデータをもとにピポットテーブル作成

元のデータを変更しないためにキャッシュデータを作ってから、キャッシュデータを使ってピポットテーブルを作るわけですね。

その後、作ったピポットテーブルに集計方法(行・列・値)を指定して、絞り込んだデータを表示します。

そのため、VBAで処理を作る場合も以下のような流れで処理を書いていきます。

1. データ範囲を指定してキャッシュデータを作る
2. キャッシュデータをもとにピポットテーブル作成
3. 集計したい方法に行・列・値を指定

ピポットテーブルの作り方

次に、VBAでピポットテーブルを作る方法について解説します。

先ほどお伝えした通り、以下の手順で作っていくためそれぞれ詳しく解説しますね。

1. データ範囲を指定してキャッシュデータを作る
2. キャッシュデータをもとにピポットテーブル作成
3. 集計したい方法に行・列・値を指定

データ範囲を指定したキャッシュデータ作成方法

まず、ピポットテーブルに使うキャッシュデータを作る方法について解説します。

キャッシュデータの作成方法は次のとおりです。

Dim キャッシュの変数名 As PivotCache
キャッシュの変数名 = ワークブック.PivotCaches.Create(SourceType:=xlDatabase, _
　　　　　　　　　　　　　　　　　　　　　　　　　      SourceData:=データ範囲)

キャッシュデータを格納する専用の型「PivotCache」を使って変数を作り、変数に「PivotCaches.Create」で作成したキャッシュを格納してます。

このように、まずはキャッシュデータを作成します。

ピポットテーブル作成方法

次は、ピポットテーブルの作成方法について解説します。

さきほど作ったキャッシュデータを使って、次のように書くことでピポットテーブルを作ります。

ピポットテーブル作成方法：

PivotCache.CreatePivotTable  TableDestination:= ワークシート.Range(ピポットを作成する開始セル)
                            ,TableName:= ピポットテーブル名

新規シートにピポットテーブルを作成する場合は、次のようになります。

新規シートにピポットを作成する方法：

'新規シート作成
Worksheets.Add
ActiveSheet.Name = "ピボットテーブルサンプル"

'A3セルからピポットテーブル作成
PivotCache.CreatePivotTable  TableDestination:= Worksheets("ピボットテーブルサンプル").Range(“A3”) _
                            ,TableName:= "言語別学習時間表"

ピポットテーブル作成直後：

行・列の指定方法

次に、行・列の追加方法について解説します。

ピポットテーブルの行・列の追加方法は次のとおりです。

行・列の追加_コード：

ワークシート.PivotTables(ピポットテーブル名).AddFields ColumnFields:=PivotFields(列に追加するフィールド名) , _
                                                  RowFields:=PivotFields(行に追加するフィールド名)

行・列設定後イメージ：

値の指定方法

次に、値の追加方法について解説します。

ピポットテーブルで値を追加するときは、以下のように値フィールドの集計方法を選択します。

VBAでは以下のように書くことで、値フィールドを指定して値を追加することができます。

ワークシート.PivotTables(ピポットテーブル名).AddDataField _
    Field:=ワークシート.PivotTables(ピポットテーブル名.PivotFields(値に追加するフィールド名), _
    Caption:=値の表示名, _
    Function:=値フィールドの集計方法

Functionに指定できる値は次のとおりです。

Functionに指定する集計方法一覧：

No	集計方法	VBAで指定する値
1	合計	xlSum
2	データの個数	xlCount
3	平均	xlAverage
4	最大値	xlMax
5	最小値	xlMin
6	積	xlProduct
7	数値の個数	xlCountNums
8	標本標準偏差	xlStDev
9	標準偏差	xlStDevP
10	標本分散	xlVar
11	分散	xlVarP

サンプルコード

次に、サンプルコードをもとに具体的な使い方を解説します。

ピポットテーブル作成に使うデータ：

サンプルコード：

Sub Test()
  'ピポットテーブルのキャッシュデータ作成
  Dim pCashData As PivotCache
  Set pCashData = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                              SourceData:="A1:D21")

  '新規シート作成
  Dim wsPivot As Worksheet
  Worksheets.Add
  ActiveSheet.Name = "ピボットテーブルサンプル"
  Set wsPivot = Worksheets("ピボットテーブルサンプル")
  
  'A3セルからピポットテーブル作成
  Dim ptblName As String
  ptblName = "言語別学習時間表"
  pCashData.CreatePivotTable TableDestination:=wsPivot.Range("A3") _
                           , TableName:=ptblName
                           
  '行・列を追加
   wsPivot.PivotTables(ptblName).AddFields RowFields:=Array("学習日"), _
                                           ColumnFields:=Array("言語")
  
  '値を追加
  wsPivot.PivotTables(ptblName).AddDataField _
    wsPivot.PivotTables(ptblName).PivotFields("時間 (h)"), _
    Caption:="合計 / 時間 (h)", _
    Function:=xlSum '「合計」を指定
  
  
End Sub

実行後：

新規シートを作成して「A1～D21」までのセル範囲でピポットキャッシュを作成し、行に「学習日」、列に「言語」、値に「時間 (h)」の合計をして指定して、ピポットテーブルを作成しています。

このように、簡単にピポットテーブルを追加することができます。

データ追加時の対処方法

ただ、既存のピポットテーブルにデータを追加したいケースもありますよね。

そのため、データの範囲を変更する方法を合わせて覚えておくのがおすすめです！

変更方法は次のとおりです。

ワークシート.PivotTables(ピポットテーブル名).ChangePivotCache _
  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                    SourceData:=データ元のシート名!データ範囲)

「ワークシート.PivotTables(ピポットテーブル名).ChangePivotCache」でデータソースの変更を指定した後、新しくデータソースを設定することで変更できます。

注意点として、「SourceData:=データ元のシート名!データ範囲」のように「データ元のシート名」 + 「!」 + 「データ範囲」になることに注意してください。

先ほど作成したピポットテーブルのデータソースを変更するサンプルを用意しました。

実行前：

サンプルコード：

Sub データソースの変更()
  Dim wsPivot As Worksheet   'ピポットテーブルのシート
  Dim wsBaseName As String   'データ元のシート名
  Dim ptblName As String     'ピポットテーブル名
  Dim sourceArea As String   'データ範囲
  wsBaseName = "学習スケジュール_サンプル"
  Set wsPivot = Worksheets("ピボットテーブルサンプル")
  ptblName = "言語別学習時間表"
  sourceArea = "A1:D10"

  'ピポットテーブルのデータソース変更
  wsPivot.PivotTables(ptblName).ChangePivotCache _
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                      SourceData:=wsBaseName & "!" & sourceArea)
End Sub

実行後：