結論：ソースデータに変更が加えられたときにピボットテーブルを自動的に更新する簡単なマクロの方法を学びます。また、ファイルを開いたときにピボットテーブルを更新するための非マクロソリューションも共有しています。ビデオチュートリアルとExcelファイルのダウンロードが含まれています。
スキルレベル：中級
Excelファイルのダウンロード
自分で行うことで最もよく学ぶ場合は、次のことができます。ビデオで使用しているファイルをダウンロードして、フォローします。これは、VBAコードを含むExcelファイルです。
ピボットテーブルを自動的に更新します。xlsm （41.5 KB）
ピボットテーブルを自動的に更新する
ソースデータが変更されたときにピボットテーブルをすぐに自動的に更新できますか？
もちろんです。使用する必要があります。以下に作成方法を示す非常に単純なマクロの例を示します。
マクロにあまり詳しくない場合およびVBAについては、マクロ& VBAの使用を開始するための無料の3部構成のビデオシリーズを確認することをお勧めします。
また、ピボットテーブルを初めて使用する場合は、それらが何であるか、そしてそれらをどのように使用するかを説明するシリーズがあります。ピボットテーブルダッシュボード
ピボットテーブルを自動的に更新するために、1つの簡単な命令でマクロを記述します。その指示は基本的に次のように述べています。ワークシートに変更を加えるときは、すべてのピボットテーブルとデータ接続を更新します。マクロを作成する手順は次のとおりです。
VisualBasicエディターを開きます。
これを行うには、リボンの[開発者]タブにある[VisualBasic]ボタンをクリックします。
VisualBasicエディターを開くためのキーボードショートカットはAlt + F11です。
表示されない場合[開発者]タブでは、こちらの手順を使用して表示できます。これを1回行うだけで、今後Excelを開くたびに[開発者]タブが常に表示されます。
ソースデータを含むシートモジュールを開きます。
Visual Basicエディターの[プロジェクトエクスプローラー]ウィンドウで、変更するブックを見つけます。そのワークブックの下に、ワークブック内のシートが一覧表示されます。ソースデータを含むシートを選択します。次に、それをダブルクリックします。
プロジェクトエクスプローラウィンドウが表示されない場合は、ビューから有効にできます。メニュー（キーボードショートカット：Ctrl + R）。
ワークシート変更用の新しいイベントを追加します。
シートをダブルクリックすると、そのオブジェクトのコードモジュールが開きます。コードモジュール内で、イベントマクロを作成します。これを行うには、左側の[オブジェクト]ドロップダウンボックスで[ワークシート]を選択します。
これにより、Worksheet_SelectionChangeイベントがに追加されます。モジュールは実際には必要ないので、すぐに削除します。その前に、右側の[手順]ドロップダウンメニューに移動して、[変更]を選択します。
これにより、新しいWorksheet_Changeと呼ばれる上部のイベント。次に、その下の不要なコードを強調表示して削除します。
Worksheet_Changeイベントマクロは、変更が加えられるたびに実行されます。そのワークシートのセルに。 Worksheet_ChangeイベントにVBAコードを追加して、ユーザーがセルを編集したときにアクションを実行できます。
注：デフォルトで追加されるSelectionChangeイベントは、ユーザーがシート内のセルを選択するたびに実行されます。ユーザーがセルを編集/変更したときにのみコードを実行する必要があるため、Changeイベントを使用します。 VBAコードモジュールに関する私の記事をチェックしてください&シートモジュールとイベントの詳細については、ユーザーイベントに基づいてマクロを実行する方法
更新するVBAコードを追加してくださいすべてのピボットテーブル。
次に、Worksheet_Change行のすぐ下に、次の命令を入力します。
ThisWorkbook.RefreshAll
RefreshAllメソッドは、ブック内のすべてのピボットテーブル、クエリ、およびデータ接続を更新します。このアクションは、[データ]タブの[更新]ボタンを手動でクリックする場合と同じです。
このコード行をWorksheet_Changeイベントに追加すると、コードがワークシートに変更が加えられるたびにワークブックが更新されます。 in。
ピボットテーブル&同じシートのソースデータ
AleksandrsはYouTubeの動画コメントですばらしい質問をしました。ピボットテーブルとソースデータが同じシート上にある場合は、イベントを無効にするコードを追加する必要があります。
更新するとイベントが再帰ループになり、Excelがクラッシュする可能性があります。これを防ぐためのコードは次のとおりです。
Application.EnableEvents = False ThisWorkbook.RefreshAllApplication.EnableEvents = True
マクロが実行されていることを確認する
マクロが機能しているかどうかを確認する1つの方法それをテストすることです。ソースデータに変更を加えて、ピボットテーブルに反映されているかどうかを確認します。データが多すぎるなどの理由で変更を見つけるのが簡単でない場合、またはその他の理由で、マクロが実行されているかどうかを確認する別の方法があります。
VBエディターで、[ Worksheet_Changeマクロのすぐ左にある灰色の列。これにより、赤い円が表示されます。また、そのコード行を赤で強調表示します。
これは停止またはブレークポイントと呼ばれます。
ブレークポイントのオン/オフを切り替えるキーボードショートカットは次のとおりです。F9
これで、マクロをトリガーするアクションが発生するたびに、ExcelがVBエディターにジャンプし、マクロを一時停止して、コードを確認できるようになります。この場合、そのアクションはワークシートで行われている変更です。
次に、F8キーを押してそれぞれをステップ実行できます。行、またはF5キーを押して最後（または次のブレークポイント）まで実行します。
ワークシートに変更を加えてもExcelでVBエディターに移動できない場合は、問題があることがわかります。マクロが実行されていません。この場合は、ファイルをマクロ対応のブック（.xlsm）または有効なマクロとして保存していない可能性があります。 &を保存してファイルを閉じてから、再度開いてマクロを有効にする必要がある場合があります。
マクロに設定したブレークポイントを削除するには、赤い円をクリックするだけで消えます（キーボードショートカット：F9）。
すべてのブレークポイントをクリアするためのキーボードショートカットは次のとおりです。Ctrl+ Shift + F9
ピボットテーブルを更新せずにマクロ
このマクロを使用してピボットテーブルを更新することの欠点の1つは、マクロを実行するたびに元に戻す履歴が失われることです。つまり、[元に戻す]ボタンをクリックする（またはCtrl + Zを押す）と、Excelは最後に行った操作を記憶しないため、元に戻すことはできません。その結果、何も起こらず、最後の変更が取り消されることはありません。
元に戻す履歴を保持できる代替手段があります。ただし、この代替方法では、変更が行われるたびにではなく、ブックが開かれたときにのみピボットテーブルが更新されます。このオプションの使用方法は次のとおりです。
ピボットテーブルの任意のセルから開始します。
- リボンの[分析]タブに移動します。
- [オプション]ボタンを選択します。
- 開いた新しいウィンドウの[データ]タブに移動します。
- [ファイルを開くときにデータを更新する]チェックボックスをオンにします。
[OK]をクリックした後、同じソースデータ範囲から複数のピボットテーブルを作成すると、次の警告メッセージが表示される場合があります。[OK]をクリックするだけで完了します。
ここでも、比較のために、このオプションを使用すると、元に戻す履歴は保持されますが、ワークブックを閉じて再度開いたときにのみピボットテーブルが更新されます。マクロオプションを使用すると、元に戻す履歴は失われますが、ピボットテーブルは自動的に更新されます。ブックに変更が加えられるたびにテストします。
ピボットテーブルを更新するためのバリエーション
確認したマクロは、ピボットテーブルを更新するだけでなく、クエリも更新します。 。ピボットテーブルのみを更新する場合は、代わりに「ThisWorkbook.RefreshAll」コマンドを次のコードに置き換えることができます。
Sub Refresh_All_Pivot_Table_Caches()"Refresh all pivot caches in the workbook."Pivot tables are automatically refreshed when cache is refreshed.Dim pc As PivotCache "Refresh all pivot tables For Each pc In ThisWorkbook.PivotCaches pc.Refresh Next pc End Sub
各ピボットテーブルはソースデータに接続されている基になるピボットキャッシュ。コードはワークブック内のすべてのピボットキャッシュをループし、それぞれを更新します。同じソース範囲のピボットテーブルはピボットキャッシュを共有できるため、この方法はすべてのピボットテーブルをループするよりも高速です。 。
同様に、特定のピボットテーブルを1つだけ更新するとします。その場合、「ThisWorkbook.RefreshAll」コードを以下のコードと交換できます。
最後に、 Power Queryを使用していて、ピボットテーブルの前にクエリが更新されるようにバックグラウンド更新を無効にしたい場合は、クエリのバックグラウンド更新を無効にすることでその方法を説明する記事を作成しました。
代わりにイベントを非アクティブ化
別のオプションは、Worksheet_Changeの代わりにWorksheet_Deactivateイベントを使用することです。Worksheet_Deactivateイベントは、ユーザーがシートを離れて別のシートを選択するたびに実行されます。これにより、ユーザーはソースデータの場合、ピボットテーブルを含むシートを含む他のシートに移動すると、ピボットテーブルが自動的に更新されます。
Private Sub Worksheet_Deactivate() ThisWorkbook.RefreshAllEnd Sub
このコードソースデータを含むシートモジュールに引き続き配置されます。これは、ピボットテーブルまたはデータ接続が更新に数秒以上かかり、ソースデータに変更が加えられるたびに待つ必要はありません。
これを使用したくない場合は、ピボットテーブルとソースの場合のみです。データは同じシートにあります。これは通常まれなケースであり、私が一般的にお勧めしないものです。
これに関するTedからの提案に感謝します。
時間の節約&恥ずかしさ
この記事が時間を節約し、ファイルのユーザーにとってより簡単になることを願っています。また、レポートを送信する前にピボットテーブルを更新するのを忘れた場合の恥ずかしさを防ぐのにも役立ちます。信じてください。 私、私は「私が認めたいよりも何度もこの間違いを犯しました…🙂
