Excel VBA マクロでピボットテーブルを作ってみよう

今回からは、Excelのデータ集計や分析に便利で強力な機能「ピボットテーブル」について勉強していきたいと思います。

くるみこ
くるみこ

ピボットテーブルってすごく便利ですよね!手動で作ることがほとんどですがVBAマクロで作成することも出来るので一緒に勉強していきましょう(^^)

はい! 手動でもまだ使いこなせていないんですけど、大丈夫かなぁ(^^; よろしくお願いします!

【この記事でわかること
・VBAマクロでピボットテーブルをつくる方法がわかります

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

くるみこ
くるみこ

前回記事は、参照渡し「ByRef」と値渡し「ByVal」の違いや使い方の解説記事でした。覗いてみてね(^^)/

マクロの記録で確認してみました

まずは「マクロの記録」を使って、実際にピボットテーブルをつくる動作を確認してみました。
使用したExcelのバージョンは「Excel 2016」です。

・使ったデータはこれです。「国勢調査」の男女別人口データです。(e-Stat政府統計ポータルサイトデータベースデータ)

・作成できたピボットテーブルがこちらです。

・「マクロの記録」で記録されたコードをそのまま下に表示します。

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "FEH_00200521_210313170050!R1C1:R3186C10", Version:=6).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="ピボットテーブル1", DefaultVersion:= _
        6
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("時間軸(調査年)")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("総数,男及び女_時系列")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("年齢(5歳階級)再掲有り_時系列")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("ピボットテーブル1").AddDataField ActiveSheet.PivotTables( _
        "ピボットテーブル1").PivotFields("value"), "合計 / value", xlSum
    With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("総数,男及び女_時系列")
        .PivotItems("総数").Visible = False
    End With
End Sub

・行ラベルが「年」と「四半期」になっていました。

記録されたマクロを順番に確認します

・大きくは「ピボットキャッシュ作成」と「ピボットテーブル作成」の2つの部分で構成されているのがわかります。

ピボットテーブル用のシートを追加

・7行目の「Sheets.Add」でピボットテーブル用のシートを追加しています。
同じシートで作成する場合、この部分はありません

ピボットキャッシュ作成部分

・8行目で、ピボットキャッシュを作成しています
ピボットキャッシュとは、元のデータのコピーのようなものです。実際のピボットテーブル作成時には元データを参照するのではなくこのピボットキャッシュを使用しています。
ピボットキャッシュは画面などには表示されません。
・一度作成したピボットキャッシュから複数のピボットテーブルを作成することができます。
元データを削除した後でもピボットキャッシュにデータが保存されています

ピボットテーブル作成部分

・9行目で、ピボットキャッシュからピボットテーブルを作成しています。
・PivotCaches.Create(引数)の作成部分に続く.CreatePivotTable(引数)で、ピボットテーブルを作成しています。

ピボットテーブルのフィールド設定部分

・作成したピボットテーブルの各種フィールドを設定する部分です。
・「行」「列」「データ」などのフィールドの配置などを設定します。

記録されたコードをスリム化します

ここからは、もう少し詳しく解説しながらコードをスリム化していきます。
ピボットテーブルを作成するには、3つのステップがあることがわかりました
1.元データからピボットキャッシュを作成する
2.ピボットキャッシュからピボットテーブルを作成する

3.ピボットテーブルのフィールド設定

ピボットキャッシュ作成

最初のステップ、ピボットキャッシュを作成する部分を見ていきます。
次のコードは、ピボットキャッシュ部分を抜粋したものです。

Sub Macro1()
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "FEH_00200521_210313170050!R1C1:R3186C10", Version:=6).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="ピボットテーブル1", DefaultVersion:= _
        6
    Sheets("Sheet1").Select
    Cells(3, 1).Select
End Sub

ピボットキャッシュは、PivotCachesコレクションのCreateメソッドで作成します
指定されている引数を確認しましょう。

SourceType:=ピボットテーブルの種類
SourceData:=元データの範囲
Version:=ピボットテーブルのバージョン(省略可能)

・引数SourceTypeは特別な場合以外の通常は xlDatabase です。
xlDatabaseを指定したときは、SourceDataを必ず指定します。
・引数SourceDataは、「元データの場所」を指定します。
 元データが一般の表の場合は、「SourceData:=”シート名!セル範囲”」という表記になります。
 元データがテーブルだった場合は、「SourceData:=”テーブル名”」と表記します。

元データは、テーブルにしておく方が良いでしょう。
・利点は、元データが増減してもデータ範囲を変更する必要がありません
・VBAでピボットテーブルを作成する場合、コードの簡素化につながります

・ということで、データをテーブルに変換しました。

・次が、省略後のコードです。(セル範囲を”テーブル名”に置き換えています)

Sub Macro1()
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                      SourceData:= "テーブル1")
                              .CreatePivotTable _
                                      TableDestination:="Sheet1!R3C1", _
                                      TableName:="ピボットテーブル1", _
                                      DefaultVersion:= 6
    Sheets("Sheet1").Select
    Cells(3, 1).Select
End Sub

ピボットテーブル作成

作成されたピボットキャッシュから、ピボットテーブルを作成していきます。この2段階目の動作が、「マクロの記録」では1行で記録されています。

PivotCaches.Createの後ろに括弧で囲まれた引数までが、ピボットキャッシュを作成する部分です。続くCreatePivotTableメソッドでピボットテーブルを作成します。後ろに記載されているはその引数です。

指定されている引数は、次のとおりです。

TableDestination:=ピボットテーブルを作成する左上のセル(一般的にはセルA3)
TableName:=ピボットテーブルの名前(省略可能)
DefaultVersion:=ピボットテーブルのバージョン(省略可能)

