Excel VBAでピボットテーブルを自動作成・データソースを動的に更新する

Excel VBAでピボットテーブルを更新する方法をお探しですね。

広告

Excelピボットテーブルを自動で作って更新する方法【VBA初心者向け】

Excelで売上データや在庫データを集計するとき、ピボットテーブルってすごく便利ですよね。

でも、毎月データが増えるたびに範囲を選び直したり、集計表を作り直したりするのって、けっこう面倒じゃないですか?
しかも、範囲の選び忘れで集計ミスが起きたりすることもあります。

この記事では、**Excel VBAを使ってピボットテーブルを自動で作ったり、データを自動更新したりする方法**を、基本から実際に使えるコードまで分かりやすく説明していきます。

毎回同じクオリティでレポートを作りたい人にぴったりの内容です。

VBAでピボットテーブルを自動作成する基本の流れ

Excel VBAでピボットテーブルを自動で作るとき、まず知っておきたいのが「元データ」「ピボットキャッシュ」「ピボットテーブル」の3つの関係です。

**元データ**は、売上明細とか顧客リストとか、集計したい表のことですね。

**ピボットキャッシュ**っていうのは、その元データをピボットテーブル用に一時的に保存しておく仕組みです。

VBAではこのキャッシュを作ってから、ピボットテーブルを配置します。

手作業だと意識しない部分ですが、VBAではこの流れをちゃんとコードで書く必要があるんです。

ピボットテーブルをちゃんと作るには、元データの形も大事です。

**1行目には必ず見出しを入れて、途中に空白行や結合セルを作らない**ようにしましょう。

特に、見出しが空白になっている列があると、VBAでピボットテーブルを作るときにエラーが出ることがあります。

あと、数値として集計したい列に文字や記号が混ざってると、合計じゃなくて件数で集計されちゃうこともあるので、データの形は事前に整えておくと安心です。

それでは、実際のコードを見てみましょう。

下のコードは、「Data」っていうシートにあるデータをもとに、「Pivot」シートへピボットテーブルを自動で作る例です。

行に「部門」、列に「商品」、値に「売上金額」を配置しています。

使うときは、シート名やフィールド名を自分のファイルに合わせて変えてくださいね。

“`vba
Sub CreatePivotTableBasic()

Dim wsData As Worksheet
Dim wsPivot As Worksheet
Dim srcRange As Range
Dim pc As PivotCache
Dim pt As PivotTable

Set wsData = ThisWorkbook.Worksheets(“Data”)
Set wsPivot = ThisWorkbook.Worksheets(“Pivot”)

wsPivot.Cells.Clear

Set srcRange = wsData.Range(“A1”).CurrentRegion

Set pc = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=srcRange)

Set pt = pc.CreatePivotTable( _
TableDestination:=wsPivot.Range(“A3″), _
TableName:=”SalesPivot”)

With pt
.PivotFields(“部門”).Orientation = xlRowField
.PivotFields(“商品”).Orientation = xlColumnField
.PivotFields(“売上金額”).Orientation = xlDataField
.PivotFields(“売上金額”).Function = xlSum
.PivotFields(“売上金額”).NumberFormat = “#,##0”
End With

End Sub
“`

このコードでは、`CurrentRegion`っていう機能を使って、A1を含む連続した表全体を自動で取得しています。

`CurrentRegion`は、空白行や空白列で囲まれた範囲を自動的に見つけてくれるので、シンプルなデータリストならすごく便利です。

ただし、表の途中に完全な空白行があると、そこで範囲が切れちゃうので注意してください。

なので、VBAで自動化するなら、**見出し行があって、データがちゃんと詰まってる一覧表**として管理するのが基本になります。

データの範囲を自動で更新する方法

ピボットテーブルの自動化で一番大事なのが、**元データの行数が増えたり減ったりしても、正しい範囲を参照できるようにすること**です。

手作業でピボットテーブルを作ったとき、元データの範囲が固定されてると、あとから追加した行が集計に含まれないことがあるんですよね。

VBAでデータソースを動的に更新すれば、毎月データを追加したり、日々明細が増えたりしても対応できます。

営業実績とか経費集計みたいに、定期的に行数が増えるデータでは、この動的な範囲指定が必須です。

動的な範囲指定には、いくつかやり方があります。

よく使われるのは、さっき紹介した`CurrentRegion`を使う方法、最終行・最終列を取得する方法、それからExcelの「テーブル」機能を使う方法です。

初心者には`CurrentRegion`が分かりやすいんですが、空白行に弱いっていう弱点があります。

一方、**最終行と最終列をちゃんと取得する方法**は、コードが少し長くなりますけど、範囲の考え方がはっきりしてて実務向きです。

下のコードは、最終行と最終列を取得して、データソースを動的に設定する例です。

A列を基準に最終行を見つけて、1行目を基準に最終列を見つけています。

この方法なら、行数や列数が増えても、元データの範囲を自動で広げてピボットテーブルを作れます。

“`vba
Sub CreatePivotTableDynamicSource()

Dim wsData As Worksheet
Dim wsPivot As Worksheet
Dim srcRange As Range
Dim lastRow As Long
Dim lastCol As Long
Dim pc As PivotCache
Dim pt As PivotTable

Set wsData = ThisWorkbook.Worksheets(“Data”)
Set wsPivot = ThisWorkbook.Worksheets(“Pivot”)

wsPivot.Cells.Clear

lastRow = wsData.Cells(wsData.Rows.Count, “A”).End(xlUp).Row
lastCol = wsData.Cells(1, wsData.Columns.Count).End(xlToLeft).Column

Set srcRange = wsData.Range(wsData.Cells(1, 1), wsData.Cells(lastRow, lastCol))

Set pc = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=srcRange)

Set pt = pc.CreatePivotTable( _
TableDestination:=wsPivot.Range(“A3″), _
TableName:=”DynamicSalesPivot”)

With pt
.PivotFields(“部門”).Orientation = xlRowField
.PivotFields(“商品”).Orientation = xlColumnField

With .PivotFields(“売上金額”)
.Orientation = xlDataField
.Function = xlSum
.Name = “売上金額 合計”
.NumberFormat = “#,##0”
End With
End With

End Sub
“`

このコードのポイントは、**固定の範囲じゃなくて`lastRow`と`lastCol`を取得してる**ところです。

たとえば、今月は100行、来月は150行になったとしても、A列の最終データ行まで自動で範囲に含めてくれます。

ただし、A列に空白が含まれるデータだと、最終行の判定がうまくいかないことがあります。

そういうときは、必ず値が入る列を基準にするか、データの入力ルールをちゃんと決めておくことが大切です。

もっと安定した運用を目指すなら、**元データをExcelの「テーブル」に変換しておく**方法もおすすめです。

テーブルはデータを追加すると自動で範囲が広がるので、ピボットテーブルのデータソースとしてすごく相性がいいんです。

VBAでは`ListObjects(“テーブル名”).Range`みたいに指定できるので、セルの位置が変わっても影響を受けにくくなります。

業務ファイルを長く使い続ける場合は、普通のセル範囲よりもテーブル化したデータを基準にする設計がおすすめです。

すでにあるピボットテーブルを更新・再作成する実用コード

実際の仕事では、毎回新しくピボットテーブルを作るだけじゃなくて、**すでにあるピボットテーブルのデータソースを更新したい**ことも多いですよね。

値だけが変わった場合は、ピボットテーブルの更新処理だけで反映できます。

でも、元データの行数や列数が変わった場合は、データソースの範囲を変更する必要があります。

この違いを理解しておくと、「更新したのに追加データが反映されない!」っていうトラブルを防げます。

既存のピボットテーブルを更新するだけでいい場合は、`RefreshTable`や`RefreshAll`を使います。

`RefreshTable`は特定のピボットテーブルだけを更新して、`RefreshAll`はブック内のすべての接続やピボットテーブルをまとめて更新します。

ただし、**元データの範囲が固定されたままだと、更新しても新しく追加した行は対象に入りません。

**
だから、行数が変わるデータでは、ピボットキャッシュを作り直してからピボットテーブルに割り当てる方法が確実です。

“`vba
Sub UpdatePivotSource()

Dim wsData As Worksheet
Dim wsPivot As Worksheet
Dim srcRange As Range
Dim lastRow As Long
Dim lastCol As Long
Dim pc As PivotCache
Dim pt As PivotTable

Set wsData = ThisWorkbook.Worksheets(“Data”)
Set wsPivot = ThisWorkbook.Worksheets(“Pivot”)
Set pt = wsPivot.PivotTables(“DynamicSalesPivot”)

lastRow = wsData.Cells(wsData.Rows.Count, “A”).End(xlUp).Row
lastCol = wsData.Cells(1, wsData.Columns.Count).End(xlToLeft).Column

Set srcRange = wsData.Range(wsData.Cells(1, 1), wsData.Cells(lastRow, lastCol))

Set pc = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=srcRange)

pt.ChangePivotCache pc
pt.RefreshTable

End Sub
“`

