本ページには広告が含まれています。

Excel VBA ピボットテーブルを操作する【セル範囲変更編】

Excel VBA ピボットテーブルを操作する【セル範囲変更編】
2021.03.16

前回、ピボットテーブルを更新する方法について勉強しましたが、サンプルの元データがテーブルでした。元データがセル範囲の表データの場合、データが追加されて範囲が広がったら自動的に範囲を更新できません。その場合どうしたらよいかその方法について解説します。

くるみこ
くるみこ

今回は、セル範囲の元データが変更された場合の範囲変更方法について勉強していきましょう(^^)/

わかりました～(^^)　よろしくお願いします！

【この記事でわかることは】
・ピボットテーブルの元データがセル範囲だった場合の更新方法
・セル範囲の元データをテーブルに変更する方法
・ピボットキャッシュは増やさないよう注意しよう！

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

Excel VBA ピボットテーブルを操作する【更新編】
Excel VBA ピボットキャッシュを変数に代入して再利用する方法とピボットテーブルを更新する方法をPivotCacheを使う方法とRefreshTableで更新する方法、PivotCachesコレクションでループ処理する方法について解説
www.kurumico.com
くるみこ
くるみこ

前回記事は、ピボットテーブルを更新する方法についての解説記事でした。関連記事なので覗いてみてね(^^)/

スポンサーリンク

ピボットテーブルのデータ範囲が変わる場合の更新方法

ピボットテーブルの元データがセル範囲データの場合はデータ範囲は自動で増減しないため再設定してあげる必要があります。
（テーブルの場合はデータ範囲が自動で変更されますからPivotCache.Refreshで更新が完了します）

ChangePivotCacheメソッドでキャッシュを変更

ChangePivotCacheメソッドでピボットテーブルのキャッシュを変更することで、データを更新できます。

ChangePivotCache メソッド

ChangePivotCache メソッドは、指定したピボットテーブルの PivotCache オブジェクトを変更します。

【構文】PivotTables(Index 又は 名前)).ChangePivotCache (bstr)

【パラメーター】

名　前必須 / オプションデータ型　説　明
bstr必須String指定されたピボットテーブルの新しい PivotCache を表す
pivottableオブジェクトまたは PivotCacheオブジェクト

【備考】
ChangePivotCacheメソッドは、ワークシートに格納されているデータをデータソースとして使用する PivotTable でのみ使用できます。 

ChangePivotCache のサンプルコード

【サンプルコードの基本設定】
・PivotTables(1)のピボットテーブルが “Sheet1” にあります。
・同じブック内 “Sheet2” の「A1セル起点のセル範囲データ」が元データとなっています。

ChangePivotCacheでピボットキャッシュ範囲を更新
'データ範囲変更更新_PTと別シートにデータがある場合
Sub PvtSourceUpdate01()
    Dim rng As Range
    Set rng = Worksheets("Sheet2").Range("A1").CurrentRegion
    With ActiveSheet
        .PivotTables(1).ChangePivotCache ActiveWorkbook.PivotCaches. _
            Create(SourceType:=xlDatabase, SourceData:=rng)
    End With
End Sub

・Sheet1 から実行する設定です。ActiveSheet は Sheet1 です。
・元データは新型コロナのPCR検査数と陽性者数のオープンデータを使っています。
・元データを変更してから実行してみてください。
・４行目で、セル範囲データを.CurrentRegion で取得してRangeオブジェクトに代入しています。
・６行目が PivotTables(1)の ChangePivotCache メソッドの処理です。
・７行目部分で、ピボットキャッシュを新しい範囲データ「Range」に入れ替えています。
・これで元データの範囲が変更されてピボットテーブルのデータも更新されました。

ピボットテーブルとデータが同じシート上にある場合
'データ範囲変更更新_PTとデータが同じシート上にある場合
Sub PvtSourceUpdate02()
    Dim rng As Range
    With ActiveSheet
        Set rng = .Range("A1").CurrentRegion
        .PivotTables(1).ChangePivotCache ActiveWorkbook.PivotCaches. _
            Create(SourceType:=xlDatabase, SourceData:=rng)
    End With
End Sub

・どちらも ActiveSheet 上での処理に変更しているだけです。

セル範囲データをテーブルの変更して反映させる

