ExcelのRANK関数で、同順位(n位タイ)があってもその次の数字(n+1位)を飛ばさずに順位をつける方法についてです。
- XMATCH関数とUNIQUE関数等を併用して、同順位となるものが複数あっても次の順位を飛ばさないように順位をつけられます。
手順
まず「飛ばさない順位」を1つを求める例です。
次の画像では、C3:C9セルの範囲内におけるC3セルの点数(85点)の順位を求めています。90点の者が2名いるのでRANK関数で順位を求めると「3」になりますが、ここでは同順位の存在を無視して「2」になっているのがわかります。
D3セル
=XMATCH(C3,SORT(UNIQUE(C3:C9),,-1))
普通に「=RANK(C3,UNIQUE(C3:C9))」で求められれば話は早いのですが、ExcelのRANK関数では第2引数内で関数を使えないのでうまくいきません(RANK.EQもダメ)。
そこでまずUNIQUE関数とSORT関数を使い、重複を除いた点数を降順で並べています。そしてXMATCH関数でC3セルの点数が上から何番目にあるのかを求めています。これが求める順位です。
スピルを利用してXMATCH関数の第1引数を「C3:C9」とすればすべての順位が求められます。
D3セル
=XMATCH(C3:C9,SORT(UNIQUE(C3:C9),,-1))
SORT関数の第3引数を省略すれば昇順での順位付け(値が最も小さいものが1位)となります。
D3セル
=XMATCH(C3:C9,SORT(UNIQUE(C3:C9)))
文字列等を含む場合
RANK関数では文字列や空白セルを無視して順位を取得することができます(文字列や空白セル自体はエラーになる)が、上記の方法ではそれらの値にも順位がついてしまいます。
これを避けたい場合、FILTER関数を使って文字列等を除けばあとは難しくありませんが、1つの式で求める例としては次のようなものが挙げられます。
D3セル
=IF(ISNUMBER(C3:C9), XMATCH(C3:C9,SORT(UNIQUE(FILTER(C3:C9,ISNUMBER(C3:C9))),,-1)), "")
IF/ISNUMBER関数を使って数値以外には空文字列(="")を出力するとともに、FILTER関数により数値だけをXMATCH関数の評価対象としています。