Excelで動的なトップ10リストを作成する8つの方法

トップリストとは、売上や成績などの数値をもとに、企業や個人をランキング形式で表示するものです。順位は業績や品質など、さまざまな基準に基づいて決められます。この記事では、Excelで動的なトップ10リストを作成する方法を、8つのアプローチに分けて詳しく解説します。元データが更新されても自動的に反映される「動的」なリストを作れば、手作業での並べ替えが不要になり、業務効率が大きく向上します。

Excelで動的なトップ10リストを作成する8つの方法

ここでは、動的なトップ10リストを作成する複数の方法を紹介します。トップ10リストがあれば、特定の分野における上位の名前や数値をひと目で把握できます。

今回は、生徒の点数をまとめたサンプルデータセットを使用します。このデータをもとに、動的なトップ10リストを作成していきましょう。

1. LARGE関数を使ってトップ10リストを作成する

LARGE関数は、指定した範囲からk番目に大きい数値を抽出する関数です。

まず、データセットの下に1〜10の連番を入力しておきます。

最初にトップ10の点数を求め、その後、対応する生徒の名前を取得します。

ステップ1:

• LARGE関数とROW関数を組み合わせた数式を使用します。
• セルG5を選択し、以下の数式を入力します。

=LARGE($C$5:$C$18,ROW(F5)-ROW($F$4))

ステップ2:

• Enterキーを押し、フィルハンドルを下方向へドラッグします。

これでトップ10の点数が表示されました。同じ目的にはROWS関数を使うこともできます。

代替数式:

同じ結果を得るための別の数式も紹介します。

手順:

• セルG5に以下の数式を入力します。

=LARGE($C$5:$C$18,ROWS(C$5:C5))

• Enterキーを押し、フィルハンドルを最下部までドラッグします。

次に、トップ10の点数に対応する生徒の名前を表示したい場合は、以下のように操作します。

手順:

• セルF5に以下の数式を入力します。

=INDEX($B$5:$B$18,MATCH(G5,$C$5:$C$18,0))

• Enterキーを押し、フィルハンドルをドラッグします。

これで、点数とともにトップ10の名前が表示されました。

2. INDEX・MATCH・LARGE関数を組み合わせてトップ10リストを作成する

このセクションでは、INDEX関数、MATCH関数、LARGE関数を組み合わせる方法を紹介します。この方法なら、補助列を使わずに直接トップ10の名前を取得できます。

ステップ1:

• セルF5を選択します。
• 以下の数式を入力します。

=INDEX($B$5:$B$18,MATCH(LARGE($C$5:$C$18,E5),$C$5:$C$18,0))

ステップ2:

• Enterキーを押し、フィルハンドルをドラッグします。

別の列を介さずに、トップ10の名前を直接取得できました。

数式の解説:

• LARGE($C$5:$C$18,E5)

セルE5の値に基づいて、範囲内から該当順位の数値を返します。

結果: 96

• MATCH(LARGE($C$5:$C$18,E5),$C$5:$C$18,0)

その数値が範囲内の何番目にあるか(相対位置)を返します。

結果: 10

• INDEX($B$5:$B$18,MATCH(LARGE($C$5:$C$18,E5),$C$5:$C$18,0))

範囲内から対応する名前を取り出し、トップ10の結果を表示します。

結果: {Jose, Henderson, Gerg, Josef, Aaron, Jose, Jessica, Joe, Rogers, Allisa}

3. 配列数式を使って動的なトップ10リストを作成する

この方法では配列数式を使用します。INDEX関数、SORT関数、SEQUENCE関数を組み合わせた数式で、トップ10の名前と点数を同時に表示できます。

ステップ1:

• セルF5を選択します。
• 以下の数式をコピーして貼り付けます。

=INDEX(SORT(B5:C18,2,-1),SEQUENCE(10),{1,2})

ステップ2:

• Enterキーを押すだけです。

数式を1つ入力するだけで、名前と点数の両方が表示されました。スピル機能により、数式をドラッグしてコピーする必要もありません。

数式の解説:

• SEQUENCE(10)

1から10までの連番を生成します。

結果: {1,2,3,4,5,6,7,8,9,10}

• SORT(B5:C18,2,-1)

2列目(点数)を基準にデータを降順で並べ替えます。

結果: {Jose 96, Henderson 95, Gerg 89, Josef 84, Aaron 80, Jose 71, Jessica 70, Joe 65, Rogers 61, Allisa 60, Joe 57, Allisa 49, John 45, Mitchel 40}

• INDEX(SORT(B5:C18,2,-1),SEQUENCE(10),{1,2})

並べ替えたデータから上位10件を抽出します。

