Excelで土日・祝日に自動で色付けする方法は？条件付き書式の設定手順と祝日リストの作り方

行全体（横一列）に色を付ける方法

カレンダーやシフト表で、日付のセルだけでなく行全体に色を付けたい場合は、色を付けたい表全体を選択し、数式の列の部分に「$」を付けて固定するのがポイントです。

色を付けたい対象	数式の例（表の範囲がA2:E32の場合）
土曜日の行	=WEEKDAY($A2)=7
日曜日の行	=WEEKDAY($A2)=1
祝日の行	=COUNTIF(祝日!$A:$A,$A2)>0

「$A2」のように列だけを固定すると、どの列のセルでもA列の日付を基準に判定されるため、行全体に同じ色が付きます。

祝日を判定して「祝日」と表示する方法

色を付けるのではなく、隣のセルに「祝日」と表示したい場合は、IF関数とCOUNTIF関数を組み合わせましょう。B2セルに次の数式を入力し、下の行までコピーしてください。

=IF(COUNTIF(祝日!$A:$A,A2)>0,”祝日”,””)

A2の日付が祝日リストに含まれていれば「祝日」と表示され、含まれていなければ空欄になります。祝日名まで表示したい場合は、XLOOKUP関数を使って「=XLOOKUP(A2,祝日!$A:$A,祝日!$B:$B,””)」とすれば、祝日シートのB列にある祝日名の表示も可能です。

祝日リストを自動で更新する方法

Power Queryで内閣府のCSVを取り込む

毎年手作業でリストを更新するのが面倒な場合は、Power Queryで内閣府のCSVを直接取り込む方法を試してみましょう（※）。

1. 「データ」タブ→「Webから」をクリックする
2. URL欄に内閣府の祝日CSVのアドレス（https://www8.cao.go.jp/chosei/shukujitsu/syukujitsu.csv）を入力し、「OK」をクリックする
3. プレビュー画面で文字化けしている場合は、「元のファイル」を「932：日本語（シフトJIS）」に変更する
4. 「読み込み」をクリックして、シートに祝日リストを出力する

※参考：内閣府「国民の祝日について」

一度設定すれば、「データ」タブの「すべて更新」をクリックするだけで、内閣府の最新の祝日データを取り込み直せます。条件付き書式の数式は、取り込んだシートの日付の列を参照するように変更してください。

非公式のAPIやWEBSERVICE関数を使う場合の注意点

インターネット上には、祝日を判定できる非公式のAPIもあり、WEBSERVICE関数と組み合わせて使う方法も紹介されています。

ただし、WEBSERVICE関数はWindows版のExcelでのみ使え、Mac版やWeb版では利用できません。また、非公式のサービスは提供が終了する可能性もあるため、業務で使うなら公式のCSVを取り込む方法がおすすめです。

色が付かないときの原因と対処法

原因	対処法
日付が文字列として入力されている	セルの表示形式を「日付」にし、日付として入力し直す
数式の参照セルが選択範囲の先頭とずれている	選択範囲の先頭セル（例：A2）を数式に指定する
行全体に色を付けたいのに一部しか色が付かない	数式の列に「$」を付けて固定する（例：$A2）
祝日なのに土曜日の色になる	「ルールの管理」で祝日のルールを一番上に移動する
新しい年の祝日に色が付かない	祝日リストに翌年分の祝日を追加する