こんにちは、フリーランスエンジニア兼ライターのワキザカ サンシロウです。
皆さんは、VBAでピポットテーブルを作ったことがありますか？
ピポットテーブルは大量のデータを効率的に集計・分析する時によく使います。
そこで今回は、
・ピポットテーブルとは？
・ピポットテーブルを作る時の考え方
・ピポットテーブルを作る方法
といった基礎的なことから、
・ピポットテーブルを作る具体的なサンプルコード
・ピポットテーブル作成後にデータ追加する方法
・マクロの記録を使った実践的なテクニック
といった応用的な方法まで、徹底的に解説します！
この記事の目次
ピポットテーブルとは
ピポットテーブルとは、データ集計・分析をしやすくするためのExcelの機能です。
次のような一覧データがあったとします。
データ一覧：
ピポットテーブルを使えば、言語別の学習時間を確認しやすい形式に変換することができます。
ピポットテーブル化したデータ：
このように一覧データから見やすい形式に変えることができるのが、ピポットテーブルです。
この例では数十行でしたが、数千行・数万行でもすぐに集計結果を確認できるので、データ分析にとても便利な機能ですね。
ピポットテーブルを作るときの考え方
次に、ピポットテーブルを作るときの考え方について簡単に解説します。
ピポットテーブルは以下のような流れでデータを作ります。
セル範囲を指定してピポットテーブル用のキャッシュデータを作成
↓
キャッシュデータをもとにピポットテーブル作成
元のデータを変更しないためにキャッシュデータを作ってから、キャッシュデータを使ってピポットテーブルを作るわけですね。
その後、作ったピポットテーブルに集計方法(行・列・値)を指定して、絞り込んだデータを表示します。
そのため、VBAで処理を作る場合も以下のような流れで処理を書いていきます。
1. データ範囲を指定してキャッシュデータを作る
2. キャッシュデータをもとにピポットテーブル作成
3. 集計したい方法に行・列・値を指定
ピポットテーブルの作り方
次に、VBAでピポットテーブルを作る方法について解説します。
先ほどお伝えした通り、以下の手順で作っていくためそれぞれ詳しく解説しますね。
1. データ範囲を指定してキャッシュデータを作る
2. キャッシュデータをもとにピポットテーブル作成
3. 集計したい方法に行・列・値を指定
データ範囲を指定したキャッシュデータ作成方法
まず、ピポットテーブルに使うキャッシュデータを作る方法について解説します。
キャッシュデータの作成方法は次のとおりです。
|
1 2 3 |
Dim キャッシュの変数名 As PivotCache キャッシュの変数名 = ワークブック.PivotCaches.Create(SourceType:=xlDatabase, _ SourceData:=データ範囲) |
キャッシュデータを格納する専用の型「PivotCache」を使って変数を作り、変数に「PivotCaches.Create」で作成したキャッシュを格納してます。
このように、まずはキャッシュデータを作成します。
ピポットテーブル作成方法
次は、ピポットテーブルの作成方法について解説します。
さきほど作ったキャッシュデータを使って、次のように書くことでピポットテーブルを作ります。
ピポットテーブル作成方法：
|
1 2 |
PivotCache.CreatePivotTable TableDestination:= ワークシート.Range(ピポットを作成する開始セル) ,TableName:= ピポットテーブル名 |
新規シートにピポットテーブルを作成する場合は、次のようになります。
新規シートにピポットを作成する方法：
|
1 2 3 4 5 6 7 |
'新規シート作成 Worksheets.Add ActiveSheet.Name = "ピボットテーブルサンプル" 'A3セルからピポットテーブル作成 PivotCache.CreatePivotTable TableDestination:= Worksheets("ピボットテーブルサンプル").Range(“A3”) _ ,TableName:= "言語別学習時間表" |
ピポットテーブル作成直後：
行・列の指定方法
次に、行・列の追加方法について解説します。
ピポットテーブルの行・列の追加方法は次のとおりです。
行・列の追加_コード：
|
1 2 |
ワークシート.PivotTables(ピポットテーブル名).AddFields ColumnFields:=PivotFields(列に追加するフィールド名) , _ RowFields:=PivotFields(行に追加するフィールド名) |
行・列設定後イメージ：
値の指定方法
次に、値の追加方法について解説します。
ピポットテーブルで値を追加するときは、以下のように値フィールドの集計方法を選択します。
VBAでは以下のように書くことで、値フィールドを指定して値を追加することができます。
|
1 2 3 4 |
ワークシート.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 |
サンプルコード
次に、サンプルコードをもとに具体的な使い方を解説します。
ピポットテーブル作成に使うデータ：
サンプルコード：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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)」の合計をして指定して、ピポットテーブルを作成しています。
このように、簡単にピポットテーブルを追加することができます。
データ追加時の対処方法
ただ、既存のピポットテーブルにデータを追加したいケースもありますよね。
そのため、データの範囲を変更する方法を合わせて覚えておくのがおすすめです！
変更方法は次のとおりです。
|
1 2 3 |
ワークシート.PivotTables(ピポットテーブル名).ChangePivotCache _ ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _ SourceData:=データ元のシート名!データ範囲) |
「ワークシート.PivotTables(ピポットテーブル名).ChangePivotCache」でデータソースの変更を指定した後、新しくデータソースを設定することで変更できます。
注意点として、「SourceData:=データ元のシート名!データ範囲」のように「データ元のシート名」 + 「!」 + 「データ範囲」になることに注意してください。
先ほど作成したピポットテーブルのデータソースを変更するサンプルを用意しました。
実行前：
サンプルコード：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 |
実行後：
データの範囲を「A1:D21」から「A1:D10」に変更しています。
このように、データ範囲も簡単に変えることができるので、覚えておくと便利です。
マクロの記録を使った実践的なテクニック
ただ、ピポットテーブルは今回解説した方法以外にも様々な機能が備わっていますよね。
それらすべての書き方をVBAで覚えるのは、正直大変です。
そのため、マクロの記録機能を覚えておくのがおすすめです！
マクロの記録を使えば、Excelの操作を自動でVBAのコードに変換することができます。
そのため、「作りたいピポットテーブルを自分で作って、作り方をマクロの記録で保存する」といった使い方ができます。
マクロの記録の詳しい使い方については以下で解説しているので、気になる方はぜひ見てみてくださいね！
まとめ
今回は、VBAでピポットテーブルを作る方法・データ範囲の変更方法について解説しました。
ピポットテーブルはデータ分析でよく使うので、覚えておくと便利です！
使い方も簡単なので、ぜひ使ってみてくださいね！
