Home » エクセルマクロ・Excel VBAの使い方 » ピボットテーブル » ピボットテーブルをマクロで作成する

ピボットテーブルをマクロで作成する

対象：Excel2003, Excel2007, Excel2010

Word文書に含まれる単語と、ページ番号＆行番号の一覧をExcelに出力する、Wordマクロをご紹介しました。

この一覧を元に、それぞれの単語がいくつ存在するのかをカウントするために、ピボットテーブルを作成します。

このピボットテーブルを作成する処理も、Wordマクロに組み込むことができますが、まずは、ピボットテーブルを作成するExcelマクロをご紹介しておきます。

［スポンサードリンク］

ピボットテーブルを作成するサンプルマクロ

ピボットテーブルの作成をマクロ記録してみると、たくさんのコードを吐き出してくれますが、整理するとかなりシンプルなコードでピボットテーブルを作成できます。

Sub ピボットテーブルを作成する()

　Dim pvt As PivotTable
　Dim src As Range　' ピボットテーブルの元データ

　' 元データを変数に格納
　Set src = ActiveSheet.Range("A1").CurrentRegion

　' ピボットテーブルを作成するシートを作成
　Sheets.Add

　' ピボットテーブルの作成
　Set pvt = _
　　ActiveWorkbook.PivotCaches.Add( _
　　　　SourceType:=xlDatabase, _
　　　　SourceData:=src). _
　　　　　　CreatePivotTable(TableDestination:=Range("A3"))

　' フィールドの配置
　With pvt
　　.PivotFields("語句").Orientation = xlRowField
　　.PivotFields("ページ・行").Orientation = xlDataField
　End With

End Sub

先日ご紹介したWordマクロで出力された、「語句」「ページ・行」というフィールドのあるワークシートがアクティブな状態で、上記のExcelマクロを実行すると、「語句」がいくあるのかをカウントするピボットテーブルができます。

サンプルマクロの解説

まず、元データを変数に格納しておいてから
　Set src = ActiveSheet.Range("A1").CurrentRegion

ピボットテーブルを作成するシートを挿入して
　Sheets.Add

ピボットテーブルを作成します。
　Set pvt = _
　　ActiveWorkbook.PivotCaches.Add( _
　　　　SourceType:=xlDatabase, _
　　　　SourceData:=src). _
　　　　　　CreatePivotTable(TableDestination:=Range("A3"))

このピボットテーブルを作成する部分は、引数がたくさん指定されているので複雑に見えるかもしれませんが、引数を取り払った状態で眺めてみると、何をやっているのかわかりやすくなるはずです。

引数を取り払うと
「ActiveWorkbook.PivotCaches.Add.CreatePivotTable」
といったオブジェクト式となります。

ActiveWorkbookの、PivotCachesコレクションに、Addして、そこからCreatePivotTableしているのです。

ピボットテーブルをVBA（Visual Basic for Applications）で作成する場合、まずPivotCacheオブジェクトを作成して、そこからCreatePivotTableするという手順が必要です。

PivotCacheオブジェクトを作成する、ActiveWorkbook.PivotCachesのAddメソッドの引数が、
　SourceType:=xlDatabase
　SourceData:=rngData
です。

「SourceType」「SourceData」という引数の名前から、何を指定しているのかはわかるでしょう。

上記のマクロでは、SourceDataを指定しやすくするために、事前にオブジェクト変数・srcに元データを格納しています。

ActiveWorkbook.PivotCachesのAddメソッドで、PivotCacheオブジェクトが作られたので、そのPivotCacheオブジェクトに対して、CreatePivotTableメソッドを実行しています。

CreatePivotTableメソッドの引数が
「TableDestination:=Range("A3")」
です。

これも「TableDestination」という引数名から、何を指示しているのかわかるでしょう。

ピボットテーブルを作成する直前に
　Sheets.Add
していますから、
　TableDestination:=Range("A3")
は、その新規に作成されたシートのA3セルを意味しています。

ここでは、マクロ記録でできるコードを解読する意味もあり、

　Set pvt = _

　　ActiveWorkbook.PivotCaches.Add( _

　　　　SourceType:=xlDatabase, _

　　　　SourceData:=src). _

　　　　　　CreatePivotTable(TableDestination:=Range("A3"))

と、PivotCacheオブジェクトの作成とPivotTableオブジェクトの作成を1行で行っていますが、一旦意味がわかったら、以下のように分割するほうが可読性の面からいいでしょう。

　Dim pvc As PivotCache

　Dim pvt As PivotTable

　Set pvc = ActiveWorkbook.PivotCaches.Add( _

　　SourceType:=xlDatabase, _

　　SourceData:=src)

　Set pvt = pvc.CreatePivotTable( _

　　TableDestination:=ActiveSheet.Range("A3"))

ピボットテーブルができたあとに、フィールドを配置しています。
　With pvt
　　.PivotFields("語句").Orientation = xlRowField
　　.PivotFields("ページ・行").Orientation = xlDataField
　End With

「xlRowField」「xlDataField」という定数から、何を指定しているかわかるはずです。

データエリアに配置した「ページ・行」フィールドは、「p.1-1」というフォーマットの文字列なので、特に指示しなくても個数をカウントしてくれます。

このExcelマクロを、Wordマクロに組み込んでやれば、Word文書に含まれる単語とページ番号＆行番号の一覧をExcelに出力して、単語の個数をカウントするピボットテーブルを作成するところまで、一気に行うことができます。~~その具体的コードは別記事で近日中にご紹介します。~~。単語一覧とピボットテーブルを作成するWordマクロを公開しました（2012-8-27）。

［スポンサードリンク］