【Excel】関数で万年業務カレンダー（日付固定）を作ってみる〜第２回〜第nX曜日の日付計算・・・第３火曜って何日？？

前回に引き続き、下のような万年業務カレンダーをつくっていきます。

全５回の記事になっておりまして、構成は下のようになっています。内容が重なるものはサラッと書きますので、詳しく知りたい方は、過去回をご確認ください。

万年業務カレンダーの作成全５回

それでは、第３火曜日のような、第nX曜日は何日になるか？を計算していきます。
まず、最初に思い当たる面倒くさそうなところが、曜日は７種類あるってところですね。素直に関数で条件分岐すると、、、、もう、考えたくもないです（笑）
うーん、どうしましょうか。。。いっそ、カレンダーの利用者に曜日を計算できる数値で指定してもらうとか、、、、、
いやいや、、、ユーザビリティが大事とか言われている、この時代にそれはないですわな（笑）

なので！文字列の曜日を数値に変換して使いたいと思います！第１回で使ったTEXT関数の反対です。
とは言え、そんな曜日に特化した便利な関数はありません！！

どうするのかといいますと、文字列の中から、含まれている文字を探す、FIND関数を使います。
例えば、「水」を、月曜始まりのWEEKDAY関数の実行結果（以下、戻り値）と同じになるようにするには、

=FIND("水","月火水木金土日")

このようにすれば、WEEKDAYと同じ戻り値「３」になります。
関数の使い方、仕組みは、見たままですが、"月火水木金土日"という文字列の中に、「水」が左から何番目にあるか？を調べているんです。
VBAなどのプログラミングなんかでも、配列を使って似たようなことをしますが、関数に配列はないので代用ですね。
WEEKDAY関数の曜日始まりに合わせて文字列の並びを変えて使えば別のことにも使えますね。さらに、この方法は他でも応用が効くので、覚えておくと便利ですよ。

はい、これで曜日は数字に置き換えることができました。それでは日付をどう導き出すか考えていきましょう。

下の図は、水曜日始まりのカレンダーを使って、指定されるX曜日の位置関係と、ｎ週目のｎが増えるとどうなるかを整理したものです。

割とまとめるのに苦労しました。これで伝わるのでしょうか？？心配だ（笑）

月によって始まりの「ついたち」の曜日が変わるし、カレンダーの１段目（１週目）にX曜日があるかないか？によって第１週目の日付の数え方が変わりはしますが、実は考え方って結構単純で、この第１週目のX曜日の日付さえわかってしまえば、２週目以降は、ｎー１週×７だけのことです！１週目だけ考えればいいなんて簡単じゃないですかっ！！
さあ、１週目のX曜日をみつけましょう！

まず、上の図の１段目にX曜日があるかどうか？を判定しましょう。
判定にはWEEKDAY関数を使います。月初とX曜日のWEEKDAY関数の戻り値とカレンダーの１段目の関係を下の図で整理しました。

このように、指定されたX曜日の戻り値が月初の戻り値以上であれば、X曜日はカレンダーの１段目にあることになります。当然ですが月初の戻り値より小さければ、１段目にはないので、２段めの月曜日以降にあることになります。
これで判定ができましたので、早速日付を計算しましょう。

まずは、１段目に曜日があるパターンです。上の水曜日始まりのカレンダーでX曜日が金曜日として考え、式に表してみます。

ややこしそうですが、式としては単純ですね。X曜日と月初の差を出して、X曜日自身を足せばOKということです。

次は、同じように２段目に曜日があるパターンを考えます。次は火曜日でみてみましょう。

いきなり８がでてきましたね（笑）どこからでてきたのかといいますと、WEEKDAY関数の戻り値は、月曜始まりで１から７で構成されていて、上の図のカレンダーをみてわかるように、カレンダーの１段目の「位置」を表しています。この１段目のどこかから月が始まるわけですが、X曜日は含んでないというのが今回の条件ですね。
ということは、２段めにX曜日があるので、X曜日の日付を調べるには、１段目の今月の日付はすべてカウントする必要があります。そうすると、カレンダーの「位置」として２段めの月曜を考えると。。。「８番目」です。なので「８」から月初の戻り値を引けば、今月の１段目の日数がわかるわけです。
さらに、この２段目の月曜から、X曜日の戻り値分日付を加算させれば、求めたい日付となるわけです。

さあ、これで日付の計算はできました。あとは実際の条件分岐を入れ込みながら、作成していきましょう、、、、といきたいところですが、、、、もう少し整理します。

上で説明した１段目に曜日があるパターンと２段目に曜日があるパターンの式ですが、よく見ると似てますよね。
そこで、次のように1段目の式を２段目の式に寄せていくように式の順番を入れ替えてみると、、

はい、実は１か８だけが違うだけで、同じ式でした！こうすれば条件分岐は短くできますね〜
それでは実際に下の箇所に式をいれて作成していきます。

式ですが、ちょっと長くなるので改行をいれて書いていきます。（Excel上でも改行OKです。）

=IF(M9="","",
  　IF(WEEKDAY(DATE($C$1,$E$1,1),2)>FIND(N9,"月火水木金土日"),8,1)
  　　+FIND(N9,"月火水木金土日")-WEEKDAY(DATE($C$1,$E$1,1),2)+(M9-1)*7
 )

短くなったとは言え、長いですね（笑）。
この条件判定の式と計算式に同じような関数があります。これは、

WEEKDAY(DATE($C$1,$E$1,1),2)
↑月初の戻り値のこと

FIND(N9,"月火水木金土日")
↑X曜日の戻り値のこと

を意味しています。
「セルM9に何も入力がなければ空白を表示する」という条件が大外についてますが、式の判定や計算は、これまで説明してきたとおりです。
あとは、入力したセルから下方向に必要な分だけコピーすればOKです。

さて、いろいろと簡潔に式を書くためにやってきましたが、、、やったがために、あとで見返して分からなくなるでは困っちゃいます。説明書を書くつもりで、ほどほどの整理ぐらいの式の形にしておくのも手です。維持管理するのは自分だけじゃないですから（笑）まあ、このへんは好き好きです。ご都合の良い方法を選んでください！

さあ、これで曜日設定の表に第何週の何曜日かを入れると日付が表示されるようになりました。
カレンダーに予定を表示させるのは第５回までおあずけです。日付設定と予定が被る可能性があるので、そうなったら２つとも表示させたいですからね。そんな表示方法をそのときにご説明します。

それでは今回はここまでです。読んで頂きありがとうございました。