使いやすい勤務シフト表
今回から何回かに分けて勤務シフト表を作っていきたいと思います。
筆者自身、事業所が変わるたびにExcelで勤務シフト表を作ってきました。
いくつかの便利な機能を紹介できればと考えています。
今回は、以下のような機能を実装します。
・１カ月の日付と曜日を自動で割り振る
・最終のシートをコピーして新しい勤務表を作る
日付と曜日のセットは、ユーザーフォームを出して年と月を指定する形を取ります。
また、テンプレートからではなく、最終シートから新しい勤務表を作成するのは、社員やパートのメンバーの変更に適応するためです。
今回のプログラムで毎月新しい勤務シフト表を自動で作ることができるようになります。
メニューシート
まずはプログラムの実行を行うためのコマンドボタンを置くシートを１つ用意します。
今回は「メニュー」という名前のシートにしました。
ここにコマンドボタンを設置して、以下のようなユーザーフォームを出して、勤務シフト表の年と月を指定してもらうようにします。
勤務シフト表
上記が今回作る勤務シフト表になります。
列方向に社員、パートの氏名。列方向が日付になります。
日付の先には、勤務日数と勤務時間を表示させます。
また、各日付で何名勤務にあたるかも表示しています。これはその日に必要な人員を確保しているか確認するためです。
作り方
最初に上記2つのシートを作っておきます。勤務シフト表はお好みでご自身の勤務形態の合わせて作ってください。
この勤務シフト表がテンプレートとなります。
氏名の欄は社員やパートの氏名を書き込んでください。最初の月や日付は無くても大丈夫です。
日付や曜日の列や行の位置はプログラムに影響してきますので、今回の例と異なる場合は適宜ずらしてください。
勤務形態に関しては、○、◎、明、休の４つですが、増やしても構いませんが、勤務日数や勤務時間に変更が必要になります。
勤務シフト表 シートのマクロ
VBAで組むブログラムとシート上のマクロの使い分けですが、VBAではプログラムを走らせるきっかけが必要になります。
どこかのセルに変化があった時に実行したいような場合は、Cahngeイベントなどを使いますが、煩雑になる場合がありますので、そのような時はシート上のマクロで実装した方がスマートでしょう。
各日付の出勤者数
日付列の最後に各勤務形態の人数をカウントしています。
○の勤務者(黄色部分)については以下のようなマクロになります。
=COUNTIF(B$5:B$14,”○”)
この程度のマクロであれば、VBAで書く必要はないでしょう。
勤務日数と勤務時間
勤務日数と勤務時間に関しては、別のセルに○と◎で個別に値を算出し、それらを合計した値を反映させています。
もちろん１つのセルだけでマクロを書くこともできますが、非常に長くなり見づらくなります。それを解消するためセルに分散させるのも一つの方法です。
こうする事で項目数が増えた時にも明示的に変更できます。
最初の社員の○の数は、以下のようなマクロになります。
=COUNTIF($B5:$AF5,”○”)
=COUNTIF($B5:$AF5,”◎”)
同じように◎の数も次の列で計算し、最後に「勤務日数」の列で合計値を計算しています。
=$AJ5+$AK5
勤務時間に関しては、○、◎にAK1、AK2の時間数を掛け算して合計値を得ています。
=$AK$1$AJ5 =$AK$2$AK5
=$AL5+$AM5
ここまでは、それほど難しくないと思います。
勤務シフト表新規作成フォーム
勤務シフト表の新規作成は、ユーザーフォームにコードを書いていきます。
標準モジュールにはこのユーザーフォームを開くコードを書きます。
標準モジュール
'/// 標準モジュール
Sub 勤務表新規作成()
UserForm1.Show
End Subユーザーフォーム（UserForm1）
配置するコントロールは以下の通りです。
オブジェクト名はデフォルトの状態で使います。
・テキストボックス（TestBox1） 年を表示
・スピンボタン（SpinButton1） テキストボックスの値を増減させる
・コンボボックス（ComboBox1） 月を表示
・ラベル(Label1,Label2) ”年”と”月”を表示
・コマンドボタン(CommandButton1,2) 作成ボタン、キャンセルボタン
初期化 Initialize関数
最初に初期化のコードを書きます。
'初期化
Private Sub UserForm_Initialize()
'年
Me.TextBox1 = Year(Date)
'月
Dim i As Long
For i = 1 To 12
Me.ComboBox1.AddItem i
Next i
Me.ComboBox1.Text = Month(Date)
End Sub まず最初に表示する「年」と「月」を決めなければいけません。今回は今日の日付から算出します。
今日の日付はDate関数で得られます。
だた、初期値として設定したいのは翌月の値なので、
DateAdd(“m”, 1, Date)
として、1ヶ月後の日付から、Yaer関数で「年」だけを取り出しています。
次にコンボボックスに1から12の値を追加しています。
最後に、「月」を設定しますが、ここでも翌月の値から「月」をセットします。
キャンセルボタン
キャンセルボタンはユーザーフォームを閉じるだけです。
'キャンセルボタン
Private Sub CommandButton2_Click()
Unload Me
End Subスピンボタン
スピンボタンはテキストボックスの「年」を増減させるだけです。
値は文字列扱いなのでCIntで整数にしてから増減させています。
'スピンボタン
Private Sub SpinButton1_SpinDown()
Me.TextBox1.Text = CInt(Me.TextBox1.Text) + 1
End Sub
Private Sub SpinButton1_SpinUp()
Me.TextBox1.Text = CInt(Me.TextBox1.Text) - 1
End Sub
作成ボタン
作成ボタンの処理が今回のメインになります。
少し長いですが順を追って解説します。
Private Sub CommandButton1_Click()
Dim i As Long
Dim shName As String
'最後の勤務表シートをコピーして新たなシートを作る ※１
For i = Worksheets.Count To 1 Step -1
If Worksheets(i).Range("A1") = "○×事業所 勤務シフト表" Then
shName = Worksheets(i).Name
Exit For
End If
Next i
If shName = "" Then ’※２
MsgBox "勤務シフト表が1つもありません。"
End If
Dim newSheetName As String ’※３
newSheetName = Me.TextBox1.Text & "年" & Me.ComboBox1.Text & "月"
Dim sh
For Each sh In Worksheets
If sh.Name = newSheetName Then
MsgBox "既にその勤務表は作成済みです"
Exit Sub
End If
Next sh
'勤務表シートをコピー(最後に追加) ’※４
Worksheets(shName).Copy After:=Sheets(Sheets.Count)
'名前を付ける
ActiveSheet.Name = newSheetName
'日付と曜日を書き込む ’※５
Dim firstDay As Date
Dim aDay As Date
firstDay = DateSerial(Me.TextBox1.Text, Me.ComboBox1.Text, 1)
With ActiveSheet
.Range("B2") = Format(firstDay, "ggge年mm月")
For i = 1 To 31
aDay = DateAdd("d", i - 1, firstDay)
If Day(aDay) < 28 And i > 28 Then
.Cells(3, i + 1) = ""
.Cells(4, i + 1) = ""
Else
.Cells(3, i + 1) = Format(aDay, "d")
.Cells(4, i + 1) = Format(aDay, "aaa")
End If
Next i
.Range("B5:AF14") = "" ’※６
End With
Unload Me ’※７
End Sub ※1では、ブック内のシートを後ろからループして、A1セルが、「○×事業所 勤務シフト表」であるシートを探しています。
これば勤務シフト表シートを選別するためです。
また、最後からループするのは最後のシートが最新のシートになるためです。
なぜ最新のシートを選択したいのかというと、メンバー変更に対応するためです。
勤務シフト表などでは、メンバーが入れ替わることが想定されます。これを考えますとなるべく最新のシートの方が訂正が少なくて済むからです。
※２の部分は、勤務シフト表が見つからなかった場合の処理です。
※3の部分では、これから作る年月の勤務シフト表が既に存在していないかを調べています。
もし既にある場合は、処理を抜けています。
※4で新しいシートをコピーして追加して、新しい年月のシート名にしています。
この段階では、ただコピーされたシートですので前の値が載ったままです。
※5で日付と曜日を新しい値に書き換えていきます。小の月で空白にすべき所は空白にしています。
※6は、勤務シフト部分を空白にしています。
※7で処理は完了したのでユーザーフォームを閉じています。
その他
テキストボックスとコンボボックスで、ユーザーが直接値を編集できないようにLockedプロパティをTrueにしておきましょう。
まとめ
今回は、新しく勤務予定表をつくるための以下の機能を実装しました。
・１カ月の日付と曜日を自動で割り振る
・最終のシートをコピーして新しい勤務表を作る
日付を割り振る機能は、他の用途にも使えそうです。
あらかじめ1年分のシートを作っておくのも悪くないでしょう。ただし社員やパートの人は辞めたり、新しい人が入ってきたりします。人の入れ替わりがあるという点も念頭に置く必要があるでしょう。
さて、次回は〇、◎、明、休などを入力する便利な機能を紹介します。
