先輩が困っていたのでChat GPTと一緒にシフト作成を自動化させてみた

こんにちは。
IT未経験のデジタル成長記録Vol.2です
今回は、今話題（？）のChat GPT と一緒にシフト作成を自動化してみました。

▼ 前回の記事はこちら【ペットの情報が散乱してたのでLINEbot化してみた】

１．シフト作成自動化に挑んだ経緯

私の職場はシフト制なのですが、先輩が毎月頭を抱えながらシフトを作ってくれています。

　

私が入社したときから、手取り足取りOJTしてくれた先輩に恩返しがしたい!!
そんな思いから私がシフト作成を変わろうかとも思いましたが、私がやったらきっと1日はかかってしまいます・・・・

せっかくデジタルツールについて学び始めたし、デジタルを活かして先輩のお悩み解決をしよう!!

ということで!!
今話題のChat GPTを相棒にして、マクロを使ったシフト作成自動化に挑戦しました！

自動でシフトを作れるようにしてみた～！
連勤術師（）になってる先輩がいるのでちょっと改良が必要…😢#protoout #シフト自動作成 pic.twitter.com/cZNdFLR0HB

— れれ (@tezukar9) July 22, 2024

２．使用ツール

• Excel（マクロ使用）

• Chat GPT

３．Chat GPTへの指示

まずはExcelでシフトを自動作成するためにVBAコードを書かなければなりません・・・。
シフト作成時の必須条件を提示しておきます。

必須条件

• 最大3連勤まで
• 1日に必ず社員1名以上出勤
• 必ず早番1名以上、遅番1名以上
• 月の所定労働時間は社員が168h,Fのうち160h,152h,140h
• すでに入っているシフトは編集しない

この条件を踏まえてシフトを自動入力するためのVBAコードをChat GPTに書いてもらいます!!

Chat GPTへの聞き方

あなたはExcelのプロフェッショナルです。
あなたの目的は私の指示に従って適切なマクロの仕組みを作ることです。

# 最終的に作成するもの
- あなたにシフト自動作成の仕組みづくりを行っていただきます。
- あなたは私の指示に従って、マクロや関数を適切に使用してください。

## 出力に関して
- 完成したものは必ずファイルに保存して下さい。
- 不足している情報があれば聞いてもらって大丈夫です。
- 今まで書いたコードを変形させても良いです。

# ファイルの内容
- A11:A17のセルには氏名が記入されています。
- B11:B17のセルには社員区分が記入されています。
- AP11:AP17のセルには契約時間が記入されています。
- C9:AJ9のセルには日にちが記入されています。
  ここでは来月の21日から翌月の20日までの日付が入力されています。
  例:8月21日から、9月20日までのシフトが入力されている。
- C10:AJ10のセルには曜日が記入されています。
- 重要:すでに入っているシフトは触らないでください。
- 非常に重要:F11:AJ17は直接シフトを入力する場所になります。
  シフトはA11:A17のセルに名前が入力されている行にのみ、入力します。
- F22:AJ41にはその日の行事を書き込みます。
- A4には年、B4には月を入力し、いつのシフトかを特定します。
- B5には社員の月間休日日数が記入されています。
- K5:AQ5は出勤時間を意味します。
- K6:AQ6は退勤時間を意味します。
- K7:AQ7は勤務時間を意味します。
- K4:AQ4はシフトの表示方法を意味します。
- シフトの情報はK4:AQ7のセルに記入されています。（例：早FA→9:30～17:30、早A→9:30～18:30、遅FC→13:30～21:30、遅B→12:30~21:30）
- 契約時間が140時間の人は早FAまたは遅FC、契約時間が152時間以上の人は早Aまたは遅Bで作成してください。
- 連続勤務日数は連続3日までです。
- 3日出勤したら、必ず休みを挟みます。表示は"休"とします。
- 遅FCの翌日に早FAを入れてはいけません。
- 遅Bの翌日に早Aを入れてはいけません。
- 社員区分が"社員"の人が毎日1名以上出勤する必要があります。
- 重要:シフト希望や特定の休み希望はすでにシフト表に入力されています。
  すでに入力されている情報を加味してシフト作成を実施してください。
- 契約時間が140時間の人は早A、遅Bになってはいけません。
- 勤務時間の合計が契約時間を超えてはいけません。
- 社員の早番総数はF18:AJ18で確認できます。
- 社員の遅番総数はF19:AJ19で確認できます。
- OPの全体の早番総数はF20:AJ20で確認できます。必ず1名以上でなければなりません。
- OPの全体の遅番総数はF21:AJ21で確認できます。必ず1名以上でなければなりません。
- 月の早番と遅番はおおよそ半分ずつでなければなりません。
- 社員区分がFの人は月に20日間勤務します。残りの10日または11日は"休"となります。

Chat GPTからの返答

