【Excel】ピボットテーブルで存在しない選択肢が出てくる場合の対処法！ – 削除済アイテムを保持しない設定

今回はピボットテーブルに関して、古いアイテムが残ってしまっているために、元データに存在しないデータが表示されてしまう問題の解決方法を紹介します。

なお、ここでいう元データに存在しないデータが表示されてしまう状態というのは、例えば以下のようなピボットテーブルで、列ラベルのプルダウンを押下した時に、

ピボットテーブルおよびその元データに存在しないデータ(2000,2001など)が、選択肢に存在している状態です。

解決方法：削除されたアイテムを保持しない

結論から言うと、ピボットテーブルのオプションで、削除済のアイテムを保持しない設定をすることで、解決することができます。

以下のように、ピボットテーブルを選択した状態で、ピボットテーブル分析 => オプションを選択します。

すると、以下のウィンドウが出てきます。ここで、”データ“タブから、”データソースから削除されたアイテムの保持“の設定を確認します。恐らく以下のように、”自動“が選択されています。これを”なし“に変更して、”OK”を押下し、このウィンドウを閉じます。

ピボットテーブルを更新してから、列ラベルのプルダウンを押下すると、以下のように、古いデータが表示されなくなっていることがわかります。

解決方法は上述の通りですが、ではなぜこのような問題が発生するのかを解説します。

Excelでは、ピボットテーブルが一度作成されると、その構成要素に関連する情報が内部的にキャッシュされます(=データが一時的に保存される)。このキャッシュにより、データソースの変更があっても、古いアイテムが表示リストに残ってしまうのです。

例えば今回のケースで言うと、元々テーブルが以下のような状態だったとします。テーブルは2000年頃のデータも含んでいます。そして、ピボットテーブルがこのタイミングで作成されたとすると、例えば”受注年”のデータとして、”2000″などもキャッシュされるということになります。

その後、このテーブルは適宜アップデートされ、以下のような状態になったとします。すべてのデータが、2021年以降のデータです。

しかし、その状態でピボットテーブルを更新してみても、既に存在しないデータがキャッシュされていることで、以下のように行ラベルとして”2000″とか”2001″といった、現時点では元テーブルに存在しない選択肢が表示されてしまうのです。

なお、過去にピボットテーブルの基礎的な内容は以下のように解説していますので、ご参照ください。