プログラミングと心理学の忘備録

自分の知識をストックしていくためのブログ

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

スポンサーリンク

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

イメージ的にはこんな感じ。

  1. dataSourceをPivotCacheにコピー

  2. 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