みなさん、こんにちは！
フジタニ(@libartweb)です。
エクセルVBAとWORKDAY関数を組み合わせたスケジュール表自動作成の方法をシリーズでお伝えしております。
前回の記事はこちら
WORKDAY関数を使用すれば土日だけでなく祝日の判定も可能であることをお伝えしました。
これを応用すれば、土日祝日を考慮した月間スケジュール表を自動で作成することができます！
実務で使いこなすことができればとても便利なツールとして活用できるはずです！
今回は最初にスケジュール表を自動作成する流れと概要について説明します。
エクセルVBAでスケジュール表を自動作成する流れ
基本的にはエクセル関数ですべての日付計算を行い、VBAは体裁を整えるためだけに使用します。
スケジュール表の完成イメージ
まずは、完成イメージをご覧いただき、どのように作るか？を具体的にイメージしていただければと思います。
このように○月○日には何のタスクがあるか？を自動でカレンダーに出力します。
スケジュール表自動作成で必要なシート
用意するのは以下の2シートのみでOKです。
| シート名 | シートの内容 |
|---|---|
| テンプレート | カレンダーのテンプレートシート |
| 日付計算 | 定例タスクの処理タイミングを羅列したスケジュール一覧シート |
カレンダーのテンプレートシート
カレンダーのテンプレートです。
エクセルVBAで指定月のスケジュール表を作成する際、このテンプレートをコピーし、変名(“yyyy年m月 スケジュール表” など)して、その変名したシートに対してエクセルVBAで土日祝日の行に色をつけたり、その月の内容に従って編集します。
日付計算シート
エクセル関数を使用して各タスクの処理日を一覧化します。
処理タイミングによって様々なエクセル関数を使用しますので、エクセル関数の知識が必要となります。(連載の中でよく使いそうなものを紹介します)
このシートがすべての処理タイミングを管理します。スケジュール表を自動作成をする上で非常に重要です。
スケジュール表自動作成の流れ
おおまかな流れは以下の通りです。
①定例タスクの処理タイミングをエクセル関数でリスト化
②エクセルVBAで翌月分のカレンダーシートを作成
②処理タイミング(①で作成)をカレンダーシート(②で作成)へエクセルVBAで転記
スケジュール表自動作成の仕組み
では①の処理タイミングをエクセル関数でリスト化する仕組みを詳しく見ていきましょう。
上記のスクリーンショットの画面内の各項目について説明します。
| 項目名 | 説明 | |
|---|---|---|
| A | 開始日 | そのタスクの基準となる開始日を設定します。例えば「英会話教室」ならその月の毎週月曜日すべてが記載されています。 |
| B | 日数 | WORKDAY関数で使用する日数を指定します。詳細は前回記事をご覧ください。 |
| C | 営業日計算 | WORKDAY関数でAの開始日から営業日を計算した日付を出力します。 |
| D | 基準日 | スケジュール表を作成する月を指定するための基準日です。デフォルトは「今日の日付」としています。 |
| E | 基準日の翌月 | 基準日の翌月を関数で求め、このセルに出力しています。今回は基準日の翌月分のカレンダーを作成するので「翌月」を求めています。 |
| F | 除外する日(祝日) | WORKDAY関数で使用する祝日の一覧です。 |
今回のスケジュールリストで紹介するスケジュールパターンは以下の通りです。
| 定例タスク | タイミング |
|---|---|
| 英会話教室 | 毎週月曜日(休み→翌営業日) |
| 月例会議 | 第2月曜日(休み→翌営業日) |
| 月末処理 | 毎月最終営業日 |
| カード引き落とし | 毎月27日(休み→翌営業日) |
| 口座へ入金 | カード引き落とし日の前営業日 |
詳細は次回以降の記事で1つずつ解説していきますが、このようなシート構成でスケジュール表作成の自動化を行います。
特に「開始日」はエクセルVBAで求めることも可能なのですが、シート内のエクセル関数で管理した方がメンテナンス性が高く、引き継ぎもしやすいということで今回紹介するツールは、基本的にはエクセル関数の使用がメインとなります。
エクセルVBAとWORKDAY関数を組み合わせる際の注意点
WORKDAY関数を実務に取り入れる際に、気をつけていただきたいのが祝日一覧表のメンテナンスです。
- 祝日が増えた
- 祝日一覧を１年分しか作っていないことを失念していてメンテナンスしていなかった
最もキケンなのが祝日一覧を直近数年分しか設定しておらず、それを過ぎてしまったときに気づけないことです。
気づかずに過ぎてしまったら…誤った営業日が表示され、正しい処理結果を得られなくなってしまいます。
- 祝日一覧の期限が近付いてきたらWorkbookオープン時に日付を自動で判定して注意メッセージを出力する(エクセルVBA)
- 祝日の取得をエクセルVBAによるIE操作で自動化する
など…忘れないための工夫を取り入れることをおすすめします。
1つ目のWorkbookオープン時の自動実行を実現する方法は以下の記事が参考になります。
忘れないための予防策も打っておきましょう。せっかく便利な関数を使っているのに、それがミスにつながったら意味がないですからね。
基準日とその翌月を求めてみよう
「日付計算」シート内の、「基準日」と「基準日の翌月」を求めてみましょう。
この部分です！
F2セル「基準日」には今日の日付を入れるため、TODAY関数を設定します。
TODAY関数は、システム日付(今日の日付)をシリアル値で返します。
書式は以下の通り。
F5セル「基準日の翌月」には、基準日の翌月を求めるため、DATE関数を使います。
DATE関数は引数に、年、月、日をバラバラにして指定し、シリアル値で日付を返す関数です。
今回のサンプルでは、以下の通り関数を設定しています。
=DATE(YEAR(F2),MONTH(F2)+1,1)
YEAR関数とMONTH関数を使用し、F2セル(TODAY関数で求めた今日の日付)を参照しています。
YEAR関数は引数として指定された日付の年だけを返す関数です。
YEAR関数は引数として指定された日付の月だけを返す関数です。
つまり、DATE関数の中身は以下のようになっています。YEAR関数とMONTH関数でF2セルの今日の日付を分解し、分解後の月に＋1して翌月を求めています。
=DATE(2018,9+1,1)
今回の場合だと、”2018年10月1日”がDATE関数で返されます。
これで、翌月分のスケジュール表を自動作成する第一歩として、今日の日付の「翌月」を求めることができました！
まとめ&次回予告
以上、スケジュール表を自動作成する流れと仕組み、翌月の求め方について説明しました。
用意するシートは2シートのみ、エクセルVBAはほぼ使わず、エクセル関数をメインに使用してスケジュール表を自動作成することを理解していただけたと思います。
次回以降、引き続きスケジュール表自動作成に必要な関数の解説を行っていきます。
どうぞお楽しみに！
連載目次：エクセルVBAで営業日を考慮したスケジュール表を自動で作成する
WORKDAY関数とエクセルVBAを組み合わせ、営業日を考慮したスケジュール表を自動作成する方法をお伝えします。 実務で使いこなすことができればとても便利なツールとして活用できるはずです！- エクセルで営業日判定を実現するWORKDAY関数の使い方
- エクセルVBAとWORKDAY関数の組み合わせでスケジュール表を自動作成する最初の一歩
- エクセル関数だけで営業日を考慮した月間スケジュールを作成する方法
- エクセル関数でスケジュール自動表示のためのカレンダーを作成する方法
- エクセルVBAでテンプレートをコピーし翌月のカレンダーシートを生成する方法
- エクセルVBAでカレンダーシートに翌月の日付を反映させる方法
- エクセルVBAのFunctionプロシージャで祝日判定関数を自作する方法
- エクセルVBAでカレンダーシートの土日祝日の行に背景色をつける方法
- エクセルVBAでカレンダーシートに自動でタスクを表示する方法
