Excel VBAを使用した勤務表の作成

質問

現在、月毎にシート別になっている勤務表の管理をしており、初心者ながらExcel VBAを使って効率的に作成したいと思っています。
勤務表は1行目に日付、A列に従業員の名前が入っています。
その中でつまづいている箇所がありますので以下の2点について教えてください。

① 従業員の中に三交代制の従業員がいます。
　A、B、Cの3つの班に分かれており、その日がどの班なのか分かるように日付の下の行に「A B C A B C ･･･」と順番に記載したいですがどのようにしたら良いでしょうか？
　また、この時に月を跨ぐとAから始まらない(前月がBで終わると翌月1日はCから始まる)と思うのですが、シートを跨いでABCの3つが続いていくようにしたいです。

② 前記①が完了したら、三交代制の従業員の隣にそれぞれABCの表記をし、日付のABCと従業員のABCがそれぞれ交わる日のセルに「出勤」と入れたいです。

職場のパソコンで作業しているため、現在手元にデータがなく、参考となる画像がつけられずに分かりづらいとは思いますがご回答頂けると助かります。
よろしくお願いします。

回答No.6

kkkkkm

回答No.5の訂正です。

1月の勤務表を作成するのは前年だと思いますから(No.5では年は作成している時点での年でいいだろうと思ってました)

Cells(1, i + 2).Value = Format(Range("A1").Value & "/" & Range("B1").Value & "/" & i, "mm/dd")

の所をA1で指定している年になるように

Cells(1, i + 2).NumberFormatLocal = "m月d日"
Cells(1, i + 2).Value = DateSerial(Range("A1").Value, Range("B1").Value, i)

に変更してください。

回答No.5

kkkkkm

回答で添付したシートの状態でのVBAです。

VBAで作成したい勤務表シートの
A1に年(2024などの数値)
B1に月(1などの数値)
を入力してから実行

VBA実行時には作成したい勤務表シートを表示していること
シート名は1月、2月のように数値月とすること
が最低条件になります。

A,B列の従業員名と班はVBAでは入れません。
出勤表示のセルは31行目までとしています。

Sub Test()
Dim LastDay As Long
Dim i As Long
Dim Ws As Worksheet

If Range("B1").Value > 1 Then
Set Ws = Sheets(Range("B1").Value - 1 & "月")
Range("B2").Value = Ws.Cells(2, Day(DateSerial(Ws.Range("A1").Value, Ws.Range("B1").Value + 1, 0)) + 2).Value
Set Ws = Nothing
Else
Range("B2").Value = ""
End If

LastDay = Day(DateSerial(Range("A1").Value, Range("B1").Value + 1, 0))

For i = 1 To LastDay
Cells(1, i + 2).Value = Format(Range("A1").Value & "/" & Range("B1").Value & "/" & i, "mm/dd")
If Cells(2, i + 1).Value = "" Or Cells(2, i + 1).Value = "C" Then
Cells(2, i + 2).Value = "A"
Else
Cells(2, i + 2).Value = Chr(Asc(Cells(2, i + 1).Value) + 1)
End If
Next

Range("C3").Formula = "=IF(C$2=$B3,""出勤"","""")"
Range("C3").Copy
Range("C3").Resize(30, LastDay).PasteSpecial xlPasteFormulas
Application.CutCopyMode = False
Range("A3").Select

End Sub

回答No.4

kkkkkm

回答No.3の一部訂正です。

3月のB2の式は
=INDIRECT("'2月'!" & ADDRESS(2,(COUNTA('2月'!C2:AE2)-COUNTBLANK('2月'!AE2))+2))
より

=INDIRECT("'2月'!" & ADDRESS(2,(COLUMNS('2月'!C2:AE2)-COUNTBLANK('2月'!AE2))+2))
がいいかもしれませんし

2月はうるう年でも29日なので直接最大日数の29を入れておいてもいけると思います。

=INDIRECT("'2月'!" & ADDRESS(2,(29-COUNTBLANK('2月'!AE2))+2))

回答No.3

kkkkkm

> VBAを使用して翌月のシートを作成し…と言う形

最初に1年分のシートを作成しておきそのブックをひな形として保存しておいて毎年そのブックのコピーを利用するのかなと思ってました。
(私が上記のようなかたちで利用をしているブックがあるので)

2月のうるう年対応しておけば万年カレンダー的に使えると思いますし、以後の勤務表作成はA列の従業員名とB列のABC(ABCがバラバラになるとしたら)の記入だけで済むと思います。VABを実行する必要もなくなります。

2月と3月は一部だけ他のシートと違う式になります。

2月ですが日付の部分を
(1月シートのA1に2024とかその年の年を入れるようにしておいて)
C1に
=DATE('1月'!A1,2,1)
とし
D1を
=C1+1
として右に29日分コピーします。
29日のセル(AE1として)の式を
=IF(MONTH(AD1+1)=2,AD1+1,"")
とします

AE2の式を
=IF(AE1="","",IF(OR(AD2="",AD2="C"),CHAR(65),CHAR(CODE(AD2)+1)))
とします。

これで2月がうるう年対応になると思います

3月のB2の式を
=INDIRECT("'2月'!" & ADDRESS(2,(COUNTA('2月'!C2:AE2)-COUNTBLANK('2月'!AE2))+2))
としておけば2月の最終日のABCが表示されます。

それで入力するセル以外はロックしておくといいと思います。

VBAでもそれほど面倒ではないと思いますが、どこまでVBAでやるのかセルの位置がどうなっているのかが分からないとセル位置が適当なコードになりますので修正が面倒かもしれません。
私の表の状態で作成してよければ挑戦してみますが、
コードを実行するときに完全にまっさらなシートで実行する(あらかじめ従業員名とかがあっても可)
「出勤」は関数で表示するようにする

B列をどうするのかも(元々ABCと並ぶのかバラバラで後入れなのか)不明なのと、どの行まで対応するのかという情報も必要になります。

回答No.2

kkkkkm

回答No.1で2月の画像を忘れてました。

B2に
='1月'!AG2
としているので1月31日が「A」でしたので「A」が表示されてC2以降の2行目のA,B,Cや他のセルも自動で変更されます。

回答No.1の画像でF3が空白ですが式を入れ忘れてました実際は「出勤」と表示されます

回答No.1

kkkkkm

VBAでなくて数式でできると思います。
セル位置は添付画像をもとにしています。
C2に
=IF(OR(B2="",B2="C"),CHAR(65),CHAR(CODE(B2)+1))
として右へ必要なだけコピーします。
B列もABCと並ぶのでしたらB3にも同じ式を入れて必要なだけ下にコピーします。
C3に
=IF(C$2=$B3,"出勤","")
として右と下に必要なだけコピーします。

12か月分同じシートを作成して

2月のB2に1月の最後のセルを参照させます。
='1月'!AG2
3月分以降もB2に前月の最後のセルを参照させて出来上がりです。

補足

ありがとうございます。回答No2も拝見しました。
関数での作成方法、参考にさせていただきます。
今後、勤務表作成の業務をわたし以外の人がやることになり、エクセルが苦手な人でも業務の負担にならないよう、VBAを使用して翌月のシートを作成し…と言う形で効率化できれば良いな(エラーが出たらどうする云々は抜きとして)と思っている面もあるのですが、コードでの作成は複雑化してしまいますでしょうか？