コンピューターシステム
株式会社

こんにちは。中部営業所のMTDです。

今回はExcelVBAでピボットテーブルを作成して、いろいろと操作してみようと思います。

Excelでデータの集計の際に欠かせないピボットテーブル。知ってるし使ったことはあるけど、
ExcelVBAで書いたことはない、という人は結構いるのではないでしょうか。（私がそうでした。）
なので、ExcelVBAでピボットテーブルを操作する際の基本的なコードをいろいろと見ていきましょう。

ちなみに、私は普段の業務ではよくExcelVBAでツール開発していますが、まだまだ勉強中です。
今回は自身の復習も兼ねて、理解を深めるためにも書いていきたいと思います。

それでは具体的に、

１．ピボットテーブルを作成する
２．ピボットテーブルを更新する
３．書式設定してみる

この流れで進めていこうと思います。

ピボットテーブルを作成する

まず、元のデータは以下のような表を用意しました。

ではここからピボットテーブルの作成です。

作成するとき、処理の流れはこんな感じになってます。

・ピボットテーブル用のシートを追加
　↓
・元データからピボットキャッシュの作成（ピボットキャッシュとは、元のデータのコピーの
　ようなもので、ピボットテーブル作成時にはこのピボットキャッシュを使用します）
　↓
・ピボットキャッシュから追加したシートにピボットテーブルを作成
　↓
・フィールドの設定

実際にコードを書いてみます。

 Sub CreatePivot()
     ' ピボットテーブル用のシート追加
     Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "ピボットテーブル"

     ' ピボットキャッシュ作成 → ピボットテーブル作成
     ThisWorkbook.PivotCaches.Create(xlDatabase, Worksheets("データ") _
     .Range("A1:D11")).CreatePivotTable Sheets("ピボットテーブル").Range("A3"), "ピボット1"

     ' フィールドを設定
     With ActiveSheet.PivotTables("ピボット1")
         .PivotFields("所属").Orientation = xlRowField
         .PivotFields("名前").Orientation = xlRowField
         .PivotFields("商品").Orientation = xlColumnField
         .PivotFields("数値").Orientation = xlDataField
     End With
 End Sub

それぞれのステップについて見ていきます。

 ' ピボットテーブル用のシート追加
 Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "ピボットテーブル"

 ' ピボットキャッシュ作成 → ピボットテーブル作成
 ThisWorkbook.PivotCaches.Create(xlDatabase, Worksheets("データ").Range("A1:D11")) _  ・・・①
 .CreatePivotTable Sheets("ピボットテーブル").Range("A3"), "ピボット1"  ・・・②

引数にはピボットテーブルの種類（：「xlDatabase」）、元データの範囲（：[データ]シートのA1セルからD11セルまで）を指定します。

そして、①で作成したPivotCacheオブジェクトを基に、②でピボットテーブルを作成しています。
引数にはピボットテーブルの作成位置（：[ピボットテーブル]シートのA3セル）、ピボットテーブル名（：「ピボット1」）を指定します。

ちなみに、作成したピボットキャッシュは使い回しができるので、同じ元データから複数のピボットテーブルを作成することができます。
その場合は、上記コードのようにピボットテーブルの作成までを一文で書かずに、ピボットキャッシュを変数に格納してコードを二段階に分けます。

 ' 変数を宣言
 Dim pvc As PivotCache

 ' ピボットキャッシュを作成して変数に格納
 Set pvc = ThisWorkbook.PivotCaches.Create(xlDatabase, Worksheets("データ").Range("A1:D11"))

 ' ピボットテーブルを作成
 pvc.CreatePivotTable Sheets("ピボットテーブル").Range("A3"), "ピボット1"

本題に戻って、次はピボットテーブルの各フィールドの設定です。

 ' フィールドを設定
 With ActiveSheet.PivotTables("ピボット1")
     .PivotFields("所属").Orientation = xlRowField  ・・・①
     .PivotFields("名前").Orientation = xlRowField  ・・・②
     .PivotFields("商品").Orientation = xlColumnField  ・・・③
     .PivotFields("数値").Orientation = xlDataField  ・・・④
 End With

ドを「値」にそれぞれOrientationプロパティで設定します。
もし同じフィールド（行・列・値のこと）に複数設定する場合は、Positionプロパティで任意の順番を指定できます。
省略するとコードに記述した順番で設定されます。

実際にコードを実行すると、

このようなピボットテーブルが作成できました。
では、次に行きます。

ピボットテーブルを更新する

