勤務シフト作成を無料で自動化してみたい

シフト作成って結構メンドクサイ......

私は小売業に勤めているのですが、どうしても避けて通れないのがこのシフト作り。
しかも一番人数が多い部署なので時間もかかってしまいます......
誰か代わりに作ってくれないかな～と思って目に入ったのが今話題のChatGPTです。

今回はシフト作成を「無料」で「自動化」するというテーマで記事を書いてみました。

シフト作成の時間を短縮するヒントがきっとあると思うのでぜひ参考にしてください。

使用したツール

• ChatGPT
• Google Sheets

実際、ChatGPTをどのようにして使ったのか？

どのようにして使用したか、順を追って説明しますね。
まず私はChatGPTに以下の命令を出しました。

あなたはグーグルスプレッドシートのスペシャリストです。
あなたの目的は私の指示に従って適切なグーグルスプレッドシートの仕組みを作ることです。

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

## 出力に関して
- 完成したものは必ずファイルに保存して下さい。
- 私が添付したファイルを使って作業してください。他のファイルは使用しません。
- 不足している情報があれば聞いてもらって大丈夫です。

# ファイルの内容
- Aのセルには氏名が記入されています。ただし、"朝オペさん"、"氏名"、"寿司"、"弁当"、"ホット"は名前ではありません。
- Bのセルには社員区分が記入されています。
- Cのセルには契約時間が記入されています。
- Dのセルには基本時間が記入されています。
- Eのセルには休憩時間が記入されています。
- Fのセルはないものとして考えてください。
- 重要:今回シフトを作成する際、Aのセルにのみ着目してください。
- G3:AK3には日付が記入されています。ここでは今月の11日から翌月の10日までの日付が入力されています。例:6月11日から、7月10日までのシフトが入力されている。
- 非常に重要:G6:AK29は直接シフトを入力する場所になります。シフトはAのセルに名前が入力されている行にのみ、入力します。
- G5:AK5にはその日の行事を書き込みます。
- 行の10,16,21は何も入力しません。
- AN1には年、AN2には月を入力し、いつのシフトかを特定します。
- AL6:AM23は出勤時間を意味します。ALとAMはそれぞれ隣同士で意味を持っています。例:Aであればその日は6:00に出勤する
- AN6:AN29までに入力された関数はエクセル関数なので気にしないでください。

不明な点はありますか？

こちらの命令はまず、AIにどのような役割を持ち、何を成し遂げてほしいのか。
また、以下のGoogle Sheetsをセルごとに分け、AIにわかりやすい表現で細かく説明したものになります。

マークダウン記法で命令すると重要な個所をしっかりと理解してくれます。

AIにとってすべての言葉はただの文字列に過ぎず、丁寧に説明してあげる必要があります。

(リンゴという言葉は私たちでは脳内で「赤い」や「甘い」などの言葉を連想させ言葉自体に意味が生まれますが、AIにとってリンゴという言葉は「リ」「ン」「ゴ」で構成される単なる言葉に過ぎません)

ちょっと手間ですが、優しい心で丁寧に教えてあげましょう。

すると....

ながーいメッセージと共にファイルの中身を分析してくれたみたいです！
うまく伝わっているようで一安心。

まずは腕試し！

うっかりしていました。最初に送った命令文の中に１つ指示を入れ忘れてしまいました。
以下の文章を送ってみようと思います。

ありがとうございます。
一点だけ言い忘れていました。

G4:AK4が空白になっていると思います。ここには日付であるG3:AK4に対応した曜日が入力されるのですが、まずあなたにこの部分を仕組化していただきたいと思います。以下の条件に従って作成してください。

# 条件
- AN1の年、AN2の月に従った曜日になるようにすること。
例:2024(AN1)年、6(AN2)月と打ち込めば6月11日から7月10日までの曜日が自動で入力される。
- 非常に重要:1か月は31日、30日、29日、28日の月があります。このシフト表には31日まで入力しているので間違えないように注意してください。存在しない日付には何も入力しないようにしてください。
例:2024(AN1)年、6(AN2)月と打ち込むと6月31日は存在しないのでこの日付には曜日を入力しないようにする。

この命令文ではG4:AK4に曜日を当てはめてもらおうという命令文です。
毎年同じ曜日ならいいんですけどね。

うまく伝わってくれるかな...と心配になるときは例:を添えてあげるとよいです！

返答はこちら

とここで壁に当たってしまいます。
このGASがまったく機能しないのです。(実行しても空白のまま)
元々固定された日付に曜日を当てはめる行為が難しそうだなと思い路線変更を決意。

それでは以下の条件に変更します。

# 条件
- AN1(年),AN2(月)に応じてG3:AK3の日付、G4:AK4に曜日が入力されるようにしてください。
- 日付はセルに日のみが記載されるのが望ましいです。
例:2024/06/11なら、G3のセルには11日のみが表示されている。

以上のことをふまえ、GASを再度生成してください。

