【Excel VBA】シフト表自動作成マクロをフォームで操作できるように作り直しました

シフト表自動作成マクロは前回のバージョンを完全版ということで最後の更新にしていました。あれから1年が経過していたんですね。

このマクロを求めてそれなりに見に来る人がいまして、どこかで使ってもらってるのかもしれませんが、正直言って使えるものではないと思っています。

僕の勤め先でも似たようなものを試してもらったのですが、結局普及には至っていません。

うまくいかない主な意見として、1回のマクロ実行で設定しているすべての配置が入力されるのですが、必ず手直しが必要な箇所が発生し、一つ直すとそれに合わせて別の箇所を直したり……という大きな手間が発生してしまうわけです。

一度雑に組み上がったプラモデルを手直しするのにバラすぐらいだったら一から組んだ方が早いという感じです。プラモデル作ったことありませんが。

一気に入力するのではなく少しずつユーザーのペースで自動入力できればいいのですが、そうするとシートの構成がごちゃごちゃになってしまうなと思って断念していたのですが、フォームを使えば解決すると思ってここに来てアップデートをしてみた次第です。

スポンサーリンク

影響されたツール

影響されたのはこの「シフト助手」というマクロを使ったツール。この動画を見てフォームを使えばいいのかと気付きました。

あと地味に良いなと思ったのがセルをクリックしたら任意の配置を入力でき、右クリックで削除するというもの。ドロップダウンリストから選ぶやつって結構めんどくさくて、結局普通にキータイプで入力してしまいがちですよね。

サンプルファイル

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

https://www.iehohs.com/files/excel-auto-shift5.zip

バグがあるかもしれません。お気づきのことがあればコメント欄でお知らせください。

軽微なバグについてはひっそりと直してアップし直しますのであしからず。

シンプルにシフト表と日別と月間の集計のみの構成です。必要最低限にまとめました。

実行ボタンの配置場所がしっくりこなかったので、「Ctrl + Shift + a」で実行するようにショートカットを設定しました。

起動するとこのようなフォームが表示されます。

新規シート

「新規シート」ページでは、指定した年月のシートを作成します。

実行するとシフト表の中身がリセットされ、選択した年月に合わせて日付や曜日が変更されます。

設定情報の表示

これ以降に行う設定情報は、すべてシートに記録され、このフォームはシート上にある値を表示しているに過ぎません。

そうした設定情報は見えていると格好悪いので普段は非表示にしています。書式設定を「;;;」にしているだけですが。

右側の設定情報のオプションボタンを表示にすると、このように設定情報が見えました。これらの値を使って自動入力を行います。

職員設定

「職員設定」ページでは各職員の設定ができます。

リストから職員を選ぶと中央の職員名のテキストボックスに職員名が表示され、ここの値を変えて[職員名保存]ボタンを押すと変更されます。

リストの[+]ボタンは最終行に職員を1人追加、[-]ボタンは選択した職員を削除します。[↑][↓]ボタンは選んだ職員の順番を並び替えます。

右側にある勤務配置のリストは自動入力の対象にするかどうかの設定で、リストをクリックするとON/OFFが切り替わります。

勤務設定

「勤務設定」ページで、勤務配置の設定を行います。

勤務名の設定はもちろん、文字色と背景色を設定できます。ここで選んだ色が条件付き書式として設定されます。

勤務と休みのオプションボタンは、自動入力の動作に影響し、勤務は日ごとに指定人数を行ランダムで配置、休みは1人の職員に対し指定回数を列ランダムで配置します。

カウントは日別の人数と月間の回数を指定でき、集計表で赤くハイライトされる条件の他、自動入力で入力される人数および回数にもなります。

自動入力

「自動入力」ページで自動入力を実行します。

人数や回数以外の設定として、前日あるいは翌日にセットで配置する設定と、並んで入力されるのをNGにする設定ができます。

このキャプチャでは「夜勤」の翌日は「休」が入力される設定になっています。「早出」の前日は「遅出」はNGといった設定などもできます。

連勤上限はこの値を上限に勤務が続かないようにするのと、これよりも狭い間隔で休みが入力されます。

手動入力

「手動入力」にチェックが入った状態で、勤務表のセルをクリックすると選択した勤務が入力されます。

これで希望休や予定を入力することが可能で、太字入力にチェックが入っていれば太字で入力されて手動で入力したものだと識別できます。

この太字は見た目だけでなく、自動入力後に入力解除で選択した勤務をクリアすることができるのですが、太字は対象外になっています。

つまり、手動入力した部分だけ残して自動入力をリセットできるというわけです。

自動入力実施

[自動入力]ボタンを押すと、選択した勤務だけが自動入力されます。すべて同時に行われるわけではありません。

夜勤で実行するとこんな感じに、翌日配置で設定した「休」がセットで入っています。

「夜勤」を選択した状態で[入力解除]ボタンを押すと、「夜勤」とセットで入力された「休」がクリアされます。

ここで手動入力で太字になっている「夜勤」または「休」があれば、それらはクリアされません。

一度に自動入力する勤務を分けることで、各勤務の自動入力後に調整ができるので思い通りのシフト表を組みやすくなるという狙いです。

一通り実行した結果がこんな感じです。

入力できなかった場合は次回優先的に入力

自動入力のところはこれまでと同じような仕組みなのですが、一つ大きく変えた部分があります。

入力する行番号が入った配列をランダムにシャッフルし、順番に行番号を引いていくのですが、既にセルに文字列が入力されている場合は入力を回避します。

次にその行番号に入力の番が回ってくるのは、配列の最後まで引き終えて再シャッフルした後になるので、かなり後回しになってしまいます。2~3周目と回ってきてそこでも入力できなかったら配置がかなり少なくなってしまいます。

このような形で偏ってしまうのを防ぐために、入力が回避された場合は控え用のreserveArrayに行番号が格納され、次回優先的に入力されるようにしました。

コードの掲載は割愛

これまではコードを掲載していましたが、今回は割愛します。

なぜならフォームの分のコードが多すぎてかなり長くなってしまうから。コードだけでなく、コードの各オブジェクトの解説までするのがめんどくさいので、気になる人は実際にダウンロードして見てください。

個人的に今後の参考にしたいコードについては僕がやっている情シスWikiに載せようと思うので、こちらを見ていただいたらまとまっているかもです。

FrontPage - PukiWiki

より多くのカスタマイズが可能です

今回お披露目したサンプルはプライベートで一から作り直して用意したものであり、仕事で作ったものは別にあるのですが、そちらは週間予定を自動で入力できたり、日や曜日で自動入力する人数を増減したりといった多機能なものでした。

それゆえにかなり設定する項目が増えてしまってフォーム全体がごちゃごちゃしちゃったので、このシンプルな去勢バージョンの方が個人的には使いやすそうに思います。

フォームを使うとシートよりも自由にUIを整えることができるので、かなりカスタマイズができますね。フォームを少し使いこなせるようになった気がしました。

コメントをする前にお読みください

  • プログラミングに関するご質問について、直接的なコードの書き方はお答えできかねます。
  • プログラムの作成依頼はランサーズクラウドワークスでご相談ください。
  • スパム対策のため、初投稿の場合はこちらで承認するまでコメント欄に表示されません。
  • 悪質なコメントはこちらの判断で未承認のまま削除します。

コメント

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