大体でIT

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

大体でIT

Excel VBAで、ピボットテーブルの値や範囲を取得する方法について、ご紹介します。「ラベル」や「フィールドの値」、「ピボットテーブル全体」を取得することができます。出力用の表として、ピボットテーブルの値を転記して使うと、便利です。ピボットテーブルの操作を自動化していきましょう。

はじめに

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

取得するピボットテーブルの値は、次の種類があります。

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

という感じです。

ピボットテーブルは、集計用のツールです。

表に書式設定などをする際は、ピボットテーブルで作成した値を、別セルに転記して使うと効率的で、応用が利きます。

データ解析を効率的にするために、ピボットテーブルの操作を自動化していきましょう。

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

この記事で紹介すること

  • VBAでピボットテーブルの値を取得する方法

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

目次

VBAコードまとめ

ピボットテーブルの値を取得する方法について、VBAコードをまとめています。

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

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

'ラベルの「項目」を取得
Debug.Print ActiveSheet.PivotTables(1).PivotFields("商品").PivotItems(2) '「商品」ラベルの「2」項目目

'「ラベル」をループして取得
'「For」を使う場合
For i = 1 To ActiveSheet.PivotTables(1).PivotFields("商品").PivotItems.Count
    '「ラベル」を取得
    Debug.Print ActiveSheet.PivotTables(1).PivotFields("商品").PivotItems(i).Value
Next
'「For Each」を使う場合
For Each A In ActiveSheet.PivotTables(1).PivotFields("商品").PivotItems
    '「ラベル」を取得
    Debug.Print A.Value
Next

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

'「全ての値」を取得
ActiveSheet.PivotTables(1).DataBodyRange.Select

'「値」を個別に取得
Debug.Print ActiveSheet.PivotTables(1).GetData("いちご 名古屋") '「いちご」の「名古屋」の値
Debug.Print ActiveSheet.PivotTables(1).GetData("いちご") '「いちご」の総計
Debug.Print ActiveSheet.PivotTables(1).GetData("名古屋") '「名古屋」の総計
Debug.Print ActiveSheet.PivotTables(1).GetData("") '「総計」の「総計」

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

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

'範囲を絞って値を取得
With ActiveSheet.PivotTables(1).TableRange1
    A = .Resize(.Rows.Count - 1).Offset(1, 0)
End With
Range("A11").Resize(UBound(A, 1), UBound(A, 2)) = A '値を転記

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

VBAでピボットテーブルのラベルを取得する

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

取得する箇所

取得したい箇所は、こちらです。

「ラベル」を取得したい

ピボットテーブルのラベルを取得する

では、取得してみます。

ラベルを取得する

ピボットテーブルのラベルを取得してみます。

「商品」ラベルを取得するVBAコードは、こんな感じになります。

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

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

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

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

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

「支店」ラベルを取得するVBAコードは、こんな感じになります。

Sub TEST2()
    
    '「支店」ラベルを取得
    ActiveSheet.PivotTables(1).PivotFields("支店").DataRange.Select
    
End Sub

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

「支店」ラベルを取得できた

VBAでピボットテーブルの支店ラベルを取得できた

VBAで、ピボットテーブルの「支店」ラベルを取得できました。

VBAでピボットテーブルのラベルの一部を取得する

VBAで、ピボットテーブルの「ラベルの一部」を取得してみます。

取得する箇所

取得したい箇所は、こちらです。

「ラベルの項目」を取得したい

ピボットテーブルのラベルの項目を取得する

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

ラベルの一部を取得する

ラベルの一部を取得してみます。

「商品」ラベルの「2項目目」を取得

「商品」ラベルの「2項目目」を取得するVBAコードは、こんな感じになります。

Sub TEST3()
    
    '「商品」ラベルの「2」項目目を取得
    Debug.Print ActiveSheet.PivotTables(1).PivotFields("商品").PivotItems(2)
    
End Sub

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

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

VBAでピボットテーブルの商品ラベルの2項目目を取得できました。

「支店」ラベルの「3項目目」を取得

「支店」ラベルの「3項目目」を取得するVBAコードは、こんな感じになります。

Sub TEST4()
    
    '「支店」ラベルの「3」項目目を取得
    Debug.Print ActiveSheet.PivotTables(1).PivotFields("支店").PivotItems(3)
    
End Sub

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

VBAでピボットテーブルの支店ラベルの3項目目を取得できた

VBAでピボットテーブルの支店ラベルの3項目目を取得できました。

VBAでピボットテーブルのラベルをループして取得する

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

取得する箇所

取得したい箇所は、ラベルのところです。

ラベルを「ループ」して取得したい

ピボットテーブルのラベルをループして取得する

ピボットテーブルのラベルをループして取得してみます。

ラベルをループして取得する(Forを使う場合)

「For文」を使って、ラベルをループして取得してみます。

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

「商品」ラベルをループして取得するVBAコードです。

Sub TEST5()
    
    '「商品」ラベルをループ
    For i = 1 To ActiveSheet.PivotTables(1).PivotFields("商品").PivotItems.Count
        '「ラベル」を取得
        Debug.Print ActiveSheet.PivotTables(1).PivotFields("商品").PivotItems(i).Value
    Next
    
End Sub

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

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

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

「支店」ラベルをループして取得

「支店」ラベルをループして取得するVBAコードです。

Sub TEST6()
    
    '「支店」ラベルをループ
    For i = 1 To ActiveSheet.PivotTables(1).PivotFields("支店").PivotItems.Count
        '「ラベル」を取得
        Debug.Print ActiveSheet.PivotTables(1).PivotFields("支店").PivotItems(i).Value
    Next
    
End Sub

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

VBAでピボットテーブルの支店ラベルをループして取得できた

