【エクセルVBA】ピボットテーブルに行、列、値フィールドを追加する

こんにちは！ノグチです。

前回の記事では、エクセルVBAでピボットキャッシュからピボットテーブルを作成する方法をご紹介しました。

エクセルVBAでピボットテーブルを作成! CreatePivotTableメソッド

エクセルVBAでPivotCacheオブジェクトのCreatePivoTableメソッドを使った、ピボットテーブル自動作成についてご紹介しています。前回ご紹介した、PIvotCacheと組み合わせれば、データソース指定からピボットテーブル作...

今回は、エクセルVBAで作成したピボットテーブルに、これまたエクセルVBAで行、列、値のフィールドを追加する方法をご紹介します！

前回までご紹介した内容と今回ご紹介する内容を使えば、ピボットテーブル作成からフィールドの設定まで完全自動化だってできますよ！

AddFieldsメソッドで行と列フィールドを追加する
1. ColumnFieldsオプションで列に追加するフィールドを指定
2. RowFieldsオプションで行に追加するフィールドを指定
AddDataFieldメソッドで値フィールドを追加する
AddFieldsメソッドとAddDataFieldメソッドでピボットテーブルにフィールドを追加する
1. サンプルプログラム
サンプルプログラムの実行結果
まとめ

AddFieldsメソッドで行と列フィールドを追加する

まずは、ピボットテーブルに行と列フィールドを追加する方法から。

そのためには、PivotTableオブジェクトのAddFieldsメソッドを使います。

AddFieldsメソッドは、ピボットテーブルに行、列フィールドを追加するメソッドです。

記述方法はこちら。

PivotTableオブジェクト.AddFields ColumnFields:=Array(項目名), RowFields:= Array(項目名)

各オプションの使い方は、この「月別仕入表」というピボットテーブルを使って見ていきましょう。

ColumnFieldsオプションで列に追加するフィールドを指定

ピボットテーブルの列フィールドに追加する項目を指定するオプションです。

先にお見せしたピボットテーブル「月別仕入表」で、このピボットテーブルの列に「品目」フィールドを追加したい場合、下記のように記述します。

ActiveSheet.PivotTables("月別仕入表").AddFields ColumnFields:=Array("品目")

そしてこのコードをピボットテーブル「月別仕入表」で実行してみると…

真っ白だったピボットテーブルの列に、「品目」フィールドが追加されていますね。

RowFieldsオプションで行に追加するフィールドを指定

ピボットテーブルの行フィールドに追加する項目を指定するプロパティです。

使用例はこちら。

ActiveSheet.PivotTables("月別仕入表").AddFields RowFields:=Array("仕入日")

では、こちらの例も列に「品目」を追加した「月別仕入表」で実行してみましょう。

この通り、RowFieldsオプションで指定したフィールドが、ピボットテーブルの行に追加されています。

これで、行と列のフィールドは追加できました！

AddDataFieldメソッドで値フィールドを追加する

お次は、 PivotTableオブジェクトのAddDataFieldメソッドを使って、ピボットテーブルに値フィールドを追加していきましょう。

AddDataFieldメソッドは、ピボットテーブルに値フィールドを追加するメソッドです。

記述方法はこちら。

PivotTableオブジェクト.AddDataField Field:=PivotFieldsオブジェクト Caption:=”項目のテキスト”,Function:=値フィールドの関数

Fieldオプションで追加する値フィールドを指定する

ピボットテーブルの値フィールドに追加したい項目を指定するオプションで、AddDataFieldメソッドの必須項目です。

例えばこんな感じで記述します。

ActiveSheet.PivotTables("月別仕入表").AddDataField Field:=PivotS.PivotTables("月別仕入表").PivotFields("仕入数")

AddFieldsと違って、こちらは項目を PivotFieldsオブジェクトで指定するので、お間違えのなきよう。

Captionオプションで項目名を指定する

ピボットテーブルに追加する値フィールドの項目名を指定するオプションです。

例えば、「仕入数」という項目を「数量」という項目名でピボットテーブルに追加したい場合は、こんな風に記述します。

PivotS.PivotTables("月別仕入表").AddDataField Field:=PivotS.PivotTables("月別仕入表"). _
PivotFields("仕入数"),Caption:="数量"

必須項目ではなく、このオプションを使わないで項目を追加した場合は値フィールドの名前は元の項目名のままでピボットテーブルに追加されます。

Functionオプションで値フィールドに使用する関数を指定する

値フィールドに表示する値に使用する関数を指定するオプションです。

関数って…？と思われたかもしれませんが、ピボットテーブルの値フィールドは、「値フィールドの設定」で集計方法を指定できますよね。

これこれ。

Functionは、この「合計」や「データの個数」を指定するオプションです。

例えば、ピボットテーブルの値フィールドの合計を表示したい場合は、こんな感じで記述します。

PivotS.PivotTables("月別仕入表").AddDataField Field:=PivotS.PivotTables("月別仕入表"). _
PivotFields("仕入数"), Caption:="数量", Function:=xlSum

AddFieldsメソッドとAddDataFieldメソッドでピボットテーブルにフィールドを追加する

サンプルプログラム

では、上でお見せしたピボットテーブル「月別仕入表」の列に「品目」、行に「仕入日」、値に「仕入数」を追加するコードを実行してみます。

「月別仕入表」は、「ピボットテーブル」というシートに作成していることとします。

コードはこちら。

Sub Add_PivotFields()

Dim PivotS As Worksheet 'ピボットテーブルがあるシート
Set PivotS = ThisWorkbook.Worksheets("ピボットテーブル")

'ピボットテーブルに行と列フィールドを追加
PivotS.PivotTables("月別仕入表").AddFields ColumnFields:=Array("品目"), _
    RowFields:=Array("仕入日")
'ピボットテーブルに値フィールドを追加
PivotS.PivotTables("月別仕入表").AddDataField _
    Field:=PivotS.PivotTables("月別仕入表").PivotFields("仕入数"), _
    Caption:="数量", _
    Function:=xlSum

End Sub