Excelで祝日だけ色が付かない原因と直し方|条件付き書式

スポンサーリンク

シフト表の土日には色が付いたのに、祝日だけ白いまま。手順どおりに COUNTIF のルールを足したのに、元日も成人の日も塗れない。そんな状態で手が止まっていませんか。

祝日のセルを手で塗れば、その場はしのげます。でも翌月も翌年も同じ作業が残り、塗り忘れた日がそのまま配られてしまうかもしれません。

条件付き書式で祝日だけ色が付かないときは、症状から原因を絞り込めます。この記事では、症状ごとに疑う原因と、1操作でできる確認方法を整理しました。2027年1月のシフト表を例に、直し方まで解説します。

対応バージョン: Microsoft 365 と Excel 2016 以降のデスクトップ版を基準にしています。Excel for the web(Web版)でも、作業ウィンドウでルールの作成・編集・削除ができます。ルールの並べ替えと「条件を満たす場合は停止」がWeb版にあるかは、確認できていません(「よくある質問」で補足)。

まず切り分け|祝日が塗れない3つの症状と疑う原因

例にするのは、2027年1月の縦型シフト表です。A列に日付、B列に曜日、C〜F列にスタッフのシフト。3行目が1/1、33行目が1/31にあたります。

行A列:日付B列:曜日付けたい色
32027/1/1金祝日(元日)
42027/1/2土土曜
52027/1/3日日曜
62027/1/4月なし
72027/1/5火なし
82027/1/6水なし
92027/1/7木なし
102027/1/8金なし
112027/1/9土土曜
122027/1/10日日曜
132027/1/11月祝日(成人の日)

祝日は、別シート「祝日リスト」のA2:A18に2027年の17日分を入れてあります。この範囲には「祝日」という名前を付けました。ルールは次の3本で、適用先はどれも同じです。

土曜: =WEEKDAY($A3)=7        書式: 薄い青の塗りつぶし
日曜: =WEEKDAY($A3)=1        書式: 薄い赤の塗りつぶし
祝日: =COUNTIF(祝日,$A3)>0   書式: 薄いオレンジの塗りつぶし
適用先(3本共通): =$A$3:$F$33

症状 → 原因 → 確認方法の早見表

自分の症状に近い行を探してください。

症状疑う原因確認方法(1操作)
祝日が1日も塗れない祝日リストの日付が文字列空きセルに =COUNT(祝日) を入れ、17になるか見る
祝日が1日も塗れない祝日リストが別のブックにあるリストがシフト表と同じブックにあるか見る
祝日が1日も塗れないカレンダー側が1〜31の数値日付のセルを選び、数式バーの表示を見る
一部の祝日だけ塗れないリストの範囲に $ が無い空き列にルールの式を貼り、TRUE の行を見る
祝日の前日など、ずれた日が塗れる適用先の先頭行と数式の行がずれている「ルールの管理」で適用先の先頭行を見る
3/22 のような休日だけ塗れない休日がリストに無いリストに 2027/3/22 があるか見る
土日と重なる祝日だけ土日の色になるルールの順番「ルールの管理」で祝日ルールの位置を見る

どれにも当てはまらず、追加した行だけ色が付かないなら、適用範囲の外かもしれません。全体の切り分けは、Excelの条件付き書式・プルダウン・フィルターが動かない原因と対処 にまとめています。

土日のルールは動く前提(WEEKDAY の式を1行だけ確認)

=WEEKDAY($A3)=7   土曜なら TRUE
=WEEKDAY($A3)=1   日曜なら TRUE

WEEKDAY は2つ目の引数(種類)を省くと、日曜を1、土曜を7として返します。式の組み立てや種類の使い分けは、ExcelのWEEKDAY関数の使い方 にまとめました。

押さえておきたいのは、土日のルールが見ているのはシフト表のA列だけ、という点。祝日のルールは、A列に加えて祝日リストも読みにいきます。土日が正しい曜日に塗れているなら、疑う場所はリスト側か、リストとの照合のしかたに絞れるわけです。

ルールの式を空き列に貼って TRUE/FALSE を見る確認テク

