タイム計算がしたいのです

質問

前略　たとえばですが、
駅伝部に６０人登録しています。１区から１０区まで全員のタイムをとり、誰が何区を走る順序だと最高タイムになるかを知りたいです。
エクセルのセルで言えば、左縦列に「Ａさん」「Ｂさん」・・・。
上横列に「１区」「２区」・・・・「１０区」まで。中にタイムが記入されています。当然ですが一人が２度走ることはできません。
ちなみに、私のエクセルのスキルは「小の上」程度です。

回答No.6

_Kyle

>マクロとかアクセスとか使えば…

"簡単"かどうかは別にして、マクロがアリならなんとか…。

基本的な考え方

　●１次選抜
　　　それぞれの区間について、
　　　11位以下の部員を選手に選んだ場合、
　　　「その部員より上位で、かつ他の区間の選手でない部員」
　　　が少なくとも１人存在することになるので候補から外す

　●２次選抜
　　　それぞれの区間について、
　　　「何位の部員を選手にするか」の組み合わせを調べる
　　　※１区から順に選んでいき、
　　　　　・既に選ばれている選手については飛ばす
　　　　　・暫定ベストよりも遅くなることが判った時点で抜ける
　　　　ので、いわゆる「総当り」ではありません。

以下、やっつけですがサンプルマクロです。

※[名前の定義]で、元表(見出行含まず、氏名列を含む)に"タイム表"という[名前]をつけて実行してください。

※元表の右隣１列を作業列として、元表の直下６行を結果表示範囲として使います。
　また、元表を直接ソートするので、バックアップをとった上で実行してください。

※区間の数、部員数は可変です（元表のサイズから判定します）
※ワークシート上でソートしているのは「持ちタイムが同じ」というケースを考慮したためです。
※合計タイムが同じになる組み合わせが複数ある場合には最初に見つけた組み合わせを表示します。

'--------------------------↓　ココカラ　↓--------------------------
　Dim cntMem　　　As Integer　'部員数
　Dim cntSct　　　As Integer　'区間数
　Dim tmpTim　　　As Double　 '仮タイム(差分)
　Dim tmpCmb()　　As Integer　'仮チーム(順位)
　Dim bstTim　　　As Double　 '暫定ベストタイム(差分)
　Dim bstCmb()　　As Integer　'暫定ベストチーム(順位)
　Dim aryIdx()　　As Integer　'IDNo.
　Dim aryTim()　　As Double　 'タイム(差分)
　Dim aryExt()　　As Boolean　'選択済フラグ
'--------------------------
Sub Sample()

　Dim orgTbl　　　As Range
　Dim tmpRng　　　As Range
　Dim i　　　　　 As Integer
　Dim j　　　　　 As Integer

　Set orgTbl = Range("タイム表")
　cntMem = orgTbl.Rows.Count
　cntSct = orgTbl.Columns.Count - 1

　ReDim aryIdx(1 To cntSct, 1 To cntSct + 1)
　ReDim aryTim(0 To cntSct, 1 To cntSct + 1)

　Set tmpRng = orgTbl.Offset(, cntSct + 1).Resize(, 1)

　For i = 1 To cntMem
　　tmpRng(i) = i
　Next i

　For i = 1 To cntSct + 1
　　With orgTbl.Resize(, cntSct + 2)
　　　.Sort _
　　　　Key1:=.Cells(1, i + 1), _
　　　　Order1:=xlAscending, _
　　　　Header:=xlNo, _
　　　　OrderCustom:=1, _
　　　　Orientation:=xlTopToBottom
　　End With
　　aryTim(0, i) = Application.Min(orgTbl.Columns(i + 1))
　　For j = 1 To cntSct
　　　aryIdx(j, i) = tmpRng(j, 1)
　　　aryTim(j, i) = orgTbl(j, i + 1) - aryTim(0, i)
　　Next j
　Next i

