【Excel VBA】ピボットテーブルを作成するマクロ

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

ピボットテーブルを簡単に作りたい!という要望があったので、VBAでピボットテーブルを作成するマクロを作りました。

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

1. Module1を追加する

標準モジュールにModule1を追加します。

【VBAProjectを右クリック】→ 【挿入】 → 【標準モジュール】をクリックします。

そもそもこの画面なに?という方は、以下の記事でVBAを書き始める準備の方法をまとめているのでご覧ください。

Excel VBA マクロの作り方 【Excel VBA】Excelマクロの始め方 ~簡単なサンプル付き~

2. 準備

Excelの1シート目に、ピボットテーブルを作るためのデータを設定します。ソースコードをそのまま使用する場合には、B2, C2, D2は以下と同じ文字にしておく必要があります。

下の表をコピーして、B2セルに貼り付ければ、上の図と同じシートが作れます。

サンプルデータ

 

日付カテゴリ代金
2020/1/3食費1,200
2020/1/193,000
2020/1/28日用品500
2020/2/19食費2,600
2020/2/25交通費900
2020/3/9日用品1,000

3. VBAのソースコードを書く

Module1を追加したら、以下のソースコードをコピぺしましょう。処理内容はソースのコメントに書いてあります。

ソースコードのポイント
  • ①でピボットテーブルに使用するデータを選択します
  • ②でピボットテーブルを表示する場所を設定します
  • ③では①で選択したデータのヘッダーの文字を使用します
    • ③(a)でピボットテーブルの列を設定します
    • ③(b)でピボットテーブルの行を設定します
    • ③(c)でピボットテーブルに表示する値を設定します
たけのこ

【2.準備】で用意したデータを変えるときには、ソースコードも同じように変更してください!例えば、【2.準備】の”日付”を”日時”に変えるときには、ソースコード内の”日付”も”日時”に変えてください。

copyを押してソース全体をコピーします。

copy

' ピボットテーブルを作成するマクロ
Sub CreatePivotTable()

    Dim pc As PivotCache
    Dim pt As PivotTable
    
    ' ①ピボットテーブルに使うデータを設定
    Set pc = ActiveWorkbook.PivotCaches.Create( _
                                SourceType:=xlDatabase, _
                                SourceData:=Worksheets(1).Range("B2").CurrentRegion)
    
    ' ピボットテーブル用の新規シートを作成
    Call DeleteSheet("ピボットテーブル")
    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "ピボットテーブル"
    
    ' ②ピボットテーブルを作成する
    Set pt = pc.CreatePivotTable( _
                                TableDestination:=Worksheets("ピボットテーブル").Range("B2"), _
                                TableName:="ピボットテーブル1")
    
    ' ③ピボットテーブルの詳細を設定する
    With pt
    
        ' (a)列ラベルに設定するデータ
        .PivotFields("カテゴリ").Orientation = xlColumnField
        
        ' (b)行ラベルに設定するデータ
        .PivotFields("日付").Orientation = xlRowField
        .PivotFields("日付").AutoGroup
        
        ' (c)表に設定するデータ
        .AddDataField .PivotFields("代金"), "家計簿", xlSum
    
    End With
    
End Sub

' シートを削除する
Sub DeleteSheet(ByVal sheetName As String)

    Dim ws As Worksheet
    For Each ws In Worksheets
    
        If ws.Name = sheetName Then
            
            ' "削除しますか?"の確認メッセージを非表示にする
            Application.DisplayAlerts = False
            
            ' "一覧"シートを削除する
            Worksheets(sheetName).Delete
            
            ' メッセージを表示する設定に戻す
            Application.DisplayAlerts = True
            
            ' 削除したら処理を終了する
            Exit Sub
            
        End If
    
    Next ws

End Sub

Module1にソースコードをコピーしたら、以下のようになります。(少し長くなっています)

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

4. マクロを実行する

画面上部の【▶】を押して、マクロを実行します。

以下のようなウィンドウが表示されたら、CreatePivotTableを選択し、実行ボタンを押します。

2シート目(正しくはシートの最後)に、新しくシートが追加されて、ピボットテーブルが作られていることを確認します。

これで完了です!

 

↓ VBAを学びたい人におすすめの書籍です!