条件付き書式の数式は、セルに入力しても同じ TRUE / FALSE を返すのが便利なところ。シフト表の右の空き列で試すと、どの行が祝日と判定されているかを目で追えます。

  1. 空いているH3に、ルールに書いた数式をそのまま入力します(例: =COUNTIF(祝日,$A3)>0)。
  2. H3をH33までコピーします。
  3. TRUE になった行の日付を確かめてください。

2027年1月なら、TRUE になるのはH3(1/1)とH13(1/11)の2行だけが正解。結果からの読み取り方は次のとおりです。

H列の結果疑う原因
すべて FALSEリストが文字列の可能性・カレンダー側が数値
H3 だけ TRUE で、H13 は FALSE祝日リストの範囲に $ が無い
H3 と H13 が TRUE なのに、色がずれる・付かない適用範囲の起点ずれ、またはルールの順番

確認が済んだら、H列の式は消しておきましょう。

祝日が1日も塗れない

祝日が1日も塗れないときに疑う原因は、リスト側の型・リストの置き場所・カレンダー側の型の3つ。空き列の確認ですべて FALSE だったなら、COUNTIF が祝日リストの日付を1件も見つけられていない状態です。

祝日リストの日付が文字列になっている(ISNUMBER で判定・一括で日付に直す)

Webページの祝日一覧をコピーしたり、CSVから貼り付けたりすると、日付が文字列のまま入ることがあります。見た目は「2027/1/1」でも、Excelにとっては文字の並びです。

シフト表の日付は、Excelの中では46388(2027/1/1のシリアル値)のような数値。リスト側が文字列だと、COUNTIF でこの数値と一致しないことがあります。COUNTIF の条件の扱いは、ExcelのCOUNTIF関数の使い方 で詳しく解説しています。

判定は、空いているセルに次の式を入れるだけ。

=COUNT(祝日)                 日付(数値)として入っている件数
=COUNTA(祝日)                空白以外の件数
=ISNUMBER(祝日リスト!A2)     1セルずつ確かめるとき(日付なら TRUE)

17日すべてが日付なら、COUNT も COUNTA も17になります。COUNT が0なら全件が文字列、15なら2件だけが文字列という読み方です。表示形式が標準のままセルの左に寄っている日付も、文字列の目印になります。

一括で日付に直す方法は2つあります。1つ目は「区切り位置」を使うやり方。

  1. 祝日リストのA2:A18を選びます。
  2. [データ] タブの [区切り位置] を選びます。
  3. [次へ] で3ページ目まで進めてください。
  4. 列のデータ形式で [日付] を選び、「YMD」になっているのを確かめます。
  5. [完了] を押します。

2つ目は、空いた列で1を掛ける方法。

C2: =A2*1    → 46388 になれば日付に変換できている

C2をC18までコピーしたら、表示形式を日付にします。そのうえでC2:C18をコピーし、A2に「値の貼り付け」で戻しましょう。作業に使ったC列は消して構いません。

「1月1日(金)」のように曜日が付いた形は、どちらの方法でも変換できないことがあります。形式ごとの直し方は、Excelで文字列の日付が日付にならない原因 にまとめています。

別シートの祝日リストを参照できていない(名前の定義で渡す)

Microsoftのサポートページでは、数式を使うルールから同じブックの別シートを参照できると説明されています。一方で、別のブックへの外部参照は条件付き書式では使えません。共有フォルダの別ファイルにある祝日リストを指そうとしているなら、ここが原因と考えられます。

対処は、祝日リストをシフト表と同じブックのシートにコピーすること。あわせて範囲に名前を付けておくと、式が短くなり、参照先の打ち間違いも防げます。

  1. 「祝日リスト」シートでA2:A18を選びます。
  2. [数式] タブの [名前の定義] を選びます。
  3. 名前に「祝日」と入力してください。
  4. 参照範囲が =祝日リスト!$A$2:$A$18 になっているのを確かめます。
  5. [OK] を押します。
直接書く:   =COUNTIF(祝日リスト!$A$2:$A$18,$A3)>0
名前で渡す: =COUNTIF(祝日,$A3)>0

直接書いた式でも動きますが、範囲の $ を付け忘れると一部の祝日が塗れなくなります(「$ の付け方」の節で解説)。名前は範囲が固定されるので、この付け忘れが起きません。

