ピボットテーブルを作成する
Sheet1のA1:B5から商品別売上を集計するピボットテーブルを新しいシートに作ります。
実行前に確認
- 新しいシートとピボットキャッシュを追加します。SalesPivotExampleという既存のピボット名がない練習用ブックで試してください。
- 数値の売上、空欄のない一意の見出しが必要です。元データ変更後の再集計には更新が必要です。
1サンプルコード
Sub Example1198()
Dim ws As Worksheet
Dim s As Worksheet, dest As Worksheet
Dim pc As PivotCache
Dim pt As PivotTable
Set ws = ThisWorkbook.Worksheets("Sheet1")
For Each s In ThisWorkbook.Worksheets
If s.Name = "集計例" Then
MsgBox "集計例シートが既にあります。新しい練習用ブックで試してください。"
Exit Sub
End If
Next s
Set dest = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
dest.Name = "集計例"
Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=ws.Range("A1:B5").Address(ReferenceStyle:=xlR1C1, External:=True))
Set pt = pc.CreatePivotTable(TableDestination:=dest.Range("A3"), TableName:="SalesPivotExample")
pt.PivotFields("商品").Orientation = xlRowField
pt.AddDataField pt.PivotFields("売上"), "売上合計", xlSum
End Sub表で見る処理のイメージ実行前・実行後のセルを見比べる
商品ごとの売上を、新しい「集計例」シートで集計します。
A・B・Cは列、1・2・3は行です。例えば「B2」はB列の2行目のセルです。この表は説明用のサンプルです。
実行前
Sheet1：元データ A1:B5
| A | B | |
|---|---|---|
| 1 | 商品 | 売上 |
| 2 | りんご | 600 |
| 3 | みかん | 240 |
| 4 | りんご | 360 |
| 5 | みかん | 160 |
実行後
集計例：A3からの出力例
| A | B | |
|---|---|---|
| 3 | 行ラベル | 売上合計 |
| 4 | りんご | 960 |
| 5 | みかん | 400 |
| 6 | 総計 | 1360 |
色付きは注目する場所です。「—」は未入力、「（空文字）」は数式などが返す空の文字列を表します。
Sheet1の元データは残ります。「集計例」シートがまだ存在しない前提です。りんごは600+360=960、みかんは240+160=400、総計1360。コンパクト形式・総計ありの場合の代表的な配置です。コードはレイアウト・並べ替え順・総計表示を明示しないため、見出し文言・項目順・総計の表示はExcelの設定で異なります。
使い方・実行手順を確認する4つの手順で、コードの使い方を確認できます
- 1
練習用のブックを.xlsm形式で保存し、Sheet1という名前のシートを用意します。A1に商品、B1に売上、A2:B5へ順に「りんご・100」「みかん・200」「りんご・150」「みかん・50」を入力します。
- 2
Alt＋F11でVBEを開き、［挿入］→［標準モジュール］へコードを貼り付けます。
- 3
A1:B5が集計元です。商品・売上は見出しと完全に一致させます。
- 4
マクロ内にカーソルを置いてF5で実行します。集計例シートに商品別合計（各250）が作られます。