更新日：、 作成日：
エクセル RANK, SMALL, LARGE 関数で条件に一致する順位の値を取得する
はじめに
エクセルで条件に一致する順位の値を取得する方法を紹介します。
「RANK 関数」で条件に一致する順位を取得できます。
「SMALL 関数」や「LARGE 関数」で条件に一致する順位の値を取得できます。
RANK 関数に条件を付ける
「RANK 関数」には条件を付けられません。代わりに「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
引数「範囲」には、順位を付ける数値が入っている範囲を指定します。 $D$4:$D$11
引数「順序」には、昇順にするなら "<" 、降順にするなら ">" を指定します。それに加えて順位を調べたい数値を指定します。 "<"&D3
引数「条件範囲1」には、条件にする値が含まれるデータの範囲を指定します。分類でグループ分けしたいので、分類のデータが入っているセルの範囲を指定します。 $C$4:$C$11
引数「条件1」には、条件にする分類を指定します。この分類と一致するものの中で順位付けされます。 C3
最後に +1 します。これをしないと順位が 0 から始まります。
引数「範囲」と「順序」だけ指定すれば「RANK 関数」と同じ動きになります。
SMALL 関数と LARGE 関数に条件を付ける その 1
「SMALL 関数」や「LARGE 関数」に条件を付けるには、上記の「RANK 関数に条件を付ける」方法に加えて「VLOOKUP 関数」を使用します。条件も「グループ分けをする」と同じです。
まずは「VLOOKUP 関数」で検索できるようにするために検索専用列を作成します。条件と順位を結合します。条件は分類なので次のようになります。
=C3&E3
降順 (LARGE):
=C3&F3
後は「VLOOKUP 関数」で検索するだけです。第 1 引数には検索専用列と同じ値を指定します。そのため条件と順位を結合した値にします。その他の引数については関数の使い方がわかっていれば問題ないはずなので説明は省略します。
=VLOOKUP(B14&C14,$B$4:$D$11,3,FALSE)
第 4 引数が TRUE か FALSE で同率順位と検索値が存在しないときの動作が違います。
- TRUE を指定すると、昇順のときに同率順位があったとき、その順位でも次の順位でも値を取得できます。降順だと正しい値が取得されません。また、検索値が存在しないときに近い値が取得される可能性があります。
- FALSE を指定すると、同率順位があったときに次の順位を指定するとエラーになります。また、検索値が存在しないときは必ずエラーになります。
SMALL 関数と LARGE 関数に条件を付ける その 2
「配列数式、CSE数式とは」を使用すると「SMALL 関数」や「LARGE 関数」に条件を付けられます。
=LARGE(IF($B$4:$B$11=B14,$C$4:$C$11),C14)
Ctrl + Shift + Enter で確定
バージョン 365 なら「スピル、動的配列数式の使い方」を使用して条件を付けられます。数式は上記と同じで Ctrl + Shift + Enter を入力する必要はありません。
詳しくは「スピル、動的配列数式の使い方」または「配列数式、CSE数式とは」をご覧ください。複雑なので数式を理解するのが難しいです。