ランダムな席替えをしたいだけ

2024年7月1日 00:30

先生のExcel力がすごいって話

　席替えって自由にやろうとすると絶対揉めますよね。
　最前列は先生からよく話しかけられるし~~（あと口が臭い）~~、後ろは寝れるし、夏の窓際ほど過酷な勉強環境はありません。

　やっぱり席替えはランダムが一番なんです。でも、わざわざ席替えのために40人が順番にくじを引くのはいろいろ面倒じゃないですか。
　そんなとき、１年の担任のＭ先生は、なんとExcelでランダム席替えシートをつくっていたんです。しかも番号が出るだけでなく、席のレイアウトが見事に整理され、名前とふりがなの参照までなされており、そのシートを印刷するだけでよいという優れもの。
　うひょー、はじめて見たときは感動しました。やっぱExcelの力は偉大だなと。それ以上に先生のExcel力がすごい。

　２年になって困りました。先生も変わり、あのすばらしいExcelシートがない。10秒で終わった席替えも、くじ引きすると5分近くかかるんですね。

どうやって作ったのか？

名前とふりがなを参照する【XLOOKUP関数】

　わたしはクラスの中ではExcelは触れる方だと自負しておりましたので、Ｍ先生のあのシートを真似して作ってみることに決めました。番号を入力すると名前とふりがなが表示されるのは、XLOOKUP関数とかでできますね。シートの見えないところに表があったのでしょう。

XLOOKUP関数
XLOOKUP 関数は、範囲または配列を検索し、最初に見つかった一致に対応する項目を返します。一致するものがない場合、XLOOKUP は最も近い (近似) 一致を返します。

=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])

XLOOKUP 関数 - Microsoft サポート

乱数を生成する【RAND関数】

　さて、番号から名前を返す方法は簡単なのですが、では肝心の乱数の生成について。指定された範囲から乱数を出力する関数にRANDBETWEEN関数があるのですが…

RANDBETWEEN関数
指定された範囲内の整数の乱数を返します。ワークシートが再計算されるたびに、新しい整数の乱数が返されます。

=RANDBETWEEN(最小値, 最大値)

RANDBETWEEN 関数の書式には、次の引数があります。
・最小値　必ず指定します。乱数の最小値を整数で指定します。
・最大値　必ず指定します。乱数の最大値を整数で指定します。

RANDBETWEEN 関数 - Microsoft サポート

簡単に言えば、
　=RANDBETWEEN(1,40)
と入力すれば、1~40の整数の中でランダムな数字を出力します。しかし、この関数を40個のセルに入力しておしまい……というわけにもいかないのです。なぜなら、この方法では番号が重複するからです。

　「1~40の値をランダムに出力する」という作業を40回繰り返してすべて違う値を出力する確率は、$${6.75 \times 10^{-17}}$$くらいだと思います。こういう計算は苦手なので合っているか分かりませんが、いくらやってもすべて違う値が出力されることはまずないと思います。

　それではどうしたものか。範囲内の整数の乱数を出力するRANDBETWEEN関数のほかに、こんな乱数関数がありました。

RAND関数
RAND では、0 以上で 1 より小さい実数の乱数を返します。ワークシートが計算されるたびに、新しい実数の乱数が返されます。

=RAND()

RAND 関数の書式には引数はありません。

RAND 関数 - Microsoft サポート

　このRAND関数は、0~1の細かな小数の乱数を出力します。もちろん、出席番号は小数ではありません。しかし、この乱数をいくつ出力しても重複は天文学的倍率でしょう。非常に細かい桁まで乱数が出力されています。

ランダムな順番を生成する【RANK関数】

　さて、ここまでくればもうお分かりかもしれません。RAND関数で40個の乱数を出力し、RANK関数で順番を対応させれば…

RANK関数
数値のリスト内の数値のランクを返します。数値のランクは、リスト内の他の値に対する相対的なサイズです。 (リストを並べ替える場合、数値のランクはその位置になります)。

=RANK(数値,範囲,[順序])

RANK 関数の書式には、次の引数があります。
・数値　必ず指定します。範囲内での順位 (位置) を調べる数値を指定します。
・範囲　必ず指定します。数値の一覧への参照。参照に含まれる数値以外の値は無視されます。
・順序　省略可能です。範囲内の数値を並べる方法を指定します。
　　　　順序に 0 を指定するか、順序を省略すると、範囲内の数値が ...3、2、1 のように降順に並べ替えられます。
　　　　順序に 0 以外の数値を指定すると、範囲内の数値が 1、2、3、... のように昇順で並べ替えられます。

RANK 関数 - Microsoft サポート

①　"=RAND()" を入力し、必要な数だけ下にオートフィル
②　となりのセルに、"=RANK("数値","範囲","順序")" を入力
　　　・"数値"　乱数を出力したとなりのセルを指定
　　　・"範囲"　乱数を出力した範囲全体のセルを指定
　　　　　　　　行数字は$で固定させる
　　　・"順序"　この場合、0でも1でもどちらでもよい
③　RANK関数を入力したセルもオートフィル

　ご覧の通り、ランダムな順番が生成されています。

再計算を自動化する

　最後に、シートを再計算させるマクロを組んで、ボタンをおいておきましょう。再計算は Shift+F9、もしくは「数式」タブの右端にあります。VBAのコードもおいておきます。

Sub シート再計算()
ActiveSheet.Calculate
End Sub

活用例

　実用的にデザインすると次のようになります。

　右下はわかりやすいように書いておきましたが、当然省略もできます。左上の座席表のセルに直接XLOOKUP関数を書き込むわけですね。でもやはり、別枠でワンクッション置いたほうが見やすいと思います。