この命令文では日付も曜日も両方打ち込んでもらうGASの生成をお願いしました。
すると、以下のGASを生成してくれました。

function setDatesAndWeekdays() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var year = sheet.getRange('AN1').getValue();
  var month = sheet.getRange('AN2').getValue();
  
  if (year == '' || month == '') {
    Logger.log('年または月が入力されていません。');
    return;
  }

  var startDate = new Date(year, month - 1, 11); // 当月11日から
  var endDate = new Date(year, month, 10); // 翌月10日まで
  var column = 7; // G列
  var dayNames = ["日", "月", "火", "水", "木", "金", "土"];
  
  while (startDate <= endDate) {
    var date = startDate.getDate();
    var dayOfWeek = startDate.getDay();
    
    // 存在しない日付は空白にする
    if (startDate.getMonth() + 1 !== month && date > 10) {
      sheet.getRange(3, column).setValue('');
      sheet.getRange(4, column).setValue('');
    } else {
      sheet.getRange(3, column).setValue(date); // 日付を日のみ表示
      sheet.getRange(4, column).setValue(dayNames[dayOfWeek]); // 曜日を表示
    }
    
    startDate.setDate(startDate.getDate() + 1);
    column++;
  }
}

このGASを打ち込むと...
(「拡張機能」→「Apps Script」→スクリプトエディタが開いたら、上記のスクリプトを貼り付け、スクリプトを保存し、実行の手順でできます。)

うまくできましたー！
AKのセルは余分なので手動で黒く塗りつぶしましたが、日付の数・曜日もあっているので大満足！

本題！シフト作成にチャレンジ！

また命令文をぶん投げます。

それではここからが本題です。
あなたにシフト作成の自動化をしていただきます。
まずは11行から14行の人物のシフトについてです。
以下の条件に従ってください。

# 条件
- ㎞,im,sm,iyはそれぞれ週休２日制です。
- 火曜日、土曜日、日曜日は人時が3人になるようにしてください。
- 非常に重要:シフト表にあらかじめ「〇」が打ち込まれている場合、希望休になるので勤務日にしてはいけません。
- シフト表に「〇」が打ち込まれているか確認した後、シフト表を作成するGASが望ましいです。
- 最大連勤数(最大連続出勤数)は「4」です。しかし、smは例外です。
- km,im,sm,iyはシフトの中で22回出勤するようにしてください。
- G4:AJ14がシフト作成範囲です。
- 出勤日はそれぞれのセルに記載されているEの基本時間を参照してください。例:kmの出勤日はD11を参照し「7A」で表す。

今回のシフトのルールは

• 「2～4日出勤し、1回休日、再度2～4日出勤し.....」
• 「火・土・日は4人中3人出勤させる」
  というシフトです。
  そして生成してくれたGASを貼り付けて実行した結果...
  
  7Aなど書いてあるところが出勤日なのですが...休みが4日しかない人もいて厳しいシフトになってしまいました。
  何度か命令文を書き換えたのですがうまくいかないので恐らく考え方を改めた方がよさそうです。
  というわけで視点を変え、以下のような文章に変更しました。

こちらの条件したがってGASを生成して下さい。
しっかり目を通してくださいね。

- G4:AJ14がシフト作成範囲です。
- kmは４のセルを参照し、まず「水」と書かれている列に「〇」を打ち込んでください。そして既定の8個には満たないので、「土」と書かれている列に残りを入力してください。
- imは４のセルを参照し、まず「木」と書かれている列に「〇」を打ち込んでください。そして既定の8個には満たないので、「月」と書かれている列に残りを入力してください。
- iyは４のセルを参照し、まず「金」と書かれている列に「〇」を打ち込んでください。そして既定の8個には満たないので、「月」と書かれている列に残りを入力してください。
- smは４のセルを参照し、まず「日」と書かれている列に「〇」を打ち込んでください。そして既定の8個には満たないので、「火」と書かれている列に残りを入力してください。
- 「〇」は「休日」を指します。
- 「〇」が当てはまらない空白のセルは名前に対応しているＤのセルを参照し、名前ごとに正しいものを打ち込んでください。例:kmであればＤのセルに7Ａが打ち込まれているので、「〇」以外の個所は7Aを打ち込む。これを全員に対して行う。
- 「〇」は1つの行につき、8個までです。
- 非常に重要:「〇」があらかじめセルに入力されている場合、「希望休」になります。「希望休」は最優先され、「〇」位置は変更しないでください。
- 1つの列につき、「〇」は1つです。ただし、上記の条件で「〇」が仕方なく被ってしまう場合は考慮しません。また、「〇」が１つもない列がある場合、分配の仕方を変更するようにしてください。

休日を打ち込んでもらって、空白に出勤日を打ち込んでもらうという考え方に変更しました。

具体的に指示しないと理解してくれません！
しっかり文で表現してあげましょう。
　
そして生成されたGASがこちら

