TL;DR — ピボットテーブルは PivotCache（元データの凍結されたコピー）の上に作られます。作成は 2 段階です。
PivotCaches.Create(xlDatabase, source)でキャッシュを作り、.CreatePivotTable(destination)でテーブルを作ります。レポートは各フィールドの.Orientation（xlRowField、xlColumnField、xlPageField）を設定して配置し、数値はAddDataFieldで追加します。誰もがはまる罠：元データを 変えても、pt.RefreshTableを呼ぶまでピボットは古い数値を表示し続けます。キャッシュを Table に 向ければ、固定範囲で凍りつく代わりに、ピボットはデータとともに伸びます。
Dim pc As PivotCache, pt As PivotTable
Set pc = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, SourceData:="tblSales") ' Table 名を指定 -> 自ら伸びる
Set pt = pc.CreatePivotTable( _
TableDestination:=Worksheets("Report").Range("A3"), TableName:="pt_Sales")
pt.PivotFields("Region").Orientation = xlRowField
pt.AddDataField pt.PivotFields("Amount"), "Total Amount", xlSum
pt.RefreshTable ' 変更後は元データを読み直す
レポートを自動化する人は、遅かれ早かれピボットテーブルを作るマクロを書き、そして同じ壁にぶつかります。作った当日は正しいのに、翌日からはずっと間違っている、という壁です。売上を 100 行追加してレポートを走らせても、集計はびくともしません。エラーも出ません。原因は、ピボットについて握っておく価値のあるたった 1 つの考えです。ピボットテーブルはセルを集計しているのではなく、PivotCache — 作成時に作られたデータのコピー — を集計しているのです。 以下のあらゆるクセはそこから生まれます。なぜ更新が要るのか、なぜ元範囲が効いてくるのか、なぜ 2 つのピボットが 1 つのキャッシュを共有できるのか。「ピボットはシートではなくスナップショットを読む」と握っておけば、ピボットはもう驚かせてこなくなります。
この記事で学べること
- 考え方の軸 — ピボットはライブのセルではなく PivotCache を読む
- 今どきの 2 段階の作成 —
PivotCaches.CreateのあとCreatePivotTable - 4 つの
Orientationによるフィールド配置と、データフィールドの追加 - 更新の罠 — ピボットが古い数値を表示する理由と直し方
- 元範囲の罠 — キャッシュを Table に向けてピボットを伸ばす
- ブックに溜まったピボットの発見・向け直し・消去
考え方の軸：ピボットはセルではなくキャッシュを読む
ピボットテーブルを作るとき、Excel はそれをワークシートに配線するのではありません。元データを取り込み、それを PivotCache と呼ばれる隠れたメモリ上の構造にコピーし、目に見えるピボットはそのキャッシュに対する 1 つのビューにすぎません。キャッシュは、あなたが作った瞬間に撮られた写真です。あとから元データを編集しても写真は変わりません — だからこそピボットは古い数値を表示し続けるのです。
Debug.Print pt.PivotCache.SourceData ' キャッシュが何から作られたか
Debug.Print pt.PivotCache.RecordCount ' スナップショットが持つ行数 — シートではない
ここから、役に立つ帰結が 2 つまっすぐに導けます。1 つ目、複数のピボットが 1 つのキャッシュを共有できます（2 つ目のピボットを、新しい PivotCaches.Create ではなく pt.PivotCache から作る）。これでファイルは小さく保たれ、まとめて更新されます。2 つ目、更新は任意の手入れではありません — 写真を撮り直す工程そのものです。キャッシュを、自前のデータのコピーを持つ独立したオブジェクトとして見えた瞬間、残りの API はメソッドの寄せ集めであることをやめ、1 つの物語になります。キャッシュを作り、ビューを整え、スナップショットを撮り直す、です。
ピボットテーブルの作成：まずキャッシュ、次にテーブル
信頼できる今どきのやり方は、明示的な 2 段階です。元データからキャッシュを作り、そのキャッシュからテーブルを作ります。
Dim pc As PivotCache, pt As PivotTable
Set pc = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:="Sales!A1:D1000")
Set pt = pc.CreatePivotTable( _
TableDestination:=Worksheets("Report").Range("A3"), _
TableName:="pt_Sales")
SourceData は、Table 名（"tblSales"）、定義された名前、あるいはシート修飾付きのアドレスを文字列として受け取ります — そしてこのアドレス形式こそが元範囲の罠の住処で、これは後ほど戻ってきます。TableDestination はワークシート上の実在するセルでなければならず、ピボットには伸びる余地が要るので、ほかに何もない領域の左上に向けましょう。ピボットに名前を付けること（TableName:="pt_Sales"）は重要です。後の実行で PivotTables(1) を当てずっぽうに使うのではなく、Worksheets("Report").PivotTables("pt_Sales") でもう一度たどり着く方法だからです。
古いマクロが Worksheets("Report").PivotTableWizard ... を 1 回の呼び出しで使っているのを、今でも見かけるかもしれません。動きはしますが、キャッシュを隠してしまい、それに対するきれいなハンドルを渡してくれず、しかもまさにマクロ記録が吐き出すコードです。2 段階の書き方を選び、キャッシュ — 実際にあなたのデータを保持するオブジェクト — を、名前を付けて更新できるものにしましょう。
フィールドの配置：4 つの Orientation
ピボットテーブルには 4 つのゾーンがあり、どのフィールドも .Orientation を通してそのいずれかに収まります。行フィールドと列フィールドがグリッドになり、ページフィールドがレポートフィルターに、データフィールドが集計される数値になります。
With pt
.PivotFields("Region").Orientation = xlRowField
.PivotFields("Month").Orientation = xlColumnField
.PivotFields("Category").Orientation = xlPageField ' レポートフィルター
.AddDataField .PivotFields("Amount"), "Total Amount", xlSum
End With
ここでは 2 つが噛みついてきます。1 つ目、フィールド名は 元データの見出しと厳密に一致 しなければなりません — PivotFields("Ammount") は空の列ではなく実行時エラーを出します。2 つ目、こちらのほうが陰険です。データフィールドの既定の集計が Sum になるのは、列全体が数値のときだけ です。金額の列に 1 つでもテキスト値や空白が紛れ込むと、Excel は既定を黙って Count に切り替え、レポートは「いくら」を期待した場所に「いくつ」を表示します。だからこそ、データフィールドを明示的な AddDataField ... , xlSum で追加するほうが、放り込んで祈るより優れています — 推測を受け継ぐのではなく、関数を自分で宣言するのです。xlAverage、xlCount、xlMax なども同じように使います。
更新の罠：ピボットが古い数値を表示する理由
これはピボットで一番多いバグですが、そもそもバグではありません — キャッシュが仕事をしているだけです。元データを変えても、キャッシュはまだ古い写真を持っていて、ピボットはそれを忠実に表示します。
' 元データに 200 行を追加した... がピボットは気づいていない。
pt.RefreshTable ' この 1 つのピボットのキャッシュを元データから読み直す
pt.PivotCache.Refresh ' キャッシュを更新 -> それを共有するすべてのピボットが更新される
ThisWorkbook.RefreshAll ' ファイル内のすべてのピボット・クエリ・リンクを更新
RefreshTable は 1 つのピボットのためにスナップショットを撮り直します。複数のピボットがキャッシュを共有しているなら、PivotCache.Refresh が一度にすべてを更新します。実践的なルール：データを変えるマクロは、それを読むピボットを更新しなければならず、無人で頼りにするピボットは、ブックを開いたときに更新すべき です。一度だけ作られて二度と更新されないピボットは、レポートではなくスクリーンショットです — 生まれた日をずっと表示し続けます。「ライブ」のダッシュボードをメールで送ったら 1 週間古かった、という経験があるなら、理由はこれです。
元範囲の罠：キャッシュを Table に向ける
2 つ目の静かな失敗は、元範囲です。固定アドレスからキャッシュを作ると、来月の行はその外側に落ち — 更新してすら取り込まれません。キャッシュが読むよう命じられた範囲に、それらが入っていないからです。
' 脆い — 1000 行目より下に追加された新しい行は永遠に見えない
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, "Sales!A1:D1000")
' 頑丈 — Table は自動拡張するので、キャッシュはつねにすべての行を見る
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, "tblSales")
キャッシュを Table（ListObject）に向けることが、下流のすべてを自己保守にする直し方です。行を追加すれば Table が伸び、キャッシュは Table 全体を読み、素の RefreshTable が範囲計算なしで新しいデータを拾います。Table が使えないなら、動的な名前付き範囲が同じ仕事をします。既存の ピボットを作り直さずにより良い元データへ向け直すには、ChangePivotCache で新しいキャッシュを渡します。範囲を Table にする方法は VBA Table を、動的な名前という代替案は VBA Named Range を参照してください。
ピボットの発見・向け直し・消去
ピボットは溜まっていくもので、後の実行は、上に 2 つ目のコピーを積み上げるのではなく、自分が作ったものを見つける必要があります。コレクションをループして、それらを特定するか掃除します。
Dim ws As Worksheet, pt As PivotTable
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
Debug.Print ws.Name & " ! " & pt.Name & " -> " & pt.PivotCache.SourceData
Next pt
Next ws
作り直す前に、名前付きのピボットがすでに存在するかを確認し、重複を追加する代わりにそれを更新しましょう。本当に新しいものがほしいなら、pt.TableRange2.Clear（ピボット領域全体）で古いものを消し、新しい作成にきれいな場所を用意します。ピボットは、名前で引くことのできる永続的なオブジェクトとして扱いましょう — VBA Worksheet のコードが間違ったタブに書き込まないようにするのと同じ規律が、レポートのマクロが実行のたびにピボットを増殖させるのを防ぎます。
ExcelMaster の活用
本当に時間を奪うピボットのミスは、静かなものです。一度も更新されなかったレポート、3 月に 1000 行目で止まった元範囲、1 つのセルがテキストを持っていたせいで黙って件数に変わった合計。どれもエラーなく走り、そして誰かに間違った数値を手渡します。
ExcelMaster は、慎重なアナリストがやるようにピボットを作ります。「売上を地域と月で集計して」と頼めば、元データが自ら伸びるようキャッシュを Table に向け、Count を受け継ぐ代わりに各データフィールドの集計関数を明示的に設定し、書き込んだあとに更新し、ピボットを名前で引くので、再実行はレポートを複製するのではなく更新します。あなたはほしい集計を説明するだけ。来月も数値が正しいままであるよう、キャッシュとフィールドと更新を配線するのは、それがやります。
よくある質問
VBA でピボットテーブルを作成するには？
2 段階で作ります。まず元データからキャッシュを作ります。Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="tblSales") です。次にそのキャッシュからテーブルを作ります。Set pt = pc.CreatePivotTable(TableDestination:=Worksheets("Report").Range("A3"), TableName:="pt_Sales") です。SourceData を Table 名に向ければピボットはデータとともに伸び、ピボットに名前を付ければあとでまた見つけられます。
VBA のピボットテーブルがデータを変えても更新されないのはなぜ？
ピボットテーブルは、ライブのセルではなく PivotCache — 作成時に作られた元データの凍結コピー — を読むからです。元データを変えてもキャッシュには触れないので、ピボットは古い数値を表示し続けます。1 つのピボットを読み直すには pt.RefreshTable、キャッシュを共有するすべてのピボットには pt.PivotCache.Refresh、変更後にファイル全体を更新するには ThisWorkbook.RefreshAll を呼びます。
VBA でピボットテーブルにフィールドを追加するには？
各フィールドの Orientation を設定します。pt.PivotFields("Region").Orientation = xlRowField、列とフィルターのゾーンにはそれぞれ xlColumnField と xlPageField です。数値は pt.AddDataField pt.PivotFields("Amount"), "Total Amount", xlSum で追加すると集計関数を自分で制御できます — さもないと Excel が推測し、列にテキストや空白のセルが 1 つでもあれば既定は Count になります。
ピボットの元範囲が新しい行を取りこぼさないようにするには？
"Sales!A1:D1000" のような固定アドレスにキャッシュを向けないでください。その下に追加された行は決して見られません。元データを Table に変換し、Table 名を SourceData として渡します（PivotCaches.Create(xlDatabase, "tblSales")）。Table は自動拡張するので、通常の RefreshTable がすべての新しい行を拾います。Table が選べないなら、動的な名前付き範囲でも同じことができます。
ブック内のすべてのピボットテーブルをループするには？
2 つのループを入れ子にします。For Each ws In ThisWorkbook.Worksheets のあと For Each pt In ws.PivotTables です。その中で、pt.Name と pt.PivotCache.SourceData が、どのピボットが何を読んでいるかを教えてくれます。同じループを使って、すべてのピボットを更新したり、重複を作る前に名前付きのピボットがすでに存在するかを確認したりできます。
検証環境
検証環境: Excel 365 (Windows 11), VBA 7.1 — 最終確認 2026-09-02.
関連ガイド： VBA Table · VBA Chart · VBA Range · VBA Named Range · VBA Worksheet