Accessで条件付きグループ分けを作ってみた

企業、学校、個人。それぞれのシーンに於いて一定の需要があるのが「グループ分け」。人の班分けだけでなく、物品のグルーピングなどに於いて一定の条件の元グループ分けをする作業は、人間が行うと高確率で失敗します。

しかし、自分の需要にあったグループ分けプログラムは自分で作るしかありません。そこで今回研修会参加者を一定条件に基づいて班分けするというプログラムを作成しました。

難易度：

今回使用するファイル

• グループ分けプログラム – Access2016で作成（64bit）
• 日程表テンプレート – 上記のプログラムにも同梱のexcelシート

※日程表テンプレートに参加者情報を記述して、Access側にインポートする仕組みになっています。グループ分けはAccess側で行わせます。

事前準備と条件

プログラムの準備

このプログラムは以下の参照設定を利用しています。今どきの環境で追加しなければならないというものは無いと思いますので、通常は特に何もする必要はありません（自前で構築する場合を除く）

• Microsoft Office 15.0 Object Library – リボンの構築で使用しています。
• Microsoft Scripting Runtime – ファイルの有無など様々なシーンで利用しています。
• ActiveX Data Object 2.8 Library – Excelデータの書き出し時に今回利用しています。

また、暗号化ZIP形式でのバックアップ・復元の為に、7-zip64.dllを利用しています（ファイルに同梱）。

図：ライブラリの参照が必要

日程表の準備

日程表テンプレートにデータの追記が必要です。本プログラムはこの日程表を取り込んで、グループ分けをするプログラムという形にしているため、事前にデータの準備が必要になります。

データはテーブル形式になっており、今回利用する列は以下の通り。

1. 会社No（数値）
2. 会社名（文字列）
3. 部署（文字列）
4. 業種（文字列）
5. 姓_漢字（文字列）
6. 名_漢字（文字列）
7. 姓_全角カナ（文字列）
8. 名_全角カナ（文字列）
9. 年齢（数値）
10. 性別（文字列） – 男 or 女

各種コードシートは、メインの受講者リストの入力を助けるためのものなので、特に整備はせずとも良いですが、データの入力規則やvlookup関数での参照などで利用すると良いでしょう。

グループ分け条件

今回のプログラムでは振り分ける条件を固定ではなく、いくつかはオプションで選べるように作成しています。固定の条件としては人数チェックと男女比チェックは必ず実行するようにしています（つまり、1チーム指定の人数を超えているか？男女比が約半分になってるかどうか？）。

1. リボンのグループ分け⇒アプリの設定を開く
2. アプリの設定ダイアログ内の「アプリの設定」タブを開く
3. 振り分けオプションでさらに振り分け条件を加えたい場合にはチェックを入れる
4. グループ人数は、1チーム当たりの人数です。日程表参加者をこの数値で割った値がグループの数になります。
5. 年齢構成チェックは、全体の中央値（平均値ではない）に近くなるように、振り分けチーム内の年齢構成を考慮して、チームに加える設定です。
6. 同姓チェックは、同じ姓を持つものはなるべく一緒にしないようにする為の設定です。
7. 同企業チェックは、複数会社参加の振り分けをする際に、同じチーム内に同じ会社の人間を一緒にしない為の設定です。
8. 同業種チェックは、複数会社参加の振り分けをする際に、同じチーム内に同じ業種の人間を一緒にしない為の設定です。
9. 同部署チェックは、同じチーム内に同じ部署の人間が一緒にしない為の設定です。
10. 年齢構成チェックをオンにすると、「なるべく近い歳」というオプションが使えます。このオプションは追加時のチーム内最小年齢の上下2歳までをチームに追加可とするという条件に変更する為のものです。
11. 保存を押すとsettingテーブルにオプション設定が保存されます。

