Excelシート上で使える、複数の関数を組み合わせた特殊な使い方を説明。
今回は DATE、WEEKDAY を組み合わせることで、
カレンダーを自作する方法を紹介
【本記事の目標】
DATE、WEEKDAYを使って
カレンダーを自作してみよう
今回組み合わせる関数は下記でも説明していますので、ご参考ください。
今回作るカレンダーのイメージ
今回作るのは下記のような一般的なカレンダーです。
ただし、年と月を入力するだけで、自動的に日付や祝日を入力し、祝日は自動的に赤く塗りつぶすように設定していきます。
カレンダーの作成
基本的な書式の設定
まずは基本的な書式の設定をしていきます。
今回は上記の図をベースに説明していきますので、上記の書式の設定を細かく記載しておきます。
ただこれに関しては各々の好みもありますので、自由に設定していただいて大丈夫です。
関数の入力
このカレンダーの中で、定数を入力してある場所は下記の緑枠部分だけです。
それ以外の部分（青枠）には全て関数が入力されています。
また下記の緑枠部分は定数のため基本的に固定ですが、
C2セル（『年』を入力する部分）とE2セル（『月』を入力する部分）
については自由に入力が可能で、
ここを変更することで好きな年・月のカレンダーを生成することができます。
青枠の部分に関数が入力されていますが、ここに入力されている関数は3種類のみです。
それぞれを詳細に説明していきます。
① 一番最初の日付の入力
このカレンダー作りで一番難しいのが、この『一番最初の日付の入力』の部分（B5セル）です。
ここが決まってしまえば、他の日付は1日ずつ足していけばいいので非常に簡単です。
では『一番最初の日付』はどうやって計算するのか。
そもそも『一番最初の日付』というのは、
当月一日の含まれる週の日曜日の日付
になります。
ですので、例えば2023年7月のカレンダーを作りたい場合、
7月1日が一番最初の日付ではなく、7月1日の週の日曜日、6月25日が最初の日付になります。
ではこの日付をどうやって計算するか。
ここで使うのがWEEKDAY関数とDATE関数です。
計算手順としては下記の通りです。
１．7月1日の曜日をWEEKDAY関数を使って１～７の数字で表す。
２．日曜日が０、土曜日が６になるようにするため、
WEEKDAY関数のデフォルト設定で、出た値ー１をする
３．7月1日からWEEKDAY関数ー１の値を引く
こうすることで、最初の日付を計算することができるのです。
最終的な関数式は下記の通りです。
=DATE(C2,E2,1)-(WEEKDAY(DATE(C2,E2,1))-1)
この式をB5セルに入力すれば、最初の日付を計算することができるのです。
② ほかの日付の入力
一番最初の日付が決まってしまえば、以降はその日付から１日ずつ足していけばいいので簡単です。
具体的には青色にハンチングされている部分に、
１つ前の日＋１
という関数を入力します。
例えば、
C5セル：=B5+1
B7セル：=H5+1
になります。
③ 祝日の入力
祝日自体は毎年国から通達があって初めて決定するため、Excelの中には決まった関数がありません。
そのため外部から取ってくる必要があります。
一番簡単なのは、内閣府のホームページからその年の祝祭日一覧を持ってくる方法です。
『2023 祝祭日 内閣』
と検索すれば一番最初に出てくると思います。
そのリストを別シートに貼り付けておきます。
あとは、
各日付がこのリストの日付と合致するか確認し、
合致するようであれば、その時の祝祭日の名前を表示する
という関数を黄色にハンチングしてある日付の下の欄に入力しておきます。
ここで使うのはIFERROR関数と、OFFSET関数・MATCH関数の組み合わせ関数です。
これらの具体的な使い方は下記でも記載していますので、ご参考ください。
具体的な関数式は下記になります。
=IFERROR(OFFSET(祝日!$A$1,
MATCH(B5,祝日!$B$2:$B$18,0),0),””)
この関数をB6セルに入力し、黄色にハンチングしている他のセルにコピーすればOKです。
条件付き書式の設定
上記までの関数を入力すれば、表示されている数値としてはカレンダーの形になっていると思います。
あとはこれをデコレーションしていき、より分かりやすいカレンダーに変えていきます。
そこで使うのが『条件付き書式』です。
『条件付き書式』はホームタブの中にあります。
条件付き書式を設定したいセルを選択した状態で、ここをクリックすれば、条件付き書式を設定することができます。
ただこの『条件付き書式』は非常に奥が深く、全てを説明すると長くなりすぎるので、
今回は比較的難易度が高く、直感的にわかりにくい『数式を使用して、書式設定するセルを決定』の部分を説明していきます。
１．当月以外の日付をグレーに
まずは、どこからどこまでが当月なのかをわかりやすくするため、前月や来月の日付の部分をグレーにする条件付き書式を設定していきます。
各日付の部分には日付しか記載されていませんが、実際にはデータとして年/月/日のデータが含まれています。
そのため、MONTH関数を使えば、その日付の月を確認することができます。
あとはその月が、E2セルに記載の月と同じかどうか確認し、違うのであればグレーなる書式設定をすればいいのです。
じゃあ、それを『条件付き書式を設定』の数式使用でどう表現するかですが、
同じかどうかを確認するので、“=”もしくは”<>”で結べばOKです。
同じかどうかを確認する条件式を作る場合、
同じ場合を『正』異なる場合を『誤』の場合は ”＝” を使いますが、
同じ場合を『誤』異なる場合を『正』にしたい場合は ”<>” を使います。
今回の場合は異なる場合に条件付き書式を設定したいので、 ”<>” で結びます。
それだけ？と思われるかもしれませんが、それだけです。
例えばB5セルの場合、
=MONTH(B5)<>$E$2
を入力するだけでOKです。
数式として入力する必要があるため、最初に”=”が必要ですが、
以降は単純に条件式を書くだけで、その条件に合致したセルだけ書式を設定することができます。
今回の場合の具体的な手順を記載しておきます。
１．日付が記載されているセル（5、７、９、１１、１３、１５行目）を選択する。
２．条件付き書式の新規ルール⇒「数式を使用して書式設定するを決定」を選択
３．「次の数式を満たす場合に値を書式設定」の欄に
＝MONTH(B5)<>$E$2
を入力。
４．「書式」ボタンをクリックし、任意の書式を設定する
（今回は文字色をグレーに設定する）
２．祝日がある日は赤く塗りつぶす
祝祭日の場合は、６，８，１０，１２，１４，１６行目に祝祭日の名前が記載されるように関数を記入していますので、ここに文字列が表示されているか否かで条件付き書式を設定すれば、祝祭日の日を赤く塗りつぶすことができます。
今回はさらに簡単で、空欄かどうかを確認するだけですので、B5セルだと
＝B6<>””
と記載しておけばいいのです。
手順としては下記の通りです。
１．日付が記載されているセル（5、７、９、１１、１３、１５行目）を選択する。
２．条件付き書式の新規ルール⇒「数式を使用して書式設定するを決定」を選択
３．「次の数式を満たす場合に値を書式設定」の欄に
＝B6<>””
を入力。
４．「書式」ボタンをクリックし、任意の書式を設定する
（今回は薄い赤の塗りつぶしに設定する）
これで日付入ったセルについて、祝祭日の時に赤で塗りつぶす条件付き書式を設定が完了です。
ただ、祝祭日の名前が入っていく行（６，８，１０，１２，１４，１６行目）にも同じような条件付き書式を設定をする必要があります。
同じように６，８，１０，１２，１４，１６行目を選択して上記と同じ関数を記入すれば、同じように書式設定をすることができますが、
ここに関しては数式を使用しなくても、『セルの値が空欄かどうか』というルールがExcelのデフォルトで作られています。
ですので、そちらを設定してもらっても大丈夫です。
設定の方法は下記画面を参考にしてください。
完成！
これで、任意の月のカレンダーを自動で生成してくれるExcelシートが完成しました！
あとはこれを自分好みにカスタマイズしていただければ、カレンダーを購入する必要性が減ってくるかと思います。
例えば子どもとのお約束カレンダーといったものも自作することができます。
自作カレンダー作りの参考にしていただけたらと思います。
コメント