【VBA】ピボットテーブルの作り方

手動でピボットテーブルを作成するときは意識しないが、PivotCacheオブジェクトが必要になる。

以下のロジックで作成する。

dataSourceをPivotCacheにコピー
PivotCacheからPivotTableを作成

参考： tonari-it.com

タスク別の所要時間を示したテーブルから、ピボットテーブルを作成する。 f:id:chi_tana:20170924003508p:plain:w300

PivotCaches.Create ピボットテーブルキャッシュを作成

SourceType：データ型*1 ExcelのデータならxlDatabase。

SourceData：ソースデータを指定

CreatePivotTable ピボットテーブルを作成

TableDestination：ピボットテーブルを配置する位置（左上端）を指定

TableName：テーブル名を指定

値フィールドの設定

行フィールドはコードに書いた通り、列フィールドもほぼ同じ書き方なので省略

PivotTable.AddDataField メソッド (Excel)

Field：フィールドを指定（PivotFieldsにフィールド列名を指定）

Caption：データフィールドの名前を指定

Function：に最大値や平均等の集計方法を指定

表示形式を変更する場合は.NumberFormatプロパティを設定

Private Function createPivot()

    Dim wb As Workbook
    Dim sheet As Worksheet
    Dim pivotCache As pivotCache 'ピボットキャッシュ格納用変数

    Set wb = Workbooks.Open(Book.xlsx)
    Set sheet = wb.Worksheets("データ"))

    'ピボットテーブルキャッシュに、「データ」シートのセル範囲をセット
    Set pivotCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        sourcedata:=sheet.Range("A1", Cells(Rows.Count, 3).End(xlUp)) _
        )

    'ピボットテーブルを作成、配置先を設定
    pivotCache.CreatePivotTable _
        TableDestination:=sheet.Range("H1"), _
        TableName:="タスク別所要時間"

    With sheet.PivotTables("タスク別所要時間")
        'ピボットテーブルに行フィールドを追加
        .AddFields RowFields:=Array("タスク")

        'ピボットテーブルに値フィールドを追加
        .AddDataField( _
            Field:=sheet.PivotTables("タスク別所要時間").PivotFields("時間"), _
            Caption:="最大値 / 時間", _
            Function:=xlMax).NumberFormat = "mm:ss.000"
    End With

End Function