【エクセル 関数 マクロ】連番のグループ割当

質問

グループをランダムに決めるため、
添付画像のように　B列に名前をずらーっと入れます。
※実際は１０００人くらいいます

これに対して、A列にて　１～１０　個単位で
１～∞　までの連番を組みたいと思ってます。

つまり、
1
1
1
1
2
2
3
3
3
3
3
4
4
4
5
5
5
5
5
5
6
7
7
・
・
・

みたいに、１～１０個ずつランダムにグループ分けされた連番を入れたいのです。

グループ数は予め固定しておらず、
１グループ辺りの人数も１～１０でランダムにしたいので、
別のセルでグループを設定して・・・という方法ではなく、
添付画像のようにB列に氏名を並べたままやりたいと思っています。

ただし、B列に１，０００人ならA列も１，０００行目まででばっちり
合わせなくてもいいです。　１，００１行目から下は無視すればよいので・・・

なお、他のセルで違う計算をしてから
A列に反映させる、というやり方なら大歓迎です。
ですから、他のセルで
randbetween(1,10）を作って、それを活用するやり方でもOKです。

マクロでも難しいでしょうか？

宜しくお願い致します。

回答No.2

keithin · ベストアンサー

Ｅ１セルにグループ番号と記入する
Ｆ１セルにグループ人数と記入する
Ｇ１セルに出現順と記入する
Ｅ２以下に１，２，３…と、平均してだいたい２００行ぐらいまで数字を列記する
Ｆ２に
=RANDBETWEEN(1,10)
と記入し、下向けにコピー貼り付ける
Ｇ２に
=IF(SUM(F1:G1),SUM(F1:G1),1)
と記入、リスト下端までコピー。この下端の値が必要な人数に達していることを確認。

で。
Ａ２に
=MATCH(ROW(A1),G:G,1)-1
と記入、下向けにコピー貼り付ける。

補足

ありがとうございます！

早速試しましたが、完璧にできました！
すごいです！

でも、どれだけ数式とにらめっこしても、
仕組みが分かりません。。。

どういう理屈で組んでいるのでしょうか・・・？

回答No.1

mt2008

どのくらいの頻度でグループを変えていいのか解らなかったので適当にやってます。あまり頻繁にグループを変えたくなければ式中の0.5を１未満のもう少し大きい値にしてください。

A2に1
A3に↓を入れて下にコピー
=A2+(0.5<((COUNTIF(A$2:A2,A2)>9)+RAND()))

乱数を使っていますので計算するたびにグループが変わります。
何度か計算させてこれで良いと思ったら、A列をコピーしてそのままA列に値のみ貼り付けを行って確定してください。

お礼

ありがとうございます！

試してみましたが、最初の数行だけがランダムにならず、１行にしかなりませんでした。
それ以降はおっけぃでした！

参考にさせて頂きます♪