シフト表自動作成マクロは前回のバージョンを完全版ということで最後の更新にしていました。あれから１年が経過していたんですね。
このマクロを求めてそれなりに見に来る人がいまして、どこかで使ってもらってるのかもしれませんが、正直言って使えるものではないと思っています。
僕の勤め先でも似たようなものを試してもらったのですが、結局普及には至っていません。
うまくいかない主な意見として、１回のマクロ実行で設定しているすべての配置が入力されるのですが、必ず手直しが必要な箇所が発生し、一つ直すとそれに合わせて別の箇所を直したり……という大きな手間が発生してしまうわけです。
一度雑に組み上がったプラモデルを手直しするのにバラすぐらいだったら一から組んだ方が早いという感じです。プラモデル作ったことありませんが。
一気に入力するのではなく少しずつユーザーのペースで自動入力できればいいのですが、そうするとシートの構成がごちゃごちゃになってしまうなと思って断念していたのですが、フォームを使えば解決すると思ってここに来てアップデートをしてみた次第です。
影響されたツール
影響されたのはこの「シフト助手」というマクロを使ったツール。この動画を見てフォームを使えばいいのかと気付きました。
あと地味に良いなと思ったのがセルをクリックしたら任意の配置を入力でき、右クリックで削除するというもの。ドロップダウンリストから選ぶやつって結構めんどくさくて、結局普通にキータイプで入力してしまいがちですよね。
サンプルファイル
こちらがサンプルファイルです。
バグがあるかもしれません。お気づきのことがあればコメント欄でお知らせください。
軽微なバグについてはひっそりと直してアップし直しますのであしからず。
- 2023-01-06 2月の表を作成した際の「型が一致しません」のエラーを修正しています
- 2023-03-28
- 先頭または末尾の職員を移動した際に、日別集計の行がずれるバグを修正しています
- 勤務・休みモードの表示について、列の指定方法が誤っていたため修正しています
- 勤務・休みモードは切り替え時にシート上でも変更する仕様でしたが、更新ボタンで変更するように修正しています
- 自動入力時に前日の条件指定をしている際のインデックスエラーを修正しています
シンプルにシフト表と日別と月間の集計のみの構成です。必要最低限にまとめました。
実行ボタンの配置場所がしっくりこなかったので、「Ctrl + Shift + a」で実行するようにショートカットを設定しました。
起動するとこのようなフォームが表示されます。
新規シート
「新規シート」ページでは、指定した年月のシートを作成します。
実行するとシフト表の中身がリセットされ、選択した年月に合わせて日付や曜日が変更されます。
設定情報の表示
これ以降に行う設定情報は、すべてシートに記録され、このフォームはシート上にある値を表示しているに過ぎません。
そうした設定情報は見えていると格好悪いので普段は非表示にしています。書式設定を「;;;」にしているだけですが。
右側の設定情報のオプションボタンを表示にすると、このように設定情報が見えました。これらの値を使って自動入力を行います。
職員設定
「職員設定」ページでは各職員の設定ができます。
リストから職員を選ぶと中央の職員名のテキストボックスに職員名が表示され、ここの値を変えて[職員名保存]ボタンを押すと変更されます。
リストの[+]ボタンは最終行に職員を１人追加、[-]ボタンは選択した職員を削除します。[↑][↓]ボタンは選んだ職員の順番を並び替えます。
右側にある勤務配置のリストは自動入力の対象にするかどうかの設定で、リストをクリックするとON/OFFが切り替わります。
勤務設定
「勤務設定」ページで、勤務配置の設定を行います。
勤務名の設定はもちろん、文字色と背景色を設定できます。ここで選んだ色が条件付き書式として設定されます。
勤務と休みのオプションボタンは、自動入力の動作に影響し、勤務は日ごとに指定人数を行ランダムで配置、休みは１人の職員に対し指定回数を列ランダムで配置します。
カウントは日別の人数と月間の回数を指定でき、集計表で赤くハイライトされる条件の他、自動入力で入力される人数および回数にもなります。
自動入力
「自動入力」ページで自動入力を実行します。
人数や回数以外の設定として、前日あるいは翌日にセットで配置する設定と、並んで入力されるのをNGにする設定ができます。
このキャプチャでは「夜勤」の翌日は「休」が入力される設定になっています。「早出」の前日は「遅出」はNGといった設定などもできます。
連勤上限はこの値を上限に勤務が続かないようにするのと、これよりも狭い間隔で休みが入力されます。
手動入力
「手動入力」にチェックが入った状態で、勤務表のセルをクリックすると選択した勤務が入力されます。
これで希望休や予定を入力することが可能で、太字入力にチェックが入っていれば太字で入力されて手動で入力したものだと識別できます。
この太字は見た目だけでなく、自動入力後に入力解除で選択した勤務をクリアすることができるのですが、太字は対象外になっています。
つまり、手動入力した部分だけ残して自動入力をリセットできるというわけです。
自動入力実施
[自動入力]ボタンを押すと、選択した勤務だけが自動入力されます。すべて同時に行われるわけではありません。
夜勤で実行するとこんな感じに、翌日配置で設定した「休」がセットで入っています。
「夜勤」を選択した状態で[入力解除]ボタンを押すと、「夜勤」とセットで入力された「休」がクリアされます。
ここで手動入力で太字になっている「夜勤」または「休」があれば、それらはクリアされません。
一度に自動入力する勤務を分けることで、各勤務の自動入力後に調整ができるので思い通りのシフト表を組みやすくなるという狙いです。
一通り実行した結果がこんな感じです。
入力できなかった場合は次回優先的に入力
自動入力のところはこれまでと同じような仕組みなのですが、一つ大きく変えた部分があります。
入力する行番号が入った配列をランダムにシャッフルし、順番に行番号を引いていくのですが、既にセルに文字列が入力されている場合は入力を回避します。
次にその行番号に入力の番が回ってくるのは、配列の最後まで引き終えて再シャッフルした後になるので、かなり後回しになってしまいます。２～３周目と回ってきてそこでも入力できなかったら配置がかなり少なくなってしまいます。
このような形で偏ってしまうのを防ぐために、入力が回避された場合は控え用のreserveArrayに行番号が格納され、次回優先的に入力されるようにしました。
コードの掲載は割愛
これまではコードを掲載していましたが、今回は割愛します。
なぜならフォームの分のコードが多すぎてかなり長くなってしまうから。コードだけでなく、コードの各オブジェクトの解説までするのがめんどくさいので、気になる人は実際にダウンロードして見てください。
個人的に今後の参考にしたいコードについては僕がやっている情シスWikiに載せようと思うので、こちらを見ていただいたらまとまっているかもです。
より多くのカスタマイズが可能です
今回お披露目したサンプルはプライベートで一から作り直して用意したものであり、仕事で作ったものは別にあるのですが、そちらは週間予定を自動で入力できたり、日や曜日で自動入力する人数を増減したりといった多機能なものでした。
それゆえにかなり設定する項目が増えてしまってフォーム全体がごちゃごちゃしちゃったので、このシンプルな去勢バージョンの方が個人的には使いやすそうに思います。
フォームを使うとシートよりも自由にUIを整えることができるので、かなりカスタマイズができますね。フォームを少し使いこなせるようになった気がしました。
コメント
お世話になっております。
勤務表作成のサンプルファイル活用させていただいております。
現状活用させていただいている上で、マクロから新規シート作成を
行う際、2月分を作成した際に
『実行エラー’13’:
型が一致しません。』
というエラー表示が出る為、ご報告させていただきます。
コメントありがとうございました。
確かに2月の表を作成した際にご指摘のエラーが出ました。
原因としましては、シートの日付を表示する行において、「左隣の日付+1日が1日の場合、空白にする」という関数で処理をしていたところ、2月の場合は左隣が空白になるケースが生じて計算ができずエラーが出てしまっていたようです。
雑な直し方ではありますが、IFERROR関数で予期せぬエラーが出たとしても空白表示にするように修正しました。
ご活用いただきありがとうございます。お気づきの点が他にございましたらコメントいただけますと幸いです。
迅速にご対応いただきありがとうございます。
また、内容についても懇切丁寧にご教示いただき、ありがとうございます。
お世話になっております。
とても参考になるシフト表で勉強になります。
一つ伺いたいのですが、例えば全体で1人ずつだけ配置するシフトだとして日勤の人がいるときは遅出を無くすようなVBAを作るためにはどのようなアプローチが可能か教えていただくことは可能でしょうか。
コメントありがとうございました。
設定をしやすくするのであれば、前日や翌日の条件設定のように勤務を指定できるようにして、１日の各職員の入力状況を走査する際に、指定の勤務であればカウントを減らすといったパターンですかね。
プログラミング自体はそんなに難しくないと思いますが、限られたフォームのエリア内でその設定画面を設けることの方が大変だと思います。
ツールの活用を検討しており、カスタマイズのために不勉強ながらコードの勉強しております。
その際見つけた不具合点のご報告2点と、ご教授頂たい事項が２点ございます。
不具合点ですが、最初と末尾のメンバを上下移動させると、日次集計表の計算式の計算範囲が不当となります。
もう一点は、勤務設定の勤務と休みの設定が、勤務設定タブではなく自動入力タブで選択している勤務名に連動しているようです。
ご教授頂きたい事項２点は以下の通りです。
（１）勤務表のセルを選択すると氏名と日付曜日セルの背景が黄色くなりますが、この制御ロジックがコードのどの部分かわかりませんでした。
（２）勤務表のセルをマウスクリックではなくキーボード入力で更新（デリートや勤務名の入力）した場合の制御コードがどの部分か分かりませんでした。
お忙しいところ申し訳ありませんがご教授願います。
コメントありがとうございました。
先頭もしくは末尾の職員を移動した際の日別集計で行がずれるバグがございました。
行の削除を行う過程で数式が更新され、その数式の行番号を戻していないことが原因でした。対応してアップし直しています。
また、勤務と休みの設定については、自動入力タブで選択している勤務名と連動しているわけではなく、情報を取得する列の指定方法が誤っていたようでした。
ご指摘ありがとうございました。
ご質問については、
(1) 過去の投稿で書いています。 https://www.iehohs.com/excel-auto-shift/
(2) キーボード入力で更新した場合の制御というのが、何を指しているのかがわかりませんでした。詳細を書いていただけますでしょうか。
以上です。
回答ありがとうございます。
(1)については条件付き書式まで考えが至りませんでした。
(2)の制御につては分かり難い表現で申し訳ありませんでした。
具体的には、勤務表セル上でデリートキーを押すと勤務名と背景色が消え、逆に勤務名をセルに書き込むとそれに対応した背景色が設定されるという動作を指しています。
同様に、手動入力モード時にマウスクリックしたセルに勤務名をセットすると文字色背景色が設定されます。この勤務名の文字色背景色を設定したり消したりする制御がコードのトレースでは分かりませんでした。
夜勤を手動入力/削除した時に次の日の休みも同時に設定/削除ができないかと考え、この部分のロジックを調べていた次第です。
以上、よろしくお願い致します。
度々申し訳ございません。
(2)についても条件付き書式ルールで制御していることを確認いたしましたので回答不要です。
お手数をお掛けしました。
佐川優
添付ファイル
16:50 (4 分前)
To 自分
勤務表作成のサンプルファイル活用させていただいております。
2点ご質問させてください。
・1点目
私の会社はシフト制であり、日曜から土曜日のクールで週休2日となっていますが
日曜から土曜日での週休2日での設定はどのようにすればよいでしょうか?
・2点目
私の会社は毎月1日始まりではなく
2023年4月シフトは4月9日から5月6日までとなっていますので
現在のサンプルファイルにてシフト表自動作成マクロで4月分を新規作成して
R2C2セルを2023/4/9とし、R1C31とR1C32とR1C33を消しています。
この場合、シフト表自動作成マクロの勤務設定の休を月8回で保存し
自動入力にて休を自動入力しても日曜から土曜日での週休2日になりません。
お手数おかけしますがどのようにすればよいか教えて頂きたいです。
コメントありがとうございます。
休みの自動入力については、１日～月末までの列番号を、連勤上限以上の間隔にならないようにしながらランダムに配置する仕組みにしています。
日曜日を起算日とした週休２日にする場合、日曜日の列を取得して１週間ずつ２日分の休みを配置するようにすればよいかと思います。
２つ目の質問についても、休みの配置方法が上記のような形なので、１か月あたりの休日数を設定しても必ずしも週休２日になるとは限りません。
そのようにルール付けられるようにコーディングしてください。
ありがとうございます！！
がんばってみます！
こんにちは。
何度も試してみましたがでにません汗
詳しいアドバイスを頂きたいです
すみませんがコメント上で具体的なコーディングについての質問は受けておりません。
自力で頑張ってみてください。