【EXCEL VBA | ノート】VBAでピボットテーブルを作成する!

スポンサーリンク

スポンサーリンク


ひらちんです。

VBAからピボットテーブルを作成する方法を紹介します!

ピボットテーブルを作る流れ

VBAからピボットテーブルを作成するには、

  1. 元になるデータを指定する
  2. ピボットテーブルを作る場所を指定する
  3. ピボットキャッシュを作る
  4. 作ったピボットキャッシュからピボットテーブルを作成する
  5. ピボットテーブルにフィールドを指定する

という流れになります!

では順番にいきましょう!

1.元になるデータを指定する

サンプルとして以下のデータがあるとします。

なんちゃって個人情報さん で作らせてもらいました。

なんちゃって個人情報さんより

これは大丈夫ですね。

普通にセルの範囲を指定しています。

CurrentRegion」はセル範囲をひとかたまりとして指定する便利なプロパティです。

Sub Sample()

    '元データの指定
    Dim 元データ As Range
    Set 元データ = ThisWorkbook.Sheets("データ").Range("A1").CurrentRegion
    
End Sub

元の範囲をテーブルデータにしておくと後で色々便利かもしれません。

VBAでのテーブル作成の方法は以下の記事をどうぞ(^o^)

ついでにテーブルの名前も変更しておきます。

Sub Sample()

    '元データの指定
    Dim 元データ As ListObject
    Set 元データ = ThisWorkbook.Sheets("データ").ListObjects.Add( _
         SourceType:=xlSrcRange, Source:=ThisWorkbook.Sheets("データ").Range("A1").CurrentRegion)
    
    元データ.Name = "tbl元データ"
    
End Sub

2.ピボットテーブルを作る場所を指定する

これは、どこでも良いんですが、通常ピボットテーブル作ると新しいシートが生まれるので、そのようにしておきましょう(^o^)

'ピボットテーブルを作る場所を指定
Dim ピボット作るシート As Worksheet
Set ピボット作るシート = Sheets.Add

最初のコードに追加します。

Sub Sample()

    '元データの指定
    Dim 元データ As Range
    Set 元データ = ThisWorkbook.Sheets("データ").Range("A1").CurrentRegion
    
    'ピボットテーブルを作る場所を指定
    Dim ピボット作るシート As Worksheet
    Set ピボット作るシート = Sheets.Add
 
End Sub

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

次に、ピボットキャッシュを作ります。

'ピボットキャッシュを作成する
Dim ピボットキャッシュ As PivotCache
Set ピボットキャッシュ = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=元データ)

PivotCachesコレクションCreateメソッドを使います。

引数のSourceTypeには、次の定数を指定します。

名前説明
xlConsolidation3複数のワークシート範囲
xlDatabase1Excel のリスト/データベース
xlExternal2外部のアプリケーションのデータ
xlPivotTable-4148既存のピボットテーブル レポート
xlScenario4データは、[シナリオの登録と管理] を使用して作成されたシナリオに基づきます。

ここでは、「xlDatabase」を指定しています。

SourceDataには、データになる範囲を指定するので、さっき作った「元データ」を指定します。

ここまでのコード全容です。

Sub Sample()

    '元データの指定
    Dim 元データ As Range
    Set 元データ = Sheets("データ").Range("A1").CurrentRegion
    
    'ピボットテーブルを作る場所を指定
    Dim ピボット作るシート As Worksheet
    Set ピボット作るシート = Sheets.Add
    
    'ピボットキャッシュを作成する
    Dim ピボットキャッシュ As PivotCache
    Set ピボットキャッシュ = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=元データ)
        
End Sub


元データをテーブルで設定している場合は、テーブルに付けた名前でも指定出来ます。

'ピボットキャッシュを作成する
Dim ピボットキャッシュ As PivotCache
Set ピボットキャッシュ = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="tbl元データ")

4.作ったピボットキャッシュからピボットテーブルを作成する

では、ピボットキャッシュからピボットテーブルを作成しましょう!

PivotCacheオブジェクトCreatePivotTableメソッドを使います。

'ピボットテーブルを作成する
Dim ピボットテーブル As PivotTable
Set ピボットテーブル = ピボットキャッシュ.CreatePivotTable(ピボット作るシート.Range("A1"))

引数には、ピボットテーブルを作る場所をセルで指定します。

Sub Sample()

    '元データの指定
    Dim 元データ As Range
    Set 元データ = Sheets("データ").Range("A1").CurrentRegion
    
    'ピボットテーブルを作る場所を指定
    Dim ピボット作るシート As Worksheet
    Set ピボット作るシート = Sheets.Add
    
    'ピボットキャッシュを作成する
    Dim ピボットキャッシュ As PivotCache
    Set ピボットキャッシュ = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=元データ)
    
    'ピボットテーブルを作成する
    Dim ピボットテーブル As PivotTable
    Set ピボットテーブル = ピボットキャッシュ.CreatePivotTable(ピボット作るシート.Range("A1"))

End Sub

これでピボットテーブルの作成は出来ますので、一度実行してみましょう!

画像のような形で、新しいシートに空のピボットテーブルが作成されます。

5.ピボットテーブルにフィールドを指定する

作成した時に、一緒にフィールドも設定する場合は、次のように指定します。

'ピボットテーブルのフィールドにセットする
With ピボットテーブル
    .PivotFields("都道府県").Orientation = xlRowField
    .PivotFields("性別").Orientation = xlColumnField
    .PivotFields("年齢").Orientation = xlDataField
    .PivotFields("血液型").Orientation = xlPageField
End With

ピボットテーブルで作成された各フィールドのOrientationプロパティの値を設定してあげます。

※設定が適当ですが悪しからず。。。

場所は図のようになっています。

では、全容です。

Sub Sample()

    '元データの指定
    Dim 元データ As Range
    Set 元データ = Sheets("データ").Range("A1").CurrentRegion
    
    'ピボットテーブルを作る場所を指定
    Dim ピボット作るシート As Worksheet
    Set ピボット作るシート = Sheets.Add
    
    'ピボットキャッシュを作成する
    Dim ピボットキャッシュ As PivotCache
    Set ピボットキャッシュ = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=元データ)
    
    'ピボットテーブルを作成する
    Dim ピボットテーブル As PivotTable
    Set ピボットテーブル = ピボットキャッシュ.CreatePivotTable(ピボット作るシート.Range("A1"))
    
    'ピボットテーブルのフィールドにセットする
    With ピボットテーブル
        .PivotFields("都道府県").Orientation = xlRowField
        .PivotFields("性別").Orientation = xlColumnField
        .PivotFields("年齢").Orientation = xlDataField
        .PivotFields("血液型").Orientation = xlPageField
    End With
    
End Sub

実行してみましょう!!

設定した通りのピボットテーブルの作成が出来ました(^o^)

ピボットテーブルの取得と更新

こちらの記事もどうぞ(^o^)


スポンサーリンク

スポンサーリンク

スポンサーリンク


シェアする

フォローする


スポンサーリンク