【Excel Tips】隔週の曜日に自動で色を付ける

毎月第1と第3の金曜日に定期的な行事があるので、カレンダーに自動的に色塗りをしたい、なんてこと、よくありますよね。

「来月の第2月曜日にアポを取りたいのだが、何日だっけ?」なんてこともあります。

もちろん第◯の◯曜日でも塗り分けられるように考えてみます。

日付の自動設定

本題ではありませんが、A列の日付の設定方法について説明します。
[A1]セルに「年」が、[B1]セルに「月」が入力されています。
[A4]の、月の初日1日は、DATE関数を使って、=DATE(A1,B1,1) となります。

[A 5]セルには、IFS関数とEOMONTH関数を使って、
=IFS(A4=EOMONTH($A$4,0),"",A4="","",A4<>EOMONTH($A$4,0),A4+1)
と設定します。[A6]セル以後はこれをオートフィルします。

IFS関数はExcelの2019以降で使用できます。Excel 2016以前のバージョンではIF関数をネストにして使います。

=IF(A4=EOMONTH($A$4,0),"",IF(A4="","",IF(A4<>EOMONTH($A$4,0),A4+1,"")))

これで大・小の月、閏年の2月にも対応できます。


A列の日付表示は、ユーザー定義で「d」を指定し、[日]だけが表示されるようにしてあります。(これを単に数字とすると、上の式が動作しません)

考え方(WEEKDAY関数を使う)

WEEKDAY関数は指定した日付の曜日番号を取得する関数です。

WEEKDAY(シリアル値,種類)

2番目の引数「種類」を省略すると、曜日番号は日曜日始まりで1からとなり、以下のようになります。

曜日曜日番号定数
日曜日1vbSunday
月曜日2vbMonday
火曜日3vbTuesday
水曜日4vbWednesday
木曜日5vbThursday
金賞日6vbFriday
土曜日7vbSaturday

2024年7月の第3金曜日の日付を求める例で、手順を説明します。

  1. 求めたい年月の1日の曜日番号を取得する。WEEKDAY(2024/7/1)=2 ですから、2024年7月1日は月曜日です。
  2. 求めたい曜日番号は金曜日なので(曜日番号=6)に7を足して、1.の(1日の曜日番号=2)との差を求め、MOD関数で、7で割った余りを求めるます(α)。7を足すのはマイナスになるのを避けるためです。
    α=MOD(6 + 7 - 2,7)=4
  3. 第X週については、1週間は7日間なので、(X-1)を7倍した数(β)を求める
    β=7*(3-1)=14
  4. 求めたい年月の1日+α+βが、 第(X)週の◯曜日の日付 となる
    1 + 4 + 14 =19 なので、2024年7月19日が第3金曜日である。
  5. Date関数で表すと、求める日付は、Date(2024,7,1+ α + β) =2024/7/19となる

説明用のExcelファイルをダウンロードできます。

マクロ付きファイルの開き方

マクロを含んだExcelファイルをダウンロードして開こうとすると、

「セキュリティリスク」のメッセージが表示されます。そのときは、ファイルを一旦閉じます。
ファイル名をマウスで右クリックし、ドロップダウンリストから「プロパティ」を選択します。

プロパティ画面で、セキュリティの「☑許可する(k)」にチェックをし、OKをクリックしてください。

再びファイルを開くと、

「セキュリティの警告 マクロが無効にされました」メッセージが出るので、「コンテンツの有効化」をクリックします。

これで正常に使えるようになります。

ダウンロードしたファイルの「日付を求める」シートに、上の計算式が入力してあるので、第◯週の◯曜日の日付を求めることができます。

ただ、こんなことをするよりも、Googleカレンダーなりを見れば解決するわけですよね。しかし、自前のカレンダーを作成する際に使えます。

これらの計算式を使って、自前のカレンダーに条件付き書式で設定する方法を次に説明します。

条件付き書式を使う方法

ダウンロードしたDayOfWeekNum.xlsmファイルの「条件付き書式」シートを使います。
入力するセルは色のセルです。第1と第3のように2回設定できるようになっています。
セルA1に「年」、B1に「月」、F2,G3には第X週を、F4,G4には求めたい曜日番号を入力します。

求める日付は、F6,G6セルに表示されます。

F,G列のセルには、WEEKDAY関数、MOD関数、DATE関数を使って、次の図で示す計算式が設定されています。

ここまでの準備ができたら、カレンダー領域に条件付き書式を設定していきます。

  1. 項目名を除いたA4:C34を範囲選択します
  2. 「条件付き書式」①「新しいルール」②をクリックし、

3.「数式を使用して、書式設定するセルを決定」③をクリックします

4.「次の数式を満たす場合に値を書式設定:」④欄に、=を入れ、A4セルをクリックしてF4キーを2回クリックして、$A4となるようにします。(Aの頭にだけ$が付く)次に求める日付のセルF6をクリックすると、

=$A4=$F$6

となります。$F$6は絶対参照ですが、$A4を複合参照形式にしておくのがポイントです。その理由は、A列の数式をB,C列にもコピーするのですが、数式ではA列を固定しておくことで、A,B,C列が同じ条件付き書式になります。

「書式」⑤をクリックして、「塗りつぶし」⑥タブを選び、適当な色⑦を選択して「OK」⑧をクリック。

同じ手順で、G列の2回目の日付を条件付き書式に設定します。

VBAマクロを使う方法

ダウンロードしたエクセルファイルの「VBAマクロ」シートをアクティブにしてください。

上と同じ動作をVBAのfunctionプロシージャで作成します。

  1. 第◯週◯曜日の日付を求めるfunctionプロシージャを作ります。
  2. 日付の範囲をRangeオブジェクトとし、For Each 構文で1日から順に、functionプロシージャの返す日付と一致するかを判定します。
  3. 一致すればその日付の行を色塗りし、「予定」C列に予定を入力します。

1.のfunctionプロシージャgetWeekNUmDate()は次のようになります。(コピペ可)

functionプロシージャの戻り値は日付です。

このfunctionプロシージャを呼び出して、カレンダーに色付けをするメインのSubプロシージャは次のようになります。ここではプロシージャ名と変数名は日本語にしてみました。

実行は、下図のセルに入力後、「開発」「マクロ」から「第何週の曜日を塗り分ける()」をクリックしてください。

以前の記事で、土日祝祭日を塗り分ける方法は紹介していますから、これと併用すると完璧なカレンダーができます。

【Excel Tips】土日祝祭日の日付に自動で色を付ける

目次この記事の要点日付の自動設定書式設定・入力規則関数を使った計算式を入力内閣府のWebサイトから「国民の祝日・休日」データを取得祝祭日の更新条件付き書式で、土、…

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です