ExcelマクロVBA入門 | 第93回.ピボットテーブル(PivotTable) | Excelマクロの基礎と応用、エクセルVBAの入門・初級・初心者向け解説



最終更新日:2018-02-10

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


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

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

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



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

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

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

ピボットテーブル関連のプロパティ、メソッド一覧

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

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

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



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




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


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入門」の記事

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

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

VBAを定型文で覚えよう|ExcelマクロVBA技術解説(3月26日)
VBAスタンダード試験対策まとめ|MOS VBAエキスパート対策(3月16日)
ユーザーフォームとメニューの操作|MOS VBAエキスパート対策(3月14日)
ファイルの操作|MOS VBAエキスパート対策(3月14日)
ユーザーフォームの各種イベント|Excelユーザーフォーム(3月13日)
レジストリの操作|MOS VBAエキスパート対策(3月12日)
変数と配列|MOS VBAエキスパート対策(3月12日)
Colorプロパティの設定値一覧|VBA技術解説(3月12日)
APIとOLEオートメーション|MOS VBAエキスパート対策(3月11日)
エラーへの対処|MOS VBAエキスパート対策(3月10日)

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

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.マクロって何?VBAって何?|ExcelマクロVBA入門
8.定数と型宣言文字(Const)|ExcelマクロVBA入門
9.とにかく書いて見よう(Sub,End Sub)|VBA入門
10.繰り返し処理(For Next)|ExcelマクロVBA入門



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

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


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





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

    本文下部へ

    ↑ PAGE TOP