コピー2018.10.03みなさん、こんにちは!フジタニ(@libartweb)です。エクセルVBAとWORKDAY関数を組み合わせたスケジュール表自動作成の方法をシリーズでお伝えしております。前回の記事はこちらエクセルで営業日判定を実現するWORKDAY関数の使い方エクセルで土日祝日を判定したい!といった場合に、祝日の判定は標準のエクセル機能では不可能です。今回紹介するWORKDAY関数を使用すれば、祝日の判定を実現することが可能です。tonari-it.com2018.09.27WORKDAY関数を使用すれば土日だけでなく祝日の判定も可能であることをお伝えしました。これを応用すれば、土日祝日を考慮した月間スケジュール表を自動で作成することができます!実務で使いこなすことができればとても便利なツールとして活用できるはずです!今回は最初にスケジュール表を自動作成する流れと概要について説明します。連載目次:エクセルVBAで営業日を考慮したスケジュール表を自動で作成するエクセルVBAでスケジュール表を自動作成する流れ基本的にはエクセル関数ですべての日付計算を行い、VBAは体裁を整えるためだけに使用します。スケジュール表の完成イメージまずは、完成イメージをご覧いただき、どのように作るか?を具体的にイメージしていただければと思います。このように○月○日には何のタスクがあるか?を自動でカレンダーに出力します。スケジュール表自動作成で必要なシート用意するのは以下の2シートのみでOKです。定例タスクの処理タイミングを羅列したスケジュール一覧シートカレンダーのテンプレートシートカレンダーのテンプレートです。)して、その変名したシートに対してエクセルVBAで土日祝日の行に色をつけたり、その月の内容に従って編集します。日付計算シートエクセル関数を使用して各タスクの処理日を一覧化します。処理タイミングによって様々なエクセル関数を使用しますので、エクセル関数の知識が必要となります。(連載の中でよく使いそうなものを紹介します)このシートがすべての処理タイミングを管理します。スケジュール表を自動作成をする上で非常に重要です。スケジュール表自動作成の流れおおまかな流れは以下の通りです。1定例タスクの処理タイミングをエクセル関数でリスト化2エクセルVBAで翌月分のカレンダーシートを作成2処理タイミング(1で作成)をカレンダーシート(2で作成)へエクセルVBAで転記スケジュール表自動作成の仕組みでは1の処理タイミングをエクセル関数でリスト化する仕組みを詳しく見ていきましょう。上記のスクリーンショットの画面内の各項目について説明します。そのタスクの基準となる開始日を設定します。「英会話教室」ならその月の毎週月曜日すべてが記載されています。WORKDAY関数で使用する日数を指定します。詳細は前回記事をご覧ください。WORKDAY関数でAの開始日から営業日を計算した日付を出力します。スケジュール表を作成する月を指定するための基準日です。デフォルトは「今日の日付」としています。基準日の翌月を関数で求め、このセルに出力しています。今回は基準日の翌月分のカレンダーを作成するので「翌月」を求めています。WORKDAY関数で使用する祝日の一覧です。今回のスケジュールリストで紹介するスケジュールパターンは以下の通りです。詳細は次回以降の記事で1つずつ解説していきますが、このようなシート構成でスケジュール表作成の自動化を行います。「開始日」はエクセルVBAで求めることも可能なのですが、シート内のエクセル関数で管理した方がメンテナンス性が高く、引き継ぎもしやすいということで今回紹介するツールは、基本的にはエクセル関数の使用がメインとなります。エクセルVBAとWORKDAY関数を組み合わせる際の注意点WORKDAY関数を実務に取り入れる際に、気をつけていただきたいのが祝日一覧表のメンテナンスです。祝日が増えた祝日一覧を1年分しか作っていないことを失念していてメンテナンスしていなかった最もキケンなのが祝日一覧を直近数年分しか設定しておらず、それを過ぎてしまったときに気づけないことです。気づかずに過ぎてしまったら...誤った営業日が表示され、正しい処理結果を得られなくなってしまいます。祝日一覧の期限が近付いてきたらWorkbookオープン時に日付を自動で判定して注意メッセージを出力する(エクセルVBA)祝日の取得をエクセルVBAによるIE操作で自動化するなど...忘れないための工夫を取り入れることをおすすめします。1つ目のWorkbookオープン時の自動実行を実現する方法は以下の記事が参考になります。エクセルVBAでブックを開いたときに他のワークブックも一緒に自動で開くエクセルで特定のファイルを開いたら、一緒に使用する他のファイルも自動で開くと便利です。今回はAuto_Openというイベントプロシージャを使ってブック起動時に他のブックも自動で開くVBAプログラムです。tonari-it.com2016.11.29忘れないための予防策も打っておきましょう。便利な関数を使っているのに、それがミスにつながったら意味がないですからね。基準日とその翌月を求めてみよう「日付計算」シート内の、「基準日」と「基準日の翌月」を求めてみましょう。この部分です!F2セル「基準日」には今日の日付を入れるため、TODAY関数を設定します。TODAY関数は、システム日付(今日の日付)をシリアル値で返します。=TODAY()F5セル「基準日の翌月」には、基準日の翌月を求めるため、DATE関数を使います。=DATE(年,月,日)DATE関数は引数に、年、月、日をバラバラにして指定し、シリアル値で日付を返す関数です。今回のサンプルでは、以下の通り関数を設定しています。=DATE(YEAR(F2),MONTH(F2)+1,1)YEAR関数とMONTH関数を使用し、F2セル(TODAY関数で求めた今日の日付)を参照しています。=YEAR(シリアル値)YEAR関数は引数として指定された日付の年だけを返す関数です。=MONTH(シリアル値)YEAR関数は引数として指定された日付の月だけを返す関数です。DATE関数の中身は以下のようになっています。YEAR関数とMONTH関数でF2セルの今日の日付を分解し、分解後の月に+1して翌月を求めています。=DATE(2018,9+1,1)今回の場合だと、”2018年10月1日”がDATE関数で返されます。以上、スケジュール表を自動作成する流れと仕組み、翌月の求め方について説明しました。用意するシートは2シートのみ、エクセルVBAはほぼ使わず、エクセル関数をメインに使用してスケジュール表を自動作成することを理解していただけたと思います。次回以降、引き続きスケジュール表自動作成に必要な関数の解説を行っていきます。お楽しみに!連載目次:エクセルVBAで営業日を考慮したスケジュール表を自動で作成するWORKDAY関数とエクセルVBAを組み合わせ、営業日を考慮したスケジュール表を自動作成する方法をお伝えします。プランノーツ代表、コミュニティ「ノンプロ研」主宰。ITで日本の『働く』の価値を上げる!」をテーマに、VBA&GASの開発、講師、執筆などをしております。→詳しいプロフィールはコチラ★ご依頼・ご相談はお気軽にどうぞ!→お問い合わせはコチラ★フォロー頂ければ嬉しいです。「コミュニティ+講座」形式で継続性&教え合える環境も提供。!ノンプログラマーがプログラミングスキルを身に着ける支援ををするコミュニティ。セミナー・もくもく会・Facebookグループのサポートで「自らで学び続ける力をつける」支援、「教え合うことで学びの価値を上げる」場の提供をしています。関連記事Excel・VBA活用術エクセルVBAでフォルダ内のファイル一覧を表示するDir関数の使い方エクセルVBAでファイルを開く作業を自動化する方法についてお伝えしています。今回はエクセルVBAでフォルダ内のファイル一覧を出力するDir関数の使い方です。ワイルドカード一つで便利に使えます。VBAで、受信ボックスの未読メール件数を取得してメッセージ表示させる方法をご紹介しています。受信フォルダだけでなく、サブフォルダの未読メール件数も取得できるので、Outlookの自動振り分け機能と一緒に使えば、Outlookをもっと便利に使えるかも?!2019.03.19Excel・VBA活用術エクセルVBAでテキストファイルを新規作成するFileSystemオブジェクトの使い方エクセルVBAによるマクロの動作状況をログファイルに残す方法をお伝えしています。今回は、書き出し先のテキストファイルの有無を判定して、もし存在しなければ新規作成するという処理を追加していきます。2017.05.07Excel・VBA活用術エクセルVBAでIEを操作するクラスに検索入力&ボタンクリックのメソッドを追加する方法エクセルVBAでIEスクレイピングをするときに便利なクラスの作り方をお伝えしております。今回は、エクセルVBAでIEを操作するクラスに検索窓の入力&ボタンクリックのメソッドを追加する方法をお伝えします。2019.04.26Excel・VBA活用術エクセル関数でスケジュール自動表示のためのカレンダーを作成する方法エクセル関数のみでカレンダーのテンプレートとなるシートを作成しそれを安全に運用していく方法をお伝えします。テンプレートは対象月のカレンダーを作成するために必要なシートで、スケジュール自動作成を行うための前準備です。Each文でファイル一覧を取得する方法エクセルVBAでFileSystemオブジェクトを使ってファイルやフォルダの操作をする方法についてお伝えしています。Each文を使ってファイル一覧を出力する方法です。「コミュニティ+講座」形式で継続性&教え合える環境も提供。詳細・お申込はこちら隣ITを応援する当ブログを「応援したい!」「役に立ったよ!」というお気持ちを、コチラからお支払いただくことができます。日々更新の活力倍増いたします。※PayPalアカウントが必要です。※金額は変更可能です。Platform23HTML・CSS32iPhone・アプリ29Monaca・スマホアプリ開発7PC全般・Windows86PowerPoint・VBA28Python87RPA1VBScript15Vue.js・JavaScript13WEBサービス・クラウド68WordPress・ブログ88Word・VBA42コミュニティ・イベント108セミナーのお知らせ11チャットワーク72データ分析・アクセス解析40仕事・生産性・働き方73動画配信8商業出版21起業・フリーランス72電子書籍・Kindle3運営会社プランノーツは"IT×働き方改革"をお手伝いする会社です。「コミュニティ+講座」形式で継続性&教え合える環境も提供。詳細・お申込はこちら隣ITを応援する当ブログを「応援したい!」「役に立ったよ!」というお気持ちを、コチラからお支払いただくことができます。日々更新の活力倍増いたします。※PayPalアカウントが必要です。※金額は変更可能です。Platform23HTML・CSS32iPhone・アプリ29Monaca・スマホアプリ開発7PC全般・Windows86PowerPoint・VBA28Python87RPA1VBScript15Vue.js・JavaScript13WEBサービス・クラウド68WordPress・ブログ88Word・VBA42コミュニティ・イベント108セミナーのお知らせ11チャットワーク72データ分析・アクセス解析40仕事・生産性・働き方73動画配信8商業出版21起業・フリーランス72電子書籍・Kindle3運営会社プランノーツは"IT×働き方改革"をお手伝いする会社です。RowSourceプロパティとAddItemメソッド2020.01.21GASでGoogleスライドのSlideオブジェクトの概要とその取得方法2020.01.162020.01.24Facebookページいつも隣にitのお仕事タイトルとURLをコピーしました。