小技集

以下の予定表は、セルC2に配置されたスピンボタンを押すことで、１週間単位で表示する日付を切り替えることができます。

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

こちらは、実際の値が置き換わっている訳ではなく、対象の週以外の列が非表示になっています。

そのため、表示されているセルに直接値を入力しても問題ございません。

今回は、上記のように、スピンボタンで表示する列を一定間隔で切り替える仕組みを実現していきます。

1. スピンボタンの用意

今回は、以下のすべての列が表示されている予定表から作成していきます。

まずは、スピンボタンを用意します。
スピンボタンは、[開発]タブの中の[挿入]から[スピンボタン]を選択することで、好みの位置に配置することができます。

スピンボタンの用意ができたら、スピンボタンの上で右クリックし、[コントロールの書式設定]を選択します。

[コントロールの書式設定]にて、以下のように設定します。

スピンボタンで指定した数値が、予定表の表示する週になります。
[最大値]を５０にすると、最大５０週目までしか表示できなくなります。
そのため、必要に応じて[最大値]は修正してください。

[リンクするセル]は、スピンボタンの現在値を表示させるセルになります。
こちらでは、スピンボタンの隣のセルB2を指定しています。

2. 開発準備

次は、スピンボタンをクリックすると同時に、予定表の週の表示を切り替える仕組みを実現します。
そのためには、VBAを活用します。

スピンボタンに連動する機能を開発する必要があるため、スピンボタンの上で右クリックし、[マクロの登録]を選択します。

[マクロの登録]の画面にて、開発する機能の名前を入力し、[新規作成]を選択します。
こちらでは、「表示切替」と入力しています。

次に表示される以下の画面の「Sub ○○」から「End Sub」の間にコードを記述します。

3. コードの記述（列の表示切り替え）

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

Sub 表示切替()

    Dim no As Long
    no = Range("B2").Value
    
    Columns("D:MY").Hidden = True
    Range( _
        Columns(no * 7 - 3), _
        Columns(no * 7 + 3)).Hidden = False
        
End Sub

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

    Dim no As Long
    no = Range("B2").Value

「no」という整数（Long）専用の変数（入れ物）を用意し、その「no」にセルB2の値（表示する週を表す数値）を格納しています。

    Columns("D:MY").Hidden = True

予定表の日付の列全体を非表示にしています。

    Range( _
        Columns(no * 7 - 3), _
        Columns(no * 7 + 3)).Hidden = False

「no」の値から対象の週を指定し、対象の週の列を表示しています。

「no」が１のときは、１週目である４列目から１０列目を表示し、
「no」が２のときは、２週目である１１列目から１７列目を表示します。
この関係性を「no」を用いた数式で表すと、「no * 7 – 3」列目から「no * 7 + 3」列目になります。

そのため、「Range」で「Columns(no * 7 – 3)」から「Columns(no * 7 + 3)」と対象の列の範囲を指定しています。

4. 完成

以上の手順で完成です。
スピンボタンを押すことで、対象の週のみが表示されるようになります。

▼サンプルファイル▼

シート上の表の内容をもとに、データ数分のフォルダを一括で作成する仕組みを実現していきます。

こちらでは、以下のように、生徒一覧表の内容をもとに「No_氏名」という名前のフォルダを一括で作成する仕組みを実現していきます。

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

1. 開発準備

まず初めに、[開発]タブを選択し、[マクロ]を選択します。

表示された以下の画面にて、開発する機能の名前を入力し、[作成]を選択します。
こちらでは、「GenerateFolders」と入力しています。

次に表示される以下の画面の「Sub ○○」から「End Sub」の間にコードを記述します。

2. コードの記述（フォルダ一括作成）

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

Sub GenerateFolders()
    
    Dim i As Long
    For i = 3 To 32
        '保存先のフォルダのパスを貼り付けて「\」を追加
        MkDir "A:\出力\" & _
            Cells(i, "B").Value & "_" & _
            Cells(i, "C").Value
    Next i
    
End Sub

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

    Dim i As Long
    For i = 3 To 32
        '省略
    Next i

表の１行１行を繰り返す際に使用する変数（i）を用意し、「For」で変数（i）を３～３２までを繰り返して実行します。

３～３２という数値は、フォルダを作成する対象のデータが入力されている行の範囲になります。

        '保存先のフォルダのパスを貼り付けて「\」を追加
        MkDir "A:\出力\" & _
            Cells(i, "B").Value & "_" & _
            Cells(i, "C").Value

「MkDir」の後に作成したいフォルダのパスを指定することで、フォルダを作成できます。

今回作成するフォルダの位置は、「Aドライブの中の出力フォルダの中」になるので、先頭に「”A:\出力\”」とパスを指定しています。

