大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、ピボットテーブルを作成する方法について、ご紹介します。手順としては、CreatePivotTableでピボットテーブルを作成したあと、行や列、フィルター、値のフィールドを設定してきます。大量のピボットテーブルを作成する必要がある場合は、VBAを活用していきましょう。

はじめに

この記事では、VBAでピボットテーブルを作成する方法について、ご紹介します。

VBAでピボットテーブルを作成するときも、手順は同じで次のように作成します。

  • 元データからピボットテーブルを作成
  • フィールドを設定する

という流れです。

ピボットテーブルを大量に作成する必要がある場合は、VBAを活用していきましょう。

では、VBAでピボットテーブルを作成する方法について、解説していきます。

この記事で紹介すること

  • VBAでピボットテーブルを作成する方法

目次から使いそうな説明のところへ移動すると便利ですよ。

目次

VBAコードまとめ

ピボットテーブルの作成と、フィールドの設定について、VBAコードをまとめました。

VBAコードだけを確認したい場合に、ご活用ください。

'ピボットテーブルの作成
Set A = Worksheets("元データ").Range("A1").CurrentRegion '元データの範囲を設定
ActiveWorkbook.PivotCaches.Create(xlDatabase, A).CreatePivotTable Range("A3") '作成する

'フィールドの設定
With ActiveSheet.PivotTables(1)
    .PivotFields("商品").Orientation = xlRowField '行フィールドを設定
    .PivotFields("商品").Orientation = xlColumnField '列フィールドを設定
    .PivotFields("商品").Orientation = xlPageField 'フィルターフィールドを設定
    .PivotFields("売上").Orientation = xlDataField '値フィールドを設定
End With
    
'「合計」の値フィールドを設定
With ActiveSheet.PivotTables(1).PivotFields("売上")
    .Orientation = xlDataField '値フィールドを設定
    .Function = xlSum '「合計」で集計する
End With

'「平均」の値フィールドを設定
With ActiveSheet.PivotTables(1).PivotFields("売上")
    .Orientation = xlDataField '値フィールドを設定
    .Function = xlAverage '「平均」で集計する
End With
    
'集計方法を「平均」に変更する
ActiveSheet.PivotTables(1).PivotFields("合計 / 売上").Function = xlAverage

'集計方法を「合計」に変更する
ActiveSheet.PivotTables(1).PivotFields("平均 / 売上").Function = xlSum

では、解説していきます。

VBAでピボットテーブルを作成する

VBAでピボットテーブルを作成します。

作成してみる

元データから、ピボットテーブルを作成するVBAコードは、こんな感じです。

Sub TEST1()
    
    'シートを追加
    Sheets.Add
    
    '元データの範囲を格納
    Set A = Worksheets("元データ").Range("A1").CurrentRegion
    
    'ピボットテーブルを作成
    ActiveWorkbook.PivotCaches.Create(xlDatabase, A).CreatePivotTable Range("A3")
    
End Sub

手順としては、

  • シートを追加
  • 元データをオブジェクトに格納
  • 元データをキャッシュしてピボットテーブルを作成

という流れです。

実行してみます。

元データを用意します。

元データを用意

元データを用意

では、VBAコードを実行してみます。

ピボットテーブルが作成できた

ピボットテーブルが作成できた

ピボットテーブルが作成できました。

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

VBAで、ピボットテーブルのフィールドを設定してみます。

フィールドの種類は、4種類あります。

  • 行フィールド
  • 列フィールド
  • フィルターフィールド
  • 値フィールド

という感じです。

それぞれのフィールドに設定する方法をみていきます。

行フィールドを設定

行フィールドを設定するVBAコードは、こんな感じです。

Sub TEST2()
    
    With ActiveSheet.PivotTables(1).PivotFields("商品")
        .Orientation = xlRowField '行フィールドを設定
        .Position = 1 '1番目にする
    End With
    
End Sub

「.Orientation = xlRowField」で行フィールドに設定してます。

「.Position = 1」で行フィールドの「1番目」に設定するという意味になります。

