アルバイトのバイトシフト表をゼロから関数とVBAで作成|曜日毎レギュラー自動シフト割り、ヘルプ対応

アルバイトのバイトシフト表概要

アルバイトのバイトシフト表をゼロから作成しました。各アルバイトがレギュラー出勤する曜日が決まっていれば、自動的にシフトを割り振ることができます。また、ヘルプに対応しており、通常出勤しない曜日にヘルプ出勤する場合は、手動で調整ができるエクセルアプリです。そこそこ便利だと思います！

各アルバイト毎のレギュラー曜日によるシフトの割り振り、ヘルプの対応、日毎のアルバイト数合計まではワークシート関数のみを使っています。編集モード・閲覧モードの切り替え、土日背景色の変更、ヘルプのリセットは、VBAを使用しています。

B２セルをクリックすると「編集モード」と「閲覧モード」が切り替わります。
「編集モード」は、その名の通り、シフトを編集する時に使います。「閲覧モード」は、不要な行を非表示にしますので、閲覧に適しています。

※Youtube類似動画https://youtu.be/6UEIGMRm0cs

編集モード

閲覧モード

出勤形態をレギュラーとヘルプに分類しています。
レギュラーとは、具体的には、何曜日に出勤するのか決まっていることをレギュラーと言います。例えば、山田太郎さんは、月・火・木、山田花子さんは水・木・金が通常出勤する曜日だとしますと、新しい月のシフトを組む際には、自動的に、その月の該当日にシフトが自動的に振り分けられます。
ヘルプとは、具体的には、通常出勤しない曜日ですが、人手が足りないなどで出勤することを言います。例えば、通常、山田太郎さんは、月・火・木がレギュラー出勤曜日ですが、2020/8/1（土）は人手が足りないなどの理由で出勤する場合は、8/1ヘルプ欄に1と入力するとシフトが編集されます。逆に、月レギュラー出勤するのが普通ですが、用事などで通常出勤する曜日に出勤できない場合は、ヘルプの該当日付に-1と入力すると出勤から外れます。

最下行には、バイト人数の合計数が表示されるようになっています。

このエクセルアプリは、ワークシート関数とVBAが混合していますが、内容は難しいものではありません。

各月1日から最終日までの自動生成

E1セル、G１セルに年、月を入力すれば、自動的に該当年月の１日から最終日までの月日・曜日を表示することができます。
2020年8月であれば、8/1～8/31までの日付を2行目に、その曜日を３行目に表示します。

E2セルからAI2セルまで該当月の1日から31日分の月を表示させます。
赤線の各セルには、DATE関数とCOLUMN関数を使い、　=DATE($E$1,$G$1,COLUMN()-4)　と入力しています。
DATE関数は、DATE(年,月,日)で日付を表示しますので、年にはE1セル、月にはG1セルの値を格納しています。
COLUMN関数は、その該当セルの列番号を得る関数です。5列目のセルであれば、5を得ることができます。5列目のセルを1日にしたいのであれば、COLUMN() – 4 で１を得ることができます。このように日は、E2セルからAI2セルまで、(その列番号 マイナス 4)として1～31を得ています。E1,G1セルは絶対参照ですから、$E$1、$G$1のようにドル記号を付けます。
セルの書式設定として、下イメージのようにしています。

E3セルからAI3セルまでは、上の行の日付が何曜日かを表示させます。

E3セルに　=TEXT(E2,”aaa”)　と数式を入力しています。これで日～土までの曜日を得ています。

各アルバイトのレギュラー曜日のシフト振り分け

各アルバイトが通常出勤する曜日があれば、緑枠の中に、日～土までの曜日を入力します。各アルバイトの緑枠内の曜日と日付下の曜日を比較して、等しければ「1」を表示します。具体的には、山田太郎さんは、月火木曜日（緑枠内）レギュラー出勤します。D4セルに「月」と入れますと、8/1～8/31内の月曜日にはG4セルのように「1」と自動的に出力します。G4セルは　=IF($D4=G$3,1,””)　となっています。D4セルが「月」で、G3セルも「月」ですから、「1」を表示します。E4セルは「土」、J４セルは「木」ですから、何も表示されません。このような数式が入力されていますので、レギュラーとして、指定する曜日があれば、自動的にシフトを割り振ることができます。

ヘルプ対応

レギュラー曜日以外に出勤する場合は、該当する日付のヘルプ行に「1」と入力します。具体的には、山田太郎さんは、月火木がレギュラー出勤しますが、8/1（土）人手が足りないなどで出勤する場合は、①のようにヘルプセルに「１」と入力します。
その逆に、レギュラー曜日でも、用事があるなどで欠勤する場合は、「-1」と入力します。

アルバイト各個人の出勤の判定

出勤行のセルは、レギュラーセルとヘルプセルの値を加算した結果が0以上であれば、「1」を出力し、それ以外は何も表示しません。
レギュラーセルに「1」且、ヘルプセルが「空白」であれば、出勤セルは合計値として「1」のため、「1」が表示されます。
レギュラーセルが「空白」且、ヘルプセルが「1」であれば、出勤セルは合計値として「1」のため、「1」が表示されます。
レギュラーセルに「1」且、ヘルプセルが「-1」であれば、出勤セルは合計値として「0」のため、何も表示されません。
閲覧モードでは出勤行だけが表示されます。

日毎のアルバイト人数合計

最下行の日毎のアルバイト数合計は、SUMIF関数を利用しています。C4セルからC28セルを縦に探索していき、「出勤」行のみの合計を取得しています。

VBAの準備

Altキーを先に押してからF11キーをクリックしてください。エディターが開きます。左上の方の緑枠「Sheet1」をクリックしてください。

①の箇所をWorksheet、SelectionChangeとします。
数値型変数　i,  j,  person  　を定義しています。person=5 とは、バイトの人数が5人ということです。

図1

編集モード・閲覧モードの切り替え

図1内②の箇所抜粋します。

B1セルをクリックすると、編集モードと閲覧モード文字を入れ替えています。また、背景色もInterior.Colorで設定しています。
閲覧モード時は、レギュラーセルとヘルプセルの行高さを0として非表示としています。
編集モード時は、レギュラーセルとヘルプセルの行高さを15として表示されるようにしています。

ヘルプリセット

新しい月のシフトを組むときに、ヘルプを手動で消去するのは大変かと思います。ヘルプリセットセルをクリックするとヘルプセルは全て、空白となります。図1③の箇所です。

土日背景色変更

年あるいは月を変更した際に、土日の背景色を自動的に変更します。土曜日は水色、日曜日はピンク色に背景色を変更します。
月の値を入れなおしEnterキーをクリックするとカーソルは、2行目7列目に移動します。
年の値を入れなおしEnterキーをクリックするとカーソルは、2行目5列目に移動します。
これを利用しています。
For ループで31日分探索し、「土」あるいは「日」であれば、背景色をInterior.Colorで変更しています。それ以外の曜日は、背景色を白色に設定しています。

ご拝読ありがとうございました。