Office 2021 で、あなたにぴったりの学びや活用方法が見つかります
Office 2021 はビジネスだけでなく、お子さまの家庭学習から、プライベートでもご利用いただけます。
Office はビジネスだけでなく、プライベートでも活用できます。
趣味や、友人との交流で使ったり、カフェで簡単な仕事を片づけたり。便利に使える機能を使って、もっと Office を活用していきましょう。
excel RANK, SMALL, LARGE 関数で条件に一致する順位の値を取得する
はじめに
excelで条件に一致する順位の値を取得する方法を紹介します。
「RANK 関数」で条件に一致する順位を取得できます。
「SMALL 関数」や「LARGE 関数」で条件に一致する順位の値を取得できます。
ランキングを抽出するには「数値のランキングの上位や下位を抽出する」をご覧ください。
目次
RANK 関数に条件を付ける
SMALL 関数と LARGE 関数に条件を付ける その 1
SMALL 関数と LARGE 関数に条件を付ける その 2
RANK 関数に条件を付ける
「RANK 関数」には条件を付けられません。代わりに「COUNTIFS 関数」を次のように使って条件を指定できます。複数の条件や同率順位に対応できます。
=COUNTIFS(範囲,順序,条件範囲1,条件1,条件範囲2,条件2 …)+1
グループ分けをする条件
excel条件を分類でグループ分けして、その中で順位を付ける方法を紹介します。これが最もよくある条件だと思います。
昇順:
=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 から始まります。
=COUNTIFS(範囲,順序)+1
引数「範囲」と「順序」だけ指定すれば「RANK 関数」と同じ動きになります。
SMALL 関数と LARGE 関数に条件を付ける その 1
「SMALL 関数」や「LARGE 関数」に条件を付けるには、上記の「RANK 関数に条件を付ける」方法に加えて「VLOOKUP 関数」を使用します。条件も「グループ分けをする」と同じです。
まずは「VLOOKUP 関数」で検索できるようにするために検索専用列を作成します。条件と順位を結合します。条件は分類なので次のようになります。
昇順 (SMALL):
=C3&E3
降順 (LARGE):
=C3&F3
後は「VLOOKUP 関数」で検索するだけです。第 1 引数には検索専用列と同じ値を指定します。そのため条件と順位を結合した値にします。その他の引数については関数の使い方がわかっていれば問題ないはずなので説明は省略します。
=VLOOKUP(B14&C14,$B$4:$D$11,3,TRUE)
=VLOOKUP(B14&C14,$B$4:$D$11,3,FALSE)
第 4 引数が TRUE か FALSE で同率順位と検索値が存在しないときの動作が違います。
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 で確定
バージョン 365 なら「スピル、動的配列数式の使い方」を使用して条件を付けられます。数式は上記と同じでCtrl + Shift + Enterを入力する必要はありません。
詳しくは「スピル、動的配列数式の使い方」または「配列数式、CSE数式とは」をご覧ください。複雑なので数式を理解するのが難しいです。