－ちょっと使えるネタを紹介－
Excel VBAで、ピボットテーブルを作成する方法について、ご紹介します。手順としては、CreatePivotTableでピボットテーブルを作成したあと、行や列、フィルター、値のフィールドを設定してきます。大量のピボットテーブルを作成する必要がある場合は、VBAを活用していきましょう。
この記事では、VBAでピボットテーブルを作成する方法について、ご紹介します。
VBAでピボットテーブルを作成するときも、手順は同じで次のように作成します。
という流れです。
ピボットテーブルを大量に作成する必要がある場合は、VBAを活用していきましょう。
では、VBAでピボットテーブルを作成する方法について、解説していきます。
この記事で紹介すること
目次から使いそうな説明のところへ移動すると便利ですよ。
目次
ピボットテーブルの作成と、フィールドの設定について、VBAコードをまとめました。
VBAコードだけを確認したい場合に、ご活用ください。
'ピボットテーブルの作成 Set A = Worksheets("元データ").Range("A1").CurrentRegion '元データの範囲を設定 ActiveWorkbook.PivotCaches.Create(xlDatabase, A).CreatePivotTable Range("A3") '作成する 'フィールドの設定 With ActiveSheet.PivotTables(1) .PivotFields("商品").Orientation = xlRowField '行フィールドを設定 .PivotFields("商品").Orientation = xlColumnField '列フィールドを設定 .PivotFields("商品").Orientation = xlPageField 'フィルターフィールドを設定 .PivotFields("売上").Orientation = xlDataField '値フィールドを設定 End With '「合計」の値フィールドを設定 With ActiveSheet.PivotTables(1).PivotFields("売上") .Orientation = xlDataField '値フィールドを設定 .Function = xlSum '「合計」で集計する End With '「平均」の値フィールドを設定 With ActiveSheet.PivotTables(1).PivotFields("売上") .Orientation = xlDataField '値フィールドを設定 .Function = xlAverage '「平均」で集計する End With '集計方法を「平均」に変更する ActiveSheet.PivotTables(1).PivotFields("合計 / 売上").Function = xlAverage '集計方法を「合計」に変更する ActiveSheet.PivotTables(1).PivotFields("平均 / 売上").Function = xlSum
では、解説していきます。
VBAでピボットテーブルを作成します。
元データから、ピボットテーブルを作成するVBAコードは、こんな感じです。
Sub TEST1()
'シートを追加
Sheets.Add
'元データの範囲を格納
Set A = Worksheets("元データ").Range("A1").CurrentRegion
'ピボットテーブルを作成
ActiveWorkbook.PivotCaches.Create(xlDatabase, A).CreatePivotTable Range("A3")
End Sub
手順としては、
という流れです。
実行してみます。
元データを用意します。
では、VBAコードを実行してみます。
ピボットテーブルが作成できました。
VBAで、ピボットテーブルのフィールドを設定してみます。
フィールドの種類は、4種類あります。
という感じです。
それぞれのフィールドに設定する方法をみていきます。
行フィールドを設定するVBAコードは、こんな感じです。
Sub TEST2()
With ActiveSheet.PivotTables(1).PivotFields("商品")
.Orientation = xlRowField '行フィールドを設定
.Position = 1 '1番目にする
End With
End Sub
「.Orientation = xlRowField」で行フィールドに設定してます。
「.Position = 1」で行フィールドの「1番目」に設定するという意味になります。
「項目が1つ」であれば、設定する必要はありません。
また、「.Position」を設定しない場合は、VBAコードを「実行する順番」で、「項目の順番」が設定されます。
行フィールドを設定する前の初期のピボットテーブルです。
では、VBAコードを実行してみます。
行フィールドを設定できました。
列フィールドを設定するVBAコードは、こんな感じです。
Sub TEST3()
With ActiveSheet.PivotTables(1).PivotFields("商品")
.Orientation = xlColumnField '列フィールドを設定
.Position = 1 '1番目にする
End With
End Sub
「.Orientation = xlColumnField」で列フィールドに設定してます。
列フィールドを設定する前の初期のピボットテーブルです。
では、VBAコードを実列してみます。
列フィールドを設定できました。
フィルターフィールドを設定するVBAコードは、こんな感じです。
Sub TEST4()
With ActiveSheet.PivotTables(1).PivotFields("商品")
.Orientation = xlPageField 'フィルターフィールドに設定
.Position = 1 '1番目にする
End With
End Sub
「.Orientation = xlPageField」でフィルターフィールドに設定してます。
フィルターフィールドを設定する前の、初期のピボットテーブルです。
では、VBAコードを実列してみます。
フィルターフィールドを設定できました。
値フィールドを設定するVBAコードは、こんな感じです。
Sub TEST5()
With ActiveSheet.PivotTables(1).PivotFields("売上")
.Orientation = xlDataField '値フィールドに設定
.Position = 1 '1番目にする
End With
End Sub
「.Orientation = xlDataField」で値フィールドに設定してます。
値フィールドを設定する前の初期のピボットテーブルです。
では、VBAコードを実値してみます。
値フィールドを設定できました。
ピボットテーブルを作成して設定するVBAコードを、まとめて実行してみます。
元データから、クロス集計表を作成するVBAコードは、こんな感じになります。
Sub TEST6()
'シートを追加する
Sheets.Add
'元データの範囲を格納
Set A = Worksheets("元データ").Range("A1").CurrentRegion
'ピボットテーブルを作成する
ActiveWorkbook.PivotCaches.Create(xlDatabase, A).CreatePivotTable Range("A3")
'フィールドを設定
With ActiveSheet.PivotTables(1)
.PivotFields("商品").Orientation = xlRowField '行フィールドを設定
.PivotFields("支店").Orientation = xlColumnField '列フィールドを設定
.PivotFields("売上").Orientation = xlDataField '値フィールドを設定
End With
End Sub
元データを用意しました。
では、VBAコードを実行してみます。
VBAを使って、ピボットテーブルでクロス集計表を作成できました。
VBAで、値フィールドの「集計種類」を設定してみます。
集計種類は、「合計」とか「平均」、「個数」といったものです。
ポイントは、値フィールドを設定する際に、集計種類を設定することです。
「合計」の値フィールドを設定するVBAコードは、こんな感じになります。
Sub TEST7()
With ActiveSheet.PivotTables(1).PivotFields("売上")
.Orientation = xlDataField '値フィールドを設定
.Function = xlSum '「合計」を設定
End With
End Sub
行と列フィールドを設定した、ピボットテーブルを用意しました。
では、実行してみます。
値フィールドの集計の種類を「合計」に設定できました。
設定した後ではなく、設定する際に、集計の種類を設定しましょう。
続いて、「平均」の値フィールドを設定するVBAコードは、こんな感じになります。
Sub TEST8()
With ActiveSheet.PivotTables(1).PivotFields("売上")
.Orientation = xlDataField '値フィールドを設定
.Function = xlAverage '「平均」を設定
End With
End Sub
行と列フィールドを設定した、ピボットテーブルを用意しました。
では、実行してみます。
値フィールドの集計の種類を「平均」に設定できました。
値フィールドの集計種類をまとめました。
こんな感じになります。
作成したい集計の種類を選択して、値フィールドを設定しましょう。
VBAで、値フィールドの集計種類を変更することもできます。
既に設定されている集計種類を、変更してみます。
値フィールドの集計種類を、「合計」から「平均」に変更するVBAコードは、こんな感じです。
Sub TEST9()
With ActiveSheet.PivotTables(1).PivotFields("合計 / 売上")
.Function = xlAverage '「平均」に変更
End With
End Sub
「.PivotFields("合計 / 売上")」という感じで、既に設定されている値フィールドの名前を、設定する必要があります。
値フィールドの集計種類が「合計」のピボットテーブルを用意します。
では、VBAコードを実行してみます。
値フィールドの集計種類を「平均」に変更できました。
値フィールドの集計種類を、「平均」から「合計」に変更するVBAコードは、こんな感じです。
Sub TEST10()
With ActiveSheet.PivotTables(1).PivotFields("平均 / 売上")
.Function = xlSum '「合計」に変更
End With
End Sub
値フィールドの集計種類が「平均」のピボットテーブルを用意します。
では、VBAコードを実行してみます。
値フィールドの集計種類を「合計」に変更できました。
こんな感じで、VBAを使って、既に設定されているフィールドを、変更することもできます。
ただ、初期の状態から作成した方が簡単なので、変更するのではなく、初期の状態から作成していきましょう。
この記事では、VBAでピボットテーブルを作成する方法について、ご紹介しました。
VBAでピボットテーブルを作成するときも、通常の手順と同じで次のように作成します。
という流れです。
ピボットテーブルを大量に作成する必要がある場合は、VBAを活用していきましょう。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す
カテゴリから見たい項目を探すと便利ですよ。
↓キーワードを入力する