このコードでは、既存のピボットテーブル`DynamicSalesPivot`に対して、新しいピボットキャッシュを割り当てています。

`ChangePivotCache`を使うと、ピボットテーブルのレイアウトをある程度そのままにして、参照元だけを更新できます。

ただし、元データからフィールド名が削除されたり、見出し名が変更されたりすると、既存の行・列・値エリアが正しく動かないことがあります。

自動化する場合でも、**フィールド名は毎月変えない**っていう運用ルールにしておくことが重要です。

ファイルを開いたときに自動で更新したい場合は、`ThisWorkbook`モジュールに`Workbook_Open`イベントを書きます。

こうすると、利用者が更新ボタンを押し忘れても、ブックを開いたタイミングで最新データに更新されます。

ただし、ファイルを開くたびに処理が動くので、データ量が多いと待ち時間が発生します。

必要に応じて、更新ボタンをシート上に置いて、ユーザーが好きなタイミングでマクロを実行できるようにする方法も実務的です。

“`vba
Private Sub Workbook_Open()
Call UpdatePivotSource
End Sub
“`

自動更新を設定するときは、**Excelのマクロセキュリティ**にも注意が必要です。

VBAを含むブックは、普通`.xlsm`形式で保存します。

あと、使う人の環境によってはマクロが無効化されてることもあるので、社内で共有する場合は保存場所や信頼済みドキュメントの設定を確認しておく必要があります。

単にコードを書くだけじゃなくて、誰がどの環境で使うのかまで考えることが、安定した自動化につながります。

エラーを防ぐ設計と運用のコツ

Excel VBAでピボットテーブルを自動作成・データソースを動的に更新する仕組みは便利ですが、**元データの状態が悪いとエラーや集計ミスが起こります。

**
特に多いのは、見出し行の空白、フィールド名の変更、数値列への文字混入、途中の空白行、同じ名前のピボットテーブルがすでに存在するケースです。

VBAは指定された名前や範囲を前提に処理するので、人が見れば分かる小さな違いでも、コード上では別物として扱われちゃいます。

だから、マクロを作る前に**データの入力ルールを整える**ことがすごく大切です。

実務で安定させるには、コード側でも最低限のチェックを入れておくと安心です。

たとえば、最終行が1行目だけならデータが存在しないと判断できますし、対象のワークシートがない場合は処理を止めるべきです。

あと、ピボットテーブルを毎回作り直す場合は、同じ名前のピボットテーブルや古い集計結果を削除してから作成すると、名前の重複エラーを避けられます。

自動化の目的は手作業を減らすことですが、**想定外のデータでも分かりやすく止まる設計**にしておくと、あとあと楽になります。

運用前に確認したいポイントは、こんな感じです。

– 元データの1行目に空白の見出しがないか確認する
– 集計に使うフィールド名を毎月変更しない
– 行数が増えるデータでは、固定範囲じゃなくて動的範囲かテーブルを使う
– ブックを共有するときはマクロ有効形式とセキュリティ設定を確認する

あと、**ピボットテーブルの更新とExcelの再計算は別の仕組み**っていう点も押さえておきましょう。

数式で作った元データをピボットテーブルに取り込んでる場合、数式の計算結果が古いままだと、ピボットテーブルを更新しても期待した結果にならないことがあります。

計算モードが手動になってるファイルでは、必要に応じて`Application.Calculate`を実行してからピボットテーブルを更新すると安全です。

外部ファイルやPower Queryから取り込んだデータを使ってる場合も、データ取得の更新タイミングを考える必要があります。

最後に、**VBAによるピボットテーブル自動化は「一度コードを書けば終わり」じゃなくて、データ構造を保ちながら運用することで効果を発揮します。

**
元データをテーブル化して、フィールド名を固定して、更新処理をボタンやファイル起動時にまとめておけば、集計作業の属人化を大きく減らせます。

日次・月次レポートみたいに同じ形式の集計を繰り返す業務では、ピボットテーブルの自動作成とデータソースの動的更新を組み合わせることで、作業時間の短縮と集計精度の向上を同時に実現できます。

まずは小さなデータでコードを試してみて、ちゃんと動くことを確認してから、実務ファイルに段階的に適用していくといいですよ。

広告