この記事でわかること
- VBAでピボットテーブルを自動作成できる（PivotCaches.Create / CreatePivotTable）
- フィールド配置（行・列・値・フィルタ）や集計方法をVBAで制御できる
- 月次データから部署別売上ピボットテーブルとグラフを自動生成できる
対象: Excel 2016以降 / Microsoft 365、Windows 10/11
—
どんな場面で使う？
- 毎月届く売上データから部署別・商品別のクロス集計を自動で作りたい
- ピボットテーブルのフィールド配置を毎回手作業でドラッグするのが面倒なとき
- データ更新のたびにピボットテーブルとグラフを再作成して報告書に使いたい
- 集計軸や集計方法（合計・平均・個数）を切り替えた複数パターンを一度に作りたい
—
完成イメージ（Before / After）
Before（手作業でピボットテーブルを作成）:
| 操作 | 所要時間 |
|---|---|
| データ範囲を選択してピボット挿入 | 約1分 |
| 行・列・値・フィルタのフィールド配置 | 約2分 |
| 書式設定・グラフ作成 | 約5分 |
| 毎月繰り返し | 毎回約8分 |
After（VBAで自動生成）:
| 操作 | 所要時間 |
|---|---|
| データを貼り付け | 数分 |
| マクロ実行でピボット＋グラフ生成 | 数秒 |
| 配置ミス・集計漏れ | ゼロ |
—
自分も以前、毎月届く売上データを受け取るたびに、ピボットテーブルを手作業で作り直していた。行フィールドに部署を入れて、列フィールドに月を入れて、値に売上金額を入れて……毎回同じ操作なのに、うっかりフィールドの配置を間違えることがあった。VBAで自動生成するようにしてからは、データを貼り付けてマクロを実行するだけで同じ形式のピボットテーブルとグラフが一瞬で完成するようになった。配置ミスもゼロ。同じようにピボットテーブルの手作業に時間を取られている人に、この記事で自動化を体験してほしい。
ピボットテーブルの作成は手作業だと毎回同じ操作の繰り返し。VBAで自動化すれば速くて正確。
なお、ピボットの元データを条件付きで抽出してから集計したい場合は 複数条件でデータを抽出してまとめる方法 を参照。
—
実行前の準備
バックアップを取る
ピボットテーブルの作成先シートにデータがある場合、上書きされる可能性がある。 必ずファイルのコピーを別フォルダに保存してから実行する。
Excelをマクロ有効ブック（.xlsm）で保存する
拡張子が .xlsx のままだとマクロが保存できない。
- 「ファイル」→「名前を付けて保存」
- ファイルの種類を「Excelマクロ有効ブック (*.xlsm)」に変更
- 保存
シート構成を確認する（実務版で使用）
実務版コードは以下のシート構成を前提としている：
- データシート: シート名「売上データ」— A列:日付、B列:部署、C列:商品、D列:売上金額（1行目はヘッダー）
- 集計シート: シート名「集計」— ピボットテーブルとグラフの出力先（自動作成される）
基本版・応用版はシート構成を問わない。
—
手順（コピペ → 実行まで約5分）
VBE（コードを書く画面）を開く
- Excelで
Alt + F11を押す
標準モジュールを挿入する
- VBEのメニュー →「挿入」→「標準モジュール」
コードを貼り付けて実行する
- コードウィンドウに、下のコードをそのままコピペする
Alt + F8→ マクロ名を選んで「実行」
ボタンに割り当てれば毎回Alt+F8を押さなくて済む。方法は マクロをボタン1つで実行する方法 を参照。
—
コード（基本版）– PivotCaches.Create + CreatePivotTable でピボット作成
まずはピボットテーブルを1つ作る基本。PivotCaches.Create でキャッシュを作り、CreatePivotTable でテーブルを配置する。
'============================================================
' ■ ピボットテーブルを作成する（基本版）
' → 指定したデータ範囲からピボットテーブルを自動生成
'============================================================
Sub CreatePivotTableBasic()
'--- ★書き換えポイント ---
Dim srcSheet As String
srcSheet = "Sheet1" '← データがあるシート名
Dim srcRange As String
srcRange = "A1:D100" '← データ範囲（ヘッダー含む）
Dim dstSheet As String
dstSheet = "Sheet2" '← ピボットの出力先シート名
Dim dstCell As String
dstCell = "A3" '← ピボットの配置先セル
Dim ptName As String
ptName = "売上集計" '← ピボットテーブルの名前
'--- ★ここまで ---
'--- 出力先シートが存在しなければ作成
Dim wsDst As Worksheet
On Error Resume Next
Set wsDst = ThisWorkbook.Worksheets(dstSheet)
On Error GoTo 0
If wsDst Is Nothing Then
Set wsDst = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
wsDst.Name = dstSheet
End If
'--- 同名のピボットテーブルがあれば削除
Dim ptDel As PivotTable
On Error Resume Next
Set ptDel = wsDst.PivotTables(ptName)
On Error GoTo 0
If Not ptDel Is Nothing Then
ptDel.TableRange2.Clear
End If
'--- ピボットキャッシュを作成
Dim srcData As String
srcData = srcSheet & "!" & srcRange
Dim pc As PivotCache
Set pc = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=srcData)
'--- ピボットテーブルを作成
Dim pt As PivotTable
Set pt = pc.CreatePivotTable( _
TableDestination:=wsDst.Range(dstCell), _
TableName:=ptName)
MsgBox "ピボットテーブル「" & ptName & "」を作成しました。" & vbCrLf & _
"フィールドエリアにドラッグしてご利用ください。", vbInformation
End Sub
書き換えポイント
| 変数 | 説明 | 初期値 |
|---|---|---|
srcSheet |
データがあるシート名 | "Sheet1" |
srcRange |
データ範囲（ヘッダー含む） | "A1:D100" |
dstSheet |
ピボットの出力先シート名 | "Sheet2" |
dstCell |
ピボットの配置先セル | "A3" |
ptName |
ピボットテーブルの名前 | "売上集計" |
コードの流れ
- 出力先シートが存在しなければ新規作成
- 同名のピボットテーブルが既にあれば
TableRange2.Clearで削除 PivotCaches.Createでソースデータのキャッシュを作成CreatePivotTableでピボットテーブルを配置
ポイント: この段階ではフィールドが未配置の空のピボットテーブルが作られる。フィールドの配置は応用版で解説する。
—
コード（応用版）– フィールド配置・フィルタ・集計方法変更
ピボットテーブルのフィールドをVBAで配置し、集計方法やフィルタも自動設定する。手作業でドラッグする操作をすべてコードで制御できる。
'============================================================
' ■ ピボットテーブルを作成してフィールドを配置（応用版）
' → 行・列・値・フィルタのフィールドを自動配置
' → 集計方法の変更にも対応
'============================================================
Sub CreatePivotTableWithFields()
'--- ★書き換えポイント ---
Dim srcSheet As String
srcSheet = "Sheet1" '← データがあるシート名
Dim srcRange As String
srcRange = "A1:D100" '← データ範囲（ヘッダー含む）
Dim dstSheet As String
dstSheet = "Sheet2" '← ピボットの出力先シート名
Dim dstCell As String
dstCell = "A3" '← ピボットの配置先セル
Dim ptName As String
ptName = "売上集計" '← ピボットテーブルの名前
'--- フィールド設定（ヘッダー名で指定） ---
Dim rowFieldName As String
rowFieldName = "部署" '← 行フィールドに入れる列名
Dim colFieldName As String
colFieldName = "商品" '← 列フィールドに入れる列名
Dim valFieldName As String
valFieldName = "売上金額" '← 値フィールドに入れる列名
Dim filterFieldName As String
filterFieldName = "" '← フィルタに入れる列名（不要なら空文字）
Dim aggFunc As XlConsolidationFunction
aggFunc = xlSum '← 集計方法（xlSum/xlCount/xlAverage/xlMax/xlMin）
'--- ★ここまで ---
'--- 出力先シートが存在しなければ作成
Dim wsDst As Worksheet
On Error Resume Next
Set wsDst = ThisWorkbook.Worksheets(dstSheet)
On Error GoTo 0
If wsDst Is Nothing Then
Set wsDst = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
wsDst.Name = dstSheet
End If
'--- 同名のピボットテーブルがあれば削除
Dim ptDel As PivotTable
On Error Resume Next
Set ptDel = wsDst.PivotTables(ptName)
On Error GoTo 0
If Not ptDel Is Nothing Then
ptDel.TableRange2.Clear
End If
'--- ピボットキャッシュを作成
Dim srcData As String
srcData = srcSheet & "!" & srcRange
Dim pc As PivotCache
Set pc = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=srcData)
'--- ピボットテーブルを作成
Dim pt As PivotTable
Set pt = pc.CreatePivotTable( _
TableDestination:=wsDst.Range(dstCell), _
TableName:=ptName)
'--- 行フィールドを配置
With pt.PivotFields(rowFieldName)
.Orientation = xlRowField
.Position = 1
End With
'--- 列フィールドを配置
If colFieldName <> "" Then
With pt.PivotFields(colFieldName)
.Orientation = xlColumnField
.Position = 1
End With
End If
'--- 値フィールドを配置（集計方法を指定）
pt.AddDataField pt.PivotFields(valFieldName), _
"集計_" & valFieldName, aggFunc
'--- フィルタフィールドを配置
If filterFieldName <> "" Then
With pt.PivotFields(filterFieldName)
.Orientation = xlPageField
.Position = 1
End With
End If
MsgBox "ピボットテーブル「" & ptName & "」を作成しました。" & vbCrLf & _
"行: " & rowFieldName & vbCrLf & _
"列: " & IIf(colFieldName = "", "（なし）", colFieldName) & vbCrLf & _
"値: " & valFieldName & "（" & _
IIf(aggFunc = xlSum, "合計", _
IIf(aggFunc = xlCount, "個数", _
IIf(aggFunc = xlAverage, "平均", "その他"))) & "）" & vbCrLf & _
"フィルタ: " & IIf(filterFieldName = "", "（なし）", filterFieldName), _
vbInformation
End Sub
書き換えポイント
| 変数 | 説明 | 初期値 |
|---|---|---|
rowFieldName |
行フィールドに入れる列名 | "部署" |
colFieldName |
列フィールドに入れる列名 | "商品" |
valFieldName |
値フィールドに入れる列名 | "売上金額" |
filterFieldName |
フィルタに入れる列名（不要なら空文字） | "" |
aggFunc |
集計方法 | xlSum（合計） |
集計方法の一覧
| 定数 | 集計方法 |
|---|---|
xlSum |
合計 |
xlCount |
個数 |
xlAverage |
平均 |
xlMax |
最大値 |
xlMin |
最小値 |
コードの流れ
- 基本版と同じくピボットキャッシュとテーブルを作成
PivotFields("列名").Orientation = xlRowFieldで行フィールドに配置- 列フィールド・フィルタフィールドも同様に配置（空文字ならスキップ）
AddDataFieldで値フィールドを追加し、集計方法を指定
ポイント: PivotFields に指定する名前は、ソースデータの ヘッダー行の文字列と完全一致 させる必要がある。大文字・小文字・全角・半角が違うとエラーになる。
ソースデータの範囲を動的に取得する方法については データの最終行・最終列を正確に取得する方法 も参考になる。
—
コード（実務版）– 月次データから部署別売上ピボット自動生成＋グラフ連動
実務で最もニーズが高いパターン。月次の売上データから部署別ピボットテーブルを自動生成し、集計グラフも連動して作成する。データ範囲は最終行を動的に取得するので、行数が変わっても集計漏れがない。
月次報告のピボットテーブルとグラフが自動で揃うようになってからは、報告書の作成時間が半分以下になった。フォーマットも毎回統一されるので見栄えも良くなった。
'============================================================
' ■ 月次売上データから部署別ピボット＋グラフを自動生成（実務版）
' → 「売上データ」シートからピボットテーブルを作成
' → 「集計」シートにピボットテーブル＋棒グラフを配置
' → データ範囲は最終行を動的取得
'============================================================
Sub CreateMonthlySalesPivot()
'--- ★書き換えポイント ---
Dim dataSheetName As String
dataSheetName = "売上データ" '← データシート名
Dim summarySheetName As String
summarySheetName = "集計" '← 集計シート名（なければ自動作成）
Dim ptName As String
ptName = "部署別売上集計" '← ピボットテーブル名
Dim chartTitle As String
chartTitle = "部署別売上集計グラフ" '← グラフのタイトル
'--- フィールド設定 ---
Dim rowField As String
rowField = "部署" '← 行フィールド
Dim colField As String
colField = "商品" '← 列フィールド（不要なら空文字）
Dim valField As String
valField = "売上金額" '← 値フィールド
Dim filterField As String
filterField = "" '← フィルタ（不要なら空文字）
'--- ★ここまで ---
'--- データシートの参照を取得
Dim wsData As Worksheet
On Error Resume Next
Set wsData = ThisWorkbook.Worksheets(dataSheetName)
On Error GoTo 0
If wsData Is Nothing Then
MsgBox "シート「" & dataSheetName & "」が見つかりません。", vbExclamation
Exit Sub
End If
'--- データ範囲を動的に取得（最終行・最終列）
Dim lastRow As Long
lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
Dim lastCol As Long
lastCol = wsData.Cells(1, wsData.Columns.Count).End(xlToLeft).Column
If lastRow < 2 Then
MsgBox "データがありません（2行目以降にデータが必要です）。", vbExclamation
Exit Sub
End If
Dim srcData As String
srcData = dataSheetName & "!" & wsData.Range( _
wsData.Cells(1, 1), wsData.Cells(lastRow, lastCol)).Address(False, False)
'--- 集計シートが存在しなければ作成、あればクリア
Dim wsSummary As Worksheet
On Error Resume Next
Set wsSummary = ThisWorkbook.Worksheets(summarySheetName)
On Error GoTo 0
If wsSummary Is Nothing Then
Set wsSummary = ThisWorkbook.Worksheets.Add( _
After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
wsSummary.Name = summarySheetName
Else
'--- 既存のピボットテーブルを削除
Dim ptDel As PivotTable
On Error Resume Next
Set ptDel = wsSummary.PivotTables(ptName)
On Error GoTo 0
If Not ptDel Is Nothing Then
ptDel.TableRange2.Clear
End If
'--- 既存のグラフを削除
Dim co As ChartObject
For Each co In wsSummary.ChartObjects
co.Delete
Next co
End If
'--- タイトル行を出力
wsSummary.Range("A1").Value = chartTitle
wsSummary.Range("A1").Font.Size = 14
wsSummary.Range("A1").Font.Bold = True
'--- ピボットキャッシュを作成
Dim pc As PivotCache
Set pc = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=srcData)
'--- ピボットテーブルを作成
Dim pt As PivotTable
Set pt = pc.CreatePivotTable( _
TableDestination:=wsSummary.Range("A3"), _
TableName:=ptName)
'--- 行フィールドを配置
With pt.PivotFields(rowField)
.Orientation = xlRowField
.Position = 1
End With
'--- 列フィールドを配置
If colField <> "" Then
With pt.PivotFields(colField)
.Orientation = xlColumnField
.Position = 1
End With
End If
'--- 値フィールドを配置（合計）
pt.AddDataField pt.PivotFields(valField), _
"合計_" & valField, xlSum
'--- フィルタフィールドを配置
If filterField <> "" Then
With pt.PivotFields(filterField)
.Orientation = xlPageField
.Position = 1
End With
End If
'--- ピボットテーブルの書式設定
pt.ShowTableStyleRowStripes = True
pt.TableStyle2 = "PivotStyleMedium9"
'--- ピボットグラフを作成（集計シートに埋め込み）
Dim ptRange As Range
Set ptRange = pt.TableRange1
'--- グラフの配置位置を計算（ピボットテーブルの右側）
Dim chartLeft As Double
chartLeft = wsSummary.Cells(3, pt.TableRange2.Columns.Count + 2).Left
Dim chtObj As ChartObject
Set chtObj = wsSummary.ChartObjects.Add( _
Left:=chartLeft, _
Top:=wsSummary.Range("A3").Top, _
Width:=450, _
Height:=300)
With chtObj.Chart
.SetSourceData Source:=ptRange
.ChartType = xlColumnClustered
.HasTitle = True
.ChartTitle.Text = chartTitle
.HasLegend = True
.Legend.Position = xlLegendPositionBottom
End With
'--- 列幅を自動調整
wsSummary.Cells.EntireColumn.AutoFit
MsgBox "部署別売上ピボットテーブルとグラフを作成しました。" & vbCrLf & vbCrLf & _
"データ範囲: " & srcData & vbCrLf & _
"データ件数: " & (lastRow - 1) & " 行" & vbCrLf & _
"出力先: " & summarySheetName & " シート", vbInformation
End Sub
書き換えポイント
| 変数 | 説明 | 初期値 |
|---|---|---|
dataSheetName |
データシート名 | "売上データ" |
summarySheetName |
集計シート名 | "集計" |
ptName |
ピボットテーブル名 | "部署別売上集計" |
chartTitle |
グラフのタイトル | "部署別売上集計グラフ" |
rowField |
行フィールド | "部署" |
colField |
列フィールド | "商品" |
valField |
値フィールド | "売上金額" |
filterField |
フィルタフィールド | ""（なし） |
データシートの例
「売上データ」シートに以下のように記入する：
| A列（日付） | B列（部署） | C列（商品） | D列（売上金額） |
|---|---|---|---|
| 2026/01/05 | 営業部 | 商品A | 150000 |
| 2026/01/10 | 開発部 | 商品B | 230000 |
| 2026/01/15 | 営業部 | 商品C | 180000 |
| 2026/02/03 | 総務部 | 商品A | 95000 |
| 2026/02/12 | 営業部 | 商品B | 310000 |
コードの流れ
- データシートの最終行・最終列を動的に取得し、ソース範囲を組み立て
- 集計シートがなければ新規作成、あれば既存のピボットテーブルとグラフを削除
- タイトル行を出力し、ピボットキャッシュとテーブルを作成
- 行・列・値・フィルタのフィールドを配置
- テーブルスタイルを設定
- ピボットテーブルの右側に棒グラフを自動作成
- 列幅を自動調整し、結果をメッセージボックスで表示
ポイント: ソースデータの範囲を最終行で動的に取得しているので、データの行数が増えても集計漏れが発生しない。
ピボットの元データを条件付きで抽出してから集計したい場合は 複数条件でデータを抽出してまとめる方法 を参照。
—
よくある落とし穴5選
1. ソースデータ範囲を固定で指定して集計漏れ
自分もこれで上司に指摘された。ソースデータ範囲を "A1:D100" と固定で書いたら、データが101行以上になったときに集計漏れが発生した。
対策: 実務版コードのように最終行を動的に取得する。詳しくは データの最終行・最終列を正確に取得する方法 を参照。
2. 同名のピボットテーブルが既にあってエラー
原因: CreatePivotTable は同名のピボットテーブルが既にあるとエラーになる。Names.Add のように上書きはされない。
対策: 作成前に同名のピボットテーブルを TableRange2.Clear で削除する。基本版・応用版・実務版のコードにはこの処理を入れてある。
3. フィールド名がヘッダーと一致しなくてエラー
原因: PivotFields("部署") のように指定する名前が、ソースデータの1行目（ヘッダー）と完全一致しないとエラーになる。全角・半角・スペースの有無も区別される。
対策: ヘッダー行の文字列をコピペして変数に設定する。手入力すると全角・半角のミスが起きやすい。
4. ソースデータに空行があって範囲が途中で切れた
原因: Excelはデータ範囲を自動認識するとき、空行があるとそこでデータが終わったと判断する場合がある。
対策: ソースデータに空行を作らない。最終行の取得は Cells(Rows.Count, 1).End(xlUp).Row で行う。
5. ピボットテーブルを削除したのに「名前が重複しています」エラー
原因: シートの内容をクリアしてもピボットキャッシュが残っている場合がある。
対策: TableRange2.Clear でピボットテーブル自体を削除する。Cells.Clear ではピボットの定義が残ることがある。
VBAでピボットテーブルのフィールド名が見つからないときの対処法
「PivotFields(“部署”)を指定したらエラーになる」という場合、原因はヘッダー行の文字列と完全一致していないことだ。全角・半角・前後のスペースが1つでも違うとエラーになる。ヘッダーセルの値をDebug.Printで出力して、コード内の文字列と見比べるのが確実。
VBAでピボットテーブルのデータが更新されないときの対処法
「ソースデータを変更したのにピボットテーブルの値が古いまま」という場合、原因はRefreshTableを呼んでいないことだ。ピボットテーブルはキャッシュでデータを保持しているため、pt.RefreshTable を実行してキャッシュを更新する必要がある。
—
FAQ
Q1: 既存のピボットテーブルをVBAで更新（リフレッシュ）するには？
RefreshTable メソッドを使う：
Dim pt As PivotTable
Set pt = Worksheets("集計").PivotTables("売上集計")
pt.RefreshTable
Q2: ピボットテーブルの総計（行・列）を非表示にしたい
pt.ColumnGrand = False '列の総計を非表示
pt.RowGrand = False '行の総計を非表示
Q3: 複数の値フィールドを追加したい
AddDataField を複数回呼ぶ：
pt.AddDataField pt.PivotFields("売上金額"), "合計_売上", xlSum
pt.AddDataField pt.PivotFields("売上金額"), "平均_売上", xlAverage
pt.AddDataField pt.PivotFields("件数"), "件数_カウント", xlCount
Q4: VBAで作ったピボットテーブルを削除したい
Dim pt As PivotTable
Set pt = Worksheets("集計").PivotTables("売上集計")
pt.TableRange2.Clear
Q5: ピボットテーブルのソース範囲を変更したい
新しいキャッシュを作成して ChangePivotCache で差し替える：
Dim pc As PivotCache
Set pc = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:="Sheet1!A1:D200")
Dim pt As PivotTable
Set pt = Worksheets("集計").PivotTables("売上集計")
pt.ChangePivotCache pc
pt.RefreshTable
—
まとめ
- PivotCaches.Create: ソースデータのキャッシュを作成。
SourceDataにデータ範囲を指定 - CreatePivotTable: キャッシュからピボットテーブルを配置。
TableNameはブック内で一意に - PivotFields / Orientation: 行・列・値・フィルタのフィールド配置を制御
- AddDataField: 値フィールドの追加。第3引数で集計方法（xlSum / xlCount / xlAverage など）を指定
- グラフ連動: ピボットテーブルの
TableRange1をグラフのソースに指定すれば連動
関連記事
- 複数条件でデータを抽出してまとめる方法 — ピボット元データの事前加工
- データの最終行・最終列を正確に取得する方法 — ソース範囲の動的指定
- データ範囲からグラフを自動作成する方法 — ピボットテーブル連動グラフの詳細設定
- テーブル形式のデータをVBAで操作する方法 — テーブルをピボットのソースに使う
- データの行と列を入れ替える（転置する）方法 — 横持ちデータを縦持ちに変換してからピボット集計
—
次にやりたくなること
- 複数条件でデータを抽出してまとめる方法: ピボットの元データを条件付きで抽出してから集計
- データの最終行・最終列を正確に取得する方法: ソースデータの範囲を動的に取得してピボットに渡す
- データ範囲からグラフを自動作成する方法: ピボットテーブルと連動したグラフをカスタマイズしたい場合
- テーブル形式のデータをVBAで操作する方法: テーブルをピボットのソースデータとして活用したい場合
—
もっとカスタマイズしたい場合
「集計軸が複雑（クロス集計で3軸以上）」「複数ブックのデータを1つのピボットにまとめたい」「ピボットテーブルのレイアウトや書式を細かく制御したい」など、業務に合わせたカスタマイズが必要な場合は、ココナラで相談できる。
相談時に伝えると話が早い情報:
- Excel のバージョン / OS
- ソースデータの構成（列名・データ件数・更新頻度）
- 集計したい軸と集計方法（合計・平均・個数など）
- グラフの種類やレイアウトの希望
コメント