VBAでピボットテーブルを作成する.
たまに「VBAでピボットテーブルを作成したい」と思うことがあるのですが,その方法をしょっちゅう忘れるので,会社でも見れる・コピペできるようにここにメモしておきましょう――あまり発生しない案件なのですが.
なお,これは他のプロシージャからの呼び出しを前提としたコードであり,引数wsSrc
で指定したシートをもとに,新規シートにピボットテーブルを作成するものとします.
Private Sub Create_PivotTable(ByRef wsSrc As Excel.Worksheet) 'PivotCache オブジェクトは Workbook オブジェクトに属するので,まずそれを取得する. Dim wb As Excel.Workbook: Set wb = wsSrc.Parent 'ここで追加したワークシートにピボットテーブルを作成する. Dim wsPvt As Excel.Worksheet: Set wsPvt = wb.Worksheets.Add(Before:=wsSrc) 'まず第一に PivotCache を生成する. Dim pvc As Excel.PivotCache '今回は元となるデータとして「1行目がヘッダの単純な表形式」を想定しているのだが, 'その場合はこのような単純な指定でも充分事足りる. Set pvc = wb.PivotCaches.Create(SourceType:=xlDatabase _ , SourceData:=wsSrc.UsedRange) '続いて PivotCache オブジェクトをもとに PivotTable オブジェクトを生成する. Dim pvt As Excel.PivotTable '引数 TableDestination は「ピボットテーブルの左上端」の位置を示す. 'これは「フィルター」の位置ではないことに一応注意. Set pvt = pvc.CreatePivotTable(TableDestination:=wsPvt.Cells(3, 1) _ , TableName:="pvtCustomerType") With pvt '値フィールドに配置したいフィールドと(第1引数),それにつける見出し(第2引数),およびその計算方法を指定する(第3引数). 'よく使うのは,xlCount(個数)・xlSum(合計値)あたりかな. Call .AddDataField(.PivotFields("来客人数"), "来店組数", XlConsolidationFunction.xlCount) Call .AddDataField(.PivotFields("来客人数"), "来店人数", XlConsolidationFunction.xlSum) With .PivotFields("店名") .Orientation = xlPageField '「フィルタ」. .Position = 1 'この値は TRUE にしておいた方がよい.既定値は FALSE だが,それでは複数選択ができないので. .EnableMultiplePageItems = True End With '列についての設定. With .PivotFields("日付") .Orientation = xlColumnField '「列」 .Position = 1 '‥の1番目 'このプロパティについては,「データがないことを示したい」場合, 'こと日付等の場合は TRUE に設定しておいた方がよいと思う. .ShowAllItems = True End With 'これを必要なだけ繰り返す. '行についての設定. With .PivotFields("来客区分") .Orientation = xlRowField '「行」 .Position = 1 '‥の,1番目. .ShowAllItems = True End With With .DataPivotField .Orientation = xlRowField '「行」 .Position = 2 '‥の,2番目. End With 'これを必要なだけ繰り返す. 'フィールドに複数のフィルタを適用できるかを指定する値. '既定値は FALSE だが,TRUE に設定しておいた方がよい. .AllowMultipleFilters = True '以下はレイアウトに係る設定なので,ヘルプ参照しつつ適宜自分の好きなように設定を行う. Call .RowAxisLayout(xlCompactRow) .TableStyle2 = "PivotStyleDark9" 'テーマは好みで選ぶ. .RowGrand = True '行方向の集計を行うか? .ColumnGrand = False '列方向の集計を行うか? .CompactLayoutRowHeader = "来客区分" .CompactLayoutColumnHeader = "日付" End With End Sub
- 対象となるシートとブックを特定する.
- そこに,ピボットテーブル用のシートを作成する.
- 対象となるシートから,PivotCache オブジェクトを生成する
- これをもとに PivotTable オブジェクトを生成する.
- 値フィールドを追加する.
- 列・行のレイアウトを行う.
- 見た目の調整を行う.
- 細部設定を行う.
という流れになると思います.
コード自体はちょっと長い,かつ,あまり使う機会がないのですが,慣れれば単純ですし,意外と役に立つ機会があるかもしれません.
ちなみに私は,「AccessのデータをExcelに出力し,そこからピボットテーブルを作成する」というパターンを多用しています.
「Accessは無理だが,Excelは使える」という人は多いですからね.