excel-vba
ピボットテーブル

備考

ピボットテーブルの作成

Excelで最も強力な機能の1つは、ピボットテーブルを使用してデータを並べ替えて分析することです。ピボット・テーブルとピボット・キャッシュの関係を理解し​​、テーブルのさまざまな部分を参照して使用する方法を理解すると、VBAを使用してピボットを作成および操作する方が簡単です。

最も基本的なソースデータは、 Worksheet上のデータのRange領域です。このデータ領域は 、範囲内の最初の行としてヘッダー行を持つデータ列を識別しなければなりません 。ピボットテーブルが作成されると、ユーザーはいつでもソースデータを表示および変更できます。ただし、ピボットテーブル自体に直接接続されているピボットキャッシュと呼ばれる中間データ記憶構造が存在するため、変更が自動的にピボットテーブル自体に反映されることはありません。

複数のピボットテーブルが必要な場合は、同じソースデータに基づいてピボットキャッシュを各ピボットテーブルの内部データストアとして再利用できます。これは、メモリを節約し、ストレージ用のExcelファイルのサイズを縮小するため、良い方法です。

例として、上記の図に示すソースデータに基づいてピボットテーブルを作成するには：

Sub test()
    Dim pt As PivotTable
    Set pt = CreatePivotTable(ThisWorkbook.Sheets("Sheet1").Range("A1:E15"))
End Sub

Function CreatePivotTable(ByRef srcData As Range) As PivotTable
    '--- creates a Pivot Table from the given source data and
    '    assumes that the first row contains valid header data
    '    for the columns
    Dim thisPivot As PivotTable
    Dim dataSheet As Worksheet
    Dim ptSheet As Worksheet
    Dim ptCache As PivotCache
    
    '--- the Pivot Cache must be created first...
    Set ptCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                                  SourceData:=srcData)
    '--- ... then use the Pivot Cache to create the Table
    Set ptSheet = ThisWorkbook.Sheets.Add
    Set thisPivot = ptCache.CreatePivotTable(TableDestination:=ptSheet.Range("A3"))
    Set CreatePivotTable = thisPivot
End Function

参照先 MSDNピボットテーブルオブジェクト

ピボットテーブルの範囲

これらの優れたリファレンスソースは、ピボットテーブルのさまざまな範囲の説明と図を提供します。

参考文献

• VBAのピボットテーブル範囲の参照 - Jon PeltierのTech Blogから
• VBAを使用してExcelピボットテーブルの範囲を参照する - globaliconnectからExcel VBA

ピボットテーブルへのフィールドの追加

ピボットテーブルにフィールドを追加するときに注意すべき重要な2つの点は、方向と位置です。時には開発者がフィールドの配置場所を想定することがあるため、これらのパラメータを明示的に定義することが常に明確になります。これらのアクションは、ピボット・キャッシュにではなく、指定したピボット・テーブルにのみ影響します。

Dim thisPivot As PivotTable
Dim ptSheet As Worksheet
Dim ptField As PivotField

Set ptSheet = ThisWorkbook.Sheets("SheetNameWithPivotTable")
Set thisPivot = ptSheet.PivotTables(1)

With thisPivot
    Set ptField = .PivotFields("Gender")
    ptField.Orientation = xlRowField
    ptField.Position = 1
    Set ptField = .PivotFields("LastName")
    ptField.Orientation = xlRowField
    ptField.Position = 2
    Set ptField = .PivotFields("ShirtSize")
    ptField.Orientation = xlColumnField
    ptField.Position = 1
    Set ptField = .AddDataField(.PivotFields("Cost"), "Sum of Cost", xlSum)
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
End With

ピボットテーブルデータの書式設定

この例では、指定したピボットテーブルのデータ範囲領域（ DataBodyRange ）にいくつかの形式を変更または設定します。標準Rangeフォーマット可能なすべてのパラメータを使用できます。データの書式設定は、ピボット・テーブル自体にのみ影響し、ピボット・キャッシュには影響しません。

注： TableStyleプロパティがPivotTableのオブジェクトプロパティのメンバでないため、プロパティはTableStyleという名前TableStyle2 。

Dim thisPivot As PivotTable
Dim ptSheet As Worksheet
Dim ptField As PivotField

Set ptSheet = ThisWorkbook.Sheets("SheetNameWithPivotTable")
Set thisPivot = ptSheet.PivotTables(1)

With thisPivot
    .DataBodyRange.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)"
    .DataBodyRange.HorizontalAlignment = xlRight
    .ColumnRange.HorizontalAlignment = xlCenter
    .TableStyle2 = "PivotStyleMedium9"
End With