ピボット テーブルの範囲を自動的に更新するにはどうすればよいですか?

ピボットテーブルの更新って面倒ですよね。仕事でピボット テーブルを頻繁に使用する場合は、きっと共感できると思います。

つまり、ソース シートに新しいデータを追加するたびに、ピボット テーブルを更新する前にピボット テーブルのソース範囲を更新する必要があります。

ここで、ソース シートにデータを毎日追加する場合、ソース範囲を毎日更新する必要があると想像してください。

そして、ピボットテーブルの範囲を変更するたびに面倒になります。はい、そのとおりです。データを追加する頻度が高くなるほど、ソース範囲を更新する必要性も高くなります。

したがって、重要なのは、新しいデータを追加したときにソース範囲を自動的に更新するメソッドが必要であるということです。

注: ピボット テーブルは中級 Excel スキルの一部です。

テーブルを適用してピボット テーブル範囲を自動更新する

数日前、私はJohn Michaloudisに 100 万ドルのピボット テーブルのヒントについて尋ねました。ソースデータをテーブルに入れます。信じてください、これは100万ドルのチップです。

ソース データにテーブルを適用すると、ピボット テーブルのソース範囲を何度も変更する必要がなくなります。

新しいデータを追加するたびに、ピボット テーブルの範囲が自動的に更新されます。

ピボットテーブルを作成する前にデータをテーブルに変換する

ピボット テーブルを作成する前に、必ず次の手順を使用してテーブルをソース データに適用してください。

1. データ内のセルの 1 つを選択します。
2. ショートカット キー Ctrl + T を使用するか、 → [タブの挿入] → [テーブル] → [テーブル] に移動します。
3. 現在のデータ範囲を示すポップアップが表示されます。
   
4. 「OK」をクリックします。
5. ここでピボット テーブルを作成するには、データ内の任意のセルを選択します。 →「デザイン」タブ→「ツール」→「ピボットテーブルで集計」に移動します。
   
6. 「OK」をクリックします。

これで、新しいデータをデータシートに追加するたびに、ピボット テーブルの範囲が自動的に更新されるため、ピボット テーブルを更新するだけで済みます。

ピボットテーブル作成後にデータをテーブルに変換する

ワークシートにすでにピボット テーブルがある場合は、次の手順を使用してデータ ソースをテーブルに変換できます。

1. データ ソース内のセルの 1 つを選択します。
2. ショートカット キー Ctrl + T を使用するか、 → [タブの挿入] → [テーブル] → [テーブル] に移動します。
3. 現在のデータ範囲を示すポップアップが表示されます。
4. 「OK」をクリックします。
5. ここで、ピボット テーブル内のセルの 1 つを選択し、 → 分析 → データ → データ ソースの編集 → データ ソースの編集 (ドロップダウン メニュー) に移動します。
6. データ ソースを再選択するためのポップアップが表示されます。または、範囲エントリにテーブル名を入力することもできます。
   
7. 「OK」をクリックします。

今後、ソース シートに新しいデータを追加するたびに、ピボット テーブルの範囲が増加して自動的に更新されます。

OFFSET 関数を使用して動的なピボット テーブル範囲を作成する

ピボット テーブルの範囲を自動的に更新するもう 1 つの最良の方法は、ダイナミック レンジを使用することです。

ダイナミック レンジは、ソース シートに新しいデータを追加するたびに自動的に拡張できます。ダイナミックレンジを作成する手順は次のとおりです。

1. → 「式」タブ → 「定義された名前」 → 「名前マネージャー」に移動します。
2. 名前マネージャーをクリックすると、ポップアップ ウィンドウが表示されます。
   
3. [名前マネージャー] ウィンドウで、[新規] をクリックして名前付き範囲を作成します。
4. 新しい名前ウィンドウに次のように入力します。
   1. 新しい範囲の名前。ここでは「SourceData」という名前を使用します。
   2. 範囲の範囲を指定します。現在のワークシートまたはワークブックの間で指定できます。
   3. 名前付き範囲を説明するコメントを追加します。 「参照」入力バーに以下の式を入力します。
      

       =OFFSET(PivotTableData!$A$1,0,0,COUNTA(PivotTableData!$A:$A),COUNTA(PivotTableData!$1:$1))

5. 最後に、「OK」をクリックします。

これで、ピボット テーブルを作成するためのダイナミック レンジが得られました。

必要なのは、ソース データを使用してピボット テーブルを作成し、最初のテーブルの方法で使用したのと同じ方法を使用して、名前付き範囲でソースを変更するだけです。

新しいデータをソース シートに追加したら、ピボット テーブルを更新するだけです。

この式はどのように機能するのでしょうか?

上の式では、オフセット関数を使用してダイナミック レンジを作成しました。

開始点としてセル A1 を指定し、行と列については触れずに、COUNTA を使用して範囲の高さと幅を指定しました。

COUNTA は、列 A と行 1 の値を持つセルをカウントし、それに応じて高さと幅を拡張するように offset に指示します。

注意する必要があるのは、A 列と 1 行の間に空のセルがないことです。

VBAコードを使用してピボットテーブルを更新する

ほとんどの人は VBA コードを使用することを好みます。 VBA でピボット テーブルの範囲を更新するために使用するコードは次のとおりです。

 Sub UpdatePivotTableRange() Dim Data_Sheet As Worksheet Dim Pivot_Sheet As Worksheet Dim StartPoint As Range Dim DataRange As Range Dim PivotName As String Dim NewRange As String Dim LastCol As Long Dim lastRow As Long 'Set Pivot Table & Source Worksheet Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3") 'Enter in Pivot Table Name PivotName = "PivotTable2" 'Defining Staring Point & Dynamic Range Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol)) NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1) 'Change Pivot Table Data Source Range Address Pivot_Sheet.PivotTables(PivotName). _ ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange) 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated." End Sub

ワークブックで使用する前に変更する必要があるもの。

1. 13 行目: ソース ワークシートの名前を変更します。
2. 14行目：ピボットテーブルシートの名前を変更します。
3. 17行目：ピボットテーブル名を変更します。

このコードの使用中にまだ問題が発生する場合は、コメント ボックスに私に書いてください。ここで、必要に応じて簡単に変更できるように、このコードがどのように機能するかを説明します。

 Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3")

コードの上記の部分では、ソース データのピボット テーブルとワークシート変数を指定しました。ここからスプレッドシート名を変更できます。

 PivotName = "PivotTable2"

コードの上記の部分に、このコードを使用するピボット テーブルの名前を入力します。

 Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))

コードの上記の部分は、データ ソース ワークシートのセル A1 を使用してダイナミック レンジを作成します。

最後の列と最後の行のデータをチェックして、ダイナミック レンジを作成します。このマクロを実行するたびに、新しいダイナミック レンジが作成されます。

 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated."

コードの上記の部分はピボット テーブルを更新し、ピボット テーブルが更新されたことを示すメッセージをユーザーに表示します。