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メソッド(インプットボックス)

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

スプレッドシートが非常に遅い、高速化するには|Google Apps Script入門(1月17日)
画像のトリミング(PictureFormat,Crop)|ExcelマクロVBAサンプル集(12月27日)
シート保護|Google Apps Script入門(12月24日)
表示の固定|Google Apps Script入門(12月24日)
グラフ|Google Apps Script入門(12月21日)
入力規則|Google Apps Script入門(12月13日)
並べ替え|Google Apps Script入門(12月12日)
メモの挿入・削除と改行文字|Google Apps Script入門(12月6日)
リンクの挿入・編集・削除|Google Apps Script入門(12月6日)
セルに数式を入れる|Google Apps Script入門(12月1日)

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

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



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

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


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

    ↑ PAGE TOP