VBAを使って1クリックでピボットテーブルを作成する

2021年12月4日

ピボットテーブルは、大量のデータをもとにさまざまな集計を行ったり、分析したりできる機能です。
例えば、「顧客や支店、さらには担当や商品ごとの集計といった、さまざまな視点から集計したり、分析したりすることができます。
今回はVBAを使ってボタンをクリックするだけでピボットテーブルを作成する方法をご紹介します。

ピボットテーブル作成の流れ

１．ピボットテーブルのキャッシュを作成
「PivotCaches」コレクションの「Create」メソッドでピボットテーブルの元となるデータをメモリ内に格納します。

２．ピボットテーブルの作成
「PivotCache」オブジェクトの「CreatePivotTable」メソッドで空白のピボットテーブルを作成します。

３．ピボットテーブルにフィールドを追加する
「PivotField」オブジェクトで行フィールド、列フィールド、値フィールドを追加してピボットテーブルを完成させます。

実際の利用方法は以下の通りです。

   'ピボットテーブルキャッシュの作成 → ピボットテーブル作成
     ThisWorkbook.PivotCaches.Create(xlDatabase, Worksheets("Sheet1").Range("B4:G16")) _
     .CreatePivotTable Sheets("Sheet1").Range("C20"), "ピボット1"


   'フィールドを設定
     With ActiveSheet.PivotTables("ピボット1")
     
        '会社名フィールド(行フィールド)の設定
         .PivotFields("会社名").Orientation = xlRowField
           
        '金額フィールドの設定
         With .PivotFields("金額")
              .Orientation = xlDataField '元データの種類をExcelの表に指定
         End With

     End With

サンプルコードの処理内容

「ピポットテーブル作成」ボタンをクリックすると会社ごとの集計金額の表が作成されます。
データ表の数字を変えて「ピポットテーブル更新」 ボタンをクリックすると会社ごとの集計金額が再集計されます。

サンプルコード　　 コードの作成手順はこちら

「ピポットテーブル作成」ボタン と 「ピポットテーブル更新」 ボタン のプログラムコードは以下の通りです。「 .NumberFormat = “#,##0" 」で集計金額を3桁区切りに指定しています。

Option Explicit

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

   '既存のピポットテーブル(B20～C30)を削除
    Range("C20:D30").Delete shift:=xlShiftUp

   'ピボットキャッシュ作成 → ピボットテーブル作成
     ThisWorkbook.PivotCaches.Create(xlDatabase, Worksheets("Sheet1").Range("B4:G16")) _
     .CreatePivotTable Sheets("Sheet1").Range("C20"), "ピボット1"

   'フィールドを設定
     With ActiveSheet.PivotTables("ピボット1")
     
        '列幅を自動調整しないようにする
         .HasAutoFormat = False
                  
        '会社名フィールドの設定
         .PivotFields("会社名").Orientation = xlRowField  '行フィールドを設定
           
        '金額フィールドの設定
         With .PivotFields("金額")
              .Orientation = xlDataField
              .NumberFormat = "#,##0"    '３桁区切りを指定
         End With


     End With

 End Sub
 


----------------------------------------------------------------------------------------------
 
 
'ピボットテーブルの更新

 Sub UpdatePivot()
     Worksheets("Sheet1").PivotTables("ピボット1").PivotCache.Refresh
 End Sub