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

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

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

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

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

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

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

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

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

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

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

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

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

ここからは、もう少し詳しく解説しながらコードをスリム化していきます。
ピボットテーブルを作成するには、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は特別な場合以外の通常は xlDatabase です。
・xlDatabaseを指定したときは、SourceDataを必ず指定します。
・引数SourceDataは、「元データの場所」を指定します。
　元データが一般の表の場合は、「SourceData:=”シート名!セル範囲”」という表記になります。
　元データがテーブルだった場合は、「SourceData:=”テーブル名”」と表記します。

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

Excel VBA セル範囲をテーブルに変換／テーブルを解除

Excel VBA でテーブルを操作する方法の解説第2弾。セル範囲をテーブルに変換する方法とテーブルを解除して通常のセル範囲にする方法を解説しています。汎用で使えるように設定したサンプルコードとブック内全テーブルを解除するコードも紹介解説

www.kurumico.com

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

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だけです。
・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」の２行も不要なのでカットしています。
・引数名も省略しています。
・さらに、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("年齢（５歳階級）再掲有り_時系列")
        .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プロパティは、各フィールドをどこに配置するかを次の定数で指定します。

・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("年齢（５歳階級）再掲有り_時系列").Orientation = xlColumnField
        .PivotFields("value").Orientation = xlDataField
    End With
End Sub

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