VBAを使ったピボットテーブルの作り方を解説
ExcelVBAを使えばワークシート上部に配置されたアイコンで用意された作業の多くを自動化することができます。
ピボットテーブルも例外ではなくマクロを構築する事で作業を自動化させることができます。
以下箇条書きのような作業を検討されているExcelユーザー様におすすめの機能です。
- 「毎日、毎週、毎月」など一定のタイミングで用意されたデータ群を使ってリスト（テーブル）を作成したい
- 複数のリスト（テーブル）にあるデータを集合させつつ組み替えることで必要な情報を抽出したい
- グラフィカルで使いやすいグラフを生成したい（グラフのフィールド内でフィルターをかけるなど）
集計データからのテーブル、グラフの作成は手作業でないと・・・と思われがちですがVBAなら数秒で作業終了です。
作成されたテーブルはフィルターなど便利な機能が用意され「見易い」かつ「使いやすい」データとなります。
加えてピボットテーブルで生成されたデータは可変式グラフを作成する為のバックデータにもなります。
ピボットテーブルをバックデータにしたグラフはグラフ内でフィルターをかけられます。
よりグラフィカルで柔軟にプレゼンに対応できるデータを生成することが可能となります。
本記事では基本編としてピボットテーブルの作り方や基礎的な操作をVBAで用意する方法を紹介します。
次の記事では応用編としてピボットテーブルをもとにしたピボットグラフの作り方を紹介する事にします。
ピボットテーブルの解説は不要ですぐに事例やコードを確認したい方はこちらから事例に進んでください。
関連記事
ピボットテーブルを作る前に行う元データの準備は配列を使うことをおすすめします。
大容量データに負けない【検索にも使える】VBAの2次元配列で作業の高速化を実現させる
元データとなるデータソースが複数のフォルダに格納されているという方。
フォルダ、ファイルの取り回しから自動化させることが出来るとさらに作業性が上がり効率UPにつながりますよ。
別ブックへのアプローチ方法VBA｜Dir関数を使ってフォルダ内の複数ファイルを順番に処理する
3種類のグラフを作る方法を解説しています。ピボットテーブルのデータをもとに実際にグラフを作成しています。
VBAでグラフを用意するマクロでグラフを新規作成｜関連オブジェクトをもとに3種類の方法を紹介
関連書籍
VBAを使ってピボットテーブルにアプローチしている書籍は数が少ないです。
こちらの書籍ではピボットテーブルが紹介されていました。分かりやすく説明されていましたので紹介します。
事例形式でマクロが用意されているので同じような事をやりたいと思っている方にはピッタリの書籍です。
ポチップ
本が苦手な方電子書籍はこちらになります。持ち運び不要ですよ。
ピボットテーブルとは
VBA×ピボットテーブルの記事ですので「ピボットテーブルとは？」という説明は別記事に任せる事にします。
Microsoftが用意したドキュメントは英語版を和訳している事もあり解読するのが非常に難しい事が多いです。
しかし今回用意したURLに記載されている内容は非常に分かりやすい仕様となっております。是非ご覧ください。
使い時（いつ使うのか）
使い時は「あらゆるシーン」です。データの持ち方次第ですが基本的に全てのデータはピボットテーブルの対象になります。
Microsoftのドキュメントからの引用をご覧ください。具体的な使い時が示されています。
あらゆる仕事シーンで使える
では、ピボットテーブルが便利に使えるのはどういう場面でしょうか。
基本的にピボットテーブルが使えるのは、2 つ以上の項目についてデータの集計を行う「クロス集計」を行うときです。例として、以下のようなシーンが考えられるでしょう。
Excel ピボットテーブルで見やすい集計、分析にトライしよう!：より抜粋
- 支店、商品、売上額が一覧になっている表から、支店ごとや商品別の売上を求めたい。
- 営業部員が商談にかけている時間一覧と、営業部員ごとの売上額の一覧から、営業部員が商談にかける時間と、売上額の相関を知りたい。
- アンケート項目の一覧から、満足度が高かった人の年代と性別を求めたい。
メリット
メリットは沢山あります。ここでもMicrosoftのドキュメントを引用させていただきます。
「大量データを素早く集計できる」というのが最大のメリットです。
さまざまなデータを軸に集計できる
ピボットテーブルとは、テーブル上にあるデータをさまざまな切り口で分析する Excel の機能です。簡単なメニューに従ってデータを選ぶだけで、きれいな集計表やグラフを作ることができます。
ピボットテーブルを使うには、まず集計の元となるデータが必要です。これは 1 つのテーブルでも、複数のテーブルであっても構いません。これを任意の分析の切り口に従って、別のテーブルにまとめていきます。
つまり、ゼロから Excel のシートを作るときに使用するのではなく、既にある程度の量のデータが入った Excel のシートがあり、それを分析する、あるいはまとめ直す際の機能だということです。
- 膨大な量のデータでも瞬時に集計ができる
- 元のデータの形を壊したり、変更したりすることなく集計できる
このような特徴から、Excel の強力な分析機能として多くの場面で使われています。
Excel ピボットテーブルで見やすい集計、分析にトライしよう!：より抜粋
デメリット
機能として万能なピボットテーブルにもデメリットはあります。
デメリットの内訳をみていくと機能についてデメリットを感じているユーザーはわずかな様です。
多くのユーザーは機能の良し悪しを考える前にそもそも「難しいので使えない」様です。これが最大のデメリットです。
「難しいので使えない」理由は複数あるので特定の原因は提示しにくいです。しかし共通して言える事があります。
ピボットテーブルが理解出来ない方は「Excelの基本的な使い方」が分かっていない傾向があります。
「Excelの基本的な使い方」について一例を挙げておきます。総務省がリリースした記事に添付されたPDFをご覧ください。
参考文献総務省：統計表における機械判読可能なデータの表記方法の統一ルールの策定
一例を挙げてみます。PDF内で指摘されている「結合セル」はピボットテーブルの構築という観点から見ても不要です。
資料を読むと本来のExcelの使い方が分かります。最終的にピボットテーブルに合う情報の持ち方を理解する事ができます。
基本編：VBAでピボットテーブルを作成する
ここからはVBAでピボットテーブルを用意する方法を解説していきます。
事例
以下のような事例を用意しました。A列からE列に元データを用意しG列からS列でピボットテーブルを構築しています。
画像右にはピボットテーブルのフィールドが表示されており行、列、値のボックスに各フィールドをセットしています。
AからE列の情報が用意されているところから作業をスタートします。ここが作業の始点です。
画像と同じ配置のピボットテーブルを組むところまでをVBAで用意していきます。ここが作業の終点です。
コード
コードはこちらです。AからE列に元データの情報を配置してからプロシージャを実行してください。
コード内のワークシート名は「元データ」にしています。ご自身で作業する際は任意の値に書き換えてご使用ください。
Sub ピボットテーブル()
Dim Pch As PivotCache 'ピボットキャッシュ（空のピボットテーブルの外枠）
Dim r As Long 'A列の最終行
Dim ws As Worksheet '元データが用意されたワークシート
Dim rng As Range 'ピボットの対象データを格納
'ワークシートも変数に格納しておく（作業性向上の為）
Set ws = Worksheets("元データ")
With ws
'元データの最終行を取得
r = .Cells(Rows.Count, 1).End(xlUp).Row
'ピボットに使うデータ範囲を決める
Set rng = .Range(.Cells(5, 1), .Cells(r, 5))
'ピボットテーブルを用意するためのフィールドを用意する
Set Pch = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng)
Pch.CreatePivotTable tabledestination:=.Cells(5, 7), TableName:="表"
End With
'行、列、値に要素を格納
With ws.PivotTables("表")
With .PivotFields("年")
.Orientation = xlRowField '行ボックス
.Position = 1 '1階層目に配置
End With
With .PivotFields("月")
.Orientation = xlRowField
.Position = 2 '2階層目に配置
End With
With .PivotFields("内外区分")
.Orientation = xlColumnField '列ボックス
.Position = 1
End With
With .PivotFields("メーカー名")
.Orientation = xlColumnField
.Position = 2
End With
'値フィールドには各要素ごとの販売数を「足し算」した値を配置
.AddDataField ws.PivotTables("表").PivotFields("販売数"), "合計 / 販売数", xlSum
End With
End Sub解説
大きく分けてポイントは3つです。順番に説明していきます。
- ピボットテーブルの元データを確定させる
- ピボットキャッシュを用意する
- 各ボックスにフィールドを配置する
1_ピボットテーブルの元データを確定させる
まずはピボットテーブルの元データを確定させます。使用するデータの範囲を決めます。
A列で最終行を取得したのち変数rngに対象データが用意されたRangeオブジェクトを格納します。
変数rngにRangeオブジェクトを代入する際にはSetが必要になります。
オブジェクト変数が理解来ていない方はこちらの記事で内容確認ください。Setを使う理由が分かります。
オブジェクト変数についてVBAで転記｜ブック間で文字列をコピペするマクロの作り方を教えます
2_ピボットキャッシュを用意する
ピボットキャッシュは見ていただいた方が早いので画像を用意しました。画像内の赤枠がピボットキャッシュです。
簡単に言うと「空のピボットテーブル」です。側だけ用意しましたという事ですね。
ここからはコード内の要素を使って具体的に解説してみます。
PivotCachesオブジェクトのCreateメソッドを実行することでワークブック内に空のピボットキャッシュを生成します。
Createメソッド以下のパラメータを指定する事でピボットキャッシュを適宜生成できます。
今回はSourceType、SourceDataを指定しました。もう1つVersionという引数があるのですが基本不要で良いです。
- SourceType：ピボットキャッシュのタイプを決めます。今回は単発でテーブルを用意するxlDatabaseを使用
- SourceData：データ範囲を指定します。今回は変数rngを使用
参考複数の範囲からピボットテーブルを生成する際はSourceTypeはxlConsolidationを使います。
このように対象となるデータに応じて指定するのがSourceTypeというパラメータになります。
次は表示位置です。ピボットキャッシュを生成したあとは何処に配置するのかを指示します。
PivotCacheオブジェクトのCreatePivotTableメソッドでピボットキャッシュの配置を決めています。
ピボットテーブルについてはオブジェクトとプロパティ、メソッドの関係が理解できてないと難しいです。
それぞれの要素の関係性はオブジェクトブラウザーで確認する事ができます。興味がある方は勉強してみてください。
3_各ボックスにフィールドを配置する
ピボットフィールドを起点にコードを書いていきます。
フィールドは元データ最上部の表題です。年、月、内外区分、メーカー名、販売数です。
ここではフィールドをどのボックス（フィルター、行、列、値）に格納するのかを指定します。
PivotFieldsメソッドで任意のインデックスを指定する事でPivotFieldオブジェクトを取得します。
その後PivotFieldオブジェクトのOrientationプロパティに各ボックスの名前を代入します。
最後にPositionプロパティに数値を代入する事で階層を指示する事ができます。
アウトプット
事例のところでも見ていただいた様にピボットテーブルを生成しデータの集計ができました。
今回は元データとピボットテーブルを並べたかったので同じシートに出力する様にしています。
本来は元データとピボットテーブルは別シートの方が取り回しが楽になります。使用感を見ながら作業してください。
ポチップ
まとめ
VBAでピボットテーブルを作る方法を解説しました。
今回は成果物としてピボットテーブルを作りきることを優先したのでそんなに難しい事はやっていません。
最低限の仕様でコードを書いています。もっと沢山できることはありますので各自で挑戦してみてください。
ピボットテーブルが組める様になるとさらに良い事が起こります。Excelの基礎的なスキルが向上します。
結合セルやスペースで文字位置を調整しなくなるのでシート内で「シンプル」&「綺麗」にデータを持つ事ができます。
データの持ち方が分かる様になると結果VBAのコードもシンプルになります。良い事しかないです。
次の記事では今回用意したピボットテーブルを使ってピボットグラフに挑戦していきます。
ピボットグラフは「グラフ内でフィルターが使える」などより柔軟にデータを表現できる様になります。
