ピボットテーブルを作成するには(マニュアル編)
下記の様なデータが存在し、項目(各品目を年毎)で集計をしたい場合にピボットテーブルを使って集計すると便利です。

↓
[挿入] - [ピボットテーブル] をクリックし、データの範囲を指定して[OK]ボタンを押します

↓
下記画面が表示されるので、[行ラベル]に【年】、[列ラベル]に【品目】、[値]に【数量】をドラッグ&ドロップします。

↓
結果、下記のように各品目を年毎で集計した結果が出力する事ができました。


ピボットテーブルを作成するには(マクロ編)
これをマクロで書くと、下記となります。
Dim objTable As PivotTable 'ピボットテーブル
Dim objField As PivotField 'ピボットテーブルのフィールド
'集計データのシート・セルを指定する
ActiveWorkbook.Sheets("Sheet2").Activate
ActiveSheet.Range("A1").Select
'ピボットテーブルのウィザード
Set objTable = ActiveSheet.PivotTableWizard
Set objField = objTable.PivotFields("年") '「年」の項目指定
objField.Orientation = xlRowField '「行ラベル」にセット
Set objField = objTable.PivotFields("品目") '「品目」の項目指定
objField.Orientation = xlColumnField '「列ラベル」にセット
Set objField = objTable.PivotFields("数量") '「数量」の項目指定
objField.Orientation = xlDataField '「値」にセット
objField.Function = xlSum '集計方法を「合計」とする
objField.NumberFormat = "#,##0" '表示方法を「カンマ区切り」とする
Dim objField As PivotField 'ピボットテーブルのフィールド
'集計データのシート・セルを指定する
ActiveWorkbook.Sheets("Sheet2").Activate
ActiveSheet.Range("A1").Select
'ピボットテーブルのウィザード
Set objTable = ActiveSheet.PivotTableWizard
Set objField = objTable.PivotFields("年") '「年」の項目指定
objField.Orientation = xlRowField '「行ラベル」にセット
Set objField = objTable.PivotFields("品目") '「品目」の項目指定
objField.Orientation = xlColumnField '「列ラベル」にセット
Set objField = objTable.PivotFields("数量") '「数量」の項目指定
objField.Orientation = xlDataField '「値」にセット
objField.Function = xlSum '集計方法を「合計」とする
objField.NumberFormat = "#,##0" '表示方法を「カンマ区切り」とする
上記マクロの処理結果は下記となります。

ピボットテーブル(マクロ編)の機能 : 集計方法
集計方法は「合計」だけではなく、下記のピボットテーブルの値フィールドのように「個数」「平均」・・など色々あります。

集計方法 | 値 |
---|---|
合計 | xlSum |
データの個数 | xlCount |
平均 | xlAverage |
最大値 | xlMax |
最小値 | xlMin |
積 | xlProduct |
数値の個数 | xlCountNums |
標本標準偏差 | xlStDev |
標準偏差 | xlStDevP |
標本分散 | xlVar |
分散 | xlVarP |
Dim objTable As PivotTable 'ピボットテーブル
Dim objField As PivotField 'ピボットテーブルのフィールド
'集計データのシート・セルを指定する
ActiveWorkbook.Sheets("Sheet2").Activate
ActiveSheet.Range("A1").Select
'ピボットテーブルのウィザード
Set objTable = ActiveSheet.PivotTableWizard
'「年」の項目指定
Set objField = objTable.PivotFields("年")
With objField
.Orientation = xlRowField
End With
'「品目」の項目指定
Set objField = objTable.PivotFields("品目")
With objField
.Orientation = xlColumnField
End With
'「数量」の【合計】項目指定
Set objField = objTable.PivotFields("数量")
With objField
.Orientation = xlDataField
.Function = xlSum '集計方法を「合計」とする
.Caption = "合計" '項目名を「合計」とする
.NumberFormat = "#,##0"
End With
'「数量」の【平均】項目指定
Set objField = objTable.PivotFields("数量")
With objField
.Orientation = xlDataField
.Function = xlAverage '集計方法を「平均」とする
.Caption = "平均" '項目名を「平均」とする
.NumberFormat = "#,##0"
End With
Dim objField As PivotField 'ピボットテーブルのフィールド
'集計データのシート・セルを指定する
ActiveWorkbook.Sheets("Sheet2").Activate
ActiveSheet.Range("A1").Select
'ピボットテーブルのウィザード
Set objTable = ActiveSheet.PivotTableWizard
'「年」の項目指定
Set objField = objTable.PivotFields("年")
With objField
.Orientation = xlRowField
End With
'「品目」の項目指定
Set objField = objTable.PivotFields("品目")
With objField
.Orientation = xlColumnField
End With
'「数量」の【合計】項目指定
Set objField = objTable.PivotFields("数量")
With objField
.Orientation = xlDataField
.Function = xlSum '集計方法を「合計」とする
.Caption = "合計" '項目名を「合計」とする
.NumberFormat = "#,##0"
End With
'「数量」の【平均】項目指定
Set objField = objTable.PivotFields("数量")
With objField
.Orientation = xlDataField
.Function = xlAverage '集計方法を「平均」とする
.Caption = "平均" '項目名を「平均」とする
.NumberFormat = "#,##0"
End With
上記マクロの処理結果は下記となります。