Sub AutoGenerateShift()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("2024年9月度")
    
    Dim namesRange As Range
    Dim contractTimeRange As Range
    Dim shiftRange As Range
    Dim startDate As Date
    Dim endDate As Date
    Dim employeeCount As Integer
    Dim i As Integer, j As Integer
    Dim currentShift As String
    Dim shiftHours As Double
    
    ' 社員の名前と契約時間の範囲を設定
    Set namesRange = ws.Range("A11:A17")
    Set contractTimeRange = ws.Range("AP11:AP17")
    Set shiftRange = ws.Range("F11:AJ17")
    
    ' 日付の範囲を設定
    startDate = ws.Range("C9").Value
    endDate = ws.Range("AJ9").Value
    
    employeeCount = namesRange.Rows.Count
    
    ' シフト希望の保持
    Dim shiftPreferences() As String
    ReDim shiftPreferences(1 To employeeCount, 1 To shiftRange.Columns.Count)
    
    For i = 1 To employeeCount
        For j = 1 To shiftRange.Columns.Count
            shiftPreferences(i, j) = shiftRange.Cells(i, j).Value
        Next j
    Next i
    
    ' 各種ルールを考慮してシフトの割り当てを行う
    For i = 1 To employeeCount
        Dim contractHours As Double
        contractHours = contractTimeRange.Cells(i, 1).Value
        Dim consecutiveWorkDays As Integer
        consecutiveWorkDays = 0
        
        For j = 1 To shiftRange.Columns.Count
            currentShift = shiftPreferences(i, j)
            
            ' シフトが未設定の場合にルールに基づいてシフトを設定
            If currentShift = "" Then
                ' 契約時間に応じたシフトの割り当て
                If contractHours < 152 Then
                    currentShift = IIf(Rnd < 0.5, "早FA", "遅FC")
                Else
                    currentShift = IIf(Rnd < 0.5, "早A", "遅B")
                End If
            End If
            
            ' 連続勤務は最大3日まで
            If currentShift <> "休" Then
                consecutiveWorkDays = consecutiveWorkDays + 1
                If consecutiveWorkDays > 3 Then
                    currentShift = "休"
                    consecutiveWorkDays = 0
                End If
            Else
                consecutiveWorkDays = 0
            End If
            
            shiftPreferences(i, j) = currentShift
        Next j
    Next i
    
    ' 遅FCの翌日に早FAを入れない
    For i = 1 To employeeCount
        For j = 2 To shiftRange.Columns.Count
            If shiftPreferences(i, j - 1) = "遅FC" And shiftPreferences(i, j) = "早FA" Then
                shiftPreferences(i, j) = "休"
            End If
        Next j
    Next i
    
    ' 遅Bの翌日に早Aを入れない
    For i = 1 To employeeCount
        For j = 2 To shiftRange.Columns.Count
            If shiftPreferences(i, j - 1) = "遅B" And shiftPreferences(i, j) = "早A" Then
                shiftPreferences(i, j) = "休"
            End If
        Next j
    Next i
    
    ' 社員が毎日1名以上出勤する
    Dim totalEmployees As Integer
    Dim dayShiftCount As Integer
    Dim nightShiftCount As Integer
    
    For j = 1 To shiftRange.Columns.Count
        dayShiftCount = 0
        nightShiftCount = 0
        For i = 1 To employeeCount
            currentShift = shiftPreferences(i, j)
            If currentShift Like "*早*" Then
                dayShiftCount = dayShiftCount + 1
            ElseIf currentShift Like "*遅*" Then
                nightShiftCount = nightShiftCount + 1
            End If
        Next i
        If dayShiftCount = 0 Then
            ' 少なくとも1人の社員を早番に設定
            For i = 1 To employeeCount
                If shiftPreferences(i, j) = "休" Then
                    shiftPreferences(i, j) = "早A" ' または他の早番
                    Exit For
                End If
            Next i
        End If
        If nightShiftCount = 0 Then
            ' 少なくとも1人の社員を遅番に設定
            For i = 1 To employeeCount
                If shiftPreferences(i, j) = "休" Then
                    shiftPreferences(i, j) = "遅B" ' または他の遅番
                    Exit For
                End If
            Next i
        End If
    Next j
    
    ' 各社員のシフトをセルに書き込む
    For i = 1 To employeeCount
        For j = 1 To shiftRange.Columns.Count
            shiftRange.Cells(i, j).Value = shiftPreferences(i, j)
        Next j
    Next i
    
    ' ファイルを保存
    ThisWorkbook.Save
    
    MsgBox "シフトが自動生成され、ファイルが保存されました。", vbInformation

End Sub

４．ExcelでVBAを使う

さて、VBAコードをゲットしたのでさっそくExcelに入れ込んでいきましょう！

1. AltとF11を同時に押して、VBAを起動します。

2. 挿入タブから標準モジュールを選択し、先ほどChat GPTから来た返答をコピペします。
   

3. 上書き保存をします。
   

4. AltキーとF8キーを同時に押して、マクロのフィールドを開きます。
   

5. 実行を押せばマクロが実行されます！
   

５． マクロ実行ボタンの作成

この4-4 と 4-5の作業するの面倒ですよね？
ここも効率化しちゃいましょう！

1. 開発タブのコントロールにある挿入をクリック。
   

2. ちょうどいいボタンを選択して、エクセル内の好きな位置に配置します。

６． 完成品

７． 最後に

自分の力だけじゃ絶対にできなかったシフト作成自動化
Chat GPTの力を借りてなんとか形にできました！
実際に先輩へ送ってみたところ、大喜び！
毎月3時間ほどかけていた業務を10分で終わらせられるので、他の業務に手が回りそうとのことでした

ここまで到達するのにかなりの時間を使ってしまいまして、実はこれがVer.4.0？くらいです（笑）
Chat GPTと時には喧嘩しながら、コツコツやり取りを続けた甲斐がありました

実際にシフトを作っている先輩に使ってみてもらい、問題点をピックアップしてもらい続けて試行錯誤した成果物ですが、ここでタイムアップ…。
提示した必須条件をきちんとクリアできていない部分もあるので、今後改良していきます

問題点

• 3連勤以上になってしまう人がいる
• 出勤日数がバラバラになってしまっている（所定休日数が取れていない）
• 希望が入っている場合に3連勤防止関数が動いてない