はじめに
ひらちんです。
VBAからピボットテーブルを作成する方法を紹介します！
ピボットテーブルを作る流れ
VBAからピボットテーブルを作成するには、
- 元になるデータを指定する
- ピボットテーブルを作る場所を指定する
- ピボットキャッシュを作る
- 作ったピボットキャッシュからピボットテーブルを作成する
- ピボットテーブルにフィールドを指定する
という流れになります！
では順番にいきましょう！
1.元になるデータを指定する
サンプルとして以下のデータがあるとします。
※なんちゃって個人情報さん で作らせてもらいました。
これは大丈夫ですね。
普通にセルの範囲を指定しています。
「CurrentRegion」はセル範囲をひとかたまりとして指定する便利なプロパティです。
Sub Sample()
'元データの指定
Dim 元データ As Range
Set 元データ = ThisWorkbook.Sheets("データ").Range("A1").CurrentRegion
End Sub元の範囲をテーブルデータにしておくと後で色々便利かもしれません。
VBAでのテーブル作成の方法は以下の記事をどうぞ(^o^)
ついでにテーブルの名前も変更しておきます。
Sub Sample()
'元データの指定
Dim 元データ As ListObject
Set 元データ = ThisWorkbook.Sheets("データ").ListObjects.Add( _
SourceType:=xlSrcRange, Source:=ThisWorkbook.Sheets("データ").Range("A1").CurrentRegion)
元データ.Name = "tbl元データ"
End Sub2.ピボットテーブルを作る場所を指定する
これは、どこでも良いんですが、通常ピボットテーブル作ると新しいシートが生まれるので、そのようにしておきましょう(^o^)
'ピボットテーブルを作る場所を指定
Dim ピボット作るシート As Worksheet
Set ピボット作るシート = Sheets.Add最初のコードに追加します。
Sub Sample()
'元データの指定
Dim 元データ As Range
Set 元データ = ThisWorkbook.Sheets("データ").Range("A1").CurrentRegion
'ピボットテーブルを作る場所を指定
Dim ピボット作るシート As Worksheet
Set ピボット作るシート = Sheets.Add
End Sub3.ピボットキャッシュを作る
次に、ピボットキャッシュを作ります。
'ピボットキャッシュを作成する
Dim ピボットキャッシュ As PivotCache
Set ピボットキャッシュ = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=元データ)PivotCachesコレクションのCreateメソッドを使います。
引数のSourceTypeには、次の定数を指定します。
| 名前 | 値 | 説明 |
|---|---|---|
| xlConsolidation | 3 | 複数のワークシート範囲 |
| xlDatabase | 1 | Excel のリスト/データベース |
| xlExternal | 2 | 外部のアプリケーションのデータ |
| xlPivotTable | -4148 | 既存のピボットテーブル レポート |
| xlScenario | 4 | データは、[シナリオの登録と管理] を使用して作成されたシナリオに基づきます。 |
ここでは、「xlDatabase」を指定しています。
SourceDataには、データになる範囲を指定するので、さっき作った「元データ」を指定します。
ここまでのコード全容です。
Sub Sample()
'元データの指定
Dim 元データ As Range
Set 元データ = Sheets("データ").Range("A1").CurrentRegion
'ピボットテーブルを作る場所を指定
Dim ピボット作るシート As Worksheet
Set ピボット作るシート = Sheets.Add
'ピボットキャッシュを作成する
Dim ピボットキャッシュ As PivotCache
Set ピボットキャッシュ = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=元データ)
End Sub元データをテーブルで設定している場合は、テーブルに付けた名前でも指定出来ます。
'ピボットキャッシュを作成する
Dim ピボットキャッシュ As PivotCache
Set ピボットキャッシュ = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="tbl元データ")4.作ったピボットキャッシュからピボットテーブルを作成する
では、ピボットキャッシュからピボットテーブルを作成しましょう！
PivotCacheオブジェクトのCreatePivotTableメソッドを使います。
'ピボットテーブルを作成する
Dim ピボットテーブル As PivotTable
Set ピボットテーブル = ピボットキャッシュ.CreatePivotTable(ピボット作るシート.Range("A1"))引数には、ピボットテーブルを作る場所をセルで指定します。
Sub Sample()
'元データの指定
Dim 元データ As Range
Set 元データ = Sheets("データ").Range("A1").CurrentRegion
'ピボットテーブルを作る場所を指定
Dim ピボット作るシート As Worksheet
Set ピボット作るシート = Sheets.Add
'ピボットキャッシュを作成する
Dim ピボットキャッシュ As PivotCache
Set ピボットキャッシュ = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=元データ)
'ピボットテーブルを作成する
Dim ピボットテーブル As PivotTable
Set ピボットテーブル = ピボットキャッシュ.CreatePivotTable(ピボット作るシート.Range("A1"))
End Subこれでピボットテーブルの作成は出来ますので、一度実行してみましょう！
画像のような形で、新しいシートに空のピボットテーブルが作成されます。
5.ピボットテーブルにフィールドを指定する
作成した時に、一緒にフィールドも設定する場合は、次のように指定します。
'ピボットテーブルのフィールドにセットする
With ピボットテーブル
.PivotFields("都道府県").Orientation = xlRowField
.PivotFields("性別").Orientation = xlColumnField
.PivotFields("年齢").Orientation = xlDataField
.PivotFields("血液型").Orientation = xlPageField
End Withピボットテーブルで作成された各フィールドのOrientationプロパティの値を設定してあげます。
※設定が適当ですが悪しからず。。。
場所は図のようになっています。
では、全容です。
Sub Sample()
'元データの指定
Dim 元データ As Range
Set 元データ = Sheets("データ").Range("A1").CurrentRegion
'ピボットテーブルを作る場所を指定
Dim ピボット作るシート As Worksheet
Set ピボット作るシート = Sheets.Add
'ピボットキャッシュを作成する
Dim ピボットキャッシュ As PivotCache
Set ピボットキャッシュ = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=元データ)
'ピボットテーブルを作成する
Dim ピボットテーブル As PivotTable
Set ピボットテーブル = ピボットキャッシュ.CreatePivotTable(ピボット作るシート.Range("A1"))
'ピボットテーブルのフィールドにセットする
With ピボットテーブル
.PivotFields("都道府県").Orientation = xlRowField
.PivotFields("性別").Orientation = xlColumnField
.PivotFields("年齢").Orientation = xlDataField
.PivotFields("血液型").Orientation = xlPageField
End With
End Sub実行してみましょう！！
設定した通りのピボットテーブルの作成が出来ました(^o^)
ピボットテーブルの取得と更新
こちらの記事もどうぞ(^o^)
コメント
VBAの作り方、参考にさせていただいています。
「ピボットテーブルを作成する」の２行目（下記の部分）
Set ピボットテーブル = ピボットキャッシュ.CreatePivotTable(ピボット作るシート.Range(“A1”))
の部分が何回やってみても「実行時エラー’５’:（プロシージャの呼び出し、または引数が不正です。）」となってしまいます。
原因と解決策を教えていただけると幸いです。
ExcelのバージョンはMicrosoft Office Home and Business 2019 です。
よろしくお願いいたします。
こんにちは！コメントありがとうございます！
返信遅くなって申し訳ありません:D
私の環境で同じエラーは発生しないのでなんとも言えない部分はありますが、
データを入れているシートのシート名が「データ」になっていますか？
そのデータシートのA1セルから使っている範囲のデータを元データとしています。
そのようにやっていただいていいましたら、一度作成されているコードもここに貼り付けていただけましたら幸いです！