Google Spreadsheetでカレンダー表示

ライフハック

よくあるパターンですが、毎年祝日、休日の設定を設定したカレンダー作りをしています。
結構きちんとチェックしたつもりですが、いつも抜け漏れが発生してしまいます。よくあるパターンが、昨年ベタで入力した祝日表記がそのまま残ってしまっているもの。こんなことがないよう、一度仕組みを作ってしまえば、以後簡単です。

手順１：祝日の一覧シートを作る

簡単な日付の羅列で構いません。祝日、振替休日の区別もあると便利です。ここではシート名はHolidayとしておきます。

手順２：続いて以下のようにカレンダーのデザインを作成します

ポイントは以下の通り

  1. A1、A2、A3のセルを結合して数字の2021を入力します。セルは右寄せにして、メニューの[ 表示形式 ] ⇒ [ 数字 ] ⇒ [ 表示形式の詳細設定 ] ⇒ [ カスタム数値形式 ] をたどって、「####年度」と入力しておきます。
  2. B3,C3を結合して、ここも数字の4を入力。上記同様、表示を「##月」となるようにしておきます。同様に、D3,E3、F3,G3…とします。
  3. A5〜A35までは数字を入力します。

ここまで出来たら今度は、各月の曜日を入力します。4月1日のセル（B5）に移動して以下のようにします。

=text(date($A$1,B$3,$A5),"ddd")

すると「木」と表示されたと思います。これだけですと曜日表記は正しいのですが、祝日が適用できていません。さらに数式を追加します。ちなみに$の記号はコピーしてもずれないようにするために入れています。

=if(COUNTIF(Holiday!$A:$A,DATE($A$1,B$3,$A5)), 
vlookup(date($A$1,B$3,$A5), Holiday!$A:$B,2,false),
TEXT(DATE($A$1,B$3,$A5),"ddd"))

ちょっとだけ説明します。長いですね。
if(a,b,c)の式として記載しており、aが成り立てばb、成り立たなければcを表示させるもの。aに当たるものが、COUNTIF(Holiday!A:A,DATE($A$1,$B$3,$A5))で、HolidayシートにあるA列に、指定された日付が存在しているかどうかを確認しています。基本的にあれば1が帰ってきます。
続いて、bに当たるところが、vlookup(date($A$1,$B$3,$A5), Holiday!A:B,2,false)です。vlookup(A,B,C,D)という式は、AをBの中から探して2列目の値を返すということ。2列目には「祝」「休」のいずれかを入れてあるので、便利です。

あとはこれをコピーします。4月1日から30日まで。同様に5月、6月…12月までコピーします。30日までコピーする月と31日までコピーするかは良く判断して下さい。

続いて1月、2月、3月はさらに年が変わりますので、1を追加した数式に変更します。

=if(COUNTIF(Holiday!$A:$A,DATE($A$1+1,T$3,$A14)), 
vlookup(date($A$1+1,T$3,$A14), Holiday!$A:$B,2,false),
TEXT(DATE($A$1+1,T$3,$A14),"ddd"))

2月29日はさらに存在確認をして表示するかどうかを決めます。
以下のように

=if(month(DATE($A$1+1,V$3,$A33))=2, "2月","3月")

とすると、month()で何月かを取り出せて、2であれば2月と表示するものとなります。実は2月に関わらずその日がその月であるか、どうか判定することで、31日の判定をする部分にも使えます。最終的に2月1日のところは、

=if(month(DATE($A$1+1,V$3,$A5))=V$3, 
  if(COUNTIF(Holiday!$A:$A,DATE($A$1+1,V$3,$A5)), 
    vlookup(date($A$1+1,V$3,$A5), 
    Holiday!$A:$B,2,false),
  TEXT(DATE($A$1+1,V$3,$A5),"ddd")),
  "")

となります。

手順３：土曜日、日曜日、祝日の色表示

カーソルを4/1のところに持っていきます。
メニューから、[ 表示形式] ⇒ [ 条件付き書式 ]をクリック。範囲としては、曜日表示の部分全てとなりますので、私の場合こんな形となりました。

B5:B35,D5:D35,F5:F35,H5:H35,J5:J35,L5:L35,N5:N35,P5:P35,R5:R35,T5:T35,V5:V35,X5:X35

書式ルールは、カスタム数式とし、数式は一番最初に出てくるB5のセルの場合だけを記載します。少し不思議な形式ですが、こういうものだと理解しましょう。

最終的に以下のようになりました。

如何でしょうか？
一度、作ってしまえば、翌年はA1のセルを2022などと年だけ変えれば完成です。

コメント

タイトルとURLをコピーしました