エクセルを使ったグループ分け

質問

３つあるグループをいくつかのグループに分け直したいと考えております。
最初あるグループをＡ，Ｂ，Ｃ，分け直した後のグループを1、2、3、4、5（5グループの場合）
とし、イメージとしては以下のようなものを考えています

1・・・ＡＡＢＣＣ
2・・・ＡＢＢＣＣ
3・・・ＡＢＢＣＣ
4・・・ＡＢＢＣ
5・・・ＡＢＣＣ

※Ａ・・・Ａグループの人間　Ｂ・・・Ｂグループの人間　Ｃ・・・Ｃグループの人間

なお、Ａ、Ｂ、Ｃグループの人数は全てばらばらで、できれば出席・欠席、グループ数の変更等にも対応できればと考えています。お力添えの方よろしくお願いいたします。

回答No.6

KURUMITO · ベストアンサー

回答No3及び5です。
その後に欠席などの人についてはグループから外して並び替えたいとのことですね。ここではNo3でのA,B,Cグループの名簿で仮に欠席の人には名前の最後に/0を入力しているとします。
作業が複雑ですので作業列をたくさん用意します。
D2セルには次の式を入力してF2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(OR(A2="",COUNTIF(A2,"*/0")>0),"",MAX(D$1:D1)+1)

G2セルには次の式を入力してI2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>MAX(D:D),"",INDEX(A:A,MATCH(ROW(A1),D:D,0)))

J2セルには次の式を入力してL2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(G2="","",RAND())

M2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(J2="","",INDIRECT("G"&RANK(J2,J$2:J$100)+1))

N2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(K2="","",INDIRECT("H"&RANK(K2,K$2:K$100)+1))

O2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(L2="","",INDIRECT("I"&RANK(L2,L$2:L$100)+1))

そこでお求めの表ですがQ列からU列に表示させるとしたらQ2セルに次の式を入力したのちにU2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF((ROW(A1)-1)*5+COLUMN(A1)<=COUNTIF($M$2:$M$100,"?*"),INDEX($M:$M,COLUMN(A1)+1+(ROW(A1)-1)*5),IF((ROW(A1)-1)*5+COLUMN(A1)<=COUNTIF($M$2:$M$100,"?*")+COUNTIF($N$2:$N$100,"?*"),INDEX($N:$N,COLUMN(A1)+1+(ROW(A1)-1)*5-COUNTIF($M$2:$M$100,"?*")),IF((ROW(A1)-1)*5+COLUMN(A1)<=COUNTIF($M$2:$M$100,"?*")+COUNTIF($N$2:$N$100,"?*")+COUNTIF($O$2:$O$100,"?*"),INDEX($O:$O,COLUMN(A1)+1+(ROW(A1)-1)*5-COUNTIF($M$2:$M$100,"?*")-COUNTIF($N$2:$N$100,"?*")),"")))

お礼

少々複雑な数式で、何か何だかさっぱりわからなくなってしまいましたが、
教えていただいたとおりに入力したところ
完璧に私の希望に対応しておりました。
本当にありがとうございました。

したがって、ベストアンサーとさせていただきます。
他の方々も含め、私の為に時間を割いていただいた皆様方本当にありがとうございました。

回答No.7

某HN クロメート(Chromate)（@CoalTar）

名前・グループ名などの元データをコピペしておく

D2セル
=IF(C2="欠席","欠席",B2&RAND())
下へオートフィル
並び替えは D列のセル一つ選んで[昇順で並び替え]
これでランダムに並び変わる
欠席者は処理が面倒なので最後に持ってくる

G2セル
=IF(COLUMN(A1)>$L$1,"",IF($F2*$L$1-$L$1+COLUMN(A1)>$L$2,"",INDEX($A:$A,$F2*$L$1-$L$1+COLUMN(A1)+1)))
右へ下へオートフィル

L2セル
=COUNTA(A:A)-1-COUNTIF(C:C,"欠席")

お礼

画像まで貼っていただいてありがとうございます。
とても分かりやすくほぼ理想に近い形になりました。
ありがとうございました。

回答No.5

KURUMITO

回答No3です。名前の順序をランダムにしたいとのことですね。元のデータからランダムな名簿を作り、それを5列に並べることにした方法を述べます。
元のデータはNo3で示した通りとしてランダムに並べるための操作を作業列を使って行います。
D2セルには次の式を入力してF2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(A2="","",RAND())

次にG2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(D2="","",INDIRECT("A"&RANK(D2,D$2:D$100)+1))

次にH2セルには次の式に入力して下方にオートフィルドラッグします。

=IF(E2="","",INDIRECT("B"&RANK(E2,E$2:E$100)+1))

次にI2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(F2="","",INDIRECT("C"&RANK(F2,F$2:F$100)+1))

