ゴミの収集等で第３土曜日っていつ？と日付を知りたい時ありますよね。
そんな時に役立つExcelテクニックです。
以下のような感じです。
内部の仕組みはこのようになっています。
難易度：
式はシンプルですが、混乱しそうになります。
使用する関数
第○□曜日を求めるのに使用する関数はWEEKDAY関数とIF関数です。
応用編になってくるとこれに＋MONTH関数を使っていきます。
【WEEKDAY関数】
WEEKDAY関数は、曜日を数字で返す関数になります。
例：日＝１、月＝２、火＝３、水＝４、木＝５、金＝６、土＝７
※上記値は初期設定時です。
【IF関数】
IF関数は条件分岐で、この条件分岐がとても重要な役割を果たします。
式の解説の所で詳しく説明いたします。
使用する式
全部で35パターンの式が必要になりますが、まず基本の第１日曜日を計算する式は、以下の式になります。
=IF(WEEKDAY($A$1)<=1,1-WEEKDAY($A$1)+$A$1,(7+1-WEEKDAY($A$1)+$A$1))
※$A$1には日付が入っています。
35パターンもの式が必要な理由は、週が第１～第５まである事。
そして曜日は日曜日〜土曜日までの７パターンあるため、合計３５パターンの式が必要になります。
多いと思うかもしれませんが、それぞれの式はちょっとした部分が違うだけです。
それでは第の部分と曜日の部分の変更によって変わる式について解説いたします。
今回使用しているファイルは下のボタンよりダウンロードできます。
第○の週を求める式
第１の式を基準に第２～第５にかけて変更する部分は最後の箇所だけです。
１週間は７日あるので、それぞれ最後に加算する数に７の倍数を入れます。
第２週：
=IF(WEEKDAY($A$1)<=1,1-WEEKDAY($A$1)+$A$1,(7+1-WEEKDAY($A$1)+$A$1))＋7
第３週：
=IF(WEEKDAY($A$1)<=1,1-WEEKDAY($A$1)+$A$1,(7+1-WEEKDAY($A$1)+$A$1))＋14
第４週：
=IF(WEEKDAY($A$1)<=1,1-WEEKDAY($A$1)+$A$1,(7+1-WEEKDAY($A$1)+$A$1))＋21
第５週：
=IF(WEEKDAY($A$1)<=1,1-WEEKDAY($A$1)+$A$1,(7+1-WEEKDAY($A$1)+$A$1))＋28
曜日を求める式
おさらいになりますが、以下は日曜日の式になります。
=IF(WEEKDAY($A$1)<=1,1-WEEKDAY($A$1)+$A$1,(7+1-WEEKDAY($A$1)+$A$1))
月〜土曜日にかけて、以下の３か所が変わります。
=IF(WEEKDAY($A$1)<=1,1-WEEKDAY($A$1)+$A$1,(7+1-WEEKDAY($A$1)+$A$1))
月：=IF(WEEKDAY($A$1)<=2,2-WEEKDAY($A$1)+$A$1,(7+2-WEEKDAY($A$1)+$A$1))
火：=IF(WEEKDAY($A$1)<=3,3-WEEKDAY($A$1)+$A$1,(7+3-WEEKDAY($A$1)+$A$1))
水：=IF(WEEKDAY($A$1)<=4,4-WEEKDAY($A$1)+$A$1,(7+4-WEEKDAY($A$1)+$A$1))
木：=IF(WEEKDAY($A$1)<=5,5-WEEKDAY($A$1)+$A$1,(7+5-WEEKDAY($A$1)+$A$1))
金：=IF(WEEKDAY($A$1)<=6,6-WEEKDAY($A$1)+$A$1,(7+6-WEEKDAY($A$1)+$A$1))
土：=IF(WEEKDAY($A$1)<=7,7-WEEKDAY($A$1)+$A$1,(7+7-WEEKDAY($A$1)+$A$1))
上記の式がそれぞれの曜日を求める式になります。
これらの式に週を求める式を組み合わせてあげて使用します。
例えば、第２月曜日を求める式は以下になります。
=IF(WEEKDAY($A$1)<=2,2-WEEKDAY($A$1)+$A$1,(7+2-WEEKDAY($A$1)+$A$1))+7
次はなぜIF文を使用しているのか？の解説です。
式の解説
今回の式はIF文が入っているため複雑のような感じに見えますが、重要なことは２つです。
１日が何曜日なのか
曜日を起点に求めたい日付の曜日が前にあるのか後ろにあるのか
ここからはカレンダーを使って、実際に見ていきます。
2023年11月がわかりやすいので、こちらで解説いたします。
それでは、2023年11月をベースに第１土曜日と第１月曜日を求めてみます
まず2023年の11月1日が何曜日に当たるのかという事と求めたい曜日との位置関係を確認していきます。
2023年11月１日は水曜日になります。
続いて求めたい曜日と11月1日（水）との位置関係は土曜日は3日後。
月曜日は２日前となります。
これを先ほどの曜日を求める式にあてはめると
土曜日：7-WEEKDAY($A$1)+$A$1
月曜日：2-WEEKDAY($A$1)+$A$1
となります。
これに水曜日のWEEKDAY関数で求めた値は「4」となりますので式に当てはめると
土曜日：7-WEEKDAY($A$1)+$A$1 ＝7-4＋2023/11/1
＝2023/11/4 （３日後）
月曜日：2-WEEKDAY($A$1)+$A$1 ＝2-4＋2023/11/1
＝2023/10/30 （2日前）
しかし、月曜日を見ると先月の月曜日を求めてしまっています。
第１月曜日にするためには１週間後の7日（１日の水曜日から見ると５日後）を足してあげる必要があります。
７日足してあげる式がこちらです。
7+2-WEEKDAY($A$1)+$A$1 ＝7+2-4＋2023/11/1
2023/11/6（５日後）
となります。
これがIF文が必要となる大きな理由です。
１日が何曜日から始まっているのかそれによって、１週間後の７日を足す必要が出てきます。
それではIF文の条件分岐使って１日の曜日以前のもの（この場合は日、月、火の３つ）には７を加算するようにします。
その式がこちらです。
=IF(WEEKDAY($A$1)<=2,2-WEEKDAY($A$1)+$A$1,(7+2-WEEKDAY($A$1)+$A$1))
式は長く複雑なような感じがしますが、実際は７を足しただけの式です。
これが、IF文を使う理由と、○月第△□曜日の求める式になります。
応用編（第５で次の月となった場合は非表示）
こちらはお好みで、１か月は最低28日、最高31日です。
曜日の数は７あるので、翌月が表示されてしまうところがあります。
こちらはその翌月分は非表示にする方法になります。
=IF(MONTH(IF(WEEKDAY($A$1)<=2,2-WEEKDAY($A$1)+$A$1,(7+2-WEEKDAY($A$1)+$A$1))+28)=MONTH(A1)+1,“”,IF(WEEKDAY($A$1)<=2,2-WEEKDAY($A$1)+$A$1,(7+2-WEEKDAY($A$1)+$A$1))+28)
長い式ではありますが、注目すべき所は上記下線部だけです。
基準日の月だけを抜き出し、それが＋１した月であれば非表示にしています。
まとめ
いかがでしたでしょうか。
ゴミ出しの曜日で第３土曜日っていつだよ！って計算するのたいへんですが、Excelで簡単に算出できますので、ぜひ使ってみてください。
コメント