作者別: it-yobi

固定曜日休みや休日（祝日など）を考慮した、指定した期間の営業日数を求める方法について解説していきます。

指定した期間の営業日数を求める

指定した期間の営業日数を求める場合は、NETWORKDAYS.INTL関数が便利です。

以下の表で営業日数（赤枠）を求めていきます。
休日を考慮する場合は、予め休日一覧表を用意する必要があります。

赤枠の先頭に、以下のような数式を入力します。

=NETWORKDAYS.INTL(B2,C2,1,休日一覧!$B$2:$B$100)
// B2：開始日
// C2：終了日
// 1：固定曜日休み（土日）
// 休日一覧!$B$2:$B$100：休日一覧表の日付の範囲（絶対参照）

固定曜日休みに関しては、以下の候補から選択することができます。

候補以外の組み合わせを指定する方法については、後半で解説します。

休日一覧表の範囲は、必ず日付が入力されているセル（空白セルを除く）のみにする必要があります。
休日一覧表に見出しが用意されている場合で、見出しを含む範囲を指定してしまうと、以下のようにエラーになってしまいます。

また、入力した数式を複数の範囲にコピーする場合は、休日一覧表の範囲を「$」を加えた絶対参照にする必要があります。

休日一覧!$B$2:$B$100

以上の内容を踏まえて、先ほどの数式を入力することで、以下のように営業日数を求めることができます。

他の行に関しても、数式をコピーするだけで営業日数を求めることができます。

候補にない固定曜日休みを指定する

候補にない固定曜日休みを指定するには、直接指定する必要があります。
直接指定する方法は、以下になります。

（例）月水金を固定休みとする場合

=NETWORKDAYS.INTL(B2,C2,"1010100",休日一覧!$B$2:$B$100)

先頭を月曜日、休みの曜日を「0」、営業日を「1」として、日曜日まで数字を並べて指定します。
上記の数式を入力することで、月水金を固定休みとした営業日数を求めることができます。

補足

NETWORKDAYS.INTL関数に似た関数で、NETWORKDAYS関数というものがあります。
NETWORKDAYS関数では、土日固定休みと休日を考慮した営業日数しか求めることができません。
固定曜日休みを指定する引数がないため、NETWORKDAYS.INTL関数を覚えていた方が、活用の幅が広がります。

以下の表の「*」が付いた項目を入力必須項目とします。

上記の画像には入力漏れがあります。
しかし、ぱっと見では、どこが漏れているのかが分かりづらいです。

ということで今回は、以下のように、データが入力されている行に対して、入力必須項目が未入力の場合に、自動で色付けする設定方法について解説していきます。

※こちらで実現したファイルは、記事の最後にて配布しています。

入力必須項目が未入力の場合に色付け

特定の条件を満たしているセルを自動で色付けするには、条件付き書式を活用します。
まずは、入力必須項目の見出しを除く列全体を選択し、[ホーム]タブの中の[条件付き書式]から[新しいルール]を選択します。

次に、選択したセルが空白かどうかで色付けするかどうかを判断していきます。
その際は、設定画面で[ルールの種類]を[指定の値を含むセルだけを書式設定]にし、[次のセルのみを書式設定]を[空白]にします。

次に、[書式]を選択し、書式設定の画面の[塗りつぶし]タブから好みの色を選択します。

後は確定するだけで、以下のように、対象の項目の空白セルが色付けされます。

しかし、このままですと、データが存在していない行（１２行目以降）も色付けされてしまいます。
いずれかの項目に値が入力されている場合のみに色付けするには、次の手順を行います。

いずれかの項目に値が入力されている場合のみに色付け

いずれかの項目に値が入力されている場合のみに色付けするためには、「いずれかの項目に値が入力されているかどうかを判定する条件」を加える必要があります。

今回の表の場合、数式が入力されている列が含まれないため、１つのデータ行の値の数をCOUNTA関数で数えて、その数が０よりも大きいかどうかで判定することができます。

では一度、先ほどの条件付き書式の設定を削除します。

次に、対象のセルを選択して、[新しいルール]を選択します。

