メンバー

以下のように、数式で複数のシートに用意された表を１つのシートにまとめて表示する方法について解説していきます。

複数の表を縦方向に繋げる

複数の表を縦方向に繋げるには、VSTACK関数を活用します。
この関数の使い方は、以下になります。

=VSTACK(配列1, [配列2], [配列3], …)
// 配列：対象の表（必要な数分、カンマ区切りで指定する）

実際に、各シートの表を指定して表を繋げる場合、以下のような数式になります。

=VSTACK('2024年1月'!A2:C10,'2024年2月'!A2:C10,'2024年3月'!A2:C11,'2024年4月'!A2:C11,'2024年5月'!A2:C11)
// それぞれのシートの表を1つ1つ指定

VSTACK関数を使うことで、上記のように表を繋げることができました。
しかし、この数式のままですと、「シートの数が増えた時」や「表にデータが追加された時」に対応するのが大変になります。
そこで、複数のシートの表を一括で指定する数式に変更していきます。

複数の表を一括で指定する

複数のシートの範囲を一括で指定する場合、「=VSTACK(」と入力した後に、「対象の先頭のシート」を選択した後に、Shiftキーを押しながら、「対象の末尾のシート」を選択します。
この時、対象のシートの間に対象でないシートが含まれていると、その対象でないシート自体も参照されてしまいます。
対象の末尾のシートを選択することができましたら、アクティブになっているシートの表の範囲を大きめに指定します。
実際に上記の手順で指定すると、以下のような数式になります。

=VSTACK('2024年1月:2024年5月'!A2:C100)
// 今後データが追加されることを考慮して100行目まで指定

上記の数式を入力すると、空の範囲が「0」として抽出されてしまいます。
ただ、全シートの表を繋げることができました。

「0」を除外する方法については、後で解説していきます。

こちらで参照しているシートは、対象のシートの先頭から末尾のシートになります。
次から追加するシートに関しては、指定した先頭と末尾のシートの間に移動することで、数式を修正する必要がなく、まとめた表に加えることができるようになります。

そのため、先頭と末尾のシートに関しては「空の表」にし、シートの開始と終了が分かりやすいようなシート名にしておくと良いです。
実際に、修正した例が以下になります。

=VSTACK(S:G!A2:C100)

先頭と末尾のシートに関しては、以下のように空の表にしています。

以上の手順で、複数のシートの表を一括で指定し、抽出することができました。
ただ、空（「0」）のデータが表示されている状態では、見た目が良くないので、空（「0」）のデータを除外して抽出する数式に変更する必要があります。

空（「0」）のデータを除外する

抽出した表から、更に絞って抽出するには、FILTER関数を活用します。
この関数の使い方は、以下になります。

=FILTER(配列, 含む, [空の場合])
// 配列：抽出対象の表
// 含む：配列に指定した表から抽出する条件
// [空の場合]：条件を満たしたデータが存在しない場合に表示する値

先ほどの数式にFILTER関数を組み合わせると、以下のような数式になります。

=FILTER(VSTACK(S:G!A2:C100),VSTACK(S:G!A2:A100)<>"","")
// VSTACK(S:G!A2:C100)：抽出対象の表
// VSTACK(S:G!A2:A100)<>""：抽出条件
// → 抽出対象の範囲のA列の値（日付の項目）が空でない場合
// ""：データが存在しない場合は空を表示

上記のように、FILTER関数を組み合わせて抽出条件を加えることで、先頭列（日付の項目）が空のデータを除外して抽出することができます。
また、各シートの表へのデータの追加にも対応することができます。

以下の表に対し、「項目名（見出し）をクリックするだけで、その項目を基準に並べ替えを行う」という機能の開発方法について解説していきます。

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

開発準備

「特定のセルを選択すると同時に、何かしら処理を実行する」という仕組みは、該当するシートモジュールのイベントプロシージャを活用することで実現できます。

シートモジュールは、該当するシートのタブ上で右クリックし、[コードの表示]を選択することで表示することができます。

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

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

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

まずは、表示された「Worksheet_SelectionChange」というプロシージャ内にコードを記述していきますが、他のイベントプロシージャを表示したい場合は、右上のリストから表示することができます。

左上のリストで「Worksheet」を選択すると、右上のリストに関しては、自動で「SelectionChange」が選択され、「Worksheet_SelectionChange」というプロシージャが表示されます。

項目名をクリックするだけで昇順にする

では、項目名をクリックするだけで、その項目を基準に表を昇順にする機能を開発していきます。

まずは、以下のコードを「Worksheet_SelectionChange」というプロシージャ内に記述します。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Row = 2 And _
        Target.Column >= 2 And Target.Column <= 5 Then
        
        Dim rng As Range
        Set rng = Range("B2").CurrentRegion
        rng.Sort _
            Key1:=Target, _
            Order1:=xlAscending, _
            Header:=xlYes
        
    End If
    
End Sub

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

    If Target.Row = 2 And _
        Target.Column >= 2 And Target.Column <= 5 Then
        
        …
        
    End If

「Worksheet_SelectionChange」プロシージャの引数「Target」に、トリガーとなったセルの情報が渡されます。
そのTargetを活用し、そのTargetの情報が「行番号が2、尚且つ、列番号が2以上、5以下」であるかどうかを確認しています。
その条件を満たしている時、要するに、以下の項目名の範囲内が選択された時に、Ifの中の処理が実行されます。

        Dim rng As Range
        Set rng = Range("B2").CurrentRegion
        rng.Sort _
            Key1:=Target, _
            Order1:=xlAscending, _
            Header:=xlYes

こちらで「rng」というRange型の変数を用意し、その変数に対象の表の範囲を割り当てています。
「Range(“B2”).CurrentRegion」で、セルB2周囲に連続しているデータ範囲全体を指定しています。

その「rng」に対し「Sort」で並べ替えを行っています。
「Key1:=Target」でTargetの属する列を並べ替えの基準の列として指定し、「Order1:=xlAscending」で昇順を指定、「Header:=xlYes」でrngが項目名を含む範囲ということを指定しています。

以上の内容で実現できます。
該当する項目名を選択することで、以下のように並べ替えを行うことができます。

項目名を右クリックするだけで降順にする

では次に、項目名を右クリックすることで、その項目を基準に表を降順にする機能を開発していきます。

先ほどと同様に、シートモジュール上にコードを記述していきます。
ただ、先ほどとは別のイベントプロシージャを使用する必要があります。

そのプロシージャとは、該当するシート上のいずれかのセルの上で右クリックした時に処理が実行されるというイベントプロシージャです。
シートモジュールの右上のリストから「BeforeRightClick」を選択することで表示することができます。

表示された「Worksheet_BeforeRightClick」というプロシージャ内にコードを記述していきます。

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

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    
    If Target.Row = 2 And _
        Target.Column >= 2 And Target.Column <= 5 Then
        
        Cancel = True
        Dim rng As Range
        Set rng = Range("B2").CurrentRegion
        rng.Sort _
            Key1:=Target, _
            Order1:=xlDescending, _
            Header:=xlYes
        
    End If
    
End Sub

コードの内容は、先ほどの内容とほぼ同じになります。
そのため、異なる点のみを解説します。

        Cancel = True

「Cancel」に対し、Trueを指定することで、右クリック時にメニューが表示されないようにすることができます。

        rng.Sort _
            Key1:=Target, _
            Order1:=xlDescending, _
            Header:=xlYes

先ほどの内容とは異なり、こちらでは「Order1:=xlDescending」として降順にしています。

以上の内容で実現できます。
該当する項目名の上で右クリックすることで、以下のように並べ替えを行うことができます。

▼サンプルファイル▼

以下のような表で、空白行を除外して上に詰める場合、並べ替え機能を活用すると、書式の設定位置や要素の順番に関しても変わってしまいます。

今回は、書式の設定位置や要素の順番を変えずに、そのままの状態で上詰めする機能をVBAで開発する方法について解説していきます。
※こちらで開発したファイルは、記事に最後にて配布しています。

開発

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

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

[作成]を選択すると表示される以下のエディタ画面の「Sub 上詰め()」から「End Sub」の間にコードを記述していきます。
「Option Explicit」は、エディタの設定次第では表示されません。

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

Sub 上詰め()

    Dim inData As Variant, outData As Variant
    inData = Range("B3:B11").Value
    ReDim outData(1 To UBound(inData), 1 To 1) 
    
    Dim d As Variant, i As Long: i = 1
    For Each d In inData
        If d <> "" Then 
            outData(i, 1) = d
            i = i + 1
        End If
    Next d
    
    Range("B3:B11").Value = outData
    
End Sub

解説

このコードについて、簡単に解説していきます。

    Dim inData As Variant, outData As Variant
    inData = Range("B3:B11").Value

ここで、inDataとoutDataという変数を用意し、inDataには上詰めする対象のセル範囲B3～B11の値のみを格納しています。
具体的には、inDataにセルB3～B11の値を１列の２次元配列として取得しています。

    ReDim outData(1 To UBound(inData), 1 To 1) 

outDataに関しては、inDataと同じ行数、列数の２次元配列になるように変換しています。
inDataのようにセルの範囲を格納すると、添字が１からの２次元配列になるため、outDataに関しても同様に、添字を１からとした２次元配列を用意しています。
行に関しては、UBoundを用いてinDataの行数を取得し、列に関しては、１列のみの範囲になるため、「1 To 1」と１列のみを用意しています。

    Dim d As Variant, i As Long: i = 1
    For Each d In inData
        If d <> "" Then 
            outData(i, 1) = d
            i = i + 1
        End If
    Next d

次に、必要な変数を用意し、For EachでinDataの値を先頭から１つずつ繰り返してdに渡しています。
この繰り返し処理の中で、dの値が空でない場合に、outDataのi行目に加えていくようにしています。
この「i」に関しては、初期値は１で、outDataに加えられた後に、「i」自身に１を加えています。
これにより、outDataには、inDataの空の値を除外した値のみが上詰めで格納されます。

    Range("B3:B11").Value = outData

最後に、outDataの値を対象の表に貼り付けています。
outDataの配列の要素で、値が格納されなかった要素に関しては、初期値として空が渡されます。

ボタン作成

後は、開発した機能をボタンに割り当てることで完成です。
ボタンに関しては、[開発]タブの中の[挿入]から作成することができます。

[挿入]から[フォームコントロール]の中の[ボタン]を選択し、図形を作成する時と同様に、好みの位置にボタンを作成します。
ボタンを作成すると、[マクロの登録]という画面が表示されるため、開発した機能の名前（上詰め）を選択して確定します。

これだけで、開発した機能を割り当てたボタンを作成することができます。
必要に応じて、ボタンの名前を変更すると良いです。
※一度作成したボタンを選択すると、機能が実行されてしまいます。作成後に編集する際は、右クリック、もしくはCtrlキーを押しながらクリックすることで編集することができます。

▼サンプルファイル▼

以下のように、マトリックス表からリスト形式の表に、数式を使わずに変換する方法について解説していきます。

実は、ピボットテーブルを活用することで、簡単にリスト形式の表に変換することができます。
ただ、マトリックス表からピボットテーブルを作成する場合は、[挿入]タブの[ピボットテーブル]から作成するのではなく、少し特殊な方法になります。

その方法とは、「ピボットテーブル/ピボットグラフ ウィザード」という機能を活用するという方法です。

ピボットテーブルの作成

「ピボットテーブル/ピボットグラフ ウィザード」という機能を立ち上げるには、Altキー、Dキー、Pキーと順番にキーを入力します。
キーを順番に入力すると、以下のような画面が表示されます。

こちらの画面にて、[複数のワークシート範囲]と[ピボットテーブル]を選択し、[次へ]を選択します。

次に表示された画面にて、[指定]を選択し、[次へ]を選択します。

以下の画面が表示されましたら、[範囲]に対象のマトリックス表の範囲を指定し、[次へ]を選択します。

最後に、以下の画面にて[新規ワークシート]を選択して[完了]を選択することで、マトリックス表からピボットテーブルを作成することができます。

以下のように、マトリックス表の横軸の「色」に関しても、[列]という１つのフィールドとして作成されていることが確認できます。

リスト形式の表の作成

先ほどの手順で作成したピボットテーブルからリスト形式の表を作成します。

作成方法は簡単です。
ピボットテーブルの右下の総計セルをダブルクリックするだけで、瞬時に、別シートにリスト形式の表を作成することができます。

ダブルクリックすると、新しいシートとして、以下のようなリスト形式に変換された表が表示されます。

こちらの表は「テーブル」になっています。
必要に応じて、項目名を変更したり、テーブルを標準の範囲に戻したりとカスタマイズしてください。

テーブルを標準の範囲に戻す際は、対象のテーブルを選択し、[テーブルデザイン]タブから[範囲に変換]を選択することで、戻すことができます。

テーブルのまま活用される場合は、テーブル名を好みの名前に変更すると良いです。
テーブル名は、[テーブルデザイン]タブから変更できます。

以上の手順によって、以下のように、マトリックス表からリスト形式の表に変換することができます。

以下のように、軸の異なる表を数式で作成する方法について２通り解説していきます。

こちらでは、「担当（A、B、C）と曜日別に担当者（田中、斎藤、井上、村上）を表した表」を元に、「担当者と曜日別に担当を表した表」に数式で抽出してきます。

方法１

１つ目の方法は、INDEX関数とMATCH関数を組み合わせた抽出方法になります。

まずは、以下のように、抽出先の表の見出しのみを用意します。

では、抽出先の表の左上のセルC8から該当する担当を抽出していきます。

抽出方法は、該当する担当者（田中）が存在するのかどうかを判断し、存在する場合は、該当する曜日（月）の上から何番目に位置するのかを取得し、その行の担当（C）を抽出するという方法になります。

何番目に位置するのかを取得するには、MATCH関数を活用します。
この関数の使い方は、以下になります。

=MATCH(検査値, 検査範囲, [照合の種類])
// 検査値：検索対象の文字
// 検査範囲：検索対象の範囲
// [照合の種類]：検索方法（0：完全一致、1：以下、-1：以上）
// ※[照合の種類]を省略した場合は、「1：以下」が指定される

この関数を用いて、セルC8に対し、該当する担当者（田中）が同じ曜日（月）に存在する位置情報（３）を取得します。
取得する際の数式は、以下になります。

=MATCH($B8,C$3:C$5,0)
// $B8：検査する担当者のセル（数式を他の列にコピーした際にも位置が移動しないように、B列のみを「$」で固定）
// C$3:C$5：検査範囲（数式を他の行にコピーした際にも位置が移動しないように、3と5の行番号のみを「$」で固定）
// 0：検査値が検査範囲で完全一致する位置の情報を取得

これだけで、以下のように該当する担当者が存在する場合は、位置情報を取得することができます。

存在しない場合は、「#N/A」というエラーになりますが、エラー対策に関しては、後に解説します。

次に、該当する位置に存在する担当（A、B、C）を抽出していきます。
指定した範囲の○番目のデータを抽出する際は、INDEX関数を活用します。
この関数の使い方は、以下になります。

=INDEX(配列, 行番号, [列番号])
// 配列：対象の範囲
// 行番号：対象の範囲の何行目のデータを抽出するのかを指定
// [列番号]：対象の範囲の何列目のデータを抽出するのかを指定
// ※配列に指定した範囲が1列の場合は[列番号]を省略できる

実際に先ほどのMATCH関数に、INDEX関数を以下のように組み合わせます。

=INDEX($B$3:$B$5,MATCH($B8,C$3:C$5,0))
// $B$3:$B$5：抽出対象の範囲（他のセルにコピーした際にも位置が移動しないように「$」で固定）
// MATCH($B8,C$3:C$5,0)：抽出対象の範囲から抽出する位置情報（行番号）

これだけで、以下のように該当する担当を抽出することができます。

ただ、この数式ですと、他のセルにコピーした際に該当する担当が存在しない場合に、以下のようにエラーになってしまいます。

そのため、先ほどの数式にエラー対策を行います。
エラーになった場合に何も表示しないという設定を行う場合は、IFERROR関数を組み合わせます。
この関数の使い方は、以下になります。

=IFERROR(値, エラーの場合の値)
// 値：エラーを検証する値
// エラーの場合の値：値がエラーの時に表示する値

実際に先ほどの数式に組み合わせると、以下のようになります。

=IFERROR(INDEX($B$3:$B$5,MATCH($B8,C$3:C$5,0)),"")
// INDEX(…)：エラーを検証する値
// ""：エラーの時に表示する値（空）

この数式を他のセルにコピーすることで、以下のように、全ての担当を抽出することができます。

方法２（Excel2021以降対応）

２つ目の方法は、１つ目に比べて短い数式で抽出することができます。
ただ、こちらの方法は、Excelのバージョンが2021以降、もしくは365でないと対応していません。

では、その方法について解説していきます。

その方法とは、XLOOKUP関数を活用するという方法です。
この関数の使い方は、以下になります。

=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
// 検索値：検索対象の値
// 検索範囲：検索して位置情報を取得する範囲
// 戻り範囲：取得した位置情報から抽出する範囲
// [見つからない場合]：検索結果が存在しない場合に表示する値 ※省略時は「#N/A」になる
// [一致モード]：検索方法（0：完全一致、-1：以下、1：以上、2：ワイルドカード文字との一致）※省略時は「0：完全一致」が指定される
// [検索モード]：検索順序（1：先頭から末尾へ検索、-1：末尾から先頭へ検索、2：バイナリ検索（昇順で並べ替え）、-2：バイナリ検索（降順で並べ替え））※省略時は「1：先頭から末尾へ検索」が指定される

実際に抽出先の表の左上のセルC8に、XLOOKUP関数を用いた数式を入力していきます。
入力する数式は、以下になります。

=XLOOKUP($B8,C$3:C$5,$B$3:$B$5,"")
// $B8：検索する担当者のセル（数式を他の列にコピーした際にも位置が移動しないように、B列のみを「$」で固定）
// C$3:C$5：検索する担当者の範囲（数式を他の行にコピーした際にも位置が移動しないように、3と5の行番号のみを「$」で固定）
// $B$3:$B$5：抽出対象の範囲（他のセルにコピーした際にも位置が移動しないように「$」で固定）
// ""：検索結果が存在しない場合に表示する値（空）

この数式を他のセルにコピーすることで、以下のように、全ての担当を抽出することができます。

ちなみに、XLOOKUP関数が使える環境では、スピルという機能が使えます。
スピルとは、数式を入力したセルから溢れて結果を表示するという機能です。

今回の数式の場合は、以下のように、検索値の範囲を複数行指定するだけで、１列分表示することができます。

=XLOOKUP($B8:$B11,C$3:C$5,$B$3:$B$5,"")

これだけで、以下のように、該当する担当を１列分抽出することができます。

そのため、他のセルにコピーする際は、行方向にコピーするだけで全てを抽出することができます。

表を作成する際などに動作確認するため、以下のような適当な数値データを手入力している方いませんか？
こちらでは、適当な数値データを瞬時に入力する方法について解説していきます。

乱数の入力

指定した範囲内の乱数を入力する場合は、『RADNBETWEEN関数』が便利です。
この関数の使い方は、以下になります。

=RANDBETWEEN(最小値, 最大値)
// 最小値：乱数を発生させる際の最小値の整数
// 最大値：乱数を発生させる際の最大値の整数

実際に、以下の表に対し、０～１００の乱数を入力していきます。

まずは、一括で入力したい対象のセルをまとめて選択し、以下の数式を入力します。

=RANDBETWEEN(0,100)

次に、Ctrlキーを押しながらEnterキーを押して確定します。
これだけで以下のように、選択範囲内全てにRANDBETWEEN関数を入力することができます。

数式を結果の値のみに変換

ただ、この状態ですと、セルを編集する度、乱数が更新されてしまいます。

そのため、数式の状態から値に変換する必要があります。
その際は、RANDBETWEEN関数を入力したセルを選択している状態で、コピーして値のみを貼り付けます。

コピーと値のみの貼り付けは、右クリックのメニューからでも行えますが、以下のショートカットを活用した方が効率的です。

・コピー：Ctrl + C
・値のみ貼り付け：Ctrl + Shift + V
　※「Ctrl + Shift + V」が使えない場合は以下でも行えます。
　　Ctrl + Alt + V → V → Enter

以下のように値のみを貼り付けることによって、数値が更新されることがなくなります。

応用

小数を含む乱数を発生させたい場合は、以下のようにRANDBETWEEN関数に加え、好みの数値で割ると良いです。

・10.0~30.0の範囲
=RANDBETWEEN(100,300)/10

・0.00~100.00の範囲
=RANDBETWEEN(0,10000)/100

他にも、発生した乱数に特定の数値を加えたい場合は、加算貼り付けをすると良いです。
例えば以下の表の「鈴木 次郎」さんの点数に対し、全体に１０点を加えたい場合は、以下の手順を行います。

①適当なセルに「10」と入力
②入力したセルをコピー
③加えたい範囲を選択し右クリック
④[形式を選択して貼り付け]から[貼り付け]を[値]に、[演算]を[加算]にして確定
⑤手順①で入力した値を削除

以下のカレンダーのように、「休」を自動で表示する方法について解説していきます。
反映される「休」は、固定の曜日と休日一覧に登録した日付になります。
※以下の例では土日を固定の休みとしています。

カレンダーに「休」を表示する

固定の曜日の休みと休日一覧に登録した日付を考慮して「休」と表示させる場合は、『NETWORKDAYS.INTL関数』が便利です。

この関数では、対象期間の営業日数を求めることができます。

例えば、2024/7/5が休みはどうかを判定したい場合、NETWORKDAYS.INTL関数を用いて2024/7/5から2024/7/5の期間（１日間）の営業日数を求めます。
その日数が０の場合が休みになります。

まずは、カレンダーに各日付（１日間）の営業日数を表示させます。
NETWORKDAYS.INTL関数の使い方は、以下になります。

=NETWORKDAYS.INTL(開始日, 終了日, [週末], [祭日])
// 開始日：対象期間の開始日
// 終了日：対象期間の終了日
// [週末]：固定の休みとする曜日を指定（省略時は土日休み）
// [祭日]：固定以外の休日をまとめた一覧を指定（省略時は対象なし）

引数の[週末]に関しては、以下の候補から簡単に指定することができます。

実際に、以下のカレンダーの先頭（セルC3）にNETWORKDAYS.INTL関数を用いて営業日数を求めていきます。
その際の数式は以下になります。

=NETWORKDAYS.INTL(B3,B3,1,$F$3:$F$20)
// 固定の休みを土日とする場合は、引数の[週末]に1を指定する
// 他のセル（C列全体）にコピーした際に休日一覧の参照位置が移動しないように、引数の[祭日]に関しては絶対参照（$）にする

入力できたら該当する列（C列）の最終行までコピーします。
この時、「0」が表示された日付が休みになります。

後は、「0」が表示されたセルを「休」という文字に、それ以外のセルを空にすることで完成です。
条件に応じて表示させる内容を変える場合は、『IF関数』を活用します。
この関数の使い方は、以下になります。

=IF(論理式, 値が真の場合, 値が偽の場合)
// 論理式：条件を指定
// 値が真の場合：条件を満たした場合に表示する値を指定
// 値が偽の場合：条件を満たしていない場合に表示する値を指定

実際に、先ほどのNETWORKDAYS.INTL関数と組み合わせると、以下のようになります。

=IF(NETWORKDAYS.INTL(B3,B3,1,$F$3:$F$20)=0,"休","")

入力できたら該当する列（C列）の最終行までコピーします。
これだけで、「休」という文字を表示させることができます。

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

次は、以下の候補（[週末]に指定する曜日）の中に理想的な曜日の組み合わせが存在しない場合の指定方法について解説していきます。

この候補の中だと、「土日、日月、月火、…」のように２日間連続した曜日、もしくは「日、月、火、…」のように単体の曜日でしか指定することができません。
そのため、「月水」や「金土日」のような曜日を固定の休みとする場合は、別の方法で指定する必要があります。
そのような際は、以下のように指定します。

・月水の場合
=NETWORKDAYS.INTL(開始日, 終了日, ”1010000”, [祭日])
・金土日の場合
=NETWORKDAYS.INTL(開始日, 終了日, ”0000111”, [祭日])
// 月曜始まりで休みの曜日を「1」として、数字を7文字並べて指定します

試しに、月水を休みとしてカレンダーに反映させてみます。
反映させる場合の数式は、以下になります。

=IF(NETWORKDAYS.INTL(B3,B3,"1010000",$F$3:$F$20)=0,"休","")

※曜日の確認がしやすいように曜日をA列に加えています。

こちらを最終行までコピーすると以下のようになります。

※休日一覧が月水と被っていたため、休日一覧に2024/7/16を追加しています。

補足

NETWORKDAYS.INTL関数と似た関数でNETWORKDAYS関数があります。
NETWORKDAYS関数では土日以外を指定することができないので、NETWORKDAYS.INTL関数の方を覚えておくと良いです。

// 引数の違い
=NETWORKDAYS.INTL(開始日, 終了日, [週末], [祭日])
=NETWORKDAYS(開始日, 終了日, [祭日])

以下のシートのセルA2に入力されているステータスに応じて、シート見出しの色を自動で変更する仕組みについて解説していきます。

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

1. 開発準備

今回の機能は、各シートのセルA2のステータスの値に応じて、シート見出しの色が変換するという内容です。
セルA2にステータスが入力されているシートに関して、全てに適用する場合は、ブックモジュールを活用します。

ブックモジュールは、[開発]タブの中の[Visual Basic]を選択して、プロジェクトウィンドウから[ThisWorkbook]を選択することで、表示することができます。

表示されたブックモジュールにて、上のリストから[Workbook]を選択し、隣のリストから[SheetChange]を選択します。

表示された[Workbook_SheetChange]というプロシージャのみを活用します。
他のプロシージャに関しては、削除しても問題ございません。

[Workbook_SheetChange]というプロシージャが、各シートのセルが編集された時に実行されるイベントプロシージャになります。
このプロシージャの引数「Sh」に実行のきっかけとなったシートの情報、引数「Target」に実行のきっかけとなったセルの情報が渡されます。

2. コードの記述

これらの情報を活用して、以下のように処理を記述します。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    If Sh.Range("A1").Value = "ステータス" Then
        If Sh.Range("A2").Value = "確認待ち" Then
            Sh.Tab.Color = RGB(255, 255, 0)
        ElseIf Sh.Range("A2").Value = "修正待ち" Then
            Sh.Tab.Color = RGB(255, 165, 0)
        ElseIf Sh.Range("A2").Value = "完了" Then
            Sh.Tab.Color = RGB(180, 180, 180)
        Else
            Sh.Tab.ColorIndex = xlColorIndexNone
        End If
    End If
    
End Sub

では、このコードの処理の内容を解説します。

If Sh.Range("A1").Value = "ステータス" Then

初めに、対象のシートのセルA1に”ステータス”という文字が入力されているかどうかを確認します。
”ステータス”という文字が入力されている場合に関してのみ、内側の以下の処理を実行します。

If Sh.Range("A2").Value = "確認待ち" Then
    Sh.Tab.Color = RGB(255, 255, 0)
ElseIf Sh.Range("A2").Value = "修正待ち" Then
    Sh.Tab.Color = RGB(255, 165, 0)
ElseIf Sh.Range("A2").Value = "完了" Then
    Sh.Tab.Color = RGB(180, 180, 180)
Else
    Sh.Tab.ColorIndex = xlColorIndexNone
End If

ここで、セルA2の値が”確認待ち”なのか、”修正待ち”なのか、”完了”なのか、それ以外なのかを判断し、それぞれの条件に合ったシート見出しの色を設定しています。

Sh.Tab.Color = RGB(255, 255, 0)

”確認待ち”の場合は、上記の内容でシート見出しの色を黄色にしています。
色はRGBで指定しています。
他、”修正待ち”の場合はオレンジ色、”完了”の場合は灰色にしています。

Sh.Tab.ColorIndex = xlColorIndexNone

どれにも該当するステータスがない場合は、上記の内容でシート見出しの色をリセット（色なし）しています。
リセットする場合は、ColorIndexを使用し、xlColorIndexNoneと指定します。

3. 完成

このコードを記述することで、以下のように、セルA2のステータスに応じて、該当するシート見出しの色が自動で変更されます。

▼サンプルファイル▼

この記事では、特定のデータを選択して「抽出」ボタンを押すだけで、関連データを別シートに自動で抽出する機能の開発方法について解説しています。
解説には、以下の表を活用します。

（例）「男」を選択して「抽出」ボタンを押す → 「男」シートが作成され「男」のデータのみが抽出される

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

1. 開発準備

まずは、[開発]タブの中の[マクロ]を選択します。

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

入力後、[作成]ボタンを押します。

以下の画面が表示されましたら、エディタ内のSub～End Subの中にプログラムを記述していきます。
※Option Explicitはエディタの設定内容によっては表示されません。

2. コードの記述

Sub～End Subの中に記述する内容は、以下になります。

Sub 抽出()
    
    Dim t As Range
    Set t = ActiveCell
    
    t.AutoFilter Field:=t.Column - 1, Criteria1:=t.Value
    Columns("A:E").Copy
    
    With Worksheets.Add
        .Range("A1").PasteSpecial Paste:=xlPasteFormats
        .Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
        .Range("A1").PasteSpecial Paste:=xlPasteValues
        .Name = t.Value
        .Range("A1").Select
    End With
    
    t.AutoFilter
    
End Sub

先頭から順番に解説していきます。

Dim t As Range
Set t = ActiveCell

こちらでは、tというセルの情報を格納する用の変数（入れ物）を用意し、アクティブセル（選択基準のセル）の情報を格納しています。

t.AutoFilter Field:=t.Column - 1, Criteria1:=t.Value

こちらで、先ほど定義したtのセルを基準にフィルター機能で絞り込みを行っています。
絞り込みの内容は、tの列番号から１を引いた位置の列（表がB列から始まっているため、実際の項目の位置情報と列番号は異なる）に対し、tのセルの値と一致するデータになります。

以下の場合、セルD5が選択されているため、D列の列番号の４から１を引いた３番目の項目（性別）に、セルD5の値（男）が入力されているデータのみで絞り込みを行います。

Columns("A:E").Copy

そして、絞り込みをした表を含むA～E列の範囲をコピーしています。

With Worksheets.Add
    .Range("A1").PasteSpecial Paste:=xlPasteFormats
    .Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
    .Range("A1").PasteSpecial Paste:=xlPasteValues
    .Name = t.Value
    .Range("A1").Select
End With

こちらで、新しいシートを追加し、コピーしたデータ（書式、列幅、値）を貼り付けています。
貼り付けた後に、シート名をtのセルの値にし、そのシートのセルA1を選択するという処理です。

t.AutoFilter

最後に、絞り込みをした表の絞り込みを解除して、処理が終了します。

3. ボタンの作成

後は、開発した機能をボタンに割り当てます。
ボタンは、[開発]タブの中の[挿入]から[ボタン]を選択することで作成することができます。

図形の作成時と同様に、好みの位置にボタンを作成することができます。
ボタンを作成すると[マクロの登録]という画面が表示されるため、開発したマクロ（こちらの場合は「抽出」）を選択して確定します。

必要に応じて、ボタンの表示名を変更することで、ボタンの完成です。
※一度作成したボタンを選択すると、割り当てた処理が実行されてしまいます。
作成したボタンを編集する際は、右クリック、もしくは、Ctrlキーを押しながら選択します。

4. 完成

以上の手順で完成です。

好みのデータを選択してから「抽出」ボタンを押すことで、選択したデータに関連するデータを別シートに抽出することができます。

こちらで開発したExcelファイルは、以下になります。

▼サンプルファイル▼

FILTER関数を活用することで表から指定した条件を満たすデータのみを抽出することができます。また、該当するデータが１件も存在しなかった場合には、予め指定した値を表示することができます。

=FILTER(B:D,B:B=F3,"なし")

今回は、該当するデータが１件も存在しなかった場合に表示する値を、以下のような好みのデータにする方法について３通りで解説していきます。

方法１

１つ目は、HSTACK関数を活用する方法です。

HSTACK関数では、指定した範囲を横方向に結合することができます。
この関数の使い方は、以下になります。

=HSTACK(配列1, [配列2, 配列3, …])
// 配列：対象の範囲（配列2以降は省略可能）

こちらの関数を活用して、好みのデータを作成します。
例えば、『日付の項目に「検索した日付（セルF3）」、商品の項目に「-」、金額の項目に「0」』を表示させる場合、以下のような数式になります。

=HSTACK(F3,"-",0)

この内容をFILTER関数の[空の場合]に指定することで、好みのデータを表示することができます。

=FILTER(B:D,B:B=F3,HSTACK(F3,"-",0))

ただ、HSTACK関数は現状（２０２４/８時点）３６５のみにしか対応していません。
FILTER関数は２０２１から使うことができますが、HSTACK関数を使うことができないため、他の方法についても解説していきます。

方法２

２つ目は、関数を使わずに好みのデータを表示する方法です。

この方法では、他のセルの値を参照して表示することはできませんが、固定のデータの場合は便利な方法になります。

固定のデータを、関数を使わずに表示する場合は、「{}」を活用します。
「{}」の中に必要な項目数分の値を「,」区切りで指定することによって複数列のデータを表現することができます。
例えば、『日付の項目に「-」、商品の項目に「-」、金額の項目に「0」』を表示させる場合、以下のような数式になります。

={"-","-",0}

この内容をFILTER関数の[空の場合]に指定することで、好みのデータを表示することができます。

=FILTER(B:D,B:B=F3,{"-","-",0})

方法３

３つ目は、CHOOSE関数を活用する方法です。

CHOOSE関数では、指定した複数の値の中から指定した番号の位置の値を表示することができます。
この関数の使い方は、以下になります。

=CHOOSE(インデックス, 値1, [値2, 値3, 値4, …])
// インデックス：表示する番号
// 値n：インデックスがnの場合に表示する値（値2以降は省略可能）

活用例は以下になります。

=CHOOSE(2,B2,C2,D2)

実は、この関数のインデックスに、方法２で解説した「{}」を活用して複数の番号を指定することで複数の列に該当する値を表示することができます。
例えば、『日付の項目に「検索した日付（セルF3）」、商品の項目に「-」、金額の項目に「0」』を表示させる場合、以下のような数式になります。

=CHOOSE({1,2,3},F3,"-",0)
// {1,2,3}：1番目、2番目、3番目の順番で列方向へ表示する

この内容をFILTER関数の[空の場合]に指定することで、好みのデータを表示することができます。

=FILTER(B:D,B:B=F3,CHOOSE({1,2,3},F3,"-",0))

まとめ

Excelが２０２１の場合は、方法２や方法３に関しても覚えておくと良いかと思います。
また、固定のデータを表示する場合は、方法２を活用することで数式を短く表現することができるため、HSTACK関数が対応していたとしても方法２を活用するのが良いかと思います。

セルに入力された基準日から「年・月・曜日・月末」などを求める際に活用する関数と活用例について解説していきます。

年月日をそれぞれ求める

年、月、日を日付から求めるには、以下の関数を活用します。

・年：YEAR関数
・月：MONTH関数
・日：DAY関数

活用例は以下になります。

=YEAR(B2) // 年
=MONTH(B2) // 月
=DAY(B2) // 日

曜日を求める

曜日を日付から求めるには、TEXT関数が便利です。
TEXT関数は指定した値に指定した表示形式を適用した文字列を返す関数です。

曜日を表示する表示形式には、以下のようなものがあります。

・aaa：漢字（１文字） 月
・aaaa：漢字（３文字） 月曜日
・ddd：英語（３文字） Mon
・dddd：英語 Monday

漢字（３文字）で抽出した活用例は以下になります。

=TEXT(B2,"aaa")

月末を求める

月末を日付から求めるには、EOMONTH関数が便利です。
EOMONTH関数は指定した日付を基準にNか月後の月末を求めることができます。
※Nは任意の数値

活用例は以下になります。

=EOMONTH(B2,-1) // 1か月前
=EOMONTH(B2,0) // 当月
=EOMONTH(B2,1) // 1か月後

こちらの方法の場合は、日付から月末を求めていますが、年と月のみから月末を求める場合は他の方法になります。
その方法については、最後に解説します。

１日を求める

１日を日付から求めるには、先ほどの同じEOMONTH関数が活用できます。
EOMOTH関数は月末を求める関数ですが、指定した月の１日を求める際にも活用できます。

１日という日付は、見方を変えると先月末の１日後とも言えます。
そのため、求めたい月の先月末をEOMONTH関数で求め、その日付に１を加えることで求めることができます。

求めた例が以下になります。

=EOMONTH(B2,-2)+1 // 1か月前
=EOMONTH(B2,-1)+1 // 当月
=EOMONTH(B2,0)+1 // 1か月後

こちらの方法の場合は、日付から１日を求めていますが、年と月のみから１日の日付を求める場合は他の方法になります。

年と月のみから１日と月末を求める

年と月のみから日付を求める場合は、DATE関数を活用します。
DATE関数は、年・月・日を別々で指定することで、その年・月・日が指す日付を求める関数です。

１日の日付を求める場合は日のみを直接「1」と指定することで求めることができ、月末を求める場合は翌月の１日の１日前ということで、月に「月+1」、日に「0（１日の１日前という意味）」と指定することで求めることができます。
※DATE関数は年月の桁上がり桁下がりにも対応しています。

求めた例が以下になります。

【１日】
=DATE(B1,B2-1,1) // 1か月前
=DATE(B1,B2,1) // 当月
=DATE(B1,B2+1,1) // 1か月後

【月末】
=DATE(B1,B2,0) // 1か月前
=DATE(B1,B2+1,0) // 当月
=DATE(B1,B2+2,0) // 1か月後

まとめ

以下の６つの関数を活用して、基準日から他の値を求める方法について解説しました。

・YEAR関数
・MONTH関数
・DAY関数
・TEXT関数
・EOMONTH関数
・DATE関数

６つの関数ではありますが、それぞれの関数を応用して活用することで、６つ以上の多様な値を求めることができます。
ぜひ色んな使い方を試し、新たな発見を楽しんでみてください。