大体でIT

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

大体でIT

Excel VBAで、ピボットテーブルの操作を自動化する方法について、ご紹介します。ピボットテーブルの「作成」から、「フィールドの設定」、「フィルター」、「並び替え」、「グループ化」などの重要なポイントを、網羅的に解説します。便利なピボットテーブルを、VBAを使ってさらに効率的に使っていきましょう。

はじめに

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

ピボットテーブルの「作成」から「フィールドの設定」、「フィルター」、「並び替え」、「グループ化」、「ピボットグラフの作成」など、ピボットテーブルを操作する方法を網羅的にまとめました。

この記事の内容だけで、十分にピボットテーブルの操作を、VBAで自動化できるようになります。

便利なピボットテーブルを、VBAを使ってさらに効率的に使っていきましょう。

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

この記事で紹介すること

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

目次

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

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

作成する

初期状態のピボットテーブルを「作成」するVABコードは、こんな感じになります。

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

元データを用意しておきます。

元データを用意

元データを用意する

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

初期状態のピボットテーブルを「作成」できた

VBAで初期状態のピボットテーブルを「作成」できた

初期状態のピボットテーブルを「作成」できました。

フィールドを設定する

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

Sub TEST2()
    
    'フィールドを設定
    With ActiveSheet.PivotTables(1)
        .PivotFields("商品").Orientation = xlRowField '行フィールドを設定
        .PivotFields("支店").Orientation = xlColumnField '列フィールドを設定
        'フィルターフィールドに設定する場合
        '.PivotFields("支店").Orientation = xlPageField
        .PivotFields("売上").Orientation = xlDataField '値フィールドを設定
    End With
    
End Sub

初期状態のピボットテーブルを用意しておきます。

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

初期状態のピボットテーブルを用意する

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

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

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

ピボットテーブルの「フィールドを設定」できました。

値フィールドの集計の種類について

値フィールドの集計の種類には、「合計」のほかに、「平均」や「個数」があります。

値フィールドの集計の種類を、こちらの記事でまとめています。

ピボットテーブルを削除する

VBAで、ピボットテーブルを削除してみます。

削除する

VBAで、ピボットテーブルを「削除」してみます。

Sub TEST3()
    
    'フィルターフィールドを含めてピボットテーブルを削除
    ActiveSheet.PivotTables(1).TableRange2.ClearContents
    
End Sub

「TableRange2」とすると、「フィルターフィールド」を含むピボットテーブルでも削除することができます。

フィールドを含むピボットテーブルを用意しておきます。

ピボットテーブルを用意

フィールドを含むピボットテーブルを用意する

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

ピボットテーブルを「削除」できた

VBAでフィールドを含むピボットテーブルを「削除」できた

フィールドを含むピボットテーブルを「削除」できました。

その他の削除方法について

ピボットテーブルを削除する方法には、「シートを削除」するなどがあります。

削除する方法の詳細について、こちらでまとめています。

初期化する

VBAでピボットテーブルを「初期化」してみます。

Sub TEST4()
    
    'ピボットテーブルを初期化する
    ActiveSheet.PivotTables(1).ClearTable
    
End Sub

ピボットテーブルを用意しておきます。

ピボットテーブルを用意

ピボットテーブルを用意する

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

ピボットテーブルを「初期化」できた

VBAでピボットテーブルを「初期化」できた

ピボットテーブルを「初期化」できました。

一部のフィールドを初期化する方法について

ピボットテーブル全体だけでなく、一部のフィールドを初期化する方法もあります。

一部のフィールドのみ初期化する方法については、こちらでまとめています。

ピボットテーブルを更新する

VBAで、ピボットテーブルを更新してみます。

元データを変更して更新

VBAで、ピボットテーブルの元データを変更して、更新してみます。

Sub TEST5()
    
    '元データの値を変更
    Worksheets("元データ").Range("C7") = 9999
    '更新する
    ActiveSheet.PivotTables(1).PivotCache.Refresh
    
End Sub

ピボットテーブルを作成しておきます。

ピボットテーブルを作成しておく

ピボットテーブルを作成しておく

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

元データの値が変更されます。

元データの値が変更される

元データの値を変更される

ピボットテーブルを「更新」されます。

