エクセルVBAでピボットテーブルを作成!PivotCacheにデータソース範囲をセットする

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


ピボットテーブル作成自動化 ピボットキャッシュ

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

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

今回は、エクセルVBAでピボットテーブルを作成する手順の1ステップ目、ピボットキャッシュにデータソースをセットする方法をご紹介していきます。

ピボットテーブルやオブジェクトとは何ぞや?という方は、下記の記事を併せてご覧ください。

データ分析をするならとりあえず作る!エクセルピボットテーブルの作り方
とあるラーメン屋さんのアンケートを題材にしてデータ分析を進めていきます。まずはデータ分析においてとりあえず作っておけ的な存在であるピボットテーブルについてその作り方を解説していきますね。
エクセルVBAでピボットテーブルを操作するための第一歩~ピボットテーブルオブジェクト変数~
エクセルのピボットテーブル操作(作成、更新など)をVBAで自動化の第一歩、オブジェクト変数へ操作対象のピボットテーブルをセットする方法を、「オブジェクトとは?」というところからご紹介しています。ピボットテーブルの操作が自動化できれば、データ加工からピボットテーブル完成まで完全自動化だってできますよ!
スポンサーリンク

PivotCacheオブジェクトとは

手作業でピボットテーブルを作成場合、セル範囲を入力するなりドラッグするなりして、データソース範囲を指定しますよね。

エクセルVBAでピボットテーブルを作成する場合も、データソース範囲を指定するという点では手動作成の場合と手順は変わりませんが、手動でピボットテーブルを作成する際には意識しないものを使うことを知っておく必要があります。

それが、ピボットキャッシュです。

手動でピボットテーブルを作成するとき、データソースにしたい範囲を選択すればピボットテーブルができますよね。

なので、「ピボットテーブル=データソースに指定したデータそのもの」と考えがちなのですが、厳密に言うとエクセルの中では、

  1. データソースからピボットキャッシュにデータをコピー
  2. ピボットキャッシュからピボットテーブル作成

という順序で処理されています。

ピボットテーブルのデータソースを書き換えてたとき、「更新」するまで書き換えたデータソースの内容はピボットテーブルに反映されませんよね。

これは、ピボットテーブルがデータソースを常に直接参照しているのではなく、ピボットテーブルのデータソースを「範囲指定した瞬間のデータ」または「データソース範囲を変更した瞬間のデータ」を参照しているからなのです。

つまり、手動でピボットテーブルを作成する場合には意識しないこのピボットキャッシュを、エクセルVBAでピボットテーブルを作成したり、データを変更したりする場合は操作する必要があるのです。

PivotCacheオブジェクトを作成する方法

ピボットテーブルは、データソースからピボットキャッシュができ、ピボットキャッシュからピボットテーブルができること、エクセルVBAでピボットテーブルのデータを操作する場合は、ピボットキャッシュを操作する必要があることをお伝えしました。

エクセルVBAでピボットテーブルを作成する場合も同様に、まずデータソース範囲を指定する必要があります。

そこで、データソース範囲をVBAで操作できるように、Createメソッドを使って指定したデータソース範囲を持ったPivotCacheオブジェクトを作ります。

エクセルVBAの記述方法はこちら。

PivotCaches.Create(SourceType:=xlDatabase, SourceData:=データソースの範囲)

今回はエクセルのリストをデータソースにしますので、「xlDatabase」を指定します。

これで、指定した範囲のデータソースを持つPivotCacheオブジェクトができる=ピボットキャッシュをVBAで操作するための準備ができます。

ピボットキャッシュをセットするVBAプログラム

では次に、データソースの範囲指定方法を、例を用いてご紹介していきます。

「データ」というシートの表をデータソースとして、「ピボットテーブル」シートにピボットテーブルを作成する、というコードを用意しました。

今回はピボットキャッシュの格納だけを記述したいので、PivotChaceオブジェクト型変数を使って、「データ」シートにある指定したセル範囲をピボットキャッシュにする、という内容にしています。

データソースとして指定したいセル範囲は、Rangeを使って記述します。

これで、

「このブックの「データシート」にあるA1セル~F20セルをピボットキャッシュにセットする」

ことができました!

まとめ

今回は、エクセルVBAでピボットテーブルを作成する手順の一つ目として、データソースをピボットキャッシュに格納する方法をご紹介しました。

今回はコードの中でセル範囲を(“A1:F20”)のように直接記述していましたが、もちろん可変にすることもできますよ。

その方法のご案内はまた別の機会に。

それでは、次回は今回格納したピボットキャッシュから、ピボットテーブルを作成していきましょう。

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

連載目次:エクセルVBAでピボットテーブルを操作する第一歩

ピボットテーブルはとっても便利です。それを自動化できたらもっと便利に使えるのでは?ということで、VBAを使ってピボットテーブルを操作する方法についてお伝えしていきます。
  1. エクセルVBAでピボットテーブルを操作するための第一歩~ピボットテーブルオブジェクト変数~
  2. エクセルVBAでピボットテーブルを作成!PivotCacheにデータソース範囲をセットする/a>

The following two tabs change content below.
ノグチ

ノグチ

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