【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からとなり、以下のようになります。
曜日 | 曜日番号 | 定数 |
日曜日 | 1 | vbSunday |
月曜日 | 2 | vbMonday |
火曜日 | 3 | vbTuesday |
水曜日 | 4 | vbWednesday |
木曜日 | 5 | vbThursday |
金賞日 | 6 | vbFriday |
土曜日 | 7 | vbSaturday |
2024年7月の第3金曜日の日付を求める例で、手順を説明します。
- 求めたい年月の1日の曜日番号を取得する。WEEKDAY(2024/7/1)=2 ですから、2024年7月1日は月曜日です。
- 求めたい曜日番号は金曜日なので(曜日番号=6)に7を足して、1.の(1日の曜日番号=2)との差を求め、MOD関数で、7で割った余りを求めるます(α)。7を足すのはマイナスになるのを避けるためです。
α=MOD(6 + 7 - 2,7)=4 - 第X週については、1週間は7日間なので、(X-1)を7倍した数(β)を求める
β=7*(3-1)=14 - 求めたい年月の1日+α+βが、 第(X)週の◯曜日の日付 となる
1 + 4 + 14 =19 なので、2024年7月19日が第3金曜日である。 - Date関数で表すと、求める日付は、Date(2024,7,1+ α + β) =2024/7/19となる
説明用のExcelファイルをダウンロードできます。
ダウンロードしたファイルの「日付を求める」シートに、上の計算式が入力してあるので、第◯週の◯曜日の日付を求めることができます。
ただ、こんなことをするよりも、Googleカレンダーなりを見れば解決するわけですよね。しかし、自前のカレンダーを作成する際に使えます。
これらの計算式を使って、自前のカレンダーに条件付き書式で設定する方法を次に説明します。
条件付き書式を使う方法
ダウンロードしたDayOfWeekNum.xlsmファイルの「条件付き書式」シートを使います。
入力するセルは色のセルです。第1と第3のように2回設定できるようになっています。
セルA1に「年」、B1に「月」、F2,G3には第X週を、F4,G4には求めたい曜日番号を入力します。
求める日付は、F6,G6セルに表示されます。
F,G列のセルには、WEEKDAY関数、MOD関数、DATE関数を使って、次の図で示す計算式が設定されています。
ここまでの準備ができたら、カレンダー領域に条件付き書式を設定していきます。
- 項目名を除いたA4:C34を範囲選択します
- 「条件付き書式」①「新しいルール」②をクリックし、
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プロシージャで作成します。
- 第◯週◯曜日の日付を求めるfunctionプロシージャを作ります。
- 日付の範囲をRangeオブジェクトとし、For Each 構文で1日から順に、functionプロシージャの返す日付と一致するかを判定します。
- 一致すればその日付の行を色塗りし、「予定」C列に予定を入力します。
1.のfunctionプロシージャgetWeekNUmDate()は次のようになります。(コピペ可)
Option Explicit
Function getWeekNumDate(year As Integer, month As Integer, DayOfWeek As Integer, weekNum As Integer) As Date
Dim firstWeekday As Integer
Dim diff As Integer
' 求めたい年月の1日の曜日番号求める
firstWeekday = Weekday(DateSerial(year, month, 1))
' 1日の曜日番号と求めたい曜日番号の差分を求める
diff = (DayOfWeek + 7 - firstWeekday) Mod 7
' 目的の日付データを返す
getWeekNumDate = DateSerial(year, month, 1 + diff + 7 * (weekNum - 1))
End Function
functionプロシージャの戻り値は日付です。
このfunctionプロシージャを呼び出して、カレンダーに色付けをするメインのSubプロシージャは次のようになります。ここではプロシージャ名と変数名は日本語にしてみました。
Sub 第何週の曜日を塗り分ける()
Dim 予定 As String
Dim 年 As Integer
Dim 月 As Integer
Dim 回(2) As Integer
Dim 曜日(2) As Integer
Dim i As Integer
Dim 日付 As Range
Dim Calendar As Range '塗り分ける範囲をオブジェクト変数とする
Dim getDate As Date 'functionプロシージャからの戻り値を格納
'「VBAマクロ」シートをアクティブにする
Worksheets("VBAマクロ").Activate
Set Calendar = Range("A4:A34")
年 = Range("A1")
月 = Range("B1")
予定 = Range("F4")
'カレンダーの背景色とデータをクリアする
Range("A3:C34").Interior.ColorIndex = xlNone
Range("C5:C34").ClearContents
For i = 1 To 2
回(i) = Cells(2, i + 5)
曜日(i) = Cells(3, i + 5)
getDate = getWeekNumDate(年, 月, 曜日(i), 回(i))
For Each 日付 In Calendar
If 日付 = getDate Then
日付.Resize(1, 3).Interior.Color = rgbAquamarine 'アクアマリン
日付.Offset(, 2) = 予定
End If
Next
Next i
End Sub
実行は、下図のセルに入力後、「開発」「マクロ」から「第何週の曜日を塗り分ける()」をクリックしてください。
以前の記事で、土日祝祭日を塗り分ける方法は紹介していますから、これと併用すると完璧なカレンダーができます。