　bstTim = Application.Max(aryTim) * cntSct
　ReDim tmpCmb(1 To cntSct)
　ReDim aryExt(1 To cntMem)
　Call Sample2(1)

　ReDim dspAry(1 To 5, 0 To cntSct)
　dspAry(1, 0) = "区間"
　dspAry(2, 0) = "IDNO."
　dspAry(3, 0) = "名前"
　dspAry(4, 0) = "区間順位"
　dspAry(5, 0) = "タイム"
　For i = 1 To cntSct
　　dspAry(1, i) = i
　　dspAry(2, i) = aryIdx(bstCmb(i), i)
　　dspAry(3, i) = orgTbl.Cells(aryIdx(bstCmb(i), i), 1)
　　dspAry(4, i) = bstCmb(i)
　　dspAry(5, i) = orgTbl.Cells(aryIdx(bstCmb(i), i), i + 1)
　Next i

　With orgTbl.Offset(cntMem + 1).Resize(5)
　　.Style = "normal"
　　.Rows(1).NumberFormatLocal = "0"" 区"""
　　.Rows(2).NumberFormatLocal = "0"" 番"""
　　.Rows(4).NumberFormatLocal = "0"" 位"""
　　.Rows(5).NumberFormatLocal = orgTbl.Cells(1, 2).NumberFormatLocal
　　.Value = dspAry
　　.HorizontalAlignment = xlCenter
　End With

End Sub
'--------------------------
Private Sub Sample2(ByVal Sct As Integer)

　Dim i　　　　 As Integer
　Dim bufTim　　As Double
　Dim bufAry()　As Boolean
　bufTim = tmpTim
　bufAry = aryExt

　For i = 1 To cntSct
　　If aryExt(aryIdx(i, Sct)) Then
　　ElseIf tmpTim + aryTim(i, Sct) >= bstTim Then
　　　Exit For
　　Else
　　　tmpTim = tmpTim + aryTim(i, Sct)
　　　tmpCmb(Sct) = i
　　　aryExt(aryIdx(i, Sct)) = True
　　　If Sct < cntSct Then
　　　　Call Sample2(Sct + 1)
　　　Else
　　　　bstTim = tmpTim
　　　　bstCmb = tmpCmb
　　　End If
　　　tmpTim = bufTim
　　　aryExt = bufAry
　　End If
　Next i

End Sub
'--------------------------↑　ココマデ　↑--------------------------

Excel2003で動作確認。
（区間が６つの場合について、ワークシート上で総当りした結果と比較検証）

以上ご参考まで。長乱コード陳謝。

お礼

ありがとうございました。
私のスキル不足で理解できない文章もありましたが、画像を見てマクロを貼って、無事にできました。
どうもお世話をかけました。

回答No.5

AKARI0418

#4様のおっしゃるとおり、できないことはありませんが、ちょっと無理のある仕様ですね。

たとえば、各区間ごとに上位TOP5を抽出します。
各区間の上位を0としたときの時間差をおのおの求めます。
例
1区
Aさん,0
Eさん,10
Dさん,16
Fさん,25
Jさん,35

最後に時間差が最も少なくなる組み合わせを抽出します。
組み合わせ数は5^10で大量ですが、人の目で見て処理できる規模になるので判断できるのではないでしょうか？

結局組み合わせ数を減らす方法でしかないのが実際なのですが・・・・。

お礼

回答ありがとうございます。
行き着くところはやっぱり組み合わせのようですね。
貴重な時間を割いていただきありがとうございました。

回答No.4

grumpy_the_dwarf

2004までのExcelの行数は65536行まで、2007以降でも100万行です。
一方、1区から10区までを60人の中から選ぶ組み合わせは27億パター
ンくらいありますから、総当たりでパターンを抽出して合計タイム
を比較することは出来ません。それじゃぁってんで、レギュラー10
人を確定させといて誰がどの区を走るかってことになっても、360万
パターンを越えるのでやっぱり総当たりは無理です。

