エクセルVBAでピボットテーブルを操作するための第一歩~ピボットテーブルオブジェクト変数~

★気に入ったらシェアをお願いします!


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

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

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

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

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

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

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

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

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

  • データソースからピボットテーブルの生成
    指定したワークシートや、外部のデータをデータソースにして、ピボットテーブルの作成を行います。
    VBAでデータソースを加工するコードの最後に記述しておけば、ワンクリックでデータソースの準備からピボットテーブル作成までできるようになりますよ。
  • ピボットテーブルの自動更新
    一度作成しておいたピボットテーブルのデータを更新します。
    手動操作の場合の、ピボットテーブルにカーソルを合わせて右クリック→「更新」をクリックするか、ツールバーの 「更新」ボタンをクリックする動作をVBAがやってくれます。
    ピボットテーブルのデータソースが変わったのに、更新ボタン押し忘れる、ということもなくなりますよ。
  • ピボットテーブルのクリア
    ピボットテーブルの表示項目やデータソースをリセットします。
    ピボットテーブルを手動で作成したとき、枠だけができますよね?あの状態にします。
    レポート分析していて、レポートの切り口を変えたいときなどに便利です。

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

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

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

オブジェクトとは?

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

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

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

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

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

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

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

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

エクセル,ピボットテーブル,vba,

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

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

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

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

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

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

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

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

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

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

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

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

Dim 変数名 As PivotTable

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

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

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

“pivot”という名前で宣言したピボットテーブルオブジェクト型変数に、ピボットテーブルの在処をセットしています。

オブジェクト変数に格納したピボットテーブル名を表示する

では、PivotTableオブジェクトにセットしたピボットテーブルを、例のピボットテーブルを使って動かしてみましょう。

セットしたオブジェクト名をメッセージボックスに表示できるように、Nameプロパティを使ってコードの最後に「MsgBox pivot.Name」を追加して動かしてみると…

エクセルvba ピボットテーブル,オブジェクトセット

ちゃんとPivotTableオブジェクトにセットしたピボットテーブルがキャッチされていますね。

これで、「今アクティブになっているブックの、”ピボットテーブル”というシートにある”仕入実績テーブル”」までがVBAで操作できるようになりました!

最後に

さぁ、これで、PivotTableをVBAで操作するための第一歩、オブジェクトのセットができました。

次回からは、このオブジェクトにプロパティ、メソッドを組み合わせて、VBAでのピボットテーブル操作方法をご紹介していきます。

それでは、最後までお読みいただきありがとうございました!


The following two tabs change content below.
ノグチ

ノグチ

元製造子会社SE、ERPパッケージシステムコンサルタント(販売管理、購買管理、生産管理、設備管理他)。 業務システム保守・導入両方やった経験をお伝えすることで、少しでもシステムや手作業に悩まされる人が減ればいいなぁ、楽にお仕事してもらえたらいいなぁと日々夢見ております。