Excelで土日に色を付けるには？WEEKDAY関数の数式と祝日のCOUNTIF、行全体を塗る$の付け方と色が付かない5つの原因

※本記事には広告・アフィリエイトリンクが含まれます。

勤務表や予定表を作るたびに、土曜と日曜の行を一つずつ手で塗っていないでしょうか。X（旧Twitter）でも、Excelの効率化を発信しているアカウントが「土日を、毎月手で赤くしていませんか?」と呼びかけ、手で塗ると行を足すたびに塗り直しになると指摘していました。月が変われば曜日の並びも変わるので、手作業の色塗りは毎月同じ手間を生みます。

この記事では、Microsoftの公式サポートにあるWEEKDAY関数と条件付き書式の説明、内閣府が公開している国民の祝日の一覧、Excel解説サイトの手順を突き合わせて、土日と祝日に自動で色を付ける方法を整理しました。数式の意味、行全体を塗るときの$の付け方、色が付かないときに確かめる点まで、仕事の表にそのまま使える順番でまとめています。

WEEKDAY関数で曜日を数字に変えるのが出発点

楽天でExcel関数の解説書を探す

条件付き書式で土日を見分けるには、まず日付から曜日を取り出す必要があります。その役目を担うのがWEEKDAY関数で、日付を曜日の番号に置き換えてくれます。番号の振り方には何通りもあるため、最初にここを押さえておくと後の数式で迷いません。

WEEKDAY関数の「週の基準」と、土日判定に扱いやすい書き方

Microsoftのサポートページによると、WEEKDAY関数の書式は「WEEKDAY(シリアル値,[週の基準])」です。週の基準を省略するか1を指定すると、日曜が1、土曜が7になる1から7の整数が返り、これは以前のバージョンのExcelと同じ動きだと説明されています。2を指定すると月曜が1、日曜が7になり、3では月曜が0、日曜が6という0始まりの数え方に変わります。

このほか11から17までの値も用意されていて、11は月曜始まり、12は火曜始まり、というように週の始まりを1日ずつずらせる仕組みです。公式ページの使用例では、2008年2月14日（木曜日）に対して、週の基準なしで5、2を指定すると4、3を指定すると3が返ると示されていました。同じ日付でも引数一つで答えが変わるので、数式を人と共有するときは、どの基準で書いたかを添えておくと読み違いが減るはずです。

そもそもWEEKDAY関数が日付から曜日を割り出せるのは、Excelが日付を連続した数値として持っているからです。公式ページの解説によると、既定では1900年1月1日がシリアル値1として保存され、2008年1月1日はそこから39,448日後に当たるので39448という値になります。シリアル値が日付の範囲から外れていたり、週の基準に表にない値を入れたりすると、#NUM!エラーが返るとも書かれていました。

土日をまとめて判定したいなら、週の基準に2を指定する書き方が便利です。月曜を1として数えると土曜が6、日曜が7になるので、「6以上なら週末」という一つの比較で済みます。

Excel解説サイトのグッドワークラボでも、土日を同じ色にする条件として「=WEEKDAY($A2,2)>=6」を基本形に挙げていました。

一方、週の基準を省略する書き方では日曜が1、土曜が7と両端に分かれてしまいます。インストラクターのネタ帳では、この場合の土日判定を「=OR(WEEKDAY($A5)=1,WEEKDAY($A5)=7)」とOR関数でつなぐ形で紹介しています。どちらも結果は同じですが、比較が一つで済む前者のほうが短く、後から見直したときにも意味を追いやすいでしょう。土曜と日曜で色を分けたいときは、週の基準2なら「=6」と「=7」の2つのルールを作れば足ります。

条件付き書式に数式を入れて土日の行を塗る手順

楽天でExcelの時短ワザの本を見る

曜日を数字にできたら、次はその数式を条件付き書式に登録します。ポイントは、色を付ける範囲を先に選ぶことと、数式の中で日付の列だけを固定することの2つです。この2点を押さえると、日付のセルだけでなく予定や担当者の欄まで同じ色に塗れます。

