第93回.ピボットテーブル(PivotTable)

ピボットテーブルをマクロVBAで操作する事が良いかどうか、少々疑問な部分もあります。
しかし、ピボットテーブル(PivotTable)はエクセルでは必須機能になりますので、
少なくとも、ピボットテーブルの基本くらいは知っておくべきでしょう。
その後は文法解説は少なめにして、マクロ記録で作成されたVBAをからスタートし、
実践的に使い回せるビボットテーブルのVBAコードを作成しつつ説明をしていきます。
ピボットテーブル(PivotTable)を構成するオブジェクト群
ピボットテーブル関連のプロパティ、メソッド一覧
そして、決してこれで全てではありません>
さらに下位のオブジェクは他にもたくさんあります。
これらを覚えることはほぼ無理ですし、覚えることに意味はありません。
マクロの記録で自動記録された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の違いについて
結局、2010も2003もほぼ同じになりました。
大きく違いうのは、
と
PivotCaches.Add
と
DefaultVersion:=xlPivotTableVersion10
非表示メンバーとなっても、Addメソッドは2010でも使用可能となっています。
さすがにもう2003は必要無いと思いますのでで、2010の記述を使えば良いでしょう。
ピボットテーブル(PivotTable)のマクロ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 ・・・新着記事一覧を見る
列全体を指定する時のRangeとColumnsの違い|ツイッター出題回答 (2023-09-24)
シートのActiveXチェックボックスの指定方法|ツイッター出題回答 (2023-09-24)
ByRef引数の型が一致しません。|ツイッター出題回答 (2023-09-22)
シートコピー後のアクティブシートは何か|ツイッター出題回答 (2023-09-19)
Excel関数の引数を省略した場合について|ツイッター出題回答 (2023-09-14)
セル個数を返すRange.CountLargeプロパティとは|VBA技術解説(2023-09-08)
記号を繰り返してグラフ作成(10単位で折り返す)|ツイッター出題回答 (2023-08-28)
シートを削除:不定数のシート名に対応|VBAサンプル集(2023-08-24)
ランクによりボイントを付ける(同順位はポイントを分割)|ツイッター出題回答 (2023-08-22)
OneDrive使用時のThisWorkbook.Pathの扱い方|VBA技術解説(2023-07-26)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.繰り返し処理(For Next)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.マクロとは?VBAとは?VBAでできること|VBA入門
7.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
8.並べ替え(Sort)|VBA入門
9.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
10.条件分岐(IF)|VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。