祝日リストをテーブルにしている場合も、日付の列に名前を付けて渡すのがおすすめ。構造化参照をルールに直接書けるかどうかを気にせずに済みます。

カレンダー側のセルが日付ではなく 1〜31 の数値になっている

格子型の月間カレンダーでは、日にちを「1」「2」…と数字で打っていることがあります。日にちのセルを選んで数式バーを見たとき、「1」とだけ出るなら中身は数値の1です。

数値の1は、日付としては1900年1月1日として扱われます。祝日リストの2027/1/1(46388)とは一致しないので、祝日は1日も塗れません。WEEKDAY も1900年の日付として曜日を判定するため、土日の色も2027年の曜日と合っていない可能性があります。

直し方は、本物の日付を入れて、表示形式で「日」だけを見せること。2027年1月は1日が金曜なので、G4がスタート地点。

行B列(日)C列(月)D列(火)E列(水)F列(木)G列(金)H列(土)
4     12
53456789
610111213141516
717181920212223
824252627282930
931      
  1. G4に =DATE(2027,1,1) と入力します。
  2. H4は =G4+1、次の行の先頭のB5は =H4+1 のように、前の日に1を足してつなぎます。
  3. B4:H9を選び、[セルの書式設定] の [表示形式] で [ユーザー定義] をクリック。
  4. [種類] に d と入力して [OK] を押してください。

見た目は1〜31のままで、中身は2027年の日付になります。数値のまま残したい事情があるなら、ルールの中で日付に組み立てる手もあります。

=COUNTIF(祝日,DATE(2027,1,B4))>0

一部の祝日だけ塗れない・ずれた日が塗れる

元日は塗れるのに成人の日は塗れない。祝日の前日が塗れる。こうした部分的なずれで疑うのは、数式の参照のしかたと、祝日リストの抜けの2つです。

$ の付け方(縦型シフト表と格子型カレンダーで逆になる)

条件付き書式の数式は、適用先の左上のセルに対して書きます。ほかのセルには、その位置からずらした形で当てはめられる仕組み。$ を付けた部分は固定され、付けない部分は行や列に合わせて動きます。

項目縦型シフト表格子型カレンダー
日付の並びA列に縦に並ぶB4:H9に格子で並ぶ
適用先=$A$3:$F$33=$B$4:$H$9
日付の参照$A3(列だけ固定)B4($ を付けない)
祝日リストの参照名前「祝日」か $A$2:$A$18縦型と同じ
祝日のルール=COUNTIF(祝日,$A3)>0=COUNTIF(祝日,B4)>0

縦型は、行全体をA列の日付で判定したいので列を固定します。格子型は、セルごとに自分の日付で判定するので固定しません。日付側の $ の付け方が逆になるのは、このためです。

$ の付け方を間違えたときに起きることは、次のとおりです。

書き方起きること
縦型で =COUNTIF(祝日,A3)>0A列だけ塗れ、曜日やシフト欄のB〜F列は塗れない
縦型で =COUNTIF(祝日,$A$3)>0全行が1/1で判定され、1月の表がすべて祝日の色になる
縦型で =COUNTIF(祝日リスト!A2:A18,$A3)>01/1はA列だけ塗れ、1/11は塗れない
格子型で =COUNTIF(祝日,$B4)>0各行が日曜の列(B列)の日付で判定され、1/1も1/11も塗れない

3行目の書き方では、1行下がるごとにリストの範囲も1行ずつ下がります。13行目(1/11)が見るのは祝日リスト!A12:A28。祝日リストのA3にある1/11が、範囲から外れてしまいます。

列も同じように右へずれるため、B〜F列は祝日リストのB列以降を見てしまい、1/1の行でも塗れません。

適用範囲の起点(アクティブセル)と数式の行がずれている

数式の行番号は、範囲を選んだときのアクティブセルの行に合わせて書きます。アクティブセルとは、選択範囲の中で1つだけ白く残っているセルのこと。ここがずれると、ずれた行数だけ判定もずれます。

例えば、見出しの2行目からA2:F33を選んで、数式に $A3 と書いた場合です。

適用先: =$A$2:$F$33   (見出しの2行目から選んだ)
数式:   =COUNTIF(祝日,$A3)>0
結果:   見出し行と1/10の行が塗れ、1/1と1/11の行は塗れない

