第93回.ピボットテーブル（PivotTable）
ピボットテーブルをマクロVBAで操作する事が良いかどうか、少々疑問な部分もあります。
しかし、ピボットテーブル（PivotTable）はエクセルでは必須機能になりますので、
少なくとも、ピボットテーブルの基本くらいは知っておくべきでしょう。
その後は文法解説は少なめにして、マクロ記録で作成されたVBAをからスタートし、
実践的に使い回せるビボットテーブルのVBAコードを作成しつつ説明をしていきます。
ピボットテーブル（PivotTable）を構成するオブジェクト群
PivotCacheオブジェクトのコレクション
ピボットテーブル レポートのキャッシュ メモリを表します。
PivotTableオブジェクトのコレクション
ワークシートにあるピボットテーブル レポートを表します。
PivotFieldオブジェクトのコレクション
ピボットテーブル レポートのフィールドを表します。
これらのコレクション、オブジェクトに、それぞれのプロパティとメソッドがあります。
ピボットテーブル関連のプロパティ、メソッド一覧
そして、決してこれで全てではありません>
さらに下位のオブジェクは他にもたくさんあります。
これらを覚えることはほぼ無理ですし、覚えることに意味はありません。
プロパティの設定値やメソッドの引数について知りたい場合は、
マクロの記録で自動記録されたVBAコードを参考にするのが最も早く簡単でしょう。
以下では、実践的なサンプでの説明をしていきます。
実践のVBAコードを読むことで、理解を深めて下さい。
ピボットテーブル関連のプロパティ、メソッド一覧
・PivotCachesコレクション ・・・ PivotCacheオブジェクトのコレクション ・PivotCacheオブジェクト ・・・ ピボットテーブル レポートのキャッシュ メモリを表します ・PivotTablesコレクション ・・・ PivotTableオブジェクトのコレクション ・PivotTableオブジェクト ・・・ ワークシートにあるピボットテーブル レポートを表します ・PivotFieldsコレクション ・・・ PivotFieldオブジェクトのコレクション ・PivotFieldオブジェクト ・・・ ピボットテーブル レポートのフィールドを表します
上の一覧をご覧いただければわかりますが、膨大な量のプロパティ、メソッドがあります。そして、決してこれで全てではありません>
さらに下位のオブジェクは他にもたくさんあります。
これらを覚えることはほぼ無理ですし、覚えることに意味はありません。
マクロの記録で自動記録されたVBAコードを参考にするのが最も早く簡単でしょう。
実践のVBAコードを読むことで、理解を深めて下さい。
A1セルからC10セルにデータが入っている表があるとします。
Excel2010のピボットテーブル（PivotTable）のマクロ記録
Sub
Macro1()
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=
_
"Sheet1!R1C1:R10C3",
Version:=xlPivotTableVersion14).CreatePivotTable
_
TableDestination:="Sheet4!R3C1", TableName:="ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1",
DefaultVersion
_
:=xlPivotTableVersion14
Sheets("Sheet4").Select
With
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("担当")
.Orientation = xlRowField
.Position = 1
End With
With
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("分類")
.Orientation =
xlColumnField
.Position = 1
End
With
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").AddDataField
ActiveSheet.PivotTables( _
"ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("金額"), "合計 / 金額",
xlSum
End Sub
このまま使えなくはありませんが、
シート名やピボットテーブル名が固定になっていますので、
整理して、使いまわせるように変更します。
"Sheet1!R1C1:R10C3", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1", DefaultVersion _
:=xlPivotTableVersion14
PivotCachesの作成と、CreatePivotTableでのピボットテーブルの作成を同時に行っている為です。
そこで、下のマクロでは、これを2段に分けて、
それぞれのオブジェクト変数に入れて使っています。
Sub Macro1()
Dim ws As Worksheet
Dim pvc As PivotCache
Dim
pvt As PivotTable
Set ws = Sheets.Add
Set pvc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="Sheet1!R1C1:R10C3",
_
Version:=xlPivotTableVersion14)
Set pvt = pvc.CreatePivotTable(TableDestination:=ws.Name & "!R3C1",
_
TableName:="ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1",
_
DefaultVersion:=xlPivotTableVersion14)
With
pvt
With .PivotFields("担当")
.Orientation =
xlRowField
.Position = 1
End With
With
.PivotFields("分類")
.Orientation = xlColumnField
.Position =
1
End With
.AddDataField .PivotFields("金額"), "合計 / 金額",
xlSum
End With
End Sub
だいぶ理解しやすいのではないかと思います。
ブックにピボットキャッシュを作成しています。
ピボットキャッシュにピボットテーブルを作成しています。
Excel2003のピボットテーブル（PivotTable）のマクロ記録
Sub
Macro2()
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
"Sheet1!R1C1:R10C3").CreatePivotTable
TableDestination:="", TableName:= _
"ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1",
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
With
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("担当")
.Orientation =
xlRowField
.Position = 1
End With
With
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("分類")
.Orientation =
xlColumnField
.Position = 1
End
With
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").AddDataField
ActiveSheet.PivotTables( _
"ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("金額"), "合計 / 金額",
xlSum
End Sub
こちらの方が2010よりは理解しやすいかもしれません。
このまま使えなくはありませんが、
シート名やピボットテーブル名が固定になっていますので、
整理して、使いまわせるように変更します。
"Sheet1!R1C1:R10C3").CreatePivotTable TableDestination:="", TableName:= _
"ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1", DefaultVersion:=xlPivotTableVersion10
PivotCachesの作成と、CreatePivotTableでのピボットテーブルの作成を同時に行っている為です。
それぞれのオブジェクト変数に入れて使っています。
Sub Macro2()
Dim ws As Worksheet
Dim pvc As PivotCache
Dim pvt As PivotTable
Set ws = Sheets.Add
Set pvc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="Sheet1!R1C1:R10C3")
Set pvt =
pvc.CreatePivotTable(TableDestination:=ws.Name & "!R3C1",
_
TableName:="ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1",
_
DefaultVersion:=xlPivotTableVersion10)
With
pvt
With .PivotFields("担当")
.Orientation =
xlRowField
.Position = 1
End With
With
.PivotFields("分類")
.Orientation = xlColumnField
.Position =
1
End With
.AddDataField .PivotFields("金額"), "合計 / 金額",
xlSum
End With
End Sub
だいぶ理解しやすいのではないかと思います。
ブックにピボットキャッシュを作成しています。
ピボットキャッシュにピボットテーブルを作成しています。
Excel2010とExcel2003のピボットテーブルVBAの違いについて
上記のExcel2010とExcel2003のマクロ記録から作成したVBAサンプルコードは、
結局、2010も2003もほぼ同じになりました。
大きく違いうのは、
PivotCaches.Create
と
PivotCaches.Add
この部分と、
DefaultVersion:=xlPivotTableVersion14
と
DefaultVersion:=xlPivotTableVersion10
PivotCachesのAddメソッドは、Excel2010では非表示メンバーとなっています。
非表示メンバーとなっても、Addメソッドは2010でも使用可能となっています。
Excel2003がまだ多く使われていたころであれば、2003の記述をお勧めするところですが、
さすがにもう2003は必要無いと思いますのでで、2010の記述を使えば良いでしょう。
結局、2010も2003もほぼ同じになりました。
大きく違いうのは、
と
PivotCaches.Add
と
DefaultVersion:=xlPivotTableVersion10
非表示メンバーとなっても、Addメソッドは2010でも使用可能となっています。
さすがにもう2003は必要無いと思いますのでで、2010の記述を使えば良いでしょう。
ピボットテーブル（PivotTable）のマクロVBA実践例
VBAでピボットテーブルを扱う時は、事前にピボットテーブルを作成しておき、
必要に応じて、その部分の処理のみVBAで行うようにすることをお勧めします。
ピボットのデータソースの変更
ピボットの更新
必要に応じて、その部分の処理のみVBAで行うようにすることをお勧めします。
With ActiveSheet
.PivotTables("ピボット1").ChangePivotCache ActiveWorkbook.PivotCaches.
_
Create(SourceType:=xlDatabase, SourceData:=.Range("A1").CurrentRegion)
End With
ピボットの更新
ActiveSheet.PivotTables("ピボット1").PivotCache.Refresh
同じテーマ「マクロVBA入門」の記事
第91回.条件付き書式（FormatCondition）
第126回.入力規則（Validation）
第92回.名前定義（Names）
第93回.ピボットテーブル（PivotTable）
第94回.コメント（Comment）
第95回.ハイパーリンク（Hyperlink）
第96回.グラフ（Chart）
第97回.図形オートシェイプ（Shape）
第136回.フォームコントロール
第137回.ActiveXコントロール
第98回.Findメソッド（Find,FindNext,FindPrevious）
新着記事NEW ・・・新着記事一覧を見る
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
累計を求める数式あれこれ｜エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE｜エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。｜エクセル雑感(2023-12-28)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.ひらがな⇔カタカナの変換｜エクセル基本操作
5.繰り返し処理（For Next)｜VBA入門
6.変数宣言のDimとデータ型｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.Findメソッド（Find,FindNext,FindPrevious）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。