【Excelのメモ】RANKとMATCH

Excelにはフィルタを使った「並べ替え」という機能がありますが、データのシミュレーションをしながらリアルタイムに順位を確認したい場合や、元データの表が1行単位でないため「並び替え」が使えない場合もありますので、関数を使って並び替えを実現する方法としてメモを残しておきます。

●RANK関数

RANK関数を使うと表の数値に順位を付けることが出来ます。
例えば、成績表だと点数の順に１から番号を付けるので、並び替えに利用できます。

[D2] =RANK(C2,$C$2:$C$11)

順位を見ると、同順位があります。点数が同じなら順位も同じという訳です。
そこで、少し手を加えて同順位の場合、名簿の上にある方を優先順位とします。

[D2] =RANK(C2,$C$2:$C$11)+A2/100
[E2] =RANK(D2,$D$2:$D$11,1)

D列を仮順位に変更します。元の順位に番号の値を小数点以下に加えています。同順位がある場合、小数点以下で順位に差が出ます。
E列で、仮順位を元に再度RANKを使って順位を付けています。この時、仮順位の逆順（小さい順）に順位を付けます。

●VLOOKUP関数

順位が出たら、順位順の名簿を作りたいと思います。
右に新しく順位表を作成し、左の表の順位から同じ順位のデータを取りたいのでVLOOKUPを使ってみます。

[H2] =VLOOKUP($G2,$A$2:$D$11,3)

VLOOKUPを使うため、左の表はE列をA列に移動してあります。
右の順位表ですが、大きな問題が発生しています。1位〜3位がエラー表示、4位と5位は同じデータが表示されています。
VLOOKUPでは、上から順に数値を調べて行きますが、検索値を超えると手前の行に戻って値を取得します。そのため、検索範囲の一番左の列の数値が順番に並んでいないと期待の動作にならないのです。

[H2] =VLOOKUP($G2,$A$2:$D$11,3,FALSE)

VLOOKUPの引数の最後にFALSEを追加します。これによって検索条件が近似値から完全一致に変わり、#N/Aが正しく表示されます。

●MATCH関数

上記の並べ替えでは、VLOOKUPを使うために元の表の順番を変更していましたが、元の順序にE列を戻して、MATCHを組み込んでみます。

[H2] =VLOOKUP(MATCH($G2,$E$2:$E$11,0),$A$2:$C$11,2)
[I2] =VLOOKUP(MATCH($G2,$E$2:$E$11,0),$A$2:$C$11,3)

MATCHを使って左の表の順位から番号を調べています。このとき、3番目の引数「0」が重要で、完全一致の検索を行います。これを省略するとVLOOKUPと同じ問題が発生します。
MATCHを使って番号が分かったら、それを元にVLOOKUPで番号から点数までの範囲（A2:C11）を使います。

●INDEX関数

上記のMACTH関数をINDEX関数と組み合わせてみます。

[H2] =INDEX($B$2:$B$11,MATCH(G2,$E$2:$E$11,0))
[I2] =INDEX($C$2:$C$11,MATCH(G2,$E$2:$E$11,0))

H2では、MATCHで順位が範囲の何行目にあるかを調べて、INDEXでは名前の範囲から同じ行にある名前を表示しています。