Excelですべてのピボットテーブルデータソースを動的に更新する方法

前回の記事では、データソースを縮小または拡大して個々のピボットテーブルを動的に変更および更新する方法を学びました。

この記事では、ワークブック内のすべてのピボットテーブルでデータソースを自動的に変更する方法を学びます。つまり、一度に1つのピボットテーブルを変更する代わりに、ワークブック内のすべてのピボットテーブルのデータソースを変更して、ソーステーブルに追加された新しい行と列を動的に含め、ピボットテーブルの変更を即座に反映しようとします。

ソースデータシートにコードを書き込む

これを完全に自動化する必要があるため、コアモジュールの代わりにシートモジュールを使用してコードを記述します。これにより、ワークシートイベントを使用できるようになります。

ソースデータとピボットテーブルが異なるシートにある場合は、ソースデータを含むシートオブジェクトのピボットテーブルデータソースを変更するVBAコードを記述します（ピボットテーブルは含まれていません）。

Ctrl + F11を押して、VBエディターを開きます。次に、プロジェクトエクスプローラーに移動し、ソースデータを含むシートを見つけます。ダブルクリックします。

新しいコーディング領域が開きます。変更は表示されない場合がありますが、ワークシートのイベントにアクセスできるようになりました。

左側のドロップダウンメニューをクリックして、ワークシートを選択します。左側のドロップダウンメニューから、[非アクティブ化]を選択します。コード領域名worksheet_deativateに空白のサブが書き込まれます。ソースデータを動的に変更し、ピボットテーブルを更新するためのコードは、このコードブロックに含まれます。このコードは、データシートから他のシートに切り替えるたびに実行されます。ここですべてのワークシートイベントについて読むことができます。

これで、コードを実装する準備が整いました。

ワークブック内のすべてのピボットテーブルを新しい範囲で動的に更新するためのソースコード

その仕組みを説明するために、ワークブックがあります。このワークブックには3枚のシートが含まれています。 Sheet1には、変更可能なソースデータが含まれています。 Sheet2とSheet3には、sheet2のソースデータに依存するピボットテーブルが含まれています。

このコードをsheet1のコーディング領域に記述しました。Worksheet_Deactivateイベントを使用しているため、このコードを実行して更新します。ソースデータシートから切り替えるたびにピボットテーブル。

同様のワークブックがある場合は、このデータを直接コピーできます。このコードは以下で機能するため、必要に応じて変更できます。

このコードの効果は以下のgifで確認できます。

このコードはどのように自動的に変更されますかソースデータとピボットテーブルの更新？

まず、worksheet_deactivateイベントを使用しました。このイベントは、コードを含むシートが切り替えられたとき、または非アクティブ化されたときにのみトリガーされます。これにより、コードが自動的に実行されます。

テーブル全体をデータ範囲として動的に取得するには、最後の行と最後の列を決定します。

lstrow = Cells（Rows.Count、1）.End（xlUp）.Row

lstcol = Cells（1、Columns.Count）.End（xlToL eft）.Column

これら2つの数値を使用して、source_dataを定義します。ソースデータの範囲は常にA1から始まることは間違いありません。独自の開始セル参照を定義できます。

Setsource_data = Range（Cells（1、1）、Cells（lstrow、lstcol））

これで、次のソースデータが得られました。動的。ピボットテーブルで使用する必要があります。

ブックに一度に含まれるピボットテーブルの数がわからないため、各シートと各シートのピボットテーブルをループします。ピボットテーブルが残っていないこと。このために、ネストされたforループを使用します。

For Each ws In ThisWorkbook.Worksheets

For Each pt In ws.PivotTables

pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create（_

SourceType：= xlDatabase、_

SourceData：= source_data）

次のpt

次のws

最初のループは各シートをループします。2番目のループはシート内の各ピボットテーブルを繰り返します。

ピボットテーブルが割り当てられます。 ptオブジェクトのChangePivotCacheメソッドを使用します。ThisWorkbook.PivotCaches.Create

メソッドを使用してピボットキャッシュを動的に作成します。このメソッドは、2つの変数SourceTypeとSourceDataを取ります。ソースタイプとして、xlDatabaseとSourceDataとして、以前に計算したsource_data範囲を渡します。

そしてそのi sそれ。ピボットテーブルは自動化されています。これにより、ブック内のすべてのピボットテーブルが自動的に更新されます。

そうですね、これにより、Excelでブック内のすべてのピボットテーブルのデータソース範囲を動的に変更できます。私は十分に説明できたと思います。この記事に関して質問がある場合は、以下のコメントセクションでお知らせください。

Excelでピボットテーブルのデータソース範囲を動的に更新する方法：ピボットテーブルのソースデータ範囲を動的に変更するには、ピボットキャッシュを使用します。これらの数行は、ソースデータ範囲を変更することでピボットテーブルを動的に更新できます。

VBAを使用してピボットテーブルを自動更新する方法：ピボットテーブルを自動的に更新するには、VBAイベントを使用できます。この単純なコード行を使用して、ピボットテーブルを自動的に更新します。ピボットテーブルを自動更新する3つの方法のいずれかを使用できます。

指定された範囲のシートに変更が加えられた場合にマクロを実行する：VBAのプラクティスでは、特定の範囲の場合にマクロを実行する必要があります。セルが変更されます。その場合、ターゲット範囲に変更が加えられたときにマクロを実行するには、changeイベントを使用します。

シートに変更が加えられたときにマクロを実行する|そのため、シートが更新されるたびにマクロを実行するために、VBAのワークシートイベントを使用します。

|を使用して現在の行と列を強調表示する最も簡単なVBAコードこの小さなVBAスニペットを使用して、シートの現在の行と列を強調表示します。

ExcelVBAのワークシートイベント|ワークシートイベントは、シートで指定されたイベントが発生したときにマクロを実行する場合に非常に便利です。

人気の記事：

生産性を向上させるための50個のExcelショートカット|あなたの仕事をより速くしてください。これらの50のショートカットにより、Excelでの作業がさらに高速になります。ExcelのVLOOKUP関数|これは、さまざまな範囲やシートから値を検索するために使用される、Excelの最も使用され人気のある関数の1つです。

Excel2016のCOUNTIF |この驚くべき機能を使用して、条件付きの値をカウントします。特定の値をカウントするためにデータをフィルタリングする必要はありません。ダッシュボードを準備するにはCountif関数が不可欠です。

ExcelでSUMIF関数を使用する方法|これはダッシュボードのもう1つの必須関数です。これは、値を合計するのに役立ちます。特定の条件で。