【Excel】人数が均等になるようにランダムにチーム分けを行い、メンバーリストを表示させる方法（マクロを使用しない）

Excelでは、関数を組み合わせることで、ランダムにチーム分けを行い、その結果をリストとして表示することができます。今回はマクロを使用せずに作成するので、比較的簡単で、負担の大きい処理も伴いません。

使用する関数

今回は複数の関数を組み合わせてランダムなチーム分けを実現したいと思います。

RAND関数

RAND関数とは、0以上1未満の乱数を生成する関数です。この乱数を用いてランダムなチーム分けを行いたいと思います。

関数	役割
RAND()	引数なしで使用し、ワークシートが再計算されるたびに0以上1未満の乱数が生成される。

RANK.EQ関数

RANK.EQ関数とは、指定した数値が範囲の中で何番目の大きさかの順位を表示させることができる関数です。この関数を用いて便宜的に各メンバーの順位付けを行い、その順位に基づきチーム分けを行います。

関数	役割
RANK.EQ(数値,参照,[順序])	指定した「数値」が参照した範囲内で何番目の大きさかを示す。 順序では、「0」を指定すると降順（大きい順）、「1」を指定すると昇順（小さい順）の順位を示す。

MOD関数

MOD関数は、ある数値を別の数値で割ったときの余りを算出する関数です。

関数	役割
MOD(数値,除数)	指定した「数値」を指定した「除数」で割った余りを表示する。

「順位÷チーム数」で算出される余りを用いてチームを割り当てます。たとえば15人を5チームに割る場合、それぞれの順位を5で割ると、余りは0~4の5通りになります。その余りの数に応じてチームを割り当てていきます。

順位	計算式	余り	チーム
1	1÷5	1	チームA
2	2÷5	2	チームB
3	3÷5	3	チームC
4	4÷5	4	チームD
5	5÷5	0	チームE

CHOOSE関数

CHOOSE関数とは、指定した番号に該当するセルの値を表示する関数です。MOD関数で算出された余りの数に基づいて、チームを割り当てる際に使用します。

関数	役割
CHOOSE(インデックス,値1,[値2],…)	「インデックス」で指定した番号に該当するセルの値を表示する。 インデックスが「1」の場合「値1」、インデックスが「2」の場合「値2」が表示される。

FILTER関数

FILTER関数は、特定の条件に該当するデータのみを抽出する関数です。

関数	役割
FILTER(配列,含む)	「含む」で絞り込みたい条件を指定し、「配列」のデータを表示する。

人数が均等になるようにランダムにチーム分けを行う方法

まず、以下のようにチーム分けするメンバーのリストと、「乱数」「順位」「順位/チーム数の余り」「チーム」という列を作成しておきます。また、右側にはチームのメンバーリストを表示させるための枠も作成しておきます。

まず、RAND関数を用いて各メンバーに乱数を表示させていきます。セルC3に以下のように入力します。

=RAND()

そうすると以下のように0以上1未満の乱数が表示されます。この数字はワークシートが再計算されるたびに更新されます。

セルC3の数式を下にコピーすれば、各メンバーの乱数が表示されます。

次にRANK.EQ関数を用いて順位付けを行います。セルD3に以下のように数式を入力します。

=RANK.EQ(C3,$C$3:$C$17)

数式	役割
RANK.EQ(C3	セルC3の順位を算出する。
$C$3:$C$17	順位はセルC3~C17の中で何番目かを算出する。

そうすると以下のように、乱数の大きさに応じた順位が表示されます。

セルD3の数式を下にコピーすれば、乱数の値に応じた順位が表示されます。この順位もワークシートが再計算されるたびに変わっていきます。

次にMOD関数により、「順位÷チーム数」の余りを算出します。チーム数はあらかじめ別の場所に記載しておくことをおすすめします。今回はセルI1にチーム数「5」を記載しています。

セルE3には以下の数式を入力します。「除数」となるチーム数は絶対参照で指定します。

=MOD(D3,$I$1)

そうすると以下のように「順位÷チーム数」の余りが表示されます。

セルE3の数式を下にコピーすれば、すべてのメンバーの「順位÷チーム数」の余りが表示されます。チーム数は5なので、「0~4」のいずれかが表示されているはずです。

最後にCHOOSE関数を使ってチームを割り当てていきます。この際、チーム名のリストを用意しておけば、それを参照することができます。今回は、右側にチーム名のリストを用意しているので、それを活用します。

セルF3に以下のように数式を入力します。ここで注意が必要なのは、CHOOSE関数の「インデックス」が「余り+1」となっていることです。CHOOSE関数は「1、2、3…」と番号が割り当てられるため、余り0を便宜的に「1」とするため「+1」をしています。他の余りの数値についても「+1」をします。

=CHOOSE(E3+1,$H$2,$I$2,$J$2,$K$2,$L$2)

数式	役割
CHOOSE(E3+1	セルE3+1を番号として指定。
$H$2,$I$2,$J$2,$K$2,$L$2	以下のように番号を割り当てる。 1：セルH2＝チームA 2：セルI2＝チームB 3：セルJ2＝チームC 4：セルK2＝チームD 5：セルL2＝チームE

そうすると以下のようにチームの割り当てが表示されます。

セルF3の数式を下にコピーすれば、各メンバーがチームA~Eに割り当てられます。

これでランダムのチーム分けは完了です。「F9」キーを押して、ワークシートを再計算すれば、チーム分けを変更することができます。

また、チーム数を「4」に変更すれば、チームA~Dの4チームに割り当てられます。

チーム数を増やす場合は、CHOOSE関数に新たにチームF、チームG…というリストを追加する必要があります。そのため、あらかじめ想定される最大チーム数で数式を入力しておくことをおすすめします。

メンバーリストを表示させる方法

次に、FILTER関数を使ってランダムに割り当てられたチームに基づき、右側にチームのメンバーリストを作成します。

セルH3に以下の数式を入力します。

=FILTER($B$3:$B$17,$F$3:$F$17=H$2)

数式	役割
FILTER($B$3:$B$17	条件を満たすセルB3~B17の値を表示させる。今回は「名前」を表示させる。
$F$3:$F$17=H$2	条件は、セルF3~F17でH2の値に合致すること。今回は「チーム」が「チームA」であること。 チームB~Eも同じように条件を設定するため、セルH2は行だけ絶対参照とする。

そうすると以下のようにチームAのメンバーが表示されます。FILTER関数はスピル関数と呼ばれ、複数の結果を同時に返す関数です。そのため、メンバーリストが一気に表示されます。

セルH3の数式を右側にコピーすれば、各チームのメンバーリストが表示されます。

また、チーム数を「4」に減らすときれいには割り切れませんが、4チームのチーム分けも可能です。

少し手間のかかる作業ですが、一度枠組みを作成すれば、色々なチーム分けに活用することができます。