うるさい老人を有無を言わせず仕分けるには？

質問

老人同好会でグランドゴルフを楽しんでおります。集まった参加者の組み分けに苦労しています。MSエクセル関数（例えばRANDBETWEEN関数）などを使って簡便迅速に参加者を組み分けする方法を教えてください。例として1４人の参加者各人が順次クリックして５、5，4人の3組に仕分けられるようにしたいのですがよろしくお願いします。

回答No.2

keithin · ベストアンサー

乱数を使う方法の難点の一つは、そのエクセルをちょっと他の事に使うと、今までの順番がぱーっと消えて新しい順番に並べ変わってしまう点です。
折角「よし今回はこの組み合わせで行こう」と思っても、うっかりエクセルに触った瞬間、新しい組み合わせに変わってしまうという事ですね。

【作り方】１４人を５，５，４人に組み分ける

【準備】必ず行う事
ご利用のエクセルのバージョンが不明ですが、エクセルのオプションで「反復計算」のチェックを入れておきます

【添付図】
Ａ１に「乱数」と記入
Ａ２に
=RAND()
を記入、以下コピー

Ｂ１に「データの入力規則」で種類を「リスト」に変え、下の空欄に
組分け,固定
と記入
Ｂ１を選択して▼から「組分け」が選べることを確認しておく

Ｂ２に
=IF($B$1="組分け",MATCH(RANK(A2,A:A),{1,6,11},1)+ROW()/1000,B2)
と記入、以下コピー

Ｂ列を選び、ホームタブ（またはツールバー）の「カンマ区切り」書式（カンマボタン）を押して置く

Ｃ１に「名前」と記入
Ｃ２以下に参加者名を羅列

Ｅ１、Ｆ１、Ｇ１にグループ名を記入
Ｅ２に
=VLOOKUP(SMALL(B:B,ROW(E1)),B:C,2,FALSE)
Ｆ２に
=VLOOKUP(SMALL(B:B,ROW(F1)+5),B:C,2,FALSE)
Ｇ２に
=VLOOKUP(SMALL(B:B,ROW(G1)+10),B:C,2,FALSE)
と記入、以下コピー

【使い方】
Ｆ９を押したり、エクセルを何か使うと、メンバーが次々入れ替わります
Ｂ１を選択、▼から「固定」を選ぶとそれで固定します
「組分け」に変更すると（「組分け」を再度選ぶと）、また組分けが変わります。

＃名前一覧の順にグループに現れるので、誰がどの組か探し易くしてあります。

＃補足
言わずもがなですが、Ａ列は非表示にして見え無くしておいて構いません。
数式を簡単にするため「５，５，４」は固定にしてありますが、実際にアナタがヤリタイ事に応じて適切に応用してください。

お礼

keithin　様　　　早速のご回答ありがとうございました。各人クリックで順次決めるというこちらからの要望よりはF9でマクロ実行一発という方法が有無を言わさないやり方に合致している気がします。又、参加人員が変わったり、組み合わせ人数を変更したりする場合、keithin様の方式がより迅速に対応できます。Best　Answerとして貴案を採用させて戴きます。

回答No.3

tsubuyuki

なるほど、面白そうなので私もちょっと参加させてくださいませ。

各人がクリックではないですけれど＾＾；

まず・・
Ａ列に氏名を用意します。１４人分ですね。

次に、１～１４までの重複しない乱数を用意しましょう。
添付図では便宜上、同じシートにおいていますが、
もちろん、他のシートにおいてもＯＫです。

Ｇ列にＲＡＮＤ関数を使って乱数を発生させます。
　　=RAND()
続いて、Ｈ列にＲＡＮＫ関数を使って順位をつけてやりましょう。
　　=RANK(G2,$G$2:$G$15)
これで、Ｈ列に重複しない１～１４の数字が入ります。

で、この数字を基にＢ列にチーム分けをしていきます。
やり方は様々です。

ＩＦ関数だけで
　　=IF(H2<5,"いぬ",IF(H2<10,"さる","きじ"))
でももちろんいけますし、
ＶＬＯＯＫＵＰを使って（別表が必要ですが）
　　=VLOOKUP(H2,$J$2:$K$4,2)
でもいいでしょう。
あるいはよりランダムぽく見せるためにＭＯＤ関数で
　　=MOD(H2,3)
として「３で割った時の余り」を求め、それを基にしても良いでしょう。

まだまだありそうです。

ちなみに、Ｆ９ボタンで再計算しますので、ボタン一発で組み替えもできます。
このシートの文字を大きくして、画面いっぱいにしたら面白そうかなぁ・・と。
Ｆ９ボタンを押しっぱなしにしている間、画面が・・・・・
おっと、ここから先はやってのお楽しみです（笑）。

で、確定次第、コピー→数値貼り付けしてしまえば動かなく出来ます。

ご希望の「各人が順次クリック」と言うやり方ではないですが、
「クリックする順番が！」と言わせない一つの手法として、
とりあえず、参考までにどうぞ＾＾

お礼

tsubuyuki　様 早速のご回答ありがとうございました。非常に参考になり助かりました。keithin様の方式と会わせて状況の変化に対応できるよう工夫します。

回答No.1

tom04

こんばんは！
面白そうなのでちょっとやってみました。

クリックではなくダブルクリックにしています。

↓の画像のような表を作成しておきます。
A2～C6（実際はC5まで）にF列の氏名が入るようにしています。

E2セルを
=RAND()
としてオートフィルでE15セルまでコピーしておきます。
（最終的にはE列は非表示にした方がよいと思います）

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてみてください。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'この行から
If Application.Intersect(Target, Range("F2:F15")) Is Nothing Then Exit Sub
Dim i As Long
Dim myArea As Range
Set myArea = Range("A2:C6")

Cancel = True
Application.Calculation = xlManual
i = WorksheetFunction.Rank(Target.Offset(, -1), Range("E2:E15"))
myArea(i) = Target
Target.Font.ColorIndex = 2
If WorksheetFunction.CountA(myArea) = WorksheetFunction.CountA(Range("F2:F25")) Then
Application.Calculation = xlAutomatic
End If
End Sub

Sub リセット()
Range("A2:C6").ClearContents
Range("F2:F15").Font.ColorIndex = xlAutomatic
End Sub 'この行まで

F列セル（本人の氏名セル）をダブルクリックしてもらいます。
そうするとA2～C5セルのどこかにその氏名が表示され、F列フォントが「白」になります。
（全員がダブルクリックするまで、再計算はしないようにしています）

※　リセットの操作も必要だと思いますので、別マクロを一緒に乗せています。
リセットの場合は
Alt+F8キー　→　マクロ　→　マクロ実行
の操作をしたのちに、F9キーを押下！
これで再計算されますので、どこに配置されるかわかりません。

こんなんではどうでしょうか？m(_ _)m

お礼

tom04　様　　早速のご回答ありがとうございました。VBAのコードまでご提示くださっての解説非常に参考になりました。３つのご回答の中こちらの要望にもっとも合致した内容として評価させていただきます。ありがとうございました。