結果: {Jose 96, Henderson 95, Gerg 89, Josef 84, Aaron 80, Jose 71, Jessica 70, Joe 65, Rogers 61, Allisa 60}

4. XLOOKUP関数を使ってトップ10リストを作成する

XLOOKUP関数は、指定した範囲や配列から値を検索し、一致する結果を返す関数です。

このセクションでは、XLOOKUP関数とLARGE関数を組み合わせます。ここではトップ10の名前一覧だけを取得します。

ステップ1:

• セルF5に以下の数式を入力します。

=XLOOKUP(LARGE($C$5:$C$18,E5),$C$5:$C$18,$B$5:$B$18)

ステップ2:

• Enterキーを押します。
• その後、フィルハンドルをドラッグします。

5. SORT関数とFILTER関数を組み合わせてトップ10リストを作成する

FILTER関数を使うと、指定した条件に合う範囲内の値だけを抽出できます。

このセクションでは、SORT関数とFILTER関数をLARGE関数と組み合わせて、トップ10リストを作成します。

ステップ1:

• セルF5を選択します。
• 以下の数式を入力します。

=SORT(FILTER(B5:C18,C5:C18>=LARGE(C5:C18,10)),2,-1)

ステップ2:

• Enterキーを押すだけで完了です。

この方法では、生徒の名前と点数の両方を一度に取得できます。

関連記事: 条件(単一・複数)に基づいてExcelで動的なリストを作成する方法

6. 重複データがある場合の動的なトップ10リストの作成

データに重複がある場合は、この方法を使うと重複を避けながらトップ10リストを作成できます。

ステップ1:

• セルF5を選択します。
• 以下の数式を入力します。

=INDEX($B$5:$B$18, MATCH(1, ($C$5:$C$18=LARGE($C$5:$C$18, E5)) * (COUNTIF(F$4:F4, $B$5:$B$18)=0), 0))

ステップ2:

• Enterキーを押します。

これで、重複しない生徒の名前だけが表示されました。

7. 条件付き書式・フィルター・並べ替えツールを組み合わせて動的なトップ10リストを作成する

数式を使わない方法として、条件付き書式、フィルター、並べ替えという複数のツールを組み合わせることもできます。

ステップ1:

• セルC5からC18を選択します。
• リボンの条件付き書式をクリックします。
• 上位/下位ルールから上位10項目を選択します。

ステップ2:

• ダイアログボックスが表示されるので、「上位に表示する」が10になっていることを確認します。
• OKをクリックします。

ステップ3:

• セルC4からC18を選択します。
• マウスの右ボタンをクリックします。
• フィルターの一覧から選択したセルの色でフィルターを選びます。

ステップ4:

• 色フィルターオプションをクリックします。

これでトップ10のデータが抽出されました。続いて並べ替えを行います。

ステップ5:

• 再びマウスの右ボタンをクリックします。
• 並べ替えの一覧から大きい順に並べ替えを選択します。

ステップ6:

• 選択範囲を拡張するを選択します。
• その後、並べ替えをクリックします。

これで、トップ10のデータがきれいに並べ替えられました。

関連記事: ExcelでVBAを使って動的なデータの入力規則リストを作成する方法

8. ピボットテーブルを使ってトップ10リストを作成する

最後に、ピボットテーブルを使ってトップ10リストを作成する方法を紹介します。

ステップ1:

• 挿入タブを開きます。
• PivotTableの一覧からテーブル/範囲からを選択します。

ステップ2:

• データセットから対象の範囲を選択します。
• OKをクリックします。

ステップ3:

• PivotTableのフィールドでNameとMarkにチェックを入れます。

ステップ4:

• 行ラベルを開きます。
• 値フィルターからトップテンを選択します。

ステップ5:

• ダイアログボックスが表示されたら、OKをクリックします。

これでトップ10リストが完成しました。ただし、この時点ではデータが並べ替えられていない状態です。

関連記事: Excelでテーブルから動的なリストを作成する3つの簡単な方法

まとめ

この記事では、Excelで動的なトップ10リストを作成する8つの方法を解説しました。LARGE関数のような基本関数から、SORT・FILTER・XLOOKUPといった新しい関数、さらにはピボットテーブルまで、目的やExcelのバージョンに応じて最適な方法を選んでください。本記事が皆さんの作業のお役に立てば幸いです。ぜひ弊サイトExceldemy.comもご覧いただき、コメント欄でご意見をお聞かせください。

関連記事

• ExcelのOFFSET関数を使って動的なドロップダウンリストを作成する(3つの方法)
• ExcelでVBAを使って動的なドロップダウンリストを作成する方法