You are here: Home \ Google Drive ・ Office \ スプレッドシート・エクセル \ 【スプレッドシート】別シートの国民の祝日一覧から判定してセルの背景色を変える

    【スプレッドシート】別シートの国民の祝日一覧から判定してセルの背景色を変える

    エクセルやスプレッドシートでシフト表や勤怠管理表を作成する時、土日や祝日の色を変えておくと色々と捗りますのでスタイルの変更の仕方を紹介します。

    この記事は祝日の判定に絞っていますので、土日のスタイルの変更の仕方は以下の記事を参考にして下さい。
    条件付き書式ルールがわからないという方はこちらから読むと良いかもしれません。
    【スプレッドシート】土日の背景色を条件付き書式ルールを利用して変える

    手順としては以下の様になります

    1.別シートに国民の祝日リストを作成する
    2.調べたい日付がリストに載っているかを調べる式を作る
    3.調べたい日付がリストに載っていたら色(スタイル)を変える条件付き書式ルールが適用されるようにする

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

    メンテナンス性を考えるとどこかしらのAPIから国民の祝日を引っ張ってきて計算させるのが一番良いのですが、
    今回はシンプルに同一ファイル内の別シートに祝日一覧を手動で作成し、そのデータを参照して判別する方法を採用します。
    デメリットとしては毎年祝日の管理を手動でしなければならない点ですが、
    マスターデータを毎年ごとに1度だけ作成すれば良いので運用でカバーできる範囲内でしょう。

    リストの作成

    別シートの作成

    まず国民の祝日を入れておくための別シートを作成します。
    20150426sc1
    画面左下の「 + 」を押して新しいシートを追加します。
    シート2というシートが追加されるのでタブをクリックして名前を変更しましょう。
    この記事では「Holidays」という名前をつけておきました。

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

    国民の祝日を提供しているサイトは沢山あります。
    内閣府のウェブサイトを参考にすれば問題ないでしょう。
    国民の祝日について - 内閣府

    個人が運営しているサイトやスクリプト等で自動生成しているサイトを参考にする場合は
    漏れがあったり春分の日等の計算が間違っている可能性もあるのでご注意下さい。

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

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

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

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

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

    扱うデータによっては単純に国民の祝日を判定すれば良いというわけではないケースも存在します。
    例えば勤怠管理のデータを直接給与計算に結びつけている場合は休日手当の計算等が絡んでくるため注意が必要です。

    国民の祝日のデータを扱う時に注意しなければならないのは
    ・国民の祝日の日付が変わる可能性がある(例:ハッピーマンデー制に移行、2016年に8/11「山の日」が追加、天皇の崩御 等)
    ・春分の日、秋分の日は毎年変わる
    ・振替休日の扱い
    ・「国民の祝日」に挟まれた平日は「国民の休日」となる
    ・国民の祝日は法定休日ではないため組織ごとに扱いが異なる

    といったところでしょうか。
    『国民の祝日の日付が変わる可能性がある』、『春分の日、秋分の日は毎年変わる』という点は
    今回のようにAPIを使用せずに毎年手動で祝日リストを作成する場合には懸念する必要はないでしょう。

    シフト表や給与計算等に使用する場合は、組織によって法定休日や所定休日が異なるため、
    作成するスプレッドシートの目的によって計算式等を変える必要があります。ご注意下さい。
    振替休日、国民の休日、法定休日などが絡んだ給与計算をする場合は社労士の方と相談しながら作成したほうが良いでしょう。

    国民の祝日の定義

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

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

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

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

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

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

    春分の日・秋分の日

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

    国民の祝日について - 内閣府
    祝日のうち、「春分の日」及び「秋分の日」は、法律で具体的に月日が明記されずに、それぞれ「春分日」、「秋分日」と定められています。

    「春分の日」及び「秋分の日」については、国立天文台が、毎年2月に翌年の「春分の日」、「秋分の日」を官報で公表しています。

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

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

    また他のシートの参照の仕方がわからない方はこちら。
    hogee

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

    ではそろそろまとめてみましょう。

    3.式を適用する

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

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

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

    About author:

    2件のコメント on "【スプレッドシート】別シートの国民の祝日一覧から判定してセルの背景色を変える"

    匿名 - 16 7月 2015 返信

    記載されている方法を試してみたのですが、 =COUNTIF( INDIRECT( "Holidays!B2:B50" ),A2)>0 を入れると計算中にエラーが発生しました~というエラー表記が出てしまい 祝日の色変えができませんでした。 原因などおわかりになりますでしょうか。

      miyadir - 17 7月 2015 返信

      匿名 様 考えられる可能性としては ・休日を入れているシートの名前が「Holidays」でない ・参照するセル(この場合B2:B50、A2)の指定が間違っている 辺りではないでしょうか? もしgoogleスプレッドシートではなくMSエクセルを使用しているならばまた別の原因があるかもしれません。

    miyadir にコメントする コメントをキャンセル