スプレッドシートで祝日のセルの背景色を変える

エクセルやスプレッドシートでシフト表や勤怠管理表を作成する時、土日や祝日の色を変えておくと色々と捗りますのでスタイルの変更の仕方を紹介します。
この記事は祝日の判定に絞っていますので、土日のスタイルの変更の仕方は以下の記事を参考にして下さい。
条件付き書式ルールがわからないという方はこちらから読むと良いかもしれません。
【条件付き書式ルール】スプレッドシートで土日の文字色・背景色を変更する

手順としては以下の様に作っていきます。

1.別シートに国民の祝日リストを作成する

メンテナンス性を考えるとどこかしらのAPIから国民の祝日を引っ張ってきて計算させるのが一番良いのですが、今回はシンプルに同一スプレッドシート内の別シートに祝日一覧を手動で作成し、そのデータを参照して判別する方法を採用します。
デメリットとしては毎年祝日の管理を手動でしなければならない点ですが、マスターデータを毎年ごとに１度だけ作成すれば良いので運用でカバーできる範囲内でしょう。マスターデータの作成とか格好つけて言っていますが、簡単に言うと「年１回、内閣府のサイトをコピペするだけ」ですので安心してください。

リストの作成

別シートの作成

まず国民の祝日を入れておくための別シートを作成します。

画面左下の「 ＋ 」を押して新しいシートを追加します。
シート2というシートが追加されるのでタブをクリックして名前を変更しましょう。
この記事では「Holidays」という名前をつけておきました。

国民の祝日のリストを探す

国民の祝日を提供しているサイトは沢山ありますが、内閣府のウェブサイトを参考にすれば間違えは起こらないでしょう。
参考 国民の祝日について内閣府

国民の祝日一覧をシートにコピーする

内閣府の祝日一覧はtableタグで構成されているのでそのままコピーアンドペーストでスプレッドシートに貼り付けられます。
一覧の一番上から一番下まで選択し、

シート「Holidays」に貼り付けます。

これで別シート（タブ）「Holidays」に国民の祝日リストを作成できました。

注意して欲しいのは今回判定するのは国民の「祝日」であって国民の「休日」や振替休日ではないということです。
例えば2015年の敬老の日（9/21）、春分の日（9/23）に挟まれている9月22日の国民の休日はこのリストには含まれていません。国民の休日や振替休日はこのリストに含まれていないため、従業員の休日出勤等を管理する場合はシートに手動で入力する等の対処をする必要があります。今回の例でいくとA17に「 国民の休日1 」、B17に「 2015/09/22 」と手動で入力すれば背景色等のスタイルを変更することが可能になります。
次の項で私自身が従業員の給与計算表を作成した時に国民の祝日・休日等について調べたことをまとめておきます。なお私も国民の祝日や労基法についてそこまで詳しいわけではないので、間違っている点があったらご指摘頂けると助かります。

国民の祝日・国民の休日とは

『国民の祝日の日付が変わる可能性がある』、『春分の日、秋分の日は毎年変わる』という点は今回のようにAPIを使用せずに毎年手動で祝日リストを作成する場合には懸念する必要はないでしょう。
シフト表や給与計算等に使用する場合は、組織によって法定休日や所定休日が異なるため、作成するスプレッドシートの目的によって計算式等を変える必要があります。ご注意下さい。
振替休日、国民の休日、法定休日などが絡んだ給与計算をする場合は社労士の方と相談しながら作成したほうが良いでしょう。

国民の祝日の定義

「国民の祝日に関する法律」では以下のように定義しています。
ただここで言う『休日とする』とは労働基準法上の法定休日とは関係が無いため注意しましょう。

国民の祝日について – 内閣府
国民の祝日に関する法律（昭和２３年法律第１７８号）
第3条
「国民の祝日」は、休日とする。
2.「国民の祝日」が日曜日に当たるときは、その日後においてその日に最も近い「国民の祝日」でない日を休日とする。
3.その前日及び翌日が「国民の祝日」である日（「国民の祝日」でない日に限る。）は、休日とする。

「国民の祝日」と「国民の休日」、「振替休日」はどう違うのか

「国民の祝日」
『国民の祝日に関する法律 第2条 』によって定められる。先ほどの内閣府のリストに載っていた日。逆を言うと「振替休日」や「国民の休日」は先のリストには載っていない。

「振替休日」
上の「国民の祝日に関する法律第3条第2項」に書いてあるとおり、国民の祝日が他の休日（日曜日、その他の祝祭日）と重なった場合に、その日に最も近い「国民の祝日」でない日です。
参考 : 国民の祝日に関する法律第３条第２項に規定する休日の例

