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

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

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

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

日付の自動設定

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

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

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

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

考え方（WEEKDAY関数を使う）

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

WEEKDAY（シリアル値,種類）

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

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

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

1. 求めたい年月の1日の曜日番号を取得する。WEEKDAY(2024/7/1)=2 ですから、2024年7月1日は月曜日です。
2. 求めたい曜日番号は金曜日なので(曜日番号=6)に７を足して、1.の（1日の曜日番号=２)との差を求め、MOD関数で、７で割った余りを求めるます（α）。７を足すのはマイナスになるのを避けるためです。
   α=MOD(6 + 7 - 2,7)=4
3. 第X週については、1週間は7日間なので、(X-1)を7倍した数（β）を求める
   β=7*(3-1)=14
4. 求めたい年月の1日＋α＋βが、 第(Ｘ)週の◯曜日の日付 となる
   1 + 4 + 14 =19 なので、2024年7月19日が第3金曜日である。
5. 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関数を使って、次の図で示す計算式が設定されています。

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

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

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

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

=$A4=$F$6

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

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

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

VBAマクロを使う方法

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

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

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

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

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

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

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

NFAキャリアスクール