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

Excelマクロの基礎と応用、エクセルVBAの入門・初級・初心者向け解説
最終更新日:2018-02-10

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


ピボットテーブルをマクロVBAで操作する事が良いかどうか、かなり疑問があります。


しかし、ピボットテーブルはエクセルでは必須機能になりますので、

少なくとも、ピボットテーブルの基本くらいは知っておくべきでしょう。


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

PivotCachesコレクション ・・・ PivotCacheオブジェクトのコレクション
PivotCacheオブジェクト ・・・ ピボットテーブル レポートのキャッシュ メモリを表します。
PivotTablesコレクション ・・・ PivotTableオブジェクトのコレクション
PivotTableオブジェクト ・・・ ワークシートにあるピボットテーブル レポートを表します。
PivotFieldsコレクション ・・・ PivotFieldオブジェクトのコレクション
PivotFieldオブジェクト ・・・ ピボットテーブル レポートのフィールドを表します。

これらのコレクション、オブジェクトに、それぞれのプロパティとメソッドがあります。

ピボットテーブル関連のプロパティ、メソッド一覧
ビボットテーブルを構成するオブジェクトには、以下のものがあります。PivotCachesコレクション・・・PivotCacheオブジェクトのコレクション PivotCacheオブジェクト・・・ピボットテーブルレポートのキャッシュメモリを表します。

上の一覧をご覧いただければわかりますが、膨大な量のプロパティ、メソッドがあります。
そして、決してこれで全てではありません、下位のオブジェクは他にもたくさんあります。
これらを覚えることは無理ですし、覚えることに意味はありません。

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

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



今回は、文法解説は抜きにして、
マクロ記録で作成したマクロからスタートし、
実践的に使い回せるビボットのVBAコードを作成しつつ説明をします。


Excel解説

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


Excel2010

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

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よりは理解しやすいかもしれません。

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

また、

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も2003もほぼ同じになりました。
大きく違いうのは、

PivotCaches.Create

PivotCaches.Add

この部分と、

DefaultVersion:=xlPivotTableVersion14

DefaultVersion:=xlPivotTableVersion10

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

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



実践例

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

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



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


ピボットの更新

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




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

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


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

IfステートメントとIIF関数とMax関数の速度比較|VBA技術解説(6月23日)
Withステートメントの実行速度と注意点|VBA技術解説(6月6日)
VBA+SeleniumBasicで検索順位チェッカー(改)|VBA技術解説(6月2日)
マクロでShift_JIS文字コードか判定する|VBA技術解説(6月1日)
Shift_JISのテキストファイルをUTF-8に一括変換|VBAサンプル集(5月31日)
「VBAによる解析シリーズその2 カッコ」をやってみた|エクセル(5月21日)
VBA+SeleniumBasicで検索順位チェッカー作成|VBA技術解説(5月18日)
テーブル操作のVBAコード(ListObject)|VBA入門(5月12日)
テーブル操作の概要(ListObject)|VBA入門(5月12日)
VBAのスクレイピングを簡単楽にしてくれるSelenium|VBA技術解説(5月6日)


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

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


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

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


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






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