Excelでシフト表を自動作成する方法 ― 希望を集めて、ボタン1つで月のシフトを作る

シクミヤの福田です。当記事では、Excelでシフト表を自動作成する方法を解説します。希望休の集め方から、人数の不足を知らせる関数、ボタン1つで月の案を作るマクロまで、順番に作っていきます。

1. はじめに
   • シフト作りで時間がかかる場所
   • 当記事の結論
   • 作り直す前の表
2. シフト表の土台を作る
   • 「シフト」シートの形
   • 「希望」シートの形
   • 希望の集め方
3. 関数で間違いを知らせる
   • 人数と偏りを数える数式
   • 色で知らせる
   • マクロが使えない場合の作り方
4. ボタン1つで月の案を作るマクロ
   • マクロがしていること
   • コード
   • ボタンを付ける
   • 押したあとの表
5. 人の組み合わせを確かめる
   • 早番・遅番がある場合
6. Excelで足りる範囲を見分ける
7. まとめ

はじめに

シフト作りで時間がかかる場所

シフト作りは、表に「出」「休」を入れる時間より、確かめ直す時間が長くなりがちです。希望休を写し、日ごとの人数を数え、足りない日に誰かを動かすと、また別の日の人数が変わります。

この「写す」「数える」「偏りを見る」は、決まった手順なのでExcelに任せられます。一方で、「この2人は同じ日にしたくない」のような判断は、人が見たほうが確実です。

当記事の結論

会社のパソコンでマクロが止められている場合も、①と②は使えます。②までで止めても、手間はかなり減ります。

作り直す前の表

よくある形を、架空のスタッフ5人で並べます。希望休は紙やメッセージで届き、担当者が見ながら手で入れます。黄色が手で入れている所、赤が間違いやすい所です。

シフト表.xlsx（作り直す前）

5日の人数は、数え直すと1人です。手で数えた数字は、シフトを動かしたあとに直し忘れると、そのまま残ります。

シフト表の土台を作る

シートを2枚用意します。名前は「シフト」と「希望」です。元のファイルがある場合は、コピーしてから試してください。

「シフト」シートの形

1行目に日付、2行目に曜日、3行目に必要な人数、4〜8行目にスタッフ、9・10行目に集計を置きます。列はB〜AFの31日分です。

日付のセルは、表示形式のユーザー定義で d にすると、「1」「2」と日だけが出ます。祝日は、3行目の数字をその日だけ手で書き換えてください。

「希望」シートの形

「シフト」シートと同じ行・同じ列に揃えます。ずれるとマクロが別の人の希望を読むので、ここがいちばん大事です。

文字を絞るには、B4〜AF8を選び、[データ]タブの[データの入力規則]で、入力値の種類を[リスト]にして、元の値に 休,出 と入れます。「お休み」「×」のような書き方が混ざると、関数やマクロが読めないためです。「出」は、その日はどうしても入りたいという希望に使います。

シフト.xlsm ／「希望」シート（黄色がスタッフの希望）

希望の集め方

集め方は3つあります。迷ったら、今のやり方を変えずに①から始めてください。

②は、マクロ入りのファイルでもブラウザで開いて編集できる、という仕様を使います。ただし、ファイルの中身によってはブラウザで開けないことがあると、公式の案内に書かれています。先に1度試してください。③のGASは「GAS入門」で始め方を書いています。

• 参考 : MONTH 関数（Microsoft サポート）
• 参考 : WEEKDAY 関数（Microsoft サポート）
• 参考 : CHOOSE 関数（Microsoft サポート）
• 参考 : ドロップダウン リストを作成する（Microsoft サポート）
• 参考 : Excel for the web で VBA マクロを操作する（Microsoft サポート）

関数で間違いを知らせる

ここまでで、数え直しをなくす準備ができました。次の数式を「シフト」シートに足します。マクロを使わない場合も、ここは同じです。

人数と偏りを数える数式

