【Excel VBA】シフト自動作成マクロを作り直しました

前にExcelでシフト自動作成マクロを作りました。現在のところこのブログで最も見られている記事で、相当シフト作成に苦労されているところが多いんだなと見受けます。

内容的にはあらかじめ予定をセットしておいてプログラムを走らせたら、ランダムで配置していくというもの。

複雑な勤務体系の場合はどうしても勤務や休みに偏りが出てしまうのですが、それを機械任せにすることで公平性をもたせるという目的です。

ただ、イマイチだったのがランダムゆえの偏りが発生してしまいがちなのが気になっていました。毎回抽選し直していたので、連続で同じ人が同じ配置についてしまうことがしばしば。

そこで、抽選する配列には行番号や列番号を入れておき、一巡してからシャッフルするという風に作り変えました。

成果物はこちらからダウンロードしてください。

スポンサーリンク

使い方

フォーマット自体もかなり変えました。

前回は設定用のシートを別に設けていましたが、シフト表の右側にまとめました(以下、設定リストとよびます)。シートが別だと設定するのを忘れてしまいがちなので、この方がミスが少ないですよね。

設定リストの早出、遅出、夜勤が入っているセルの色をセットするようにしています。これでお好みの色にすることができます。また、その上の人数と書かれた行の数字の数だけ配置します。

実行ボタンを押した後です。割とバランスよく並んでいるかと思います。

要件としましては、今回は夜勤を1労働としてカウント、なるべく連続で入るようにして、その場合は2連休を配置するようにしました。

前回は労基的な意味で7連勤にならないようにしていましたが、今回は廃止しました。いろいろ方法を考えましたが、ループしてしまう可能性が少しでもあったので。バグをなくすことを最優先に考えました。

勤務配置

今回は公休を確実に取らせるために、夜勤→公休→日勤という順に配置していきます。夜勤が先に来ているのは公休との関連が強いからです。

大きく変えたのは配列の考え方。前回は設定シートから職員の情報を配列に入れて、日ごとに配列をシャッフルして取り出すということをしていました。これだとランダムとはいえ30日連続同じ配置ということもありえます。

配列のシャッフルはrandomPro()で行います。今回は夜勤と日勤を配置する時は行番号、公休を配置する時は列番号を配列にセットし、一度シャッフルした配列は最後のインデックスが取り出されてからシャッフルするという方式にしました。これでかなり分散させることができます。

配置できたらカウンタを減らして、0になったら次のフェーズへ移行するのですが、全部のセルが埋まっていてカウンタが0にならない事態を防ぐために、配列をシャッフルするタイミングで空白セルがあるかどうかをチェックするcolChkPro()とrowChkPro()を用意しました。

2021年3月21日追記

コメント欄でお知らせくださったのですが、設定リストの早出と遅出のON/OFFがうまく機能していませんでした。

「If Cells(rowArr(arrCnt), colCfg + 1 + j).Value = 1(以下略)」とすべきところを、「If Cells(rowArr(arrCnt), colCfg + 1).Value = 1(以下略)」としていたため、常勤列の1 or 0で判定するようになっていました。

ファイルを更新していますので差し替えていただくか、Module1をそのまま上記のコードで上書きしてください。

新規シート作成

この辺はほぼ同じです。

配置ボタン

色の情報を設定リストから取得するようにしています。

完成版はこちらをご覧ください

いろいろ作った結果、これが完成版ってものができあがりました。

動作も速いしカスタマイズしやすいのでこちらをどうぞ。

