【Excel】エクセルのシフト表を自動作成する方法（関数・マクロ・条件・自動振り分け）

エクセルでシフト表を作る際、毎月の勤務希望を転記し、人数を数え、不足日を探し、担当者の偏りを調整する作業に時間がかかっていませんか。

関数、条件付き書式、VBAマクロを組み合わせると、勤務希望をもとにしたシフト表の自動作成や自動振り分けを行いやすくなります。

ただし、希望を完全に満たしながら公平な勤務回数まで機械的に決めるには、職場ごとのルールを整理しておく必要があります。

まずは関数で確認できる部分を整え、繰り返し作業だけをマクロに任せる構成にすると、修正しやすく実用的です。

この記事では、1行目に日付などのヘッダーがあるサンプルを使い、エクセルのシフト表を自動作成する方法を順番に解説します。

エクセルのシフト表を自動作成する基本設計

それではまず、シフト表を自動化するための土台となる表の作り方について解説していきます。

入力シートと完成シートの分離

シフト表の自動作成では、勤務希望を入力する場所と、完成した勤務表を表示する場所を分ける方法が基本です。

同じセルに希望と確定シフトを混在させると、後からどちらの情報を修正すべきか分かりにくくなります。

おすすめは、希望を入力するシート、必要人数を設定するシート、完成シフトを表示するシートの3枚構成です。

希望入力シートでは、スタッフ名をA列、日付を1行目に並べ、各セルへ出勤希望、休み希望、勤務不可などを入力します。

勤務記号は早番なら早、遅番なら遅、休みなら休のように短く統一すると、関数やマクロで扱いやすくなります。

入力規則のリストを使って記号を選択式にすると、表記ゆれを防げます。

たとえば休、休み、公休という複数の表記が混ざると、COUNTIF関数で正確に勤務数を数えられません。

表記の統一は小さな準備に見えますが、自動振り分けの精度を左右する重要な工程です。

マスタシートには、勤務記号、勤務時間、休憩時間、対象スタッフ、希望休上限などを一覧化しておくと便利です。

月ごとにコピーして使うテンプレートでも、マスタを参照する仕組みにしておけば、変更箇所を減らせます。

日付と曜日を自動表示する設定

シフト表の1行目には、日付と曜日を自動表示させると、月替わりの作成作業を短縮できます。

B1セルに対象月の初日を入力し、C1セルには翌日の日付を返す数式を入れる方法が分かりやすいでしょう。

B1セルの表示形式をd、C1以降も同じ形式に設定すると、1、2、3のように日だけを表示できます。

曜日はB2セルにTEXT関数を使って表示します。

土曜日と日曜日だけ文字色や背景色を変えると、休日や繁忙日の把握がしやすくなります。

月末までの日付をあらかじめ31日分並べ、対象月以外の日付を薄く表示する運用も可能です。

ただし2月や30日までの月で不要な列が残るため、数式で空欄にする設計が見やすいでしょう。

たとえばB1セルに月初日がある場合、31日分の日付欄で翌月になった列を空白にできます。

この数式は、前日の値に1を加えた日付が対象月と同じなら日付を表示し、翌月なら空欄を返す考え方です。

日付セルが空欄になる構造は、勤務数の集計範囲やマクロの処理範囲を決めるときにも役立ちます。

勤務記号と必要人数のルール

自動作成の前に、早番、日勤、遅番、夜勤、休み、有給などの勤務記号と扱いを決めます。

勤務記号が決まらないまま数式やVBAを作ると、途中でルールが変わったときに修正範囲が広がります。

早番と遅番をそれぞれ何人必要とするか、日ごとに必要人数を入力できる表を用意してください。

たとえば必要人数シートでは、B列に早番の必要人数、C列に遅番の必要人数、D列に夜勤の必要人数を設定します。

勤務記号は人に割り当てるデータであり、必要人数は日付に割り当てる条件です。

この2種類のデータを分けることで、休日や繁忙日だけ人数を増やす調整も行いやすくなります。

さらに、夜勤の翌日は原則休み、連続勤務は5日まで、土日の勤務回数を公平にするなどの条件も、別の列に整理しておくとよいでしょう。

