作者別: it-yobi

以下の表のように、チェックボックスにチェックすると同時に、その行が自動で非表示になる仕組みを実現する方法について解説していきます。

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

今回の仕組みでは、チェックすると同時に自動でフィルター機能が実行され絞り込みされます。
そのため、フィルター機能を解除することで、簡単に再表示することもできます。

1. 開発準備

今回は、以下の表をもとに作成していきます。
あらかじめ、「済」の項目にチェックボックスを用意しています。

チェックボックスが用意されているセルには、TRUE（チェックされている）/FALSE（チェックされていない）の値が入力されます。
今回は、用意したチェックボックスをチェックすると同時に、フィルター機能による絞り込み（「済」が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)
    
    If Target.CountLarge >= 100 Then Exit Sub
    
    Dim t As Range
    For Each t In Target
        If t.Row >= 3 And t.Row <= 15 And _
            t.Column = 2 Then
            
            Range("B2").AutoFilter 1, False
            Exit Sub
            
        End If
    Next t
    
End Sub

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

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.CountLarge >= 100 Then Exit Sub
    
    Dim t As Range
    For Each t In Target
        '省略
    Next t
    
End Sub

「Worksheet_Change」というプロシージャの引数「Target」に、編集されたセルの情報が渡されます。
複数セルを同時に編集した場合は、複数セルの情報が渡されます。

こちらでは、編集された１つ１つのセルに対して処理を実行します。
そのため編集されたセルが多い場合は処理が重たくなってしまうため、１００セル以上が同時に編集された場合は処理を終了しています。

次に、編集された１つ１つのセルの情報を受け取る用の「t」という入れ物（変数）を用意し、その「t」にセルの情報を１つ１つ渡して、繰り返して実行しています。

        If t.Row >= 3 And t.Row <= 15 And _
            t.Column = 2 Then
            
            Range("B2").AutoFilter 1, False
            Exit Sub
            
        End If

繰り返し処理の中で、対象のセルの行番号が３以上、１５未満、列番号が２のとき、要するに、チェックボックスの範囲内が編集されたときに、チェックされていない行のみをフィルター機能で絞り込みしています。

１回絞り込みを行えば、繰り返し行う必要はないので、処理を終了しています。

3. 完成

以上の内容で実現できます。
チェックボックスにチェックするだけで、その行がフィルター機能により非表示になります。

▼サンプルファイル▼

「商品を選んだら別の表に追加し、同じ商品なら数量を加算」
この処理を右クリックだけで実現する方法について解説していきます。

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

1. 開発準備

今回は、以下の表（商品一覧とカート）を元に作成していきます。

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

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

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

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

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

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

2. コードの記述

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

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim t As Range: Set t = Target
    
    If t.Row >= 4 And _
        t.Column >= 2 And t.Column <= 4 And _
        t.CountLarge = 1 And _
        Cells(t.Row, "B").Value <> "" Then
        
        Cancel = True
        
        Dim lRow As Long, r As Long, f As Boolean
        lRow = Cells(Rows.Count, "F").End(xlUp).Row
        f = False
        
        For r = 4 To lRow
            If Cells(r, "F").Value = Cells(t.Row, "B").Value Then
                f = True
                Cells(r, "H").Value = Cells(r, "H").Value + 1
            End If
        Next r
        
        If Not f Then
            Cells(lRow + 1, "F").Value = Cells(t.Row, "B").Value
            Cells(lRow + 1, "G").Value = Cells(t.Row, "C").Value
            Cells(lRow + 1, "H").Value = 1
        End If
        
    End If
    
End Sub

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

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim t As Range: Set t = Target
    
    If t.Row >= 4 And _
        t.Column >= 2 And t.Column <= 4 And _
        t.CountLarge = 1 And _
        Cells(t.Row, "B").Value <> "" Then
        
        '省略
        
    End If
    
End Sub

「Worksheet_BeforeRightClick」というプロシージャの引数「Target」に、右クリックされたセルの情報が渡されます。
複数範囲を選択している範囲の上で右クリックした場合は、複数セルの情報が渡されます。

次に、そのセルの情報を「t」という名前の入れ物（変数）に格納し、その「t」を活用して、対象の範囲上で右クリックされたかどうかを確認します。

対象の範囲とは、以下の条件を満たす範囲です。

・行番号が４以上 ・列番号が２以上４以下 ・単体のセル ・そのセルと同じ行のＢ列が空でない