「数式を使用して…」で設定し、で行全体に色を付ける

グッドワークラボの手順では、見出しを除いた表の範囲（例ではA2:C8）を選び、「ホーム」タブの「条件付き書式」から「新しいルール」を開きます。続いて「数式を使用して、書式設定するセルを決定」を選び、入力欄に土日を判定する数式を入れて、「書式」で塗りつぶしの色を指定すれば完成です。日付セルだけを塗りたい場合は、A列だけを選んでから同じ操作をすればよいとも書かれていました。

Microsoftの条件付き書式の解説では、ここで入れる数式は「True または False (1 または 0) を返す必要があります」とされています。エクセル実践塾も、COUNTIF関数の結果が1になれば条件付き書式では真とみなされると説明していました。つまり、比較の結果がTRUEになる行にだけ色が付く、という単純な仕組みです。手で塗った色との関係について、公式ページは条件付き書式のルールがTRUEと評価されると同じ範囲の手動の書式より優先されると記しています。

行全体を塗るときに一番つまずきやすいのが、数式の$の位置です。「$A2」はA列だけを固定し、行番号は2、3、4と動く書き方で、B列やC列のセルもA列の日付を見て判定するようになります。グッドワークラボは、これを「$A$2」と書くと行番号まで固定され、すべての行がA2だけを見て判定してしまうと注意していました。表全体が同じ色になったときは、まずこの違いを疑ってみてください。

もう一つ大切なのが、数式に書く行番号と選んだ範囲の先頭をそろえることです。適用先がA2から始まるなら数式も$A2から、A3から始まるなら$A3から書く必要があります。大体でITの解説でも、選択した範囲の一番左上のセルを数式に入れるのがポイントだとされていました。Microsoftも、選択範囲の各セルに合わせて参照を自動で調整させるには相対参照を使うよう案内しています。

数式の入力欄でセル番地を選んでF4キーを押すと、「$A$2」「A$2」「$A2」「A2」の順に切り替わります。列だけを固定する「$A2」は3回押したときの形なので、手で$を打つより間違いを防ぎやすいですよ。

祝日は内閣府の一覧とCOUNTIF関数で判定する

楽天で2027年の卓上カレンダーを探す

土日と違い、祝日は曜日から計算で求められません。グッドワークラボが「Excelは、日本の祝日を自動で持っているわけではありません」と書いているとおり、祝日の日付を一覧にして、そこに含まれるかどうかを調べる方法が一般的です。一覧の作り方と照合の数式を順に見ていきます。

内閣府のcsvで祝日リストを作り、COUNTIF関数で判定する

内閣府の「国民の祝日について」のページには、令和8年（2026年）と令和9年（2027年）の祝日・休日が表で掲載されています。2026年でいえば、5月6日と9月22日の「休日」も含まれていました。前者は祝日が日曜日に当たったときの振替休日、後者は前日と翌日が祝日に挟まれた日の休日で、それぞれ祝日法第3条第2項と第3項に基づくと備考に書かれています。

同じページには、昭和30年（1955年）から令和9年（2027年）までの国民の祝日をまとめたcsvファイルも置かれていました。2028年の祝日は、前年の2027年2月に掲載すると案内されています。祝日リストは別のシートに縦一列で入れておき、会社独自の休業日があれば同じ列に足しておくと、一つのルールで両方に色を付けられて便利です。祝日法では年間に計16の日が「国民の祝日」とされ、成人の日は1月の第2月曜日、海の日は7月の第3月曜日というように、曜日で決まる日も含まれています。春分の日と秋分の日も年によって日付が動くため、前の年の一覧を日付だけ書き換えて使い回すのは避けたほうが安全です。エクセル実践塾も、祝日一覧は内閣府のWebなどから入手できると紹介していました。

