VBAを使ってピボットテーブルに行と列を設定する

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("売上高", "在庫数", "在庫金額")

このとき指定する名前は、ピボットテーブル作成の際に指定したデータ範囲のヘッダ部分の名前です。具体的には下の画像の部分です。

excel1

ピボットテーブルに行と列の定義を適用

行と列の定義をピボットテーブルに反映します。

'ピボットテーブルに行フィールドを追加
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に + ” ” と空文字を連結しているのは、そうしないと「項目名が重複している」というエラーが出ることがあるためです。

マクロが成功していれば、下のように設定した行と列で集計されて表示されるかと思います。

 

excel2

 

コメント