エクセルでの順位に同率がある場合の対処について

質問

■エクセル上で数字に順位を付けて上位(下位)3位までの数字に色を付けたい。

■現在の方法
書式→条件付書式

条件1
セルの値が次の値に等しいを選び、
関数「=LARGE($C$6:$C$100,1)」と入力し、書式で文字の色を指定。赤色

条件2
セルの値が次の値に等しいを選び、
関数「=LARGE($C$6:$C$100,2)」と入力し、書式で文字の色を指定。青色

条件3
セルの値が次の値に等しいを選び、
関数「=LARGE($C$6:$C$100,3)」と入力し、書式で文字の色を指定。緑色

■問題点
1番大きい数字が2つある場合、2箇所が赤色になり、2番目に大きい数字が緑色になる。青色は使われない。
(順位で言うと同率1位が二つ、2位が飛ばされて3位が一つと処理される(1位・1位・3位))

■やりたい事
1番大きい数字が2つある場合2箇所とも赤色にし、2番目に大きい数字を青色、3番目に大きい数字を緑色にしたい。
(順位で言うと1位・1位・2位・3位と言った具合にしたい)
もし2番目に大きい数字が3つあってもその3箇所を青色にして、3番目に大きい数字を緑色にする。
(順位で言うと1位・1位・2位・2位・2位・3位の様な感じ)

具体的に言うと

現在
10
20
30→緑色
40→赤色
40→赤色

これを
10
20→緑色
30→青色
40→赤色
40→赤色

にしたいのです。

■補足
表の範囲はC6～C100としていますが、実際はC30までしか使っていなく、それ以降は日々データを記入するので拡張用としている。
使用しているエクセルはエクセル2002です。

よろしくお願いします。

回答No.4

keithin · ベストアンサー

ありゃりゃ。とんだ手抜かりを。
んでは簡単じゃない数式を使った方法で。

それぞれ「次の値に等しい」で
１位：
=LARGE(IF(FREQUENCY($C$6:$C$100+0,$C$6:$C$100+0),$C$6:$C$100),1)

２位：
=LARGE(IF(FREQUENCY($C$6:$C$100+0,$C$6:$C$100+0),$C$6:$C$100),2)

３位：
=LARGE(IF(FREQUENCY($C$6:$C$100+0,$C$6:$C$100+0),$C$6:$C$100),3)

失礼しました。

お礼

別の方法での回答ありがとうございます。

この方法を使うと出来ましたが、ちょっと参照量が多くなると動作が重くなってしまいました。

FREQUENCY関数と言うのは初めて見たので、理解は出来ていませんが方法の一つとして参考になりました。

No.3のkagakusuki様の補足もあり、最初に回答された方法と併せて解決しました。
ありがとうございました。

回答No.5

noname#204879

添付図参照
１．セル B1 に式 =IF(COUNTIF(A$1:A1,A1)>1,"",A1) を入力して、
　　此れを下方にズズーッとドラッグ＆ペースト
２．セル A1 に次の[条件付き書式]を設定して、範囲 A2:A5 に
　　[書式の貼り付け]を実行
　　　条件1
　　　　　数式が　　　=RANK(A1,B$1:B$5)=1
　　　　　フォント色　赤
　　　条件2
　　　　　数式が　　　=RANK(A1,B$1:B$5)=2
　　　　　フォント色　青
　　　条件3
　　　　　数式が　　　=RANK(A1,B$1:B$5)=3
　　　　　フォント色　緑

お礼

回答ありがとうございます。

出てきた数字を調べてBに書き出す(同じ数字は2回目以降は空白)
Aセルの値をBに書き出した数字でランク降順にして、それぞれ1位・2位・3位となった場合にそれぞれ色を付ける。

と言った風に解釈しました。

色んなやり方で出来るのですね。どれも思いつかない方法だったので助かりました。

回答No.3

kagakusuki

ANo.1様の方法ですと、一位の数字と二位の数字の方法に関しましては、大変優れた方法だと思います。
　但し、三位の数字の方法に関しましては、一位の数字が複数存在している場合には、誤った動作をしてしまいます。
　ですから、三位の数字の場合は

=LARGE($C$6:$C$100,COUNTIF($C$6:$C$100,">="&LARGE($C$6:$C$100,2))+1)

ではなく、

=LARGE($C$6:$C$100,COUNTIF($C$6:$C$100,">="&LARGE($C$6:$C$100,COUNTIF($C$6:$C$100,MAX($C$6:$C$100))+1))+1)

等とする必要があります。

お礼

回答ありがとうございます。

この方法を使う事で解決しました。

3位の方法は自分なりに解釈しようとしたのですが、特に大なりイコールとアンドで結ばれている所が分りませんでした。
解釈は出来ていませんが解決は出来ました。ありがとうございます。

ベストアンサーに非常に悩んだのですが、ベストアンサーが1つしか選べないので申し訳ないです。

回答No.2

Cupper-2

自分は計算の誤差にならない微少な数字を各値に加えちゃいます。

　10　→　10 + 0.000001
　20　→　20 + 0.000002
　30　→　30 + 0.000003
　40　→　40 + 0.000004
　40　→　40 + 0.000005

面倒なのでROW関数を使って

　10　→　10 + ROW()/1000000
　20　→　20 + ROW()/1000000
　30　→　30 + ROW()/1000000
　40　→　40 + ROW()/1000000
　40　→　40 + ROW()/1000000

と、しちゃうかな。
でもって、微少数値を加えた後でLARGE関数を使うとかします。

お礼

回答ありがとうございます。

今回は同率1位を両方とも赤色にした上で次の順位を青色と言った具合にしたかったので、
質問の仕方が悪かったのかも知れませんが、この方法は同率1位を回避する事に使えそうです。

一つの参考になりました。

回答No.1

keithin

簡単な関数だけで結果を得るなら。

一位の数字は今のままでもいいですが
=MAX($C$6:$C$100)
でいいです。

二位の数字は
=LARGE($C$6:$C$100,COUNTIF($C$6:$C$100,MAX($C$6:$C$100))+1)
三位の数字は
=LARGE($C$6:$C$100,COUNTIF($C$6:$C$100,">="&LARGE($C$6:$C$100,2))+1)
などのように拾ってくることができます。

それぞれ「次の値に等しい」に与えます。

お礼

素早い回答ありがとうございます。

2位の方法はMAX関数で1位の数を調べ、それに+1する事で次が何位になるのか分かるので、
それをLARGE関数で抜き出すと言う風に解釈しました。

同率1位がいくつあろうと、その次の順位を抜き出すのはややこしい手順が必要なんですね。