要するに、商品一覧の表の中かつ商品が入力されている行の上で、単体のセルが右クリックされた場合に処理を実行するようにしています。

        Cancel = True
        
        Dim lRow As Long, r As Long, f As Boolean
        lRow = Cells(Rows.Count, "F").End(xlUp).Row
        f = False

対象の範囲内で、単体のセルが右クリックされた場合に、右クリック時のメニュー表示を無効にし、「lRow」と「r」という数値専用（Long）の入れ物（変数）、「f」という論理値専用（Boolean：True / Falseのみ）の入れ物（変数）を用意しています。

「lRow」には、カートの表の最終行の行番号を格納しています。
最終行の行番号は以下のように取得しています。

F列の末尾のセルを選択し、「Ctrl+↑」で移動した先のセルの行番号

そのため、カートの表にデータが１件も存在しない場合は、見出しの行である「３」が取得されます。

「r」には何も格納せず、「f」には「False」を格納しています。

        For r = 4 To lRow
            If Cells(r, "F").Value = Cells(t.Row, "B").Value Then
                f = True
                Cells(r, "H").Value = Cells(r, "H").Value + 1
            End If
        Next r

カートの４行目から最終行（lRow）まで繰り返し、右クリックされた商品（B列のID）と一致する商品（F列のID）を探します。
一致する商品が見つかった場合は、「f」を「True」にして、その見つかったカートの行のH列の数量に「１」を加えています。

        If Not f Then
            Cells(lRow + 1, "F").Value = Cells(t.Row, "B").Value
            Cells(lRow + 1, "G").Value = Cells(t.Row, "C").Value
            Cells(lRow + 1, "H").Value = 1
        End If

「f」が「False」の場合、要するに、先ほどの繰り返しで一致する商品が見つからなかった場合に、カートの末尾（lRow + 1）のF列に右クリックされた商品のID（B列）、G列に商品名（C列）、H列に「１」を入力しています。

3. 完成

以上の内容で実現できます。
商品一覧の表の中の、IDが入力された行のいずれかのセルを右クリックすることで、隣の表のカートに、その商品を追加することができます。

▼サンプルファイル▼

以下のように何かしらの数値を日々記録している場合、値が変わった場所を見つけるのは若干大変です。

そこで今回は、以下のように「前日と値が変わったセル」と「その日付のセル」を色付けする設定方法について解説していきます。

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

前日と値が変わったセルを色付け

はじめに、前日と値が変わったセルを色付けしていきます。
特定の条件を満たしたセルの書式を自動で変更する場合は、「条件付き書式」を活用します。

まずは、日付を除く色付けする可能性のあるセルを選択します。
こちらでは、２行目のデータに関しては前日のデータが存在しないため、３行目以降のB列からD列のセルを選択します。

次に、[ホーム]タブから[条件付き書式]の[新しいルール]を選択します。

条件付き書式の設定画面が表示されましたら、[ルールの種類]を[指定の値を含むセルだけを書式設定]にして、[セルの値]→[次の値に等しくない]→「=B2」と指定します。

これは選択基準のセルB3を色付けする条件です。
セルB3を色付けするときとは、その一つ上のセルB2の値と異なる場合になるので、上記のように指定しています。
他の選択範囲に関しては、セルが相対的に参照されます。（例：セルC3→「=C2」）

条件の指定ができたら、[書式]から好みの書式を指定します。
こちらでは、[フォント]タブから「赤色」、[塗りつぶし]タブから「黄色」を指定しています。

上記のように書式の設定ができたら、設定内容を確定することで、前日と値が変わったセルを自動で色付けすることができます。

１件でも値が変わった日付のセルを色付け

では次に、１件でも値が変わった日付のセルを色付けしていきます。
先ほどは自分自身のセルに対して、前日と値が変わったかどうかを確認しましたが、今回は自分自身ではなく、他の複数セルを確認する必要があります。
そのため、先ほどより設定内容が複雑になります。

まずは、色付けする可能性のある日付の範囲、３行目以降のA列を選択します。

次に、[ホーム]タブから[条件付き書式]の[新しいルール]を選択します。

条件付き書式の設定画面が表示されましたら、[ルールの種類]を[数式を使用して、書式設定するセルを決定]にし、条件式を直接入力します。

今回の場合の条件は、以下のようになります。

「B列（数量１）が前日の値と異なる」または「C列（数量２）が前日の値と異なる」または「D列（数量３）が前日の値と異なる」場合

このように、「複数条件のいずれかを満たす場合」という条件式はOR関数を活用し、以下のように指定します。