ピボットテーブルを「更新」できた

VBAでピボットテーブルを「更新」できた

ピボットテーブルを「更新」できました。

ブック全体の更新について

特定のピボットテーブルだけでなく、ブック全体のピボットテーブルを更新する方法もあります。

ピボットテーブルを更新する方法については、こちらでまとめています。

ピボットテーブルの元データの範囲を変更する

VBAで、ピボットテーブルの「元データの範囲」を変更してみます。

元データにデータを追加して範囲を変更

元データにデータを追加して、VBAで元データの範囲を変更してみます。

Sub TEST6()
    
    '元データの範囲を取得
    Set A = Worksheets("元データ").Range("A1").CurrentRegion
    
    'データソースを変更する
    ActiveSheet.PivotTables(1).ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, A)
        
End Sub

ピボットテーブルを作成しておきます。

ピボットテーブルを用意

元データからピボットテーブルを作成しておく

元データにデータを追加します。

元データにデータを追加

元データにデータを追加する

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

元データの「範囲の変更」ができた

VBAでピボットテーブルの「元データの範囲を変更」できた

ピボットテーブルの「元データの範囲を変更」できました。

「自動」で範囲を変更する

元データの範囲を変更するVBAコードを応用すると、「自動」で範囲を変更することもできます。

ピボットテーブルの更新について、詳細はこちらでまとめています。

ピボットテーブルをフィルターする

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

ラベルをフィルターする

VBAで、ピボットテーブルのラベルを「いちご」でフィルターしてみます。

Sub TEST7()
    
    With ActiveSheet.PivotTables(1)
        .PivotFields("商品").ClearAllFilters 'フィルターを解除
        '「商品」ラベルを「いちご」でフィルター
        .PivotFields("商品").PivotFilters.Add2 xlCaptionEquals, , "いちご"
    End With
    
End Sub

フィルターするのは、ピボットテーブルの「商品」ラベルのところです。

「商品」ラベルをフィルターする

ピボットテーブルのラベルをフィルターしたい

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

「いちご」でフィルターできた

VBAでピボットテーブルのラベルを「いちご」でフィルターできた

ピボットテーブルのラベルを「いちご」でフィルターできました。

フィルター条件の「引数」について

フィルターする際には、「指定の値に等しい」の条件だけでなく、「指定の値を含む」などの条件を設定することができます。

ラベルをフィルターする方法について、詳細はこちらでまとめています。

日付をフィルターする

VBAで、日付を「2021/9/1」~「2021/11/1」でフィルターしてみます。

Sub TEST8()
      
    With ActiveSheet.PivotTables(1)
        'フィルター解除
        .PivotFields("年").ClearAllFilters
        .PivotFields("月").ClearAllFilters
        .PivotFields("日付").ClearAllFilters
        '「2021/9/1」~「2021/11/1」の範囲内でフィルター
        .PivotFields("日付").PivotFilters.Add2 xlDateBetween, , "2021/9/1", "2021/11/1"
    End With
        
End Sub

ピボットテーブルの「日付」ラベルをフィルターします。

「日付」ラベルをフィルターする

ピボットテーブルの日付をフィルターしたい

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

「2021/9/1」~「2021/11/1」でフィルターできた

VBAでピボットテーブルの日付を「2021/9/1」~「2021/11/1」でフィルターできた

ピボットテーブルの日付を「2021/9/1」~「2021/11/1」でフィルターできました。

日付フィルターの「引数」について

日付をフィルターする際には、「指定の範囲内」だけなく、「指定の値より後」などがあります。

日付のフィルターについて、詳細はこちらでまとめています。

値をフィルターする

VBAで、ピボットテーブルの値を「1400以上」でフィルターしてみます。

Sub TEST9()
    
    With ActiveSheet.PivotTables(1)
        .PivotFields("商品").ClearAllFilters 'フィルター解除
        '「商品」ラベルの「値」を「1400以上」でフィルター
        .PivotFields("商品").PivotFilters.Add2 xlValueIsGreaterThanOrEqualTo, .PivotFields("合計 / 売上"), 1400
    End With
    
End Sub

ピボットテーブルの値をフィルターします。

値をフィルターしたい

ピボットテーブルの値をフィルターしたい

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

