こんにちは。
カレンダーにちょっとしたメモを残したいときはないですか？
しかもそれをExcelを使って簡単に編集できるような形で。
で、それをしようとしても日付を手打ちしてたのではめんどくさい。
年月を指定すれば勝手に日付が入るようにしたいのが人情というものです。
わりと一般的な関数を使ってカレンダーを作ってみたというのが今回のお話です。
今回やりたいこと
この図のように、年月を指定すればカレンダーが更新される、こんなExcelファイルを作ります。
そして今回のポイントがこちら。
- カレンダー上の表示位置の特定
- 「表示位置の特定」とは、日付に応じてカレンダー上の座標を指定すること
これを踏まえて、さっそく作ってみましょう。
事前準備
まずは３つ、フォーマットを用意します。
年月入力欄、年月日から座標を指定するテーブル、カレンダーです。
年月の入力欄
まず、年月欄はこんな感じでOKです。
ここでは黄色のセルが入力欄になっています。
そして最後の月末日。ここは関数で年月に応じて表示するようにしておきます。
この月末日は後で使います。
・DAY関数：指定した年月日から日付のみを返す
例）DAY(2020/9/1）→1
・DATE関数：年・月・日から日付を返す
例）DATE(2020,9,1)→2020/9/1
・各月の月末日のだしかた
DAY（翌月1日の前日）
=DAY(翌月1日-1)
＝DAY(DATE(年,月+1,1(固定)-1）
※ちなみにこれだと2020年12月を指定した場合本当は2021年1月1日の前日を返さないといけませんが、このままだと2020/1/1の前日を指定してしまいます。
ですがいずれにしても12月の月末は31なので問題は無いとしておきましょう。
年月日から座標を指定するためのテーブル
まずはこんな感じ作っておきます。
年と月は先の入力欄から飛ばし、日付欄は1から31を入力します。
カレンダーのフォーマットの作成
最後にカレンダーのフォーマットを作成します。
例えばこんな形に作ってみましょう。
年月欄は、上の図のように入力欄から数値が飛んでくるようにしておくと入力の手間が省けます。
そして、行番号・列番号の記載もお忘れなく。
この番号に基づいて、日付をカレンダーに表示させる関数を組みますので。
ここまでで準備は完了です。
次に座標を指定する方法とカレンダーへの飛ばし方を見ていきます。
座標の指定
次に座標の指定方法を見ていきましょう。
座標、要は曜日と何週目、これです。
曜日の指定方法
まずは曜日から。
使う関数はWEEKDAY関数です。
WEEKDAY関数は、指定した日付から曜日(に応じた数値)を返します。
WEEKDAY関数には、曜日コードを指定する方法がいくつかありまして、開始曜日（月～日）と開始値（０か１）を選択することができまして、今回は日曜・１から開始するコード「１」を指定しています。
２行目WEEKDAYの２つ目の引数「１」がそれです。
これが、カレンダーの列に対応しています。
さらにIF関数でくくっているのは、例えば２０２０年９月３１日は存在しませんが、このままWEEKDAY関数に入れると曜日コードを返してしまうため、「日付が月末を超える場合は０を返す」という条件分岐にしています。
まとめるとこうです。
条件：日付が月末日以下（IF）
○：曜日コードを返す（WEEKDAY)
×：０を返す
これでこのようになります。
２０２０年９月３１日は曜日が０になり、これでカレンダーに反映させる対象から外しています。
○週目（行）の指定方法
次に日付が何週目かの指定方法を見てみましょう。
まず、１週目から２週目に、２週目から３週目に移るルールを確認しておきましょう。
といっても単純です。
- １日は１週目からスタート
- 曜日コードは１～７の繰り返し
- 曜日コード７の翌日が翌週に移る
というわけで、組むべき関数はこんな感じになります。
まとめておきます。
- １行目（1日）は「１」
- 2行目以降
条件：前行の曜日コードが７
○：前行の週数＋１
×：前行の週数
以上です。ここまでで座標の指定ができました。
カレンダーの作成
ここまでくればあとは簡単です。
使う項目は複数条件での集計に使うSUMIFS関数です。
SUMIFS関数の中身ですが、これは合計範囲・条件範囲①と条件①、条件範囲②と条件②の３つのブロックに分けることができます。
- 合計範囲：「日」列
- 条件範囲①：範囲「曜日列」、条件「列（曜日の値）」
- 条件範囲②：範囲「週列」、条件「行（週数）」
ざっとこんな感じです。
ところでこのままだとゼロ表示されたままです。
これを空欄表示に変えてみましょう。
IF関数でSUMIFSの結果がゼロの時は空欄に、ゼロでないときはSUMIFSの結果を表示させればOKです。
つまりこうなります。
まとめておきます。
条件：SUMIFSの結果がゼロと等しい
○：空欄表示「””」
×：SUMIFSの計算結果を表示
IFの中が長文ですが、内容としてはこれだけのことです。
以上でカレンダーが完成しました。
あとは行を挿入して記入欄を作るなり使いやすいようにカスタマイズしていくだけです。
その時に座標の指定がずれないようにだけ気を付けてください。
最後に
いかがだったでしょうか。
Excelでカレンダーを作る方法についてお届けしました。
最初は難しいかなと思いましたが、やってみれば案外簡単にできるものですね。
今後も、一見難しそうだけど案外できちゃった、みたいなことも書いていけたらと思います。
最後まで読んでいただき、ありがとうございました。
