Office TANAKA - Excel VBA Tips[ピボットテーブルを作る]

Excelでデータ分析といえば、何と言ってもピボットテーブルでしょう。さらにこのピボットテーブルをマクロから使えれば、さまざまな用途に応用できるはずです。しかし、マクロでピボットテーブルを操作するという情報は、ほとんどありません。ここでは、マクロでピボットテーブルを操作するポイントを解説します。

まずはマクロ記録で調べる

ピボットテーブルの作成をマクロ記録すると、次のようなコードが記録されます。なお、ここでは、次のようなデータからピボットテーブルを作成したとします。

【元データ】

【作成したピボットテーブル】

元データは[Sheet1]にあります。ここをアクティブシートにします。データはセル範囲A1:E101ですが、テーブル形式にしています。ピボットテーブルを作成する元データは、テーブルにしておくことを強くお勧めします。テーブルから作成したピボットテーブルは、元データのデータ量が増減しても、そのつどピボットテーブル側で元データ範囲を変更する必要がありません。また、VBAでピボットテーブルを作成する場合は、後述するように、テーブルから作成するとコードが短く簡素になります。もし、テーブルではない普通のワークシートからピボットテーブルを作成すると、どうしてもマクロが複雑になります。なお、今回の元データとして使うテーブルは「テーブル1」という名前です。

Sub Macro1()
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "テーブル1", Version:=6).CreatePivotTable TableDestination:= _
        "Sheet2!R3C1", TableName:="ピボットテーブル1", DefaultVersion:=6
    Sheets("Sheet2").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("商品")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("ピボットテーブル1").AddDataField ActiveSheet.PivotTables( _
        "ピボットテーブル1").PivotFields("金額"), "合計 / 金額", xlSum
End Sub

上記のコードは、Excel 2016でマクロ記録しました。Excelのバージョンによって、記録されるコードが少し違います。まず、上記のExcel 2016では

Version:=6
DefaultVersion:=6

のように「6」という数字が記録されます。これは、作成するピボットテーブルのバージョン番号です。ほとんど知られていませんが、ピボットテーブルには、Excel本体とは別に独自のバージョンが存在します。

Excelのバージョン	ピボットテーブルのバージョン	指定する定数	実体
Excel 2000	バージョン2000	xlPivotTableVersion2000	0
Excel 2002	バージョン10	xlPivotTableVersion10	1
Excel 2003	バージョン11	xlPivotTableVersion11	2
Excel 2007	バージョン12	xlPivotTableVersion12	3
Excel 2010	バージョン14	xlPivotTableVersion14	4
Excel 2013	バージョン15	xlPivotTableVersion15	5
Excel 2016	バージョン16	なし	6

Excel 2013までは、それぞれの数値(0～5)にxlPivotTableVersion??という定数が割り当てられていましたが、Excel 2016からは定数がありません。なので、Excel 2016でマクロ記録すると、ここに6という数値が記録されます。

また、ピボットテーブルを作成するときに、便宜的につけられるピボットテーブルの名称は、Excel 2016では「ピボットテーブル1」のように全角ですが、Excel 2013までは「ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1」と半角です。

さて、記録されたコードを精読し、何をしているのかを理解して、不要な部分を削っていきましょう。まず、ご理解いただきたいのが「ピボットテーブルが作られる手順」です。ワークシート上のデータからピボットテーブルを作成すると、Excelは次のように動作します。

ピボットテーブルを作るとき、Excelはまず、元データからピボットキャッシュを作ります。ピボットキャッシュは画面に表示されません。複雑な分析や表示を実現するために、Excelが必要とする"一次加工データ"のようなものです。そのピボットキャッシュからピボットテーブルを作ります。ある元データから作成したピボットキャッシュは、何度も"使い回し"ができます。もし、同じ元データから複数のピボットテーブルを作成するような場合は、一度作成したピボットキャッシュから複数のピボットテーブルを作成できます。

マクロ記録で記録されたコードは、大きく次の2ブロックに分かれています。

