みなさん、こんにちは！
フジタニ(@libartweb)です。
エクセルVBAとWORKDAY関数を組み合わせたスケジュール表自動作成の方法をシリーズでお伝えしております。
前回の記事はこちら
前回は「スケジュール自動作成ツール」を作るためには、どのシートに何のエクセル関数の入力が必要であるか？
そしてツールが動作する全体の仕組みと流れについて解説しました。
最初に必要なのが定例タスクの日付計算です。何日に何のタスクがあるのか？それをまずはエクセル関数で計算する必要があります。
今回は定例タスクのスケジュールを自動計算する際に便利なエクセル関数を紹介します。
月間スケジュールリストを作成する際に便利なエクセル関数
定例タスクの日付計算には様々なパターンがありますが、よく使うものをピックアップしました。
これだけでは求められないパターンもあるかもしれませんが、大体はこれでOKなはずです。
紹介するパターンは以下の通りです。
| 定例タスク | タイミング |
|---|---|
| 英会話教室 | 毎週月曜日(休み→翌営業日) |
| 月例会議 | 第2月曜日(休み→翌営業日) |
| 月末処理 | 毎月最終営業日 |
| カード引き落とし | 毎月27日(休み→翌営業日) |
F2セルとF5セルには前回の記事で説明した「基準日」と「基準日の翌月」が入力されている前提とします。
おさらいすると･･･以下の2つの関数が設定されています。
F2セル=基準日(TODAY関数)
F5セル=基準日の翌月(DATE関数)
月の最終日の求め方
月の最終日を求めるのは非常に簡単です。
月末はEOMONTH関数でシンプルかつ簡単に求められます！
「開始日」から起算して、指定した月数だけ前または後の月の最終日に対応するシリアル値を返します。
書式は以下の通り。
「開始日」に最終日を求めたい日付を入力します。
「月指定」は「開始日」から見たどの月の最終日を求めるかを指定します。
月指定は以下の通り。
| 月指定 | 内容 |
|---|---|
| 0 | 当月末 |
| 1 | 来月末 |
| 2 | 再来月末 |
| -1 | 前月末 |
| -2 | 前々月末 |
2018年11月の最終日を求めたい場合はF5セルに「2018年11月1日」と入力しておき以下のように関数を使用します。
=EOMONTH($F$5,0)
これで2018/11/30が返ります。
毎週○曜日の求め方
英会話教室(毎週月曜日)を例に説明します。
まず、第1月曜日を求めます。式は以下の通り。（F2セルに今日の日付をTODAY関数で入力しておきます。今回は2018年10月9日の日付がF2セルに入っています）
※実際に毎週月曜日を求める月の対象は、今日の日付の翌月です。
=EOMONTH($F$2,0)+0-WEEKDAY(EOMONTH($F$2,0),3)+7
この式は、WEEKDAY関数の返り値を使いながら、曜日を考慮して日付計算を行っています。
WEEKDAY関数の書式等は以下の記事の冒頭で紹介しております。
EOMONTH($F$2,0)+0からWEEKDAY($F$2,3)+7を引いております。1つずつこの式を解説していきます。
=EOMONTH($F$2,0)+0
F2セルに入力されている基準日である「2018年10月」の最終日をEOMONTH関数で求めます。その最終日に対して求めたい曜日の「数値」を足します。
EOMONTH関数の書式、使い方は前述の「月の最終日の求め方」で説明しましたので参照してください。
ここでいう「数値」とはWEEKDAY関数の返り値(種類は3)とします。そのため、WEEKDAY関数の返り値の表を見ながら作ってください。
| 種類 | 返り値 |
|---|---|
| 1 | 1：日 2：月 3：火 4：水 5：木 6：金 7：土 |
| 2 | 1：月 2：火 3：水 4：木 5：金 6：土 7：日 |
| 3(今回使用) | 0：月 1：火 2：水 3：木 4：金 5：土 6：日 |
今回求めたいのは月曜日なので、月曜日の「数値」である0を足します。
=2018年10月31日+0
=EOMONTH($F$2,0)+0の式で2018年10月31日が求められました。月曜日ではなく毎週火曜日を求める場合、=2018年10月31日+1で2018年11月1日を求めます。
今回は説明のためにあえて＋0をしています。0を足しても結果は変わらないのは明白なので実務で使用する際は0を足す必要はありません。
=WEEKDAY(EOMONTH($F$2,0),3)
続いてF2セルに入力されている「2018年10月」の最終日をEOMONTH関数で求め、それをWEEKDAY関数で曜日を表す「数値」にします。「=EOMONTH($F$2,0)+0」で求めた日付(2018年10月31日)からその「数値」を引きます。
曜日の「数値」は前述のWEEKDAY関数の返り値表を参照してください。
2018年10月31日は水曜日なので「数値」は2です。
2018年10月31日-2
→2018年10月29日(月曜日)が求められました。これだと前月の最終月曜日となってしまうので+7します。
2018年10月29+7=2018年11月5日
これで求めたい月の、1週目の月曜日が求められました。
ここまで説明してきた内容は、以下の通りです。
その月の全月曜日を求める
第1月曜日は7を足し、第2月曜日は14を足します。
7の倍数を足していけばOKです。
つまり以下の通りです。
第1月曜日：=EOMONTH($F$2,0)-WEEKDAY(EOMONTH($F$2,0),3)+7
第2月曜日：=EOMONTH($F$2,0)-WEEKDAY(EOMONTH($F$2,0),3)+14
第3月曜日：=EOMONTH($F$2,0)-WEEKDAY(EOMONTH($F$2,0),3)+21
第4月曜日：=EOMONTH($F$2,0)-WEEKDAY(EOMONTH($F$2,0),3)+28
第5月曜日：=EOMONTH($F$2,0)-WEEKDAY(EOMONTH($F$2,0),3)+35
第n月曜日の求め方
弟2月曜日だけ求めたいなど、特定の弟n曜日を求めたい場合は、7の倍数を足すことを覚えておけば便利です。上記の「その月の全月曜日を求める」で説明した式をご覧いただくとわかりやすいでしょう。
今回の例だと「月例会議」が第2月曜日ですので、14を最後に足せばOKです。
第2月曜日 =EOMONTH($F$2,0)-WEEKDAY(EOMONTH($F$2,0),3)+14
毎月○日の求め方
毎月○日、と日付を指定したい場合は、DATE関数を使用します。
今回の例だと「カード引き落とし」が毎月27日にあります。
DATE関数の書式は前回の記事で説明しました。
書式をおさらいすると以下の通りです。
年と月と日をバラバラに指定して、それを合体し、日付で返す関数でしたね。
=DATE(YEAR($F$5),MONTH($F$5),27)
F5セルには2018年11月1日が入力されていますので、この式の中身は以下のようになっています。
=DATE(2018,11,27)
日だけ直接27を指定しています。
計算結果は以下の通りです。
2018年11月27日
今回紹介したエクセル関数をスケジュールリストシートに設定する
定例タスクのタイミングに従って、エクセル関数をシート内に一覧で入力していきましょう。
スケジュールリストシートの例は以下の通り。（前回までの記事でB~Fの内容について紹介しました。）今回紹介した関数はAの開始日に入力してきます。
今回紹介したすべてのエクセル関数を入力した設定例は以下の通りです。参考にしてください！
まとめ&次回予告
以上、スケジュールを作成する際に想定されるよくある日付パターンをエクセル関数で求める方法をお伝えしました。
複雑なようですが慣れればとても簡単です。今回の記事を参考にすれば、処理日がパターン化された定例タスクであればすべて自動で求めることが可能です。
ここまで来たら、カレンダー作成までもう少しです。
次回は、エクセルVBAを使用して、カレンダーを作成する方法を紹介します！
どうぞお楽しみに！
連載目次：エクセルVBAで営業日を考慮したスケジュール表を自動で作成する
WORKDAY関数とエクセルVBAを組み合わせ、営業日を考慮したスケジュール表を自動作成する方法をお伝えします。 実務で使いこなすことができればとても便利なツールとして活用できるはずです！- エクセルで営業日判定を実現するWORKDAY関数の使い方
- エクセルVBAとWORKDAY関数の組み合わせでスケジュール表を自動作成する最初の一歩
- エクセル関数だけで営業日を考慮した月間スケジュールを作成する方法
- エクセル関数でスケジュール自動表示のためのカレンダーを作成する方法
- エクセルVBAでテンプレートをコピーし翌月のカレンダーシートを生成する方法
- エクセルVBAでカレンダーシートに翌月の日付を反映させる方法
- エクセルVBAのFunctionプロシージャで祝日判定関数を自作する方法
- エクセルVBAでカレンダーシートの土日祝日の行に背景色をつける方法
- エクセルVBAでカレンダーシートに自動でタスクを表示する方法
コメント
はじめまして、いつもこちらのサイトで勉強させて頂いています。
第1月曜日を求める計算式は、修正前の記事のWEEKDAY関数の種類を3から12に変えれば（下の計算式）、1日が月曜日でも求めることができるようです。
=$F$5-WEEKDAY($F$5,12)+7
コメントありがとうございます
種類12なら月曜日であればクリアできるのですが他の曜日だとクリアできなくなってしまいます。
曜日ごとに種類の指定をするのは大変なので、今回の修正では「前月最終日」を求めています。
紛らわしく申し訳ありません。
以上、ご確認ください。どうぞよろしくお願いいたします。