EXCELでの組合わせ 内容の表示について

質問

17名のパート登録者より6名を選抜し、1日8回の勤務に割り当てる一月分の勤務表を作成しています。

IF文をNESTして
17C6*8*一ヶ月を総当りで出す

均等にする為、8*30（一ヶ月）/17回、乱数によりセルに個々の名前を書き出し、全てのセルデータ個数が6になるまでLOOP（多少の差はCUT）という方法

どなたか　実装のアドヴァイスをいただけたら幸いです。

回答No.1

imogasi

約２日回答が出ませんね。難しいですね。確率論などから言えば、下記は全く駄目な回答だと思いますが、空で２４０組６人の組み合わせを上げろといわれると、苦痛ですよね。それで上げてみますのでご検討下さい。エクセルで組み合わせの問題の質問が偶に上がりますが、エクセルは無力なように思います。
考えかたとしては出動回数平等を優先の考えです。
（１）A列に１－１７の値が入ったセルを８５個作る
　　　１７ｘ８５＝１４４０(ほぼ）
　　　A1に関数式=INT((ROW()-1)/85)+1を入れる。
　　　この式をA2:A1440に複写する。
　　（A1から遠いセルまでの複写のやり方は下記（６）に
　　　述べた方法参照）
　　　A列の数字は、担当者の背番号だと考えてください。
（２）自分自身のセルに、形式を選択して貼りつけ－値を　　　行い関数式を消し値だけのセルにする。
（３）B列B1に=RAND()を入れて、B2：B1440に複写する。
（４）B1:B1440について、自分自身のセルに、形式を選択　　　して貼りつけ－値を行い関数式を消し値だけのセル　　　にする。
（５）A1:B1440を範囲指定し、B列の値で、並べ替えする。
　　　これで１－１７はシャッフルされたつもり。
（６）C1に=INDEX($A$1:$A$1440,(ROW(C1)-1)*6+COLUMN(C1)-2,0)と入れて、H1まで横方向に式を複写する。
C1:H1を範囲指定して、編集－コピーその後B1をポイントして名前ボックスにH240といれ、SHIFTキーを押しつつENTERで、B1:H240が範囲指定されるから、編集－貼りつけ。これでA列の１列がC-Hの６列に折り畳まれる。
（７）I列に=SUM(IF(COUNTIF(C1:H1,C1:H1)=1,0,1))
と入れてSHIFT+CTRL+ENTER（３キーを同時に押す）。配列数式。
I1をI2:I240に複写する。
I列の値が０の行はそのまま採用できる。
２，３，４・・の行は約半数出てきてしまうようである。
２，３，４・・の行は同一人が出てきて、組み合わせとして使えない。それで人間が修正する。
（８）修正ルールとして、完璧なものは思いつかなかったが一法として下記を記す。
別に１－１７の１セットを手持ちをメモしておく。
ある行で同一数字ダブリがいると、その数字を次のダブリ行のダブリ数字に置き変える。出来れば相互に置換えて見る。あるいは、上から下へ置換え、下から上へ置換えやってみる。
それで上手く行けばそれでよし、上手くいかない時は１－１７の手持ちの先頭から適当にダブらない数字を補充し、ダブリ分は次回に出来るだけ早い機会に使うよう先頭に待機させる。
１－１７のワンセットの予備で足りない時は第２回目の手持ちとして１－１７を繰り出す。
最後に残った番号分だけ（１回以内のはず）出動回数の不平等（出動が少ない）が発生するが、目をつぶる。
（９）１つの行でダブリセルを色を付ける方法を記します。ダブリ潰し洩れを防ぐのに使うと良いと思う。
C1:H1を範囲指定
書式－条件つき書式－数式が－数式を指定。
書式を指定して（例えばセルのパターンで色を指定し、セルに色を付ける）
=COUNTIF($C1:$H1,C1:H1)>1と入れて、SHIFT+CTRL+ENTER（３つのキーを同時に押す。（配列数式）
C1:H1をコピーし、C2:H240を範囲指定し、形式を選択して貼りつけ－書式を行う。これでダブリ数字セルに色がつく
。これか１つも残らないように修正するわけです。」

お礼

ご回答、ありがとうございます。
早速、検討させていただきたく思います。
また追加質問にてお手を煩わせるかとも思いますが、何卒、宜しくお願いいたします。