VBAでピボットテーブルに行と列を設定
VBAで作成したピボットテーブルに行と列を設定する方法です。VBAでピボットテーブルを作成する方法は下記を参照してください。
VBAでデータからピボットテーブルを自動作成
VBAでピボットテーブルを作成する ピボットテーブルは挿入タブのピボットテーブルから手動で作成できますが、VBAを使って自動で作成することもできます。 例えば、データシートからピボットテーブルを作成するVBAコード例は下のような感じです。 ...
hatolabo.com
2018.10.30
ピボットテーブルに行と列を設定するコード例は下のような感じです。（※この記事では列フィールドを値フィールドのこととして説明します）
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に + ” ” と空文字を連結しているのは、そうしないと「項目名が重複している」というエラーが出ることがあるためです。
マクロが成功していれば、下のように設定した行と列で集計されて表示されるかと思います。
コメント