はじめに：シフト表の「色塗り」、手作業でやってませんか？
飲食店や小売店の店長さん、毎日のシフト作成お疲れ様です！
スタッフの出勤時間と退勤時間を見て、Excelのマス目を一つひとつ「塗りつぶしツール」で塗っていく…。
そんな地道な作業に時間を取られていませんか？
今回作成するのは、いわゆる「ガントチャート」のようなシフト表です。
【ガントチャートとは？】
横軸に「時間」、縦軸に「タスク（今回はスタッフ）」をとり、作業の開始から終了までを横棒（バー）で表した表のことです。
元々はプロジェクト管理で「誰が・いつからいつまで・何の作業をするか」を見える化するために使われますが、シフト管理にも最適なんです！
「出勤時間を変えたら、自動で色が塗り変わってくれたらいいのに…」
「12時台に何人いるか、パッと計算したいなぁ…」
その悩み、Excel関数を使えば一発で解決できます！
今回は、時間が入力されたら勝手にバーが伸びる、しかも「時間帯ごとの人数」や「労働時間」の集計まで自動で終わってしまう、最強のシフト表を作ります。
一見難しそうに見えますが、使う数式はたった1つだけ。
実務で即使えるテクニック満載でお届けします！
本記事では、無料のWeb版Excelを使用して検証および画像の作成を行っています。Windowsはもちろん、MacやLinuxの方もブラウザさえあれば挑戦できます！
ステップ1：表のデザインと「27時」の秘密
まずは、今回作成するシフト表の土台を見てみましょう。
ポイント1：見出しの配置（セルの結合）
時間軸（1行目、2行目）の見出し位置を揃えるために、セルを結合しています。
「セルの結合は悪だ！」という意見もよく聞きますが、
結論から言うと「そのセルを数式で参照したり、データを並べ替えたりしないなら、全く問題ありません」。
セルの結合が問題になるのは、例えば「データベースの元データとして使う時（並べ替えができなくなる）」や「VBAで処理する時」などです。
今回のような「人間が見るための見出し」として使う分には、デザインを整えるための有効な手段です。
Web版Excelのように「選択範囲内で中央」機能がない環境では、気にせず使い分けていきましょう！
※下の画像：1,2行目の赤枠はすべてセル結合しています。
ポイント2：深夜3時は「27:00」
これが最大のコツです！
夜中の3時まで出勤しているスタッフがいる場合、通常なら「翌日の3:00」として扱いますが、計算が複雑になります。
そこで、Excel上では「27:00」と入力します。
こうすることで、「出勤 < 退勤」という単純な大小関係だけで計算できるようになり、数式が劇的にシンプルになります。
【重要：27:00の表示形式】
実は「27:00」と入力すると、Excel内部では「1日と3時間（シリアル値では1.125）」として扱われます。
Web版Excelでこれを「3:00」と表示させるには、以下の設定が必要です。
1. セルを選択して Ctrl + 1 を押す。（セルの書式設定）
2. 「表示形式」タブの「時刻」カテゴリから、「13:30」（24時間表記のサンプル）を選択。
これで、入力は「27:00」でも、見た目は「3:00」となり、計算上は「翌日の3時（今日の時間より大きい数値）」として正しく扱われます。
ポイント3：1行おきのレイアウト
今回は見やすさを重視して、データの入る行（3, 5, 7…行目）の間に空白行を入れています。
これも後で数式をコピペする際にちょっとした工夫で対応できます。
ステップ2：ロジックの核心！「1」を立てろ！
いきなり条件付き書式を設定する前に、ちょっとしたテクニックを使います。
それは、「塗りつぶしたいセルに、数式で『1』を表示させる」ことです。
なぜ「1」なのか？
それは、後で「人数の合計」や「労働時間」を計算するときに、この「1」をSUM関数で足すだけで済むからです。
これが「使えるシフト表」の秘訣です。
その前に：「シリアル値」って？
数式を作る前に、Excelの時間の正体「シリアル値」について少しだけおさらいしましょう。
Excelでは、時間を「0から1までの数値」で管理しています。
- 24時間（1日） = 1
- 12時間（半日） = 0.5
- 1時間 = 1/24
つまり、「8:00」というのはExcelにとっては「8 ÷ 24」という計算結果の数値なのです。
この仕組みを使って、横軸（時間軸）の数値を自動で作ります。
※シリアル値については、Excel 特定の時間帯のデータ数をカウントする方法で詳しく解説しています。
数式を入力
では、E3セル（最初のスタッフの8:00のマス）に、以下の数式を入力しましょう。
これ一つで解決します！
=LET(a,SEQUENCE(,COLUMNS(E1:AP1),8,0.5)/24,IF((B3:B19<=a)*(C3:C19>a),1,""))
「うわっ、難しそう！」と思いましたか？
大丈夫、分解すれば簡単です。
数式の内訳解説
1. 時間軸を作る：SEQUENCE(,COLUMNS(E1:AP1),8,0.5)/24
まず、SEQUENCE関数で「8, 8.5, 9, 9.5 …」という数値を作り、それを24で割って「時間のシリアル値（8:00, 8:30…）」に変換しています。
これを LET 関数を使って「a」という名前で定義します。
つまり「a」は、この表の横軸（時刻）そのものです。
2. 塗りつぶし判定：IF((出勤<=a)*(退勤>a), 1, "")
ここが判定ロジックです。
出勤 <= a：そのマスの時間が、出勤時間「より後（以上）」か？退勤 > a：そのマスの時間が、退勤時間「より前（未満）」か？
重要ポイント！
退勤時間の判定を > （イコールなし）にしているのがミソです。
もし退勤が「15:30」の場合、15:30のマス（T列）は働いていませんよね？
だから「15:30より前」である必要があります。
ここを間違えると、バーが30分（1セル分）長く伸びてしまいます。
この数式を入力すると、該当する時間帯にズラッと「1」が表示されます！
ステップ3：色を塗って仕上げる
「1」が表示されたら、あとは簡単。
条件付き書式で「1なら色を塗る」設定をするだけです。
1.E3:AP19 の範囲を選択します。
2. 「ホーム」タブ → 「条件付き書式」 → 「新しいルール」を選択。
3.「数式が true のセルを書式設定する」を選び、以下の数式を入力します。
=E3=1
4. 「カスタム書式」ボタンを押し、以下のように設定します。
- 塗りつぶし：好きな色（例：青）
- フォントの色：塗りつぶしと同じ色（青）
【テクニック】
文字色と背景色を同じにすることで、セルに入っている「1」を見えなくします。
これで、見た目は綺麗なバーチャート、中身は計算可能な数値データの完成です！
ステップ4：集計も一瞬！
苦労して「1」を作ったおかげで、集計は驚くほど簡単になります。
各時間帯の「出勤人数」を出す
表の下、C21セルあたりに「時間帯人数」と入力し、その横（E21セル）に以下の数式を入れ、右にコピーします。
=SUM(E3:E19)
たったこれだけ！縦に並んでいる「1」を合計すれば、その瞬間に何人働いているかが一発で分かります。
各スタッフの「労働時間」を出す
表の右端、AS2セルに「労働時間」と入力し、AS3セルに以下の数式を入れ、下にコピーします。
=IF(A3="","",SUM(E3:AP3)/2)
横に並んでいる「1」を合計すると、「30分単位のコマ数」が分かります。
これを「2」で割れば、時間単位（8時間など）になりますね！
IF(A3="",""...)を入れているので、間の空白行もエラーにならず、きれいに空白のまま維持されます。
まとめ：見た目と機能を両立させよう！
「条件付き書式だけで色を塗る」のではなく、「一度『1』という数値にしてから色を塗る」というワンクッション置くアプローチ。
これこそが、集計まで自動化できる「強いExcelシート」を作る秘訣です。
今回は30分単位で作成しましたが、列を増やして「15分単位」にしたり、行を増やして「50人のスタッフ」に対応させたり、開始時間を変えて「24時間フル稼働」のシフト表にしたりと、このロジックさえ分かれば応用は自由自在です。
ぜひ、あなたのお店やチームにぴったりの、オリジナルシフト表を作ってみてくださいね！
