Worksheet PivotTable

Worksheet

1. はじめに

Excelのピボットテーブルは大量のデータを集計・分析・可視化する強力な機能です。
VBAではPivotTableオブジェクトを使い、ピボットテーブルの作成や操作を自動化できます。

PivotTablesはWorksheetオブジェクトのサブコレクションで、そのシートにあるすべてのピボットテーブルを管理します。
個々のピボットテーブルはPivotTableオブジェクトとして操作可能です。

2. PivotTableオブジェクトの基礎

  • PivotTableはピボットテーブルそのものを表します。
  • Worksheet.PivotTablesはそのシートにあるピボットテーブルの集合を表すコレクション。
  • ピボットテーブルの作成、更新、設定変更、レイアウト調整、データ操作が可能。

3. ピボットテーブルの取得

Sub ピボットテーブル一覧表示()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Dim pt As PivotTable
    Dim i As Long

    Debug.Print "ピボットテーブル数: " & ws.PivotTables.Count
    For i = 1 To ws.PivotTables.Count
        Debug.Print "ピボットテーブル名: " & ws.PivotTables(i).Name
    Next i
End Sub
  • ws.PivotTables.Countでピボットテーブルの個数取得。
  • 名前またはインデックスで個別のPivotTableを取得可能。

4. ピボットテーブルの作成

ピボットテーブルはVBAのPivotCacheとPivotTables.Addを使って作成します。

4-1. PivotCacheとは？

  • ピボットテーブルの元データを格納・管理するキャッシュ。
  • 複数のピボットテーブルで同じデータソースを共有できる。

4-2. 作成コード例

Sub ピボットテーブル作成()
    Dim wsData As Worksheet
    Set wsData = ThisWorkbook.Worksheets("DataSheet")

    Dim wsPivot As Worksheet
    Set wsPivot = ThisWorkbook.Worksheets("PivotSheet")

    Dim pc As PivotCache
    Dim pt As PivotTable

    ' データ範囲を設定
    Dim dataRange As Range
    Set dataRange = wsData.Range("A1").CurrentRegion

    ' ピボットキャッシュ作成
    Set pc = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=dataRange.Address(True, True, xlA1, True))

    ' ピボットテーブル作成（配置先セルはPivotSheetのA3）
    Set pt = pc.CreatePivotTable( _
        TableDestination:=wsPivot.Range("A3"), _
        TableName:="MyPivotTable")

    ' 行フィールド追加
    pt.PivotFields("商品").Orientation = xlRowField

    ' 列フィールド追加
    pt.PivotFields("地域").Orientation = xlColumnField

    ' データフィールド追加（合計）
    pt.AddDataField pt.PivotFields("売上"), "売上合計", xlSum
End Sub

5. PivotTableの主要プロパティ

プロパティ名説明
Nameピボットテーブルの名前
PivotCache元データを保持するピボットキャッシュ
RowFields行エリアのフィールドコレクション
ColumnFields列エリアのフィールドコレクション
DataFieldsデータエリアのフィールドコレクション
PageFieldsフィルターエリアのフィールドコレクション
TableRange1ピボットテーブルのセル範囲（レイアウト範囲）
RefreshTableピボットテーブルを更新（再計算）
ManualUpdate更新を手動制御（Trueで自動更新停止）

6. フィールドの操作

6-1. フィールドの追加・削除

Sub フィールド追加削除()
    Dim pt As PivotTable
    Set pt = Worksheets("PivotSheet").PivotTables("MyPivotTable")

    ' 行フィールド追加
    pt.PivotFields("担当者").Orientation = xlRowField

    ' フィールド削除
    pt.PivotFields("地域").Orientation = xlHidden
End Sub

6-2. データフィールドの追加

Sub データフィールド追加()
    Dim pt As PivotTable
    Set pt = Worksheets("PivotSheet").PivotTables(1)

    pt.AddDataField pt.PivotFields("利益"), "利益合計", xlSum
End Sub

7. ピボットテーブルの更新とリフレッシュ

  • 元データが変わった場合はRefreshTableでピボットテーブルを更新。
Sub ピボット更新()
    Worksheets("PivotSheet").PivotTables(1).RefreshTable
End Sub
  • 大量データの場合はManualUpdateをTrueにして処理後にFalseに戻すことで効率化。

8. レイアウトや表示設定

8-1. フィールドの並べ替えや集計方法変更

Sub 集計方法変更()
    Dim pt As PivotTable
    Set pt = Worksheets("PivotSheet").PivotTables(1)

    Dim pf As PivotField
    Set pf = pt.PivotFields("売上")

    ' 合計から平均へ変更
    pt.AddDataField pf, "売上平均", xlAverage
End Sub

8-2. フィールドの並び順

Sub フィールド並べ替え()
    Dim pt As PivotTable
    Set pt = Worksheets("PivotSheet").PivotTables(1)

    pt.PivotFields("商品").AutoSort xlAscending, "商品"
End Sub

9. ピボットテーブルの削除

Sub ピボットテーブル削除()
    Worksheets("PivotSheet").PivotTables("MyPivotTable").TableRange2.Clear
End Sub
  • TableRange2はピボットテーブルの全範囲（フィルター含む）。
  • クリアするとピボットテーブルがシートから削除される。

10. イベント制御と応用

  • ピボットテーブルの更新時にイベントを使って自動処理も可能（Worksheet.PivotTableUpdateイベント）。
  • ユーザーがピボット操作した際に連動してマクロを実行できる。

11. まとめ

項目内容
PivotTablesワークシートに存在するピボットテーブルの集合
PivotTable個別のピボットテーブルオブジェクト
主要操作作成、フィールド追加・削除、更新、レイアウト設定
作成の流れPivotCache作成 → PivotTable作成 → フィールド設定
注意点データ範囲の指定ミスに注意、更新タイミング管理
応用イベント連動、自動レポート生成
タイトルとURLをコピーしました