エクセルでの特定範囲の順合計

質問

学校内のマラソン大会を開催するに当たり、チームごとに順位を得点化してチーム順位を出そうとしています。

走った生徒は、80人参加の場合は1位80点、2位79点・・・とつけていきます。体調不良やけがで走れない生徒の得点の付け方で、70人走り、5人が歩いた場合、この5人に10点→6点の合計（10＋9＋8＋7＋6=40）を均等割りして分配します。この処理を、「徒歩」と入力されている数を数え、自動で処理したいのですが、1つ減しながら加算していくことがどうしてもうまくできません。

手動で計算して、得点を出せばいいことなのですが、同時に複数の箇所で処理をするため、ミスが起こりそうなので自動化したいのです。

どなたか、いい方法がありましたら教えてください。

お願いします。

回答No.1

hallo-2007 · ベストアンサー

A　　B　　　C
順位　名前
1
2　
・・・
徒歩
徒歩
・・・
休み
休み
・・・　合計80名
作業列使用しますすが　C列に　=COUNTA(A:A)-ROW(A1) 80名分下までコピィ
D1セルに　=SUMIF(A:A,"徒歩",C:C)　で徒歩の人に渡せる点数の合計
D2セルに　=COUNTIF(A:A,"徒歩")　で徒歩の人数
E列に　=IF(A1="徒歩",D$1/D$2,IF(A1="休み",0,C1))　入れて下までコピィします。

回答No.4

mt2008

No.2です。
やっぱり勘違いしていました。先ほどの回答は無視してください。

参加者名簿に男女の情報が無いと男女別の得点は出せませんので、性別情報があるはずですよね。
参加者名簿シートのA列が名前、B列が性別（M/F）、C列が参加確認とします。

名前　性別　参加確認
太郎　M　　　○
次郎　M　　　徒歩
花子　F　　　○
愛子　F　　　見学
：

以下は最終的な表を表示するシートの作業になります。
空きセルに徒歩と見学の点数を入れて置きましょう。
H1に　=(COUNTIF(参加者名簿!C:C,"徒歩")+2*COUNTIF(参加者名簿!C:C,"見学")+1)/2　これが徒歩の一人当たり点数
H2に　=(COUNTIF(参加者名簿!C:C,"見学")+1)/2　これが見学の一人当たり点数

次に男女別の徒歩/見学者数を求めます、
B2に　=SUMPRODUCT((参加者名簿!B2:B81="M")*(参加者名簿!C2:C81="徒歩"))
B3に　=SUMPRODUCT((参加者名簿!B2:B81="M")*(参加者名簿!C2:C81="見学"))
D2に　=SUMPRODUCT((参加者名簿!B2:B81="F")*(参加者名簿!C2:C81="徒歩"))
D3に　=SUMPRODUCT((参加者名簿!B2:B81="F")*(参加者名簿!C2:C81="見学"))

最後に、
C2に　=B2*H1
C3に　=B3*H2
E2に　=D2*H1
E3に　=D3*H2

これでどうでしょう？

お礼

こんにちは。

SUMPRODUCT!!私がやりたかったのは、まさにこれです！

ありがとうございました。目からうろことはこのことですね。

説明が下手でうまく伝わらないのに、何度も知恵を与えてくれてありがとうございました。

本当に感謝です！

回答No.3

grumpy_the_dwarf

小学校の算数で1から10までの合計とかヤリますね。その解き方を思
い出して下さい。一番大きい数と一番小さい数の合計、二番目に大
きい数とに番目に小さい数の合計、ってペアを作るヤツですよ。

一般化すると、xからyまで整数の合計は、(x+y)*(y-x+1)/2です。歩
いた者がx人で行方不明の者がy人なら、y+1からx+yまでの合計です
から、(x+2y+1)*y/2点。一人当たりはその1/yですから、(x+2y+1)/2
点です。

問題はExcelの使い方じゃなくて、算数の知識だってオチでした。

回答No.2

mt2008

どんな表があって、どうしたいのか具体的な例がありませんので、想像するしかありませんが、例えば、A列に参加者名、B列に順位と言う表があって、順位の欄には歩いた場合は「徒歩」、欠席者は空白が入っている物とします。

あああ　1
いいい　徒歩
ううう　
えええ　10
　：　　：

順位が出ている人の点数は　参加人数-順位+1　(例：1位　80-1+1＝80点）

「徒歩」の人の点数は　（参加人数-走った人数）*（歩いた人数-1）/歩いた人数 で、計算します。
例:（80-70）*（5-1）/5　＝8

C1に↓を入れて下までコピーしてください。
=IF(ISNUMBER(B1),COUNTA(A:A)+1-B1,IF(B1="徒歩",(COUNTA(A:A)-COUNT(B:B))*(1-1/COUNTIF(B:B,"徒歩")),""))

COUNTA(A:A)　が参加者名の数＝参加人数＝80
COUNT(B:B)　が順位が数字の人数＝走った人数=70
COUNTIF(B:B,"徒歩")　が順位が「徒歩」の人数＝5

に、なります。

具体例がないとまったく外した説明をしているかも知れず不安です。
できるだけ具体例は付けてください。

補足

ありがとうございます。わかりにくくて申し訳ありません。

まず、得点はこのようにつけます。
　順位　　　得点
　　1　　　　80
　　2　　　　79
　　　　・
　　　　・
　 70　　　　11
　徒歩
　徒歩
　徒歩　　→（10＋9＋8＋7＋6）/5=8点
　徒歩
　徒歩
　見学
　見学
　見学　　→（5＋4＋3＋2＋1）/5=3点
　見学
　見学　
となるのですが、この徒歩と見学のところを当日の状況によって自動計算したいのです。　

参加者名簿シートに
　名前　　参加確認
　太郎　　　　○
　次郎　　　　○
　三郎　　　徒歩
　四郎　　　　○
　五郎　　　徒歩
といった感じです。この中から
　　　Ａ　　　　Ｂ　　　　Ｃ　　　　Ｄ　　　　Ｅ　　　　
１　　　　　　　男子　　　得点　　　女子　　　得点
２　　徒歩　　　○人　　　○点　　　○人　　　○点
３　　見学　　　○人　　　○点　　　○人　　　○点
といった表をつくり、この表から参照して得点が入るようにしたいのです。
Ｃ2、Ｃ３、Ｅ２、Ｅ３に入る数式をあれこれとやっているのですが、うまくいかないので…お分かりいただけたでしょうか？