各行が1つ下の行の日付で判定されるため、祝日の前日が塗れる形になります。見出し行が塗れるのは、その下の3行目が1/1だから。下の行から上へドラッグして選んだ場合も、始点のセルがアクティブセルになり、同じように判定がずれます。

直すなら、祝日ルールを作り直すのが確実です。

  1. [ホーム] タブの [条件付き書式] から [ルールの管理] を開き、祝日ルールを削除します。
  2. A3をクリックしてからF33までドラッグし、A3をアクティブセルにして選びます。
  3. [条件付き書式] の [新しいルール] で、[数式を使用して、書式設定するセルを決定] を選んでください。
  4. =COUNTIF(祝日,$A3)>0 を入力し、書式を設定して [OK] を押します。

新しいルールは一覧の先頭に追加されるので、作り直した祝日ルールは土日のルールより上に来ます。行のコピーや挿入でルールが何本にも増えているなら、先に整理しておきましょう。手順は Excelの条件付き書式が勝手に増える・崩れる原因 で解説しています。

振替休日・国民の休日が祝日リストに無い

2027年は春分の日の3/21が日曜に当たり、翌日の3/22(月)が休日になります。内閣府の祝日一覧では、3/22は「休日」と書かれています。一般に振替休日と呼ばれるのは、この日のこと。

カレンダーで祝日の名前を拾って手入力すると、名前の無い3/22を入れ忘れることがあります。リストに無い日は COUNTIF で数えられないので、3月のシフト表で3/22だけ白いまま残ります。

内閣府の一覧に載っている2027年の祝日・休日は、次の17日です。

日付曜日名称
2027/1/1金元日
2027/1/11月成人の日
2027/2/11木建国記念の日
2027/2/23火天皇誕生日
2027/3/21日春分の日
2027/3/22月休日
2027/4/29木昭和の日
2027/5/3月憲法記念日
2027/5/4火みどりの日
2027/5/5水こどもの日
2027/7/19月海の日
2027/8/11水山の日
2027/9/20月敬老の日
2027/9/23木秋分の日
2027/10/11月スポーツの日
2027/11/3水文化の日
2027/11/23火勤労感謝の日

祝日リストを作ったら、=COUNTA(祝日) が17になるかを見ておくと安心。2027年は、祝日に挟まれた平日が休みになる「国民の休日」はありません。9月の敬老の日(9/20)と秋分の日(9/23)の間が、2日空いているためです。

1/2と1/3は祝日ではないので、一覧には載っていません。年末年始などの会社の休みも塗るなら、祝日リストの下に日付を足してください。その日付が名前「祝日」の範囲に入っているかも、あわせて確かめましょう。

土日と重なる祝日だけ土日の色になる

3月のシフト表(3行目が3/1)では、23行目の3/21が日曜の春分の日、24行目の3/22が休日です。3/22はオレンジに塗れたのに、3/21だけ日曜の赤のまま。これは、ルールの順番で起きる症状です。

祝日を日曜と同じ色にしているなら、重なっても見た目は変わりません。この節は、祝日に土日とは別の色を付けたい場合の直し方です。

ルールの管理で祝日ルールを一番上に置く(並び順を表で確認)

Microsoftのサポートページによると、複数のルールが当てはまるときは、一覧の上から順に評価されます。新しいルールは、既定で一覧の先頭に追加されるとのこと。祝日ルールを先に作り、あとから土曜・日曜の順にルールを足すと、土日のルールが上に来てしまいます。

順番変更前変更後
1日曜 =WEEKDAY($A3)=1(薄い赤)祝日 =COUNTIF(祝日,$A3)>0(薄いオレンジ)
2土曜 =WEEKDAY($A3)=7(薄い青)日曜 =WEEKDAY($A3)=1(薄い赤)
3祝日 =COUNTIF(祝日,$A3)>0(薄いオレンジ)土曜 =WEEKDAY($A3)=7(薄い青)
  1. シフト表のセルを1つ選び、[ホーム] タブの [条件付き書式] から [ルールの管理] を開きます。
  2. [書式ルールの表示] で、シフト表のワークシートを選んでください。
  3. 祝日ルールを選び、上向きの矢印ボタンで一番上まで移動します。
  4. [OK] を押します。

