Excelで割り振りしたい。

質問

Excelを使って以下の割り振りをしたいと思います。
(添付ファイル参照)
・No1～10の10人がそれぞれA～Fの６つの部屋のうちから３つの部屋を選びます。
・選んだ部屋で行われる説明会に参加します。
・各説明会は３回行われるので、10人は１回目～３回目で選んだ３つの部屋を回ることになります。
・各部屋の各回の人数はなるべく均等になるようにします。
　例えば添付ファイルでは、会場別のべ希望数がEは３なので、１回目～３回目の参加者数は1,1,1とし、Bは6なので2,2,2となるようにします。

このような条件に当てはまるように、各人の１回目～３回目に参加する部屋を割り振りたいのです。
なるべくマクロは使わずに割り振り表を作成したいのですが、Excelでできますでしょうか？
よろしくお願いいたします。

回答No.5

masnoske · ベストアンサー

> ・各部屋の各回の人数はなるべく均等になるようにします。

この条件も曲者ですね。
(1,1,3) よりも (1,2,2) のほうが均等（＝ばらつきが少ない）と考えますが、(1,1,3) の組があるのは許されないのでしょうか。
そもそも必ず (1,2,2) になる答えがあるのかどうか。

仮に (1,1,3) が許されるなら、200件を10～15分で手作業振り分けできそうです。そんなに時間を掛けられないというのなら、手作業をマクロ化することは可能です。でも、できるだけマクロを使いたくないんですよね。

また、せっかくマクロでやるのに、最適解がどうかわからないというのも気持ち悪いです。

お礼

ご指摘のように各部屋の人数にバラツキを許可して、手作業を加えた半自動で解決できました。

補足

(1,1,3)の組が許されないわけではないです。

というか、目から鱗です。
今まで、自分で作ったものでは、どうにもうまくいかなかったのですが、(1,1,3)を許したらどうなるか、明日早速試してみたいと思います。

回答No.4

Mathmi

実際にコードを組んだ訳ではないので出来るかどうか分かりませんが、数独のアルゴリズムは応用できないでしょうか？
　回数(1,2,3)と部屋(A,B,C,D,E,F)の3*6の18マスで、各マスには参加人数分の子マスがある、といったデータ形式ならば、既存のアルゴリズムが利用できそうな気がしますが。

お礼

ありがとうございます。次の３連休あたりに数独のアルゴリズム研究してみたいと思います。

回答No.3

masnoske

各参加者の部屋を回る順列は、以下の６通りあります。
(1,2,3)(1,3,2)(2,1,3)(2,3,1)(3,1,2)(3,2,1)
それが200人ということは、6の200乗の組み合せから答えを見つけなければなりません。マクロで総当りするのは、現実的ではないので他の方法を検討する必要がありますね。

補足

おそらく答えのパターンも数多くあるんだと思います。
そのウチの１つが分かればいいのですが…

最初、もっと簡単だと思ったのですが、やり始めてみるとものすごく難しく困っています。

回答No.2

masnoske

簡単な関数と手作業をして良いなら方法はありますが、関数やゴールシークを使って一発で求めたいというのなら難しい。
この例であれば、以下が答えの１つになるかと（他にあるかも）。
1(B,C,D)
2(F,A,C)
3(D,B,A)
4(F,C,B)
5(A,D,E)
6(B,E,F)
7(E,A,D)
8(C,D,B)
9(A,F,C)
10(C,B,F)

部屋の人数割は、
A(2,2,1)
B(2,2,2)
C(2,2,2)
D(1,2,2)
E(1,1,1)
F(2,1,2)

回答No.1

molly1978

希望者数から各回別の各部屋定員を決め、一人ずつ希望から割り振っていくことになると思います。簡単に関数で割り振ることはできません。マクロならできなくありませんが、かなり面倒です。手動で行うのが現実的です。

補足

実は、対象者が10名でなく200名前後となるため、手動は困難なのです。