STUDY387

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

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

time 2014/10/15

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

先日書いた『ランダムな自動班分けにお役立ちの関数』の続編です。

前回ご紹介した方法は1回きりの作成方法でした。
しかし、自動班分けの方法を知りたい人は、たぶん班分けをする機会が何度もある人だと思います。

そこで、前回ご紹介した方法をアレンジて、ランダムな自動班分けをフォーマット化する方法を続編でご紹介したいと思います。
excel-randamuhan02-01

なお、以降は『ランダムな自動班分けにお役立ちの関数』の記事を理解していることを前提に話を進めますので、よろしくお願いいたします。

sponsored link

名簿のフォーマット化

名簿のフォーマット化のポイントは、
RANDBETWEEN関数で使用する名簿の人数を入力するセルを作成する点です。

そして、RANDBETWEEN関数の式を
=RANDBETWEEN(1,名簿の人数を入力するセル番地)」にしてください。
下の例だと「=RANDBETWEEN(1,$E$3)」になります。
excel-randamuhan02-02

これで名簿のフォーマット化が完成です。

使い方は、名前の部分に班分けする人達の名前を貼り付けてください。
人数に応じて、通し番号や並替用の数式をコピー/削除をしてください。

名簿の人数をセルに入力すると、RANDBETEEN関数がランダムな数値を表示します。
あとは、並替用を基準にして昇順(または降順)に並び替えをすればOKです。

班分け表のフォーマット化

班分け表のフォーマット化のポイントは、
班の個数を入力するセルを作成する点と、
名前を表示しないセルにゼロやエラー表示が出ないように関数を組む点です。
excel-randamuhan02-03

なお、今回フォーマット化する班分け表は、最大6の班分けに対応していますが、必要に応じて変更してください。
横並びで作成すれば、いくつの班分けでもOKです。

班分け表に入力した式を紹介します。
=IF(COLUMN()<=班数のセル番地,IF(ISBLANK(INDEX(名簿の名前の範囲,(ROW()-数値α)*班数のセル番地+COLUMN()-数値β,1)),"",INDEX(名簿の名前の範囲,(ROW()-数値α)*班数のセル番地+COLUMN()-数値β)),"")」です。

下の例だと、
=IF(COLUMN()<=$F$1,IF(ISBLANK(INDEX(名簿!$C$2:$C$1000,(ROW()-4)*$F$1+COLUMN(),1)),"",INDEX(名簿!$C$2:$C$1000,(ROW()-4)*$F$1+COLUMN())),"")」です。
excel-randamuhan02-04

一気に数式が長くなって、分かりにくくなってしまいましたね。
でも、数式が長いだけで、内容はシンプルなんですよ。
ランダムな自動班分けにお役立ちの関数』で説明したことを理解していれば、すぐに理解できます。

では、数式が長いので、分解して説明しますね。
まずは「=IF(COLUMN()<=班数のセル番地,A,"")」です。

Aの部分は「IF(ISBLANK(INDEX(名簿の名前の範囲,(ROW()-数値α)*班数のセル番地+COLUMN()-数値β,1)),"",INDEX(名簿の名前の範囲,(ROW()-数値α)*班数のセル番地+COLUMN()-数値β))」に対応していますが、長いのでAと置き換えているだけです。
excel-randamuhan02-05

この意味は「列番号が班数以下だったらAを実行し、
そうでなければブランク("")にする」という意味です。
たとえば、下の例だと班数が5と入力されているので、6班以降が不要になります。
そのため「=IF(COLUMN()<=班数のセル番地,A,"")」により、F列以降がブランク表示になっています。
excel-randamuhan02-06

今度は先ほど省略したAを見ていきます。
しかし、Aの式も長いので「IF(ISBLANK(B,"",B)」とします。
Bは「INDEX(名簿の名前の範囲,(ROW()-数値α)*班数のセル番地+COLUMN()-数値β,1))」です。
excel-randamuhan02-07

これなら、すぐに分かりますよね。
「もし、Bがブランクならばブランク("")を表示し、ブランクでないならばBの結果を表示する」という意味です。
これにより、下の例だとピンク色の範囲の未使用セルがブランク表示になります。
excel-randamuhan02-08

最後にBの部分の説明です。
Bの部分、「INDEX(名簿の名前の範囲,(ROW()-数値α)*班数のセル番地+COLUMN()-数値β,1))」に関しては、
すでに『ランダムな自動班分けにお役立ちの関数』で主要な説明しています。

追加の説明は2つだけです。
ひとつは、班数を自由に設定できるようにするため、班数を入力していた点を
班数のセル番地に変更しています。
excel-randamuhan02-09

もうひとつは名簿の名前の範囲です。
前回は名前が入力されている範囲をきっちり指定しましたが、
今回は人数の変動があるかもしれないので、アバウトな設定にしています。

下の例ではC2セルからC1000セルの間を範囲指定しまています。
しかし、実際はご自身が扱うMAX人数で指定する範囲を決めてください。
excel-randamuhan02-10

これで班分け表のフォーマット化の完成です。

実際に使用する班分け表は、フォーマット化した班分け表をコピーして
excel-randamuhan02-11

別のシートに値&書式貼り付けをすればOKです。
excel-randamuhan02-12

いかがですか。
これで班分け表を作成するたびに、関数を入力する手間が省けます。
よろしければ、ご活用ください♪

sponsored link



sponsored link

sponsored link

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

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