【Excel】ピボットテーブルで上位N件だけを表示するフィルターの使い方

ピボットテーブルで上位N件フィルターが役立つ場面

ピボットテーブルは、大量のデータを集計・分析するための強力なツールです。しかし、集計結果が多岐にわたる場合、どの項目が重要なのかを見つけ出すのが難しくなることがあります。

例えば、店舗別の売上データを集計した際に、全店舗のリストが表示されると、売上トップ10の店舗がどこにあるのか一目で分かりません。このような時に、「上位N件フィルター」が非常に有効です。

このフィルターを使うことで、売上上位の店舗だけ、あるいは回答数の多い選択肢だけを絞り込んで表示できます。これにより、データの中から最も注目すべき情報を素早く特定し、意思決定のスピードを上げることが可能になります。

また、マーケティング活動で顧客の購入頻度を分析する際にも、購入回数の多い上位顧客リストを作成することで、ターゲットを絞ったアプローチがしやすくなります。

この機能は、Excel 2010以降で利用可能であり、それ以前のバージョンでは利用できません。Excel for Microsoft 365、Excel 2021、Excel 2019、Excel 2016、Excel 2013、Excel 2010の各バージョンで同様の手順で設定できます。

上位N件フィルターの仕組みと設定方法

ピボットテーブルの上位N件フィルターは、「値フィルター」の一種です。これは、ピボットテーブルで集計された「値」に基づいて、表示する項目を絞り込む機能です。

具体的には、指定したフィールド(例:売上金額)の合計値や個数などが大きい順、または小さい順に並べ替えた際に、上位または下位のN件だけを表示するように設定します。

このフィルターは、ピボットテーブルの「行ラベル」または「列ラベル」に配置されたフィールドに対して適用できます。集計値が基準となるため、フィルターを適用したいフィールドの「値」エリアへの配置が前提となります。

ピボットテーブルで上位N件だけを表示する手順

ここでは、具体的なデータを用いて、ピボットテーブルで上位N件フィルターを設定する手順を説明します。

準備:ピボットテーブルの作成

まず、元データからピボットテーブルを作成します。以下の手順は、既にピボットテーブルが作成されていることを前提としています。

1. 元データの選択
   分析したいデータ範囲全体を選択します。
2. ピボットテーブルの挿入
   「挿入」タブをクリックし、「ピボットテーブル」を選択します。
3. ピボットテーブルの配置
   「新しいワークシート」または「既存のワークシート」を選択し、配置場所を指定して「OK」をクリックします。
4. フィールドの設定
   「ピボットテーブルのフィールド」ウィンドウで、分析したい項目を「行」「列」「値」エリアにドラッグ&ドロップします。「値」エリアには、集計したい数値データ(例:売上金額)を配置します。

上位N件フィルターの設定方法

ピボットテーブルが作成できたら、いよいよ上位N件フィルターを設定します。

1. フィルター対象フィールドの選択
   ピボットテーブル上で、上位N件を表示したい項目が配置されている「行ラベル」または「列ラベル」のフィールド名(例:「店舗名」)の横にあるフィルターボタン(下向き矢印)をクリックします。
2. 値フィルターの選択
   表示されるメニューから「値フィルター」にカーソルを合わせ、「上位N」を選択します。
3. 上位N件フィルターダイアログの設定
   「上位N件フィルター」ダイアログボックスが表示されます。ここで以下の設定を行います。
   • 1番目のドロップダウンリスト:「上位」を選択します。
   • 2番目のボックス(数値入力欄):表示したい件数(例:10)を入力します。
   • 3番目のドロップダウンリスト:集計値の基準となるフィールド(例:「売上金額」)を選択します。
   • 4番目のドロップダウンリスト:集計方法(例:「合計」)を選択します。
4. 設定の確定
   「OK」ボタンをクリックします。

これで、指定したフィールド(例:店舗名)において、集計値(例:売上金額の合計)が大きい上位10件だけがピボットテーブルに表示されるようになります。

並べ替え順序の確認と調整

上位N件フィルターを適用した際、ピボットテーブルの表示順序は、通常、集計値が大きい順(降順)になります。

もし、表示順序が意図したものと異なる場合は、以下の手順で並べ替え順序を確認・調整してください。

1. 並べ替え対象フィールドの選択
   ピボットテーブル上で、並べ替えたいフィールド(例:「店舗名」)のいずれかのセルを右クリックします。
2. 並べ替えメニューの選択
   表示されるメニューから「並べ替え」にカーソルを合わせ、「その他の並べ替えオプション」を選択します。
3. 並べ替えオプションの設定
   「並べ替え」ダイアログボックスが表示されます。「降順」または「昇順」を選択し、集計値の基準となるフィールド(例:「売上金額の合計」)が正しく選択されているか確認します。
