Excel(エクセル)で大きい順や小さい順に順位を確認する際、フィルターを使って並べ替えしていませんか？
エクセルには、順位をつける『RANK関数』『RANK.EQ関数』『RANK.AVG関数』の３つの関数が存在します。
これらの３つの関数の特徴と違いと、それぞれの関数の使い方を紹介します。
また、最後の見出しでは、上位３項目や下位３項目のセルに自動で色をつける方法と、順位の重複データの確認方法を紹介します。
RANK関数とRANK.EQ関数とRANK.AVG関数の違い
順位をつける『RANK関数』『RANK.EQ関数』『RANK.AVG関数』の３つの関数の特徴と違いを紹介します。
- RANK関数(読み方：ランク)
- RANK.EQ関数(読み方：ランク・イコール)
- RANK.AVG関数(読み方：ランク・アベレージ)
この３つの関数の違いは、エクセルの関数の変革に隠されています。
まず、Excel2007のバージョンまでは、ランク付けする関数は、RANK関数しか存在しませんでした。
しかし、同じ順位の人が複数いる場合に、順位の表示方法を分けたいというニーズに応えるため、Excel2010のバージョンで、RANK関数はRANK.EQ関数とRANK.AVG関数の２種類の関数に分かれました。
RANK.EQ関数は、１位が２人いた場合、２人とも１位と表示します。
しかし、RANK.AVG関数の場合、１位が２人いた場合には、本来１位と２位で分け合う順位なので、１と２の平均で1.5位と表示されます。
RANK関数はRANK.EQ関数と全く同じ仕様です。いずれ使えなくなる関数でしょう。
長くなりましたが、３つの関数の違いは以下の通りです。
- RANK関数：RANK.EQ関数に置き換えられた旧関数
- RANK.EQ関数：同じ順位がいる場合は、上位のランクで表示
(例：１位、１位、3位) - RANK.AVG関数：同じ順位がいる場合は、平均値のランクで表示
(例：1.5位、1.5位、3位)
一般的には、順位を平均値で表す機会は少ないので、どの関数を使っていいか悩む場合は、『RANK.EQ関数』を使いましょう。
もちろん、３つの関数とも小さい順、大きい順のどちらでも順位付けすることが出来ます。
RANK.EQ関数を使って順位をつける方法
それでは、RANK.EQ関数を使って順位をつける方法を紹介します。
上で紹介したとおり、RANK関数と同じ仕様ですが、新関数のRANK.EQ関数を使うようにしましょう。
RANK.EQ関数の構文と引数
まず、RANK.EQ関数の構文と引数を紹介します。
＝RANK.EQ(数値,範囲,[順序])
第１引数【数値】：セル 範囲内での順位 (位置) を調べるセル番号を指定します。(例：A3,”15”)
第２引数【範囲】： 数値のセル範囲を指定します。セル 範囲内に数値以外が含まれる場合は無視されます。(例：B2:B10)
第３引数【[順序]】：入力を省略できます。 順序に「0」 (ゼロ) を指定するか省略すると、範囲内の数値が、大きい順に順位付けされます。「0」以外の数値を指定すると、小さい順に順位付けされます。
RANK.EQ関数で大きい順に順位をつける方法
下の点数表を使って、大きい順に順位を付ける手順を紹介します。
手順１．RANK.EQ関数を挿入
RANK.EQ関数を挿入して、第１引数に順位をつけたいセルを指定します。
=RANK.EQ(C3,
手順２．RANK.EQ関数の第２引数を指定
続いて、第２引数【範囲】を指定するために、順位付けする対象のセル範囲をドラッグします。
=RANK.EQ(C3,C3:C10
手順３．第２引数を絶対参照に設定する
手順２の直後にファンクションキー「F2」キーを１回押して、絶対参照に設定します。下のように、列番号と行番号に「$」マークが付きます。
=RANK.EQ(C3,$C$3:$C$10
第２引数を絶対参照に設定する理由は、RANK.EQ関数を下のセルにコピペした時に、セル範囲が移動しないよう固定するためです。
手順４．RANK.EQ関数を確定
「Enter」キーを押してRANK.EQ関数を確定してください。セル範囲の中で点数が何番目に大きいか順位が表示されます。
手順５．RANK.EQ関数を下のセルにコピペ
RANK.EQ関数を下のセルにコピペしてください。
「Ctrl＋C」キーのショートカットキーでコピーして、「Ctrl＋V」キーのショートカットキーで貼り付けします。(右クリックメニューを使っても構いません。)
以上で、大きい順で順位付けが完了しました。
点数「76」点は２人いるため、「２位」は同順で２人いて、「３位」がいないという表示になってます。
フィルターを使って順位の良い人から並べ替えしても関数の結果は崩れません。
必要に応じて並べ替えしましょう。フィルターの設定方法はこちらの記事を参考にしてください。
RANK.EQ関数で小さい順に順位をつける
今度は以下の50ｍ走の結果を使って、小さい順に順位を付ける手順を紹介します。
手順１．RANK.EQ関数を挿入
RANK.EQ関数を挿入して、第１引数に順位をつけたいセルを指定します。
=RANK.EQ(C3,
手順２．RANK.EQ関数の第２引数を指定
続いて、第２引数【範囲】を指定するために、順位付けする対象のセル範囲をドラッグします。
=RANK.EQ(C3,C3:C10
手順３．第２引数を絶対参照に設定する
手順２の直後にファンクションキー「F2」キーを１回押して、絶対参照に設定します。下のように、列番号と行番号に「$」マークが付きます。設定したら、引数を区切るために、「,」(カンマ)を入力します。
=RANK.EQ(C3,$C$3:$C$10,
第２引数を絶対参照に設定する理由は、RANK.EQ関数を下のセルにコピペした時に、セル範囲が移動しないよう固定するためです。
手順４．RANK.EQ関数の第３引数を入力
小さい順に順位を付ける場合は、第３引数の入力が必須です。
「0」は大きい順で利用する引数なので、「0」以外の数値を指定します。この説明では「1」を入力します。
=RANK.EQ(C3,$C$3:$C$10,1)
手順５．RANK.EQ関数を確定
「Enter」キーを押してRANK.EQ関数を確定してください。点数のセル範囲の中で何番目に小さいか順位が表示されます。
手順６．RANK.EQ関数を下のセルにコピペ
RANK.EQ関数を下のセルにコピペしてください。
「Ctrl＋C」キーのショートカットキーでコピーして、「Ctrl＋V」キーのショートカットキーで貼り付けします。(右クリックメニューを使っても構いません。)
以上で、小さい順で順位付けが完了しました。
タイム「7.4」秒は２人いるため、「２位」は同順で２人いて、「３位」がいないという表示になってます。
フィルターを使って順位の良い人から並べ替えしても関数の結果は崩れません。
必要に応じて並べ替えしましょう。フィルターの設定方法はこちらの記事を参考にしてください。
RANK.AVG関数を使って順位をつける方法
続いて、RANK.AVG関数を使って順位をつける方法を紹介します。
RANK.EQ関数と構文や引数、使い方は同じです。
ランク・アベレージの関数名のとおり、同じ順位が複数いる場合のみ返す値が特殊です。
例えば、１位が２人いた場合には、１と２の平均で1.5位と表示されます。
２位が２人いた場合は、２と３と平均で、2.5位と表示されます。
RANK.AVG関数の構文と引数
まず、RANK.AVG関数の構文と引数を紹介します。
＝RANK.AVG(数値,範囲,[順序])
第１引数【数値】：セル 範囲内での順位 (位置) を調べるセル番号を指定します。(例：A3,”15”)
第２引数【範囲】： 数値のセル範囲を指定します。セル 範囲内に数値以外が含まれる場合は無視されます。(例：B2:B10)
第３引数【[順序]】：入力を省略できます。 順序に「0」 (ゼロ) を指定するか省略すると、範囲内の数値が、大きい順に順位付けされます。「0」以外の数値を指定すると、小さい順に順位付けされます。
RANK.AVG関数で大きい順に順位をつける
RANK.AVG関数を使って、大きい順に順位を付ける手順を紹介します。
手順１．RANK.AVG関数を挿入
RANK.AVG関数を挿入します。
=RANK.AVG(C3,$C$3:$C$10)
手順２．RANK.AVG関数を確定
「Enter」キーを押してRANK.AVG関数を確定してください。点数のセル範囲の中で何番目に大きいか順位が表示されます。
手順３．RANK.AVG関数を下のセルにコピペ
RANK.AVG関数を下のセルにコピペしてください。
手順４．表示桁数を増やす
同じ順位がいる場合、順位が平均値で算出されるので小数点の可能性があります。「ホーム」タブの「小数点を増やす」コマンドを使って、小数第１位まで表示します。
手順５．完成
以上で、大きい順で順位付けが完了しました。
点数「76」点は２人いるため、「2位」と「3位」の平均値の「2.5位」が表示されます。
RANK.AVG関数で小さい順に順位をつける
RANK.AVG関数を使って、小さい順に順位を付ける手順を紹介します。
手順１．RANK.AVG関数を挿入
RANK.AVG関数を挿入します。小さい順で利用するので、３つ目の引数「順序」に「１」を入力しましょう。
=RANK.AVG(C3,$C$3:$C$10,1)
手順２．RANK.AVG関数を確定
「Enter」キーを押してRANK.AVG関数を確定してください。点数のセル範囲の中で何番目に小さいか順位が表示されます。
手順３．RANK.AVG関数を下のセルにコピペ
RANK.AVG関数を下のセルにコピペしてください。
手順４．表示桁数を増やす
同じ順位がいる場合、順位が平均値で算出されるので小数点の可能性があります。「ホーム」タブの「小数点を増やす」コマンドを使って、小数第１位まで表示します。
手順５．完成
以上で、小さい順で順位付けが完了しました。
タイム「7.4」点は２人いるため、「2位」と「3位」の平均値の「2.5位」が表示されます。
上位/下位の情報を抽出する方法
RANK.EQ関数やRANK.AVG関数を使って順位を付けた後に、上位や下位の順位の情報を抽出する方法を紹介します。
まず情報を抽出するための入力枠を作成します。
次に、情報を抽出することが出来る以下のVLOOKUP関数を挿入して、順位に一致する「氏名」を抽出します。
=VLOOKUP(F3,$B$3:$D$9,2,FALSE)
次に、順位に合う「点数」を抽出するため、隣の列に再びVLOOKUP関数を挿入します。
=VLOOKUP(F3,$B$3:$D$9,3,FALSE)
以上で、上位３項目の情報を抽出することが出来ました。
VLOOKUP関数の使い方に慣れていない人は、以下の記事で詳しく紹介していますので参考にしてみて下さい。
上位/下位ルールを使って自動で色をつける方法
上位や下位の項目に自動で色を付けて表示する方法を紹介します。
順位を数字で表す必要がなく、目立たせたいことが目的であればお勧めの方法です。
まずは上位３項目に緑色を自動で付ける設定を行います。
色を表示したいセル範囲を選択して、「ホーム」タブ⇒「条件付き書式」⇒「上位/下位ルール」の「上位10項目」を選択します。
上位の項目数を「３」に設定し、書式を「濃い緑の文字、緑の背景」を選択し、「OK」を押します。
そうすると、上位３項目に自動で色が付けられます。
続いて、下位３項目に赤色を自動で付ける設定を行います。
色を表示したいセル範囲を選択して、「ホーム」タブ⇒「条件付き書式」⇒「上位/下位ルール」の「下位10項目」を選択します。
下位の項目数を「３」に設定し、書式を「濃い赤の文字、明るい赤の背景」を選択し、「OK」を押します。
そうすると、下位３項目にも自動で色が付けられます。
もちろん、条件付き書式を設定した後にセルの数値を変更しても、色の付くセルは自動で変更されます。
このように「条件付き書式」を利用すると、上位/下位の数項目に簡単に色付けが可能です。
「条件付き書式」の「上位/下位ルール」には、「上位10％」や「下位10％」に色付けするメニューも存在します。
用途に合わせて利用してみてください。
重複データを確認する方法
ランク付けする際、順位が重複しているかどうかは重要なポイントです。
そこで、重複データを確認して抽出する方法を紹介します。
まず重複データを抽出したい表を選択します。
「ホーム」タブの「条件付き書式」から「セルの強調表示ルール」を選び、「重複する値」をクリックします。
「重複する値」画面が表示されるので、条件を設定します。
左側のプルダウン項目は「重複」を選択し、右側の「書式」はプルダウンリストから任意の項目を選択して「ＯＫ」をクリックします。
「書式」を変えることで、重複セルにどの色を付けるか、文字色を変えるかなどを選択することが出来ます。
列ごとに「重複する値」が抽出されますので、視覚的に簡単に確認することが出来ます。
