ExcelマクロVBA入門
第93回.ピボットテーブル(PivotTable)

ExcelマクロVBAの基本と応用、エクセルVBAの初級・初心者向け解説
最終更新日:2019-07-12

第93回.ピボットテーブル(PivotTable)


マクロ VBA ピボットテーブル PivotTable

ピボットテーブルをマクロVBAで操作する事が良いかどうか、少々疑問な部分もあります。
しかし、ピボットテーブル(PivotTable)はエクセルでは必須機能になりますので、
少なくとも、ピボットテーブルの基本くらいは知っておくべきでしょう。


今回はピボットテーブル(PivotTable)のオブシェクトをざっと見てから、
その後は文法解説は少なめにして、マクロ記録で作成されたVBAをからスタートし、
実践的に使い回せるビボットテーブルのVBAコードを作成しつつ説明をしていきます。

ピボットテーブル(PivotTable)を構成するオブジェクト群

PivotCachesコレクション
PivotCacheオブジェクトのコレクション
PivotCacheオブジェクト
ピボットテーブル レポートのキャッシュ メモリを表します。
PivotTablesコレクション
PivotTableオブジェクトのコレクション
PivotTableオブジェクト
ワークシートにあるピボットテーブル レポートを表します。
PivotFieldsコレクション
PivotFieldオブジェクトのコレクション
PivotFieldオブジェクト
ピボットテーブル レポートのフィールドを表します。
これらのコレクション、オブジェクトに、それぞれのプロパティとメソッドがあります。
ピボットテーブル関連のプロパティ、メソッド一覧
ビボットテーブルを構成するオブジェクトには、以下のものがあります。PivotCachesコレクション…PivotCacheオブジェクトのコレクション PivotCacheオブジェクト…ピボットテーブルレポートのキャッシュメモリを表します。PivotTablesコレクション…PivotTableオブジェクトのコレクション PivotTableオブジェクト……
上の一覧をご覧いただければわかりますが、膨大な量のプロパティ、メソッドがあります。
そして、決してこれで全てではありません>
さらに下位のオブジェクは他にもたくさんあります。
これらを覚えることはほぼ無理ですし、覚えることに意味はありません。

プロパティの設定値やメソッドの引数について知りたい場合は、
マクロの記録で自動記録されたVBAコードを参考にするのが最も早く簡単でしょう。

以下では、実践的なサンプでの説明をしていきます。
実践のVBAコードを読むことで、理解を深めて下さい。

A1セルからC10セルにデータが入っている表があるとします。


マクロ VBA ピボットテーブル

この表を元に、マクロ記録を行ってみます。

Excel2010のピボットテーブル(PivotTable)のマクロ記録



Sub Macro1()
  Sheets.Add
  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Sheet1!R1C1:R10C3", Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:="Sheet4!R3C1", TableName:="ピボットテーブル1", DefaultVersion _
    :=xlPivotTableVersion14
  Sheets("Sheet4").Select
  With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("担当")
    .Orientation = xlRowField
    .Position = 1
  End With
  With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("分類")
    .Orientation = xlColumnField
    .Position = 1
  End With
  ActiveSheet.PivotTables("ピボットテーブル1").AddDataField ActiveSheet.PivotTables( _
    "ピボットテーブル1").PivotFields("金額"), "合計 / 金額", xlSum
End Sub

このまま使えなくはありませんが、
シート名やピボットテーブル名が固定になっていますので、
整理して、使いまわせるように変更します。

いきなり非常に長い一文から始まっています。
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Sheet1!R1C1:R10C3", Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:="Sheet4!R3C1", TableName:="ピボットテーブル1", DefaultVersion _
    :=xlPivotTableVersion14
この部分がわかりづらいと思います。
PivotCachesの作成と、CreatePivotTableでのピボットテーブルの作成を同時に行っている為です。
そこで、下のマクロでは、これを2段に分けて、
それぞれのオブジェクト変数に入れて使っています。

Sub Macro1()
  Dim ws As Worksheet
  Dim pvc As PivotCache
  Dim pvt As PivotTable
  Set ws = Sheets.Add
  Set pvc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                        SourceData:="Sheet1!R1C1:R10C3", _
                        Version:=xlPivotTableVersion14)
  Set pvt = pvc.CreatePivotTable(TableDestination:=ws.Name & "!R3C1", _
                        TableName:="ピボットテーブル1", _
                        DefaultVersion:=xlPivotTableVersion14)
  With pvt
    With .PivotFields("担当")
      .Orientation = xlRowField
      .Position = 1
    End With
    With .PivotFields("分類")
      .Orientation = xlColumnField
      .Position = 1
    End With
    .AddDataField .PivotFields("金額"), "合計 / 金額", xlSum
  End With
End Sub

だいぶ理解しやすいのではないかと思います。