Sub Macro1()
    ''【ブロック1】ピボットテーブルの作成
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "テーブル1", Version:=6).CreatePivotTable TableDestination:= _
        "Sheet2!R3C1", TableName:="ピボットテーブル1", DefaultVersion:=6
    Sheets("Sheet2").Select
    Cells(3, 1).Select
    ''【ブロック2】フィールドのレイアウト
    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("金額"), "合計 / 金額", xlSum
End Sub

まず、【ブロック1】部分の「ピボットテーブルの作成」を解説します。

ピボットキャッシュを作る

上記のように、ピボットテーブルを作成するには、

元データからピボットキャッシュを作成する
そのピボットキャッシュからピボットテーブルを作成する

という二段階の処理が必要です。まずは、ピボットキャッシュを作成する部分から見ていきましょう。ピボットキャッシュを作成するにはPivotCachesコレクションのCreateメソッドを実行します。2行目がそれです。

Sub Macro1()
    ''【ブロック1】ピボットテーブルの作成
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "テーブル1", Version:=6).CreatePivotTable TableDestination:= _
        "Sheet2!R3C1", TableName:="ピボットテーブル1", DefaultVersion:=6
    Sheets("Sheet2").Select
    Cells(3, 1).Select
End Sub

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

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

引数SourceType(ピボットテーブルの種類)は特別な場合を除いてxlDatabaseです。ここにxlDatabaseを指定したときは、次のSourceDataを必ず指定します。

引数SourceDataは、要するに「元データの場所」を指定します。冒頭で書いたように、ピボットテーブルの元データはテーブルにしておきましょう。元データがテーブルだったときは、上記コードのように

SourceData:="テーブル1"

と記述すれば済みます。ちなみに、この"テーブル1"は、テーブルに設定されている名称ではありません。テーブルには必ず固有の名称が必要です。テーブルを作成するとExcelは、便宜的に「テーブル1」「テーブル2」などの名称を設定します。と同時に、作成したテーブルに名前を定義します。任意のセルに独自の名前を定義する機能です。これは、[数式]タブの[名前の管理]をクリックして表示される[名前の管理]ダイアログボックスで確認できます。このように、テーブルに定義される名前は、ブックレベルの名前として定義されます。したがって、アクティブシートがどこであっても「テーブル1」という名前でテーブルを操作できます。

もし、元データがテーブルではなく、通常のワークシートだったときは、ピボットテーブルの元データが次のようにR1C1形式で記録されます。

SourceData:="Sheet1!R1C1:R101C5"

引数SourceDataには、普通にRangeオブジェクトを指定できますので、ここは次のように書けます。

SourceData:=Sheets("Sheet1").Range("A1:E101")

ここで注意が必要です。たとえ元データがアクティブシートであっても、上記のようにシート名をつけなければなりません。なぜなら一般的にピボットテーブルは、新しいシートに作成します。つまり、新しいワークシートが挿入されます。すると必ずアクティブシートが移動します。シートを挿入した後で、元データのセル範囲を指定しなければなりませんから、シートの指定が必要になるのです。ですから、ピボットテーブルの元データは、テーブル形式にしておくことをお勧めします。

次の引数Versionは、作成するピボットテーブル(ピボットキャッシュ)のバージョンを指定します。指定できる定数または数値は、上記の表をご覧ください。ただし「Excel 2016でExcel2010形式のピボットテーブルを作成する」というような特別な場合を除いて、この引数Versionは省略できます。省略すると、マクロを実行しているExcelのバージョンに合わせたピボットテーブルが作成されます。

では、ここまでを整理してみましょう。

SourceType：ピボットテーブルの種類 → xlDatabase
SourceData：元データの範囲 → テーブル1(テーブルの名前)
Version：ピボットテーブルのバージョン → 省略する

ついでに、長くなるので、名前付き引き数名も省略します。

Sub Macro1()
    ''【ブロック1】ピボットテーブルの作成
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(xlDatabase, _
        "テーブル1").CreatePivotTable TableDestination:=　_
        "Sheet2!R3C1", TableName:="ピボットテーブル1", DefaultVersion :=6
    Sheets("Sheet2").Select
    Cells(3, 1).Select
End Sub

ここまでが、ピボットキャッシュを作成するところです。

ピボットテーブルを作る

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