「1400以上」でフィルターできた

VBAでピボットテーブルの値を「1400以上」でフィルターできた

ピボットテーブルの値を「1400以上」でフィルターできました。

値フィルターの「引数」について

値のフィルターは、「指定の値以上」だけなく、「指定の値より大きい」などでもフィルターができます。

値のフィルターについて、詳細はこちらでまとめています。

ピボットテーブルを並び替えする

VBAで、ピボットテーブルを並び替えしてみます。

「ラベル」を昇順や降順に並び替え

「商品」ラベルを昇順や降順に並び替えしてみます。

「商品」ラベルを「昇順」にする

VBAでピボットテーブルの「商品」ラベルを「昇順」に並べ替えします。

Sub TEST10()
    
    '「商品」ラベルを「昇順」にする
    ActiveSheet.PivotTables(1).PivotFields("商品").AutoSort xlAscending, "商品"
     
End Sub

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

VBAでピボットテーブルの「商品」ラベルを昇順に並べ替えできた

ピボットテーブルの「商品」ラベルを「昇順」に並べ替えできました。

「商品」ラベルを「降順」にする

VBAで、ピボットテーブルの「商品」ラベルを降順に並べ替えしてみます。

Sub TEST11()
    
    '「商品」ラベルを「降順」にする
    ActiveSheet.PivotTables(1).PivotFields("商品").AutoSort xlDescending, "商品"
      
End Sub

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

VBAでピボットテーブルの「商品」ラベルを降順に並べ替えできた

ピボットテーブルの「商品」ラベルを「降順」に並べ替えできました。

その他ラベルでの並び替え

同じように「支店」ラベルでも並び替えができます。

他のラベルを並び替える方法について、詳細はこちらでまとめています。

「値」を昇順や降順に並び替え

「商品」ラベルの「値」を昇順や降順に並び替えしてみます。

「商品」ラベルの「値」を「昇順」にする

ピボットテーブルの「商品」ラベルの「値」を「昇順」に並べ替えします。

Sub TEST12()
    
    '「商品」ラベルの「値」を「昇順」にする
    ActiveSheet.PivotTables(1).PivotFields("商品").AutoSort xlAscending, "合計 / 売上"
 
End Sub

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

VBAでピボットテーブルの「商品」ラベルの「値」を昇順に並べ替えできた

ピボットテーブルの「商品」ラベルの「値」を「昇順」に並べ替えできました。

「商品」ラベルの「値」を「降順」にする

ピボットテーブルの「商品」ラベルの「値」を「降順」に並べ替えしてみます。

Sub TEST13()
        
     '「商品」ラベルの「値」を「降順」にする
    ActiveSheet.PivotTables(1).PivotFields("商品").AutoSort xlDescending, "合計 / 売上"
        
End Sub

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

VBAでピボットテーブルの「商品」ラベルの「値」を降順に並べ替えできた

ピボットテーブルの「商品」ラベルの「値」を「降順」に並べ替えできました。

その他ラベルでの並び替え

同じように「支店」ラベルの「値」でも並び替えができます。

「他のラベルの値」を並び替える方法について、詳細はこちらでまとめています。

ピボットテーブルをグループ化する

VBAで、ピボットテーブルをグループ化してみます。

数値をグループ化する

「点数」ラベルの数値を20点刻みでグループ化してみます。

Sub TEST14()

    With ActiveSheet.PivotTables(1).PivotFields("点数").DataRange.Cells(1, 1)
        .Group Start:=0, End:=100, By:=20 '0~100を、20刻みでグループ化
    End With
    
End Sub

ピボットテーブルを作成しておきます。

ピボットテーブルを作成しておく

ピボットテーブルを作成しておく

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

数値を「20点刻み」でグループ化できた

VBAでピボットテーブルの数値を20点刻みでグループ化できた

ピボットテーブルの数値を「20点刻み」でグループ化できました。

日付をグループ化する

「日付」ラベルの日付を「年」と「月」でグループ化してみます。

Sub TEST15()

    With ActiveSheet.PivotTables(1).PivotFields("日付").DataRange.Cells(1, 1)
        '「年」「月」でグループ化
        '引数⇒開始日、終了日、秒、分、時、日、月、四半期、年
        .Group Start:=True, End:=True, periods:=Array(False, False, False, False, True, False, True)
    End With
    