設定画面が表示されましたら、以下の条件を設定していきます。

対象のセルが空白、かつ、対象行のいずれかのデータが入力されている

このような複雑な設定を行うには、[ルールの種類]を[数式を使用して、書式設定するセルを決定]にします。

表示されたテキストボックスに、選択基準のセルに対する条件式を入力します。
こちらの場合は、選択基準のセルがF2になるため、セルF2に対する以下のような条件式を入力します。

=AND(F2="",COUNTA(A2:G2)>0)
// F2=""：セルF2が空の場合
// COUNTA(A2:G2)>0：該当行のいずれかの項目に値が入力されている場合
// AND(…)：指定した複数条件のすべてを満たしている場合

ただ、ここで設定した条件式は、他の選択範囲にも相対的に反映されます。
そのため、他のセルに反映されるときに変わってほしくない参照範囲は、予め「$」を加えて固定しておく必要があります。

こちらの場合は、表の列をA～G列で固定したいので、以下のように「$」を加えます。

=AND(F2="",COUNTA($A2:$G2)>0)

条件式の入力ができましたら、[書式]を選択し、書式設定の画面の[塗りつぶし]タブから好みの色を選択します。

後は確定するだけで完成です。

以上の設定で、データが存在する行の入力必須項目で、未入力のセルのみが色付けされます。

▼サンプルファイル▼

以下の表は、各作業に対して「A～C」のクラスを割り当てる表です。
「A～C」にはチェックボックスが用意されており、それとは別に、「A～C」を一括でチェックする用のチェックボックス「全」も用意されています。

チェックボックスの作成方法は、以下の記事を参考にしてください。
＞チェックボックスの作成方法

この「全」と「A～C」のチェックボックスは連動しており、以下のように動作します。

---

・「全」をチェック

・A~Cをすべてチェック

・「全」を外す

---

このような、動的なチェックボックスの作成方法について解説していきます。

※こちらで開発したファイルは記事の最後にて配布しています。

1. 開発準備

今回は、特定のチェックボックスを操作すると同時に、他のチェックボックスも操作するという仕組みを作っていきます。

チェックボックスが配置されたセルには、TRUEもしくはFALSEという値が入力されています。

つまり、チェックボックスが配置されたセルの値が操作されると同時に、必要に応じて、他のチェックボックスを操作する必要があります。

そのように、該当するシートのセルを編集すると同時に処理を自動で実行するには、「シートモジュール」の「イベントプロシージャ」を活用します。
シートモジュールは、該当するシートのタブ上で右クリックし、[コードの表示]を選択することで表示することができます。

選択すると、以下のエディタ画面（VBE）が表示されます。
また、該当するシートのシートモジュールが表示された状態になります。
「Option Explicit」は、VBEの設定内容次第では表示されません。「Option Explicit」についての解説はこちらでは省略します。

次に、該当するシートのセルを編集すると同時に処理が実行される特殊なプロシージャ（イベントプロシージャ）を用意する必要があります。
そのためには、シートモジュールの左上のリストから「Worksheet」を選択します。

「Worksheet」を選択すると、自動で「Worksheet_SelectionChange」というプロシージャが表示されます。
このプロシージャは、該当するシート上のいずれかのセルが選択されると同時に処理が実行されるイベントプロシージャになります。

ただ、今回使用するイベントプロシージャは、セルを編集すると同時に処理が実行されるものになります。
そのため、右上のリストから「Change」を選択します。

表示された「Worksheet_Change」というプロシージャを活用します。
「Worksheet_SelectionChange」というプロシージャに関しては削除して問題ないです。

2. コードの記述

以下のコードを記述します。

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Range: Set r = Target

    If r.CountLarge = 1 And _
        r.Row >= 4 And r.Row <= 23 And _
        r.Column >= 3 And r.Column <= 6 Then
        
        Application.EnableEvents = False
        
        Dim r1 As Range: Set r1 = Cells(r.Row, "C")
        Dim r2 As Range: Set r2 = Cells(r.Row, "D")
        Dim r3 As Range: Set r3 = Cells(r.Row, "E")
        Dim r4 As Range: Set r4 = Cells(r.Row, "F")
        
        If r.Column = 3 Then
            If r.Value Then
                Range(r2, r4).Value = True
            Else
                Range(r2, r4).Value = False
            End If
        Else
            If r2 And r3 And r4 Then
                r1.Value = True
            Else
                r1.Value = False
            End If
        End If
        
        Application.EnableEvents = True
        
    End If