PivotCaches.Createの後ろに括弧で囲まれて指定している引数1は、ピボットキャッシュを作成するために必要な引数群です。続くCreatePivotTableの後ろに記載されている引数2が、ピボットテーブルを作成するための引数群です。

Sub Macro1()
    ''【ブロック1】ピボットテーブルの作成
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(xlDatabase, _
        "テーブル1").CreatePivotTable TableDestination:= _
        "Sheet2!R3C1", TableName:="ピボットテーブル1", DefaultVersion :=6
    Sheets("Sheet2").Select
    Cells(3, 1).Select
End Sub

ピボットキャッシュからピボットテーブルを作成するには、CreatePivotTableメソッドを実行します。

Sub Macro1()
    ''【ブロック1】ピボットテーブルの作成
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(xlDatabase, _
        "テーブル1").CreatePivotTable TableDestination:= _
        "Sheet2!R3C1", TableName:="ピボットテーブル1", DefaultVersion :=6
    Sheets("Sheet2").Select
    Cells(3, 1).Select
End Sub

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

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

必ず指定しなければならないのは、最初の引数TableDestinationだけです。一般的に、新しいシートに作るピボットテーブルは、左上がセルA3になります。なので、ここは何も考えずにセルA3を指定すればいいでしょう。なお、ヘルプに書かれていませんが、この引数TableDestinationにもRangeオブジェクトを指定できますので、Range("A3")と書けばOKです。ちなみに「アクティブシートの」セルA3です。この時点で、どのシートがアクティブシートになっているか、おわかりですね？新しく挿入したワークシートのセルA3ですよ。

引数TableName(ピボットテーブルの名前)なんて、省略してかまいません。どうせ、そのワークシートにピボットテーブルは1つしか作らないんでしょ？だったら、その後はPivotTables(1)として操作すればいいんです。

ピボットキャッシュを作成するときと同様に、作成するピボットテーブルのバージョンを表す引数DefaultVersionも省略します。

ということで、ピボットテーブルを作成するCreatePivotTableメソッドは、次のような指定でOKです。

Sub Macro1()
    ''【ブロック1】ピボットテーブルの作成
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(xlDatabase, _
        "テーブル1").CreatePivotTable Range("A3")
    Sheets("Sheet2").Select
    Cells(3, 1).Select
End Sub

最後に、ゴミのような2行です。

Sub Macro1()
    ''【ブロック1】ピボットテーブルの作成
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(xlDatabase, _
        "テーブル1").CreatePivotTable Range("A3")
    Sheets("Sheet2").Select
    Cells(3, 1).Select
End Sub

ここでは「Sheets("Sheet2").Select」とSheet2が指定されていますが、これは、1行目の「Sheets.Add」で挿入したワークシートの名前が記録されます。でも「Sheets.Add」で新しいワークシートを挿入すれば、そのワークシートが必ずアクティブシートになります。なので、この「Sheets("Sheet2").Select」は意味がなく不要です。ピボットテーブルの作成は複雑です。おそらくExcelの内部では、Excelの事情として、一度アクティブシートを選択しなければいけないのでしょうね。Excelの内部では、きっとそう動いているんです。でも、われわれがマクロでExcelに指示を出すときには、そうした不要な命令は必要ありません。

最後の「Cells(3, 1).Select」は、アクティブシートのセルA3にアクティブセルを移動しろという命令です。別に、アクティブセルがどこにあったって、ピボットテーブルを作成するのには関係ありません。したがって、これも不要です。

まとめると、ピボットテーブルを作成する【ブロック1】は、次のように記述すればいいです。

Sub Macro1()
    ''【ブロック1】ピボットテーブルの作成
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(xlDatabase, _
       "テーブル1").CreatePivotTable Range("A3")
End Sub

だいぶ短くなりました。でも、まだです。もっと簡素化できます。

新しいワークシートにピボットテーブルを作成するのですから、Sheets.Add は必要です。でも、冒頭に1行 Sheets.Add がポツンとあるのは美しくありません。新しいワークシートを挿入すると、必ず挿入した新しいワークシートがアクティブシートになります。その後はアクティブシートを操作してやればいいんです。上記コードの最後で指定している Range("A3") は、この新しく挿入したワークシートのセルです。だったら、次のように書けます。

