先日、「EXCEL: 1万件のテスト用データを簡単に作成する方法【VBAマクロ】」にて、1万行のテスト用データを作ってみました。
今回は、それを使ってピボットテーブルを作ってみようと思います。
ピボットテーブル1つ作るだけであれば、手作業でやった方が速いかと思いますが、1つのデータセットから、様々なデータを抽出したりグラフを作ってみたりと、複雑なこともしていきたいので、練習と言う意味も込めて、VBAマクロでピボットテーブルを操作してみようと思います。
今回、以下のサイトを参考にさせて頂きました。
ピボットテーブルを作る(Office TANAKA)
エクセルマクロVBAでピボットテーブル作成|値範囲を可変しデータ取得
概要
「PivotCache」と「CreatePivotTable」により、ピボットテーブルを作成し、「PivotFields」で項目の指定や、円表示を行っています。
プログラムを実行することで、以下の画像のようなデータから、ピボットテーブルを作成することができます。
<実行前>
サンプル準備方法は、こちら(1万件のテスト用データを簡単に作成する方法【VBAマクロ】)をご覧ください。
↓
<実行後>
・件数不明のデータからピボットテーブルを作成
・行と列の項目を指定
・購入金額を円(¥)表示
コード【VBAマクロ】
- Sub pivottable()
-
- Dim Piv_cashe As PivotCache
- Set Piv_cashe = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ActiveSheet.Range("A1").CurrentRegion.Address)
-
- Dim Piv_table As PivotTable
- Set Piv_table = Piv_cashe.CreatePivotTable(tabledestination:=Sheets.Add.Range("A3"), TableName:="pivot1")
-
- With Piv_table
- .PivotFields("取引先会社名").Orientation = xlRowField
- .PivotFields("製品名").Orientation = xlColumnField
- .PivotFields("購入額").Orientation = xlDataField
- .PivotFields("合計 / 購入額").NumberFormat = "¥#,##0;¥-#,##0"
- End With
- End Sub
コード【VBAマクロ】の解説
3.
作成するピボットテーブルのキャッシュ(PivotCache)を、Piv_casheと名前を付けています。
4.
作成したPivotCacheを、Piv_casheという箱に入れています。
ピボットテーブルのキャッシュは、ThisWorkbook.PivotCaches.Create(引数)で作成できます。
SourceTypeは、xlDatabaseとします。
SourceDataは、A1セルを含むCurrentRegion(空白行・空白列が出てくるまでの範囲)ということで指定しています。
6.
ピボットテーブル(PivotTable)を、Piv_table と定義しています。
7.
4行目で作成したピボットテーブルのキャッシュ(Piv_cashe)を使って、CreatePivotTable(引数)により、ピボットテーブルを作成します。
tabledestinationは、テーブルの配置場所で、A3セルにピボットテーブルの左上がくるようにしています。
TableNameは、pivot1と名前を付けています。
9-14.
9〜14行目は、項目(PivotFields)の設定を行っています。
取引先会社名の配置(Orientation)を、行(xlRowField)にしています。
製品名の配置(Orientation)を、列(xlColumnField)にしています。
購入額の配置(Orientation)を、データ(xlDataField)にしています。
合計 / 購入額の表示形式(NumberFormat )を、”¥#,##0;¥-#,##0″にすることで、円表示にしています。
感想
初めてピボットテーブルをVBAで操作してみましたが、簡単なコードで書くことができるのですね。
ルーティーンで毎回ピボットテーブルを作る必要がある人などは、一回プログラムを準備しておけば、だいぶ楽になりそうです。
また、ピボットテーブルから、グラフを作ったり、数字を抜き出してまとめたり、という場面もあると思うので、次回のピボットテーブル編では、より複雑なプログラムを書いてみようと思います。