2019年10月1日SHAREツイートシェアはてブLINEPocketエクセルやスプレッドシートでシフト表や勤怠管理表を作成する時、土日や祝日の色を変えておくと色々と捗りますのでスタイルの変更の仕方を紹介します。この記事は祝日の判定に絞っていますので、土日のスタイルの変更の仕方は以下の記事を参考にして下さい。条件付き書式ルールがわからないという方はこちらから読むと良いかもしれません。【条件付き書式ルール】スプレッドシートで土日の文字色・背景色を変更する手順としては以下の様に作っていきます。別シートに国民の祝日リストを作成する調べたい日付がリストに載っているかを調べる式を作る調べたい日付がリストに載っていたら色(スタイル)を変える条件付き書式ルールが適用されるようにするでは1からやっていきましょう。国民の祝日・国民の休日とは春分の日・秋分の日2.調べたい日付がリストに載っているかを調べる式を作るCOUNTIF関数を利用する条件付き書式設定ルールでは他シートが参照できない3.式を適用する1.別シートに国民の祝日リストを作成するメンテナンス性を考えるとどこかしらのAPIから国民の祝日を引っ張ってきて計算させるのが一番良いのですが、今回はシンプルに同一スプレッドシート内の別シートに祝日一覧を手動で作成し、そのデータを参照して判別する方法を採用します。デメリットとしては毎年祝日の管理を手動でしなければならない点ですが、マスターデータを毎年ごとに1度だけ作成すれば良いので運用でカバーできる範囲内でしょう。マスターデータの作成とか格好つけて言っていますが、簡単に言うと「年1回、内閣府のサイトをコピペするだけ」ですので安心してください。別シートの作成まず国民の祝日を入れておくための別シートを作成します。」を押して新しいシートを追加します。シート2というシートが追加されるのでタブをクリックして名前を変更しましょう。この記事では「Holidays」という名前をつけておきました。国民の祝日のリストを探す国民の祝日を提供しているサイトは沢山ありますが、内閣府のウェブサイトを参考にすれば間違えは起こらないでしょう。民間のサイトは精度に注意もし内閣府ではなく、民間・個人が運営しているサイトやスクリプト等で自動生成しているサイトを参考にする場合は漏れがあったり春分の日等の計算が間違っている可能性もあるのでご注意下さい。国民の祝日一覧をシートにコピーする内閣府の祝日一覧はtableタグで構成されているのでそのままコピーアンドペーストでスプレッドシートに貼り付けられます。一覧の一番上から一番下まで選択し、シート「Holidays」に貼り付けます。これで別シート(タブ)「Holidays」に国民の祝日リストを作成できました。注意して欲しいのは今回判定するのは国民の「祝日」であって国民の「休日」や振替休日ではないということです。2015年の敬老の日(9/21)、春分の日(9/23)に挟まれている9月22日の国民の休日はこのリストには含まれていません。国民の休日や振替休日はこのリストに含まれていないため、従業員の休日出勤等を管理する場合はシートに手動で入力する等の対処をする必要があります。」と手動で入力すれば背景色等のスタイルを変更することが可能になります。次の項で私自身が従業員の給与計算表を作成した時に国民の祝日・休日等について調べたことをまとめておきます。私も国民の祝日や労基法についてそこまで詳しいわけではないので、間違っている点があったらご指摘頂けると助かります。単純に祝日(国民の休日ではなく祝日のみ)の背景色を変えられば良いという方は「2.調べたい日付がリストに載っているかを調べる式を作る」まで読み飛ばして下さい。この項目は主に勤怠管理や給与管理を行う方向けです。国民の休日も色を変えないといけないという方は読み飛ばさないほうが良いです。扱うデータによっては単純に国民の祝日を判定すれば良いというわけではないケースも存在します。勤怠管理のデータを直接給与計算に結びつけている場合は休日手当の計算等が絡んでくるため注意が必要です。国民の祝日のデータを扱う時に注意しなければならないのは以下のような点です。等)春分の日、秋分の日は毎年変わる振替休日の扱い「国民の祝日」に挟まれた平日は「国民の休日」となる国民の祝日は法定休日ではないため組織ごとに扱いが異なる『国民の祝日の日付が変わる可能性がある』、『春分の日、秋分の日は毎年変わる』という点は今回のようにAPIを使用せずに毎年手動で祝日リストを作成する場合には懸念する必要はないでしょう。シフト表や給与計算等に使用する場合は、組織によって法定休日や所定休日が異なるため、作成するスプレッドシートの目的によって計算式等を変える必要があります。振替休日、国民の休日、法定休日などが絡んだ給与計算をする場合は社労士の方と相談しながら作成したほうが良いでしょう。「国民の祝日に関する法律」では以下のように定義しています。ここで言う『休日とする』とは労働基準法上の法定休日とは関係が無いため注意しましょう。「国民の祝日」は、休日とする。2.「国民の祝日」が日曜日に当たるときは、その日後においてその日に最も近い「国民の祝日」でない日を休日とする。3.その前日及び翌日が「国民の祝日」である日(「国民の祝日」でない日に限る。』によって定められる。先ほどの内閣府のリストに載っていた日。「国民の祝日に関する法律第3条第2項」に書いてあるとおり、国民の祝日が他の休日(日曜日、その他の祝祭日)と重なった場合に、その日に最も近い「国民の祝日」でない日です。国民の祝日に挟まれた平日。法改正や新しい天皇の誕生により国民の祝日が増減する可能性もあるので注意が必要です。春分の日、秋分の日は毎年変動し、前年2月1日に暦要項が官報に掲載されるまでは確定していません。国立天文台が2030年までの計算をしていますのでこちらを参考にする事ができます。質問3-1)何年後かの春分の日・秋分の日はわかるの?ただあくまで計算上での数値であり、官報に掲載されるまでは確定していないということを頭に入れておく必要があります。「春分の日」及び「秋分の日」については、国立天文台が、毎年2月に翌年の「春分の日」、「秋分の日」を官報で公表しています。2.調べたい日付がリストに載っているかを調べる式を作る国民の祝日の解説で長くなりましたが本題に入ります。条件付き書式ルールの使い方がわからない方は、以下の記事の「条件付き書式ルールで背景色を変える」を読んで頂ければ幸いです。【条件付き書式ルール】スプレッドシートで土日の文字色・背景色を変更するCOUNTIF関数を利用する祝日を判定する方法はたくさんあると思いますが、COUNTIFで進めていきます。関数名の通り「IF(もしも)〜だったら数を増やす(COUNT)」という関数です。ヘルプ範囲内で条件に一致する要素の個数を返します。条件の検証対象となる範囲です。範囲に適用するパターンまたはテストです。いうことで以下の式で祝日の数をカウントします。=COUNTIF(Holidays!B2:B50,A2)>0「Holidays」シートのB2からB50までの値がA2と一致してたらカウントが増えます。以上だったらtrueが返されます。ループで回すイメージですね。毎年祝日の数が変わる毎に各月シートの条件付き書式を変更するのは面倒なのでB50までを範囲に加えておきます。これで祝日の数が49個まで増えても月のデータをいじらなくて良くなります。スプレッドシートでは条件付き書式内で直接他シートを参照することができません。スプレッドシートではまだ対応していないようです。」でA2に入っている値と「Holidays」シート内B2-B23の値を比較できるはずなのですが、『計算式が無効です』というエラーが表示されルールを保存することができません。帰ってくるので恐らく文字列として扱われてしまうのでしょう。INDIRECT関数をかませれば条件付き書式内から他シートの参照ができるそうです。ヘルプではそろそろまとめてみましょう。3.式を適用するシート内で右クリック→「条件付き書式...」をクリック。右に出てきたメニューから「新しい条件を追加」をクリック。「範囲に適応」はルールを適応したい領域。カスタム数式に入力する引数はA2にしてありますが、範囲をA2-A32にしてもオートフィルしてくれます。),A2)>0を入力。あとはお好きなスタイルを自分のお好みで変えれば完成です。)の背景色を変えることができました。以下の記事を参考に土日も変更すればシフト表等の作成が劇的に楽になります。