【基本】スプレッドシートで作る祝日入り万年カレンダー

Excel2002よりの使者。うにですこんにちは。はるか昔に先輩インストラクターが作った万年カレンダーを見て感動を覚えてから幾星霜、まさか20年近くこの仕事をすることになろうとは…ということで悠久の時を刻むカレンダーの作り方をご紹介します。

インターネットを検索すると山のように出てくるやり方ですが、毎回最初の関数忘れちゃうので自分用備忘録

今回はこういうのを作ろうと思います

月と祝日マスタを作ろう

前回のポストで「開発にはマスタ必須」を覚えたので最初にマスタ用シートを作っていきます。

内閣府ホームページから祝日一覧をコピペしよう

今回のカレンダーには祝日を反映させたいので、あらかじめ内閣府のホームページから祝日一覧をコピペしておきましょう。

令和6年の祝日一覧、休日は振替休日のこと。C列は使わないので削除してもよし。

このままだとVLOOKUP関数で日付から祝日名が検索できないので、A列とB列を入れ替えます。

こうしておく
スプレッドシートは列番号をドラッグすれば列の入れ替えができるので超便利

あとはプルダウンメニューで月が選べるように1～12までのリストも同じシートに作っておきます。

マスタ完成

範囲に名前をつけておこう

範囲選択でいちいちマスタシートまで戻ってドラッグしたくないので、あらかじめ使う範囲には名前をつけておこう。

画像ではセルA3：B23までを選択してデータメニューから「名前付き範囲」を選択

自分がわかる範囲の名前をつけて完了

同様に1～12まで入力した範囲に名前をつけて完了

カレンダー用シートを作ろう

準備ができたところでカレンダー用のシートをつくります。

A1に年の数値、B1に文字列「年」、A2にプルダウン形式で月を入れたいので「データ」から「データの入力規則」を選択

画面右側の「＋ルールを追加」をクリック

条件のプルダウンをクリックして「プルダウン（範囲内）」を選択

範囲に先ほど名前をつけた「月」と入力してEnterを押すと自動的に数値を拾ってきます。色はお好みで選んで「完了」をクリック。

できた。▼をクリックすると数値がでるか確認してね。

曜日を入力しよう

今回は月曜始まりのカレンダーにしたいのでA4に月と入力してG4までオートフィル

そしてこれがないと万年カレンダーにならないとっても重要な関数をA3とB3にいれていきます。

date関数

1つめdate関数（デイト関数）バラバラにある数値をつなげて日付にしてくれちゃうやつ。

=date($A$1,$A$2,1)

絶対参照はかけなくてもいいけど、念の為。3つ目の日は１にすることで何月であってもその月の初日が出るようになります。

2つめweekday関数、日付から曜日を出してさらにその曜日を数値に変換してくれる。（１なら月曜日とかね）これがカレンダーづくりには重要

weekday関数

=weekday($A$3,2)

２番目の引数は何曜日始まりにするかを決められる。１なら日曜はじまり、２は月曜始まり。今回は月曜始まりのカレンダーなので２番目の引数は２になる。

A5に該当する日付を計算式で入力する

=A3-B3+1

今の日付-初日の曜日+1（そのままだと１日足りないので）よくわかんないときはここは理解しようとしないでとりあえず計算式だけ入れて。

たまたま今年は１月１日が月曜日だからわかりにくいかもしれないので、３月にすると月曜日は3/1の５日前に１日足すとちょうど月曜日の日付になる。という仕組み。

私はよくわかっていない。

火曜日以降を作る

横のセルに+1してから日曜日までオートフィルしよう

=A5+1

祝日の行は空けて、次の週をつくる

上の日付に+7してオートフィル

=A5+7

あとは７行目をコピーして、9.11.13.15行に貼り付けていけば完成。2024年は9月が6週目まで必要でした。

日曜始まりだと5週でおさまる

カレンダーの見栄えを整えていく

表示形式を日付だけにしよう

カレンダーとしては完成したので、ここからは見栄えを良くしていく。まずはyyyy/mm/ddになっている形式を日付だけのdのみにしよう。

セルA5：G15を選択して「表示形式」▶「数字」▶「カスタム数値形式」を選択

日はdayの頭文字であるdをいれて適用ボタンをクリック

見やすいフォントサイズに調整してあげれば一気にカレンダーぽくなる

条件付き書式を使って該当月じゃない文字色を薄くする

9月の前後になっている日付を薄くすることでカレンダーを見やすくします。

「表示形式」▶「条件付き書式」を選択

範囲に適用のところをクリックして、A5:G5と「別の範囲を追加」をクリックしてA13:G13とA15:G15それぞれを選んでおくと祝日に条件付き書式が影響しないのでおすすめ

month関数

書式ルールの条件を「カスタム数式」にしてA5の月がA3の月ではない場合という条件を設定します。A3は絶対参照になっていないと正しく反映されないので注意。

=month(A5)<>month($A$3)

あとは好きな書式設定をつけて完了

カレンダーぽくなってきたぞ

A3とB3の文字を白にして見えないように、罫線とセルの塗りつぶしでカレンダーぽくしました

VLOOKUP関数で祝日を表示させよう

いよいよVLOOKUP関数を使って祝日をセルA6に表示させていきます。わかりやすくするためにカレンダーは元旦が表示される1月にしておきましょう。

IFNA関数

祝日じゃないときにエラーが出てしまうので、IFNA関数を使ってもし該当しないなら空白と表示されるように準備します。

IFNAの中にVLOOKUP関数を入れます。スプレッドシートは途中まで文字を入力して候補が出たらTabキーで関数が入ってしまうので便利。

=ifna(vlookup(A5,R6祝日,2,0),"")

【検索キー】は日付を元に祝日名を探すのでA5（オートフィルするので絶対参照などは不要）【範囲】は最初に名前をつけた祝日の名前（R6祝日と入力すると候補が表示されます）

つけた名前の１文字でも入力すると候補が表示されます

【指数】は祝日の表の何列目を答えとして必要かなので祝日名が入っている２列目で数値の２を入力、最後の【並べ替え済み】のところには完全一致検索という意味の0（またはFALSE）を入力します。

引数の切り替えは半角のカンマ（,）を入力しないといけませんので忘れないようにしましょう。

VLOOKUPができたらカッコ閉じを入力してカンマをいれるとIFNAに進みます。

エラーの場合は空白が出るようにしたいので、空白という意味のダブルクォーテーション２つ""を入れてカッコを閉じましょう。

元旦が表示されれば成功！

数式のみ貼り付けを使おう

Excelではここでオートフィルして書式なしコピーをすれば土日の塗りつぶしも消えないのですが、スプレッドシートにはそんな機能はないので、A6をコピーしてセルA7：G7を選択
「編集」▶「特殊貼り付け」▶数式のみ貼り付け
で関数のみを貼り付けましょう。

こういうときExcelの書式なしコピーは便利だなぁって実感

他の月にして祝日が反映されれば完成！

アレンジも可能

一番面倒なのが今回の形なので、縦型とかにしてしまえばもっと簡単

TODOリストやスケジュール表などに活用可能。私は自分とスタッフの秋日程6ヶ月分を取引先に送ったりするときにもカレンダーをバーっと作って◯つけて送ったりしています。

今回使った関数

DATE関数、WEEKDAY関数、VLOOKUP関数、IFNA関数、MONTH関数
でした！お疲れ様でした。