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

Question

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

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

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

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

KURUMITO · Accepted Answer

回答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,"?*")),"")))

CoalTar · Answer

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

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,"欠席")

KURUMITO · Answer

回答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グループの名前が表示されることになります。

imogasi · Answer

＃２です。補足ありがとう。
下記は単純な割り当て方法なのですが、下記ではどうだろう。
グループからの人間をなるべく分散させるのを第１に考えている。
例　Ｓｈｅｅｔ１
グループでソート、Ａ、Ｂ，Ｃの順に集る。下記はソート後からの話。
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))ト入れて
上記と同じことをする。
質問に挙げられた例（結果の１例）と同じになる。
AABCC
ABBCC
ABBCC
ABBC
ABCC
６グループにする場合はＳｈｅｅｔ３の
Ａ２に　=IF((ROW()-1)+(COLUMN()-1)*6>23,"",INDEX(Sheet1!$A:$A,(ROW()-1)+(COLUMN()-1)*6,1))
と入れて、６行縦に式を複写し、Ａ２：Ａ７の式を右方向に式複写する。
結果
ABBC
ABBC
ABCC
ABCC
ABCC
ABC
名前版の式は
=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列は式が無く空白（グループ番号などを入れる）列に使う
AABCC
ABBCC
ABBCC
ABBC
ABCC
ーー
欠席が混じる場合で出席者のみで考える場合は、出席（１）や欠席（２）のコードの列でソートして分離し、上に集った出席者のみのセル範囲で上記回答の方法を行うとどうだろう。

KURUMITO · Answer

例として次に述べますので必ず一度述べたとおりに表を作成して試験してください。その結果をご自分の表に適応することで問題が解決できるのではないかと思います。
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の数値を変えることで良いでしょう。

imogasi · Answer

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

-9L9- · Answer

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

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

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

回答No3及び5です。

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

回答No3です。

＃２です。

例として次に述べますので必ず一度述べたとおりに表を作成して試験してください。

この回答への補足

シートのデータの様子も質問に書かないで質問してどうする。

単にリストを並べ替えるだけのことと思われますので、ソートすればいいだけだと思います。

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング