どのように動的に更新すべてのピボットテーブルExcelのデータソースへの

投稿日: 2020年10月22日 2020年11月12日
タグ: excel-pivot-tables

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

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

ソースデータシートにコードを書き込むこれを完全に自動化する必要があるため、コアモジュールの代わりにシートモジュールを使用してコードを記述します。これにより、 `link：/ events-in-vba-the-worksheet-events-in-excel-vba [worksheetevents]`を使用できるようになります。

ソースデータとピボットテーブルが異なるシートにある場合は、ソースデータを含む（ピボットテーブルを含まない）シートオブジェクトのピボットテーブルデータソースを変更するVBAコードを記述します。 Ctrl + F11を押して、VBエディターを開きます。次に、プロジェクトエクスプローラーに移動し、ソースデータを含むシートを見つけます。それをダブルクリックします。

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

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

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

ワークブック内のすべてのピボットテーブルを新しい範囲で動的に更新するためのソースコードそれがどのように機能するかを説明するために、ワークブックがあります。このワークブックには3枚のシートが含まれています。 Sheet1には、変更可能なソースデータが含まれています。 Sheet2とSheet3には、sheet2のソースデータに依存するピボットテーブルが含まれています。

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

Private Sub Worksheet_Deactivate()

Dim source_data As Range

'Determining last row and column number

lstrow = Cells(Rows.Count, 1).End(xlUp).Row

lstcol = Cells(1, Columns.Count).End(xlToLeft).Column

'Setting the new range

Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol))

'Code to loop through each sheet and pivot table

For Each ws In ThisWorkbook.Worksheets

For Each pt In ws.PivotTables



pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create( _

SourceType:=xlDatabase, _

SourceData:=source_data)



Next pt

Next ws

End Sub

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

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

このコードはどのようにしてソースデータを自動的に変更し、ピボットテーブルを更新しますか？まず、worksheet_deactivateイベントを使用しました。このイベントは、コードを含むシートが切り替えられたとき、または非アクティブ化されたときにのみトリガーされます。つまり、これがコードが自動的に実行される方法です。

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

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

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

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

set source_data = Range（Cells（1、1）、Cells（lstrow、lstcol））

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

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

ThisWorkbook.Worksheetsの各wsについて

ws.PivotTablesの各ポイントについて

pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create（_

SourceType：= xlDatabase、_

SourceData：= source_data）

次のポイント

次のws

最初のループは各シートをループします。 2番目のループは、シート内の各ピボットテーブルを反復処理します。ピボットテーブルは変数ptに割り当てられます。* ptオブジェクトのChangePivotCacheメソッドを使用します。 ThisWorkbook.PivotCaches.Createメソッドを使用してピボットキャッシュを動的に作成します。このメソッドは、SourceTypeとSourceDataの2つの変数を取ります。ソースタイプとしてxlDatabaseを宣言し、SourceDataとして以前に計算したsource_data範囲を渡します。

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

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

link：/ excel-pivot-tables-how-to-dynamically-update-pivot-table-data-source-range-in-excel [Excelでピボットテーブルのデータソース範囲を動的に更新する方法]：動的に変更するにはピボットテーブルのソースデータ範囲では、ピボットキャッシュを使用します。これらの数行は、ソースデータ範囲を変更することにより、ピボットテーブルを動的に更新できます。

link：/ custom-functions-in-vba-how-to-auto-refresh-pivot-tables-using-vba-excel [VBAを使用してピボットテーブルを自動更新する方法]：ピボットテーブルを自動的に更新するには、 VBAイベント。この単純なコード行を使用して、ピボットテーブルを自動的に更新します。ピボットテーブルを自動更新する3つの方法のいずれかを使用できます。

link：/ events-in-vba-run-macro-if-any-change-made-on-sheet-range [指定された範囲のシートに変更が加えられた場合はマクロを実行]：* VBAのプラクティスでは、特定の範囲またはセルが変更されたときにマクロを実行する必要があります。その場合、ターゲット範囲に変更が加えられたときにマクロを実行するには、changeイベントを使用します。

link：/ Tips-using-worksheet-change-event-to-run-macro-when-any-change-is-made [シートに変更が加えられたときにマクロを実行する] |したがって、シートが更新されるたびにマクロを実行するには、VBAのワークシートイベントを使用します。

link：/ events-in-vba-simplest-vba-code-to-highlight-current-row-and-column-using [現在の行と列を強調表示する最も簡単なVBAコード] |この小さなVBAスニペットを使用して、シートの現在の行と列を強調表示します。

link：/ events-in-vba-the-worksheet-events-in-excel-vba [ExcelVBAのワークシートイベント] |ワークシートイベントは、シートで指定されたイベントが発生したときにマクロを実行する場合に非常に便利です。