すべての条件を最初から自動化しようとせず、絶対に守る条件と管理者が最後に調整する条件を分ける考え方が現実的です。

関数による勤務数と必要人数の判定

続いては、関数で勤務数と必要人数を確認する方法を解説していきます。

COUNTIF関数による勤務人数の集計

各日付に早番が何人いるかを調べるには、COUNTIF関数を使います。

シフト表でスタッフ名がA3からA20、1日の日付の勤務記号がB3からB20に入っていると仮定します。

この数式は、B3からB20の範囲で早という文字が入ったセルを数えます。

遅番の人数を数える場合は、条件の文字を遅に変えるだけです。

複数の勤務記号を合計したい場合は、COUNTIF関数を足し算できます。

日ごとの必要人数を自動判定する前に、勤務記号ごとの人数が正しく数えられることを確認しましょう。

数式を右へオートフィルすれば、1か月分の日別人数をまとめて集計できます。

ヘッダーが1行目にあり、スタッフの入力行が固定されているサンプルでは、集計行をスタッフ一覧の直下に置くと見やすくなります。

スタッフが増減する職場では、テーブル機能を使うか、余裕を持った範囲を指定すると管理しやすくなります。

IF関数による不足人数の表示

必要人数と実際の勤務人数を比較し、不足している日を見つけるにはIF関数が便利です。

実際の早番人数がB21セル、必要人数がB22セルにある場合、B23セルに判定式を作れます。

勤務人数が必要人数より少ない場合に早番不足と表示され、それ以外なら充足と表示されます。

不足している具体的な人数まで表示したいなら、差を計算する式を組み合わせましょう。

この式では、必要人数から勤務人数を引き、その結果を文章へ連結します。

たとえば必要人数が3人、実際の人数が2人なら、あと1人と表示されます。

人数が多すぎる日も確認するなら、IF関数を入れ子にして不足、充足、過剰の3段階に分ける方法があります。

過剰配置も人件費や勤務回数の偏りにつながるため、不足だけでなく過剰も同時に見つける設計が大切です。

COUNTIFS関数による条件付き集計

勤務表では、単純な勤務人数だけでなく、資格保有者や特定担当者の人数を数えたい場面があります。

このような複数条件の集計にはCOUNTIFS関数を利用します。

たとえばA3からA20にスタッフ名、B3からB20に早番や遅番、C3からC20に資格区分が入力されているとします。

この数式は、勤務記号が早であり、かつ資格区分が有である行だけを数えます。

介護、医療、製造、コールセンターなど、配置基準に必要なスキルがある職場では特に有効です。

夜勤に責任者を1人以上置く必要がある場合も、担当区分を条件に追加すれば確認できます。

条件を増やしすぎると式の意味が分かりにくくなるため、マスタシートの列名と条件の意味をそろえておきましょう。

条件付き書式による不足と連勤の可視化

続いては、条件付き書式で問題のあるセルを見つけやすくする方法を確認していきます。

不足人数セルの色分け

必要人数の判定セルに条件付き書式を設定すると、不足している日をひと目で確認できます。

判定行に早番不足や遅番不足と表示される場合、文字列に特定の文字を含むセルを赤くする設定が分かりやすいでしょう。

ホームタブの条件付き書式からセルの強調表示ルールを選び、文字列を含む条件を指定します。

条件には不足を入力し、書式には薄い赤の塗りつぶしと濃い赤の文字を選ぶと視認性が高まります。

修正が必要な日だけが色で浮かび上がるため、月間シフトを目視で探す負担を減らせます。

必要人数を超えた日には黄色、ちょうどよい日には緑を設定する運用も便利です。

ただし色だけに頼ると印刷時に分かりにくいことがあるため、不足という文字も残しておくと安心です。

シフト表を共有する場合、色の意味を凡例として表の上部や別シートに記載しておきましょう。

連続勤務の確認式

連勤の確認には、横方向に並ぶ勤務記号から休み以外の連続を数える考え方を使います。