並び順が変更後の形になれば、23行目の3/21は日曜の赤ではなく、祝日のオレンジで塗られます。

塗る属性が違えば両方効く・同じ属性なら上が勝つ

Microsoftのサポートページでは、競合しないルール同士は両方が適用されると説明されています。例として挙がっているのは、太字と赤色の組み合わせ。書式が競合するときは、順番が上のルールが適用されます。

上のルール下のルール3/21の見た目
祝日:オレンジの塗りつぶし日曜:赤の塗りつぶしオレンジの塗りつぶし
日曜:赤の塗りつぶし祝日:オレンジの塗りつぶし赤の塗りつぶし
祝日:オレンジの塗りつぶし日曜:赤い文字オレンジの塗りつぶし+赤い文字

日曜を文字色で、祝日を塗りつぶしで表している表では、順番を入れ替えても赤い文字は残ります。祝日の日に赤い文字を出したくないなら、次の除外式を使いましょう。

順番に頼らない式(土日の式で祝日を除外する)

土日のルールに「祝日ではない」という条件を足しておけば、並び順に関係なく祝日の色だけが付きます。

土曜: =AND(WEEKDAY($A3)=7,COUNTIF(祝日,$A3)=0)
日曜: =AND(WEEKDAY($A3)=1,COUNTIF(祝日,$A3)=0)
祝日: =COUNTIF(祝日,$A3)>0

3/21の行では、日曜の式の WEEKDAY($A3)=1 は TRUE です。一方で COUNTIF(祝日,$A3) は1なので、=0 の判定が FALSE になります。AND は両方が TRUE のときだけ TRUE を返すため、日曜のルールは適用されません。

格子型カレンダーなら、$A3 を B4 に置き換えて使います。

なお、デスクトップ版の「ルールの管理」には「条件を満たす場合は停止」というチェックもあります。ただ、Microsoftのサポートページでは、旧バージョンの表示を再現するための設定と説明されています。祝日を優先させる目的なら、除外式のほうが式を見るだけで意図が伝わるはず。

よくある質問とまとめ

Q. 翌年に祝日リストを差し替えたら色が付かなくなった

差し替えのあとに確かめたいのは、次の3点です。

  1. 新しいリストが文字列で貼られていないか。=COUNT(祝日) と =COUNTA(祝日) が同じ件数かを見ます。
  2. 新しいリストが名前の範囲からはみ出していないか。[数式] タブの [名前の管理] で、「祝日」の参照範囲を確かめます。
  3. シフト表とリストの年が合っているか。シフト表だけ2027年にしてリストが2026年のままだと、1件も一致しません。

名前の範囲は、=祝日リスト!$A$2:$A$40 のように少し広めに取っておくと、翌年に行が増えても直さずに済みます。空白のセルが範囲に混ざっても、日付との照合には影響しません。

Q. Web 版でも同じ直し方でいい

ルールの数式や $ の付け方、祝日リストの直し方は、デスクトップ版と同じ考え方で使えます。Microsoftのサポートページによると、Web版は作業ウィンドウでルールの作成・編集・削除が可能です。

一方で、ルールの並べ替えや「条件を満たす場合は停止」について、同じページに記載はありません。この記事でも確認できていないため、Web版で祝日を土日より優先させたいときは除外式を使うのが確実です。

まとめ

祝日だけ色が付かないときは、症状で切り分けると直す場所が見えてきます。

  • 祝日が1日も塗れない: リストが文字列でないか(COUNT と COUNTA を比べる)、同じブックにあるか、カレンダー側が本物の日付かを確認
  • 一部だけ塗れない・ずれた日が塗れる: $ の付け方、適用範囲のアクティブセル、3/22のような休日の抜けを確認
  • 土日と重なる日だけ土日の色: 祝日ルールを一番上に移すか、土日の式で祝日を除外する

祝日リストは、営業日の計算にもそのまま使えます。営業日数を数えるなら、ExcelのNETWORKDAYS関数の使い方 が参考になるはず。祝日を飛ばして納期を出す方法は、ExcelのWORKDAY関数の使い方 で解説しています。

条件付き書式の基本操作から見直したいときは、Excelの条件付き書式完全ガイド もどうぞ。

タイトルとURLをコピーしました