Set pvc = ActiveWorkbook.PivotCaches.Create(
ブックにピボットキャッシュを作成しています。

Set pvt = pvc.CreatePivotTable(
ピボットキャッシュにピボットテーブルを作成しています。

Excel2003のピボットテーブル(PivotTable)のマクロ記録



Sub Macro2()
  Range("A1").Select
  ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "Sheet1!R1C1:R10C3").CreatePivotTable TableDestination:="", TableName:= _
    "ピボットテーブル1", DefaultVersion:=xlPivotTableVersion10
  ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
  With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("担当")
    .Orientation = xlRowField
    .Position = 1
  End With
  With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("分類")
    .Orientation = xlColumnField
    .Position = 1
  End With
  ActiveSheet.PivotTables("ピボットテーブル1").AddDataField ActiveSheet.PivotTables( _
    "ピボットテーブル1").PivotFields("金額"), "合計 / 金額", xlSum
End Sub

こちらの方が2010よりは理解しやすいかもしれません。
このまま使えなくはありませんが、
シート名やピボットテーブル名が固定になっていますので、
整理して、使いまわせるように変更します。

Excel2010のマクロ記録同様に、長い一文から始まっています。
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "Sheet1!R1C1:R10C3").CreatePivotTable TableDestination:="", TableName:= _
    "ピボットテーブル1", DefaultVersion:=xlPivotTableVersion10
この部分がわかりづらいと思います。
PivotCachesの作成と、CreatePivotTableでのピボットテーブルの作成を同時に行っている為です。

そこで、下のマクロでは、これを2段に分けて、
それぞれのオブジェクト変数に入れて使っています。

Sub Macro2()
  Dim ws As Worksheet
  Dim pvc As PivotCache
  Dim pvt As PivotTable
  Set ws = Sheets.Add
  Set pvc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
                      SourceData:="Sheet1!R1C1:R10C3")
  Set pvt = pvc.CreatePivotTable(TableDestination:=ws.Name & "!R3C1", _
                      TableName:="ピボットテーブル1", _
                      DefaultVersion:=xlPivotTableVersion10)
  With pvt
    With .PivotFields("担当")
      .Orientation = xlRowField
      .Position = 1
    End With
    With .PivotFields("分類")
      .Orientation = xlColumnField
      .Position = 1
    End With
    .AddDataField .PivotFields("金額"), "合計 / 金額", xlSum
  End With
End Sub

だいぶ理解しやすいのではないかと思います。

Set pvc = ActiveWorkbook.PivotCaches.Add(
ブックにピボットキャッシュを作成しています。

Set pvt = pvc.CreatePivotTable(
ピボットキャッシュにピボットテーブルを作成しています。

Excel2010とExcel2003のピボットテーブルVBAの違いについて

上記のぃさそいり2010とExcel2003のマクロ記録から作成したVBAサンプルコードは、
結局、2010も2003もほぼ同じになりました。
大きく違いうのは、
PivotCaches.Create

PivotCaches.Add
この部分と、
DefaultVersion:=xlPivotTableVersion14

DefaultVersion:=xlPivotTableVersion10

PivotCachesのAddメソッドは、Excel2010では非表示メンバーとなっています。
非表示メンバーとなっても、Addメソッドは2010でも使用可能となっています。

Excel2003がまだ多く使われていたころであれば、2003の記述をお勧めするところですが、
さすがにもう2003は無くなってきましたので、2010の記述を使えば良いでしょう。

ピボットテーブル(PivotTable)のマクロVBA実践例

VBAでピボットテーブルを扱う時は、事前にピボットテーブルを作成しておき、
必要に応じて、その部分の処理のみVBAで行うようにすることをお勧めします。

ピボットのデータソースの変更

With ActiveSheet
  .PivotTables("ピボット1").ChangePivotCache ActiveWorkbook.PivotCaches. _
    Create(SourceType:=xlDatabase, SourceData:=.Range("A1").CurrentRegion)
End With

ピボットの更新

ActiveSheet.PivotTables("ピボット1").PivotCache.Refresh



同じテーマ「マクロVBA入門」の記事

第90回.フィルタオプションの設定(AdvancedFilter)
第91回.条件付き書式(FormatCondition)
第92回.名前定義(Names)
第93回.ピボットテーブル(PivotTable)
第94回.コメント(Comment)
第95回.ハイパーリンク(Hyperlink)
第96回.グラフ(Chart)
第97回.図形オートシェイプ(Shape)
第98回.Findメソッド(Find,FindNext,FindPrevious)
第99回.Replaceメソッド(置換)
第100回.InputBoxメソッド(インプットボックス)


新着記事NEW ・・・新着記事一覧を見る

VBAにおける変数のメモリアドレスについて|VBA技術解説(11月8日)
空文字列の扱い方と処理速度について(""とvbNullString)|VBA技術解説(1月7日)
Errオブジェクトとユーザー定義エラー|VBA入門(11月5日)
シングルクォートの削除とコピー(PrefixCharacter)|VBA技術解説(11月4日)
ユーザー定義型の制限とクラスとの使い分け|VBA技術解説(11月3日)
クリップボードに2次元配列を作成してシートに貼り付ける|VBA技術解説(11月1日)
VBAクラスを使ったイベント作成(Event,RaiseEvent,WithEvents)|VBA技術解説(10月31日)
VBAクラスのAttributeについて(既定メンバーとFor Each)|VBA技術解説(10月19日)
VBAの用語について:ステートメントとは|VBA技術解説(10月16日)
VBAのマルチステートメント(複数のステートメントを同じ行に)|VBA技術解説(10月14日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数宣言のDimとデータ型|ExcelマクロVBA入門
6.繰り返し処理(For Next)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.ひらがな⇔カタカナの変換|エクセル基本操作
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.セルに文字を入れるとは(Range,Value)|VBA入門



  • >
  • >
  • >
  • ピボットテーブル(PivotTable)

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。




    このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
    本文下部へ