ほとんどを人間が判断して、どうしても迷う数パターンについてだ
け比較検討するためのシートを作るしかありませんよ。区間名の下
に名前を入れると、さらにその下の行にそいつの成績が出るような
ものを思いつきますね。もちろんそんなことをしても、10回走って
10回同じタイムにならないのが人間だから、単なる参考資料なんで
すけどね。

お礼

貴重な時間を割いていただきありがとうございました。
見当違いかもしれませんが、マクロとかアクセスとか使えば簡単に出来るのかと思っていました。

回答No.3

Nouble

その表より
各々が何区を走る想定にするかの組み合わせ表を作り
それぞれにパターンNo.を割り当てて
パターンNo.＿＿＿1＿＿＿＿2＿＿＿＿＿＿3＿＿＿＿4＿＿＿＿5＿＿＿＿＿6＿＿＿＿7＿＿＿＿＿8＿＿＿＿9＿＿＿＿10＿＿＿＿＿トータル
＿＿1＿＿＿5分16秒＿＿6分05秒＿＿5分22秒＿4分37秒＿9分33秒＿8分59秒＿4分39秒＿10分17秒＿5分58秒＿7分36秒＿1時間08分22秒
＿＿2＿＿＿6分11秒＿＿4分59秒＿10分43秒＿8分02秒＿4分34秒＿6分05秒＿10分51秒＿4分38秒＿9分28秒＿5分51秒＿1時間11分22秒
＿＿3＿＿＿4分55秒＿10分33秒＿＿5分41秒＿9分17秒＿8分18秒＿10分30秒＿8分02秒＿5分58秒＿9分22秒＿6分39秒＿1時間19分15秒
＿＿4＿＿＿6分51秒＿＿7分57秒＿＿5分03秒＿7分20秒＿7分31秒＿4分55秒＿7分39秒＿9分23秒＿6分41秒＿7分20秒＿1時間10分40秒
＿＿5＿＿＿6分52秒＿10分27秒＿＿6分45秒＿8分48秒＿4分19秒＿7分41秒＿5分48秒＿6分31秒＿9分28秒＿6分35秒＿1時間13分14秒
＿＿6＿＿＿5分21秒＿＿9分23秒＿＿6分46秒＿6分08秒＿9分44秒＿5分34秒＿8分39秒＿7分29秒＿6分24秒＿9分33秒＿1時間15分01秒
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　・
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　・
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　・
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　・
と言った表を作成すれば簡単に出るのじゃないですか？
この際にタイムは１分２４秒なら0:1:24と入力して
書式設定のユーザー定義で
m"分"ss"秒"
としてくださいね

お礼

組み合わせ表を作成すると、莫大な組み合わせが出来るみたいで・・・。
回答をありがとうございました。

回答No.2

tom04

No.1です・・・
質問内容を勘違いしていました！m(__)m
読み流してください！

よくよく考えると、数名の人の実力が極端に優れている場合は
どの区間も上位になってしまうので、RANK関数は使えないような気がします・・・
どうも失礼しました。

お礼

下記に補足を書いている間に、すれ違いでご返事を頂き間が悪くなってしまいすみません。
お気持ち、ありがとうございます。

回答No.1

tom04

一例ですが・・・
各区間の右側に作業列（順位を入れるため）の列を挿入します。
結局10区間あるのであれば10列挿入することになります。

B2～B61に1区のタイムが入っていると仮定して・・・
C2に1区の順位を表示させる場合
C2=RANK(B2,$B$2:$B$61,-1) としてオートフィルの（+）マークでダブルクリック
これで好タイム順（少ない数値順）に表示されるはずです。

同じようにすべての区間について同じ操作を行えばいいと思います。

補足

早速のご返事、ありがとうございます。
私も最初は各区の１位を出せば良いのかと考えたのですが、
同じ人が１位を何度も出すので、出来ればすべての組み合わせのタイムと走順を明記したいのです。６０人だと組み合わせは・・。
６０ｘ５９ｘ５８ｘ５７・・・・１＝？？
説明不足ですみませんでした。