続いてピボットテーブルの更新をしてみます。元のデータの情報が変わったとき、ピボットテーブルが勝手にその変更を適用してくれたりはしないので、更新処理を実行する必要があります。

元データについて、中村さんと加藤さんの所属を「チーム3」、数値をマイナスに変えてピボットテーブルを更新してみます。

ピボットテーブルを更新するコードは、

 ' ピボットテーブルの更新
 Sub UpdatePivot()
     Worksheets("ピボットテーブル").PivotTables("ピボット1").PivotCache.Refresh
 End Sub

もし、新しいデータを追加した場合は上のコードを実行してもピボットテーブルに情報は追加されません。
ピボットテーブルの元データの範囲を再指定する必要があります。

元データに以下を追加して確認してみます。

ピボットテーブルを更新するコードは、

 ' データ範囲が変わる場合の更新
 Sub UpdatePvt2()
     Worksheets("ピボットテーブル").PivotTables("ピボット1").ChangePivotCache _
     ThisWorkbook.PivotCaches.Create(xlDatabase, Worksheets("データ").Range("A1:D13"))
 End Sub

データを2行追加したため、範囲はA1:D13の指定となっています。

更新後のピボットテーブル

ピボットテーブルの更新もできました。
では、次に行きます。

書式設定してみる

書式設定といっても色々ありますが今回は、

・所属（チーム1,2,3）の背景色を変えて強調する。
・数値のデータを3桁ごとにカンマ区切りにする。
・条件付き書式で0より小さいデータの文字色を赤に設定する。

この3つをやってみようと思います。

まずは背景色の変更と、データのカンマ区切りから。

 Sub FormatSet_1()
     ' 背景色の変更 色：薄いオレンジ
     Worksheets("ピボットテーブル").PivotTables("ピボット1").PivotSelect "所属[All]", _
         xlDataAndLabel + xlFirstRow  ・・・①
     Selection.Interior.ColorIndex = 40

     ' データをカンマ区切りに
     With Worksheets("ピボットテーブル").PivotTables("ピボット1").DataBodyRange  ・・・②
         .NumberFormat = "#,#"  ・・・③
     End With
 End Sub

①で “所属[All]” を指定して、チーム1,2,3を選択します。このとき、引数(Mode)は「xlDataAndLabel + xlFirstRow」でラベルの一行目を選択しています。
続いて色を指定、ColorIndexプロパティの番号40は薄いオレンジ色になります。

数値データをカンマ区切りにする処理では、
②でデータ範囲を指定し、③で桁区切り（,）を使用した数値書式を設定します。（例：1,000）
②のDataBodyRangeはテーブルの見出しを除いたデータ全体を選択できますが、ピボットテーブルでもデータエリアだけを選択できるようです。

もちろん、①と同じようにPivotTableのSelectメソッドで範囲を指定してもいいと思います。

実際に処理を実行すると、

ちゃんと書式設定が適用されています。

続いて条件付き書式の設定になります。
「0」より小さいデータについて、文字色を「赤」にするという内容でやってみます。

コードは、

 Sub FormatSet_2()
     ' ピボットシートの条件付き書式を削除する
     Worksheets("ピボットテーブル").Cells.FormatConditions.Delete  ・・・①

     ' データエリアを選択
     Worksheets("ピボットテーブル").PivotTables("ピボット1").PivotSelect "", xlDataOnly  ・・・②

     ' 条件の設定：0より小さい場合
     Selection.FormatConditions.Add Type:=xlCellValue, _
         Operator:=xlLess, Formula1:="0"  ・・・③

     ' 文字を赤色に
     Selection.FormatConditions(1).Font.ColorIndex = 3  ・・・④
 End Sub

条件が設定できたら、④でその条件を満たす場合の書式を指定します。文字色を赤にしたいので、ColorIndexの番号は「3」になっています。
登録している条件が1つ目のため、FormatConditions()では「1」を指定しています。

処理を実行してみます。

条件付き書式も設定する事ができました。

これでピボットテーブルの作成・更新、書式設定ができました。今回はここまでになります。

最後に

ピボットテーブルの作成から書式設定まで見てきましたが、基本的な処理はこのあたりだと思います。あまりないかもしれないですが、ExcelVBAでピボットテーブル作成したりするとき、少しでも参考にしてもらえれば幸いです。

でも、今回書いたコードはシートの名前やデータの範囲などベタ書きしてるので、実際のツールに組み込むときには変数とかいろいろ使って書く必要がありますね。

私自身そういった部分が弱いので、少しずつ良いコードが書けるよう今後も勉強に励みたいと思います。

最後まで読んでいただきありがとうございました。それでは。