その後には、作成するフォルダの名前をセルの値を用いて指定しています。
「Cells(i, “B”).Value」で繰り返し処理で使用している変数（i）の値の行にあるB列の値（No）を取得し、「& “_”」で「_」を加え、「& Cells(i, “C”).Value」で変数（i）の値の行にあるC列の値（氏名）を加えています。
※「 _」は１つのコードを改行するときに指定します。

3. 完成

以上の手順で完成です。
作成したコードを指定して実行することで、瞬時にフォルダが作成されます。

4. 補足（Noを「０埋め２桁表記」にする）

作成するフォルダ名の先頭のNoを「０埋めの２桁表記」にしたい場合は、以下のように、「Format」を活用することで実現できます。

        MkDir "A:\出力\" & _
            Format(Cells(i, "B").Value, "00") & "_" & _
            Cells(i, "C").Value

「Format」についての詳しい解説は省略しますが、ワークシート上の「TEXT関数」とほぼ同じようなものです。

上記の内容に書き換えて実行すると、以下のように、Noを「０埋め２桁表記」で作成できます。

▼サンプルファイル▼

以下の書類フォーマットは、書類の中に直接入力することも、書類の下の表に入力して書類の中に反映させることもできます。

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

このような、双方向からの入力を実現する方法について解説していきます。

1. 開発準備

今回は、予め指定したセルに値を入力すると同時に、そのセルに紐づいた別のセルにも同じ値を入力する仕組みを作っていきます。

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

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

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

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

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

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

2. コードの記述

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

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.EnableEvents = False
    
    Select Case Target.Address(False, False)
        Case "F3"
            Range("D14").Value = Target.Value
        Case "F4"
            Range("D15").Value = Target.Value
        Case "B4"
            Range("D16").Value = Target.Value
        Case "D6"
            Range("D17").Value = Target.Value
        Case "C7"
            Range("D18").Value = Target.Value
        Case "D14"
            Range("F3").Value = Target.Value
        Case  "D15"
            Range("F4").Value = Target.Value
        Case "D16"
            Range("B4").Value = Target.Value
        Case "D17"
            Range("D6").Value = Target.Value
        Case "D18"
            Range("C7").Value = Target.Value
    End Select
    
    Application.EnableEvents = True
    
End Sub

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

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.EnableEvents = False
    
    '省略
    
    Application.EnableEvents = True
    
End Sub

「Worksheet_Change」というプロシージャの引数の「Target」に、編集されたセルの情報が渡されます。
そのセルの情報を用いて、双方向での入力を実現するのですが、処理の中で別のセルの値を編集してしまうと、再度「Worksheet_Change」が実行されてしまい無限ループになってしまいます。
そのため、処理の前後に、イベントを無効化する処理（Application.EnableEvents = False ～ Application.EnableEvents = True）を記述しています。

    Select Case Target.Address(False, False)
        Case "F3"
            Range("D14").Value = Target.Value
        Case "F4"
            Range("D15").Value = Target.Value
        Case "B4"
            Range("D16").Value = Target.Value
        Case "D6"
            Range("D17").Value = Target.Value
        Case "C7"
            Range("D18").Value = Target.Value
        Case "D14"
            Range("F3").Value = Target.Value
        Case  "D15"
            Range("F4").Value = Target.Value
        Case "D16"
            Range("B4").Value = Target.Value
        Case "D17"
            Range("D6").Value = Target.Value
        Case "D18"
            Range("C7").Value = Target.Value
    End Select

編集されたセルのアドレスから、双方向の入力をする対象のセルかどうかを判断し、対象のセルの場合は、紐づいているセルにも同じ値を入力しています。

「Target.Address(False, False)」で編集されたセルのアドレスを相対参照の形式で取得し、そのアドレスが「F3」の場合は、セルD14にも同じ値を入力、「F4」の場合は、セルD15にも同じ値を入力と、必要な組み合わせ分記述しています。

3. 完成

以上の内容で実現できます。
予め指定したセルに関しては、値を入力すると同時に、紐づいたセルにも同じ値が入力されます。

▼サンプルファイル▼

今回は、１つ１つのセルごとに割り当てましたが、表などで双方向の入力を実現する場合は、以下のように記述した方がよいです。

（例）以下の表①に入力したら表②にも反映、表②に入力したら表①にも反映させる

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.EnableEvents = False
    
    If Target.Row >= 3 And Target.Row <= 10 Then
        
        If Target.Column >= 2 And Target.Column <= 3 Then
            Cells(Target.Row, Target.Column + 3).Value = Target.Value
        End If
        
        If Target.Column >= 5 And Target.Column <= 6 Then
            Cells(Target.Row, Target.Column - 3).Value = Target.Value
        End If
        
    End If

    Application.EnableEvents = True
    
End Sub

こちらのコードについての詳しい解説は省略します。

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

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

指定した期間の営業日数を求める場合は、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をすべてチェック

・「全」を外す

---

▼サンプルファイル▼