新年あけましておめでとうございます。
久しぶりのブログ更新なんですが、今回のネタは、「Excelで作った名簿の順序をシャッフルしたい」と言う要求に対する回答例を解説します。
まず問題の背景なんですが、小学校の役員改正の選挙で名簿の上位の人が不利という話がありました。まぁ、実際の選挙でも、候補者掲示板の場所が重要と言う話もありますし、ならば、各個人に配布する候補者名簿の順番をランダムにしたらどうかと言う話が出たようです。（あくまでも、カミさんからの伝聞ｗ）
さて、実際に作るにあたって、過去数年の名簿がExcelのファイルで各年度の役員を持ち回ってきた事を鑑みて、Excelでお膳立てしておけば、何かと役に立つんだろうということでExcelでの実現とし、幸か不幸か私のメイン環境がMac版のExcelということもあり、VBAに頼らず関数のみで実現する方法としました。
Excelファイルの中は大きく2種類のシートに分かれ、1つは名簿の元データとして編集可能な上3年〜下4年の4シート（原簿）、もう１つは、ランダムに順序が並び変わる候補者名簿の2シート（名簿）で構成されています。
仕組み自体は、名簿の各個人データに rand() で乱数を振り、名簿側で、乱数を元に昇順にソートしたデータを使うというもの。再計算するたびに原簿の乱数が変わるので、名簿の順序はシャッフルされます。
まずは、原簿シートについて
A列から順に、順序キー、班、名前、・・・と個人情報が並んでいます。
順序キーのみ関数が仕込んであります。
関数：=IF(C3="","",ROUNDUP(RAND()*10000,0))
名前欄に値があった時のみ、乱数値が入ります。 乱数値は、1万倍してから小数点以下を切り上げる事によって0〜9999の間の整数を得ています。
また、名簿シートで使うための名前の定義として、順序キーのみと、データ全体を定義しています。
次に、名簿シートについて
図では、上地区、下地区となっていますが、基本的には参照している原簿シートが異なるのみの違いです。
図でA列は非表示になっていますが、原簿の順序キーを参照する関数が入っています。
関数：=IF(上3年!A3="","",SMALL(上3年順位,1))
原簿側の人数が可変できるようにするため、原簿側のデータ1行目のデータの生む判定を組み込んでいます。
関数：SMALL(上3年順位,1)
が肝で、原簿側の順序キーを昇順に並べ替え、小さい順に1番目の値をとって来る という動作をします。
つまり2行目は、SMALL(上3年順位,2) となります。
次にB列では、A列の順序キーを検索鍵として原簿を検索し、見つけた行の2列目つまりB列の値を取っています。
関数：=IF(A3="","",IF(VLOOKUP(A3,上3年原簿,2,FALSE)="","",VLOOKUP(A3,上3年原簿,2,FALSE)))
参照する値がない場合、つまりA列がブランクの場合、エラーになるので、if関数で、A列に値があるときのみ関数を実行するようにしています。
C列以降もB列と同じ関数の繰り返しで、原簿シートのC列の値を取得するためには、VLOOKUP関数で取得する列番号が3になります。
作ったファイルは、下記リンクにおいておきますので、ご興味があればどうぞ。
RandomList.zip
尚，ファイル中の個人情報は、なんちゃって個人情報を利用して生成しました。