「項目が1つ」であれば、設定する必要はありません。

また、「.Position」を設定しない場合は、VBAコードを「実行する順番」で、「項目の順番」が設定されます。

行フィールドを設定する前の初期のピボットテーブルです。

初期状態のピボットテーブル

行フィールドを設定する前

では、VBAコードを実行してみます。

「行」フィールドを設定できた

行フィールドを設定できた

行フィールドを設定できました。

列フィールドを設定

列フィールドを設定するVBAコードは、こんな感じです。

Sub TEST3()
    
    With ActiveSheet.PivotTables(1).PivotFields("商品")
        .Orientation = xlColumnField '列フィールドを設定
        .Position = 1 '1番目にする
    End With
    
End Sub

「.Orientation = xlColumnField」で列フィールドに設定してます。

列フィールドを設定する前の初期のピボットテーブルです。

初期状態のピボットテーブル

列フィールドを設定する前

では、VBAコードを実列してみます。

「列」フィールドを設定できた

列フィールドを設定できた

列フィールドを設定できました。

フィルターフィールドを設定

フィルターフィールドを設定するVBAコードは、こんな感じです。

Sub TEST4()
    
    With ActiveSheet.PivotTables(1).PivotFields("商品")
        .Orientation = xlPageField 'フィルターフィールドに設定
        .Position = 1 '1番目にする
    End With
     
End Sub

「.Orientation = xlPageField」でフィルターフィールドに設定してます。

フィルターフィールドを設定する前の、初期のピボットテーブルです。

初期状態のピボットテーブル

フィルターフィールドを設定する前

では、VBAコードを実列してみます。

「フィルター」フィールドを設定できた

フィルターフィールドを設定できた

フィルターフィールドを設定できました。

値フィールドを設定

値フィールドを設定するVBAコードは、こんな感じです。

Sub TEST5()
    
    With ActiveSheet.PivotTables(1).PivotFields("売上")
        .Orientation = xlDataField '値フィールドに設定
        .Position = 1 '1番目にする
    End With
    
End Sub

「.Orientation = xlDataField」で値フィールドに設定してます。

値フィールドを設定する前の初期のピボットテーブルです。

初期状態のピボットテーブル

値フィールドを設定する前

では、VBAコードを実値してみます。

「値」フィールドを設定できた

値フィールドを設定できた

値フィールドを設定できました。

ピボットテーブルの作成と設定のVBAコードをまとめる

ピボットテーブルを作成して設定するVBAコードを、まとめて実行してみます。

クロス集計表を作成

元データから、クロス集計表を作成するVBAコードは、こんな感じになります。

Sub TEST6()
    
    'シートを追加する
    Sheets.Add
    
    '元データの範囲を格納
    Set A = Worksheets("元データ").Range("A1").CurrentRegion
    
    'ピボットテーブルを作成する
    ActiveWorkbook.PivotCaches.Create(xlDatabase, A).CreatePivotTable Range("A3")
    
    'フィールドを設定
    With ActiveSheet.PivotTables(1)
        .PivotFields("商品").Orientation = xlRowField '行フィールドを設定
        .PivotFields("支店").Orientation = xlColumnField '列フィールドを設定
        .PivotFields("売上").Orientation = xlDataField '値フィールドを設定
    End With
    
End Sub

元データを用意しました。

元データを用意

元データを用意

では、VBAコードを実行してみます。

クロス集計表を作成できた

ピボットテーブルでクロス集計表を作成できた

VBAを使って、ピボットテーブルでクロス集計表を作成できました。

VBAで値フィールドの集計種類を設定する

VBAで、値フィールドの「集計種類」を設定してみます。

集計種類は、「合計」とか「平均」、「個数」といったものです。

ポイントは、値フィールドを設定する際に、集計種類を設定することです。

「合計」の値フィールドを設定

「合計」の値フィールドを設定するVBAコードは、こんな感じになります。