これでG列からI列にはA,B,Cグループでそれぞれの名前がランダムに並べられます。この名前を元に5グループへの並べ替えをするためK1セルには1グループ、L1セルには2グループ・・・・と5グループまで文字列を入力したのちに、K2セルには次の式を入力してO2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF((ROW(A1)-1)*5+COLUMN(A1)<=COUNTIF($G$2:$G$100,"?*"),INDEX($G:$G,COLUMN(A1)+1+(ROW(A1)-1)*5),IF((ROW(A1)-1)*5+COLUMN(A1)<=COUNTIF($G$2:$G$100,"?*")+COUNTIF($H$2:$H$100,"?*"),INDEX($H:$H,COLUMN(A1)+1+(ROW(A1)-1)*5-COUNTIF($G$2:$G$100,"?*")),IF((ROW(A1)-1)*5+COLUMN(A1)<=COUNTIF($G$2:$G$100,"?*")+COUNTIF($H$2:$H$100,"?*")+COUNTIF($I$2:$I$100,"?*"),INDEX($I:$I,COLUMN(A1)+1+(ROW(A1)-1)*5-COUNTIF($G$2:$G$100,"?*")-COUNTIF($H$2:$H$100,"?*")),"")))

これでK列からO列には5グループの名前が表示されることになります。

回答No.4

imogasi

＃２です。補足ありがとう。
下記は単純な割り当て方法なのですが、下記ではどうだろう。
グループからの人間をなるべく分散させるのを第１に考えている。
例　Ｓｈｅｅｔ１
グループでソート、Ａ、Ｂ，Ｃの順に集る。下記はソート後からの話。
A 田中
A 石田
A 大田
A 木村
A 水野
A 河合
B 鈴木
B 川崎
B 品川
B 河北
B 内田
B 戸田
B 内村
B 大山
C 田上
C 上田
C 今野
C 今川
C 木下
C 西田
C 今井
C 北村
C 東
Ｓｈｅｅｔ２
５グループの場合
Ａ２セルに　=IF((ROW()-1)+(COLUMN()-1)*5>23,"",INDEX(Sheet1!$A:$B,(ROW()-1)+(COLUMN()-1)*5,2))
と入れて、Ａ６まで５行で式複写。
Ａ２：Ａ６の式を右方向に式複写
結果
田中 河合 内田 上田 今井
石田 鈴木 戸田 今野 北村
大田 川崎 内村 今川 東
木村 品川 大山 木下
水野 河北 田上 西田
グループ記号で言えば
Ａ２に　=IF((ROW()-1)+(COLUMN()-1)*5>23,"",INDEX(Sheet1!$A:$B,(ROW()-1)+(COLUMN()-1)*5,1))ト入れて
上記と同じことをする。
質問に挙げられた例（結果の１例）と同じになる。
A A B C C
A B B C C
A B B C C
A B B C
A B C C
６グループにする場合はＳｈｅｅｔ３の
Ａ２に　=IF((ROW()-1)+(COLUMN()-1)*6>23,"",INDEX(Sheet1!$A:$A,(ROW()-1)+(COLUMN()-1)*6,1))
と入れて、６行縦に式を複写し、Ａ２：Ａ７の式を右方向に式複写する。
結果
A B B C
A B B C
A B C C
A B C C
A B C C
A B C
名前版の式は
=IF((ROW()-1)+(COLUMN()-1)*6>23,"",INDEX(Sheet1!$A:$B,(ROW()-1)+(COLUMN()-1)*6,2))
結果
田中 鈴木 内村 木下
石田 川崎 大山 西田
大田 品川 田上 今井
木村 河北 上田 北村
水野 内田 今野 東
河合 戸田 今川
Ａ列を明けたい場合は、Ｓｈｅｅｔ４のＢ２に
=IF((ROW()-1)+(COLUMN()-2)*5>23,"",INDEX(Sheet1!$A:$B,(ROW()-1)+(COLUMN()-2)*5,1))
結果B2:F6 A列は式が無く空白（グループ番号などを入れる）列に使う
A A B C C
A B B C C
A B B C C
A B B C
A B C C
ーー
欠席が混じる場合で出席者のみで考える場合は、出席（１）や欠席（２）のコードの列でソートして分離し、上に集った出席者のみのセル範囲で上記回答の方法を行うとどうだろう。

お礼

教えていただいた中でもっとも簡単な数式で
ほぼ理想に近い形になりました。
ありがとうございました

回答No.3

KURUMITO

例として次に述べますので必ず一度述べたとおりに表を作成して試験してください。その結果をご自分の表に適応することで問題が解決できるのではないかと思います。
A1セルにはAグループと入力したのちに、A2セルには例えばA1と入力し、下方にオートフィルドラッグします。下方にはA2,A3,A4・・・と表示されますね。適当な番号まで表示させます。
B1セルにはBグループと入力したのちに、B2セルにはB1と入力し下方にオートフィルドラッグします。
C1セルにはCグループと入力したのちに、C2セルにはC1と入力し下方にオートフィルドラッグします。
いずれも下方への表示は適当な行まで行います。A,B,C列では表示の行が勿論変わっていて問題ありません。
ただし、ここでは最大100行までの式になっていますが1000までの式に変えることもできます。
さて5グループに並べ替えるための操作ですがE１セルに1グループと入力したのちにI1セルまでオートフィルドラッグして5グループまでを表示させます。
その後にE2セルには次の式を入力したのちI2セルまで横方向にオートフィルドラッグしたのち下方にもオートフィルドラッグします。

