【Excelマクロ(VBa)】ピボットテーブルの作成

　ピボットテーブルを作成するには(マニュアル編)


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


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

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

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




　ピボットテーブルを作成するには(マクロ編)


これをマクロで書くと、下記となります。


上記マクロの処理結果は下記となります。





　ピボットテーブル(マクロ編)の機能 ： 集計方法


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

集計方法	値
合計	xlSum
データの個数	xlCount
平均	xlAverage
最大値	xlMax
最小値	xlMin
積	xlProduct
数値の個数	xlCountNums
標本標準偏差	xlStDev
標準偏差	xlStDevP
標本分散	xlVar
分散	xlVarP

例えば「合計」と「平均」それぞれで集計したい場合は、下記の様に書きます。


上記マクロの処理結果は下記となります。



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



この場合は、上記コードに下記コードを追記する事で実現できます。


各項目の合計・平均ではなく、下記のように合計毎・平均毎に取り纏めて見たいという要望があるかもしれません。



この場合は、上記コードのデータフィールドの設定で、下記コードのようにPositionの位置を指定する事で実現できます。





　ピボットテーブル(マクロ編)の機能 ： 小計を非表示


行項目が１つだけであれば良いのですが、行項目が複数の場合、それぞれの項目毎に小計が自動集計されます。

下記サンプルは「年」の後に「年月」を入れる事により、「年」の小計が自動集計されてしまい、同じデータが重複して表示される事により見難くなってしまいました。(通常であれば12ヶ月なので年毎・月毎で表示した方が見やすいですが、あくまでサンプルとして記載してます)



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



小計の設定は、Subtotals(インデックス) = True / Falseで行います。
インデックスの内容は下記となります。

インデックス	集計内容
1	自動
2	合計
3	データの個数
4	平均
5	最大値
6	最小値
7	積
8	数値の個数
9	標本標準偏差
10	標準偏差
11	標本分散
12	分散

デフォルトが1(自動)の場合、他の項目はFalseが設定されています。(MS「デベロッパーセンター」)

従ってSubtotals(1)=Falseを設定する事により小計を非表示にする事ができます。(下記サンプルの赤字ご参照)



　ピボットテーブル(マクロ編)の機能 ： 全体の合計・平均を非表示


上記のようにピボットテーブルを作成すると、デフォルトで「総計」「全体の合計」「全体の平均」・・という集計がされます。必要な時もありますが、別々のカテゴリを集計する場合、意味ない情報が表示されるので、非表示にしたい！という要望があります。

非表示にする場合、行や列を非表示にする方法もありますが、行数や列数を考慮すると、非常に複雑なマクロを組むことになるので、単純に下記のように記述しましょう。