Excelで順位付けして並び替え・色付けする方法

エクセルのRANK関数で、数値の順位を出す方法をご紹介します。重複する順位が存在する場合にランキング方式で並び替える方法や、特定の順位に自動で色付けする方法についても解説しているので、ぜひ参考にしてみてください。

順位を抽出する方法

RANK関数は上位互換であるRANK.EQ関数と、RANK.AVG関数に置き換えられました。RANK関数は新しいバージョンで利用できなくなる可能性があるため、新しい関数の使用が推奨されています。

RANK関数は指定した数値を、範囲内で比較したときの順位で返します。順序には、並べ替え方を降順(0)または昇順(1)で指定します。

RANK関数の構成要素：(数値, 範囲, [順序])

RANK.AVG関数は複数の値が同じ順位を持つ場合に、その値の平均順位を返します。

例えば 1 位が2つ存在するときにRANK.EQ関数は値の上位ランクに当たる 1 を返す一方、RANK.AVG関数は値の平均順位 1.5 を返します。

RANK関数とRANK.EQ関数は、同じものと考えて問題ありません。

RANK.AVG関数の構成要素：(値,データ範囲,[順序])

Excelのバージョンが Microsoft 365 または Excel 2021 以降なら、SORT関数で簡単に並び替えが可能です。Excel 2019 以前の場合は、VLOOKUP関数を組み合わせて並び替えします。

SORT関数は指定した範囲を、並べ替える列を基準として昇順または降順に並べ替えます。昇順(小さい順)なら 1 を、降順(大きい順)なら -1 を指定してください。

例えば SORT(A2:A6,2,1) なら 2 列目(価格)を基準として、範囲を降順に並び替えます。

SORT関数の構成要素：(範囲, [並べ替える列], [順序], [基準方向])

SORT関数を使った並び替えの場合は、重複する値があっても問題なく並び替え可能です。

VLOOKUP関数は範囲の１列目を検索し、検索値と一致する最初の行を指定した列から返します。

まずRANK関数でそれぞれの数値の順位を取得し、任意の列に順位番号を作成してください。

例えば VLOOKUP($E2,$A$2:$C$6,2,FALSE) なら範囲の1列目(RANK列)から検索値 1 を検索し、一致する行を指定した2列目(商品名)から返します。

VLOOKUP関数の構成要素：(検索値, 範囲, 列番号, [検索の型])

検索値は $E2 のように列記号を固定し、範囲は列記号・行番号ともに固定しておきます。

これにより入力したVLOOKUP関数を隣の行にコピーして、列番号を変更するだけでランキング順の価格の抽出も可能です。

まずCOUNTIF($B$2:B2,B2)-1 のように順位が出現する回数を取得し、1 を減算してください。

COUNTIF関数は、範囲内から検索条件に一致する要素の個数を返します。

ポイントは $B$2:B2 のように、範囲の開始位置のみ固定することです。これにより数式をコピーしたときに、終点位置のみ１行ずつ下に拡張されます。

COUNTIF関数の構成要素：(範囲, 検索条件)

次にRANK関数で取得した順位に、COUNTIF関数で取得した出現回数を加算します。

これにより順位に重複がない場合は順位+0 でそのままの順位を、同順位が2回目に出現した場合は順位+1 で上位ランクに 1 を加算した順位を返します。

同順位が3回目に出現した場合は順位 +2 で、上位ランクに 2 を加算した順位を返します。

重複しない順位付けができたら、後は通常通りVLOOKUP関数で順位を抽出するだけです。

VLOOKUP関数が検索するのは範囲の1列目なので、一意の順位は左の列に作成しておきましょう。

まず、色付けする範囲全体を選択します。

次にホームタブの[条件付き書式]から、[新しいルール]を開いてください。

ルールの種類から、[数式を使用して、書式設定するセルを決定]を選びます。

次を満たす場合に入力する数式は =RANK($B2,$B$2:$B$6,0)=1 です。数値にはトリガー列の先頭のセルを指定し、$記号を付けて列のみ固定してください。

この場合 RANK関数の返り値が 1 のとき、つまり降順から数えて1位の価格に色が付きます。

上位3つのセルに色付けするなら、=RANK($B2,$B$2:$B$6,0)<4 になります。

この場合は未満を表す比較演算子なので、4より小さい順位 = 上位 3つの価格に色が付きます。

比較演算子：以下(<=)、より小さい(<)、以上(>=)、より大きい(>)、等しくない(<>)