Sub TEST7()
    
    With ActiveSheet.PivotTables(1).PivotFields("売上")
        .Orientation = xlDataField '値フィールドを設定
        .Function = xlSum '「合計」を設定
    End With
    
End Sub

行と列フィールドを設定した、ピボットテーブルを用意しました。

行と列フィールドを設定したピボットテーブル

行と列フィールドを設定したピボットテーブルを用意

では、実行してみます。

「合計」の値フィールドを設定できた

集計種類が「合計」の値フィールドを設定できた

値フィールドの集計の種類を「合計」に設定できました。

設定した後ではなく、設定する際に、集計の種類を設定しましょう。

「平均」の値フィールドを設定

続いて、「平均」の値フィールドを設定するVBAコードは、こんな感じになります。

Sub TEST8()
    
    With ActiveSheet.PivotTables(1).PivotFields("売上")
        .Orientation = xlDataField '値フィールドを設定
        .Function = xlAverage '「平均」を設定
    End With
    
End Sub

行と列フィールドを設定した、ピボットテーブルを用意しました。

行と列フィールドを設定したピボットテーブル

行と列フィールドを設定したピボットテーブルを用意

では、実行してみます。

「平均」の値フィールドを設定できた

集計種類が「平均」の値フィールドを設定できた

値フィールドの集計の種類を「平均」に設定できました。

値フィールドの集計種類の一覧

値フィールドの集計種類をまとめました。

こんな感じになります。

  • 合計:xlSum
  • 個数:xlCount
  • 平均:xlAverage
  • 最大:xlMax
  • 最小:xlMin
  • 積:xlProduct
  • 重複しない値の数:xlCountNums
  • 標本標準偏差:xlStDev
  • 標準偏差:xlStDevP
  • 標本分散:xlVar
  • 分散:xlVarP

作成したい集計の種類を選択して、値フィールドを設定しましょう。

VBAで値フィールドの集計種類を変更する

VBAで、値フィールドの集計種類を変更することもできます。

既に設定されている集計種類を、変更してみます。

「合計」を「平均」に変更する

値フィールドの集計種類を、「合計」から「平均」に変更するVBAコードは、こんな感じです。

Sub TEST9()
    
    With ActiveSheet.PivotTables(1).PivotFields("合計 / 売上")
        .Function = xlAverage '「平均」に変更
    End With
    
End Sub

「.PivotFields("合計 / 売上")」という感じで、既に設定されている値フィールドの名前を、設定する必要があります。

値フィールドの集計種類が「合計」のピボットテーブルを用意します。

「合計」のピボットテーブル

値フィールドの集計種類が「合計」のピボットテーブルを用意

では、VBAコードを実行してみます。

「平均」に変更できた

値フィールドの集計種類を「平均」に変更する

値フィールドの集計種類を「平均」に変更できました。

「平均」を「合計」に変更する

値フィールドの集計種類を、「平均」から「合計」に変更するVBAコードは、こんな感じです。

Sub TEST10()
    
    With ActiveSheet.PivotTables(1).PivotFields("平均 / 売上")
        .Function = xlSum '「合計」に変更
    End With
    
End Sub

値フィールドの集計種類が「平均」のピボットテーブルを用意します。

「平均」のピボットテーブル

値フィールドの集計種類が「平均」のピボットテーブルを用意

では、VBAコードを実行してみます。

「合計」に変更できた

値フィールドの集計種類を「合計」に変更する

値フィールドの集計種類を「合計」に変更できました。

こんな感じで、VBAを使って、既に設定されているフィールドを、変更することもできます。

ただ、初期の状態から作成した方が簡単なので、変更するのではなく、初期の状態から作成していきましょう。

おわりに

この記事では、VBAでピボットテーブルを作成する方法について、ご紹介しました。

VBAでピボットテーブルを作成するときも、通常の手順と同じで次のように作成します。

  • 元データからピボットテーブルを作成
  • フィールドを設定する

という流れです。

ピボットテーブルを大量に作成する必要がある場合は、VBAを活用していきましょう。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

サイト内を検索する

↓キーワードを入力する

アーカイブから探す