Excel VBAでシフト表自動作成ツール

  • 目的:Excelでシフト表を作成。
  • 課題:条件通りのものを作ろうとすると時間がかかる。
  • 解決策:VBAで条件に従い自動作成
  • 成果物:excel-auto-shift.xlsm

※改良版を作成しています。下記リンクからどうぞ。

Excel VBAでシフト自動作成マクロを作り直しました
目的:前回作成したシフト表自動作成ツールの運用反省:抽選の偏りがあるなど細かい点で改善点が出てきた改善:一巡してからシャッフルして満遍なく配置されるようにする成果:excel-auto-shift2.xlsm前回からの反省点...

スクリーンショット

シート「職員リスト」

シフト表を作成する上での要件をまとめます。

  • 早出、日勤、遅出、夜勤の4種類の配置がある
  • 1日あたり夜勤は2人、それ以外は1人ずつを最低限配置する
  • 夜勤は1.5労働とし、基本的に2日連続で入り翌日は「明け」、その翌日は公休とする
  • 夜勤を連続で入れない場合は翌日を年次有給休暇(年休)を半日使用する「半年」、その翌日を公休とする
  • 6日連続で勤務となった場合、公休を2日続けて取得する
  • 役職や個人の都合により特定の配置にしかつけない場合、「職員リスト」シートで「0」を入力する
  • 「職員リスト」では、1ヶ月あたりの夜勤の上限回数を指定する

操作について

上部に各種ボタンを配置しています。クリックするとマクロが動きます。

まず「新規」のボタンをクリックするとアクティブのシートを複製し、シフトをまっさらな状態にします。その際、年月を入力し、日数と曜日を取得します。

配置の書かれたボタンは、選択したセルにそれぞれの配置を入力することができます。希望休など予定が決まってるのでればあらかじめ入力しておきます。

実行ボタンを押すと、空いているセルに次々と配置を入れていきます。職員はランダムに選出され、既に予定が入っているところは避けます。また、既に定員分の入力がされている配置についてはスキップします。

これで決まっている予定を守りつつ最低限の配置がされます。あとは公休の日数を調整しながら、手動で配置をしていく感じです。

シフト作成作業の6割程度が自動化されるイメージです。公休の日数調整までやろうとすればできるのですが、勤務のバランスをとるのが難しいので、仕上げは人がやると割り切った方が効率的だという判断です。

VBA

まずは最も中心となるマクロから。これは実行ボタンを押すと行われます。

主にやっていることは、日数と人数を調べた上でひたすらループさせて、条件に合っていればシフトを配置するというのを繰り返しているだけです。

このマクロの肝は配列と抽選。職員リストで配置可能は1、配置不可能は0という入力をするというルールですが、各列の値をくっつけて配列に格納させています。

例えば1人目の佐藤さんの場合は「011109」で、1桁目が0なので夜勤不可、それ以外は配置可能、末尾2桁は行番号を示すのでこれは佐藤さんの情報であるということを意味します。

これを職員の人数分配列に格納させ、Randomizeで配列内をシャッフルして抽選します。この抽選のコードはVBAで配列をシャッフルする(要素をランダムに並べ替える)を参考にしました。

新規作成

新規シートを作成するマクロです。新規ボタンから実行します。

特に言うことはありません。閏年の判定については前に記事にしましたので説明は不要です。

強いて言うなら曜日は初日の分を取得し、あとはオートフィルで仕上げていること。それだけ。

各配置ボタン

これも単純。配置名とセルの背景色を入れるだけ。念の為枠外を選択している場合は実行しないようにしています。下方については操作ミスは起こりにくいでしょうし、職員が増えた時に動作しなくなるので制限しています。

ハイライト

予定を入力する際、日や職員を間違えて選択しやすいのでハイライト表示させます。

ThisWorkbookに次のコードを入れます。下部のコメント部分は条件付き書式に設定します。

Excel/選択している行をハイライト表示する方法を参考にしています。

そんな感じです。以上。

続編があります。こっちの方がおすすめです。

コメント

  1. 佐々木真司 より:

    実用性のある素晴らしいVBA情報ありがとうございます。質問になりますが夜勤は1.5労働とし、基本的に2日連続で入り翌日は「明け」、その翌日は公休とするの条件に遅番を追加する場合はどうすればよろしいでしょうか?

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

      佐々木真司 様
      コメントありがとうございます。
      autoMakingプロシージャの205行目にある”公休”を”遅出”に変えればできます。
      公休か遅出のどちらかをランダムで配置という場合は、さらにひと手間が必要ですのでここでの説明は割愛します。

  2. 山田義久 より:

    素晴らしいVBAですね。
    初心者にはとてもありがたいです。

    遅出の次の日は早出にしないということはできますでしょうか。

  3. 山田義久 より:

    素晴らしい機能でした。

    VBA初学者なので、ほとんど書いてあることが分からないのですが、少しずつコマンドを勉強しています。

    遅出の次の日は、早出以外(日勤か遅出のランダム)としたいのですが、どうしたらよいでしょうか

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

      山田義久 様
      コメントありがとうございます。

      遅出の次の日は早出以外とするのは可能です。

      97行目からのForループが各配置の処理になるのですが、早出の処理はiが1の時に行われます。
      149行目あたりに、iが1の場合、1列前のセルの値を確認し「遅出」だった場合はsetFlgをFalseにするという分岐を加えたらできます。
      あまり難しくない処理なので、調べながらコードを書いてみてください。
      ヒントとしましては、処理を行う行番号はsetRow、列番号はsetColで表すので、1列前のセルを参照する場合はsetCol – 1で表せます。

  4. 近江鈴雅 より:

    実用的な素晴らしい情報ありがとうございます。
    質問ですが必ず3連勤「同じ時間を」入れたい場合はどうすればよろしいでしょうか?

    • しめひつ より:

      コメントありがとうございます。
      151行目と152行目がセルに入力するコードですので、翌日と翌々日の分も同時に入力するようにすればよいかと思います。

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