Excel上では、会社の会議などで『毎週火曜日』とかならWEEKDAY関数だけでと検索関数(LOOKUP関数やINDEX+MATCH関数など)で楽なんですが、『毎月第2、第4何曜日』とかになると個別の日付になり指定するのが複雑になります。今回はその対処法についてお話ししていきます。
月初の日付の曜日から確定させる
○週目は○×7にしつつ、月初日の曜日に応じて変更
祝日などと同じ仕組みでカレンダーに自動記入にする？
基本的なカレンダーの作り方、祝日を自動的に記入していく高性能カレンダーの作り方に関してはこちらをご覧ください。
今回使用するファイルも貼っておきます。
月初の日付の曜日を確定させる
いきなり結論ですが、
- 月初日の日付と曜日の曜日データ(WEEKDAY関数の戻り値)を取得する(月初日が第1○曜日になる)
- 目的の『第○何曜日』でWEEKDAY関数戻り値が月初曜日のWEEKDAY関数の戻り値以上なのか未満なのかで○×7か((○-1)×7)となる
- (○週×7日)-(月初曜日のWEEKDAY関数の戻り値-指定曜日のWEEKDAY関数の戻り値)
これをやるだけです。言葉で書くと難しそうなので実際に見てみましょう。
今回は2018年8月を例にとって考えてみます。なお、今回は最終的な数式が長くなるのでこまめに数式結果をセルに落とし込んでいきます。
E4セルに＝WEEKDAY(DATE(A1,C1,1),1)で2018年8月1日の曜日を算出します。
今回の種類ですが1です。よって以下のようになります。
今回は日～土を1～7で返す表を作っておきます。
先程の曜日と1～7の対応表も脇に作っておきました。
○週目は○×7にしつつ、月初日の曜日に応じて変更
まず『第3水曜日』について考えていきます。
重要なの何回目の●曜日になるのかという事です。
月初曜日が水曜なので既に1回来ています。なので((3週目-1)×7日)で14日後の8/15日が『第3水曜日』になります。ここまではそんなに難しくないですね。
『第2火曜日』だとどうでしょう？
いくつか考え方があります。
①8/1(水)から該当曜日のWEEKDAY関数戻り値(今回は火曜なので3)日分を引き算して、そこに○週目×7日の日数を足す。
②8/1(水)から○週目×7日をした後にWEEKDAY関数の戻り値を足し引きする。
言葉で書くと②のほうが簡単です。しかし実際に数式にするとこうなります。
①の方法
=(DATE($A$1,$C$1,1)-($E$1-HLOOKUP($C3,$G$1:$M$2,2,FALSE)))+IF(($E$1-HLOOKUP($C3,$G$1:$M$2,2,FALSE)>0),($B3*7),($B3-1)*7)
②の方法
=DATE($A$1,$C$1,1)+IF(WEEKDAY(DATE($A$1,$C$1,1),1)<=HLOOKUP($C3,$G$1:$M$2,2,FALSE),(($B3-1)*7-($E$1-HLOOKUP($C3,$G$1:$M$2,2,FALSE))),$B3*7-($E$1-HLOOKUP($C3,$G$1:$M$2,2,FALSE)))
どっちも長いですが、①のほうが楽そうなので今回は①をメインに『第2火曜日』の算出方法を考えていきます。実際のセル番地と比較しながら見ていきましょう。今回はE3セルに数式を入れていきます。
=(DATE($A$1,$C$1,1)-($E$1-HLOOKUP($C3,$G$1:$M$2,2,FALSE)))+IF(($E$1-HLOOKUP($C3,$G$1:$M$2,2,FALSE)>0),($B3*7),($B3-1)*7)
=2018/8/1から((水曜=4)-(『第2火曜日』の火曜日=3)をして、もし(水曜＝4)-(火曜3)が0より大きいなら○週目×7日、そうでなければ(○-1週目)×7日を足します
こういう意味になります。
祝日などと同じ仕組みでカレンダーに自動記入にする？
『第○何曜日』までは出すことが可能になりました。あとは、『第○何曜日』の横に『全体会議』『不燃ごみの日』などを追加してカレンダーに該当する日に表示させてしまえばOKです。
OK何ですが、自動表示の弱点でもあるのは「空白でも数式が入っているのでスペースを確保しておく必要がある」という事です。以下のカレンダーで見ていきます。
卓上タイプ
メリット：
全体や先の日付まで見やすい
デメリット：
記入項目欄が少ない
縦型タイプ
メリット：
項目欄や行(列)を追加しやすい
デメリット：
1画面に収めるのが困難
前後の月など見れる範囲が限られやすい
どちらが優れているというわけではありません。双方メリット/デメリットがあります。しかし、デメリットもExcelの機能である程度はカバーが出来ます。
条件付き書式設定です。
条件付き書式設定については以下をご確認ください。また、高性能カレンダーを作る際にもお話ししている「名前の定義」も必要になります。
要は、『第○何曜日』の欄の隣に追加した『全体会議』などの日付に『会議』とでも名前の定義で設定して、条件付き書式設定でイベントがある日には記入欄に色掛けしてあげれば無駄に空欄スペースを確保しておかなくとも視覚的にわかりやすくなります。
実際にやってみましょう。
各日付の1行目は会社関係(会議など)、最終行は家事関係(不燃ごみの日など)と振り分けて設定してあげればより使い勝手が増す型にもアレンジできます。やってみましょう。
