この記事では、Excelでデータを追加・更新しても自動的にランキング順に並べる方法を紹介します。
SORT関数を使用する方法
まず、SORT関数を使用する方法を紹介します。この関数はMicrosoft 365、Excel 2021以降で使用できます。
たとえば、以下のような営業成績表を売上のランキング順に並べたいと思います。
営業成績表を選択し、「Ctrl+T」で表をテーブルに変換します。
これで「OK」を押せば、表がテーブルに変換されます。テーブルに変換する理由は、データの追加を自動的に反映させるためです。
次にランキング順に並べるための表を追加します。
この表にSORT関数を使ってランキング順に並べ替えたデータを表示します。
| SORT(配列,並べ替えインデックス,並べ替え順序,並べ替え基準) |
|---|
| 指定した配列を並べ替えの基準としたい列や行を指定し、並べ替えを行う。並べ替えの順序は昇順「1」、降順「-1」から選択できる。また、並べ替えの基準は列「TRUE」か行「FALSE」を選択する。 |
表の左上のセルに以下のように数式を入力します。「並べ替えインデックス」には「売上」の列を指定するため、「2」列目と記載します。並べ替えは降順（大きい順）の「-1」を指定します。
=SORT(テーブル3,2,-1,FALSE)
これで売上の大きい順にデータを並べ替えることができます。
表をテーブルに変換しているので、下にデータを追加すれば、自動的にSORT関数の数式にも反映されます。
このランキング順に並べ替えた表を活用してグラフを活用すれば、以下のように常に上位5位だけを表示させるようなグラフを作成することができます。
VLOOKUP関数を使用する方法
SORT関数が使用できない場合は、VLOOKUP関数を使っても同じように並び替えることができます。
まず、元の表に順位を表示させる列を追加します。
順位はRANK.EQ関数を使って表示させます。
| RANK.EQ(数値,参照,[順序]) |
|---|
| 指定した「数値」が参照した範囲内で何番目の大きさかを示す。順序では、「0」を指定すると降順（大きい順）、「1」を指定すると昇順（小さい順）の順位を示す。 |
数値には各人の売上を選択し、参照範囲には売上の列全体を選択します。順位は降順（大きい順）に付けていくため、順序に「0」を指定します。
=RANK.EQ([@売上],[売上],0)
これで売上に基づき、順位が表示されます。
ランキング順に表示させるための表には、あらかじめ順位をオートフィルなどで入力しておきます。
それぞれの順位に対応する「氏名」と「売上」を表示させるため、VLOOKUP関数を使用します。
| VLOOKUP(検索値,範囲,列番号,[検索方法]) |
|---|
| 検索値で指定した値を、範囲の左端列から検索。検索値と一致するデータの左から列番号目の値を抽出し表示する。 |
ここでは、検索値に順位を指定し、範囲に元の表を選択します。列番号は「氏名」と「売上」の列を表示させるため2列目と3列目を指定します。この際、{}で囲んで指定すれば、2列目、3列目を一気に選択できます。
=VLOOKUP(E3,テーブル35,{2,3},FALSE)
これで順位に対応する「氏名」と「売上」が表示されます。
ただし、この方法の注意点は同じ順位の人が2名いる場合は、順位に該当する人がおらず、正しく表示されないことがあります。
その場合は、適宜順位を変更する必要があります。
コメント