複雑な連勤管理はVBAでも行えますが、まずは補助行で勤務日を1、休みを0に変換すると仕組みを理解しやすくなります。

たとえばB3セルの勤務記号をもとに、B25セルへ勤務判定を表示する場合は次の数式を使えます。

この式は勤務記号が休または有なら0、それ以外なら1を返します。

5日連続勤務を確認する場合は、対象日の補助セル5個を合計します。

5つのセルがすべて1なら合計は5になり、5連勤と表示されます。

この判定式を右方向へコピーすると、どの期間で連勤が続いているかを確認できます。

夜勤明けを勤務として数えるか、休みとして数えるかは職場の就業ルールで異なります。

自動作成前に定義を合わせておかないと、数式が正しくても現場の勤務ルールと判定結果がずれる可能性があります。

希望休と確定勤務の不一致判定

希望休を入力したセルに勤務が割り当てられていないかを確認するには、希望入力シートとシフト表を比較します。

希望入力シートのB3セルに休、完成シフトの同じ位置に早や遅が入った場合を検出する仕組みです。

完成シフトのB3セルへ条件付き書式を設定し、数式を使用して書式設定するセルを決定するルールを作ります。

条件が成立したセルを赤枠や赤い塗りつぶしにすると、希望休と確定勤務の不一致をすぐ確認できます。

勤務不可、午前のみ可、夜勤不可なども、別の記号にして判定式を追加できます。

完全自動の振り分け結果でも、最終確認は必要です。

条件付き書式は、管理者が修正すべき箇所へすばやく到達するための安全網になります。

VBAマクロによるシフトの自動振り分け

続いては、VBAマクロで希望休を避けながら勤務記号を自動振り分けする基本例を確認していきます。

マクロを実行するための準備

VBAを使う場合、ファイルはマクロ有効ブックとして保存します。

保存形式はExcel マクロ有効ブックを選び、拡張子はxlsmにしてください。

開発タブが表示されていない場合は、ファイル、オプション、リボンのユーザー設定から開発を有効にします。

開発タブのVisual Basicを選択し、VBA画面で標準モジュールを挿入するとコードを貼り付けられます。

サンプルでは、希望入力シートのB3からAF20に希望記号があり、シフト表シートの同じ範囲へ勤務記号を出力する前提です。

実行前には必ず元ファイルを複製し、テスト用のデータで動作を確認してください。

マクロは複数セルを一度に書き換えるため、想定外の範囲を処理すると復元に時間がかかることがあります。

スタッフ名や行数、日付列数が異なる場合は、コード内の範囲を実際の表に合わせて変更します。

希望休を避ける自動振り分けコード

次のVBAは、希望入力シートで休と入力されたセルには休を出力し、それ以外のスタッフへ早と遅を順番に配置する簡易例です。

勤務回数や資格条件を厳密に最適化するものではありませんが、自動振り分けの基本構造を学ぶ出発点になります。

Sub シフトを自動作成()

    Dim wsHope As Worksheet
    Dim wsShift As Worksheet
    Dim r As Long
    Dim c As Long
    Dim shiftCount As Long

    Set wsHope = Worksheets("希望入力")
    Set wsShift = Worksheets("シフト表")

    For c = 2 To 32
        shiftCount = 0

        For r = 3 To 20
            If wsHope.Cells(r, c).Value = "休" Then
                wsShift.Cells(r, c).Value = "休"
            Else
                If shiftCount Mod 2 = 0 Then
                    wsShift.Cells(r, c).Value = "早"
                Else
                    wsShift.Cells(r, c).Value = "遅"
                End If

                shiftCount = shiftCount + 1
            End If
        Next r
    Next c

    MsgBox "シフト表を作成しました"

End Sub

ForからNextまでの処理は、日付列とスタッフ行を順番に確認する繰り返しです。

希望が休ならそのまま休を出力し、それ以外ならshiftCountの余りによって早番と遅番を交互に入れます。

Mod 2は、数値を2で割った余りを求める記述です。

余りが0のときは早、1のときは遅となるため、出勤可能なスタッフに交互の勤務記号を入れられます。