コメント

  1. 山本 より:

    夜勤の人数の件ですが、たとえば、その日に夜勤入と夜勤明けがありますが、設定で夜勤を2にすると夜勤入が二人重なるため、夜勤明けと夜勤入りができません。
    当日に夜勤明けと夜勤入りを分けることは可能ですか?

    • オクタ゛シヨウヘイ より:

      コメントありがとうございます。
      すみません、夜勤明けと夜勤入りを分けるという意味があまりわかりません。
      夜勤入りの翌日は夜勤明けにするということでしょうか。
      それでしたら前バージョンでやっておりますので、そちらを参考にしてください。
      https://www.iehohs.com/excel-auto-shift/

  2. 山本 より:

    返答ありがとうございます。
    説明が悪くてすみません、早出・遅出・夜勤の色付きのところの数字を夜勤を2にした場合に夜勤入りのスタッフが同日に夜勤入りが2人になり、次の日はその2人が夜勤明けになるため、夜勤入も含めた場合は、当日は夜勤入1、夜勤明1 で夜勤は数通りに2で合計されますが、夜勤明けが2人になって夜勤入りがない状態になりますので、どのようにしたらよいでしょうか?よろしくお願いいたします。
     12月 1日   2日
        夜勤入  夜勤明
        夜勤入  夜勤明
        早出   早出
        
        早出1  早出1
        夜勤2  夜勤2

             ↑設定どおり夜勤は2になりますが、夜勤入がないためその日に夜勤に入る人がいない。

    • オクタ゛シヨウヘイ より:

      既にアレンジして「夜勤入」と「夜勤明」がランダムで入るようにされているということですかね。
      元のプログラムとしては設定リストのところでR4C42のセルが「夜勤」となっていれば、既に入っている人数の確認は「夜勤」の文字列が入っている時のみカウンタを減らすので「夜勤入」および「夜勤明」どちらの場合でもカウンタは減らないはずです。
      単純に「夜勤入」を設定した人数分配置したいのであればR4C42のセルを「夜勤入」に変更し、「夜勤入」が配置された翌日は「夜勤明」を設定するようにコードを書き足せばOKかと思います。
      ちなみに「夜勤入」の翌日が必ず「夜勤明」になるということは2労働制の夜勤でしょうか。

  3. 山本 より:

    夜分遅くに何度も返答ありがとうございます。
    そうです、2労働制の夜勤です。コードはどのように書き足せばよろしいですか?

    • オクタ゛シヨウヘイ より:

      「夜勤入」が入力されたらその次の列に「夜勤明」が入るようにコードを記述すればいいと思います。
      具体的なコードについてはコメント欄でお伝えしないことにしているので、ご自身で頑張ってコーディングしてみてください。
      どうしてもわからないのであれば、ランサーズあるいはクラウドワークスよりご依頼ください。

  4. 山口悟 より:

    まさに私が欲しかった情報があって助かりました。
    条件を一つ追加したい時などはどうやって記述すればいいでしょうか。
    当直回数を本人の希望数にしたいと思っています。
    Aさんが3回を希望してたら、元から配置した分+ランダム配置=3
    になった時点でBさんに以降するようにしたいと考えてます。
    元から配置した分とランダムで配置された分があるので、わからなくなってしまいました。

    • オクタ゛シヨウヘイ より:

      コメントありがとうございます。
      設定リストのところで希望回数を入力して運用することになると思うのですが、このサンプルのコードだと夜勤については1日ずつに設定していくので、それだと希望回数に満たない人も出てきてしまいます。上限回数として認識させることは可能ですが。
      公休については1人ずつに決まった日数分ランダムで設定していくので、これを応用して夜勤を配置するのが良いかと思います。

  5. 通りすがり より:

    仕組みは分かるのですが、コードが面倒になることが分かってるので、作り上げたことについて素晴らしいと思わせられました。これからも頑張ってくださいー!!

  6. たまき より:

    質問なのですが今回も配置可能を1、不可能を0として扱っているのでしょうか?サンプル画像を拝見したところ早出が0になっている方も早出が入っているのでそういう仕様はないコードになっているのでしょうか?

  7. やまと より:

    こんにちは。質問してもよろしいでしょうか?
    作り直した方のコードだと1と0で配置の可不可を判定していないのでしょうか?初歩的な事ですいません。

    • やまと より:

      すいません。消えたと勘違いして同じようなコメントをしてしまっています。

      • しめひつ より:

        コメントありがとうございました。

        初回のコメント投稿は承認するまで一旦止まる仕様になっているので、勘違いされるのも無理ないです。気にしないでください。

        ご指摘いただいた点ですが、結論から申し上げると私の記述ミスでした。失礼いたしました。
        ファイルおよびこの記事のコードを修正し、掲載さいているキャプチャも差し替えしました。

        早出および遅出を自動入力するかは設定リストの0 or 1で切り替えていましたが、修正前のコードでは常勤列で判定していたため早出列が0だったとしても常勤列が1であれば早出も自動入力するという状態でした。

        コメントしていただいて大変助かりました。ありがとうございました。
        今後もお気付きの点がございましたらコメントを頂けますと幸いです。

タイトルとURLをコピーしました