Sub Macro1()
    ''【ブロック1】ピボットテーブルの作成
    ActiveWorkbook.PivotCaches.Create(xlDatabase, _
       "テーブル1").CreatePivotTable Sheets.Add.Range("A3")
End Sub

上記は、分かりやすく改行していますが、実際は1行で済みます。

ただし、先にも書きましたが、もし元データがテーブルではなく、通常のワークシートだったら話は別です。これほど簡単にはなりません。

Sub Macro1()
    ''【ブロック1】ピボットテーブルの作成
    ActiveWorkbook.PivotCaches.Create(xlDatabase, _
       Sheets("Sheet1").Range("A1:E101")).CreatePivotTable Sheets.Add.Range("A3")
End Sub

または

Sub Macro1()
    ''【ブロック1】ピボットテーブルの作成
    ActiveWorkbook.PivotCaches.Create(xlDatabase, _
       ActiveSheet.Next.Range("A1:E101")).CreatePivotTable Sheets.Add.Range("A3")
End Sub

みたく書かなければなりません。繰り返しますが、だから元データはテーブルにしておくことをお勧めします。

フィールドの設定

次に、フィールドの設定(レイアウト)をします。

Sub Macro1()
    ''【ブロック2】フィールドのレイアウト
    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("金額"), "合計 / 金額", xlSum
End Sub

まず、ピボットテーブルの名前です。マクロ記録では、ピボットテーブルを作成するCreatePivotTableメソッドの引数で、作成するピボットテーブルの名前を指定します。その後は、そこで指定した名前を使って、フィールドの設定などを行います。しかし、一般的には、ワークシート上に複数のピボットテーブルを作成することは希です。1つのワークシートに、1つのピボットテーブルを作成することが多いです。であれば、その後にピボットテーブルを操作するとき、
PivotTables("ピボットテーブル1")
のように名前で指定しなくても
PivotTables(1)
と「1つめのピボットテーブル」を指定すれば同じことになります。名前ではなくインデックス値で指定した方が、汎用性の高いコードになりますので、ここではピボットテーブルをインデックス値で指定するように変更します。もちろん、何らかの事情で、ピボットテーブルを名前で操作したいのでしたら、ピボットテーブルを作成するCreatePivotTableメソッドで任意の名前を指定し、その後はその名前を使ってください。

Sub Macro1()
    ''【ブロック2】フィールドのレイアウト
    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("金額"), "合計 / 金額", xlSum
End Sub

Orientationプロパティは、各フィールドをどこに配置するかを次の定数で指定します。

定数	場所	実体
xlHidden	非表示	0
xlRowField	行	1
xlColumnField	列	2
xlPageField	ページ(フィルター)	3
xlDataField	データ(値)	4

xlHidden を指定すると非表示になりますが、それだったら最初から配置しなければいいのでは？だから、たぶん使うことはないでしょうね。それぞれの定数が「xl○○Field」となっているのは、昔のExcelでは、この配置するエリアのことを「行フィールド」「集計フィールド」などと呼んでいた名残ですね。その当時はすごく分かりにくくて、配置する[担当]とか[金額]もフィールドと呼んでいました。だから解説では「"担当"フィールドを"行"フィールドに配置します」と書かざるを得なかったです。解説を書いていて「これって、分かりにくくねぇ～か」と、いつも悶々としていました。おそらく、そんな理由からでしょうね。配置するエリアのことを、いつの間にか"フィールド"とは呼ばなくなりました。

Positionプロパティには、配置する順番を指定します。

【例】

PivotFields("担当").Position = 1
PivotFields("地域").Position = 2

PivotFields("担当").Position = 2
PivotFields("地域").Position = 1

Positionプロパティを省略すると、1を指定したとみなされます。また、たとえば"行"エリアに、複数のフィールドを配置するようなとき、Positionプロパティを省略すると、VBAのコードで指定した順番に配置されます。

【例】

PivotFields("担当").Orientation = xlRowField
PivotFields("地域").Orientation = xlRowField

PivotFields("地域").Orientation = xlRowField
PivotFields("担当").Orientation = xlRowField