AH列は、公平さを見るための列です。出勤日数が同じでも、土日の出勤が特定の人に偏っていないかを、数字で確かめられます。

色で知らせる

数字が出ても、31列を目で追うのは大変です。条件付き書式で色を付けます。

数式のルールは、[ホーム]タブの[条件付き書式]→[新しいルール]→[数式を使用して、書式設定するセルを決定]で入れます。範囲の左上のセル（B4）を基準に書くと、ほかのセルにもずれて当てはまります。3つ目のルールがあると、あとから手で動かしたときの見落としも防げます。

マクロが使えない場合の作り方

マクロを使わないなら、B4に次の数式を入れて、AF8までコピーします。

=IF(希望!B4="休","休","")

希望休だけが先に入るので、残りの空いたセルに「出」と「休」を手で入れます。10行目の「不足」が消え、AG列とAH列の数字が揃えば完成です。毎月この表をコピーして使ってください。

• 参考 : Microsoft Excel で COUNTIF 関数を使用する（Microsoft サポート）
• 参考 : COUNTIFS 関数（Microsoft サポート）
• 参考 : 条件付き書式を使用して Excel 内の情報を強調表示する（Microsoft サポート）
• 参考 : 労働時間・休日（厚生労働省）

ボタン1つで月の案を作るマクロ

マクロがしていること

難しい計算はしていません。1日から順に、次の3つを繰り返すだけです。

コード

Excelで Alt＋F11 を押し、[挿入]→[標準モジュール]を選んで、次のコードを貼り付けます。

Sub MakeShift()
    Const FIRST_ROW = 4, LAST_ROW = 8   ' スタッフの行（5人の場合）
    Const FIRST_COL = 2, LAST_COL = 32  ' 1日から31日の列（B列からAF列）
    Const MAX_RUN = 5                   ' 続けて出勤できる日数の上限
    Dim wish As Worksheet, c As Long, r As Long, best As Long, low As Long, days As Long, streak As Long
    Set wish = Worksheets("希望")
    With Worksheets("シフト")
        .Range(.Cells(FIRST_ROW, FIRST_COL), .Cells(LAST_ROW, LAST_COL)).ClearContents  ' 前の案を消す
        For c = FIRST_COL To LAST_COL
            If .Cells(1, c).Value = "" Then Exit For  ' その月にない日で終わる
            For r = FIRST_ROW To LAST_ROW
                .Cells(r, c).Value = wish.Cells(r, c).Value  ' 希望を写す
            Next r
            Do While WorksheetFunction.CountIf(.Range(.Cells(FIRST_ROW, c), .Cells(LAST_ROW, c)), "出") < .Cells(3, c).Value
                best = 0: low = 999
                For r = FIRST_ROW To LAST_ROW
                    days = WorksheetFunction.CountIf(.Range(.Cells(r, FIRST_COL), .Cells(r, c)), "出")
                    streak = 0
                    If c - MAX_RUN >= FIRST_COL Then streak = WorksheetFunction.CountIf(.Range(.Cells(r, c - MAX_RUN), .Cells(r, c - 1)), "出")
                    If .Cells(r, c).Value = "" And days < low And streak < MAX_RUN Then
                        best = r: low = days
                    End If
                Next r
                If best = 0 Then Exit Do  ' 入れる人がいない日は不足のまま残す
                .Cells(best, c).Value = "出"
            Loop
            For r = FIRST_ROW To LAST_ROW
                If .Cells(r, c).Value = "" Then .Cells(r, c).Value = "休"  ' 残りは休み
            Next r
        Next c
    End With
End Sub

スタッフが増えたら、両方のシートで8行目より上に同じ位置で行を足し、LAST_ROW の数字を変えます。上限を変えたいときは MAX_RUN を変えます。マクロの動きは、下の例で計算を追って確かめたものです。必ずコピーしたファイルで、一度動きを確かめてから使ってください。

ボタンを付ける