End Sub

では、コードについて解説していきます。

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Range: Set r = Target

    If r.CountLarge = 1 And _
        r.Row >= 4 And r.Row <= 23 And _
        r.Column >= 3 And r.Column <= 6 Then
        
        Application.EnableEvents = False
        
        '省略
        
        Application.EnableEvents = True
        
    End If

End Sub

「Worksheet_Change」というプロシージャの引数の「Target」に、編集されたセルの情報が渡されるため、そのセルの情報から、表の対象のセルが編集されたかどうかを確認しています。

まずは、入れ物（変数）「r」を用意し、そこにTargetの情報を割り当てています。

次に、編集されたセルが１つ（r.CountLarge = 1）で、行番号が４以上２３以下（r.Row >= 4 And r.Row <= 23）、尚且つ、列番号が３以上６以下（r.Column >= 3 And r.Column <= 6）であることを確認しています。
こちらは、「チェックボックスが配置されている範囲内のセルが、単体で編集された場合」という条件になります。

この条件を満たした場合に、Ifの中の処理を実行します。
Ifの中では、他のチェックボックスを操作する可能性があります。
チェックボックスが操作されると、「Worksheet_Change」の処理が再度実行されてしまい無限ループになってしまう恐れがあります。
そのため、Ifの処理の中では、イベントを無効化する処理（Application.EnableEvents = False ～ Application.EnableEvents = True）を記述しています。

        Dim r1 As Range: Set r1 = Cells(r.Row, "C")
        Dim r2 As Range: Set r2 = Cells(r.Row, "D")
        Dim r3 As Range: Set r3 = Cells(r.Row, "E")
        Dim r4 As Range: Set r4 = Cells(r.Row, "F")
        
        If r.Column = 3 Then
            If r.Value Then
                Range(r2, r4).Value = True
            Else
                Range(r2, r4).Value = False
            End If
        Else
            If r2 And r3 And r4 Then
                r1.Value = True
            Else
                r1.Value = False
            End If
        End If

次に、操作されたセルと同じ行にあるチェックボックスのセルの情報を入れ物（変数）「r1～r4」に割り当てています。
そして、操作されたセルの列番号が３の場合とそれ以外の場合で、処理を分岐しています。

操作されたセルの列番号が３の場合は、「全」のチェックボックスが操作されたときになります。
そのため、「全」がチェックされている場合は、「A～C」のチェックボックスすべてをチェックし、「全」がチェックされていない場合は、「A～C」のチェックボックスすべてのチェックを外しています。
「A～C」のチェックボックスが配置されたセルは、連続したセルになっています。
そのため、「A」のチェックボックスのセルから「C」のチェックボックスのセルまでということで、「Range(r2, r4)」と範囲指定することができます。

ちなみに、以下の該当する処理に関しては、１行で表現することもできます。

            If r.Value Then
                Range(r2, r4).Value = True
            Else
                Range(r2, r4).Value = False
            End If

↓

            Range(r2, r4).Value = r.Value

操作されたセルの列番号が３でない場合は、「A～C」のチェックボックスのいずれかが操作されたときになります。
そのため、「A～C」のチェックの有無を確認し、すべてがチェックされているときのみ、「全」をチェックし、それ以外は、「全」のチェックを外しています。

ちなみに、以下の該当する処理に関しても、１行で表現することができます。

            If r2 And r3 And r4 Then
                r1.Value = True
            Else
                r1.Value = False
            End If

↓

            r1.Value = r2 And r3 And r4

3. 完成

以上の内容で実現できます。
「全」と「A～C」のチェックボックスが連動するようになります。

---

・「全」をチェック

・A~Cをすべてチェック

・「全」を外す

---

▼サンプルファイル▼