End Sub

ピボットテーブルを作成しておきます。

ピボットテーブルを作成しておく

ピボットテーブルを作成しておく

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

「年」と「月」でグループ化できた

VBAでピボットテーブルの日付を「年」と「月」でグループ化できた

ピボットテーブルの日付を「年」と「月」でグループ化できました。

その他の日付のグループ化や解除する方法

日付は「年」や「年、月、日」でもグループ化することができます。

日付のグループ化やグループ化の解除について、詳細はこちらでまとめています。

ピボットテーブルの値を取得

VBAで、ピボットテーブルの値を取得してみます。

取得する値は、次の種類があります。

  • ラベル
  • 値フィールド
  • ピボットテーブル全体

では、ピボットテーブルの値を取得してみます。

ラベルを取得する

「商品」ラベルを取得してみます。

Sub TEST16()
        
    '「商品」ラベルを取得する
    ActiveSheet.PivotTables(1).PivotFields("商品").DataRange.Select
    
End Sub

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

「商品」ラベルを取得できた

VBAでピボットテーブルの「商品」ラベルを取得できた

「商品」ラベルを取得できました。

同じように「支店」ラベルも取得できます。

ラベルをループして取得する

「商品」ラベルをループして取得してみます。

Sub TEST17()
    
    '「商品」ラベルをループ
    For Each A In ActiveSheet.PivotTables(1).PivotFields("商品").PivotItems
        '「ラベル」を取得
        Debug.Print A.Value
    Next
    
End Sub

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

「商品」ラベルをループして取得できた

VBAでピボットテーブルの「商品」ラベルをループして取得できた

「商品」ラベルをループして取得できました。

値フィールドを取得する

「値フィールド」を取得してみます。

Sub TEST18()
    
    '「値」フィールドを取得
    ActiveSheet.PivotTables(1).PivotFields("合計 / 売上").DataRange.Select
    
End Sub

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

「値フィールド」を取得できた

VBAでピボットテーブルの「値フィールド」を取得できた

「値フィールド」を取得できました。

全ての値を取得する

「総計を含む値フィールド」を取得してみます。

Sub TEST19()
    
    '全ての値を取得
    ActiveSheet.PivotTables(1).DataBodyRange.Select
    
End Sub

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

「総計を含む値フィールド」を取得できた

VBAでピボットテーブルの「総計を含む値フィールド」を取得できた

「総計を含む値フィールド」を取得できました。

個別に値を取得する

値フィールドの値を「個別」に取得することもできます。

「いちご」で「名古屋」の値を取得

行ラベルが「いちご」で、列ラベルが「名古屋」の値を取得してみます。

Sub TEST20()
        
    '「いちご」の「名古屋」の値を取得
    Debug.Print ActiveSheet.PivotTables(1).GetData("いちご 名古屋")
    
End Sub

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

VBAで行ラベルが「いちご」で、列ラベルが「名古屋」の値を取得できた

行ラベルが「いちご」で、列ラベルが「名古屋」の値を取得できました。

「いちご」の「総計」を取得

行ラベルが「いちご」の「総計」を取得してみます。

Sub TEST21()
    
    '「いちご」の総計を取得
    Debug.Print ActiveSheet.PivotTables(1).GetData("いちご")
    
End Sub

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

VBAで行ラベルが「いちご」の「総計」を取得できた

行ラベルが「いちご」の「総計」を取得できました。

「名古屋」の「総計」を取得

列ラベルが「名古屋」の「総計」を取得してみます。

Sub TEST22()
    
    '「名古屋」の総計を取得
    Debug.Print ActiveSheet.PivotTables(1).GetData("名古屋")
    
End Sub

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

VBAで列ラベルが「名古屋」の「総計」を取得できた

列ラベルが「名古屋」の「総計」を取得できました。

「総計」の「総計」を取得

「総計」の「総計」を取得してみます。

Sub TEST23()
    
    '「総計」の「総計」を取得
    Debug.Print ActiveSheet.PivotTables(1).GetData("")
    
End Sub

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

VBAで「総計」の「総計」を取得できた

「総計」の「総計」を取得できました。

