office.notes@misora05

本館ブログのうち,Office・Windwsがらみのネタ+@を抜き出したもの.月水金更新予定.

VBAでピボットテーブルを作成する.

たまに「VBAでピボットテーブルを作成したい」と思うことがあるのですが,その方法をしょっちゅう忘れるので,会社でも見れる・コピペできるようにここにメモしておきましょう――あまり発生しない案件なのですが.

なお,これは他のプロシージャからの呼び出しを前提としたコードであり,引数wsSrcで指定したシートをもとに,新規シートにピボットテーブルを作成するものとします.

Microsoft Public Affiliate Program (JP)(マイクロソフトアフィリエイトプログラム)

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

Microsoft Store (マイクロソフトストア) 基本的には

  1. 対象となるシートとブックを特定する.
  2. そこに,ピボットテーブル用のシートを作成する.
  3. 対象となるシートから,PivotCache オブジェクトを生成する
  4. これをもとに PivotTable オブジェクトを生成する.
  5. 値フィールドを追加する.
  6. 列・行のレイアウトを行う.
  7. 見た目の調整を行う.
  8. 細部設定を行う.

という流れになると思います.

コード自体はちょっと長い,かつ,あまり使う機会がないのですが,慣れれば単純ですし,意外と役に立つ機会があるかもしれません.

ちなみに私は,「AccessのデータをExcelに出力し,そこからピボットテーブルを作成する」というパターンを多用しています.
Accessは無理だが,Excelは使える」という人は多いですからね.