複雑な数式？教えてください。。。

質問

教えてください。。。
　　　ゲーム1　ゲーム2　ゲーム3　ゲーム4　ゲーム5
鈴木　　104　　　125　　　136　　　120　　　100
岩崎　　111　　　140　　　160　　　130　　　114　
佐藤　　155　　　150　　　180　　　132　　　140
井上　　132　　　112　　　151　　　117　　　109
新井　　106　　　133　　　161　　　122　　　108

ポイント表
1位　5
2位　3
3位　1
4位　0
上記の表で各ゲームごとに1位なら5ポイント、2位なら3ポイント、4位以下は0ポイントとなるようにそれぞれの方のポイントの合計を求める方法を教えてください。各ゲームごとにというところがみそな気がします、、、宜しくお願いいたします。。。

回答No.4

mirage70 · ベストアンサー

=IF(B2=LARGE($B$2;$B$6,1),5,IF(B2=LARGE($B$2;$B$6,2),3,IF($B$2=LARGE($B$2;$B$6,3),1,0)))+同一でBをCに+同一でBをDに+同一でBをEに+同一でBをFに
此をG2に書き込み、ドラッグしてコピーすれば全ての点数が出てきます。

お礼

今回、この数式を利用させていただきました。
大変助かりました。また何かありましたらよろしくお願いいたします。
ありがとうございました。。。

回答No.6

imogasi

ＶＢＡでやって見ました。
ＲＡＮＫ関数とＣＨＯＯＳＥ関数を使っています。
Sub suc()
For j = 3 To 11 Step 2
Columns(j).EntireColumn.Insert '点数列挿入
For i = 2 To 6
r = Application.WorksheetFunction.Rank(Cells(i, j - 1), Range(Cells(2, j - 1), Cells(6, j - 1)), 0) 'ＲＡＮＫ順位を得る
Cells(i, j) = Application.WorksheetFunction.Choose(r, 5, 3, 1, 0, 0) 'ランクを点数化
Next i
Next j
'----各列点数を合計
For i = 2 To 6
t = 0
For j = 3 To 11 Step 2
t = t + Cells(i, j)
Next j
Cells(i, 13) = t
Next i
End Sub
結果は
　ゲーム１ ゲーム２ ゲーム３ ゲーム４ ゲーム５
鈴木 104 0 125 0 136 0 120 0 100 0 0
岩崎 111 1 140 3 160 1 130 3 114 3 11
佐藤 155 5 150 5 180 5 132 5 140 5 25
井上 132 3 112 0 151 0 117 0 109 1 4
新井 106 0 133 1 161 3 122 1 108 0 5

お礼

ご丁寧にありがとうございました。
ただわたしには少し難しいのと今回は関数で
ということだったので。。。
勉強になりました。
ありがとうございます。

回答No.5

shishishishi

hinebotさん、補足説明ありがとうございます。
そうです。ポイント対応表ですね。

ゲーム数が増えることなどを考えたら、いったん（ゲーム毎の）「ポイント表」をつくってSUM関数で合計したほうがあとあと楽ですね。

回答No.3

hinebot

＃１です。

>=VLOOKUP(RANK(B2,B2:B6),I2:J6,2)

RANK関数で順位をだし、VLOOKUP関数でそれに応じたポイントを探しています。

#2さんが
>スコアの表とポイント表の配置をNo1さんと同じとすると
で「ポイント表」と言っているのは、#1で言った
>空いているセルを使ってポイントと順位の対応表を作ります。
この対応表のことです。

#2さんの式でも十分OKですが、式が長ったらしくなることと、ゲーム数が増えたりした場合に柔軟に対応できるよう、いったん（ゲーム毎の）「ポイント表」をつくってから、SUM関数で合計するようにしました。

お礼

ご丁寧にありがとうございました。

回答No.2

shishishishi

スコアの表とポイント表の配置をNo1さんと同じとすると

鈴木さんの合計ポイントを出したいセルに下記をコピーして入れてください。
その際、余白など式以外のものが入らない様に注意してください。

=VLOOKUP(RANK(B2,B$2:B$6),$I$2:$J$5,2,TRUE)+VLOOKUP(RANK(C2,C$2:C$6),$I$2:$J$5,2,TRUE)+VLOOKUP(RANK(D2,D$2:D$6),$I$2:$J$5,2,TRUE)+VLOOKUP(RANK(E2,E$2:E$6),$I$2:$J$5,2,TRUE)+VLOOKUP(RANK(F2,F$2:F$6),$I$2:$J$5,2,TRUE)

これを岩崎さん以下にドラッグすればそれぞれの合計ポイントが自動的に出ます。

お礼

解答ありがとうございました。
１ステップで出来るのでよかったのですが
参照する既存のポイント表の順位が一位、二位と文字列だったためうまく出来ませんでした。。。
ですがやり方は覚えました。
大変ありがとうございました。

回答No.1

hinebot

ゲーム１、ゲーム２、…のセルをB1,C1,…
鈴木、岩崎、…のセルをA2,A3,…
とします。

空いているセルを使ってポイントと順位の対応表を作ります。例えば、I列に順位(I2が1,I3が2,…）、J列にポイント（J2が5,J3が3,…）を入れておきます。

さらに空いているセル（例えばL1:Q6の範囲で、1行目とL列は項目名とします）を使ってポイント表を作ります。
作り方はセルM2に
=VLOOKUP(RANK(B2,B2:B6),I2:J6,2)
と入れます。
すると、例の場合、鈴木さんのゲーム１でのポイントが計算されます。
あとは、人数とゲーム分同様に入力します。
最後にポイント表で鈴木さんなら、
=SUM(M2:P2)
とすれば、各自のポイントが集計できます。

お礼

わかりやすく丁寧なご説明に感謝します。
今回は既存のポイント表を元に作成するので順位の
ところが一位、二位と文字列だったためうまくできませんでした。ですが大変勉強になりました。
また何かありましたら宜しくお願いいたします。。。ありがとうございました。