全体を取得する

ピボットテーブル全体を取得することもできます。

「ピボットテーブル全体」を取得

「ピボットテーブル全体」を取得してみます。

Sub TEST24()
    
    'ピボットテーブル全体を取得
    ActiveSheet.PivotTables(1).TableRange1.Select
    
End Sub

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

VBAで「ピボットテーブル全体」を取得できた

「ピボットテーブル全体」を取得できました。

フィルターフィールドを含むピボットテーブル全体を取得

「フィルターフィールドを含むピボットテーブル全体」を取得してみます。

Sub TEST25()
    
    'フィルターフィールドを含むピボットテーブル全体を取得
    ActiveSheet.PivotTables(1).TableRange2.Select
    
End Sub

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

VBAで「フィルターフィールドを含むピボットテーブル全体」を取得できた

「フィルターフィールドを含むピボットテーブル全体」を取得できました。

ピボットテーブルを転記して利用する

ピボットテーブルの値を転記して利用すると、書式設定などが簡単にできます。

ピボットテーブルを転記する方法について、詳細はこちらでまとめています。

ピボットグラフを作成する

VBAで、ピボットグラフを作成してみます。

ピボットグラフを作成する

ピボットグラフを「作成」してみます。

Sub TEST26()
    
    'ピボットテーブルを選択
    ActiveSheet.PivotTables(1).TableRange1.Select
    '集合縦棒を挿入
    ActiveSheet.Shapes.AddChart2(, xlColumnClustered).Select
    
End Sub

ピボットテーブルを作成しておきます。

ピボットテーブルを作成しておく

ピボットテーブルを作成しておく

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

ピボットグラフを「作成」できた

VBAでピボットグラフを「作成」できた

ピボットグラフを「作成」できました。

ピボットグラフの種類を変更

ピボットグラフの種類を「積み上げ縦棒」に「変更」してみます。

Sub TEST27()
    
    '「集合縦棒」に変更
    ActiveSheet.ChartObjects(1).Chart.ChartType = xlColumnStacked
    
End Sub

ピボットグラフを作成しておきます。

ピボットグラフを作成しておく

ピボットグラフを作成しておく

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

「積み上げ縦棒」に「変更」できた

VBAでピボットグラフの種類を積み上げ縦棒に「変更」できた

ピボットグラフの種類を「積み上げ縦棒」に「変更」できました。

その他のピボットグラフの種類

ピボットグラフの種類には、「積み上げ縦棒」だけでなく、「折れ線」や「円」などがあります。

ピボットグラフの種類一覧については、こちらでまとめています。

位置と幅、高さを設定

ピボットグラフの「位置、幅、高さ」を設定してみます。

Sub TEST28()
    
    'グラフを指定
    With ActiveSheet.ChartObjects(1)
        .Left = Range("B11:H19").Left '左の位置
        .Top = Range("B11:H19").Top '上の位置
        .Width = Range("B11:H19").Width '幅
        .Height = Range("B11:H19").Height '高さ
    End With
    
End Sub

ピボットグラフを作成しておきます。

ピボットグラフを作成しておく

ピボットグラフを作成しておく

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

「位置、幅、高さ」を設定できた

VBAでピボットグラフの「位置、幅、高さ」を設定できた

ピボットグラフの「位置、幅、高さ」を設定できました。

ピボットグラフの削除

ピボットグラフを「削除」してみます。

Sub TEST29()
    
    'グラフを削除する
    ActiveSheet.ChartObjects(1).Delete
    
End Sub

ピボットグラフを作成しておきます。

ピボットグラフを作成しておく

ピボットグラフを作成しておく

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

ピボットグラフを「削除」できた

VBAでピボットグラフを「削除」できた

ピボットグラフを「削除」できました。

おわりに

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

ピボットテーブルの「作成」から「フィールドの設定」、「フィルター」、「並び替え」、「グループ化」、「ピボットグラフの作成」などの重要なところをピックアップしました。

この記事の内容だけで、十分にピボットテーブルの操作を、VBAで自動化できるようになります。

便利なピボットテーブルを、VBAを使ってさらに効率的に使っていきましょう。

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

関連する記事から探す

カテゴリから探す

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

アーカイブから探す