どのようにVBA Excelを使用して自動更新ピボットテーブルに

投稿日: 2020年10月22日 2020年11月12日
タグ: custom-functions-in-vba

ご存知のとおり、ピボットテーブルのソースデータに変更を加えても、ピボットテーブルにすぐには反映されません。変更を確認するには、ピボットテーブルを更新する必要があります。また、ピボットテーブルを更新せずに更新されたファイルを送信すると、恥ずかしい思いをする可能性があります。したがって、この記事では、VBAを使用してピボットテーブルを自動更新する方法を学習します。この方法はあなたが想像したよりも簡単です。

これは、ブック内のピボットテーブルを自動的に更新するための単純な構文です。

'Code in Source Data Sheet Object

Private Sub Worksheet_Deactivate()

sheetname_of_pivot_table.PivotTables("pivot_table_name").PivotCache.Refresh

End Sub

ピボットキャッシュとは何ですか？すべてのピボットテーブルは、データをピボットキャッシュに格納します。これが、ピボットが以前のデータを表示できる理由です。ピボットテーブルを更新すると、ピボットテーブルの変更を反映するために、新しいソースデータでキャッシュが更新されます。

したがって、ピボットテーブルのキャッシュを更新するためのマクロが必要です。これは、マクロを手動で実行する必要がないように、 `link：/ events-in-vba-the-worksheet-events-in-excel-vba [worksheetevent]`を使用して行います。 ===ピボットテーブルを自動更新するようにコーディングする場所ソースデータとピボットテーブルが異なるシートにある場合は、VBAコードをソースデータシートに入れる必要があります。

ここでは、Worksheet_SelectionChangeイベントを使用します。これにより、ソースデータシートから別のシートに切り替えるたびにコードが実行されます。

このイベントを使用した理由については後で説明します。

ここでは、sheet2にソースデータがあり、sheet1にピボットテーブルがあります。

Ctrl + F11キーを使用してVBEを開きます。プロジェクトエクスプローラーでは、Sheet1、Sheet2、およびWorkbookの3つのオブジェクトを確認できます。

Sheet2にはソースデータが含まれているため、sheet2オブジェクトをダブルクリックします。

これで、コード領域の上部に2つのドロップダウンが表示されます。最初のドロップダウンから、ワークシートを選択します。そして、2番目のドロップダウンから[非アクティブ化]を選択します。これにより、空のサブ名Worksheet_Deactivateが挿入されます。私たちのコードはこのサブで書かれます。このサブに書かれた行は、ユーザーがこのシートから他のシートに切り替えるとすぐに実行されます。

sheet1には、2つのピボットテーブルがあります。ピボットテーブルを1つだけ更新したい。そのためには、ピボットテーブルの名前を知る必要があります。ピボットテーブルの名前を知るには、そのピボットテーブルのセルを選択し、[ピボットテーブルの分析]タブに移動します。左側に、ピボットテーブルの名前が表示されます。ここでピボットテーブルの名前を変更することもできます。

ピボットテーブルの名前がわかったので、ピボットテーブルを更新する簡単な行を記述できます。

Private Sub Worksheet_Deactivate()

Sheet1.PivotTables("PivotTable1").PivotCache.Refresh

End Sub

そして、それは行われます。

これで、ソースデータから切り替えるたびに、このvbaコードが実行されてピボットテーブルが更新されます1。あなたが下のgifで見ることができるように。

ブック内のすべてのピボットテーブルを更新する方法は？上記の例では、1つの特定のピボットテーブルのみを更新する必要がありました。ただし、ブック内のすべてのピボットテーブルを更新する場合は、コードにわずかな変更を加えるだけで済みます。

Private Sub Worksheet_Deactivate()

'Sheet1.PivotTables("PivotTable1").PivotCache.Refresh

For Each pc In ThisWorkbook.PivotCaches

pc.Refresh

Next pc

End Sub

このコードでは、 `link：/ vba-for-loops-with-7-examples [For loop]`を使用して、ブック内の各ピボットキャッシュをループします。 ThisWorkbookオブジェクトには、すべてのピボットキャッシュが含まれています。それらにアクセスするには、ThisWorkbook.PivotCachesを使用します。

Worksheet_Deactivateイベントを使用する理由

ソースデータに変更が加えられたらすぐにピボットテーブルを更新する場合は、Worksheet_Changeイベントを使用する必要があります。しかし、私はそれをお勧めしません。シートに変更を加えるたびに、ワークブックでコードが実行されます。結果を確認する前に、何百もの変更を行う必要がある場合があります。ただし、Excelは、変更のたびにピボットテーブルを更新します。これは、処理時間とリソースの浪費につながります。したがって、ピボットテーブルとデータが異なるシートにある場合は、ワークシート非アクティブ化イベントを使用することをお勧めします。それはあなたがあなたの仕事を終えることを可能にします。ピボットテーブルシートに切り替えて変更を確認すると、変更が修正されます。

同じシートにピボットテーブルとソースデータがあり、ピボットテーブルを自動的に更新する場合は、 `link：/ tips-using-worksheet-change-event-to-run-macro-when-を使用できます。 any-change-is-made [Worksheet_Changeイベント] `。

Private Sub Worksheet_Change(ByVal Target As Range)

Sheet1.PivotTables("PivotTable1").PivotCache.Refresh

End Sub

ソースデータに変更が加えられたときにワークブックのすべてを更新するにはどうすればよいですか？ブックのすべて（グラフ、ピボットテーブル、数式など）を更新する場合は、ThisWorkbook.RefreshAllコマンドを使用できます。

Private Sub Worksheet_Change(ByVal Target As Range)

ThisWorkbook.RefreshAll

End Sub

このコードはデータソースを変更しないことに注意してください。したがって、ソースデータの下にデータを追加した場合、このコードにはそのデータが自動的に含まれません。 `link：/ table-excel-2007-17-amazing-features-of-excel-tables [ExcelTables]`を使用してソースデータを保存できます。テーブルを使用したくない場合は、VBAを使用して新しいデータを含めることもできます。次のチュートリアルで学習します。

そうですね、これがExcelでピボットテーブルを自動的に更新する方法です。私は十分に説明的であり、この記事があなたに役立ったことを願っています。このトピックに関して質問がある場合は、以下のコメントセクションで私に質問することができます。

Excelでピボットテーブルのデータソース範囲を動的に更新する方法：ピボットテーブルのソースデータ範囲を動的に変更するには、ピボットキャッシュを使用します。これらの数行は、ソースデータ範囲を変更することにより、ピボットテーブルを動的に更新できます。 VBAでは、以下に示すようにピボットテーブルオブジェクトを使用します…

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

どのようにVBA Excelを使用して自動更新ピボットテーブルに

Worksheet_Deactivateイベントを使用する理由

関連記事：

人気の記事：