得点をもとにチーム分け

質問

小学校の教員をしている者なのですが・・・
体育などでいろいろなタイムや得点をもとにチーム分けをしなければならない場面が多々あります。
そんな時，excelで上手くチーム分けをする方法ってあるのでしょうか？

ちょっと具体的には
　A～Xまで２４人の児童の50m走のタイムをもとに，走力が均等になるよう１チーム６人のチームを４つ作りたい場合，チームの合計タイムが　[A～Xの平均タイム]×６　に最も近くなるようにA～Xを４つに割り振るようなことが，関数やマクロ（？）でできたりするのでしょうか。

もしこれができるなら本当に助かります。excelでできない場合も何かツールのような物でできればよいのですが・・・検索してみても思うような物が見つけ出せませんでした。

どなたか詳しい方，ご回答よろしくお願い致します。

回答No.2

hayasi456 · ベストアンサー

2通りやって見ました。
予想以上に差が出ました。

１）早い順にabcd 4チームに分ける
A列に名前、B列にタイムを入力
並び替えでタイムを優先
C列にチームabcdを繰り返し入力
並び替えでチームを優先
結果：チーム別平均にかなりの差が出来てしまいました。

２）一番早い子と一番遅い子を組み合わせる
A列に名前、B列にタイムを入力
並び替えでタイムを優先
C列１２番目までにチームabcdを繰り返し入力
C列最下位から逆にabcdを繰り返し入力(１３番目からはdcbaを繰り返し入力)
並び替えでチームを優先
結果：チーム別平均は近いものになりました。
修正：差のある2チームの各1名を入れ替え平均タイムがそろいました。

添付図参考

お礼

ご回答ありがとうございます。
　実は自分も同様の方法でやっていました。結局のところ「補正」の手作業が必要になるのですね。
そこまで自動的にやってくれる計算式みたいなのがあれば楽なのですが・・・。
　タイムには極端に遅い子や早い子が少しだけいたりするので，計算の組み合わせも相当難しくなりそうですので，無理みたいですね。もしかしたら「数学者」とかだったらどんなパターンにも当てはまるような数式を考えられるのかもしれませんが・・・。
　ここでこれまでに自分がやっていた地道な方法が間違っていないことに気づけただけでもめっけもんでした。ありがとうございました。

回答No.3

MackyNo1

持ちタイムの上位から順にならべたデータをできる限り総合計時間が等しくなるようにチーム分けしたい場合は、以下のような順に配分するのが合理的です。

すなわち、4チームに配分するなら上位から順に1～4チームに4番目まで配置し、次の5番目の人は、逆に4チームから1チームの順に配分し、その後は同じ配分を繰り返します。

操作的には、データをタイム順に並べ、チームの列の8つのセルに「1,2,3,4,4,3,2,1」と入力して、このセルをコピーします。

実際のチームの合計時間は、以下の式で表示できますので、うまく分配できているか調べてみてください。

=SUMIF($C$2:$C$25,E2,$B$2:$B$25)

お礼

ご回答ありがとうございました。
残念ながら＝SUMIFという関数がつかいこなせず，E2のセルには０が表示されてしまうだけでした・・・。
うまくいったとしても，おそらくNo1様の方法のように，チーム合計には無視できない差が出てしまうかもしれませんね・・・。
とにかく，ありがとうございました。

回答No.1

MarcoRossiItaly

組み合わせとかいろいろ考えると、なかなか難しい数学になっていきます。それより、単純に処理してみてはいかがですか？どの程度Excelを使いこなされているか分かりませんが、例えばこのような。

1.
A2:A27の範囲にA～Xの子の名前を、B2:B27にそれぞれのタイムを記入。

2.
C2セルに次の式を記入。

=rank(b2,$B$2:$B$27)　　※「$」マークを付けるには、キーボードのF4キーを何回か押します

3.
C2にカーソルを置いてその右下隅の角をダブルクリックすると、下方向にオートフィルされ、C2:C27に式が入力されます。

4.
A2:C27の表中のどこかにカーソルを置いた状態で、リボンあるいはメニューバーの「並べ替え」をクリック。C列を基準に降順で並べ替え。（または降順での並べ替え専用のボタンをクリック）

5.
足の速い子から順に4チームに分けます。次の式をD2に入力。

=mod(row(),4)

6.
3.と同様にしてD2:D27をオートフィル。

7.
A1:D27の範囲（数字に注意）を選択。続いて、リボンあるいはメニューバーのフィルタあるいはオートフィルタをクリック。表示されるプルダウンのボタンを使って、特定チームに所属する子だけを表示できます。

お礼

早速のご回答ありがとうございました。実はexcelはほとんど使えないのですが，この回答をいただいてから調べ，RANKという関数とmod(row()○)という関数についてうすらぼんやり理解することができました。勉強になりましたm(_ _)m

この方法だと
　「順位をつけて並べ替え，単純に４行おきにチーム編成したものを表示する」
ということになるようですね。
　合計タイムを出してみると，やはりけっこうな差が出てしまいました(^^;

　「数学的に難しい」と言われ，納得です。よくよく考えてみれば，確かに組み合わせや平均の考え方が複雑に入り組みそうですね。