振り分けには乱数を利用し、グループ数分のコードが割り振られるようになっていますが、条件によっては必ずしも条件通りの振り分けができるとは限りません。その場合、あぶれた人は再度、人数チェックと男女比チェックだけでどこかのチームにねじ込むようにプログラムされています。条件にあぶれた人をねじ込んでるので、条件通りになっていないチームも発生します。

但しこれでもあぶれるケースがあります。この場合振り分け失敗となりますが、乱数を使ってるので、再度振り分け実行を行えばうまくいく事もあります。

使い方

使い方自体は非常に単純です。

1. 受講者リストのインポートをクリックし、用意した日程表テンプレートを読み込ませてデータを取り込みます（洗い替えなので、新しいデータを取り込むと古いデータは削除されます）
2. グループ分け実行を行うと。アプリの設定にてセットした条件に基づいて判定し、チーム割を行います。
3. グループ分けが完了するとグループ分け結果フォームが表示され、チーム内平均年齢や人数、それぞれの人間の値が表示されます。
4. グループ分け出力をクリックすると、指定の場所に受講者リストとして分けた結果をソートしたデータをExcel形式で出力してくれます。

ZIPバックアップを活用すると、セットした条件やその時の結果などをキープしておけ、復元で元に戻せるようになっています。いくつか試してみて納得の行くパターンになったら、出力してみると良いでしょう。

ソースコード

グループ分けを実行するコード

• グループテーブルは振り分け後の振り分け結果の親テーブルです。チームテーブルがそれにぶら下がるサブテーブルとなります。
• 受講者リストソートはクエリです。人数が多い順に会社毎にソートしています（こうする事で振り分け成功率が上がります）
• グループ数は参加者総数÷1チーム当たり人数で算出。
• 平均年齢とありますが、実際には中央値を利用しています。振り分け時に、全体中央値よりチーム平均値が低い場合、高い場合、それぞれに於いて追加しようとしてる者の年齢を比較して追加するか？しないかを決定しています。
• 男女比の算出は全体から算出。1チーム当たり人数÷2で計算していますが、男女比が1:1ではないようなケースでは、ここの加工が必要になります。
• 対象者の追加可否のトライ回数はグループ数回分行う。この判定用にbreaker変数を用意してあり、breakerがリミットに達したら、ループを強制離脱します。
• チェック順序はより条件が厳しい重複可能性の高い項目から行っています（でないと、条件適合外が大量に発生してしまう）。
• 順番は、人数リミットチェック、男女比チェック、部署チェック、同部署チェック、同企業チェック、同業種チェック、同姓チェックの順に行っています。
• beforeflgに値を加算する事で通過とし、合計値がオプション項目の数の合計である5に達したらチームに加えるようにしています。
• あぶれた場合、2段階目として「人数リミット」「男女比」のチェックのみで通過とし、チームに強制振り分けをさせています。
• それでもNGの場合には「失敗」と看做し、プログラムは強制終了します。但し、Rnd関数によるランダムな生成なので、再度の実行や条件の緩和によって通過するようになるかもしれません。

実行結果をExcelシートに書き出すコード

• デスクトップフォルダを初期値として、export.xlsxファイルをコピーし、そこに出力します。
• チーム内容をソートしたクエリをそれぞれ、CopyFromRecordsetメソッドで指定のシートに出力します。
• GetFileName関数はファイルの選択用ダイアログを出すための関数です。
• 時々指定の列の文字に半角全角混在してるケースがあるので、kanahenにて全角カナに統一しています。
• 時々指定の列の文字にスペースが混在してるケースがあるので、cutspaceにてスペースは除去しています。
• ADOとCopyFromRecordsetがあれば同様のデータ出力は可能なので、Excel単体でも出力系は完結は可能といえば可能です。

関連リンク

• CopyFromRecordsetでRecordsetの中身をワークシートに一括出力
• [ファイルを開く]ダイアログボックスを表示する（GetOpenFilenameメソッド）
• VBAで乱数を発生させる（Rnd、Randomize）