【Excel VBA】シフト表自動作成マクロを二次元配列で高速処理にしました

このブログの人気記事は相変わらずシフト表自動作成マクロに関する記事です。よっぽど勤務を組むのに苦労している人が多いようです。

Excelのセル、すなわちオブジェクト上で情報の書き込みを行うと結構処理が遅くなります。

そういう時は変数や配列を使って処理を行うと速くなります。前回作ったものも配列を使ってましたが、条件を満たす度にオブジェクトに書き込んでいたため、完了までに時間がかかってました。

また、複数の配列を使ってインデックス番号で配列同士を関連付けしていましたが、配列がたくさんできすぎてややこしくなっていました。

そこで勤務表や設定表を二次元配列に格納して配列上ですべて処理を行い、オブジェクトへの書き込みは1回にまとめて処理を高速化しました。

成果物はこちらからダウンロードしてください。

スポンサーリンク

使い方

こんな感じのレイアウトに作り変えました。

メインとなる勤務表、その右に職員の1ヶ月分の集計、下には各日付の配置の集計、集計表の右に職員ごとに配置の有無を設定する表、さらにその右上に条件を設定する表があります。

新規シート

新規シートボタンを押すと西暦と月を入力し、内容がリセットされて日付と曜日を整えられた新しいシートができあがります。

職員追加

職員追加ボタンを押すと最後の行に追加します。集計表と設定表の数式や値は自動で入力され、氏名は1列目に入れたら集計表と設定表にも自動で表示されます。

マクロの設定

勤務表の各セルはドロップダウンリストでの選択式です。

「夜」「早」「遅」が入力されるとセルの背景色が変わるように条件付き書式を設定しています。

自動入力では既に入力されたところはセットしないようにしているため、希望休や勤務希望をあらかじめ入力した上でマクロを実行します。

設定表では各配置の人数を決められます。夜勤がない場合は人数を0にしておけば処理は行われません。

各職員の各配置について、入力の対象であれば「1」を入力します。

右の条件はそれぞれ以下のような内容です。

夜勤労働数1労働, 1.5労働, 2労働夜勤労働数によって処理が変わります
連続夜勤あり, なし1労働夜勤における連続夜勤の設定です
値にかかわらず、1.5労働の場合はあり、2労働の場合はなしに変更されます
連続夜勤後連休あり, なし1労働夜勤における、連続夜勤の後は連休にする設定です
1.5労働と2労働の場合は明け→休みとなるためこの設定は無効です
遅→早禁止あり, なし遅出の翌日が早出になることを禁止します
ありで禁止、なしで遅→早の配置を許可します

2021年4月17日機能追加:公休のON/OFF

この記事を投稿した時点のものと変更している点があります。

公休の下が0か1の選択式になっています。他の項目では人数の設定ですが、ここでは1ならON、0ならOFFの切り替えです。

公休の振り分けを行わない場合は0にすればスキップされます。ただし、夜勤の翌日以降の公休については行われますので注意です。

マクロの実行

自動入力ボタンを押すと一瞬でできあがります。前回のものとは比にならない速度です。

VBA

すべて1つのモジュールにまとめました。

今回はかなり丁寧にコメント付けたので改造したい人は頑張ってください。

ハイライトのこととかは前回やったので割愛します。

二次元配列への格納

二次元配列へ格納するには、配列をVariant型で宣言しておき、Rangeメソッドで指定して範囲を代入すれば一発で格納できます。

ただ、この場合だとデータ型を指定できないため、それにより不都合がある場合は今回のようにループで1つずつ格納していくことをおすすめします。そんなに時間はかかりません。

今回の場合だと、空白セルは””で格納してほしいところがEmpty 値になっちゃうのがいやだったので、配列をString型で宣言してループで格納しました。

コメント

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