1. [開発]タブがなければ、[ファイル]→[オプション]→[リボンのユーザー設定]で[開発]にチェックを入れる
2. [開発]タブの[挿入]→フォームコントロールの[ボタン]を選び、シートの空いている場所をクリックする
3. マクロを選ぶ画面で MakeShift を選んで[OK]を押す
4. ボタンの文字を「シフトを作る」に変える
5. ファイルの種類を「Excel マクロ有効ブック（.xlsm）」にして保存する

これで、シフトを作る を押すだけで案が入ります。ファイルをメールやダウンロードで受け取った場合は、マクロが止められることがあります。自分たちが作った安全なファイルなら、ファイルのプロパティの[全般]で[ブロック解除]にチェックを入れると動きます。画面やメニューの名前は2026年11月時点のもので、Excelのバージョンによって少し違うことがあります。

押したあとの表

先ほどの希望で押すと、次の表になります。黄色は希望休、緑はマクロが入れた「出」です。AG列・AH列は省略し、8日以降も省略しています。

シフト.xlsm ／「シフト」シート（ボタンを押したあと）

手で数えていた5日の間違いが、「不足」としてはっきり出ました。土曜に4人が希望休を出しているので、誰かに相談するしかありません。ここからが人の仕事です。

この例で12月の31日分を最後まで計算すると、出勤日数は5人とも13〜14日に揃い、不足は5日だけでした。土日の出勤回数は3〜5回で、ここは少し差が残ります。

• 参考 : WorksheetFunction.CountIf メソッド（Microsoft Learn）
• 参考 : Range.ClearContents メソッド（Microsoft Learn）
• 参考 : フォームまたはコントロール ボタンにマクロを割り当てる（Microsoft サポート）
• 参考 : インターネットからのマクロは、Office で既定でブロックされます（Microsoft Learn）

人の組み合わせを確かめる

当記事のマクロは、人数と出勤日数だけを見ています。「鍵を開けられる人が1人は要る」のような条件は見ていません。こうした条件は、マクロを複雑にするより、まず関数で知らせるほうが直しやすくなります。

たとえば、Aさん（4行目）とBさん（5行目）が鍵を持っているなら、11行目に次の数式を入れて、AF11までコピーします。

=IF(B1="","",IF(COUNTIF(B4:B5,"出")=0,"鍵なし",""))

上の表なら、2日と4日に「鍵なし」と出ます。2日はAさんが希望休なのでBさんに、4日はAさんかBさんに入ってもらい、代わりに誰かを休みにします。直すと9行目の人数やAG列の数字もすぐに変わるので、影響をその場で確かめられます。

早番・遅番がある場合

早番と遅番を分けるときは、まず関数を増やします。「希望」と同じ形のまま、記号を「早」「遅」「休」にして、人数の行を2つに分けます。

マクロも同じ考え方で「早」と「遅」を順に埋める形に広げられます。ただ、「遅番の翌日に早番を入れない」のような条件が増えるほど、コードは長くなります。

Excelで足りる範囲を見分ける

Excelで続けるか、別の道具にするかは、次の表で考えてみてください。

サービスに移る場合も、シフトの決まりごとを書き出した表は無駄になりません。「必要な人数」「続けて働ける日数」「鍵を持つ人」が整理できていれば、比べるときの物差しになります。自分たちで作り切れない場合に、外に頼むときの考え方は「エクセル・マクロ・GASの外注費用は何で決まる？」にまとめています。

まとめ

• Excelに任せるのは「案を作る」「間違いを知らせる」まで。最後の調整は人がする
• 「シフト」と「希望」の2枚を、同じ行・同じ列で作る。希望は入力規則で「休」「出」に絞る
• COUNTIFと条件付き書式で、人数の不足・続けての出勤・希望休の見落としを色で出す
• マクロは1日から順に、希望を写し、出勤の少ない人から埋めるだけ。手直しのあとは押さない
• 条件が増えてきたら、マクロを長くする前に関数の確認で足りないかを考える