function createShift() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var startColumn = 7; // G列
  var endColumn = 36; // AJ列
  var startRow = 11; // 11行目から14行目
  var endRow = 14;
  var offDaysPerPerson = 8;

  // 人物のリストと対応する出勤時間
  var persons = [
    {name: "km", shift: "7A", row: 11, primary: "水", secondary: "土"},
    {name: "im", shift: "6.5E", row: 12, primary: "木", secondary: "月"},
    {name: "iy", shift: "7C", row: 14, primary: "金", secondary: "月"},
    {name: "sm", shift: "7G", row: 13, primary: "日", secondary: "火"}
  ];

  // 曜日の列番号を取得
  var dayColumns = {};
  for (var col = startColumn; col <= endColumn; col++) {
    var day = sheet.getRange(4, col).getValue();
    if (!dayColumns[day]) dayColumns[day] = [];
    dayColumns[day].push(col);
  }

  // 各人物の出勤情報を初期化
  var shifts = {};
  for (var i = 0; i < persons.length; i++) {
    shifts[persons[i].name] = {
      name: persons[i].name,
      daysOff: 0,
      row: persons[i].row,
      shift: persons[i].shift,
      offDays: []
    };
  }

  // 希望休（既に打ち込まれている〇）を確認し、カウント
  for (var col = startColumn; col <= endColumn; col++) {
    for (var row = startRow; row <= endRow; row++) {
      if (sheet.getRange(row, col).getValue() === "〇") {
        var person = persons.find(p => p.row === row);
        if (person) {
          shifts[person.name].daysOff++;
          shifts[person.name].offDays.push(col);
        }
      }
    }
  }

  // 各人物の「〇」を配置
  for (var i = 0; i < persons.length; i++) {
    var person = persons[i];
    var row = person.row;

    // 主な曜日に「〇」を配置
    var primaryCols = dayColumns[person.primary] || [];
    for (var col of primaryCols) {
      if (shifts[person.name].daysOff < offDaysPerPerson && sheet.getRange(row, col).getValue() === "") {
        sheet.getRange(row, col).setValue("〇");
        shifts[person.name].daysOff++;
        shifts[person.name].offDays.push(col);
      }
    }

    // 副次的な曜日に残りの「〇」を配置
    var secondaryCols = dayColumns[person.secondary] || [];
    for (var col of secondaryCols) {
      if (shifts[person.name].daysOff < offDaysPerPerson && sheet.getRange(row, col).getValue() === "") {
        sheet.getRange(row, col).setValue("〇");
        shifts[person.name].daysOff++;
        shifts[person.name].offDays.push(col);
      }
    }
  }

  // 各人物の残りの「〇」を配置（副次的な曜日）
  for (var i = 0; i < persons.length; i++) {
    var person = persons[i];
    var row = person.row;
    var remainingCols = dayColumns["月"] || [];
    for (var col of remainingCols) {
      if (shifts[person.name].daysOff < offDaysPerPerson && sheet.getRange(row, col).getValue() === "") {
        sheet.getRange(row, col).setValue("〇");
        shifts[person.name].daysOff++;
        shifts[person.name].offDays.push(col);
      }
    }
  }

  // 残りの空白にシフト時間を打ち込む
  for (var i = 0; i < persons.length; i++) {
    var person = persons[i];
    var row = person.row;
    for (var col = startColumn; col <= endColumn; col++) {
      if (sheet.getRange(row, col).getValue() === "") {
        sheet.getRange(row, col).setValue(person.shift);
      }
    }
  }
}

スプレッドシートにうまく反映されるでしょうか？

されました！
kmが後半6連勤になってしまっていますがしっかり「〇」の数を8個と守ってくれている証でもあります！
今回でいえばkmの6日を有給にしてあげるだけで解消はされるので問題なしです！
あとは希望休がうまくいくかですね～！

今回、適当に希望休を入れてみました。
これが消されずに残って、〇の数が計8個であれば成功になります！

結果は....

うまくできました～！
これも本来「〇」がつくところに有給を使えばシフトとして使えそうです！

と、ここで急遽予定が入ってしまったので製作は中断します...
ただ、他の部署も命令文を少し変えて同じ感じでChatGPTにお願いすればできそうですね。

AIは万能ではない？

今回これを制作していて思ったことは、ChatGPTは言ったことすべてを叶えてくれるスーパーマンのように思えますが、やはり制作物の微調整は難しいです。
これは恐らく人間の脳内を完全に読み取るAIが実現しない限り、今後どこまでいっても変わらないと思います！

AIをたたき台として活用し、微調整は人間が行うといった流れが最も良いやり方かもしれませんね。

また、命令文の作り方でAIの行動を大きく左右します。

人によって言葉の使い方もちがうので、万人がAIの性能をフル活用できないのが悔しいところですね。(私のことです)

また、今回はChatGPTに関する本を一冊読みつつ、記事を執筆してみました。
インプットとアウトプットをこなせて自身の知識として定着させることができたかと思います。

今回はここまでになります。
ありがとうございました。