尚、上記のように合計・平均を縦並びではなく、下記のように横並びで見たいという要望があるかもしれません。また、タイトル行に色を付けたい、ソートで「年」を降順にしたい・・という要望もあるかもしれません。

この場合は、上記コードに下記コードを追記する事で実現できます。
'データフィールドの設定
With objTable.DataPivotField
.Orientation = xlColumnField '行に設定
.LabelRange.Interior.Color = 16777164 'データラベルの色
.DataRange.Interior.Color = 16777164 'データ項目の色
End With
'「年」の装飾
Set objField = objTable.PivotFields("年") '「年」の項目指定
With objField
.AutoSort xlDescending, "年" '降順の設定
.LabelRange.Interior.Color = 10092543 'ラベルの色
.DataRange.Interior.Color = 10092543 '項目の色
End With
'「品目」の装飾
Set objField = objTable.PivotFields("品目")
With objField
.LabelRange.Interior.Color = 16777164 'ラベルの色
.DataRange.Interior.Color = 16777164 '項目の色
End With
With objTable.DataPivotField
.Orientation = xlColumnField '行に設定
.LabelRange.Interior.Color = 16777164 'データラベルの色
.DataRange.Interior.Color = 16777164 'データ項目の色
End With
'「年」の装飾
Set objField = objTable.PivotFields("年") '「年」の項目指定
With objField
.AutoSort xlDescending, "年" '降順の設定
.LabelRange.Interior.Color = 10092543 'ラベルの色
.DataRange.Interior.Color = 10092543 '項目の色
End With
'「品目」の装飾
Set objField = objTable.PivotFields("品目")
With objField
.LabelRange.Interior.Color = 16777164 'ラベルの色
.DataRange.Interior.Color = 16777164 '項目の色
End With
各項目の合計・平均ではなく、下記のように合計毎・平均毎に取り纏めて見たいという要望があるかもしれません。

この場合は、上記コードのデータフィールドの設定で、下記コードのようにPositionの位置を指定する事で実現できます。
'データフィールドの設定
With objTable.DataPivotField
.Orientation = xlColumnField '行に設定
.Position = 1
.LabelRange.Interior.Color = 16777164 'データラベルの色
.DataRange.Interior.Color = 16777164 'データ項目の色
End With
With objTable.DataPivotField
.Orientation = xlColumnField '行に設定
.Position = 1
.LabelRange.Interior.Color = 16777164 'データラベルの色
.DataRange.Interior.Color = 16777164 'データ項目の色
End With


ピボットテーブル(マクロ編)の機能 : 小計を非表示
行項目が1つだけであれば良いのですが、行項目が複数の場合、それぞれの項目毎に小計が自動集計されます。
下記サンプルは「年」の後に「年月」を入れる事により、「年」の小計が自動集計されてしまい、同じデータが重複して表示される事により見難くなってしまいました。(通常であれば12ヶ月なので年毎・月毎で表示した方が見やすいですが、あくまでサンプルとして記載してます)

このような場合に小計を非表示(下記ご参照)すると、見やすくなったと思います。