祝日の判定にはCOUNTIF関数を使います。Microsoftの説明では、書式は「COUNTIF(範囲, 検索条件)」で、範囲の中で条件に合うセルの数を数える関数です。祝日リストがE2:E20にあるなら、グッドワークラボの例のように「=COUNTIF($E$2:$E$20,$A2)>0」と書けば、A列の日付が一覧に1件以上ある行だけがTRUEになります。

ここでの$の付け方は土日の数式と違い、祝日リストは「$E$2:$E$20」と行も列も固定します。固定しないままだと、ルールが下の行に当てはめられるたびに参照範囲もずれてしまうと解説されていました。インストラクターのネタ帳では、リストに「休日」という名前を付けて「COUNTIF(休日,$A5)>0」と書く方法も紹介されています。なお同じ記事は、MATCH関数を使った式ではOR関数で土日判定とまとめたときに正しく設定できないと指摘していたため、迷ったらCOUNTIFを選ぶのが無難でしょう。

Microsoftの説明によれば、COUNTIF関数で指定できる検索条件は一つだけで、複数の条件で数えたいときはCOUNTIFS関数を使うよう案内されています。祝日判定の場合は「この日付と同じ値があるか」という一つの条件で足りるため、COUNTIFで十分です。ちなみにXでは、日付の横に「土」「日」といった曜日の文字を表示している表で「=OR($B2=”土”,$B2=”日”)」と文字を比べる手順を紹介する投稿も見かけました。曜日を文字で表示している表ならこの書き方でも塗れますが、日付を基準にするWEEKDAYのほうが、曜日の列を消したときにも動き続けます。

色が付かない・付きすぎるときに確かめる点

楽天でExcel入門書を探す

数式を入れたのに思いどおりに塗られないときは、原因がいくつかの型に分かれます。日付のデータそのもの、数式の書き方、ルール同士の関係の3つに分けて確かめると、たいていはすぐに見つかるものです。ここでは解説サイトと公式ページに書かれていた確認点をまとめました。

空白行・文字列の日付・時刻のつまずきと、ルールの評価順

日付を入れていない下のほうの行まで色が付く場合、グッドワークラボはWEEKDAY関数が空白セルを日付の数値として扱う場合があると説明しています。対策は、数式の先頭に「$A2<>””」を加えて、空白でないことを先に確かめる形です。土日の数式なら「=AND($A2<>””,WEEKDAY($A2,2)>=6)」となり、AND関数で両方を満たす行だけが塗られます。

見た目は日付でも文字列として入っている場合も、正しく判定されません。Microsoftの公式ページも、WEEKDAY関数に日付を文字列として入力した場合はエラーが発生することがあると書いています。表示形式を変えるだけでは直らないことがあるため、日付データに変換し直すのが確実です。また、2026/8/12 9:00のように時刻まで入っていると日付だけの祝日リストと完全には一致しません。その場合は「=AND($A2<>””,COUNTIF($E$2:$E$20,INT($A2))>0)」のようにINT関数で日付部分だけを取り出す方法が紹介されていました。

土曜を青、日曜と祝日を赤にするなど複数のルールを作ると、祝日が土曜に重なった日はどちらの色になるのかが問題になります。Microsoftの解説では、複数のルールが当てはまる場合、「条件付き書式ルールの管理」ダイアログの一覧に並んだ順に上から評価されるとのことです。祝日の赤を優先したいなら、祝日のルールを一覧の上に移しておきましょう。

同じ画面には「条件を満たす場合は停止」というチェックボックスもあります。公式ページでは、これは下位互換のため、複数のルールに対応していない以前のバージョンのExcelでの見え方を再現する機能だと説明されていました。色を分ける必要がなく、土日も祝日も同じ色でよいなら、「=AND($A2<>””,OR(WEEKDAY($A2,2)>=6,COUNTIF($E$2:$E$20,$A2)>0))」とORでまとめた一つのルールにすれば、順番を気にする必要自体がなくなります。