【Excel VBA】シフト表自動作成マクロをカスタマイズしやすくしました

これまで3回に渡って改良してきた勤務表自動作成ツールですが、今回の記事で完全版としたいと考えています。

前回の記事は二次元配列を使うことで高速化したというのがメインでしたが、それ以外に夜勤の労働数や遅出の翌日は早出禁止といった条件をセルの値を変えることで切り替えられる仕様にしていました。

これは考えうるニーズのある機能を埋め込んでおいて、ユーザーが任意で使うか使わないかを簡単に切り替えられるというユーザー目線の仕様でした。

ただ、多機能にしようと思えばいくらでもできてしまうのでキリがありません。多機能にすると操作や設定が複雑になって使いにくいものになってしまいます。

それでも使用するユーザーが多いのであれば、知識のないユーザーが自由に設定を切り替えられるのは便利です。逆に使用するユーザーが限定されるのであれば、機能を最低限にして開発者が特定のユーザー向けにカスタマイズする方が合理的だと考えました。

というわけで、この完全版では開発者が任意でカスタマイズしやすいようにコードを整理しました。

スポンサーリンク

使い方

https://www.iehohs.com/files/excel-auto-shift4.xlsm

こちらがサンプルファイルです。

勤務表本体部分と、下に日別の集計、右側に人別の集計、さらにその右に自動作成用の設定があります。

勤務表は入力規則によってドロップダウンで選択できるようにしています。

集計は1列・行目の文字列をCOUNTIFで数え、2列・行目の数字未満であれば赤くハイライトするよう条件付き書式を設定しています。

なお、前回まで選択しているセルの職員と日付がハイライトされるようにしていましたが、動作が重たくなるので今回は設定していません。

新規シート作成

[新規]ボタンを押すと年月を入力するフォームが出てきます。選択式にすることで分岐を減らせますし、何よりユーザーの入力が楽になります。

西暦は現在の年と前後1年を選択できるようにしています。

この西暦と月から1ヶ月の日数と曜日を取得して自動的に設定し、新規シートを作成します。

実際には[新規]ボタンを押した時のアクティブシートを複製して、勤務表の中身をクリアして日付と曜日を設定し直しているだけです。

職員追加

[追加]ボタンを押すと職員名を入力するウィンドウが出てきます。

これで勤務表の最終行に追加されます。

これだけなら行追加でやってもそれほど労力はかからないのですが、その場合の問題は下の集計です。

単に行追加しただけではCOUNTIFの数式の参照が変わらず、追加した行が集計されません。このボタンから追加を行うことで、中の数式も更新しています。

自動入力

肝となる部分です。事前準備として、希望休や勤務の予定が決まっていればあらかじめ入力しておきます。

自動入力はランダムでセルを選択し、空白セルに対して入力していきます。何かしらの値が入っているセルは対象外になります。

そして設定の部分ですが、夜・公休・早・遅の4つのシフトを入力します。この並びで順番に処理が行われます。

それぞれの要件は以下の通りです。

1労働の夜勤で、基本的に連続夜勤でその後は連続休み。
連続にならなかった場合は翌日休み。
公休月によって日数が変化。31日の月は10日、28日の月は8日、それ以外は9日。
5連勤以上にならないようにする。
遅出の次の日は早出にならないようにする。
早出の前日は遅出にならないようにする。

1行目の文字列が入力され、2行目は設定する回数です。夜・早・遅は1日あたりの人数で、公休は1ヶ月あたりの回数です。公休については勤務表の日付の入力から自動的に値が変わるように関数を埋め込んでいます。

今回の設定では夜勤は1人、早出と遅出が2人ずつ、6月は30日までなので公休は9日設定されます。

各職員の行は自動入力するか否かを設定でき、0ならオフ、1ならオンです。公休は1なら常勤、0なら非常勤とみなされます。

設定が確認できたら[入力]ボタンを押します。

二次元配列内で処理をして最後にシートへ貼り付けているのでほとんど時間がかからず完了します。セルの背景色はあらかじめ条件付き書式で設定されています。

設定通り入力されますが、職員数が少なかったりあらかじめ入力されていた予定が多いと設定の人数や回数に満たない場合もあります。集計で数が合っているか確認します。

空白置換

自動入力後の調整は自由です。ここでめんどくさいのは空白セルの処置。1つずつ埋めるのは大変です。

[置換]ボタンを押すと、常勤職員は「日」、非常勤職員は「休」が空白セルに入ります。常勤と非常勤の判定は公休の列で行います。

これで勤務表の完成という流れです。

VBA

Module1(標準モジュール)

解説

各ボタンのプロシージャは下記の通り。

ボタンプロシージャ
新規新規シート
追加職員追加
入力自動入力
置換空白置換

そして繰り返し同じ記述が必要な部分についてはプロシージャでまとめています。

レイアウト設定参照する行・列番号を設定
人数カウント1日に対して既に入力されている数をカウント
シャッフル配列の順番をランダムに入れ替える

今回の主な変更点は夜→公休→早→遅という流れを、ループの変数iの値0→1→2→3で表し、Select Caseでそれぞれの処理を独立させたことです。

Case 0の中は夜勤のみの処理で、Case 1の中は公休のみの処理と、明確に分けることで処理を自由にカスタマイズできるようにしています。もし遅の右にシフトを追加した場合は、Case 4でその処理を実行できます。

前回までは共通する処理を行った後、分岐させてそれぞれ独自の処理を行っていましたが、この共通の設定がいつでも共通になるとは限りません。多少コードが重複してもそれぞれで独立させた方が保守面では良いと考えました。

どのシフトでも出てくるような処理があれば、人数カウントのようにプロシージャでまとめてしまうとよいでしょう。

UserFormNewSheet(フォーム)

解説

フォーム内のプロシージャは以下の3つです。

UserForm_Initializeフォームを開いた際に年月の値を作成
CommandButtonOK_Click実行ボタンクリック時の処理
CommandButtonCancel_Clickキャンセルボタンクリック時の処理

そしてフォーム内のコンボボックスとボタンには下記のオブジェクト名があてられています。

ComboBoxYear
ComboBoxMonth
実行ボタンCommandButtonOK
キャンセルボタンCommandButtonCancel

コメント

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