STUDY387

エクセルで仕事をもっと簡単にしよう

「ランダムな自動班分け」にお役立ちの関数

time 2014/10/11

「ランダムな自動班分け」にお役立ちの関数
※ エクセル2013で説明しています。

班分けって結構面倒ですよね。
手作業で名前を分けるのも面倒ですし、
名簿等の決まった順序を基準にすると
いつも同じ班分けになるので一工夫が必要ですしね。

そこで関数を使って、自動でランダムな班分けをしてもらいましょう!

名簿一覧を用意します。
まず、これをランダムに並び替えましょう。
excel-randamhan-01

ランダムな並び替えに使う関数は、RANDBETWEEN関数です。
適当に列を挿入し、「=RANDBETWEEN(1,名簿の人数)」と入力します。
今回は30名なので「=RANDBTWEEN(1,30)」です。
excel-randamhan-02

すると、数値がランダムに表示されます。
念のため値貼り付けをして、数値を確定します。
念のためなので、値貼り付けをしなくても大丈夫です。
excel-randamhan-03

そしたら、この数値に基づいて昇順(または降順)に並び替えをしてください。
これでランダムな並び替えの完了です!
excel-randamhan-04

次に、班分け表の作成です。
今回は3班に分けるので、下の班分け表を事前に作成しました。
名簿とは別のシートに作成しています。
excel-randamhan-05

名簿から班分け表に、関数を使って名前を自動で挿入します。
これに使用する関数が、INDEX関数、ROW関数、COLUMN関数です。
式は「=INDEX(名前の範囲,(ROW()-数値α)*班数+COLUMN()-数値β,1)」です。

下の例だと「=INDEX(名簿!$C$2:$C$31,(ROW()-3)*3+COLUMN(),1)」です。
excel-randamhan-06

では、式の内容を確認していきましょう。

まず、INDEX関数の確認です。
INDEX関数は「=INDEX(範囲,行番号,列番号)」という構成で、
「指定した範囲内の、行番号と列番号が交差するセルの値を返す」関数です。
excel-randamhan-07

そこでINDEXの範囲には、名簿の名前の列を範囲指定します。
これが「名簿!$C$2:$C$31」です。
excel-randamhan-08

次に行番号ですがこの説明は後にして、
列番号を先に説明しますね。
列番号は「1」です。これは、指定した範囲が1列だからです。

あとは班分け表のセルに対応して、行番号が1,2,3となれば、
名簿の名前が班分け表に表示されます。
excel-randamhan-09

班分け表のセルに対応して行番号を入力する式が、
(ROW()-3)*3+COLUMN()」の部分です。
一般化すると「(ROW()-数値α)*班数+COLUMN()-数値β」でしたね。

つまり、班分け表のセルの行番号(ROW関数)と列番号(COLUMN関数)を使って、
表示する範囲(名簿の名前)の行番号を算出しています。

考え方としては、「(ROW()-数値α)*班数+COLUMN()-数値β」のうち、
COLUMN()-数値β」で名前を横方向に並べる計算をし、
(ROW()-数値α)*班数」でINDEX関数の行番号の数値の増加分を計算をしています。
excel-randamhan-10

COLUMN()-数値β」から確認していきます。
班分けを一旦無視して、名簿の名前をA列から横並びで表示する場合、
=INDEX(名簿!$C$2:$C$31,COLUMN(),1)」になります。
excel-randamhan-11

もし、B列から横並びで表示したいなら、
=INDEX(名簿!$C$2:$C$31,COLUMN()-1,1)」になります。
B列の列番号(COLUMN関数)が2なので1を減算して、
INDEX関数の第2引数の行番号を「1」にしています。
excel-randamhan-12

数値βは減算した1を指します。
言い換えれば、開始列番号より1個少ない数値です。
開始列番号が3なら数値βは2、開始列番号が4なら数値βは3という具合です。

今回の班分け表はA列から横並びを開始しているので、
COLUMN()-数値β」は「COLUMN()-0」で、結局「COLUMN()」となります。

今度は「(ROW()-数値α)*班数」の説明です。
3班に分けているので、縦方向に班数の3単位で増えています
excel-randamhan-13

さらに詳しく見ると、
A列なら「3の倍数+1」、B列なら「3の倍数+2」、
C列なら「3の倍数+3」になっています。
ただし、開始行の3行目だけは3の倍数が全てゼロです。
excel-randamhan-14

3の倍数に加算する「+1、+2、+3」の部分は「+COLUMN()(-数値β)」が担当しているので、
(ROW()-数値α)*班数」で3の倍数を計算します。
excel-randamhan-15

3の倍数は、開始行の3行目は0、4行目が3、5行目が6になっています。
これを満たすには、「(ROW()-3)*3」となります。

ポイントは、開始行の行番号と同じ数値を減算する点です。
今回の例だと3行目から開始しているので、3になります。
数値αは、この「3」を指します。

結果、すべてを足し合わすと、
=INDEX(名簿!$C$2:$C$31,(ROW()-3)*3+COLUMN(),1)」という式になります。

ふぅ~。。。長い説明でしたが、理解して頂ける説明になっているでしょうか。
INDEX関数の説明は、文章がややこしくなって本当に難しいです。
毎回、死闘です(文章力ないだけです)。

分かりにくい点があれば教えて頂けると嬉しいです!

sponsored link



sponsored link

sponsored link

何はともあれOFFICEのことならマイクロソフト。意外とキャンペーンやっています。

コピー用紙あったかな?トナーもあったかな?事務用品はアクスルへGO!