VBAでピボットテーブルに行と列を設定
VBAで作成したピボットテーブルに行と列を設定する方法です。VBAでピボットテーブルを作成する方法は下記を参照してください。

VBAでデータからピボットテーブルを自動作成
VBAでピボットテーブルを作成する ピボットテーブルは挿入タブのピボットテーブルから手動で作成できますが、VBAを使って自動で作成することもできます。 例えば、データシートからピボットテーブルを作成するVBAコード例は下のような感じです。 ...
ピボットテーブルに行と列を設定するコード例は下のような感じです。(※この記事では列フィールドを値フィールドのこととして説明します)
Dim pivot As PivotTable Dim columnFieldsArray As Variant Dim RowFieldsArray As Variant ' --略-- ' 在庫明細表シートにピボットテーブル作成 PCache.CreatePivotTable _ TableDestination:=stockSheet.Cells(startCell.Row + 1, startCell.Column), _ TableName:="ピボットテーブル" stockSheet.Activate Set pivot = stockSheet.PivotTables("ピボットテーブル") 'ピボットテーブルに追加する行、値のリストを設定 RowFieldsArray = Array("品名") columnFieldsArray = Array("売上高", "在庫数", "在庫金額") 'ピボットテーブルに行フィールドを追加 pivot.AddFields RowFields:=RowFieldsArray 'ピボットテーブルに値フィールドを追加 For i = 0 To UBound(columnFieldsArray) pivot.AddDataField _ Field:=pivot.PivotFields(columnFieldsArray(i)), _ Caption:=columnFieldsArray(i) + " " Next
ピボットテーブルのオブジェクトを取得
まず操作したいピボットテーブルのオブジェクトを取得します。
Dim pivot As PivotTable Set pivot = stockSheet.PivotTables("ピボットテーブル")
ピボットテーブルオブジェクトはPivotTableの型で定義します。PivotTables()の引数にはピボットテーブルを作成する際に指定したTableNameを渡します。
ピボットテーブルに設定したい行と列を定義
設定したい行と列の名前を配列で定義します。配列なのでもちろん複数指定可能です。
Dim columnFieldsArray As Variant Dim RowFieldsArray As Variant 'ピボットテーブルに追加する行、値のリストを設定 RowFieldsArray = Array("品名") columnFieldsArray = Array("売上高", "在庫数", "在庫金額")
このとき指定する名前は、ピボットテーブル作成の際に指定したデータ範囲のヘッダ部分の名前です。具体的には下の画像の部分です。
ピボットテーブルに行と列の定義を適用
行と列の定義をピボットテーブルに反映します。
'ピボットテーブルに行フィールドを追加 pivot.AddFields RowFields:=RowFieldsArray 'ピボットテーブルに値フィールドを追加 For i = 0 To UBound(columnFieldsArray) pivot.AddDataField _ Field:=pivot.PivotFields(columnFieldsArray(i)), _ Caption:=columnFieldsArray(i) + " " Next
行フィールドは配列をそのまま指定できるのですが、列フィールドはそのまま配列で指定できません。
列フィールドは一つ一つの列に対して別々な設定ができるよう、1項目ずつ指定します。そのため配列をforループで回して設定しています。
Fieldには項目名、Captionにはピボットテーブルに表示される項目名を指定します。Captionに + ” ” と空文字を連結しているのは、そうしないと「項目名が重複している」というエラーが出ることがあるためです。
マクロが成功していれば、下のように設定した行と列で集計されて表示されるかと思います。
コメント