サイトについて      連絡先   検索

エクセル 2013 RANK, SMALL, LARGE 関数で条件に一致する順位の値を取得する

はじめに

エクセル 2013 で条件に一致する順位の値を取得する方法を紹介します。

RANK 関数」で条件に一致する順位を取得できます。

SMALL 関数」や「LARGE 関数」で条件に一致する順位の値を取得できます。

RANK 関数に条件を付ける

RANK 関数」には条件を付けられません。代わりに「COUNTIFS 関数」を次のように使って条件を指定できます。複数の条件や同率順位に対応できます。

=COUNTIFS(範囲,順序,条件範囲1,条件1,条件範囲2,条件2・・・)+1

本来の「COUNTIFS 関数」の引数名はこのとおりではありませんが、説明上この名前を使っています。

グループ分けをする条件

条件を分類でグループ分けして、その中で順位を付ける方法を紹介します。これが最もよくある条件だと思います。

昇順:
=COUNTIFS($D$4:$D$11,"<"&D3,$C$4:$C$11,C3)+1
降順:
=COUNTIFS($D$4:$D$11,">"&D3,$C$4:$C$11,C3)+1
1

引数「範囲」には、順位を付ける数値が入っている範囲を指定します。$D$4:$D$11

引数「順序」には、昇順にするなら"<"、降順にするなら">"を指定します。それに加えて順位を調べたい数値を指定します。"<"&D3

引数「条件範囲1」には、条件にする値が含まれるデータの範囲を指定します。分類でグループ分けしたいので、分類のデータが入っているセルの範囲を指定します。$C$4:$C$11

引数「条件1」には、条件にする分類を指定します。この分類と一致するものの中で順位付けされます。C3

最後に +1 します。これをしないと順位が 0 から始まります。

=COUNTIFS(範囲,順序)+1
引数「範囲」と「順序」だけ指定すれば「RANK 関数」と同じ動きになります。

SMALL 関数と LARGE 関数に条件を付ける その 1

SMALL 関数」や「LARGE 関数」に条件を付けるには、上記の「RANK 関数に条件を付ける」方法に加えて「VLOOKUP 関数」を使用します。条件も「グループ分けをする」と同じです。

まずは「VLOOKUP 関数」で検索できるようにするために検索専用列を作成します。条件と順位を結合します。条件は分類なので次のようになります。

昇順 (SMALL):
=C3&E3
降順 (LARGE):
=C3&F3
2

後は「VLOOKUP 関数」で検索するだけです。第 1 引数には検索専用列と同じ値を指定します。そのため条件と順位を結合した値にします。その他の引数については関数の使い方がわかっていれば問題ないはずなので説明は省略します。

=VLOOKUP(B14&C14,$B$4:$D$11,3,TRUE)
=VLOOKUP(B14&C14,$B$4:$D$11,3,FALSE)
3 4

第 4 引数が TRUE か FALSE で同率順位と検索値が存在しないときの動作が違います。

SMALL 関数と LARGE 関数に条件を付ける その 2

配列数式、CSE数式とは」を使用すると「SMALL 関数」や「LARGE 関数」に条件を付けられます。

=SMALL(IF($B$4:$B$11=B14,$C$4:$C$11),C14)
=LARGE(IF($B$4:$B$11=B14,$C$4:$C$11),C14)
Ctrl + Shift + Enter で確定
5 6

詳しくは「配列数式、CSE数式とは」をご覧ください。配列数式は複雑なので好んで使うものではありません。