excelで受賞者を決める数式

質問

こんにちは、よろしければご教授願います。

下記のような表があるとします。
各点数が高い順に、受賞者を決定する数式を組みたいです。

条件は下記です。

●点数が高い人が受賞
●受賞の順番は国語＞数学＞英語
●全く同じ点数の人がいたら、Noの若い順に上から受賞者を決める
●受賞者の上限が変わる事もある

例えば、図１では、一郎さんは国語・数学・英語全てで一位の成績ですが
既に国語を受賞しているので、数学と英語は次点の人が受賞する事になります。

また、図１の次郎さんは数学と英語で一郎さんの次点ですが、
数学賞を受賞しているので、英語は次点の人が受賞する事になります。

図２では一郎さん、三郎さん、四朗さんが１位で、３人同じ点数ですが
受賞者上限が各１人ずつのため、Noの若い順に受賞します。

図３は、国語賞の受賞者上限が３人になったため、
一郎さん、三郎さん、四朗さんが３人とも国語賞を受賞し、
数学・英語は次点の人が受賞することになります。

データの関係上、一番左のNoでのソートで固定されており、ソートはし直せません。

どのような数式を組めばいいか分からないので、よろしければご教授願います。
バージョンはWindows7、Excel2010です。

回答No.3

tom04

No.2です！
たびたびごめんなさい。
前回の数式に不備がありました。
訂正させてください。

セル配置は前回と同じだとします。（データは10人という数式です。）

作業列H2セルは
=COUNTIF(C$2:C$11,">"&C2)+COUNTIF(C$2:C2,C2)

I2セルは
=IF(H2<=$L$3,1000,COUNTIF($D$2:$D$11,">"&D2)+COUNTIF($D$2:D2,D2))

J2セルは
=IF(OR(I2=1000,I2<SMALL($I$2:$I$11,$M$3+1)),1000,COUNTIF($E$2:$E$11,">"&E2)+COUNTIF($E$2:E2,E2))

としてオートフィルでデータ最終行までコピー！

結果のF2セルに
=IF(H2<=$L$3,$C$1&"賞",IF(RANK(I2,$I$2:$I$11,1)<=$M$3,$D$1&"賞",IF(RANK(J2,$J$2:$J$11,1)<=$N$3,$E$1&"賞","")))

としてオートフィルで下へコピーしてみてください。

これで何とか希望に近い形にならないでしょうか？
何度も失礼しました。m(_ _)m

お礼

二度も書き込んで頂きありがとうございました！
表は無事に作成することが出来ました

回答No.2

tom04

こんにちは！
外しているかもしれませんが・・・

↓の画像のように作業用の列を3列・上限人数を入力する表を別途作成します。

作業列H2セルに
=COUNTIF(C$2:C$11,">"&C2)+COUNTIF(C$2:C2,C2)

I2セルに
=IF(H2<=$L$3,1000,COUNTIF($D$2:$D$11,">"&D2)+COUNTIF($D$2:D2,D2)-$L$3)

J2セルに
=IF(OR(I2=1000,I2<=$M$3),1000,COUNTIF($E$2:$E$11,">"&E2)+COUNTIF($E$2:E2,E2)-$M$3)

という数式を入れH2～J2セルを範囲指定　→　J2セルのフィルハンドルで下へコピー！

最後にF2セルに
=IF(H2<=$L$3,$C$1&"賞",IF(I2<=$M$3,$D$1&"賞",IF(RANK(J2,$J$2:$J$11,1)<=$N$3,$E$1&"賞","")))

という数式を入れオートフィルで下へコピーすると
画像のような感じになります。
これで上限人数を変えるだけでその数だけ「賞」が表示されると思います。

※　余計なお世話かもしれませんが、作業列の数式
＞=COUNTIF(C$2:C$11,">"&C2)+COUNTIF(C$2:C2,C2)

は同ランクの場合は上位行の人がランク上位になるようにしています。

※　表示人数（上限人数）が複数の場合、その科目のランク順としています。
（総合得点のランク順ではありません）

参考になれば良いのですが、
最初に書いたように的外れの場合はごめんなさいね。m(_ _)m

回答No.1

mt2008

G：L列を作業列として使います。

・C1:E1に各教科の受賞者上限数を入力します（例：3,1,1）
・F3に　=""&J3&K3&L3
　G3に　=C3-A3/10000
　H3に　=(D3-A3/10000)*(RANK(G3,$G$3:$G$8)>$C$1)
　I3に　=(E3-A3/10000)*(RANK(H3,$H$3:$H$8)>$D$1)*(H3>0)
　J3に　=IF(AND(C3>0,RANK(G3,G$3:G$8)<=$C$1),C$2&"賞","")
　K3に　=IF(AND(J3="",RANK(H3,H$3:H$8)<=$D$1),D$2&"賞","")
　L3に　=IF(AND(J3="",K3="",RANK(I3,I$3:I$8)<=$E$1),E$2&"賞","")
　と、入れます。
・F3:L3を下にコピー（８行目まで）

G:I列は、同点の場合の順位と受賞済みを考慮した得点です。

式中の10000は同点の場合の順位を決めるための「大きな数」です。
対象者数によってはもっと大きな数にしてください。

お礼

ありがとうございます！無事に表を作成することが出来ました！