【Excel】ランキング分析の超便利関数★
こんにちは、HARUです！
実務では営業拠点ごとの業績や担当者別の成約件数実績を順位づけすることがよくありますよね。
この記事では、それぞれのデータの順位を求めたり、指定した順位に該当するデータを抽出したりする関数を解説します。
サンプルは支店別販売実績の「対前年伸長率」です。
E列に伸長率の順序を表示し、H列に上から数えて1～3番目の値、下から数えて1～3番目の値をそれぞれ取り出します。
１つ１つの関数の役割をおさえて、シーンに応じて使い分けていきましょう！
データの順序を求める
RANK関数
参照した値が全体の中で何番目に位置しているかを求めるには、RANK関数を使います。
"=RANK"と入力すると、以下の候補が出てきます。
①RANK
②RANK.EQ
③RANK.AVG
それぞれの違いは後ほど解説するとして、まずはRANK関数を挿入します。
第１引数「数値」は順位を求める値を指しています。
今回の場合は伸長率が入力されたセルを参照します。
第２引数「参照」は順位の母数となるすべてのデータです。
今回の場合は伸長率の範囲を選択します。
この後、数式を下方向にコピーします。
第１引数の「数値」の参照は下にスライドさせていくので相対参照とし、第２引数の「参照」は下にコピーしてもズレて欲しくないので絶対参照にしておきます。
※参照形式は[F4]で変更するんでしたね！
第３引数「順序」では、「降順（大きい順の順序）」＝"0"か、「昇順（小さい順の順序）」="1"かを選択します。
※RANK関数は降順の順位付けがデフォルトになっており、第３引数を指定しなければ大きい順にランキングされます。今回は伸長率の大きい順に順位を付けますので入力しなくても問題ありません。
A支店の伸長率"112%"の順位として、"4"番目が返ります。
この数式を下へコピーします。
RANK.EQとRANK.AVGの違い
RANK関数を入力したときに候補として出てきた「RANK.EQ」と「RANK.AVG」は、RANK関数の後継となる関数です。
これら２つの関数は、同率順位の処理(表示)方法に応じて使い分けます。
▶RANK.EQの場合
同率4位のA支店"112%"とJ支店"112%"には、それぞれ"4"が表示されます。これはRANK関数と同じ結果です。
▶RANK.AVGの場合
同率4位のA支店"112%"とJ支店"112%"には、それぞれ"5"が表示されています。
小数点以下の値を見てみると、実際には"4.5"となっています。
RANK.EQ（equal：同等、等しい）が同率順位をそのまま返すのに対し、RANK.AVG（average：平均）は順位の平均を返します。
今回のように同率4位が2つある場合、次の順位は6位からカウントされますので、この2つの値は4位と5位にあたります。
RANK.AVGはこの"4"と"5"の平均値である"4.5"を返したのです。
※たとえば3つの値が同率7位だった場合、RANK.EQはすべてに"7"位を返しますが、これら3つの値は全体において7位,8位,9位にあたるため、RANK.AVGでは平均値"8"位を表示するということです。
基本的にはRANK関数と同じ役割をもつRANK.EQ関数を使えばOKですし、RANK関数が使える環境であれば引き続きRANK関数を用いても問題ありません。
指定の順位に該当するデータを取り出す
ここからは切り口を変えて、指定した順位に該当するデータを抽出します。
LARGE関数
指定した順位を降順（大きい順）で照合して該当のデータを取り出すときは、LARGE関数を使います。
第１引数「配列」は伸長率の範囲を絶対参照で参照します。
第２引数「順位」には求めたい順位を指示します。直接数値を入力しても結構ですし、下図のようにセル参照を活用してもOKです。
結果として、対象データの中で１番大きい値"130%"が返ります。
入力した数式を下へコピーすると、全体における上位３つの値が表示されます。
SMALL関数
指定した順位を昇順（小さい順）で照合して該当のデータを取り出すときは、SMALL関数を使います。
関数の構成はLARGE関数と同じです。
第１引数「配列」は伸長率の範囲を絶対参照で参照します。
第２引数「順位」には求めたい順位を指示します。
結果として、対象データの中で１番小さい値"72%"が返ります。
入力した数式を下へコピーすると、全体における下位３つの値が表示されます。
LARGE関数とSMALL関数を単体で使うと値を取り出すだけの機能に留まりますが、検索関数と組み合わせることで上位３つの支店名、下位３名の担当者、などを表示することもできます。
これらのテクニックは今後、別の記事で解説いたしますね！
いかがでしたか？
今回はそれぞれのデータの順位を求めたり、指定した順位に該当するデータを抽出したりする関数をご紹介しました。
全データの順位を俯瞰的に見たいのか、トップ３・ワースト３などの定点観測をしたいのかによって、それぞれの関数を使い分けていきましょう！
↓↓記事の内容を動画で解説しています↓↓
※本記事の委細が動画収録当時のバージョン・解説内容と異なる場合があります。
↓↓Excel操作をとにかく高速化したい方へ↓↓