ピボットテーブルを使ってデータを加工することがあります。
でもピボットテーブルをマクロで作る方法って難しいですよね。
そこで今回は、VBAでピボットテーブルを自動作成する方法を解説します。
目次
【マクロVBA】ピボットテーブルを自動作成する方法を解説
以下のエクセルデータをピボットテーブルにするコードを作成しました。
Sub CreatePivotTable()
'リストの範囲を取得
Dim rowCount As Long
Dim colCount As Long
Dim sourceRange As Range
With Range("A1").CurrentRegion
rowCount = .Rows.Count
colCount = .Columns.Count
Set sourceRange = .Resize(rowCount, colCount)
End With
'ピポットテーブル用のシートを作成
Dim pivotSheet As Worksheet
Set pivotSheet = Sheets.Add
pivotSheet.Name = "データ"
'ピボットテーブルを作成
Dim pivotCache As pivotCache
Set pivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=sourceRange, Version:=6)
Dim pivotTable As pivotTable
Set pivotTable = pivotCache.CreatePivotTable(TableDestination:=pivotSheet.Cells(3, 1), TableName:="ピボットテーブル", DefaultVersion:=6)
'任意の項目を列に追加
With pivotTable
.PivotFields("担当者").Orientation = xlRowField
.PivotFields("商品").Orientation = xlRowField
End With
'金額フィールドをデータフィールドとして合計
With pivotTable.PivotFields("金額")
.Orientation = xlDataField
.Function = xlSum
End With
'ピボットテーブルのレイアウトを「表形式で表示」に設定
pivotTable.RowAxisLayout xlTabularRow
End Sub
リストの範囲を取得
'リストの範囲を取得
Dim rowCount As Long
Dim colCount As Long
Dim sourceRange As Range
With Range("A1").CurrentRegion
rowCount = .Rows.Count
colCount = .Columns.Count
Set sourceRange = .Resize(rowCount, colCount)
End With
このコードは、現在のワークシート上のリストの範囲を取得しています。
それぞれの行の説明は以下の通りです。
説明
Dim rowCount As Long:rowCountという名前の変数を作成し、リストの行数を格納するために使用します。Dim colCount As Long:colCountという名前の変数を作成し、リストの列数を格納するために使用します。Dim sourceRange As Range:sourceRangeという名前の変数を作成し、リストの範囲（セル）を格納するために使用します。With Range("A1").CurrentRegion: セルA1から始まる連続したデータ領域（リスト）を参照します。このWithステートメントにより、以下のコードで.CurrentRegionを繰り返し参照することなく、この領域に対して操作を行うことができます。rowCount = .Rows.Count:.CurrentRegionの行数を数え、rowCount変数に格納します。colCount = .Columns.Count:.CurrentRegionの列数を数え、colCount変数に格納します。Set sourceRange = .Resize(rowCount, colCount):sourceRange変数に、.CurrentRegionの範囲を設定します。これにより、sourceRange変数はリストの範囲を指します。End With:Withステートメントを終了します。これで、.CurrentRegionを参照する操作が終わります。
このコードの結果、sourceRange変数は、ワークシート上のリスト（連続したデータ領域）を指すようになります。
このsourceRangeは、後続のコードでピボットテーブルのデータソースとして使用されます。
ピポットテーブル用のシートを作成
'ピポットテーブル用のシートを作成
Dim pivotSheet As Worksheet
Set pivotSheet = Sheets.Add
pivotSheet.Name = "データ"
このコードは、新しいワークシートを作成し、ピボットテーブルを配置するために使用します。
それぞれの行の説明は以下の通りです。
説明
Dim pivotSheet As Worksheet:pivotSheetという名前の変数を作成し、新しく作成されるワークシートを格納するために使用します。Set pivotSheet = Sheets.Add:Sheets.Addを使って新しいワークシートを作成し、作成されたワークシートをpivotSheet変数に格納します。この時点で、pivotSheet変数は新しく作成されたワークシートを指しています。pivotSheet.Name = "データ": 新しく作成されたワークシートの名前を「データ」に変更します。これにより、新しいワークシートは「データ」という名前でアクセスできるようになります。
このコードの結果、新しいワークシートが作成され、「データ」という名前が付けられます。
ピボットテーブルを作成
'ピボットテーブルを作成
Dim pivotCache As PivotCache
Set pivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=sourceRange, Version:=6)
Dim pivotTable As PivotTable
Set pivotTable = pivotCache.CreatePivotTable(TableDestination:=pivotSheet.Cells(3, 1), TableName:="ピボットテーブル", DefaultVersion:=6)
このコードは、ピボットテーブルを作成するために使用されます。それぞれの行の説明は以下の通りです。
説明
Dim pivotCache As PivotCache:pivotCacheという名前の変数を作成し、ピボットテーブルのデータソース（キャッシュ）を格納するために使用します。Set pivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=sourceRange, Version:=6):ActiveWorkbook.PivotCaches.Createメソッドを使って、新しいピボットキャッシュを作成します。引数は次のとおりです。
SourceType:=xlDatabase: データソースのタイプを指定します。この場合、Excelのデータベース（ワークシート上のリスト）が指定されています。SourceData:=sourceRange: データソースの範囲を指定します。以前に取得したリストの範囲（sourceRange変数）が使用されます。Version:=6: ピボットテーブルのバージョンを指定します。6はExcel 2016以降で使用されているバージョンです。
Dim pivotTable As PivotTable:pivotTableという名前の変数を作成し、新しく作成されるピボットテーブルを格納するために使用します。Set pivotTable = pivotCache.CreatePivotTable(TableDestination:=pivotSheet.Cells(3, 1), TableName:="ピボットテーブル", DefaultVersion:=6):pivotCache.CreatePivotTableメソッドを使って、新しいピボットテーブルを作成し、pivotTable変数に格納します。引数は次のとおりです。
TableDestination:=pivotSheet.Cells(3, 1): ピボットテーブルを配置するワークシートとセルを指定します。新しく作成した「データ」ワークシートのR3C1（3行1列目）が指定されています。TableName:="ピボットテーブル": ピボットテーブルに名前を付けます。この例では、名前は「ピボットテーブル」です。DefaultVersion:=6: ピボットテーブルのバージョンを指定します。6はExcel 2016以降で使用されているバージョンです。
このコードの結果、新しいピボットテーブルが作成され、「データ」という名前のワークシートのR3C1（3行1列目）に配置されます。
任意の項目に列を追加
'任意の項目を列に追加
With pivotTable
.PivotFields("担当者").Orientation = xlRowField
.PivotFields("商品").Orientation = xlRowField
End With
'金額フィールドをデータフィールドとして合計
With pivotTable.PivotFields("金額")
.Orientation = xlDataField
.Function = xlSum
End With
このコードは、ピボットテーブルにデータを追加し、行とデータフィールドを設定するために使用されます。それぞれの行の説明は以下の通りです。
説明
With pivotTable:pivotTable変数が指すピボットテーブルを操作するためのWithステートメントを開始します。.PivotFields("担当者").Orientation = xlRowField: 担当者フィールドを行フィールドとして設定します。これにより、ピボットテーブルの行に担当者が表示されます。.PivotFields("商品").Orientation = xlRowField: 商品フィールドを行フィールドとして設定します。これにより、ピボットテーブルの行に商品が表示されます。End With:Withステートメントを終了します。これにより、pivotTable変数が指すピボットテーブルに対する操作が完了します。With pivotTable.PivotFields("金額"): 金額フィールドを操作するためのWithステートメントを開始します。.Orientation = xlDataField: 金額フィールドをデータフィールドとして設定します。これにより、ピボットテーブルのデータ部分に金額が表示されます。.Function = xlSum: 金額フィールドの集計方法を合計（xlSum）に設定します。これにより、ピボットテーブルのデータ部分には、金額の合計が表示されます。End With:Withステートメントを終了します。これにより、金額フィールドに対する操作が完了します。
このコードの結果、ピボットテーブルには担当者と商品が行フィールドとして設定され、金額がデータフィールドとして合計された値が表示されます。
これにより、担当者と商品ごとの金額の合計が分かるピボットテーブルが作成されます。
ピボットテーブルのレイアウトを「表形式で表示」に設定
'ピボットテーブルのレイアウトを「表形式で表示」に設定
pivotTable.RowAxisLayout xlTabularRow
この行のコードは、ピボットテーブルのレイアウトを「表形式で表示」に設定するために使用されます。説明は以下の通りです。
説明
pivotTable.RowAxisLayout: ピボットテーブルオブジェクトのRowAxisLayoutメソッドを呼び出します。このメソッドは、ピボットテーブルの行レイアウトを変更するために使用されます。xlTabularRow: 行レイアウトのスタイルを指定するためのExcel定数です。「表形式で表示」（Tabular）スタイルを適用するために使用されます。このスタイルでは、各フィールドが独自の列に表示され、データが読みやすい形式で提示されます。
このコードの結果、ピボットテーブルの行レイアウトが「表形式で表示」に設定されます。
これにより、担当者と商品フィールドが独立した列に表示され、データが見やすく整理された形式で表示されます。
コメント