vba ピボットメモ

はじめに

ピボットをvbaで作成するにあたってとりあえず出力した。

データ

プログラム例

Sub CreatePivotTable()
    Dim pivotCache As pivotCache
    Dim pivotTable As pivotTable
    Dim Sheet As Worksheet
    Dim dataRange As Range
    Dim pivotRange As Range

    ' データ範囲を設定
    Set Sheet = ThisWorkbook.Sheets("テストシート")
    Set dataRange = Sheet.Range("A1:D9") ' 例としてA1からD100までの範囲を指定

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

    ' 新しいシートにピボットテーブルを作成
    Set Sheet = ThisWorkbook.Sheets.Add
    Sheet.Name = "ピボットテーブル"
    Set pivotRange = Sheet.Range("A3")

    ' ピボットテーブルの作成
    Set pivotTable = pivotCache.CreatePivotTable( _
        TableDestination:=pivotRange, _
        TableName:="MyPivotTable")

    ' フィールドの追加
    With pivotTable
        .PivotFields("商品名").Orientation = xlRowField
        .PivotFields("顧客名").Orientation = xlColumnField
        .PivotFields("売上額").Orientation = xlDataField
    End With
End Sub

結果

With pivotTable
    .PivotFields("商品名").Orientation = xlRowField
    .PivotFields("顧客名").Orientation = xlColumnField
    .PivotFields("売上額").Orientation = xlDataField
End With

商品名: ピボットテーブルの行に配置されるフィールド。

顧客名: ピボットテーブルの列に配置されるフィールド。

売上額: ピボットテーブルの値（通常は数値データ）に配置されるフィールド。

年月追加

年月追加、行を美ポットに２つにし、列を年月で対応する。
行毎で合計出力追加

以下のような表示をさせる。

Sub CreateOrUpdatePivotTable()
    Dim pivotCache As pivotCache
    Dim pivotTable As pivotTable
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim pivotRange As Range
    Dim pivotSheetName As String
    pivotSheetName = "ピボットテーブル"

    ' データ範囲を設定
    Set ws = ThisWorkbook.Sheets("テストシート")
    Set dataRange = ws.Range("A1:E10") ' 実際のデータ範囲に合わせてください

    ' ピボットテーブルのシートをチェックし、存在しなければ新規作成
    On Error Resume Next ' エラーを一時的に無視
    Set ws = ThisWorkbook.Sheets(pivotSheetName)
    On Error GoTo 0 ' エラーを通常通りに戻す
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Sheets.Add
        ws.Name = pivotSheetName
    Else
        ' シートが存在する場合は内容をクリア
        ws.Cells.Clear
    End If
    Set pivotRange = ws.Range("A3")

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

    ' ピボットテーブルの作成
    Set pivotTable = pivotCache.CreatePivotTable( _
        TableDestination:=pivotRange, _
        TableName:="MyPivotTable")

    ' フィールドの追加
    With pivotTable
        .PivotFields("商品名").Orientation = xlRowField
        .PivotFields("商品名").Position = 1
        .PivotFields("商品名").Subtotals(2) = True
        ' Arrayの中でTrueに設定された位置がサブトータルの種類に対応します。
        ' この例では、最後の要素がTrueで、これは合計に対応しています。
        ' 他の種類の集計を表示したい場合は、該当する位置をTrueに設定します。
    
        .PivotFields("顧客名").Orientation = xlRowField
        .PivotFields("顧客名").Position = 2
        ' 顧客名にサブトータルは不要な場合は以下のように設定します。
        .PivotFields("年月").Orientation = xlColumnField
        .PivotFields("年月").Position = 1
        .PivotFields("売上額").Orientation = xlDataField
                ' 行フィールドのレイアウトをタブ形式に変更
        .RowAxisLayout xlTabularRow
    End With

    '文字、色への対応
    'pivotTable.DataBodyRange.Font.Bold = False
    'pivotTable.PivotFields("商品名").dataRange.Interior.Color = RGB(0, 0, 255) ' 青色
    
End Sub