=IF((ROW(A1)-1)*5+COLUMN(A1)<=COUNTA($A$2:$A$100),INDEX($A:$A,COLUMN(A1)+1+(ROW(A1)-1)*5),IF((ROW(A1)-1)*5+COLUMN(A1)<=COUNTA($A$2:$A$100)+COUNTA($B$2:$B$100),INDEX($B:$B,COLUMN(A1)+1+(ROW(A1)-1)*5-COUNTA($A$2:$A$100)),IF((ROW(A1)-1)*5+COLUMN(A1)<=COUNTA($A$2:$A$100)+COUNTA($B$2:$B$100)+COUNTA($C$2:$C$100),INDEX($C:$C,COLUMN(A1)+1+(ROW(A1)-1)*5-COUNTA($A$2:$A$100)-COUNTA($B$2:$B$100)),"")))

これでE列からI列にはお望みのグループ分けされた結果が表示されます。5グループ以外にする場合には上記の式で5の数値を変えることで良いでしょう。

お礼

詳しくご説明ありがとうございました。結果、以下のような
出力結果を得ることができました。
希望はこれに加え、Ａ1～Ａ9、Ｂ1～Ｂ11、Ｃ1～Ｃ14の間でランダムに並びかえたいというものでしたが、元データに乱数を与え、ソートすることで解決できそうです。ありがとうございました。

1グループ 2グループ 3グループ 4グループ 5グループ
Ａ1 Ａ2 Ａ3 Ａ4 Ａ5
Ａ6 Ａ7 Ａ8 Ａ9 Ｂ1
Ｂ2 Ｂ3 Ｂ4 Ｂ5 Ｂ6
Ｂ7 Ｂ8 Ｂ9 Ｂ10 Ｂ11
Ｃ1 Ｃ2 Ｃ3 Ｃ4 Ｃ5
Ｃ6 Ｃ7 Ｃ8 Ｃ9 Ｃ10
Ｃ11 Ｃ12 Ｃ13 Ｃ14

補足

乱数を与えようと思ったのですが、
上の数式を理解していないせいでどこかで
ずれが生じてしまい、いまひとつな感じになってしまいました；

元のデータを他の場所に残したまま、
ランダムの順で名前をＡ1～Ａ○、Ｂ1～Ｂ○、Ｃ1～Ｃ○
の位置にに入力させるにはどうすればよいでしょう？？

回答No.2

imogasi

シートのデータの様子も質問に書かないで質問してどうする。
ある列に、並べなおす「キー」はあるのか、造る（入力する）のか、人間が総合的に判断して分類コードなどを入力するのか、または何かこんなことではないのか、はっきりさせるため、この質問に補足を書くこと。
＞、Ａ、Ｂ、Ｃグループの人数は全てばらばら・・
こんなことは普通問題にならない。コンピュターで並べなおす場合は。
＞グループ数の変更等・・
所属するが変わった時にはシートのデータはどうなるのか。
コンピュターの並びを自動的に整えるには、それに相応しいデータが無いといけない。
こんなことはコンピュター処理の基礎。

お礼

失礼しました。
並び直すキーはランダム（乱数）を希望です。

Ａ　○○太郎
Ａ　○○花子
　　　・
　　　・
　　　・
Ｃ　○○二郎

以上のデータを保持したまま

１　Ａ　○○太郎
　　Ａ　○○三郎
　　Ｂ　○○四朗
　　Ｃ　○○二郎

２　Ａ　○○花子
　　Ｂ　○○○○
　　Ｂ　○○○○
　　Ｃ　○○○○

以上のように出力させるのが希望です

行程としてはＡグループのメンバーをランダムに並び変えて
１グループ2グループ３グループ・・・に配分

Ｂグループのメンバーをランダムに並び変えて、
Ａグループの最後の人が配分された次のグループから順に配分

Ｃグループのメンバーをランダムに並び変えて、
Ｂグループの最後の人が配分された次のグループから順に配分

のようなものを希望しております。
あと、例えば出席している人に1、
欠席している人に0というキーを与えた場合に
0を与えた人間だけ飛ばすことや、
作り直した後のグループの数を簡単に変えれたらいいなと思っています。
（Ａ，Ｂ，Ｃ→1、2、3、4　⇒　Ａ，Ｂ，Ｃ→1、2、3、4、5、）

回答No.1

-9L9-

単にリストを並べ替えるだけのことと思われますので、ソートすればいいだけだと思います。それで問題があるのなら、もっと詳しく書いてください。

ソートのやり方
http://www.eurus.dti.ne.jp/~yoneyama/Excel/sort.html
EXCELのバージョンによってソートのダイアログボックスが異なるので、とりあえず2003以前の古いバージョンでのやり方です。2007以降では画面が違いますが、考え方は同じで、項目が4つ以上に対応していますからより便利になっています。
ソートは基本中の基本なので、自分で調べてみてください。

お礼

ご回答ありがとうございます。
少々、説明が不十分でした。
分けた後の文字Ａの中身についてはランダムで
変更可能な状態にしたいのが希望です。
あと、元データの３グループもそのままデータとして残しておきたいです。