Introduction
Ver. all 2013 2016 2019 365
エクセルで自動の班分け表を作成する方法です。
同じメンバーが重複しないように、また、グループ人数が均等になるように分ける必要がありますね。
最初に１度だけ使える簡単な方法を説明して、その後に何度も繰り返し班分けできる表を作成する方法を紹介します。
作業の流れは下のようになります。
- 全員の氏名データを貼り付ける
- そのデータに連続番号を振る
- 乱数を作成する
- 乱数に応じてメンバーに順位を付ける
- 並べ替えた順位に応じて氏名を表示する
- １班ごとのメンバーを別のセルに移動する
また、最後の「１班ごとのメンバーを別のセルに移動する」のを関数で対処する方法を最後に紹介します。少し複雑になります。
自動化を進めるほど難しくなるにゃ
１番簡単な方法
もし、あなたがExcel初心者で、急いでグループ分けしたいのであればこちらの方法が１番早いでしょう。
メンバーの一覧から手作業でセルを移動して作成する
下のような名簿があったとしたら、右側に必要に応じた班の一覧を作成しておいて、
一人ひとりのセルを選択して、
マウスで移動していく・・・。
次の名前も適当にランダムに移動して・・。
そして完成。
「え！もっと楽できないの？」
と思うかもしれませんが、メンバーの人数によっては、手作業で作成するのが１番早いかもしれません。
また、毎週同じような班分け作業が必要になるのであれば自動化するべきですが、年に１回程度であれば、やはり泥臭く見える手作業が効率が良いと思われます。
これから説明する方法ですが、ランダムな順番で名前を表示する途中のところでも
- RAND関数
- RANK.EQ関数
- VLOOKUP関数
という３つの関数を使用します。
また、RANK.EQ関数やVLOOKUP関数では、絶対参照の知識も必要になるため初心者の方には難しく感じるはずです。
さらに、その後の自動でグループを班分けして表示するところまでいくと、
- IF関数
- IFERROR関数
- INDEX関数
- ROW関数
- COLUMN関数
を追加で使用します。
また、関数の中に関数を入れ子にして使用するネストの知識も必要になります。
「関数の学習も兼ねている」「１年に何回も班分けする」という方は是非最後まで読んで欲しいのですが、急いでいる方は無理に自動化にこだわらない方が良いかもしれませんよ。
何度も使える班分け表を作成する方法
では、何度も使える班分け表を作成してみます。
使用する関数は、
- RAND関数
- RANK.EQ関数（旧RANK関数）
- VLOOKUP関数
最初の段階として、「B列」に名前を貼り付けると、「F列」に名前がランダムに表示されるようにしてみます。
それを手作業で右に移動する感じ。ここまで作ってみましょう。
最終的には、右側にランダムに班分けされるようにしますよ。
まず、手元にある名簿の一覧データの氏名を貼り付ける場所と、番号を振る場所を作成します。
「番号」は後で使用するので必ず必要です。元の名簿に出席番号のような番号が振ってある場合はそのまま貼り付けましょう。
無い場合は、連続番号を振ってしまいましょう。
数字が必要なので、社員IDなどアルファベットが入った番号は使えません。
今回は下のように氏名の代わりに「名前A」～「名前O」というようにデータを用意しました。
次に、氏名をランダムに並べるために、右側に乱数を表示します。
使用するのはRAND関数。
0以上で1より小さい乱数を返す関数です。
簡単に言うと、1より小さい、小数点の付いたランダムな数字を表示する関数ということ。
RAND関数・・・1より小さいランダムな数字を表示する
=RAND（）
- 引数（括弧の中身）は必要なし
乱数を表示するセルを選択して［関数の挿入］ボタンをクリック。
［関数の分類］を［すべて表示］か［数学/三角］に切り替えて「RAND」を選んで［OK］。
下の画面が表示されますが、そのまま［OK］をクリックします。
完成する関数は、
=RAND()
RAND関数は引数が必要ない関数なので、すぐに結果が表示されます。
1より小さいランダムな数字のはずです。
下にオートフィル機能を使用してコピーします。
これでランダムな数字が表示されました。
「RAND関数」は、オートフィル機能を使用したり、ブックを開くごとに再計算されて新たなランダム数字が作成されます。
作業の度に数字が切り替わりますが、気にせず進めましょう。
次に、今作成した乱数が大きい順に氏名を並べ替えします。
氏名を乱数で表示後、並べ替えることにより、同じデータ（氏名）が重複するのを防ぐことができます。
使用する関数はRANK.EQ関数、もしくはRANK関数でも構いません。
※以前から存在するRANK関数が、最近はRANK.EQ関数と名称が変わっています。
［関数の分類］を［統計］か［すべて表示］に切り替えて探しましょう。
RANK.EQ関数・・・指定したリスト範囲中で、順位を出す関数
=RANK.EQ（数値,参照,順序［省略可］）
- 数値・・・順位を出したい場所を指定
- 参照・・・順位を出す全体の比較範囲を指定
- 順序・・・「0」を入力すると降順、「1」を入力すると昇順で順位を出す（省略した場合は降順になる）
引数は、下のように設定します。
［順序］は空欄のままで構いません。
今回完成した関数は、
=RANK.EQ(C2,$C$2:$C$16)
［OK］を押すと乱数の大きい順で順位が表示されるので、オートフィル機能を使用してコピーします。
順位が表示されました。
※左の乱数が切り替わるごとに、順位も切り替わります。
では、表示された順位に応じて、「名簿」の氏名を右側に取り出しましょう。
これで、ランダムな氏名が上から並ぶことになります。
使用する関数はVLOOKUP関数。
［関数の分類］を［検索/行列］か［すべて表示］に切り替えて探しましょう。
VLOOKUP関数・・・他の表から対応データを探し出し、一致した行中のデータを求める関数
=VLOOKUP（検索値,範囲,列番号,検索の型［省略可］）
- 検索値・・・検索する値（一致させたい場所のセル参照や値、文字列）を指定
- 範囲・・・検索する（一致させる）相手のデータ全体を指定
- 列番号・・・「範囲」で指定したデータの、左から何列目と「検索値」を一致させるかを指定
- 検索の型・・・完全一致させたい場合は「FALSE」、近似値で一致させたい場合は「TRUE」を入力。省略した場合は「TRUE」になる。（FALSEは「0」、TRUEは「1」でも可）
ここでは、VLOOKUP関数の詳しい説明は省略します。使用したことが無い方は、下のリンク先をご覧ください。
今回は、下のように引数を設定します。
今回完成する関数は、下のようになります。
=VLOOKUP(D2,$A$2:$B$16,2,FALSE)
すると、「順位」と「番号」が一致した「名簿」の氏名が取り出されます。
下にオートフィル機能を使用してコピーします。
これで、「名簿」に貼り付けた氏名をランダムに並べることができました。
しかし、このままではいけません。何か作業をするごとにRAND関数が働いてしまい常に氏名が入れ替わってしまいます。
ということで、表示された氏名から数式を抜いた状態のデータを右側に貼り付けましょう。
まず、VLOOKUP関数で表示した氏名全体を選択して、
［コピー］して、
右のセルを選択。
［貼り付け］の下部分をクリックして、一覧から［値］を選びます。
中の計算式（関数）が削除され、見た目の氏名だけになりました。これでRAND関数の影響を受けなくなります。
後は、各グループの人数に応じてメンバーを並べていけば完成です。
今回は１班４名にしたいと思います。
とりあえず、右側に班表を作成しておきましょう。
４人分のセルを選択して、
選択した範囲の外枠にマウスを移動すると、マウスカーソルが矢印十字になります。
その状態で右側に引っ張ると、
データを移動できます。
同じように、繰り返しデータを選択して右側に移動・・・。
すると、下のように班分けが完成します。
この班分け表ですが、一度作成しておけば何度でも使用できます。
左の赤枠「名簿」の場所を入れ替えるだけで、右の青枠にランダムな氏名が並んでくれます。
しかし、問題が・・・。
注意すべきは、「順位」のRANK関数と「班名簿」のVLOOKUP関数の引数でのセル範囲です。
もし、メンバーの人数が異なると関数で指定するセル範囲を変更する必要があります。
説明を分かり易くするために、人数の変化に対応する説明は省略していました。
次は、その人数の変化への対応をしていきます。
班の人数変化への対応
では、ここまで作成した表を人数が変更されても問題ないようにしていきます。
現在の状態だと、
例えば15人より数が減ると、RANK関数やVLOOKUP関数の範囲が対応せず、エラーや「0」が表示されてしまいます。
ということで、人数が毎回入れ替わっても対応できるように関数を変更していきます。
追加する関数は下の２つ。
- IF関数
- IFERROR関数
まず、名簿の最大人数だけ決めてしまいましょう。今回はどんなに多くても班分けする人数は100人以内に収まることにします。※少し多めの人数分にしておくのがお勧め。
まず、１番左の「番号」を100番まで振っておきます。
まず、RAND関数で乱数を表示した場所を人数の変化に対応させます。
左の氏名の場所が空欄の場合は、空欄のままにして乱数が表示されないように設定します。
使用するのはIF関数です。
=RAND()
の式を、
=IF(B2=””,””,RAND())
に変更します。
日本語にすると、
「もし、名簿のセルが空欄だったら空欄のまま、氏名が入っていれば乱数を表示」
という意味になります。
修正したら、その式を下までコピー。
続いて順位を出す場所も変更します。以下の２点の修正が必要です。
- 順位を算出する範囲を100番までに変更
- ［参照］場所が空欄でもエラーが表示されないようにする
そのままの状態だと、下のように乱数が存在しない場所にはエラーが表示されてしまうのです。
ということで、乱数が表示されていない場合は空欄になるように、
=RANK.EQ(C2,$C$2:$C$16)
の式を
=IFERROR(RANK.EQ(C2,$C$2:$C$101),””)
に変更します。
RANK.EQ関数の順位を算出する範囲を100名分の範囲まで広げて、IFERROR関数でエラー時には空欄表示になるようにしています。
日本語にすると、
「もし乱数のセルがエラーでなければ順位を表示、エラーだったら空欄のまま」
という意味になります。
IFERROR関数に関しての説明は下のリンク先をご覧ください。
修正した式を、下にコピーします。
最後に、すぐ右の「班名簿」の場所も続けてエラー対応します。
=VLOOKUP(D2,$A$2:$B$16,2,FALSE)
の式を
=IFERROR(VLOOKUP(D2,$A$2:$B$101,2,FALSE),””)
に変更します。
やり方はさっきと同じ。IFERROR関数で作成していた式を囲んであげます。
修正した式を下までコピーします。
これで完成です。下のように番号だけ表示されてる場所に、氏名だけ貼り付けると、
人数に応じて、右側のランダムな氏名「班名簿」が表示されるようになりました。
作業用の列を非表示にして体裁を整えるとすっきり使用することができますよ。
さて、最後が結局手作業での移動になってしまいました。
「班名簿」をコピーして、右の「確定名簿」に［値］として貼り付けてから、右側へマウスで移動する作業です。
自動化できると便利そうですね。
ということで、説明をさらに追加していきます。難しくなっていくので時間に余裕のある時にご覧ください。
自動で班分け表を並べる方法
完成する予定の表は下のようになります。
左側に氏名を貼り付けると、「G2」に入力した数値に応じて、右側にランダムに氏名が表示されるようにします。
これまでに作成した表の形を、
下のように変更します。
変更といっても、ほとんど形を変えるだけです。
具体的には、「A列」～「E列」は式も消さずにそのまま使用します。
「確定名簿」の「F列」は使用しないので列ごと削除して、代わりに「G２」に「１班の人数」を入力する場所を用意します。
班の数は今回8班にしてみました。これは後で変更可能なので適当な数で用意する感じです。
※ついでに見出しが全て１行目に来るようにしました。
新しく追加する関数は下の３つです。
- INDEX関数
- ROW関数
- COLUMN関数
まず、説明を分かり易くするためにサンプルデータを貼り付けておきます。
氏名は「24人」貼り付けました。これを「3人」ごとの班に分けてみることにして、「G2」には「3」と入力しておきます。
これまでの式のおかげで、「E列」には自動でランダムな氏名が表示されます。
これが、「G3」の数字に応じて、自動で右側に並んでくれればOKということになります。
ということで、肝心の式は、「I2」に作成することになります。
作成後、その式を下と右にコピーすれば完成です。
では行きましょう。
最終的な完成式から説明すると式が長すぎて混乱すると思いますので、順を追って説明します。
まず、「E列」に表示された氏名が、「G2」の１班ごとの人数に応じて順番に並ぶようする式です。
=INDEX($B$2:$B$101,ROW()-1+$G$2*(COLUMN()-9),1)
上のような式になるのですが、順番に説明していきます。
まず、INDEX関数を使用します。
ここでは、使用する関数そのものの詳しい説明は避けます。
詳しく知りたい方は下のリンク先を参照してください。
INDEX関数を簡単に言うと、指定した行列が交差した場所のセル内容を取り出す関数ということになります。
INDEX関数・・・指定した範囲からセルの値を取り出す。
=INDEX（配列,行番号,[列番号]）
- 配列・・・セル範囲、または配列定数を指定（範囲が１行もしくは１列の場合、それぞれ行番号、列番号を省略できる）
- 行番号・・・配列の中にあり、値を返す行を数値で指定
- 列番号・・・配列の中にあり、値を返す列を数値で指定［省略可能］
下の画像の番号のように「班名簿」から氏名を取り出したいのですが、
単純に、１行目の氏名だけ取り出したいのであれば、次の式になります。
=INDEX($E$2:$E$101,1,1)
「班名簿」全体の範囲の「１行１列目」を抜き出すという意味です。
ここでのポイントとしては、 下で青文字にした場所は変更する必要は無いということです。
=INDEX($E$2:$E$101,1,1)
常に、「班名簿」全体を参照するべきですし、そもそも１列しか存在しないため、最後の引数も「1」になるのが当たり前。
そうなると行である、２番目の引数の「1」を何とかする必要がありますね。
下に移動した時は「2」「3」と増えて欲しいですし、右の「J列」では「4」から始まり、「5」「6」と増えてまらわないと困ります。
=INDEX($E$2:$E$101,1,1)
その問題となる２番目の引数は下のようになります。
ROW()-1+$G$2*(COLUMN()-9)
うーーーん。何だか複雑。
ここを理解すれば、ここでのお話の８割は終了です。
まず、ROW関数とCOLUMN関数です。
それぞれ下のリンク先に説明があります。
簡単に説明するとそれぞれ下のような機能があります。
- ROW関数・・・指定したセルの行番号を取得する
- COLUMN関数・・・指定したセルの列番号を取得する
また、それぞれ引数を指定しない（括弧の中を空欄にする）ことで、関数を入力した自分の場所の行列番号を取得することができます。
- ROW()・・・関数を入力した場所の行番号を取得する
- COLUMN()・・・関数を入力した場所の列番号を取得する
例えば、下のようにROW関数を入力すると、
入力したセルは２行目なので、「2」と表示されます。
下のようにCOLUMN関数を入力すると、
入力したセルは９列目なので、「9」と表示されます。
これを踏まえて
ROW()-1+$G$2*(COLUMN()-9)
の式を見直します。
まず、前半の「ROW()-1」の部分ですが、名簿の場所は１行下がった場所からスタートしているので、「-1」する必要が出てきます。
もう少し詳しく説明すると、
INDEX関数は、最初の引数で指定した範囲内の〇行〇列の場所を取りだします。
INDEX（ 範囲 , 行番号 , 列番号 ）
つまり、今回の場合は、取り出したい名前のデータは２行目ではなく１行目になります。
そのため ROW() をそのまま使用すると、結果は「2」になってしまうので、「-1」して「1」行目を取得するようにします。
では、取り出す行の指定を ROW()-1 にして、
=INDEX($E$2:$E$101,ROW()-1,1)
その式を下と右にコピーすると、下のような結果になります。
「班名簿」の最初の名前から順番に取り出されていますね。
では、次の段階です。
今回の場合、１班の人数を「３」としているので４人目からは次の列に表示する必要があります。
続けて「5」「6」と3人分表示して、さらに次の列です。
それを可能にしているのが下の赤文字部分です。今作成した ROW()-1 に式を追加します。
=INDEX($E$2:$E$101,ROW()-1+$G$2*(COLUMN()-9),1)
何番目の行を表示すれば良いかまとめたのが下の画像です。
数字を眺めていると何となく見えてくると思います。
「J列」は「4」「5」「6」。これは、「I列」に3を足したものです。
「K列」は「7」「8」「9」。これは「I列」に3×2を足したものです。
「L列」は「10」「11」「12」。これは「I列」に3×3を足したものです。
どうやら、最初の列「I列」の値に3の倍数（G2の値の倍数）を足せば実現できそうです。
「G2」はそのままセル参照を使うとして、「G2×〇」の後ろの部分はどうするか・・・。
右に式をコピーするごとに「1」ずつ増える必要があります。
「列が移動すると数値が変わる」ようにしたいのですが、ここで活躍するのがCOLUMN関数。
COLUMN関数は入力された式の列番号を取得するのでした。
右に移動した式は、前の列より「+1」された結果となります。
これを利用します。
では、
I列+G2×〇
の式を下のようにすれば良いのでしょうか。
ROW()-1+G2*COLUMN()
これではまだ困ります。
COLUMN関数を「 COLUMN() 」のまま使用すると、
「I列」が「9列目」なので、今回の場合は下のように「9」「10」「11」～となってしまいます。
「I列」は「0」からスタートするようにして「J列」から「1」となるようにしなければなりません。
これは、単純に COLUMN() から「9」引き算することで対応しましょう。
COLUMN()-9
これで、「I列」は「9-9」で「0」になり、「J列」は「10-9」で「1」になります。
また、手前の掛け算より早く計算する必要があるので、括弧で括っておきましょう。
これで、
ROW()-1+G2*(COLUMN()-9)
さらに、「G2」の「1班ごとの人数」のセルは後で式をコピーすることを考えると絶対参照にしておく必要がありますね。
ROW()-1+$G$2*(COLUMN()-9)
これで行番号が下の画像のように順番に取り出されるようになりました。
実際に「I2」に
=ROW()-1+$G$2*(COLUMN()-9)
の式を入力すると、下の結果が表示されます。
では、この式をINDEX関数の「行」の引数の場所に入れてみましょう。
式は下のようになります。赤文字の部分が長々と説明した行を取り出す部分です。
=INDEX($E$2:$E$101,ROW()-1+$G$2*(COLUMN()-9),1)
結果は下のようになります。
ちゃんと３人ずつ班のメンバーが取り出されています。
これで完成！
と言いたいところですが、余計に関数が入力されている場所までデータが入ってしまいます。
毎回その場所の式を消すという作業も面倒ですね。
これも何とかしてしまいましょう。
具体的に言うと、
もし「E列」の「班名簿」にデータが入っている場合は、対応したデータを表示して、データが無い場合は空欄にするという式を作成します。
「もし」とくればIF関数ですね。
下の画像をご覧ください。赤丸で囲んだ場所までがデータを表示したい箇所です。
つまり、G2の倍数以下の場合まではデータを表示したいということです。
G2の倍数を式にすると「G2×1」「G2×2」「G2×3」となります。
「G2×〇」の後ろの〇の場所ですが、右に移動するたびに1ずつ増やしたい・・・。
そう。先ほど使用したCOLUMN関数がまた活躍します。
先ほどは「0」からスタートしたかったので COLUMN()-9 でしたが、
今回の場合は「1」からスタートしたいので、
COLUMN()-8
とすれば「1」となります。
ここまでの説明を踏まえて、
「もし、その場所で取得する行数値がG2の倍数以下であれば、対応したデータを表示して、それ以外は空欄にする」
という式を作成すると下のようになります。
=IF(ROW()-1+$G$2*(COLUMN()-9)<=$G$2*(COLUMN()-8),INDEX($E$2:$E$101,ROW()-1+$G$2*(COLUMN()-9),1),””)
なんだか長い式になりました。
部分部分を色分けすると、
「もし、その場所で取得する行数値がG2の倍数以下であれば、
=IF(ROW()-1+$G$2*(COLUMN()-9)<=$G$2*(COLUMN()-8),INDEX($E$2:$E$101,ROW()-1+$G$2*(COLUMN()-9),1),””)
対応したデータを表示して、
=IF(ROW()-1+$G$2*(COLUMN()-9)<=$G$2*(COLUMN()-8),INDEX($E$2:$E$101,ROW()-1+$G$2*(COLUMN()-9),1),””)
それ以外は空欄にする」
=IF(ROW()-1+$G$2*(COLUMN()-9)<=$G$2*(COLUMN()-8),INDEX($E$2:$E$101,ROW()-1+$G$2*(COLUMN()-9),1),"")
という感じ。
これで完成です。その式を全体にコピーしておきましょう。
１班の人数に応じた場所だけ氏名が表示されます。
実際に使用する際は、作業用の「C列」「D列」を非表示にすれば良いですね。
41人を6人ずつの班に分けてみました。
まとめ
とても長い文章になってしまいました。
長いと言いましても、絶対参照やネストなどの説明には触れていませんし、それぞれの関数に関する詳しい説明も省いています。
それぞれの説明のリンクを貼っておきますので、必要になったら確認してください。
- 関数を入れ子で使用する（ネスト）
- RANK関数と絶対参照
- VLOOKUP関数
- IF関数
- INDEX関数
- ROW関数
- COLUMN関数
以上です。
一度作成しておけば便利に使える表になりますが、エクセルが得意でない場合は、最初に説明したように手作業で作成した方が効率が良いかもしれません。
しかし、ROW関数やCOLUMN関数の活用など他で使える知識もありますので、エクセルの学習のつもりで作成するのも良いかもしれませんね。
途中で眠くなったにゃ・・・