ただし、必要人数が日ごとに異なる場合や、夜勤、資格、前日の勤務との関係まで考慮するなら、条件を追加する必要があります。

勤務回数の偏りを減らす改善

単純な順番で自動振り分けすると、行の上にいるスタッフへ勤務が偏ることがあります。

偏りを減らすには、各スタッフの現在の勤務回数を数え、勤務回数が少ない人から優先して割り当てる考え方が必要です。

完成シフトの右端に勤務数列を設け、COUNTIF関数で早、遅、日などを集計しておくと確認しやすくなります。

マクロ側では、この勤務数が少ないスタッフを探してから勤務を割り当てる処理へ発展させられます。

さらに、土日勤務数、夜勤数、早番数などを別々に集計すると、公平性をより細かく確認できます。

自動振り分けは完全な正解を出す仕組みではなく、管理者が調整しやすい初期案を作る仕組みとして考えると運用しやすいでしょう。

自動作成後の調整と運用ルール

続いては、自動作成したシフト表を安全に調整し、毎月の業務で使い続けるための考え方を解説していきます。

自動結果を確定前に確認する手順

自動振り分けが終わったら、最初に日ごとの必要人数を確認します。

早番、遅番、夜勤などの集計行に不足表示がないかを見てください。

次に、希望休と確定シフトの不一致を条件付き書式で確認します。

最後に、連勤、夜勤明け、資格保有者の配置、土日勤務回数など、職場固有の条件を確認しましょう。

自動作成の結果をそのまま配布せず、確認用のチェック項目を毎月同じ順番で見ることがミスの防止につながります。

確認済みの日付やスタッフをチェックする列を作っておくと、複数人でシフトを調整するときにも便利です。

変更したセルにはコメントを残したり、修正者の列を設けたりすると、後から調整理由を追跡できます。

スタッフ追加と退職時の修正

スタッフが追加された場合は、希望入力シート、シフト表、マスタシートの順に情報を追加します。

新しいスタッフの行を追加したあと、COUNTIF関数や条件付き書式の対象範囲にその行が含まれているかを確認してください。

固定範囲の数式では、スタッフ行を増やしても集計対象に自動追加されないことがあります。

テーブル形式で作成しておくと、追加行に書式や数式が引き継がれやすくなります。

退職者を消去する場合も、過去のシフト記録まで失わないように、当月以降のテンプレートから外す運用が安全です。

過去月の確定シフトはPDF化や別ファイル保存を行い、変更履歴を残しておくと確認が容易になります。

人員構成が変わった月は、マクロの最終行や対象範囲も必ず点検しましょう。

共有時の保護とバックアップ

完成したシフト表を共有する前に、数式や集計行を誤って上書きされないように保護を設定する方法があります。

入力してよいセルだけをロック解除し、シートの保護をかけると、勤務記号や集計式の破損を防ぎやすくなります。

ただし、シート保護は高度なセキュリティ機能ではないため、重要な個人情報を扱う場合は保存先のアクセス権も管理してください。

マクロ有効ブックは、更新前にバックアップを取る習慣が大切です。

月初にテンプレートをコピーし、ファイル名へ対象年月を付けると、前月のデータを誤って上書きする事故を防げます。

共有フォルダで共同編集する場合は、誰が確定権限を持つかをあらかじめ決めておくと混乱しにくくなります。

まとめ エクセルの自動振り分けによるシフト表作成方法

エクセルのシフト表は、勤務希望、必要人数、勤務記号を分けて管理することで、自動作成しやすくなります。

COUNTIF関数で日ごとの勤務人数を集計し、IF関数で不足を表示すれば、調整が必要な日をすばやく見つけられます。

条件付き書式を使うと、希望休との不一致や連勤の可能性も見える化できます。

VBAマクロは、希望休を避ける初期案の作成から導入し、現場のルールに合わせて少しずつ改善する方法がおすすめです。

完全な自動振り分けを目指すよりも、管理者が短時間で確認し、微調整できるシフト表を作るほうが実務では安定します。

関数、条件付き書式、マクロを役割ごとに使い分け、毎月のシフト作成を効率化していきましょう。