4. 設定の確定
   「OK」ボタンをクリックします。

これにより、上位N件が表示されている中でも、集計値の大きい順に正しく並べ替えられた状態になります。

上位N件フィルターの応用と注意点

上位N件フィルターは、単に件数を絞るだけでなく、様々な応用が可能です。しかし、いくつか注意すべき点もあります。

「下位N件」の表示方法

「上位N件」だけでなく、「下位N件」を表示したい場合も、同様の手順で設定できます。「上位N件フィルター」ダイアログボックスの最初のドロップダウンリストで、「下位」を選択するだけです。例えば、売上が低い店舗のワースト10を表示したい場合などに利用できます。

「上位N%」や「上位Nアイテム」の表示

「上位N件」だけでなく、「上位N%」や「上位Nアイテム」という条件で絞り込むことも可能です。これは、「上位N件フィルター」ダイアログボックスの最初のドロップダウンリストで、「上位%」や「上位アイテム」を選択することで設定できます。

例えば、「上位10%」を選択すれば、全体の10%にあたる項目が表示されます。また、「上位アイテム」は、ピボットテーブルの集計方法(合計、個数など)に関わらず、単純に項目数で上位N件を抽出する際に使用します。

フィルターの解除方法

設定した上位N件フィルターを解除したい場合は、フィルターを設定したフィールドのフィルターボタンをクリックし、「値フィルター」から「すべて」を選択します。これにより、フィルターが解除され、全ての項目が表示されるようになります。

注意点:更新時の挙動

ピボットテーブルの元データが更新された場合、ピボットテーブルも更新する必要があります。更新すると、上位N件フィルターは、更新後のデータに基づいて再計算されます。例えば、元データに新しい店舗が追加され、その店舗の売上が上位に入った場合、自動的に表示対象に含まれます。

注意点:集計フィールドの選択ミス

「上位N件フィルター」ダイアログボックスで、集計値の基準となるフィールドや集計方法を間違えると、意図しない結果が表示されます。例えば、「売上金額」ではなく「数量」を基準にしてしまったり、「合計」ではなく「平均」で絞り込んでしまったりするケースです。設定画面で、基準となるフィールドと集計方法が正しいか、必ず確認してください。

注意点:フィルター対象フィールドの配置

上位N件フィルターは、「行ラベル」または「列ラベル」に配置されたフィールドに対して適用されます。また、そのフィールドの集計値が「値」エリアに配置されている必要があります。もし、これらの条件を満たしていない場合、フィルターオプションが表示されなかったり、意図通りに機能しなかったりします。ピボットテーブルのフィールド配置を見直してください。

注意点:Excel 2010より前のバージョンでの利用不可

前述の通り、この「上位N件フィルター」機能はExcel 2010以降で提供されています。Excel 2007以前のバージョンでは利用できません。古いバージョンのExcelを使用している場合は、代替手段(例:元データで並べ替えと行番号付けを行うなど)を検討する必要があります。

ピボットテーブルの上位N件フィルターと他のフィルター機能との比較

ピボットテーブルには、上位N件フィルター以外にも様々なフィルター機能があります。ここでは、代表的なものと比較してみましょう。

機能名	上位N件フィルター	ラベルフィルター	値フィルター(条件指定)
目的	集計値が大きい(または小さい)上位/下位N件の項目を表示	項目名(ラベル)自体に条件を設定して絞り込む	集計値に対して特定の条件(例:〇〇以上)を満たす項目を表示
設定対象	行ラベル/列ラベル(値エリアの集計値が基準)	行ラベル/列ラベル	行ラベル/列ラベル(値エリアの集計値が基準)
例	売上トップ10の店舗を表示	「東京」を含む店舗名を表示	売上合計が100万円以上の店舗を表示
応用	上位N%、上位Nアイテムでの絞り込みも可能	テキスト検索(前方一致、後方一致など)や特定文字の除外も可能	トップ〇〇、平均以上、〇〇から〇〇の範囲なども設定可能

上位N件フィルターは、「値フィルター」の一種であり、集計された数値に基づいて項目を絞り込む点で「値フィルター(条件指定)」と共通しますが、その中でも「件数」や「割合」で絞り込む点に特化しています。

一方、「ラベルフィルター」は、項目名そのものの文字列で絞り込むため、上位N件フィルターとは全く異なる目的で使用されます。

これらのフィルターを組み合わせることで、より複雑で詳細なデータ分析が可能になります。例えば、まず「ラベルフィルター」で特定の地域(例:関東地方)の店舗だけを表示し、その上で「上位N件フィルター」を適用して、関東地方の店舗の中で売上トップ5を表示するといった高度な絞り込みができます。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】