[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『ボーリングの団体戦の順位』（YOU君）
３人１チームでボーリングの団体戦を行って、３人のスコア合計で順位を決めます。 sheet1の結果からsheet2のように順位を決めてまとめたいのですが、個人ならなんとか できそうですがチームになるとどうしていいかわかりません。
sheet1
A B C D E F G
1 ﾁｰﾑ 名前 1ｹﾞｰﾑ目 2ｹﾞｰﾑ目 3ｹﾞｰﾑ目 合計 ﾁｰﾑ合計
2 Ａ ＡＡ 80 90 85 255 985
3 ＢＢ 110 115 120 345
4 ＣＣ 120 130 135 385
5 Ｂ ＤＤ 120 100 110 330 1045
6 ＥＥ 135 140 130 405
7 ＦＦ 100 120 90 310
この結果からsheet2に
A B C D
1 順位 名前 ﾁｰﾑ合計 個人合計
2 １ ＤＤ 1045 330
3 ＥＥ 405
4 ＦＦ 310
5 ２ ＡＡ 985 255
6 ＢＢ 345
7 ＣＣ 385
ご教授願います。
回答がつかないので、とりあえず作業列を使った例です。 Sheet1のH2に、 =IF(G2="",G1+1,RANK(G2,G2:G7)*10)
と入力してH7までコピーするとこのようになります。
A B C D E F G H
1 ﾁｰﾑ 名前 1ｹﾞｰﾑ目 2ｹﾞｰﾑ目 3ｹﾞｰﾑ目 合計 ﾁｰﾑ合計
2 Ａ ＡＡ 80 90 85 255 985 20
3 ＢＢ 110 115 120 345 21
4 ＣＣ 120 130 135 385 22
5 Ｂ ＤＤ 120 100 110 330 1045 10
6 ＥＥ 135 140 130 405 11
7 ＦＦ 100 120 90 310 12
次にSheet2のB2に =INDEX(Sheet1!$B$2:$B$7,MATCH(INT((ROW()+1)/3)*10+MOD(ROW()+1,3),Sheet1!$H$2:$H$7,0))
と入力してB7までコピーしてください。 C列、D列は $B$2:$B$7 の部分を適当に直すか、 または、氏名が重複しないときは氏名をキーにVLOOKUPなどでも参照できますね。 なお、同順位がある場合は考慮していません。 (sato)
satoさん、ありがとうございます。（YOU君） チーム名と名前をリンクさせる方法が何かないでしょうか？ どうしても、そこがクリアできません。
ごちゃごちゃしてますが。。。
Sheet2のB2に =INDEX(OFFSET(Sheet1!B$2:B$4,MATCH(OFFSET(C$2,CEILING(ROW(Sheet1!A1),3)-3,),Sheet1!G$2:G$10,0)-1,),MOD(ROW(Sheet1!A1)-1,3)+1)
C2に =IF(OR(MOD(ROW(A1),3)={0,2}),"",LARGE(Sheet1!G$2:G$10,ROUNDUP(ROW(A1)/3,0)))
D2に =VLOOKUP(B2,Sheet1!B$2:F$10,5,0)
として、B2：D2を下にコピー。
チーム合計の同点、同名には対応してません＾＾；
(かなれっと)
かなれっとさん、希望する結果が得られました。 ありがとうございます。 初心者のため、式の意味が理解できません。
できれば、式の説明をお願いできないでしょうか？
また、同姓同名はいないので問題ありません。 チーム合計が同点の場合は、第１投者の１ゲーム目の スコアで順位を決めたいと思います。 つまり、C2とC5を比較して大きい方が上位。 これでも同点のときはC3とC6との比較･･･という具合です。 （YOU君）
同点の場合はSheet1のどこかに作業列を設け、その列の値で順位を決定するのがよいのでは？ =G2+IF(COUNTIF(G$2:G$7,G2)>1,C2*0.1+C3*0.01+C4*0.001) のような感じで。 意味としては、チーム合計にもしAチーム合計の点数と同点の数値がG2からG7の範囲に1より多くあれば(要は2個以上)、 C2の値の1/10とC3の値の1/100とC4の値の1/1000を足しましょう、です。
上で提案した式について、ですが。 まず範囲がコチラで試した範囲になっていたので例と若干違っています＾＾； すみません。 以下に意図したことを大雑把ですが書いてみますね〜
Sheet2のB2は、INDEX(範囲,行数) がモトとなる関数です。範囲の指定した行数にある値を返してね、という意図です。 で、この範囲を出すのに、OFFSET関数を使いました。範囲の基準をSheet1のB2からB4とし、 すなわちＡチームのＡＡさんからＣＣさんを一組として、指定した行数だけ範囲を移動させます。 このとき指定する行数は、MATCH関数により、C列で求めたチーム合計値と一致する値をSheet1のG2からG10で探して、 その位置番号より1少ない数値とします。（OFFSET関数では行数0が基準位置の行数になるためです。） MATCH関数についても、3行ごとに求められるC列のチーム合計値を検索値にするため、OFFSET関数を使っています。 ここでは、指定する行数を下に数式をコピーしていくごとに0,0,0,3,3,3,6,6,6…となるよう ROW関数をCEILING関数で丸めて、最初の数値を0にするために-3で調整しています。 また、INDEX関数で指定する行数は、MOD関数で出しました。下に数式をコピーしていくごとに1,2,3,1,2,3となるよう ROW関数を使い、-1や+1で調整しています。
C2は、IF関数で場合分けをしてます。3行ごとにLARGE関数を計算させるようにするため、MOD関数で判定させました。 大事なのがLARGE関数です。Sheet1のG2からG10で指定した順位番目に大きな数を返します。 この順位番目を３行下がるごとに1,2,3…となるよう、ROUNDUP関数でROW関数を3で割った数を切り上げしています。
D2はVLOOKUP関数そのままです。
以上を踏まえて、同点を考慮し、範囲を訂正して、ROW関数の参照セルの不要な参照をはずして…
Sheet1のH列を上記のようにして作業列にします。
Sheet2のB2を =INDEX(OFFSET(Sheet1!B$2:B$4,MATCH(OFFSET(C$2,CEILING(ROW(A1),3)-3,),Sheet1!H$2:H$7,0)-1,),MOD(ROW(A1)-1,3)+1)
C2を =IF(OR(MOD(ROW(A1),3)={0,2}),"",LARGE(Sheet1!H$2:H$7,ROUNDUP(ROW(A1)/3,0)))
D2を =VLOOKUP(Sheet1!B2,Sheet1!B$2:F$7,5,0)
として、C列のセルの書式設定、表示形式を数値、小数点以下の桁数を0とする。。。
ではいかがでしょうね〜？
(かなれっと)☆それぞれの関数については、エクセルのヘルプをご覧くださいね。
また、関数を切り出して、フィルコピーするとどうなるか…など試してみられると、
より理解しやすいと思いますよ〜
かなれっとさん、大変ありがとうございました。 お陰で、アラビア語（？）のようにチンプンカンプンだった関数が 英語レベル（辞書があればなんとか）にはなったと思います。 これからも、落ちこぼれないように頑張ります。（YOU君）
sheet1H2に 作業列 =IF(G2="",H1+1,SUM((sheet1!$G$2:$G$11+ROW($A$1:$A$10)/100>G2+ROW(A1)/100)*3)+1) shift+ctrl+enterで確定 下へフィールコピー
sheet2A2に =IF(MOD(ROW(A1),3)=1,RANK(LARGE(sheet1!$G$2:$G$11,ROW(A3)/3),sheet1!$G$2:$G$11),"") 下へフィールコピー sheet2B2に =INDEX(sheet1!B$2:B$11,MATCH(ROW(A1),sheet1!$H$2:$H$11,0)) 下へフィールコピー sheet2C2に sheet2D2に sheet1!B$2:B$11の範囲変更で他の列も （JUN)
JUNさん、 H2の作業列はsheet1のH2のことですよね？（YOU君）
1部間違いを書き換えました （JUN) sheet1H2に 作業列 =IF(G2="",H1+1,SUM((
JUNさん sheet1H2に 作業列 =IF(G2="",H1+1,SUM((sheet1!$G$2:$G$11+ROW($A$1:$A$10)/100>G2+ROW(A1)/100)*3)+1) の「shift+ctrl+enterで確定」がどうしてもできません。何故でしょうか？ そのまま下へフィールコピーしたら、こうなりました。 sheet1!B$2:B$11 当ののセル範囲はsheet1!B$2:B$16 等に変更しています。（YOU君)
sheet1 1 2 3 小計 合計 Ａ ＡＡ 80 90 85 255 985 1 ＢＢ 110 115 120 345 2 ＣＣ 120 130 135 385 3 Ｂ ＤＤ 120 100 110 330 1045 1 ＥＥ 135 140 130 405 2 ＦＦ 100 120 90 310 3 Ｃ ＧＧ 120 100 130 350 1025 1 ＨＨ 85 85 120 290 2 ＩＩ 120 130 135 385 3 Ｄ ＪＪ 100 120 115 335 1020 1 ＫＫ 120 100 130 350 2 ＬＬ 115 120 100 335 3 Ｅ ＭＭ 130 135 120 385 1110 1 ＮＮ 140 130 115 385 2 ＯＯ 120 90 130 340 3
sheet2 順位 名前 ﾁｰﾑ合計 個人合計 1 ＡＡ 80 90 ＢＢ 110 115 ＣＣ 120 130 2 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 3 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
=IF(G2="",H1+1,SUM((sheet1!$G$2:$G$11+ROW($A$1:$A$10)/100>G2+ROW(A1)/100)*3)+1)│ ↑ カーソル カーソルがある状態でshift+ctrl+enterの同時押し ｛=IF(G2="",H1+1,SUM((sheet1!$G$2:$G$11+ROW($A$1:$A$10)/100>G2+ROW(A1)/100)*3)+1)｝ 数式が｛｝で囲まれた状態で下へフィールコピー （JUN)
同点の場合下から上位に =IF(G2="",H1+1,SUM((sheet1!$G$2:$G$11+ROW($A$1:$A$10)/100>G2+ROW(A1)/100)*3)+1) 同点の場合 C2,C5,C8・・・の値が大きい順に =IF(G2="",I1+1,SUM((Sheet1!$G$2:$G$16+($C$2:$C$16)/1000>G2+C2/1000)*3)+1) （JUN)
JUNさん、ありがとうございました。今度は完璧にできました。（YOU君）
参加人数が３の倍数とは限らないので、３人構成のチーム（３人組）と ４人構成のチーム（４人組）で実施することになりました。 つまり、 参加人数46人＝３人組×14組＋４人組×1組、47人＝３人組×13組＋４人組×2組 といった具合で、４人組みは参加者No.の最後から構成していきます。 順位決定には４人組のチーム合計に0.75（=3/4）を掛けることにします。 既に、私のエクセル能力を超えてしまいましたので、皆様のお知恵をお貸し下さい。 （YOU君)
sheet1（データベース）
[A] [B] [C] [D] [E] [F] [G]
[1] No. 名前 1ｹﾞｰﾑ目 2ｹﾞｰﾑ目 3ｹﾞｰﾑ目 個人合計 ﾁｰﾑ合計
[2] 1 ＡＡ 80 90 85 255 985
[3] 2 ＢＢ 110 115 120 345
[4] 3 ＣＣ 120 130 135 385
[5] 4 ＤＤ 120 100 110 330 1045
[6] 5 ＥＥ 135 140 130 405
[7] 6 ＦＦ 100 120 90 310
・・ ・・ ・・・ ・・・ ・・・ ・・・ ・・・ ・・・
[44] 43 ＪＪ 100 120 115 335 1405
[45] 44 ＫＫ 120 100 130 350
[46] 45 ＬＬ 115 120 100 335
[47] 46 ＭＭ 130 135 120 385
sheet2（順位結果まとめ） [A] [B] [C] [D] [1] 順位 名前 ﾁｰﾑ合計 個人合計 [2] 1 ＪＪ 1054 238 [3] ＫＫ 233 [4] ＬＬ 224 [5] ＭＭ 260 [6] 2 ＤＤ 1045 330 [7] ＥＥ 405 [8] ＦＦ 310 ・・ ・・・ ・・・ ・・・ ・・・ [24] 8 ＡＡ 958 255 [25] ＢＢ 345 [26] ＣＣ 385
尚、1054(sheet2)=1405(sheet1)×0.75の関係です。
[A] [B] [C] [D] [E] [F] [G]
[1] No. 名前 1ｹﾞｰﾑ目 2ｹﾞｰﾑ目 3ｹﾞｰﾑ目 個人合計 ﾁｰﾑ合計
[2] 1 ＡＡ 80 90 85 255 985
[3] 2 ＢＢ 110 115 120 345
[4] 3 ＣＣ 120 130 135 385
[5]
[6] 4 ＤＤ 120 100 110 330 1045
[7] 5 ＥＥ 135 140 130 405
[8] 6 ＦＦ 100 120 90 310
=IF(G2="",H1+1,SUM((sheet1!$G$2:$G$11+ROW($A$1:$A$10)/100>G2+ROW(A1)/100)*3)+1)
^^^
↑
*3 を *4に
４行で１チームで ３人の場合1行は空き行して sheet1（データベース）を作成
４行で１チームで ３人の場合1行は空き行になりますが、 sheet2（順位結果まとめ）
[A] [B] [C] [D] [1] 順位 名前 ﾁｰﾑ合計 個人合計 [2] 1 ＪＪ 1054 238 ４人１チーム [3] ＫＫ 233 [4] ＬＬ 224 [5] ＭＭ 260 [6] 2 ＤＤ 1045 330 3人１チーム [7] ＥＥ 405 [8] ＦＦ 310 [9] 空き行 （JUN)
３人組×10組＋４人組×5組といった 組み合わせ（３人組・・・４人組・・）も 対応できますよ（JUN)
JUNさん、ありがとうございます。（YOU君) 以下 2/2 7:00 訂正
参加人数をｎ（=30〜60）とした場合、sheet1のH列に入る式のセル範囲を変数nで表わすと
どんな式になりますか？
=IF(G2="",H1+1,SUM((sheet1!$G$2:$G$11+ROW($A$1:$A$10)/100>G2+ROW(A1)/100)*3)+1)
^^ ^^
↑ ↑
nはsheet1の名前の有無によりcountif関数で求める予定です。A列はB列のセルが空欄の
場合でも60までシリアルNo.が入っているので。
参加人数とチーム構成は下表のようになりますが、sheet1の空き行にすべき行番号を nで表すとどんな式になりますか？
参加人数 3人ﾁｰﾑ 4人ﾁｰﾑ
30 10 0
31 9 1
32 8 2
・・・ ・・・ ・・・
58 18 1
59 17 2
60 20 0
過去ログで検索した式をそのまま使ってみましたが、 IF(G2="",H1+1,SUM((sheet1!$G$2:$G$11+ROW($A$1:$A$10)/100>G2+ROW(A1)/100)*3) +1)が#VALUE!になってしまいます。 入力する数字がボーリングと違って3桁ではなく2桁（0〜10）と小さいためと思いますが、 式の修正がわからないので、教えて下さい。（Mr.ﾀﾞｰﾂ）
JUNさん
先週からずっと考えているのですが、 1054(sheet2)=1405(sheet1)×0.75にするための補正はどこに 入っていますか？ ＪＪ、ＫＫ，ＭＭ、ＮＮの４人の個人合計は 238+233+224+260=955となって1054にはなりませんが？
[A] [B] [C] [D] [1] 順位 名前 ﾁｰﾑ合計 個人合計 [2] 1 ＪＪ 1054 238 ４人１チーム [3] ＫＫ 233 [4] ＬＬ 224 [5] ＭＭ 260 [6] 2 ＤＤ 1045 330 3人１チーム [7] ＥＥ 405 [8] ＦＦ 310 [9] 空き行 （YOU君)
sheet1（データベース）
[A] [B] [C] [D] [E] [F] [G] H
[1] No. 名前 1ｹﾞｰﾑ目 2ｹﾞｰﾑ目 3ｹﾞｰﾑ目 個人合計 ﾁｰﾑ合計 修整合計
[2] 1 ＡＡ 80 90 85 255 985
修整合計列設け1列ずらせば （参照範囲もずらしましょう。） （JUN） Mr.ﾀﾞｰﾂサンわけのわからない割り込みは止めましょう。
JUNさん 返事が遅くなりました。 修整合計列と参照範囲の変更で対応できました。 ありがとうございました。
只、どうしても下のキーになる数式の意味がわかりません。 説明して頂けないでしょうか？（特に、･･･>G2･･･の部分） IF(G2="",H1+1,SUM((sheet1!$G$2:$G$11+ROW($A$1:$A$10)/100>G2+ROW(A1)/100)*4) +1)
Mr.ﾀﾞｰﾂさん 修整合計列の式で対応できませんか？ 多分いけると思いますよ。 ひょっとして、このログはもう読んでないかな？(YOU君)
$G$2:$G$11+ROW($A$1:$A$10)/100 $G$2+0,01 $G$3+0,02 ・ ・ $G$11+0.10 同じ数字の場合上位下位を決める（仮 次の関数の並び替え順を決める為） （ROW($A$1:$A$10)をC列を参照で順位を決める方法も）JUN
JUNさん ありがとうございました。
問題が１つみつかりました。
参加人数に応じて下式の↑の数字を（セル範囲）を変更しないとH列に正確な数字が
出てきません｡
=IF(G2="",H1+1,SUM((sheet1!$G$2:$G$11+ROW($A$1:$A$10)/100>G2+ROW(A1)/100)*3)+1)
^^ ^^
↑ ↑
つまり、sheet1とsheet2の使いまわしができず、計算する都度参加人数（データの最終セル
の行番号）に応じて↑の数字を変更する必要があります。
私のやり方が間違っているのでしょうか？（YOU君)
どうもこのスレはスッキリ収まらんみたいでんなぁ。
JUNはんが登場するまでの繋ぎとして一遍マクロで遊んでみまっか？
下のコードを標準モジュールにコピペして、順位一発を実行すればどないでっか？
ご希望通りになりまへんか？
（弥太郎）
'-------------
Sub 順位一発()
Dim mxrow As Long, i As Long, b As Integer, m As Integer, f As Integer
Dim j As Integer, rnk, u As Integer, flag As Boolean, lrge As Integer, x
Dim dic As Object, dic1 As Object, w_flg As Boolean, tbl, t_tbl
Set dic = CreateObject("scripting.dictionary")
Set dic1 = CreateObject("scripting.dictionary")
With Sheets("sheet1")
mxrow = .Cells(Rows.Count, 2).End(xlUp).Row
tbl = .Range("b2").Resize(mxrow - 1, 6)
ReDim x(1 To UBound(tbl, 1), 1 To 4)
t_tbl = .Range("g2").Resize(mxrow - 1)
ReDim t_tbl(1 To UBound(tbl, 1))
i = 1
On Error Resume Next
Do While i <= UBound(tbl, 1)
If i + 3 <= UBound(tbl, 1) Then
If tbl(i + 3, 6) = "" Then
tbl(i, 6) = Int(tbl(i, 6) * 3 / 4)
dic(i) = Empty
flag = True
End If
End If
t_tbl(i) = tbl(i, 6)
If dic1.exists(tbl(i, 6)) Then
w_flg = True
m_data = tbl(i, 6)
Else
dic1(tbl(i, 6)) = Empty
End If
i = IIf(flag, i + 4, i + 3)
flag = False
Loop
i = 1
Do While i <= UBound(tbl, 1)
f = f + 1
x(i, 1) = f
lrge = Application.Large(t_tbl, f)
rnk = Application.Match(lrge, t_tbl, 0)
If w_flg And m_data Then
For j = 1 To UBound(t_tbl)
If lrge = t_tbl(j) And rnk <> j Then
If tbl(rnk, 2) <> tbl(j, 2) Then
rnk = IIf(tbl(rnk, 2) > tbl(j, 2), rnk, j)
ElseIf tbl(rnk + 1, 2) <> tbl(j + 1, 2) Then
rnk = IIf(tbl(rnk + 1, 2) > tbl(j + 1, 2), rnk, j)
Else
rnk = IIf(tbl(rnk + 2, 2) > tbl(j + 2, 2), rnk, j)
End If
t_tbl(j) = t_tbl(j) + 10000
w_flg = False: Exit For
End If
Next j
End If
m = IIf(dic.exists(rnk), 4, 3)
n = 0
For b = i To i + m - 1
x(b, 2) = tbl(rnk + n, 1)
If b = i Then x(b, 3) = tbl(rnk + n, 6)
x(b, 4) = tbl(rnk + n, 5)
n = n + 1
Next b
i = i + m
Loop
End With
Sheets("sheet2").Cells(2, 1).Resize(UBound(tbl, 1), UBound(x, 2)) = x
End Sub
弥太郎さん、残念ながらダメでした。
いろいろやってみましたが、現実的な解決方法は B列（名前）にデータがある最終行を検索して、その１つ下の行から最下段の行までを 一気に削除することです。でも、マクロがわかりません･･･。（YOU君）
=if(counta(sheet1!$A$2:$A$101)*4<row(A1),"",IF(G2="",H1+1,SUM((sheet1!$G$2:$G$101+ROW($A$1:$A$100)/100>G2+ROW(A1)/100)*4)+1)) 範囲を多めに設定すれば if(counta(sheet1!$A$2:$A$101)*4<row(A1),"", 多い部分は 空白対応で JUN
どこがどうちゃいまんのやろ？ こちらではスパーッとものの見事に結果が表示されますがなぁ・・・ ramrun邸のアップローダーにアップしようと思いましたけど、今日は一日中調子が悪く て繋がりまへんでしたわ。残念ながら・・・ （弥太郎）
JUNさん 弥太郎さん 返事が大変遅くなりました。ありがとうございました。。（YOU君）
JUNさん >範囲を多めに設定すれば >if(counta(sheet1!$A$2:$A$101)*4<row(A1),"", 多い部分は 空白対応で により、３人組と４人組が混在しない場合は、なんとかなりそうです。
弥太郎さん 原因を調査した結果、こちらのsheet1の入力に誤りがありました。失礼しました。 ご指摘の通り、順位が無事表示されました。同点時の順位決定も反映されています。
現在、参加人数によるチーム構成（３人組・４人組）とチーム順位を決める 方法を検討中です。良いアイデアがあれば教えて下さい。
参加人数 3人ﾁｰﾑ 4人ﾁｰﾑ
30 10 0
31 9 1
32 8 2
・・・ ・・・ ・・・
58 18 1
59 17 2
60 20 0
参加人数によるチーム構成（３人組・４人組）ができました。（YOU君)
参加人数を３で割った余り（０，１，２）を３つのパターンに分けて A列に1〜60までのシリアルNo.を入力して、B列（０の場合）C列（１の場合）、 D列（２の場合）のセルにA列と参加人数からなる計算式を入れることで チーム構成（３人組・４人組）の組み合わせが可能になりました。
satoさん かなれっとさん JUNさん 弥太郎さん ありがとうございました。これで全部解決しました。
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.