=OR(B2<>B3,C2<>C3,D2<>D3)
// B2<>B3：B2とB3が等しくない
// C2<>C3：C2とC3が等しくない
// D2<>D3：D2とD3が等しくない

条件式の入力ができたら、[書式]から好みの書式を指定します。
こちらでは、 [塗りつぶし]タブから「黄色」を指定しています。

上記のように書式の設定ができたら、設定内容を確定することで、いずれかの項目の値が前日と変わっている日付のセルを自動で色付けすることができます。

▼サンプルファイル▼

FILTER関数の登場により、以下のようにフィルター機能を活用した絞り込みをしなくても、簡単にデータを抽出することができるようになりました。

=FILTER(B3:C100,D3:D100="着手中","")

しかし、データの確認ができても、データを更新する際は元の表の対象データを探す必要があります。

更新するたび、毎回元のデータを探すのは大変です。

ということで今回は、元のデータに瞬時に遷移するハイパーリンクを自動で表示させる方法について紹介していきます。

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

対象データの行番号を取得する

まずは、抽出されたそれぞれのデータが、元の表の何行目のデータになるのか、その行番号を取得する数式を入力していきます。

今回の表の場合、「No」の値が一意のデータになるため、抽出された「No」と一致する元の表の「No」の位置情報を取得していきます。

特定の値と一致するセルの位置情報を取得する場合は、MATCH関数が便利です。
以下のように、抽出データの先頭行の隣のセルに数式を入力します。

=MATCH(F3,B1:B100,0)
// F3：検索値
// B1:B100：検索範囲（行番号を取得するため、1行目から表の範囲を大きめに指定）
// 0：検索方法（検索値と完全一致する値が入力されたセルの位置情報を取得）

これで、先頭行に関しては抽出することができました。

次に、この結果をFILTER関数の抽出範囲の全ての行に対して表示する必要があります。
MATCH関数の検索値の範囲（F3）を複数行指定することでも、全体に展開して表示することができますが、データの増減に対応することはできません。

=MATCH(F3:F19,B1:B100,0)

データの増減に対応させるためには、FILTER関数の展開範囲から指定する必要があります。
スピルにより展開された範囲は、数式を入力したセルの後に「#」を加えることで指定することができます。

=F3#

しかし、今回指定すべき範囲は、抽出された表全体ではなく、「No」の範囲のみになります。

そのような時は、INDEX関数を活用します。
INDEX関数で列番号のみを以下のように指定することで、対象範囲の指定した列のみを抽出することができます。

=INDEX(F3#,,1)
// =INDEX(範囲,行番号,列番号)

後は、この数式をMATCH関数の検索値に指定するだけで、データの増減に対応した数式にすることができます。

=MATCH(INDEX(F3#,,1),B1:B100,0)

ハイパーリンクを表示させる

各行番号のD列（「進捗」の項目）に遷移するハイパーリンクにしていきます。
特定のセルに遷移する動的なハイパーリンクを用意するには、HYPERLINK関数を活用します。

特定のセルに遷移するハイパーリンクは、以下のように指定します。

=HYPERLINK("#[遷移先のセルのアドレス] ", "表示するテキスト")

遷移先のセルのアドレスには、以下のような指定をします。

同じシートのセルD3に遷移：”#D3″ 別シート（Sheet1）のセルD3に遷移：”#Sheet1!D3″

今回の例では、同じシート上に遷移させます。
また、行番号はMATCH関数で取得した値になるため、以下のように指定します。

=HYPERLINK("#D"&MATCH(INDEX(F3#,,1),B1:B100,0),"●")

これでハイパーリンクの完成です。
ただ、数式を入力したセル以外の書式は、以下のようにハイパーリンクの書式にならないため、列全体をハイパーリンクの書式と似た書式にしておくとよいです。

こちらでは、青文字に下線を設定しています。
直接書式を変更しているため、通常のハイパーリンクのように、クリックした後に色が変わることはありません。

ハイパーリンク自体は完成していますが、このままですと、抽出データが１件も存在しないときに、以下のようなエラーになってしまいます。

そのため、以下のようにIFERROR関数を組み合わせるとよいです。

=IFERROR(HYPERLINK("#D"&MATCH(INDEX(F3#,,1),B1:B100,0),"●"),"")

完成

以上の手順で完成です。

抽出されたデータから元のデータに遷移したい場合、対象行のハイパーリンクをクリックするだけで、簡単に遷移することができます。

▼サンプルファイル▼