皆様こんにちは、ノグチです。
ピボットテーブルは、手動で使ってもとっても便利ですよね。
じゃあそれを自動化できたらもっと便利に使えるのでは？ということで、今回から何度かに分けて、ピボットテーブルの自動化についてご紹介していきたいと思います。
今回は、VBAでのピボットテーブル操作例と、ピボットテーブルをVBAで操作するためのめの第一歩、ピボットテーブルオブジェクトのご紹介です！
ピボットテーブルの基本的な作り方と操作方法は、以下の記事で詳しく説明されていますので、併せてご覧くださいね。
ピボットテーブル自動化の代表例
ピボットテーブルの操作自動化には、VBAを使います。
ピボットテーブル操作をVBAで自動化する代表例には、以下のようなものがあります。
- データソースからピボットテーブルの生成
指定したワークシートや、外部のデータをデータソースにして、ピボットテーブルの作成を行います。
VBAでデータソースを加工するコードの最後に記述しておけば、ワンクリックでデータソースの準備からピボットテーブル作成までできるようになりますよ。
- ピボットテーブルの自動更新
一度作成しておいたピボットテーブルのデータを更新します。
手動操作の場合の、ピボットテーブルにカーソルを合わせて右クリック→「更新」をクリックするか、ツールバーの 「更新」ボタンをクリックする動作をVBAがやってくれます。
ピボットテーブルのデータソースが変わったのに、更新ボタン押し忘れる、ということもなくなりますよ。
- ピボットテーブルのクリア
ピボットテーブルの表示項目やデータソースをリセットします。
ピボットテーブルを手動で作成したとき、枠だけができますよね？あの状態にします。
レポート分析していて、レポートの切り口を変えたいときなどに便利です。
もちろん、上記で挙げた処理以外にも、色々なことができますよ。
では、ピボットテーブル操作自動化の方法を見ていきましょう！
ピボットテーブルをオブジェクト型変数にセットする
オブジェクトとは？
まず、ピボットテーブルをVBAで操作するための第一歩、オブジェクトについてご紹介します。
ピボットテーブルに限らず、VBAで何らかの操作を行う場合、オブジェクトが必要です。
オブジェクトとは、VBAで操作する対象のことです。
VBAのコードは、「何に対して、どのような操作(処理)をするのか」を記述する必要がありますが、この「何に対して」がオブジェクトにあたります。
ピボットテーブルをVBAで操作するためには、操作したいピボットテーブルが操作の対象になるようにコードを記述する必要があります。
そうすることで、「このピボットテーブルを操作するんだな」とVBAに認識させることができるわけです。
今回使用するピボットテーブル
さて、今回の説明用の例として、数か月の仕入実績リストをデータソースにして作成したピボットテーブルを用意しました。
ピボットテーブルがあるワークシートには「ピボットテーブル」、ピボットテーブルには、「仕入実績テーブル」という名前をそれぞれ付けてあります。
ピボットテーブルオブジェクトの基本的な記述
まず、特定のピボットテーブルをVBAで操作する場合、下記のように記述します。
上記の”ピボットテーブル”には、ピボットテーブル名を指定するので、例でお見せしたピボットテーブルをVBAで操作したい場合は、このように記述します。
ActiveWorkbook.Worksheets("ピボットテーブル").PivotTables("仕入実績テーブル")
これで、”ピボットテーブルシート”にある”仕入実績テーブル”をオブジェクトとしてVBAで操作できるようになるのです。
PivotTableオブジェクト変数にピボットテーブルをセットする
すでにお気付きかもしれませんが、特定のピボットテーブルをVBAで操作するためには、「どこのブックの、どこのシートにあるピボットテーブルなのか？」までコードに記述しなければいけません。
基本的な記述方法は前述した通りですが、毎回記述するには少々長いですよね。
そこで、PivotTableオブジェクト型変数を用いてコードを記述する方法があります。
PivotTableオブジェクト型変数はピボットテーブル専用の変数で、ここにVBAで操作したいピボットテーブルの在処を格納しておくことができるものです。
PivotTableオブジェクト型変数を使う方法は、他の変数と同様、変数の宣言をして、
宣言した変数に以下のようにセットします。
上記をコードで記述してみたものがこちら。
Sub PivotTable()
Dim pivot As PivotTable 'ピボットテーブルのオブジェクト変数
Set pivot = ActiveWorkbook.Worksheets("ピボットテーブル").PivotTables("仕入実績テーブル")
End Sub
“pivot”という名前で宣言したピボットテーブルオブジェクト型変数に、ピボットテーブルの在処をセットしています。
オブジェクト変数に格納したピボットテーブル名を表示する
では、PivotTableオブジェクトにセットしたピボットテーブルを、例のピボットテーブルを使って動かしてみましょう。
セットしたオブジェクト名をメッセージボックスに表示できるように、Nameプロパティを使ってコードの最後に「MsgBox pivot.Name」を追加して動かしてみると…
Sub PivotTable()
Dim pivot As PivotTable 'ピボットテーブルのオブジェクト変数
Set pivot = ActiveWorkbook.Worksheets("ピボットテーブル").PivotTables("仕入実績テーブル")
MsgBox pivot.Name
End Sub
ちゃんとPivotTableオブジェクトにセットしたピボットテーブルがキャッチされていますね。
これで、「今アクティブになっているブックの、”ピボットテーブル”というシートにある”仕入実績テーブル”」までがVBAで操作できるようになりました！
最後に
さぁ、これで、PivotTableをVBAで操作するための第一歩、オブジェクトのセットができました。
次回から、このオブジェクトにプロパティ、メソッドを組み合わせて、VBAでのピボットテーブル操作方法をご紹介していきます。
それでは、最後までお読みいただきありがとうございました！
