Excelで拡張するデータを自動更新する動的なピボットテーブルを作成するにはどうすればよいですか？

通常、ピボットテーブルは元のデータ範囲内の更新されたデータで更新できます。しかし、新しいデータを元の範囲に追加した場合（例えば、元の範囲の下や右側に新しい行や列のデータを追加した場合）、その拡張されたデータは手動で更新してもピボットテーブルに追加されません。Excelで拡張するデータを持つピボットテーブルをどのように更新すればよいでしょうか？この記事の方法がお役に立つでしょう。

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

元の範囲を表範囲に変換して動的なピボットテーブルを作成する

元のデータを表に変換することで、Excelでピボットテーブルを拡張データとともに更新することができます。以下の手順に従ってください。

1. データ範囲を選択し、Ctrl + Tキーを同時に押します。開いたテーブルの作成ダイアログで、OKボタンをクリックします。

2. これで元のデータが表範囲に変換されました。表範囲を選択したまま、「挿入」＞「ピボットテーブル」をクリックします。

3. 「ピボットテーブルの作成」ウィンドウで、ピボットテーブルを配置する場所を選択し、「OK」をクリックします。（この場合、ピボットテーブルを現在のワークシートに配置します）

4. ピボットテーブルフィールドペインで、フィールドを対応するエリアにドラッグします。

5. 新しいデータを元の範囲の下または右に追加した場合、ピボットテーブルに移動して右クリックし、コンテキストメニューから「更新」をクリックします。

以下のように、ピボットテーブルが拡張データで更新されていることが確認できます。

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

このセクションでは、OFFSET関数を使って動的なピボットテーブルを作成する方法をご紹介します。

1. 元のデータ範囲を選択し、「数式」＞「名前マネージャー」をクリックします。スクリーンショットをご覧ください：

2. 名前マネージャーウィンドウで、「新規」ボタンをクリックして「名前の編集」ダイアログを開きます。このダイアログで次の操作が必要です：

• 名前ボックスに範囲の名前を入力します；
• 以下の数式をコピーして 参照先 ボックスに貼り付けます；
  =OFFSET('dynamic pivot with table'!$A$1,0,0,COUNTA('dynamic pivot with table'!$A:$A),COUNTA('dynamic pivot with table'!$1:$1))
• 「OK」ボタンをクリックします。

注: この数式では、'dynamic pivot with table' は元の範囲を含むワークシートの名前です。$A$1 は範囲の最初のセル、$A:$A は範囲の最初の列、$1:$1 は範囲の最初の行です。これらは自分の元のデータ範囲に基づいて変更してください。

3. 名前マネージャーウィンドウに戻ると、新しく作成された名前範囲がウィンドウに表示されますので、閉じてください。

4. 「挿入」＞「ピボットテーブル」をクリックします。

5. 「ピボットテーブルの作成」ウィンドウで、ステップ2で指定した範囲名を入力し、ピボットテーブルを配置する場所を選択してから「OK」ボタンをクリックします。

6. ピボットテーブルフィールドペインで、フィールドを対応するエリアにドラッグします。

7. 元の範囲に新しいデータを追加した後、「更新」オプションをクリックすると、ピボットテーブルのデータが更新されます。

関連記事