どのようにExcelで動的に更新するピボットテーブルのデータソースの範囲に

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

現在、 `link：/ table-excel-2007-17-amazing-features-of-excel-tables [ExcelTables]`または `link：/ excel-range-name-dynamic-を使用して、ピボットテーブルを動的に変更または更新できます。 names-ranges-in-excel [動的な名前付き範囲]。しかし、これらのテクニックは絶対確実ではありません。ピボットテーブルを手動で更新する必要があるためです。何千もの行と列を含む大きなデータがある場合、Excelテーブルはあまり役に立ちません。代わりに、ファイルが重くなります。したがって、残る唯一の方法はVBAです。

この記事では、ピボットテーブルでデータソースを自動的に変更する方法を学習します。つまり、データソースを変更する手動プロセスを自動化して、ソーステーブルに追加された新しい行と列を動的に含め、ピボットテーブルの変更を即座に反映します。

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

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

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

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

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

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

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

Private Sub Worksheet_Deactivate()

Dim pt As PivotTable

Dim pc As PivotCache

Dim source_data As Range

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

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

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

Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data)

Set pt = Sheet2.PivotTables("PivotTable1")

pt.ChangePivotCache pc

End Sub

同様のワークブックがある場合は、このデータを直接コピーできます。このコードは以下で機能することを説明しました。

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

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

次に、ピボットテーブルのソースデータを変更するために、ピボットキャッシュのデータを変更します。ピボットテーブルは、ピボットキャッシュを使用して作成されます。ピボットテーブルが手動で更新されないか、ソースデータの範囲が手動で変更されるまで、ピボットキャッシュには古いソースデータが含まれます。

ptという名前のピボットテーブル、pcという名前のピボットキャッシュ、およびsource_dataという名前の範囲の参照を作成しました。ソースデータにはデータ全体が含まれます。テーブル全体をデータ範囲として動的に取得するために、最後の行と最後の列を決定します。

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））

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

ピボットキャッシュがすべてのデータを格納することがわかっているため、このデータをピボットキャッシュに格納します。

Set pc = ThisWorkbook.PivotCaches.Create（xlDatabase、SourceData：= source_data）次に、更新するピボットテーブルを定義します。 sheet1のPivotTable1（ピボットテーブルの名前。ピボットテーブルを選択しているときに、[分析]タブでピボットテーブルの名前を確認できます。）を更新するため、次のようにptを設定します。

Set pt = Sheet2.PivotTables（ “PivotTable1″）

ここで、このピボットキャッシュを使用して、ピボットテーブルを更新します。 ptオブジェクトのchangePivotCacheメソッドを使用します。

pt.ChangePivotCache pc

また、ピボットテーブルは自動化されています。これにより、ピボットテーブルが自動的に更新されます。同じデータソースを持つ複数のテーブルがある場合は、各ピボットテーブルオブジェクトで同じキャッシュを使用するだけです。

そうですね、これが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のワークシートイベント] |ワークシートイベントは、シートで指定されたイベントが発生したときにマクロを実行する場合に非常に便利です。