エクセルを使って 組み合わせ リレーチーム

28人の子供の100m走の記録を元にリレーチームを作りたいと思います。
条件は、リレーは１チーム４人　それぞれのチームのタイム差ができるだけ少ない　としたいと思います。
30人全ての記録の組み合わせを作り、最も差が少ない組み合わせにしたらよいと思うのですが。
また30人の子供でおなじ4人組を作ったとしたら、2人あまるような場合はどうしたらよいでしょうか。
関数、VBA等を使ってできる方法を教えていただけないでしょうか。
windows7 excel2007 を使っています。

質問者からの補足コメント

• soixante 様
  ありがとうございます。教えていただいたとおりにやってみました。
  子供の記録の組み合わせまではできましたが、説明の仕方が悪かったようなので補足させていただきます。
  
  28人でリレーチームを７つ作ります。７チームのメンバー４人の合計タイムができるだけ近いような組み合わせにしたいと思います。
  
  soixante 様の方法だとたくさんの組み合わせができて、オートフィル同じタイムの組み合わせの抽出はできるのですが、別々の子供の組み合わせ（７チーム）にするためには数値を手作業で拾っていくようになってしまいます。
  できれば最後のチーム分けまでできるとたいへん助かるので、お知恵をお借りいただけないでしょうか。よろしくお願いします。

  No.1の回答に寄せられた補足コメントです。 補足日時：2015/05/06 16:32

No.2 回答者： soixante 回答日時： 2015/05/09 16:17

#1です。

補足コメント有難うございました。

大変失礼しました。確かに組み合わせまで確定しないと意味ないですよね。
申し訳ないですm(_ _)m

で、ちょっと考えてみました。・・・が、かなり難問ですね。

28人から4人ずつのチームを7組作る組合わせ
28C4*24C4*20C4*16C4*12C4*8C4*4C4　／　7！
＝13,189,599,057,009,400 通り！
１京3189兆5990億5700万9400通り　・・・・　Orz

というわけで、マクロを使用しても到底総当たりは無理。
（＃１で見た最頻値のチームだけを取り出して、マクロで総当たりの7組を作ろうとしましたがうまくいきませんでした。
あれこれ試行錯誤しましたがちょっと無理でした。）


結局、超ベタな方法を考えてみました。もはやマクロとは全く関係ないですが（汗。

生徒28人を、タイムの昇順に並べて、速い子から、A,B,C・・・X,Y,Z,a,b とします。
で、これを順に7チームに割り振っていく。
①　各チームの1人目は　A～F　に。以下の図でいえば、上から下に。
②　2人目は、下から上に、H～N　まで。
3人目、4人目も同様に。

A　N　O　b
B　M　P　a
C　L　Q　Z
D　K　R　Y
E　J　S　X
F　I　T　W
G　H　U　V

横につながった4人が、一チームです。
各人の測定タイムの散らばり具合にもよりますが、かなり均質化できると思います。
わたしの手元ダミーデータではほぼ同じタイムでした。
人名、タイムを当てはめて4人の合計タイムを見てみて下さい。

わたしの力ではお役にたてず申し訳ありません。大変失礼いたしました。m(_ _)m
もっと詳しい方の回答が付くことを祈っています。

この回答へのお礼

ありがとうございます。思っていた以上にたいへんなお願いだったようで、すいませんでした。
私も今までは速い子から順番に並べて・・・というやり方をしておりましたが、エクセルを使えば格好良くできるかな、と安易に思ったのですが。
お手数をおかけして、ほんとうにありがとうございました。

お礼日時：2015/05/10 20:13

No.1 回答者： soixante 回答日時： 2015/05/06 13:59

>28人の子供の100m走の記録を元に

>30人全ての記録の組み合わせを作り、最も差が少ない組み合わせに

ちょっとここが良く分かりません。
「28人の記録をもとに、30人？のすべての記録の組み合わせ」？

というわけで、28人の記録をもとに28人の記録の組み合わせ、でやってみました。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
まず、4人ずつのチームは何通り作れるか。
28人から4人を抜き出す組合せなので、28C4＝20,475通り。
エクセルで出すなら、=COMBIN(28,4)　で出せます。

では作業。
わたしは以下の通りやってみました。

①　シートの1枚目を「data」とし、A列に縦に1～28までの番号を振る
②　同シートのB列に、縦に、生徒名28人を振る
③　同シートのC列に、それぞれの生徒のタイムを入力する
　（ここは昇順か降順にしとくとよいかもしれません）
④　シートの2枚目を「kekka」とする。
⑤　マクロは以下のコードを標準モジュールに書き、実行
------------------------------------------------------------------------
Sub zzzz()
Dim a As Integer, b As Integer, c As Integer, d As Integer
Dim k As Long
Dim WsD As Worksheet, WsK As Worksheet
Dim Rng As Range, WSF As Object

Set WsD = Worksheets("data")
Set WsK = Worksheets("kekka")
Set Rng = WsD.Cells(1, 1).CurrentRegion
Set WSF = Application.WorksheetFunction

k = 2

For a = 1 To 28
　　　 For b = a + 1 To 28
　　　　　　 For c = b + 1 To 28
　　　　　　　　　 For d = c + 1 To 28
　　　　　　　　　　　　WsK.Cells(k, 1).Value = WSF.VLookup(a, Rng, 2, 0)
　　　　　　　　　　　　WsK.Cells(k, 5).Value = WSF.VLookup(a, Rng, 3, 0)
　　　　　　　　　　　　WsK.Cells(k, 2).Value = WSF.VLookup(b, Rng, 2, 0)
　　　　　　　　　　　　WsK.Cells(k, 6).Value = WSF.VLookup(b, Rng, 3, 0)
　　　　　　　　　　　　WsK.Cells(k, 3).Value = WSF.VLookup(c, Rng, 2, 0)
　　　　　　　　　　　　 WsK.Cells(k, 7).Value = WSF.VLookup(c, Rng, 3, 0)
　　　　　　　　　　　　WsK.Cells(k, 4).Value = WSF.VLookup(d, Rng, 2, 0)
　　　　　　　　　　　　WsK.Cells(k, 8).Value = WSF.VLookup(d, Rng, 3, 0)
　　　　　　　　　　　　 WsK.Cells(k, 9).Value = WSF.Sum(Range(WsK.Cells(k, 5), WsK.Cells(k, 8)))
　　　　　　　　　　　　k = k + 1

　　　　　　　　　Next d
　　　　　　Next c
　　　Next b
Next a

Set WsD = Nothing
Set WsK = Nothing
Set Rng = Nothing
Set WSF = Nothing
End Sub
-----------------------------------------------------------------
⑥　これを実行すると、kekkaシートのA~D列に生徒名、E~H列にそれぞれのタイム。
　I列に4人のタイム合計が並びます。
⑦　最も多い合計パターンを出すには、最頻値を求めるとして、
　＝MODE($I$2:$I$20476) で出せますから、オートフィルタして、この最頻値となる
　組み合わせを抽出してみてはどうでしょうか。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
＞30人の子供でおなじ4人組を作ったとしたら、2人あまるような場合
これについてもよく分からなかったのですみません。