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

エクセルVBA,ピボットテーブル,pivottable,addfields,addfield

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

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

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

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

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

スポンサーリンク

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

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

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

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

記述方法はこちら。

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

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

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

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

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

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

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

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

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

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

使用例はこちら。

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

では、こちらの例も列に「品目」を追加した「月別仕入表」で実行してみましょう。
ピボットテーブル,AddField実行後,行追加

この通り、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

サンプルプログラムの実行結果

上のコードを実行した結果がこちら。
AddDataField,AddField,実行後

真っ白だったピボットテーブルに、行も列も値も追加されていますね。

これで、ピボットテーブルのフィールドに項目が追加できました!

まとめ

今回は、すでに作成されたピボットテーブルに、フィールドを追加する方法をご紹介しました。

今回の内容と、前回、前々回のピボットキャッシュ作成~ピボットテーブル作成と組み合わせれば、フィールドがセットされたピボットテーブル作成まで一気通貫で作成することも可能ですね。

それでは、最後までお読みいただきありがとうございました!

コメント

  1. 通りすがり より:

    PivotFields(“品目”)やPivotFields(“仕入日”)という表現がありますが、
    それぞれarray(“品目”)、array(“仕入日”)ですね。

    • ノグチ より:

      通りすがりさん
      こんにちは!
      コードのご指摘ありがとうございました。ご指摘いただいた個所を修正させて頂きました。

      今後とも当ブログをどうぞ宜しくお願いいたします。

タイトルとURLをコピーしました