マクロ無しでシフト表作成ツールその1(使用方法編)

VBAを触ること20年近くになると何でもマクロ組むかとなってしまう癖がついてしまう。「長い関数入力するのは億劫だな。ユーザー定義関数を作ろう。」「この操作は手間がかかるな。ショートカットマクロ作ろう。」などだ。

別に悪くはないが、世にはマクロ使用禁止の職場もあるし、実装の重複で効率がいいとは言えない。何よりExcel操作が鈍るのは避けられない。ということでマクロ禁止縛りでシフト表作成ツールを作ることにした。

以降敬語。

2024 5/18追記

配布していたツールをバージョンアップしました。シフト数は5つまで、メンバーは22人まで対応したものです。シフトインジケータも新しくなっています。詳しい操作方法はリボン内のShiftMaker2をご覧になって下さい。配布はここから可能です。

シフト表作成ツールの概要

シフト表作成ツールを作るに当たって、以下の条件を満たすように努力することにしました。

1. マクロは使用しない
2. 入力は少なく・操作は簡単にする
3. 希望シフトと希望休日を反映する
4. 作成したシフト表は印刷して配ることとする

そうして土日の数時間を使用しできたのがこれです。リンクをクリックするとダウンロードが可能です。ライセンス表記はしていませんが表記の不要なMITライセンス用な感じで自由に使ってもらって構いません。

このツールは西暦、従業員、希望シフト、希望休日、定休日を入力するとシフトシミュレータが叩き台のシフト表を自動作成します。希望のシフト表ができるまで再計算実行をクリックすることでシフト表を再作成します。希望したシフト表に近いものができた場合、シフトコードをコピーし、テンプレートシートをコピーしたシート上のコードにシフトコードを値として貼り付けて、微調整をしていく方法を取っています。

各従業員の希望シフトを決めるアルゴリズムは希望シフトがある場合はまず希望シフトを50％の確率で抽選し、外れた場合はもう一度すべてのシフト(休みを含んだ)で抽選を行い、シフトを決定します。

ひとまずシフトは2つのみにしていますが、入力されている関数を少し変更することで26個まで対応が可能です。(現実的にはマクロなしでは5つが限界かと思います。)

シフト名は次に紹介するコード化の為に必ずアルファベット1文字で構成します。

コピペの範囲を小さくするためにコード化を行っているのがこのシフト表作成ツールの最大の特徴です。このコードのみを該当セルに貼り付けるだけでシフト表が反映されます。

希望休日は確実に反映されるようになっています。(6日間まで)

微調整は各従業員の日付セルにリストを設定しているのでそこからシフトを選び、微調整を行います。一度リストで微調整すると関数がリセットされます。

使用方法

1.テンプレートシートをコピーし、シート名を月の番号にする。西暦を入力しておく。

2.シフトシミュレータにシフト種類(2つ以上は後述する方法で関数を変更する)と従業員名、希望シフト、月、定休日(欠日)をリストから選択入力する。

3.再計算実施を何度かクリックして希望に近いシフトを自動作成し、シフトコードをコピーする。

4.1でコピーしたシートのP2セルにシフトコードを値として貼り付ける。

5.2で入力した従業員名が反映されていることを確認したら、希望休日を入力していく。

6.勤務人数と各シフトインジケータが正常(異常の場合はオレンジに変化。条件付き書式で制御。)になるように各従業員の日付セルをリストから選択入力していく。

7.完成したら印刷する。(このシフト表作成ツールでは状態保存が完全にできないので必ず印刷等で記録を残してください。)

月毎のシートでは希望休日をアウトライン化しているので、表示領域が狭いと感じたら折りたたむことが可能です。尚、印刷時にも折りたたむことを推奨します。

シフトを増やす場合はchoose関数などいくつかの関数を触る必要があります。詳しい内容は次回に説明します。