「国民の休日」
国民の祝日に挟まれた平日。Wikipediaなどに発生予定日が書いてありますが、
法改正や新しい天皇の誕生により国民の祝日が増減する可能性もあるので注意が必要です。
参考 : 国民の祝日に関する法律第３条第３項に規定する休日の例

春分の日・秋分の日

春分の日、秋分の日は毎年変動し、前年２月１日に暦要項が官報に掲載されるまでは確定していません。しかしながら国立天文台が2030年までの計算をしていますのでこちらを参考にする事ができます。
質問3-1）何年後かの春分の日・秋分の日はわかるの？
ただあくまで計算上での数値であり、官報に掲載されるまでは確定していないということを頭に入れておく必要があります。

国民の祝日について – 内閣府
祝日のうち、「春分の日」及び「秋分の日」は、法律で具体的に月日が明記されずに、それぞれ「春分日」、「秋分日」と定められています。「春分の日」及び「秋分の日」については、国立天文台が、毎年２月に翌年の「春分の日」、「秋分の日」を官報で公表しています。

2.調べたい日付がリストに載っているかを調べる式を作る

国民の祝日の解説で長くなりましたが本題に入ります。条件付き書式ルールの使い方がわからない方は、以下の記事の「条件付き書式ルールで背景色を変える」を読んで頂ければ幸いです。
【条件付き書式ルール】スプレッドシートで土日の文字色・背景色を変更する

COUNTIF関数を利用する

祝日を判定する方法はたくさんあると思いますが、COUNTIFで進めていきます。
関数名の通り「IF(もしも)〜だったら数を増やす（COUNT）」という関数です。

COUNTIF
COUNTIF – ドキュメント エディタ ヘルプ
範囲内で条件に一致する要素の個数を返します。
使用例
COUNTIF(A1:A10,”>20″)
COUNTIF(A1:A10,”支払済み”)
構文
COUNTIF(範囲, 条件)
範囲 – 条件の検証対象となる範囲です。
条件 – 範囲に適用するパターンまたはテストです。

ということで以下の式で祝日の数をカウントします。
=COUNTIF(Holidays!B2:B50,A2)>0
「Holidays」シートのB2からB50までの値がA2と一致してたらカウントが増えます。そしてカウントが 0 以上だったらtrueが返されます。ループで回すイメージですね。
将来的には既存の祝日と天皇誕生日がかぶったりする可能性もあるので「 =1 」ではなく「 >0 」で演算します。
「Holidays」シートのB列で祝日が載っているのはB2-B16までですが、
毎年祝日の数が変わる毎に各月シートの条件付き書式を変更するのは面倒なのでB50までを範囲に加えておきます。
これで祝日の数が49個まで増えても月のデータをいじらなくて良くなります。

条件付き書式設定ルールでは他シートが参照できない

このまま条件付き書式設定に貼り付けたいところですが、2015年4月現在のgoogle スプレッドシートでは条件付き書式内で直接他シートを参照することができません。
エクセルだと可能だそう（検証はしていません）ですが、google スプレッドシートではまだ対応していないようです。

上記の画像ではカスタム数式「 =COUNTIF(Holidays!B2:B50,A2)>0 」でA2に入っている値と「Holidays」シート内B2-B23の値を比較できるはずなのですが、『計算式が無効です』というエラーが表示されルールを保存することができません。
ダブルクオーテーション「 “” 」をつけ、「=COUNTIF(“Holidays!B2:B50”,A2)>0」とすればエラーは出ないのですが、祝日である 2015/01/01 を COUNTIF しても FALSE が帰ってくるので恐らく文字列として扱われてしまうのでしょう。
調べてみると INDIRECT関数をかませれば条件付き書式内から他シートの参照ができるそうです。
INDIRECT – ドキュメント エディタ ヘルプ
ではそろそろまとめてみましょう。

3.式を適用する

シート内で右クリック→「条件付き書式…」をクリック。右に出てきたメニューから「新しい条件を追加」をクリック。

「範囲に適応」はルールを適応したい領域。
カスタム数式に入力する引数はA2にしてありますが、範囲をA2-A32にしてもオートフィルしてくれます。
「セルの書式設定の条件…」のプルダウンを「カスタム数式」にセットし、
=COUNTIF( INDIRECT( “Holidays!B2:B50” ),A2)>0
を入力。
あとはお好きなスタイルを自分のお好みで変えれば完成です。

元旦（ 1月1日 ）と成人の日（ 1月12日 ）の背景色を変えることができました。
以下の記事を参考に土日も変更すればシフト表等の作成が劇的に楽になります。
【条件付き書式ルール】スプレッドシートで土日の文字色・背景色を変更する