・セル範囲をテーブルに変更してしまえば更新の面倒が無くなります。
・テーブルに変更した後、セル範囲のキャッシュからテーブルのキャッシュに変更します。
・テーブル名は、”テーブル1″ とします。

'セルデータ範囲からテーブルにソースデータを変更更新する
Sub PvtSourceUpdate03()
    With ActiveSheet
        .PivotTables(1).ChangePivotCache ActiveWorkbook.PivotCaches. _
            Create(SourceType:=xlDatabase, SourceData:="テーブル1")
    End With
End Sub

・セル範囲のキャッシュからテーブルのキャッシュに変更するのも ChangePivotCache メソッドを使います。

ピボットキャッシュは増やさないよう注意しよう！

ピボットキャッシュについては、前回記事でも少し触れています。

同じソースデータから手動で新しいピボットテーブルを作成する時に、前のテーブルに基づいてそれを使用するかどうかを尋ねられます。

そこで「いいえ」を選択した場合、このピボットテーブルは新たに独自のキャッシュを作成します。その結果、ソースデータのキャッシュサイズが2倍になってしまいます。

「はい」ならば、1つのキャッシュを共有してピボットテーブルのコレクションに追加されます。コレクション内の単一のピボットテーブルを更新すると、コレクション全体が更新されます。

ピボットキャッシュのサイズを検証してみます

ピボットテーブルは新たに独自のキャッシュを作成します。
ファイルのサイズは作成したピボットキャッシュ分がプラスされ約30％強増加します。
次のデータは、わかりやすいように巨大なデータからピボットテーブルを作った際のものです。

元ファイルのサイズ = 36.4MB（475171行、16列）
ピボットテーブル付 = 47.6MB（データ＋ピボットテーブル）約30.8％増加
元データのシートを削除したサイズ = 11.2MB（ピボットテーブルだけのサイズ）
※ ピボットテーブルを作ってソースデータを削除すればデータサイズ削減になる。
※ ピボットキャッシュには元データが保存されているので、ピボットテーブルから元データを復元できます。（総数のセルをダブルクリックすればテーブルが作成されます）

わかりますでしょうか？
上のデータでもう一つピボットキャッシュを作った場合11MB増加するのです！
ピボットキャッシュを安易に増やしてしまうと、とんでもなくファイルサイズが巨大化していきます！　注意してくださいね(^^;

まとめ（おわりに）

・いかがでしたでしょうか？
・「いつも汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーを念頭に記事を書いています。
・今回も、記事内で使用したコードのサンプルファイルを登録していますのでご利用ください。
・今までの記事のサンプルも登録していますのでよろしければお使いください(^^)

まとめと感想など

くるみこ
くるみこ

セル範囲データの変更を更新する方法を勉強しました。それからピボットキャッシュのサイズについても説明しました。ピボットキャッシュを有効利用すれば効率的なのかが良くわかりましたね(^^)

はい！　今回もすごくおもしろかったです(^^)　ピボットテーブル（キャッシュ）のサイズについてもよくわかりました！

くるみこ
くるみこ

次回もピボットテーブルの勉強を継続します。まだまだいろいろあるので楽しみにしていてね(^^)/

【今回分かったことは】
・ピボットテーブルの元データがセル範囲だった場合の更新方法がわかりました
・セル範囲の元データをテーブルに変換後ピボットキャッシュに反映する方法がわかりました
・ピボットキャッシュはファイルが肥大化するので安易に増やさないほうがよいことがわかりました！

★★★ ランキング参加中！ クリックしてね(^^)/ ★★★

PVアクセスランキング にほんブログ村

にほんブログ村 IT技術ブログ VBAへ
にほんブログ村

人気ブログランキングアイコン
Visual Basicランキング

今後の記事について

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にもご期待ください(^^)/

スポンサーリンク
エックスサーバーバナー広告
スポンサーリンク

記事のサンプルファイルをダウンロードできます

今回の記事のサンプルをダウンロードできるようリンク先に登録しています！

過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら（このリンク先）からご利用ください

【Excel VBA】サンプルファイル_ダウンロードのページ
【Excel VBA】の記事内で使ったサンプルファイルを（Zipファイルで）ダウンロードできるようにしました。記事内では細かい部分の解説が不十分ですが、実際にファイルをダウンロードして実行することで使えるのかどうか見ていただけたら幸いです。
www.kurumico.com