小計の設定は、Subtotals(インデックス) = True / Falseで行います。
インデックスの内容は下記となります。
インデックス | 集計内容 |
---|---|
1 | 自動 |
2 | 合計 |
3 | データの個数 |
4 | 平均 |
5 | 最大値 |
6 | 最小値 |
7 | 積 |
8 | 数値の個数 |
9 | 標本標準偏差 |
10 | 標準偏差 |
11 | 標本分散 |
12 | 分散 |
デフォルトが1(自動)の場合、他の項目はFalseが設定されています。(MS「デベロッパーセンター」)
従ってSubtotals(1)=Falseを設定する事により小計を非表示にする事ができます。(下記サンプルの赤字ご参照)
Dim objTable As PivotTable 'ピボットテーブル
Dim objField As PivotField 'ピボットテーブルのフィールド
'集計データのシート・セルを指定する
ActiveWorkbook.Sheets("Sheet2").Activate
ActiveSheet.Range("A1").Select
'ピボットテーブルのウィザード
Set objTable = ActiveSheet.PivotTableWizard
'「年」の項目指定
Set objField = objTable.PivotFields("年")
With objField
.Orientation = xlRowField
.Subtotals(1) = False
End With
'「年月」の項目指定
Set objField = objTable.PivotFields("年月")
With objField
.Orientation = xlRowField
.Subtotals(1) = False
End With
'「品目」の項目指定
Set objField = objTable.PivotFields("品目")
With objField
.Orientation = xlColumnField
.Subtotals(1) = False
End With
'「数量」の【合計】項目指定
Set objField = objTable.PivotFields("数量")
With objField
.Orientation = xlDataField
.Function = xlSum '集計方法を「合計」とする
.Caption = "合計" '項目名を「合計」とする
.NumberFormat = "#,##0"
End With
'「数量」の【平均】項目指定
Set objField = objTable.PivotFields("数量")
With objField
.Orientation = xlDataField
.Function = xlAverage '集計方法を「平均」とする
.Caption = "平均" '項目名を「平均」とする
.NumberFormat = "#,##0"
End With
Dim objField As PivotField 'ピボットテーブルのフィールド
'集計データのシート・セルを指定する
ActiveWorkbook.Sheets("Sheet2").Activate
ActiveSheet.Range("A1").Select
'ピボットテーブルのウィザード
Set objTable = ActiveSheet.PivotTableWizard
'「年」の項目指定
Set objField = objTable.PivotFields("年")
With objField
.Orientation = xlRowField
.Subtotals(1) = False
End With
'「年月」の項目指定
Set objField = objTable.PivotFields("年月")
With objField
.Orientation = xlRowField
.Subtotals(1) = False
End With
'「品目」の項目指定
Set objField = objTable.PivotFields("品目")
With objField
.Orientation = xlColumnField
.Subtotals(1) = False
End With
'「数量」の【合計】項目指定
Set objField = objTable.PivotFields("数量")
With objField
.Orientation = xlDataField
.Function = xlSum '集計方法を「合計」とする
.Caption = "合計" '項目名を「合計」とする
.NumberFormat = "#,##0"
End With
'「数量」の【平均】項目指定
Set objField = objTable.PivotFields("数量")
With objField
.Orientation = xlDataField
.Function = xlAverage '集計方法を「平均」とする
.Caption = "平均" '項目名を「平均」とする
.NumberFormat = "#,##0"
End With
ピボットテーブル(マクロ編)の機能 : 全体の合計・平均を非表示
上記のようにピボットテーブルを作成すると、デフォルトで「総計」「全体の合計」「全体の平均」・・という集計がされます。必要な時もありますが、別々のカテゴリを集計する場合、意味ない情報が表示されるので、非表示にしたい!という要望があります。
非表示にする場合、行や列を非表示にする方法もありますが、行数や列数を考慮すると、非常に複雑なマクロを組むことになるので、単純に下記のように記述しましょう。
'全体の合計・平均を表示しない(行)
ActiveSheet.PivotTables(objTable.Name).ColumnGrand = False
'全体の合計・平均を非表示にする(列)
ActiveSheet.PivotTables(objTable.Name).RowGrand = False
ActiveSheet.PivotTables(objTable.Name).ColumnGrand = False
'全体の合計・平均を非表示にする(列)
ActiveSheet.PivotTables(objTable.Name).RowGrand = False
