【基本】スプレッドシートで作る祝日入り万年カレンダー
Excel2002よりの使者。うにですこんにちは。はるか昔に先輩インストラクターが作った万年カレンダーを見て感動を覚えてから幾星霜、まさか20年近くこの仕事をすることになろうとは…ということで悠久の時を刻むカレンダーの作り方をご紹介します。
インターネットを検索すると山のように出てくるやり方ですが、毎回最初の関数忘れちゃうので自分用備忘録
月と祝日マスタを作ろう
前回のポストで「開発にはマスタ必須」を覚えたので最初にマスタ用シートを作っていきます。
内閣府ホームページから祝日一覧をコピペしよう
今回のカレンダーには祝日を反映させたいので、あらかじめ内閣府のホームページから祝日一覧をコピペしておきましょう。
このままだとVLOOKUP関数で日付から祝日名が検索できないので、A列とB列を入れ替えます。
スプレッドシートは列番号をドラッグすれば列の入れ替えができるので超便利
あとはプルダウンメニューで月が選べるように1～12までのリストも同じシートに作っておきます。
範囲に名前をつけておこう
範囲選択でいちいちマスタシートまで戻ってドラッグしたくないので、あらかじめ使う範囲には名前をつけておこう。
カレンダー用シートを作ろう
準備ができたところでカレンダー用のシートをつくります。
曜日を入力しよう
そしてこれがないと万年カレンダーにならないとっても重要な関数をA3とB3にいれていきます。
date関数
=date($A$1,$A$2,1)絶対参照はかけなくてもいいけど、念の為。3つ目の日は１にすることで何月であってもその月の初日が出るようになります。
weekday関数
=weekday($A$3,2)２番目の引数は何曜日始まりにするかを決められる。１なら日曜はじまり、２は月曜始まり。今回は月曜始まりのカレンダーなので２番目の引数は２になる。
A5に該当する日付を計算式で入力する
=A3-B3+1
たまたま今年は１月１日が月曜日だからわかりにくいかもしれないので、３月にすると月曜日は3/1の５日前に１日足すとちょうど月曜日の日付になる。という仕組み。
火曜日以降を作る
=A5+1祝日の行は空けて、次の週をつくる
=A5+7あとは７行目をコピーして、9.11.13.15行に貼り付けていけば完成。2024年は9月が6週目まで必要でした。
カレンダーの見栄えを整えていく
表示形式を日付だけにしよう
カレンダーとしては完成したので、ここからは見栄えを良くしていく。まずはyyyy/mm/ddになっている形式を日付だけのdのみにしよう。
条件付き書式を使って該当月じゃない文字色を薄くする
9月の前後になっている日付を薄くすることでカレンダーを見やすくします。
month関数
=month(A5)<>month($A$3)
VLOOKUP関数で祝日を表示させよう
いよいよVLOOKUP関数を使って祝日をセルA6に表示させていきます。わかりやすくするためにカレンダーは元旦が表示される1月にしておきましょう。
IFNA関数
=ifna(vlookup(A5,R6祝日,2,0),"")【検索キー】は日付を元に祝日名を探すのでA5（オートフィルするので絶対参照などは不要）【範囲】は最初に名前をつけた祝日の名前（R6祝日と入力すると候補が表示されます）
【指数】は祝日の表の何列目を答えとして必要かなので祝日名が入っている２列目で数値の２を入力、最後の【並べ替え済み】のところには完全一致検索という意味の0（またはFALSE）を入力します。
引数の切り替えは半角のカンマ（,）を入力しないといけませんので忘れないようにしましょう。
エラーの場合は空白が出るようにしたいので、空白という意味のダブルクォーテーション２つ""を入れてカッコを閉じましょう。
数式のみ貼り付けを使おう
Excelではここでオートフィルして書式なしコピーをすれば土日の塗りつぶしも消えないのですが、スプレッドシートにはそんな機能はないので、A6をコピーしてセルA7：G7を選択
「編集」▶「特殊貼り付け」▶数式のみ貼り付け
で関数のみを貼り付けましょう。
アレンジも可能
TODOリストやスケジュール表などに活用可能。私は自分とスタッフの秋日程6ヶ月分を取引先に送ったりするときにもカレンダーをバーっと作って◯つけて送ったりしています。
今回使った関数
DATE関数、WEEKDAY関数、VLOOKUP関数、IFNA関数、MONTH関数
でした！お疲れ様でした。