Excelで集計作業をするとき、ピボットテーブルは非常に便利ですが、
- 毎回ピボットを作り直すのが面倒
- 元データを更新するたびに手動で更新している
- 同じ形式の集計を何度も作っている
といった悩みを感じたことはありませんか？
そこで今回は、ExcelVBAでピボットテーブルを自動作成し、さらに更新まで行うマクロをご紹介します。
この記事でできること
- 元データからピボットテーブルを自動作成
- 行・列・集計項目をVBAで指定
- 既存ピボットがあれば削除して作り直し
- 元データ更新後、ワンクリックで再集計
想定するデータ構成
元データシート（シート名：元データ）
下記の通りデータを入力してください。
シート名を「元データ」に変更してください。
ピボット出力シート（シート名：ピボット）
部署別の売上合計を集計するピボットテーブルを作成します。
ピボットテーブルを自動作成・更新するマクロ（コピペOK）
以下のコードを標準モジュールに貼り付けてください。
Option Explicit
Sub CreateAndUpdatePivot()
Dim wsSrc As Worksheet
Dim wsPvt As Worksheet
Dim lastRow As Long
Dim lastCol As Long
Dim srcRange As Range
Dim pvtCache As PivotCache
Dim pvtTable As PivotTable
' ===== 設定 =====
Set wsSrc = Worksheets("元データ")
Const PIVOT_SHEET_NAME As String = "ピボット"
Const PIVOT_TABLE_NAME As String = "売上集計ピボット"
' =================
' ピボット出力シートを作り直す
On Error Resume Next
Application.DisplayAlerts = False
Worksheets(PIVOT_SHEET_NAME).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set wsPvt = Worksheets.Add
wsPvt.Name = PIVOT_SHEET_NAME
' 元データの範囲を取得
lastRow = wsSrc.Cells(wsSrc.Rows.Count, 1).End(xlUp).Row
lastCol = wsSrc.Cells(1, wsSrc.Columns.Count).End(xlToLeft).Column
Set srcRange = wsSrc.Range(wsSrc.Cells(1, 1), wsSrc.Cells(lastRow, lastCol))
' ピボットキャッシュ作成
Set pvtCache = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=srcRange)
' ピボットテーブル作成
Set pvtTable = pvtCache.CreatePivotTable( _
TableDestination:=wsPvt.Cells(1, 1), _
TableName:=PIVOT_TABLE_NAME)
' 行フィールド（部署）
With pvtTable.PivotFields("部署")
.Orientation = xlRowField
.Position = 1
End With
' 集計フィールド（売上金額：合計）
pvtTable.AddDataField _
pvtTable.PivotFields("売上金額"), _
"売上合計", _
xlSum
' 見た目を少し整える
pvtTable.ShowTableStyleRowStripes = True
pvtTable.TableStyle2 = "PivotStyleMedium9"
MsgBox "ピボットテーブルを作成しました。", vbInformation
End Sub
コード解説（初心者向け）
① 元データ範囲を自動で取得
Set srcRange = wsSrc.Range(wsSrc.Cells(1, 1), wsSrc.Cells(lastRow, lastCol))行数・列数が増減しても対応できるよう、
最終行・最終列を自動判定しています。
② PivotCache → PivotTable の順で作成
ピボットテーブルは、
- 元データを元に PivotCache を作成
- そこから PivotTable を作成
という2段階構成になっています。
③ 行・集計項目をコードで指定
pvtTable.AddDataField pvtTable.PivotFields("売上金額"), "売上合計", xlSumこの指定で、 「売上金額の合計」を集計項目として設定しています。
ピボットテーブルを更新するだけのマクロ
元データが変わったあと、
更新だけしたい場合はこちらが便利です。
Sub RefreshAllPivots()
Dim pvt As PivotTable
For Each pvt In ActiveWorkbook.PivotTables
pvt.RefreshTable
Next pvt
MsgBox "すべてのピボットテーブルを更新しました。", vbInformation
End Sub
複数のピボットがあるブックでも、 まとめて更新できます。
応用例
① 列フィールドを追加したい場合
With pvtTable.PivotFields("日付")
.Orientation = xlColumnField
.Position = 1
End With
② フィルター（ページフィールド）を設定
With pvtTable.PivotFields("担当者")
.Orientation = xlPageField
End With
③ 平均・件数で集計
xlAverage '平均
xlCount '件数
実行手順
- 元データシートを用意（1行目は見出し）
- Alt + F11 → 標準モジュールを追加
- マクロを貼り付け
- CreateAndUpdatePivot を実行
まとめ：ピボットはVBAで「作る＋更新」が最強
ピボットテーブルは非常に強力ですが、 手作業だと「作る・更新する」だけでも意外と時間がかかります。
- 毎回同じ集計をしている
- 定型レポートを作っている
- 月次・週次の集計作業がある
こうした場合は、VBAで自動化するのがベストです。
ぜひ、あなたのExcel業務に取り入れてみてください。
コメント