－ちょっと使えるネタを紹介－
Excel 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コードは、こんな感じになります。
Sub TEST1()
'「商品」ラベルを取得する
ActiveSheet.PivotTables(1).PivotFields("商品").DataRange.Select
End Sub
では、VBAコードを実行してみます。
VBAで、ピボットテーブルの「商品」ラベルを取得できました。
「支店」ラベルを取得するVBAコードは、こんな感じになります。
Sub TEST2()
'「支店」ラベルを取得
ActiveSheet.PivotTables(1).PivotFields("支店").DataRange.Select
End Sub
では、VBAコードを実行してみます。
VBAで、ピボットテーブルの「支店」ラベルを取得できました。
VBAで、ピボットテーブルの「ラベルの一部」を取得してみます。
取得したい箇所は、こちらです。
では、ピボットテーブルのラベルの項目を取得してみます。
ラベルの一部を取得してみます。
「商品」ラベルの「2項目目」を取得するVBAコードは、こんな感じになります。
Sub TEST3()
'「商品」ラベルの「2」項目目を取得
Debug.Print ActiveSheet.PivotTables(1).PivotFields("商品").PivotItems(2)
End Sub
では、VBAコードを実行してみます。
VBAでピボットテーブルの商品ラベルの2項目目を取得できました。
「支店」ラベルの「3項目目」を取得するVBAコードは、こんな感じになります。
Sub TEST4()
'「支店」ラベルの「3」項目目を取得
Debug.Print ActiveSheet.PivotTables(1).PivotFields("支店").PivotItems(3)
End Sub
では、VBAコードを実行してみます。
VBAでピボットテーブルの支店ラベルの3項目目を取得できました。
VBAで、ピボットテーブルのラベルをループして取得してみます。
取得したい箇所は、ラベルのところです。
ピボットテーブルのラベルをループして取得してみます。
「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コードです。
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で、ピボットテーブルの「支店」ラベルを、ループして取得できました。
「For Each」を使って、ラベルをループして取得してみます。
「商品」ラベルをループして取得するVBAコードです。
Sub TEST7()
'「商品」ラベルをループ
For Each A In ActiveSheet.PivotTables(1).PivotFields("商品").PivotItems
'「ラベル」を取得
Debug.Print A.Value
Next
End Sub
では、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コードは、こんな感じです。
Sub TEST9()
'「値」フィールドを取得
ActiveSheet.PivotTables(1).PivotFields("合計 / 売上").DataRange.Select
End Sub
では、VBAコードを実行してみます。
VBAで、ピボットテーブルの「値フィールド」を取得できました。
「総計を含む」値フィールドを取得するVBAコードは、こんな感じです。
Sub TEST10()
'全ての値を取得
ActiveSheet.PivotTables(1).DataBodyRange.Select
End Sub
では、VBAコードを実行してみます。
VBAで、ピボットテーブルの「総計を含む値フィールド」を取得できました。
VBAで、ピボットテーブルの値を「個別」に取得してみます。
取得したい箇所は、こちらです。
ピボットテーブルの値を個別に取得してみます。
値フィールドの「値」を取得するVBAコードは、こんな感じです。
Sub TEST11()
'「いちご」の「名古屋」の値を取得
Debug.Print ActiveSheet.PivotTables(1).GetData("いちご 名古屋")
End Sub
では、VBAコードを実行してみます。
VBAを使って、ピボットテーブルで「いちご」の「名古屋」の値を取得できました。
「総計」の値を取得してみます。
「いちご」の「総計」の値を取得するVBAコードは、こんな感じです。
Sub TEST12()
'「いちご」の総計を取得
Debug.Print ActiveSheet.PivotTables(1).GetData("いちご")
End Sub
では、VBAコードを実行してみます。
「いちご」の「総計」の値を取得できました。
「名古屋」の「総計」の値を取得するVBAコードは、こんな感じです。
Sub TEST13()
'「名古屋」の総計を取得
Debug.Print ActiveSheet.PivotTables(1).GetData("名古屋")
End Sub
では、VBAコードを実行してみます。
「名古屋」の「総計」の値を取得できました。
「総計」の「総計」を取得するVBAコードは、こんな感じです。
Sub TEST14()
'「総計」の「総計」を取得
Debug.Print ActiveSheet.PivotTables(1).GetData("")
End Sub
では、VBAコードを実行してみます。
「総計」の「総計」の値を取得できました。
VBAでピボットテーブル全体を取得してみます。
取得したい箇所は、ピボットテーブル全体です。
では、ピボットテーブル全体を取得してみます。
ピボットテーブル全体を取得するVBAコードは、こんな感じです。
Sub TEST15()
'ピボットテーブル全体を取得
ActiveSheet.PivotTables(1).TableRange1.Select
End Sub
では、VBAコードを実行してみます。
ピボットテーブル全体を取得できました。
フィルターフィールドを含むピボットテーブル全体を、取得するVBAコードは、こんな感じです。
Sub TEST16()
'フィルターフィールドを含むピボットテーブル全体を取得
ActiveSheet.PivotTables(1).TableRange2.Select
End Sub
では、VBAコードを実行してみます。
フィルターフィールドを含む、ピボットテーブル全体を取得できました。
VBAでピボットテーブルの値を転記してみます。
ピボットテーブル全体を転記するVBAコードは、こんな感じになります。
Sub TEST17()
'ピボットテーブル全体を取得
A = ActiveSheet.PivotTables(1).TableRange1
'値を転記
Range("A11").Resize(UBound(A, 1), UBound(A, 2)) = A
End Sub
では、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で、ピボットテーブルの範囲を絞って転記できました。
ちょっと複雑なので、順を追って解説します。
Resizeで1行減らします。
Sub TEST19()
With ActiveSheet.PivotTables(1).TableRange1
'1行減らす
.Resize(.Rows.Count - 1).Select
End With
End Sub
実行します。
Resizeで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行だけ下に移動できました。
範囲を指定して貼り付けます。
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でピボットテーブルの値を取得する方法について、ご紹介しました。
取得するピボットテーブルの値は、次の種類があります。
という感じです。
ピボットテーブルは、集計用のツールです。
表に書式設定などをする際は、ピボットテーブルで作成した値を、別セルに転記して使うと効率的で、応用が利きます。
データ解析を効率的にするために、ピボットテーブルの操作を自動化していきましょう。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す
カテゴリから見たい項目を探すと便利ですよ。
↓キーワードを入力する