文字をランダムに特定のセル内に振りわける

質問

ご教授よろしくお願いします。

[エクセル2003]

複数の高校から多数の高校生に「筆記試験」と「面接試験」の模擬を受けさせることになりました。そこで以下の条件で特定のセル内にそれぞれの高校からランダムに生徒を振り分けたいと考えています。

(1)筆記試験は全ての生徒が受験するため該当するセルの学生数は手入力（Ｌ１、Ｓ１、Ｌ４、Ｓ４など）
(2)筆記試験を受けた生徒はその後面接試験を受験する。面接試験は前後半に分かれている。
(3)前後半はそれぞれ高校の学生数を半分にする。ただし学生数が奇数の場合は前半の受験者数を多くする。
(4)面接会場は「Ａ」～「Ｊ」までの部屋があり、各部屋6人まで入室できる。
(5)面接部屋はＡ＞Ｂ＞Ｃ＞Ｄ・・・の順で生徒の割り振りをランダムで行う。このため「まずＡ部屋に全員を入れる」ことはせず、あくまでＡＢＣＤの順で高校生を振り分けていく。
(6)ランダムに振り分けた結果「Ａ部屋」にb高校の学生が5人いたとしてもそれは構わないが、極力それぞれの高校生がバラバラの部屋に振り分けられるようにしたい。
(7)後半は前半で受験しなかった残りの学生（数）を同様に振り分ける。前後半の合計学生数は筆記試験を受験する合計数と同じになる。
(8)実際には漢字で高校名が入る。

分かりにくいところがあるかと思いますので添付画像をご確認いただき、ご教授いただけると助かります。よろしくお願いします。

回答No.1

tsubuyuki

いくつか。

・各高校の「人数」がわかっても、名簿が無い状態でどう振り分けるのか
・前後半に分けたいようだが、その「基準」はどうなるのか
・例えば１２１人が受験するとして、前半６１人・後半６０人
　１０部屋に６人ずつとして、前半で一人があふれるが、この扱いをどうするか
・前後半の合計学生数は筆記試験を受験する合計数と同じとあるが、
　例の図を見る限り、筆記試験５０名・面接４９名のようだが、どういうことか

とまぁ、色々ありますが。

とりあえず、名簿を作って、基準を決めて前後半を分け、
上から「Ａ～Ｊ」を順番に割り付けていくのが一番早くないですか？
高校毎に名簿を作っていけば
> 極力それぞれの高校生がバラバラの部屋に振り分けられるように
できますし。

Ａ高校

受験番号　　高校　　　　名前　　　部屋
　　１　　　井高校　　　一郎　　　　Ａ
　　２　　　井高校　　　二郎　　　　Ｂ
　　３　　　井高校　　　三郎　　　　Ｃ
　　４　　　呂高校　　　四郎　　　　Ｄ
　　　　　　　　～　中略　～
　　８　　　呂高校　　　八郎　　　　Ｈ
　　９　　　波高校　　　九郎　　　　Ｉ
　１０　　　波高校　　　十郎　　　　Ｊ
　１１　　　波高校　　十一郎　　　　Ａ
　１２　　　波高校　　十二郎　　　　Ｂ
　　　　　　　　～　後略　～

こんな感じで名簿を作っておけば、
あとはＬＯＯＫＵＰ関数ででもＩＮＤＥＸ関数を使ってでも
部屋を割り振った表を作るのはそんなに難しくないですよ。

完全にランダムにしたいなら、どちらにしても名簿が必要ですが、
ＲＡＮＤ関数・ＲＡＮＫ関数・ＭＯＤ関数辺りを組み合わせて使うと良いかも。

上表のＤ列に「=RAND()」で乱数を発生させ、
Ｅ列に「=RANK(D2,$D$2:[必要な範囲])」で乱数の「順位」を返し、
Ｆ列に「=MOD(E2,10)」で「順位の一の位」を返すことで
０～９までの数字を均等に割り振ることが出来ますので、
それを部屋に割り振る、と言う手があります。

ただし「ブックのどこかを再計算」すると、ここも再計算されてしまいますので、
確定次第「数値を貼り付け」するのを忘れると大変です。

まぁ、何にせよ、こう言うときは「名簿」「割振りのルール」は必須ですよ。

お礼

早速のご回答ありがとうございます。まず質問にお答えします。

・各高校の「人数」がわかっても、名簿が無い状態でどう振り分けるのか

→個人名簿はここには含みません。どの学校の生徒がどの部屋に入るかだけをここでは決めていきます。

・前後半に分けたいようだが、その「基準」はどうなるのか

→基準はあくまで筆記試験を受ける各学校の人数の半数を分けると考えています。

・例えば１２１人が受験するとして、前半６１人・後半６０人
　１０部屋に６人ずつとして、前半で一人があふれるが、この扱いをどうするか

→この模擬面接は「一部屋6名×部屋数」で上限が決まっています。それ以上は受付しませんので部屋のキャパを越える人数の扱いは発生しません。

・前後半の合計学生数は筆記試験を受験する合計数と同じとあるが、
　例の図を見る限り、筆記試験５０名・面接４９名のようだが、どういうことか

→画像が小さいため見づらいかもしれませんが、どちらも50名です。

ご提示いただいた方法は人数が少なければそれもいいと思うのですが、今回は色々な場所で行うため、全体では1000名超の割り振りが最終的に行われると考えています。

各会場のキャパもそれぞれ異なるため、小さいところでは100～200名で済みますが、多いところは600名くらいになりそうな場所もあり、初めに割り振りを入力する作業に時間がかかってしまうことと、ミスが発生しやすくなることで敬遠したいところでした。

いただいたヒントを元に再度考えています。ご助言有難うございました。