必須なのは最初の引数TableDestinationだけです
TableDestination は新しいシートに作る場合は、左上がセルA3になります
・この部分は、Rangeオブジェクトで指定できますので、Range(“A3”)に変更できます。
・TableName(ピボットテーブルの名前)は、省略可能なので省略します。
・DefaultVersionも省略可能なので省略します。

CreatePivotTableメソッド部分も省略したコードは、次のようなになります。

Sub Macro1()
    ActiveWorkbook.PivotCaches.Create(xlDatabase, "テーブル1") _
                              .CreatePivotTable Sheets.Add.Range("A3")
End Sub

・「Sheets(“Sheet1”).Select」と「Cells(3,1).Select」の2行も不要なのでカットしています。
・引数名も省略しています。
・さらに、Addメソッドもピボットテーブル作成部分に組み入れています
・これで大分スッキリしましたね(^^)
ただし、これは元データがテーブルだからこその短さです。一般のセル範囲の場合はテーブル名の部分がセル範囲の記述になります

ピボットテーブルのフィールド設定

次は、ピボットテーブルのレイアウトを決めるフィールドの設定部分を調整します。

・コードのこの部分です。

    With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("時間軸(調査年)")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("総数,男及び女_時系列")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("年齢(5歳階級)再掲有り_時系列")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("ピボットテーブル1").AddDataField ActiveSheet.PivotTables( _
        "ピボットテーブル1").PivotFields("value"), "合計 / value", xlSum
    With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("総数,男及び女_時系列")
        .PivotItems("総数").Visible = False
    End With

・ピボットテーブル名の部分です。ここではPivotTables(“ピボットテーブル1”)のように名前で指定せず、PivotTables(1)」とインデックス値で指定した方が、汎用性が高くなります
・Orientationプロパティは、各フィールドをどこに配置するかを次の定数で指定します。

定数場所実体
xlHidden非表示0
xlRowField1
xlColumnField2
xlPageFieldページ(フィルター)3
xlDataFieldデータ(値)4

・Positionプロパティには、配置する順番を指定します。
【例】PivotFields(“時間軸(調査年)”).Position = 1
   PivotFields(“”総数,男及び女_時系列”).Position = 2
・Positionプロパティを省略すると、1を指定したとみなされます。
・Positionプロパティを省略して複数のフィールドを配置するときは、コードで指定した順番に配置されます。順番通りならば省略可能ということです。
・15~17行目は、表示項目 ”総数” を非表示に設定している部分です。5~8行の同じフィールドと処理を合わせることができます。
13行目の「”value”」フィールドは、Orientation の xlDataField設定すればOKです

・以上の結果、無くても良い部分を極力カットしてスリム化したコードがこちらです。

Sub Macro1()
    ActiveWorkbook.PivotCaches.Create(xlDatabase, "テーブル1") _
                              .CreatePivotTable Sheets.Add.Range("A3")
    With ActiveSheet.PivotTables(1)
        .PivotFields("時間軸(調査年)").Orientation = xlRowField
        .PivotFields("総数,男及び女_時系列").Orientation = xlRowField
        .PivotFields("総数,男及び女_時系列").PivotItems("総数").Visible = False
        .PivotFields("年齢(5歳階級)再掲有り_時系列").Orientation = xlColumnField
        .PivotFields("value").Orientation = xlDataField
    End With
End Sub

・テーブルを選択(アクティブに)してマクロを実行すると、新しいシートが追加されて無事ピボットテーブルが作成されました。

以上のように、ピボットテーブルを作成するコードは、手動で作成する際に「マクロの記録」でコードを記録するようにします。
でも、記録したコードはそのまま再実行してもうまく動作しません!
それは、シート名などが変わってしまうからです。
そこで、この記事のようにコードの無駄を省いたり、ピボットテーブル名をインデックスに設定するなどで意外と簡単にスリム化できます。

今回はやりませんでしたが、インデックスに設定できるのはPivotFields()の部分もテーブル列のインデックスに置き換えることができます

このようにして汎用で使えるように工夫すれば、実務でのピボットテーブル作成に活用できるでしょう。是非チャレンジしてみてください(^^)

スポンサーリンク

まとめ(おわりに)

・いかがでしたでしょうか?
「いつも汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーを念頭に記事を書いています。
・今回も、記事内で使用したコードのサンプルファイルを登録していますのでご利用ください。
今までの記事のサンプルも登録していますのでよろしければお使いください(^^)

まとめと感想など

くるみこ
くるみこ

「マクロの記録」を使ったピボットテーブルの作成を実演しました。やってみれば意外と簡単だったんじゃないですか?

はい!なんとかうまくいきました。少しづつ動かしながらやってみたのが良かったと思います(^^; 今度は一人でもう一度チャレンジしてみます(^^)

くるみこ
くるみこ

追加されるピボットテーブル名が実行するたびに変わるのでインデックスに設定するのは最初にやった方がいいでしょう。それと、データはテーブルに変換して使うようにしましょうね! では次回もピボットテーブルをもう少し継続して勉強していきましょう(^^)/

【今回分かったことは】
・ピボットテーブルの作成方法がわかりました
・「マクロの記録」のコードを汎用で使えるようにする方法がわかりました
・データはテーブルに変換しておいた方が良いことがわかりました

★★★ ランキング参加中! クリックしてね(^^)/ ★★★

今後の記事について

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にもご期待ください(^^)/

記事のサンプルファイルをダウンロードできます

今回の記事のサンプルをダウンロードできるようにしています
過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら(このリンク先)からご利用ください