VBAで、ピボットテーブルの「支店」ラベルを、ループして取得できました。

ラベルをループして取得する(For Eachを使う場合)

「For Each」を使って、ラベルをループして取得してみます。

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

「商品」ラベルをループして取得するVBAコードです。

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

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

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

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

「支店」ラベルをループして取得

「支店」ラベルをループして取得するVBAコードです。

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

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

VBAでピボットテーブルの支店ラベルをループして取得できた

VBAで、ピボットテーブルの「支店」ラベルを、ループして取得できました。

VBAでピボットテーブルの値を取得する

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

取得する箇所 

取得したい箇所は、「値フィールド」と「総計を含む値フィールド」です。

「値フィールド」を取得したい

ピボットテーブルの値フィールドを取得する
ピボットテーブルの総計を含む値フィールドを取得する

では、値を取得してみます。

値フィールドを取得する

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

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

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

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

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

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

すべての値を取得する

「総計を含む」値フィールドを取得するVBAコードは、こんな感じです。

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

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

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

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

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

VBAでピボットテーブルの値を個別に取得する

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

取得する箇所

取得したい箇所は、こちらです。

値を「個別」に取得したい

ピボットテーブルの値を個別に取得する

ピボットテーブルの値を個別に取得してみます。

値フィールドを取得する

値フィールドの「値」を取得するVBAコードは、こんな感じです。

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

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

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

VBAを使ってピボットテーブルで「いちご」の「名古屋」の値を取得できた

VBAを使って、ピボットテーブルで「いちご」の「名古屋」の値を取得できました。

「総計」を取得する

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

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

「いちご」の「総計」の値を取得するVBAコードは、こんな感じです。

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

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

VBAを使ってピボットテーブルで「いちご」の「総計」の値を取得できた

「いちご」の「総計」の値を取得できました。

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

「名古屋」の「総計」の値を取得するVBAコードは、こんな感じです。

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

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

VBAを使ってピボットテーブルで「名古屋」の「総計」の値を取得できた

「名古屋」の「総計」の値を取得できました。

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

「総計」の「総計」を取得するVBAコードは、こんな感じです。

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

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

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

VBAを使ってピボットテーブルで「総計」の「総計」の値を取得できた

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

VBAでピボットテーブル全体を取得する

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

取得する箇所

取得したい箇所は、ピボットテーブル全体です。

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

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

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

全体を取得する

ピボットテーブル全体を取得するVBAコードは、こんな感じです。

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

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

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

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

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

フィルターフィールドがある場合

フィルターフィールドを含むピボットテーブル全体を、取得するVBAコードは、こんな感じです。

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

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

「フィルターフィールドを含むすべて」を取得できた

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

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

VBAでピボットテーブルの値を転記する

VBAでピボットテーブルの値を転記してみます。

ピボットテーブル全体を転記する

ピボットテーブル全体を転記するVBAコードは、こんな感じになります。

Sub TEST17()
    
    'ピボットテーブル全体を取得
    A = ActiveSheet.PivotTables(1).TableRange1
    
    '値を転記
    Range("A11").Resize(UBound(A, 1), UBound(A, 2)) = A
    
End Sub

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

「ピボットテーブル全体」を転記できた

VBAでピボットテーブル全体を転記できた

ピボットテーブル全体を転記できました。

範囲を絞って転記する

ピボットテーブルの範囲を絞って転記するVBAコードは、こんな感じです。

Sub TEST18()
    
    '範囲を絞ってピボットテーブル全体を取得
    With ActiveSheet.PivotTables(1).TableRange1
        A = .Resize(.Rows.Count - 1).Offset(1, 0)
    End With
    
    '値を転記
    Range("A11").Resize(UBound(A, 1), UBound(A, 2)) = A
    
End Sub

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

「範囲を絞って」転記できた

VBAでピボットテーブルの範囲を絞って転記できた

VBAで、ピボットテーブルの範囲を絞って転記できました。

ちょっと複雑なので、順を追って解説します。

Resizeで1行減らす

Resizeで1行減らします。

Sub TEST19()
    
    
    With ActiveSheet.PivotTables(1).TableRange1
        '1行減らす
        .Resize(.Rows.Count - 1).Select
    End With
    
End Sub

実行します。

Resizeで1行だけ範囲を小さくできた

Resizeで1行だけ範囲を小さくできました。

Offsetで1行だけ下に移動する

Offsetで1行だけ下に移動します。

Sub TEST20()
    
    With ActiveSheet.PivotTables(1).TableRange1
        '1行減らして、1行だけ下に移動する
        .Resize(.Rows.Count - 1).Offset(1, 0).Select
    End With
    
End Sub

実行します。

Offsetで1行だけ下に移動できた

Offsetで1行だけ下に移動できました。

範囲を指定して貼り付け

範囲を指定して貼り付けます。

Sub TEST21()
    
    '範囲を絞って値を取得
    With ActiveSheet.PivotTables(1).TableRange1
        A = .Resize(.Rows.Count - 1).Offset(1, 0)
    End With
    
    '値を転記
    Range("A11").Resize(UBound(A, 1), UBound(A, 2)) = A
    
End Sub

実行します。

範囲を指定して貼り付けできた

範囲を指定して貼り付けできました。

こんな感じで、ピボットテーブルで作成した値を、別セルに自由に転記することができます。

おわりに

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

取得するピボットテーブルの値は、次の種類があります。

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

という感じです。

ピボットテーブルは、集計用のツールです。

表に書式設定などをする際は、ピボットテーブルで作成した値を、別セルに転記して使うと効率的で、応用が利きます。

データ解析を効率的にするために、ピボットテーブルの操作を自動化していきましょう。

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

関連する記事から探す

カテゴリから探す

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

アーカイブから探す