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



最終更新日:2013-05-11

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


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

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

マクロで扱う事もありますので、ピボットテーブルの基本くらいは知っておくべきでしょう。


今回は、文法解説は抜きにして、サンプルでの説明をします。

マクロ記録で作成したマクロの変更方法が主になります。



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


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


このまま使えば良いのですが、シート名やピボットテーブル名が固定になっていますので、

整理して、使いまわせるように変更します。

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


ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Sheet1!R1C1:R10C3", Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:="Sheet4!R3C1", TableName:="ピボットテーブル1", DefaultVersion _
    :=xlPivotTableVersion14

この部分がわかりづらいと思います。

PivotCachesの作成と、CreatePivotTableでのピボットテーブルの作成を同時に行っている為です。

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

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


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


こちらの方が理解しやすいかもしれません。

それでも、シート名やピボットテーブル名が固定になっていますので、整理してみると。

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


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "Sheet1!R1C1:R10C3").CreatePivotTable TableDestination:="", TableName:= _
    "ピボットテーブル1", DefaultVersion:=xlPivotTableVersion10

この部分がわかりづらいと思います。

PivotCachesの作成と、CreatePivotTableでのピボットテーブルの作成を同時に行っている為です。

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

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



結局、2010も2003も、ほぼ同じになりました。

大きく違いうのは、

PivotCaches.Create

PivotCaches.Add

この部分になります。

ですので、どちらでも動作するように、2003での記述を使う事をお勧めします。





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

第94回.コメント(Comment)
第95回.ハイパーリンク(Hyperlink)
第96回.グラフ(Chart)
第97回.図形(Shape)
第98回.Findメソッド(Find,FindNext,FindPrevious)
第99回.Replaceメソッド(置換)
第100回.InputBoxメソッド(インプットボックス)

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

SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)

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

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
7.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
8.ひらがな⇔カタカナの変換|エクセル基本操作
9.定数と型宣言文字(Const)|ExcelマクロVBA入門
10.CSVの読み込み方法|ExcelマクロVBAサンプル集



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

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


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

    ↑ PAGE TOP