Sub Macro1()
    ''【ブロック2】フィールドのレイアウト
    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("金額"), "合計 / 金額", xlSum
End Sub

今回の場合、[担当]は行に配置します。「Orientation = xlRowField」です。この[担当]は行の左から見て1番目です。したがって「Position = 1」です。でも、Positionプロパティを指定しないと「Position = 1」なのですから、先頭のフィールドでは、Positionプロパティを省略できます。ということになれば、[担当]フィールドに指定しなければならないのは、「Orientation = xlRowField」だけです。Withでくくるまでもありません。

Sub Macro1()
    ''【ブロック2】フィールドのレイアウト
    ActiveSheet.PivotTables(1).PivotFields("担当").Orientation = xlRowField

    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("金額"), "合計 / 金額", xlSum
End Sub

[商品]フィールドも同様です。

Sub Macro1()
    ''【ブロック2】フィールドのレイアウト
    ActiveSheet.PivotTables(1).PivotFields("担当").Orientation = xlRowField

    With ActiveSheet.PivotTables(1).PivotFields("地域")
        .Orientation = xlRowField
        .Position = 2
    End With

    ActiveSheet.PivotTables(1).PivotFields("商品").Orientation = xlColumnField

    ActiveSheet.PivotTables(1).AddDataField ActiveSheet.PivotTables( _
        1).PivotFields("金額"), "合計 / 金額", xlSum
End Sub

[地域]フィールドは、[担当]と同じ行に配置します。左から見て2番目です。「Position = 2」を指定してもいいですが、[担当]の次に書くことで自動的に2番目の位置に配置されます。

Sub Macro1()
    ''【ブロック2】フィールドのレイアウト
    ActiveSheet.PivotTables(1).PivotFields("担当").Orientation = xlRowField
    ActiveSheet.PivotTables(1).PivotFields("地域").Orientation = xlRowField
    ActiveSheet.PivotTables(1).PivotFields("商品").Orientation = xlColumnField

    ActiveSheet.PivotTables(1).AddDataField ActiveSheet.PivotTables( _
        1).PivotFields("金額"), "合計 / 金額", xlSum
End Sub

最後の[金額]フィールドは"データ"に配置しています。昔の"集計フィールド"です。"データ"は集計方法を設定できるなど、ほかのエリア(行や列)とは、ちょっと異なります。なのでマクロ記録では AddDataField メソッドが記録されています。しかし、フィールドをレイアウトするときの Orientation には、ちゃんとxlDataFieldも用意されています。これを設定してやればいいです。

Sub Macro1()
    ''【ブロック2】フィールドのレイアウト
    ActiveSheet.PivotTables(1).PivotFields("担当").Orientation = xlRowField
    ActiveSheet.PivotTables(1).PivotFields("地域").Orientation = xlRowField
    ActiveSheet.PivotTables(1).PivotFields("商品").Orientation = xlColumnField
    ActiveSheet.PivotTables(1).PivotFields("金額").Orientation = xlDataField
End Sub

完成です。ActiveSheet.PivotTables(1) が何度も出てきますので、Withでくくりましょう。

Sub Macro1()
    ''【ブロック2】フィールドのレイアウト
    With ActiveSheet.PivotTables(1)
        .PivotFields("担当").Orientation = xlRowField
        .PivotFields("地域").Orientation = xlRowField
        .PivotFields("商品").Orientation = xlColumnField
        .PivotFields("金額").Orientation = xlDataField
    End With
End Sub

最初の、ピボットテーブルを作成するコードと合体させます。

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

    With ActiveSheet.PivotTables(1)
        .PivotFields("担当").Orientation = xlRowField
        .PivotFields("地域").Orientation = xlRowField
        .PivotFields("商品").Orientation = xlColumnField
        .PivotFields("金額").Orientation = xlDataField
    End With
End Sub

このように、ピボットテーブルを作成するコードは、マクロ記録のコードから無駄を省き、使用するプロパティなどを理解し、記述を工夫すれば、こんなに簡単です。これを応用すれば、実務で使うピボットテーブルも作成できるでしょう。一度手で作ってみて、それをマクロ記録し、上記のシンプルパターンに組み込んでください。