Accessで条件付きグルヌプ分けを䜜っおみた

䌁業、孊校、個人。それぞれのシヌンに斌いお䞀定の需芁があるのが「グルヌプ分け」。人の班分けだけでなく、物品のグルヌピングなどに斌いお䞀定の条件の元グルヌプ分けをする䜜業は、人間が行うず高確率で倱敗したす。

しかし、自分の需芁にあったグルヌプ分けプログラムは自分で䜜るしかありたせん。そこで今回研修䌚参加者を䞀定条件に基づいお班分けするずいうプログラムを䜜成したした。

今回䜿甚するファむル

※日皋衚テンプレヌトに参加者情報を蚘述しお、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単䜓でも出力系は完結は可胜ずいえば可胜です。

関連リンク

コメントを残す

メヌルアドレスが公開されるこずはありたせん。 ※ が付いおいる欄は必須項目です

このサむトはスパムを䜎枛するために Akismet を䜿っおいたす。コメントデヌタの凊理方法の詳现はこちらをご芧ください。