エクセルVBAでピボットテーブル操作自動化～ピボットテーブルオブジェクト変数～

皆様こんにちは、ノグチです。

ピボットテーブルは、手動で使ってもとっても便利ですよね。

じゃあそれを自動化できたらもっと便利に使えるのでは？ということで、今回から何度かに分けて、ピボットテーブルの自動化についてご紹介していきたいと思います。

今回は、VBAでのピボットテーブル操作例と、ピボットテーブルをVBAで操作するためのめの第一歩、ピボットテーブルオブジェクトのご紹介です！

ピボットテーブルの基本的な作り方と操作方法は、以下の記事で詳しく説明されていますので、併せてご覧くださいね。

データ分析をするならとりあえず作る！エクセルピボットテーブルの作り方

とあるラーメン屋さんのアンケートを題材にしてデータ分析を進めていきます。まずはデータ分析においてとりあえず作っておけ的な存在であるピボットテーブルについてその作り方を解説していきますね。

ピボットテーブル自動化の代表例
ピボットテーブルをオブジェクト型変数にセットする
最後に
連載目次：エクセルVBAでピボットテーブルを操作する第一歩

ピボットテーブル自動化の代表例

ピボットテーブルの操作自動化には、VBAを使います。

ピボットテーブル操作をVBAで自動化する代表例には、以下のようなものがあります。

データソースからピボットテーブルの生成
指定したワークシートや、外部のデータをデータソースにして、ピボットテーブルの作成を行います。
VBAでデータソースを加工するコードの最後に記述しておけば、ワンクリックでデータソースの準備からピボットテーブル作成までできるようになりますよ。

ピボットテーブルの自動更新
一度作成しておいたピボットテーブルのデータを更新します。
手動操作の場合の、ピボットテーブルにカーソルを合わせて右クリック→「更新」をクリックするか、ツールバーの「更新」ボタンをクリックする動作をVBAがやってくれます。
ピボットテーブルのデータソースが変わったのに、更新ボタン押し忘れる、ということもなくなりますよ。

ピボットテーブルのクリア
ピボットテーブルの表示項目やデータソースをリセットします。
ピボットテーブルを手動で作成したとき、枠だけができますよね？あの状態にします。
レポート分析していて、レポートの切り口を変えたいときなどに便利です。

もちろん、上記で挙げた処理以外にも、色々なことができますよ。

では、ピボットテーブル操作自動化の方法を見ていきましょう！

ピボットテーブルをオブジェクト型変数にセットする

オブジェクトとは？

まず、ピボットテーブルをVBAで操作するための第一歩、オブジェクトについてご紹介します。

ピボットテーブルに限らず、VBAで何らかの操作を行う場合、オブジェクトが必要です。

オブジェクトとは、VBAで操作する対象のことです。

VBAのコードは、「何に対して、どのような操作(処理)をするのか」を記述する必要がありますが、この「何に対して」がオブジェクトにあたります。

ピボットテーブルをVBAで操作するためには、操作したいピボットテーブルが操作の対象になるようにコードを記述する必要があります。

そうすることで、「このピボットテーブルを操作するんだな」とVBAに認識させることができるわけです。

今回使用するピボットテーブル

さて、今回の説明用の例として、数か月の仕入実績リストをデータソースにして作成したピボットテーブルを用意しました。

ピボットテーブルがあるワークシートには「ピボットテーブル」、ピボットテーブルには、「仕入実績テーブル」という名前をそれぞれ付けてあります。

ピボットテーブルオブジェクトの基本的な記述

まず、特定のピボットテーブルをVBAで操作する場合、下記のように記述します。

Worksheetオブジェクト.PivotTables(ピボットテーブル名)

上記の”ピボットテーブル”には、ピボットテーブル名を指定するので、例でお見せしたピボットテーブルをVBAで操作したい場合は、このように記述します。

ActiveWorkbook.Worksheets("ピボットテーブル").PivotTables("仕入実績テーブル")

これで、”ピボットテーブルシート”にある”仕入実績テーブル”をオブジェクトとしてVBAで操作できるようになるのです。

PivotTableオブジェクト変数にピボットテーブルをセットする

すでにお気付きかもしれませんが、特定のピボットテーブルをVBAで操作するためには、「どこのブックの、どこのシートにあるピボットテーブルなのか？」までコードに記述しなければいけません。

基本的な記述方法は前述した通りですが、毎回記述するには少々長いですよね。

そこで、PivotTableオブジェクト型変数を用いてコードを記述する方法があります。

PivotTableオブジェクト型変数はピボットテーブル専用の変数で、ここにVBAで操作したいピボットテーブルの在処を格納しておくことができるものです。

PivotTableオブジェクト型変数を使う方法は、他の変数と同様、変数の宣言をして、

Dim 変数名 As PivotTable

宣言した変数に以下のようにセットします。

Set 変数名 = ワークシートオブジェクト.PivotTables(ピボットテーブル名)

上記をコードで記述してみたものがこちら。

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